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
