SQLite multi process access

Question

Hello,

We are using SQLite in a multi processes and multi threaded application. The SQLite database files are encrypted using the embedded SQLite encryption. The FAQ states that SQLite should be able to manage multi process accesses using locks mechanism. We are experiencing a strange problem: When many threads are accessing the same database file, sometime constrains violations occur, more specifically - a field with a unique constrain is getting duplicate values after calling "insert or replace" statement. It happens quite often now, that we are using the encryption. Before we started using SQLite encryption we did not notice such a behavior. Are there any specific known issues with this?

Answer 1

While SQLite is "thread-safe" you still can't concurrently modify the database:

Each thread then proceeds to insert a number of records, let's say 1000. The problem you will encounter is the following: one thread will get control over the database by setting a lock on the file. This is fine, but the rest of the threads will keep on failing for each attempted INSERT while the lock is active. (reference)

Only one thread is allowed to modify the database at a time, but you can have multiple threads that attempt to modify the database.

If you want to avoid the failing-while-locked issue you can check the SQLITE_BUSY flag:

Test for SQLITE_BUSY, which I didn't do originally. Here's some pseudo-code to illustrate a 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 reference

My bet is that your constraint violation has nothing to do with multithreading, so could you please post the actual constraint violation that you're getting (or an example that complies with www.sscce.org).

Answer 2

Make sure you're not sharing connections across threads - each thread should make it's own connection. And make sure you're wrapping your queries in transactions.

I use the open-source System.Data.Sqlite (http://sqlite.phxsoftware.com/) ADO.Net wrapper, which is thread-safe as long as you don't share connections across threads. It also easily encrypts the database, as described here: http://sqlite.phxsoftware.com/forums/t/130.aspx (just set the password property). Search his forum for how he specifically uses the Microsoft Crypto API to do encryption, and for details on thread safety.

Answer 3

Thanks for all your comments!

(it is to mention we are using the System.Data.SQLite .Net library)

In the meanwhile we made some more tests and here are the outcomes

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

We've built a tester that does the following: - create a table with a number of fields. One of the fields - nvarchar(255) - has a unique index: "create unique index IX_MyKey on Table (MyKey)" - start many idenctical processes (25) simultaneously - Each process has a Key (string representing a number 1-25) - Each process has a single (main) thread doing the following in a loop for 30 sec:

read the record where MyKey=@MyKey (the Key of the process) get a value of a numeric field write back 'value + 1' to the same field of the same record "insert or replace ... where MyKey=@MyKey"

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

• When we do all above using the System.Data.SQLite library without encryption - everything works as expected (including locks that are slowing down the access to the database when the amount of processes increases)

• When we use encryption (by setting a password to the database), the index unique constrain is "broken" - there appear records having the same MyKey value

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

So it seams the problem is somehow related to encryption...