On 15 January 2016 at 22:09, Olivier Vidal <vidal.olivier at mac.com> wrote:
> For the DEFERRED transaction: > > - BEGIN DEFERRED TRANSACTION > - SELECT > - UPDATE > - SELECT > - UPDATE > - INSERT > - SELECT > - COMMIT > > The lock is requested at the first UPDATE (and there have no TIMEOUT?). > The database cannot be changed by others threads between the FIRST UPDATE > and COMMIT. But others threads may write between BEGIN and the first > UPDATE. Other threads that read at the same time will see the State of the > database before the first UPDATE? > Actually another thread cannot *change* the database between the first SELECT and COMMIT, although as you say they can start their own write transaction anytime before the first UPDATE. To be specific, the first SELECT will obtain a SHARED lock on the database. Another thread can still acquire the RESERVED lock at this point, and once it is ready to commit can even upgrade to the PENDING lock. However it will not be able to obtain the EXCLUSIVE lock to update the database until all SHARED locks are relinquished. -Rowan
