Than you Mr Hipp! So DEFERRED advantage is that less time locked (if there are SELECTS between BEGIN and UPDATE). But its drawback is that other threads can write between BEGIN and the first UPDATE / DELETE .. ?
> Richard Hipp <mailto:drh at sqlite.org> > vendredi 15 janvier 2016 15:19 > > BEGIN IMMEDIATE claims a write lock on the database file immediately, > so that no other process or thread can come along and start a second > write transaction until after the one you just started finishes. > > BEGIN DEFERRED (the default) waits for the first actual change to the > database (a subsequent DELETE, INSERT, or UPDATE) before acquiring the > write lock. That means that even though the "BEGIN" was successful, > the next write operation might fail with an SQLITE_BUSY error if > another thread or process jumps in line ahead of you and acquires the > write lock. > > Olivier Vidal <mailto:vidal.olivier at mac.com> > vendredi 15 janvier 2016 15:09 > > Hello all, > > I would like to be sure I understand the difference between an > IMMEDIATE transaction and a DEFERRED transaction, in WAL mode. > Sorry for my bad english. > > Here is what I understand: > > Example of an IMMEDIATE transaction: > > - BEGIN IMMEDIATE TRANSACTION > - SELECT > - UPDATE > - SELECT > - UPDATE > - INSERT > - SELECT > - COMMIT > > When the transaction starts, it tries to put a RESERVED lock on the > database. If the database is busy (another thread that writes at the > same time), the engine retries for the duration of the TIMEOUT. If > this is not possible at the end of the timeout, a BUSY error is > generated. > > If BEGIN IMMEDIATE obtains the lock, it is sure that the database > cannot be modified between BEGIN and COMMIT by others threads. Also, > it is sure that the others threads that read the database at the same > time will get a non-altered view of the database, so the view before > the BEGIN. > > 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? > > is that right? > > Thank you > olivier > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
