Examples of Updating a Row That is Being Updated by a Concurrent Transaction

The following example illustrates how the different isolation levels behave when selecting and updating a row that is being updated by a concurrent transaction.

In this example, all three isolation levels supported by NuoDB are illustrated. The isolation level of the main transaction is the same as the isolation level for the transaction being illustrated in the second, third, and fourth columns. In other words:

The isolation_level for the main transaction is CONSISTENT READ when compared with the behavior of the statements listed in the CONSISTENT READ column.

The isolation_level for the main transaction is WRITE COMMITTED when compared with the behavior of the statements listed in the WRITE COMMITTED column.

The isolation_level for the main transaction is READ COMMITTED when compared with the behavior of the statements listed in the READ COMMITTED column.

Deadlock Detection: If blocking exists, there is a possibility of deadlock. Therefore, NuoDB has a distributed deadlock detection system that detects such deadlocks and then picks one of the transactions to sacrifice (force abort). This is very similar to what a database using two phase locking would do.

CONSISTENT READ

The update of the main transaction causes an update conflict in the second CONSISTENT READ transaction. This is because there is no consistent way to both select a record version and update that record version. CONSISTENT READ sees a snapshot view, as defined at the start of the transaction, not the changes made by the main transaction. Any continued update attempt on that row will result in an update conflict because the transaction's snapshot view cannot be changed.

WRITE COMMITTED

Identical update behavior to READ COMMITTED. This transaction detected the main transaction update and blocked the update, awaiting a final state of the main transaction once a commit or rollback occurs. When the main transaction commits, the WRITE COMMITTED transaction evaluated the update predicate in terms of the most recent committed version. It then attempts the update on the row(s). However, since there was no longer a record where f1 = 1, the WRITE COMMITTED update did not update any rows and also did not return any error.

READ COMMITTED

This transaction detected the main transaction update and blocked the update, awaiting a final state of the main transaction once a commit or rollback occurs. When the main transaction commits, the READ COMMITTED transaction evaluated the update predicate in terms of the most recent committed version. It then attempts the update on the row(s). However, since there was no longer a record where f1 = 1, the READ COMMITTED update did not update any rows and also did not return any error.