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 the 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

(REPEATABLE READ and SERIALIZABLE are also allowed and provide the same behavior as 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.

WRITE COMMITTED

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

NuoDB also supports a transaction isolation level called READ COMMITTED. At this level, a transaction always reads the most recently committed version of a record. The same query can return different results as other transactions update records and then commit or roll back changes.

At the READ COMMITTED isolation level, NuoDB handles update conflicts in the same way that it handles them at the WRITE COMMITTED level. While the first transaction is performing the UPDATE of a row, the second transaction must wait for the the first transaction to commit or roll back the update. The second transaction can then update the newest version of the updated row. If the first transaction deletes the row then the second transaction returns an error. Otherwise, the second transaction can successfully perform any type of update, including deletion of the row.

Examples

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