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:

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.

WRITE COMMITTED

Each WRITE COMMITTED transaction starts with a snapshot view of the database as of the start of the current transaction. The main transaction, when it does the update, will block any concurrent updates. The second transaction waits for the main transaction to finish with either a commit or a rollback, before it can complete the update. In the case of WRITE COMMITTED, the UPDATE proceeds on the most recent committed version of the row(s). Subsequent reads in the WRITE COMMITTED transaction continue 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 WRITE COMMITTED Transaction READ COMMITTED Transaction
CREATE TABLE t1 (f1 INTEGER);
INSERT INTO t1 VALUES (1),(3),(5),(7);
COMMIT;
START TRANSACTION
ISOLATION LEVEL 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 WRITE COMMITTED;
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 */
UPDATE t1 SET f1 = f1+1
WHERE f1 > 4;
/* Blocks */
COMMIT;	
  Update completes, no error. 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
Subsequent reads see only its updates.
SELECT * FROM t1;
F1 
---
1 
3 
6 
8
SELECT * FROM t1;
F1 
---
2 
4 
6 
8