Difficulty running concurrent INSERTS on SQLite database in C#

Question

Hello,

I'm running a number of threads which each attempt to perform INSERTS to one SQLite database. Each thread creates it's own connection to the DB. They each create a command, open a Transaction perform some INSERTS and then close the transaction. It seems that the second thread to attempt anything gets the following SQLiteException: The database file is locked. I have tried unwrapping the INSERTS from the transaction as well as narrowing the scope of INSERTS contained within each commit with no real effect; subsequent access to the db file raises the same exception.

Any thoughts? I'm stumped and I'm not sure where to look next...

Answer 1

Update your insertion code so that if it encounters an exception indicating database lock, it waits a bit and tries again. Increase the wait time by random increments each time (the "random backoff" algorithm). This should allow the threads to each grab the global write lock. Performance will be poor, but the code should work without significant modification.

However, SQLite is not appropriate for highly-concurrent modification. You have two permanent solutions:

• Move to a "real" database, such as PostgreSQL or MySQL
• Serialize all your database modifications through one thread, to avoid SQLite's modifications.

Answer 2

Two things to check:

1) Confirmed that your version of SQLite was compiled with THREAD support

2) Confirm that you are not opening the database EXCLUSIVE