CODEWITHSUNDEEP
csqlite
Molly Birk
Molly Birk

Reputation: 178

Multithreaded reads and writes to a single SQlite database using the C API

My application consists of a number of threads (typically 5-10) and each is responsible for reading a value from an SQLite database, working on it for an amount of time and then writing a new value back to the database.

Each of the threads is running by itself without any synchronization.

My question is: Do I need to write the synchronization code myself or it is possible to interact with the SQLite C API in such a way that this gets taken care of for you. IE. If there is a transaction in progress to write a value and a different thread tries to write or read the same row, SQLite will block until it's okay to do so?

Upvotes: 1

Views: 2727

Answers (2)

Shawn
Shawn

Reputation: 52344

If there is a transaction in progress to write a value and a different thread tries to write or read the same row, SQLite will block until it's okay to do so?

The easy way to do this is use mult-threaded mode, with one connection object per thread. Then threads can acquire a lock on the database with a BEGIN IMMEDIATE transaction, and if you get a SQLITE_BUSY error, the thread can either do something else for a while before trying again, or if you had set up a busy timeout ahead of time to a reasonable for your needs timeout, the thread will just sleep for up to that length of time trying to acquire the transaction lock periodically before giving up.

If you use serialized mode and a single connection for the entire program, you have to include all the logic and locking to make sure that only one particular thread can access the database when a transaction is active yourself. Much easier to use sqlite's native, well tested, support for that functionality.

Upvotes: 2

John Bollinger
John Bollinger

Reputation: 180181

Do I need to write the synchronization code myself or it is possible to interact with the SQLite C API in such a way that this gets taken care of for you.

The SQLite documentation covers this. In a nutshell, SQLite has three different threading models available:

  • single-thread, in which all internal mutexes are disabled, and the SQLite API cannot safely be used by multiple threads at the same time;

  • multi-thread, in which the library can safely be used concurrently by multiple threads, but database connections can be used only by a single thread at a time; and

  • serialized, in which multiple threads can use the API concurrently without restriction.

There is a built-in default selected at compile time (which is "serialized" mode in a standard build). A different mode can be selected during library initialization (sqlite3_config()), and a per-connection thread mode can be specified when you open a new connection, except that single-thread mode cannot be overridden.

But note well that under all circumstances, SQLite provides a single transaction at a time per connection. Thus, you may need your own synchronization even in "serialized" mode.

If there is a transaction in progress to write a value and a different thread tries to write or read the same row, SQLite will block until it's okay to do so?

If an SQLite connection is in serialized mode and has autocommit enabled, then you're fine, in the sense that each statement will be executed in its own transaction, and different threads will not interfere with each other (but they may counteract each other). If a connection is instead in the weaker "multi-thread" mode then you must provide your own synchronization, so that different threads do not attempt to use the connection concurrently. If autocommit is disabled, then you will probably need to synchronize even in "serialized" mode to accommodate multiple threads using the same connection, else you will be unable to effectively control transaction boundaries and the contents of each transaction.

With respect to a single, established connection, there is no meaningful difference between "multi-thread" mode and "single-thread" mode.

Upvotes: 4

Related Questions