When does the first write occur in a deferred transaction?

Question

In the SQLite documentation, it states:

Deferred means that no locks are acquired on the database until the database is
first accessed. Thus with a deferred transaction, the BEGIN statement itself 
does nothing to the filesystem. Locks are not acquired until the first read or 
write operation. 

But when does this first read or write actually occur? Suppose I start a deferred transaction, then do a bunch of INSERTs or UPDATEs, then a COMMIT. Am I correct in thinking that the first write should not occur until I actually do the commit, or is it possible that the write might occur before that?

I basically want to allow multiple threads to perform transactions consisting of multiple inserts or updates, without the possibility of blocking other threads (due to the way the design was done, if this occurs there will be a deadlock). I don't care about the order of the inserts/updates between threads, only that each set is done at the same time as a single transaction.

Answer 1

The first read occurs when you are actually reading something from the database. The first write can occur as late as the COMMIT, or earlier if the database is not able to cache that much data.

To prevent deadlocks, you might need to use BEGIN IMMEDIATE instead of the default DEFERRED.

When SQLite finds that the database is locked, it just sleeps for a somewhat random amount of time and tries again. If your accesses are all from the same process, you might be able to remove useless waits and increase concurrency by using a mutex around all write transactions.