SQLite Forum

Updating in-process connections on write
Sharing a connection between threads that use implicit transactions means that none of the threads can be sure when a transaction begins or ends. They share a common transaction that begins when any thread starts reading and ends when all threads are done with reading simultaneously. This is not transparent to the individual threads. E.g. if two threads alternate in reading, each starting a new SELECT before the other one ends it's SELECT, the transaction never commits, and thus never sees the result of a write transaction on the other connection.

Using a separate connection for each thread gives each thread control over it's own transaction. No transaction can see "intermediate" values if you properly BEGIN and END any write transactions.