SQLite Forum

Statements outside of explicit transactions in SQLite
Your description is accurate.  "Transaction" processing is controlled by "locks".  All "lock" processing is "automatic".  An "explicit transaction" is merely exerting manual control by "turning off (BEGIN) and on (COMMIT)" the automation.

A "read-lock" is required when reading from the database, and a "write-lock" is required to write to it (and exclusive access -- meaning no OTHER readers -- is required to "make it so").

Every statement, when executed, must acquire the locks that it requires for the duration of its execution.  When the statement completes, the **additional** locks that the statement acquired are released.  Locks which were already acquired on the same connection will not be released until that other statement that acquired them is complete.

This means that when you execute a SELECT statement on a connection, it will acquire a READ lock for the entire duration of the statement execution.  If while this statement is executing you execute an INSERT statement (on the same connection), that connection must acquire a WRITE lock.  When that INSERT statement completes then the data is written (assuming there is NO OTHER CONNECTION that has a READ lock by temporarily acquiring an EXCLUSIVE lock and writing the data) and releases the WRITE lock.  It cannot release the READ lock because the SELECT still requires it.  Once the SELECT statement is complete, then the READ lock can be released.

The BEGIN statement makes some small changes to this process.  It sets a flag that stops the "automatic release of acquired locks".  In addition it may also acquire a specific lock type at the time it is executed though the default is just to set the flag and not acquire any locks.  If you use the form BEGIN IMMEDIATE, then a WRITE lock is obtained immediately as well as turning off the "auto release mode".  Mutatis Mutandis BEGIN EXCLUSIVE with an EXCLUSIVE lock.

Now when you make changes (ie, execute an INSERT statement) and the "auto release" mechanism is turned off, the WRITE lock is held after the statement completes and the changes are not yet written to the database.  Eventually a COMMIT statement will be executed.  This will "turn the auto-release" flag back on and when the COMMIT statement ends it will release the locks THAT ARE NO LONGER REQUIRED (in the case of releasing an EXCLUSIVE or WRITE lock, it will write the changes to the database).  However, if there is a statement executing which requires a READ lock then that read lock will not be released until that statement ends (which will now release its locks because the "auto-release" flag has now been turned back on).