Examples of Reading Data After a Concurrent Transaction Updates Data

The following example illustrates how the different isolation levels read data after a concurrent transaction, running at the same isolation level, updates some rows.

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

Shows how snapshot isolation prevents other transactions from seeing inserts and updates, even after the commit, unlike READ COMMITTED, where the other transactions could see the changes after the commit. CONSISTENT READ uses a snapshot view of the database as of the start of the transaction. This data remains consistent throughout the transaction.

READ COMMITTED

Allows all the other transactions to see its changes only after it has committed the changes. The important thing here is that neither inserts nor updates are visible to the other transactions until after the COMMIT. To do otherwise would have been, at least, an atomicity violation.

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 isolation_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
 
INSERT INTO t1 VALUES(2);
 
SELECT * FROM t1;
F1
---
1
3
5
2
 
SELECT * FROM t1;
F1
---
1
3
5
 
SELECT * FROM t1;
F1
---
1
3
5
 
UPDATE t1 SET f1=10 WHERE f1=1;
 
SELECT * FROM t1;
F1
---
10
3
5
2
 
SELECT * FROM t1;
F1
---
1
3
5
 
SELECT * FROM t1;
F1
---
1
3
5
 
COMMIT;
 
SELECT * FROM t1;
F1
---
1
3
5
 
SELECT * FROM t1;
F1
---
10
3
5
2