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:

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.

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.

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.

WRITE COMMITTED

For read operations, WRITE COMMITTED behaves the same as CONSISTENT READ. The main transaction changes are not visible to other transactions doing selects, even after the main transaction commits its changes. Only WRITE COMMITTED transactions doing SELECT...FOR UPDATE, UPDATE, or DELETE will see changes made by the main transaction (see Examples of Selecting Data for Update After a Concurrent Transaction Updates Data).

Main Transaction CONSISTENT READ Transaction WRITE COMMITTED 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 WRITE COMMITTED;
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
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
SELECT * FROM t1;
F1 
---
1 
3 
5
COMMIT;
     
 
SELECT * FROM t1;
F1 
---
1
3 
5
SELECT * FROM t1;
F1 
---
1
3 
5
SELECT * FROM t1;
F1 
---
10 
3 
5 
2