SQLite Multiprocessor Access

Question

We use SQLite in several processes and a multi-threaded application. SQLite database files are encrypted using the built-in SQLite encryption. The FAQ states that SQLite should be able to manage multiple processes using a locking mechanism. We have a strange problem: When many threads access the same database file, sometimes restrictions are violated, and more specifically, a field with a unique restriction gets duplicate values ​​after calling the "insert or replace" instruction. It often happens that we use encryption. Before we started using SQLite encryption, we did not notice this behavior. Are there any specific issues with this?

Answer 1

Although SQLite is thread safe, you still cannot modify the database at the same time:

Then each thread inserts the number of records, say 1000. The problem you will encounter is this: one thread will gain control of the database by setting a lock on the file. This is fine, but the rest of the threads will continue to fail for each INSERT attempt, while the lock is active. ( link )

Only one thread can modify the database at a time, but you can have multiple threads trying to modify the database.

If you want to avoid the lock delay problem, you can check the SQLITE_BUSY flag:

Test for SQLITE_BUSY, which I did not use initially. Here is some pseudo code to illustrate the solution:

while (continueTrying) { retval = sqlite_exec(db, sqlQuery, callback, 0, &msg); switch (retval) { case SQLITE_BUSY: Log("[%s] SQLITE_BUSY: sleeping fow a while...", threadName); sleep a bit... (use something like sleep(), for example) break; case SQLITE_OK: continueTrying = NO; // We're done break; default: Log("[%s] Can't execute \"%s\": %s\n", threadName, sqlQuery, msg); continueTrying = NO; break; } } return retval; 

same link

My bet is that breaking your restriction has nothing to do with a multi-threaded process, so could you please post the actual restriction violation you get (or an example that matches www.sscce.org ).

Answer 2

Thanks for all your comments!

(we can say that we use the System.Data.SQLite.Net library)

Meanwhile, we did some more tests, and here are the results

================

We have created a tester that performs the following actions: - create a table with several fields. One of the fields - nvarchar (255) - has a unique index: "Create a unique index IX_MyKey in the table (MyKey)" - start many identical processes (25) at the same time - Each process has a key (a string representing the number 1-25) - Each process has one (main) thread executing the following in a loop for 30 seconds:

read the entry where MyKey = @MyKey (process key) get the value of the numeric field write "value + 1" in the same field of the same entry "insert or replace ... where MyKey = @MyKey"

================

• When we do everything using the System.Data.SQLite library without encryption, everything works as expected (including locks that slow down database access as the number of processes increases)

• When we use encryption (by setting a password for the database), the unique unique index is “broken” - records with the same MyKey value appear

================

So the problem is that the problem is with encryption ...

Answer 3

Make sure you are not using shared threads — each thread must create its own connection. And make sure you complete your requests in transactions.

I use the open-source System.Data.Sqlite ( http://sqlite.phxsoftware.com/ ) ADO.Net wrapper, which is thread-safe, as if you are not using thread links. It also encrypts the database easily, as described here: http://sqlite.phxsoftware.com/forums/t/130.aspx (just set the password property). Check out his forum on how he specifically uses the Microsoft Crypto API for encryption and for more information on thread safety.