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
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
ROLLBACK statement is executed, the setting of autocommit is again in effect (see COMMIT, ROLLBACK).
|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.
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.
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:
If the conflicting transaction is rolled back, the current transaction continues executing.
If the conflicting transaction is committed, the current transaction reads the changed record and reapplies the condition of the query modifying the record. For example, the transaction reevaluates the
WHEREclause of an
UPDATEstatement on the current version of the record. If the record still matches the condition, the transaction proceeds with the update. If not, the record is ignored.
Refer to the following examples for illustrations of transactions using each isolation level: