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).
| 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.
REPEATABLE READ and SERIALIZABLE provide the same behavior as CONSISTENT READ.
|
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:
-
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 anUPDATEstatement 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.