Isolation levels - cojen/TuplDB GitHub Wiki

When using the base API, the isolation level is controlled by the lock mode, which is UPGRADABLE_READ by default. The locks only apply to individual entries (or rows), and for this reason the base API doesn't support the serializable isolation level.

Isolation level	Lock mode	Read lock type	Write lock type
Serializable	—	—	—
Repeatable read	UPGRADABLE_READ	upgradable	exclusive
Repeatable read	REPEATABLE_READ	shared	exclusive
Read committed	READ_COMMITTED	shared	exclusive
Read committed	Auto-commit (null transaction)	shared	exclusive
Read uncommitted	READ_UNCOMMITTED	none	exclusive
—	UNSAFE	none	none

For all modes except for UNSAFE, writing an entry acquires an exclusive lock, which is held until the transaction (or scope) finishes.

When reading an entry with a repeatable read isolation level, the associated read lock is held until the transaction (or scope) finishes. When updating an entry using the REPEATABLE_READ lock mode, the associated lock might need to be upgraded from a shared lock to an exclusive lock. Because this is deadlock prone, the upgrade attempt is illegal by default. If desired, the rule can be changed to allow the upgrade to succeed under some circumstances.

When using nested transaction scopes, the held locks which were acquired within the scope are promoted to the parent scope only when the scope commits. If the scope is rolled back, all of the locks acquired within the scope are released after undoing any changes. A scope which commits behaves as if the scope wasn't nested, and a scope which rolls back behaves as if it never existed at all.

Cursor stability

The base cursor API doesn't support cursor stability, except when using a repeatable read isolation level. With the read committed level, the lock is released immediately after the entry has been copied to the cursor's value. To emulate cursor stability with the read committed level, the lock can be explicitly released when advancing the cursor:

Database db = ...
View view = ...

Transaction txn = db.newTransaction(); // UPGRADABLE_READ

try (Cursor c = view.newCursor(txn)) {
    for (LockResult result = c.first(); c.key() != null; result = c.next()) {
        if (<should update>) {
            c.store(...);
        } else if (result.isAcquired()) {
            // Wasn't updated, so release the lock.
            txn.unlock();
        }
    }
    txn.commit();
} finally {
    txn.exit();
}

Table API

The table API builds upon the base API and introduces predicate locking and automatic cursor stability. Predicate locks and row locks are used to implement serializable isolation, which is the default. Predicate locks prevent rows from being inserted or updated into the query results of another transaction, and row locks prevent deletes as usual.

Note than when using the table API, a pure repeatable read isolation level cannot be configured. Instead, the isolation level is promoted to serializable, which is stronger.

When using a scanner:

Isolation level	Lock mode	Read lock type	Write lock type	Predicate lock	Cursor stability
Serializable	UPGRADABLE_READ	upgradable	exclusive	yes	yes
Serializable	REPEATABLE_READ	shared	exclusive	yes	yes
Repeatable read	—	—	—	—	—
Read committed	READ_COMMITTED	shared	exclusive	yes	no
Read committed	Auto-commit (null transaction)	shared	exclusive	no	no
Read uncommitted	READ_UNCOMMITTED	none	exclusive	no	no
—	UNSAFE	none	none	no	no

When using an updater, upgradable locks are used instead of shared locks, and cursor stability is supported for all isolation levels. The read uncommitted isolation level isn't supported, and instead the stronger read committed level is used.

Isolation level	Lock mode	Read lock type	Write lock type	Predicate lock	Cursor stability
Serializable	UPGRADABLE_READ	upgradable	exclusive	yes	yes
Serializable	REPEATABLE_READ	upgradable	exclusive	yes	yes
Repeatable read	—	—	—	—	—
Read committed	READ_COMMITTED	upgradable	exclusive	yes	yes
Read committed	Auto-commit (null transaction)	upgradable	exclusive	no	yes
Read committed	READ_UNCOMMITTED	upgradable	exclusive	no	yes
Read uncommitted	—	—	—	—	—
—	UNSAFE	none	none	no	no

When using either a scanner or an updater, a transaction with an explicit READ_COMMITTED mode uses a predicate lock, and so it's a bit stronger than what the read committed isolation level requires. The predicate lock prevents observing a row more than once if another transaction tries moving it to a different location within the table or secondary index. The other transaction must wait on the predicate lock instead. When using a null transaction, the isolation level is also read committed, but without the predicate lock.