Set ConnectionTimeout in System.Data.SQLite

Question

I think that I'm experiencing a database connection timeout with my SQLite connection, but I'm not sure how to bump up the connection timeout. The command timeout can be set with ConnectionString.DefaultTimeout but Connection.ConnectionTimout is read only. Any suggestions?

Thanks, Brian

Answer 1

It is set in the connection string: SqlConnection

Answer 2

Exceeding the default (30 seconds) timeout for SQLite queries is a strong indication that something is wrong in your approach.

Timeouts usually occur when there are too many concurrent connections. SQLite behaves poorly when you have interleaved write/read transactions.

Make sure that you chunk related SQL statements in a single transaction (the performance gains can be x1000!).

Another tip: by default - when you start a transaction (BeginTransaction) - the default behavior is to get a write lock immediatly. Try to use BeginTransaction(deffered) version instead and specify that you want to defer acquiring a write lock until it is actually needed. This will help you if have multiple readers because now they will be able to run concurrently.

Good Luck

Liron Levi

Creator of the SQLite Compare diff/merge tool