SQLite database is getting locked

Question

I have a piece of code in C++ that creates many threads & each of these threads access a common SQLite database & SELECTs & INSERTs data.

All the INSERTs are with in transactions. Many times I get "Database is locked" error.

How can I fix this?

Answer 1

You can use SQLite in a multithreaded way, but for each thread you must open a new connection (sqlite3_open()) and operate on that connection.

There is more info in the official documentation

Answer 2

If you start your transactions only with "BEGIN" on more than one thread and you are then using INSERTs sqlite can deadlock.

That's because sqlite only aquires a read lock on BEGIN. On the INSERT it has to upgrade that read lock to a write lock (which it can only do if there is no read lock). This does not happen with journal_mode=WAL (only the default journal_mode=delete) as wal mode allows read access during writes. (So using WAL mode might be a fix).

Solution: Use BEGIN IMMEDIATE if you plan to use an insert in a transaction. That way sqlite aquires a write lock on the database.