SQLite Forum

Unexepected commit result with two threads application
Hm nope your expectations are reasonable -- that should work fine using a separate connection for each thread.

How are you handling errors during COMMIT? Do you have a busy handler or a timeout configured?

In the default rollback journal mode, once the updates have run and the change is ready to commit┬╣, sqlite acquires the PENDING lock to indicate it is ready to modify the main db file, but it has to wait until all readers finish and relinquish their SHARED locks before it can acquire the EXCLUSIVE lock and actually write the new data.

If the busy timeout expires before the readers finish, then you will get an `SQLITE_BUSY` error. So if your policy on errors is to rollback then that would explain the behaviour you're seeing. The default configuration for a connection is no timeout, ie. you will immediately get `SQLITE_BUSY` if any readers are active. See <https://www.sqlite.org/pragma.html#pragma_busy_timeout>

┬╣ note this can also happen before commit time if your transaction is large enough to spill sqlite's memory cache; see <https://www.sqlite.org/pragma.html#pragma_cache_spill>