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.

Reply via email to