Examples of Concurrent Updates That Do Not Overlap

The following example illustrates how the different isolation levels behave when concurrent transaction updates do not overlap.

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 does not cause an update conflict in the second CONSISTENT READ transaction because the main transaction is updating a different set of rows. Each CONSISTENT READ transaction updates their respective rows. The CONSISTENT READ transaction continues to see only the snapshot of the database at the start of the transaction plus changes made by that transaction only.

READ COMMITTED

This second READ COMMITTED transaction detected the main transaction updates and blocks, awaiting a final state of the main transaction. When the main transaction performs the COMMIT, the second READ COMMITTED transaction evaluates the update predicate in terms of the most recent committed version. It then updates the rows.

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
WHERE f1 < 4;
SELECT * FROM t1;
F1
---
2
4
5
7
 
UPDATE t1 SET f1 = f1+1
WHERE f1 > 4;
SELECT * FROM t1;
F1
---
1
3
6
8
 
UPDATE t1 SET f1 = f1+1
WHERE f1 > 4;
/* Blocks */
 
COMMIT;

Update completes, no error.

 
SELECT * FROM t1;
F1
---
2
4
5
7

Subsequent reads see only its updates.

SELECT * FROM t1;
F1
---
1
3
6
8
 
SELECT * FROM t1;
F1
---
2
4
6
8