> SQLite transactions are always serializable.
Serializable mode requires two-phase locking protocol. In practice
it means all locks (for read and for write) hold until transaction
end.
Deferred transaction drop SHARED lock in middle of transaction
and replace it with RESERVED lock. Hence, it is not two-phase
transaction. AFAIK this behaviour is not allowed for true
serializable transactions.
With correct lock implementation serializable transactions
must not release READ lock in the middle. If two transaction
hold READ locks and then both of them try to acquire WRITE
locks it must lead to deadlock. It is the reason for special
UPDATE or UPGRADE lock mode which means "read, and then
possible write".
In previous mail I describe situation when two transactions
started, acquires SHARED mode and then (one-after-another)
update database and commit successfully. According
documentation (http://sqlite.org/lockingv3.html) this can
take place if first transaction release EXCLUSIVE lock
before second transaction try to acquire RESERVED lock.
Hence, SQLite deferred transactions is not serializable ones.
Best regards,
Alexandermailto:[EMAIL PROTECTED]
