There are four standard SQL isolation levels: Read Uncommitted, Read Committed, Repeated Read, and Serializable. (c.f. Database System Concepts 6ed.)
In SQLite, there are three isolation levels: DEFERRED, IMMEDIATE, or EXCLUSIVE. They control how the required read/write locks are acquired. See https://www.oreilly.com/library/view/using-sqlite/9781449394592/ch04s07.html
By default, all transactions (including autocommit transactions) use the DEFERRED mode. Under this mode, none of the database locks are acquired until they are required. This is the most “neighborly” mode and allows other clients to continue accessing and using the database until the transaction has no other choice but to lock them out. This allows Transaction Control Language | 53other clients to continue using the database, but if the locks are not available when the transaction requires them, the transaction will fail and may need to be rolled back and restarted.
IMMEDIATE attempts to acquire a reserved lock immediately. If it succeeds, it guarantees the write locks will be available to the transaction when they are needed, but still allows other clients to continue to access the database for read-only operations.
The EXCLUSIVE mode attempts to lock out all other clients, including read-only clients. Although the IMMEDIATE and EXCLUSIVE modes are more restrictive to other clients, the advantage is that they will fail immediately if the required locks are not available, rather than after you’ve issued your DDL or DML commands.
I was wondering if it is possible to make some correspondence between the four standard SQL isolation levels and the three SQLite isolation levels?
Does DEFERRED mean exactly serializability isolation level, but only applies to transactions which consist of only one statement not more than one?
Does IMMEDIATE mean exactly read uncommitted isolation level, which prevents dirty write but not dirty read?
Does EXCLUSIVE mean exactly serializability isolation level?