Description of NuoDB Transaction Isolation Levels

Isolation levels are set for the duration of a transaction. Use the START TRANSACTION command to set the isolation level of the current transaction (see START TRANSACTION). You can also specify READ_ONLY or READ_WRITE.

The NuoDB default is that autocommit behavior is enabled. This means that each statement is executed in its own transaction and is automatically committed. When autocommit behavior is enabled then execution of START TRANSACTION suspends it for the duration of the transaction. After a COMMIT or ROLLBACK statement is executed, the setting of autocommit is again in effect (see COMMIT, ROLLBACK).

Note: Different NuoDB drivers support isolation levels in different ways. Consult the documentation for each driver you are using to find which isolation levels are supported by that driver.

The following is a high level description of each isolation level supported by NuoDB.

CONSISTENT READ

A transaction running with the CONSISTENT READ isolation level reads a snapshot of the database at the start of the transaction. The transaction can perform updates, including deletes, successfully on those rows, providing no other concurrent transaction has updated those same rows. When a transaction running at the CONSISTENT READ isolation level attempts to update or delete a record that has been changed by a concurrent transaction, it waits for the other transaction to complete. When the other transaction completes then the waiting transaction succeeds in its update only if the previous transaction rolled back. Otherwise, the waiting transaction gets an error.

How the waiting transaction handles the error depends on the logic of the application. If the logic requires updating that particular record, then the application must rollback the transaction that got the error and retry it. If not, then the transaction can retry the update and succeed.

Note: REPEATABLE READ and SERIALIZABLE provide the same behavior as CONSISTENT READ.

WRITE COMMITTED

Note: With the release of NuoDB 3.2, WRITE COMMITTED functionality has been deprecated.

Applications that depend on updating the same record repeatedly are difficult to run at the CONSISTENT READ isolation level; there are usually frequent failures because of conflicting updates. To avoid these update conflicts, at the expense of read consistency, NuoDB supports WRITE COMMITTED, which is a lower level of transaction isolation.

For read operations, a transaction running at the WRITE COMMITTED isolation level will have the same behavior as CONSISTENT READ.

For write operations, a transaction running at the WRITE COMMITTED isolation level will have the same behavior as READ COMMITTED.

READ COMMITTED

Unlike a transaction using CONSISTENT READ, a transaction using the READ COMMITTED isolation level takes a snapshot of the database at the beginning of each statement executed in the transaction. This means that transactions using READ COMMITTED read record changes that have been committed after the transaction has started, but before the current statement starts to execute. Re-executing the same statement can return different results as other transactions update records and then commit changes.

In comparison to CONSISTENT READ, a READ COMMITTED transaction does not throw a concurrent update exception. When a concurrent transaction modifies the same record, a READ COMMITTED transaction waits for the conflicting transaction to end. Once it does, the READ COMMITTED transaction performs one of the following actions:

Examples

Refer to the following examples for illustrations of transactions using each isolation level: