"database locked" errors with SQLITE_CONFIG_SERIALIZED

2013-03-23 22:36  i3068     [permalink]

I've been impressed by sqlite3 ever since I get to know about it. It's open source, and even more it's really put in the public domain completely avoiding the licensing headache. I haven't read the source in great detail, but as I understand it it's all in a single big file! Even how it works internally is impressive.

Since I didn't like the options available, I created my own very thin wrapper for sqlite3.dll without much trouble. Only in this one multi-threaded application, I've run into an occasional "database locked" error, so I had to search documentation and source for what I can do about it.

Working with other database solutions, I've had the common sense of using a database connection for each thread, so each could work with the database undisturbed by eachother. As it turns out this is not required with sqlite3! As stated with SQLITE_CONFIG_SERIALIZED, which is the default setting, you're better off using a single connection over all threads. Indeed in my case it solved the "database locked" errors.

Update 2016-03-02: Using a single connection over all threads, has a negative effect on transactions over queries that make changes, especially when multiple threads want to start a transaction on the same connection, which is not at all the point of connections! After some searching around, the normal way to avoid the 'SQLITE_BUSY' errors (which apparently translate to "Database is locked.") is to set a busy timeout.

→