CODEWITHSUNDEEP
c#sqlitesystem.data.sqlite
Fred
Fred

Reputation: 391

How to wait for the database lock to clear in system.data.sqlite

My application works in the following way: Each query opens a new connection to the database file with sqlite3, and after the query runs, it closes the connection with the close() method.

The problem: Another thread created by my application needs to do an INSERT query, but while this is happening the "main" body of my application is performing some SELECTs and they seem to collide and since sqlite doesn't allow selects and updates at the same time, it cases problems. Namely, the application freezes for 30 seconds, after which the sqlite connection times out and I get:

A first chance exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll
System.Data.SQLite.SQLiteException (0x80004005): database is locked

Is there some way to instruct a query to wait for the lock to clear before trying to proceed? Or perhaps some other way of solving the problem?

Upvotes: 0

Views: 4883

Answers (1)

CL.
CL.

Reputation: 180240

Those 30 seconds are the wait.

You can increase the wait time with PRAGMA busy_timeout or the corresponding connection parameter, but this does not help if some transaction blocks the database for longer than that.

If you can live with its restrictions, try enabling WAL mode, where a writer does not block readers.

Upvotes: 3

Related Questions