What standard SQL isolation levels do the SQLite isolation levels correspond to

concurrencyisolation-levelsqlitetransaction

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?

Thanks.

Best Answer

DEFERRED, IMMEDIATE, and EXCLUSIVE are not isolation levels. All three modes ensure the serializable isolation level. (SQLite's transcations are always serializable because it actually serializes them; there is a single, database-wide lock.)

IMMEDIATE takes the equivalent of a write lock at the beginning of the transaction; this prevents a deadlock when two concurrent read-only transactions try to update to a write lock. EXCLUSIVE is useful when you want to lock the database without actually writing.

Related Question