On 10 Jun 2015, at 9:48am, Olivier Vidal <paxdo at mac.com> wrote: > SELECT > UPDATE > In this example, the set of these commands is serialized? The two commands > act on the same snapshot? So, two commands have the same data (same > snapshot), but those data may be modified by another thread/process between > SELECT and UPDATE, and UPDATE will be unaware of this previous change?
In addition to Clemens' excellent answer, I tell you that most of these questions will be answered if you just trust that SQLite will do The Right Thing. So a SELECT locks the database so that nothing can change it, but an UPDATE locks the database so that nothing can change it and nothing can get an inconsistent view of it. Another thread doing a SELECT while you are doing an UPDATE will retrieve all its data from before anything changes, or retrieve all its data from after the UPDATE is finished. Never with some updated and some not. If you are making a change which depends on the answer to a SELECT operation and it's important that nothing changes the database between the two of them, you just put both operations in the same transaction: BEGIN; SELECT ...; UPDATE ...; END; BEGIN DEFERRED (which is BEGIN's default) does not lock the database until the first command which needs it locked. This makes sure that the databases stays unlocked for the longest time possible, which will suit other threads trying to access it. BEGIN IMMEDIATE puts a write-lock on the database immediately. BEGIN EXCLUSIVE puts a read-lock and write-lock on the database immediately. Simon.
