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

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.

Main Transaction CONSISTENT READ Transaction READ COMMITTED Transaction 
CREATE TABLE t1 (f1 INTEGER);
INSERT INTO t1 VALUES (1),(3),(5),(7);
COMMIT;
START TRANSACTION
ISOLATION LEVEL [.var]__isolation_level__;
SELECT * FROM t1;
F1
---
1
3
5
7
 
START TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM t1;
F1
---
1
3
5
7
 
START TRANSACTION
ISOLATION LEVEL READ COMMITTED;
SELECT * FROM t1;
F1
---
1
3
5
7
 
UPDATE t1 SET f1 = f1+1;
SELECT * FROM t1;
F1
---
2
4
6
8
 
SELECT * FROM t1;
F1
---
1
3
5
7
 
SELECT * FROM t1;
F1
---
1
3
5
7
 
UPDATE t1 SET f1=110
WHERE f1 = 1;
/* Blocks */
 
UPDATE t1 SET f1=110
WHERE f1 = 1;
/* Blocks */
 
COMMIT;
 
SELECT * FROM t1;
F1
---
2
4
6
8

ERROR: update conflict in table T1.

Update completes, no error.
No Update as there is no row with f1=1.

SELECT * FROM t1;
F1
---
2
4
6
8
 
UPDATE t1 SET f1=110
WHERE f1 = 1;
 +
ERROR: update conflict in table T1
 
UPDATE t1 SET f1=110
WHERE f1 = 2;
COMMIT;
 
SELECT * FROM t1;
F1
---
2
4
6
8

Any reads continue with snapshot view.

SELECT * FROM t1;
F1
---
1
3
5
7
 
SELECT * FROM t1;
F1
---
110
4
6
8