System.Data.SQLite

This will come up frequently, so I thought I'd sticky a post about it.

The SQLite engine itself works fine in a multithreaded environment as long as you follow the rules.  The chief rule is: you may not have multiple threads calling sqlite functions on the same connection pointer, nor may multiple threads call sqlite functions on pointers that hold a reference to the same connection pointer.  Sure you could implement your own synchronization objects over the top of these pointers to prevent simultaneous access, but the ADO.NET wrapper does not do this -- therefore the same rules apply to ADO.NET.  I'll expand a bit:

It is perfectly legal for two threads to each have their own instance of a SQLiteConnection object, and for each of those connections to reference the same database file.  This is the "right" way to do things.  You can also call the Clone() method on an open connection at any time and pass the cloned connection to a secondary thread.  It is NOT safe to pass a connection to another thread and THEN clone it -- clone it FIRST and pass the clone to the secondary thread.

Just about every other object in the wrapper holds a reference to the underlying SQLiteConnection object too, and therefore should never be shared between threads.  SQLiteCommand, SQLiteDataReader, SQLiteCommandBuilder, SQLiteDataAdapter, and SQLiteTransaction are just some of the objects that either directly or indirectly hold a reference to a SQLiteConnection and should never be shared across threads.  SQLiteCommand also implements ICloneable, so if you must share a command across threads, make a clone first.

So to summarize:  If multiple threads must talk to the same database file, then make multiple connections to it and make sure you don't share them.  Also remember that SQLite's locking mechanism is not row or table level, it is file level locking.  Therefore you cannot be actively iterating through a datareader while a write operation is pending, and all write operations are suspended while a datareader is reading.  The ADO.NET wrapper has built-in retry and timeout mechanisms to handle these scenarios, but if you are using multiple threads and attempt to mix a read with a write, or a write with a read, you're going to hit a deadlock.

Robert

 

rsimpson wrote:

Sure you could implement your own synchronization objects over the top of these pointers to prevent simultaneous access, but the ADO.NET wrapper does not do this -- therefore the same rules apply to ADO.NET.

To clarify, is this acceptable to do?  I'm using a SQLite database as a log file and have threads coming from everywhere which want to write to the log file.  Some come from async operation thread pools, so there's no way of assigning every thread its own connection.  To get around this problem I implemented my own locking mechanism on a single SQLiteConnection so that only one thread will be executing a statement at a time.

It seems to work well but am I going to run into a problem in the underlying code due to some thread-specific resource?  I'd like to avoid creating a specific thread to do my logging.

It works as long as you coded it properly :)  The SQLite engine itself doesn't have any threading issues, so as long as you've guarded your connection (and anything that executes on that connection) with a mutex, you should be fine.

Robert

 

rsimpson wrote:

It works as long as you coded it properly :)

Well that's always up for debate.  Thanks for alleviating my fears, I was just hoping that something wasn't allocated in thread-local storage or something crazy like that which would cause an invisible problem.  Awesome!

Did the 3.5.0 update relax the restrictions in regards to this?  I know the SQLite added some pretty heavy use of mutexes in that release.