Examples of Selecting Data for Update After a Concurrent Transaction Updates Data

The following example illustrates how the different isolation levels attempt SELECT…​FOR UPDATE after a concurrent transaction has performed updates.

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 main transaction will block the second CONSISTENT READ transaction on a SELECT…​FOR UPDATE. The second CONSISTENT READ transaction blocks because it has detected a concurrent update and doesn’t know whether to fail or update until the main transaction has finished. Only if the main transaction performs a ROLLBACK can the second CONSISTENT READ transaction proceed.

READ COMMITTED

The main transaction will block the second READ COMMITTED transaction on a SELECT…​FOR UPDATE. The second READ COMMITTED transaction blocks because it has detected a concurrent update and doesn’t know whether to fail or update until the main transaction has finished. Once the main transaction has successfully committed the transaction, the second READ COMMITTED transaction completes its update after it has obtained the most recent version of the row.

Main Transaction CONSISTENT READ Transaction READ COMMITTED Transaction 
CREATE TABLE t1 (f1 INTEGER);
INSERT INTO t1 VALUES (1),(3),(5);
COMMIT;
START TRANSACTION
ISOLATION LEVEL [.var]__isoloation_level__;
SELECT * FROM t1;
F1
---
1
3
5
 
START TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM t1;
F1
---
1
3
5
 
START TRANSACTION
ISOLATION LEVEL READ COMMITTED;
SELECT * FROM t1;
F1
---
1
3
5
 
UPDATE t1 SET f1 = 42 WHERE f1 = 5;
 
SELECT * FROM t1;
F1
---
1
3
42
 
SELECT * FROM t1;
F1
---
1
3
5
 
SELECT * FROM t1;
F1
---
1
3
5
 
SELECT f1 FROM t1
WHERE f1 = 5 FOR UPDATE;
/* Blocks */
 
SELECT f1 FROM t1
WHERE f1 = 5 FOR UPDATE;
/* Blocks */
 
COMMIT;

ERROR: update conflict in table T1. Any reads continue with snapshot view.

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

 
SELECT * FROM t1;
F1
---
1
3
42

Any reads continue with snapshot view.

SELECT * FROM t1;
F1
---
1
3
5
 
SELECT * FROM t1;
F1
---
1
3
42
 
COMMIT;
 
COMMIT;
 
SELECT * FROM t1;
F1
---
1
3
42
 
SELECT * FROM t1;
F1
---
1
3
42