Transaction Isolation Levels

Transaction isolation defines how and when the changes made by one transaction become visible to other concurrent transactions. DBMSs usually offer a number of transaction isolation levels that define the degree to which one transaction must be isolated from data modifications made by other transactions. In fact, the ANSI / ISO SQL standard defines four levels of transaction isolation: Read Uncommitted, Read Committed, Repeatable Read and Serializable. The eXtremeDB transaction managers support three of these levels as described below.

Read Committed

When this level is used a transaction always reads committed data. The transaction will never read data that another transaction has changed and not yet committed, but it does not ensure that the data will not be changed before the end of the transaction. With MVCC (but not MURSIW) it is possible that a value can change if it is re-read during the transaction, because another transaction committed a change to that same object.

Repeatable Read (snapshot)

This is the default isolation level in eXtremeDB. When a MVCC transaction runs at the Repeatable Read level, it creates a snapshot of the object(s) read when they are read. Re-reading the object will re-read the snapshot, not the committed object in the database (that might have been changed), hence the name “repeatable read”. For MURSIW, transactions are both Read Committed and Repeatable Read because no write transaction can execute in parallel with any other transaction.

Serializable

This level applies an exclusive lock to all write transactions—no other write transactions can run at the same time, however, with MVCC, “readers” can still run in parallel with the “writer” and with each other. Note that this “sequential” transaction processing of MVCC write transactions applies only to transactions with MCO_SERIALIZABLE isolation level; transactions with other isolation levels can be active simultaneously. With MURSIW, write transactions are always serialized.

Example

To illustrate how choosing an isolation level with MVCC could be important, consider the example of two transactions t1 and t2 that concurrently read and update data sets as follows:

 
    a=1 b=2
    t1: e=b ; a=a+1; c=a+b
    t2: f=a ; b=b+2; d=a+b
     

Serializable case #1

 
    t1: e=b			e=2
    t1: a=a+1	a=1+1	a=2
    t1: c=a+b	c=2+2	c=4
 
    t2: f=a			f=2
    t2: b=b+2	b=2+2	b=4
    t2: d=a+b	d=2+4	d=6
     
    -------------------------
    Result: a=2, b=4, c=4, d=6, e=2, f=2
     

Serializable case #2

 
    t2: f=a			f=1
    t2: b=b+2	b=2+2	b=4
    t2: d=a+b	d=1+4	d=5
 
    t1: e=b			e=4
    t1: a=a+1	a=1+1	a=2
    t1: c=a+b	c=2+4	c=6
     
    -------------------------
    Result: a=2, b=4, c=6, d=5, e=4, f=1
     

Repeatable read

 
    t1: read a		a(t1)=1
    t1: read b		b(t1)=2
    t2: read a		a(t2)=1
    t2: read b		b(t2)=2
    t1: e=b(t1)		e=2
    t2: f=a(t2)		f=1
    t2: b(t2)=b(t2)+2	b=2+2 b(t2)=4
    t2: d=a(t2)+b(t2)	d=1+4 d=5
    t2: commit
 
    t1: re-read a		a(t1)=1
    t1: re-read b		b(t1)=2
    t1: a(t1)=a(t1)+1	a=1+1 a(t1)=2
    t1: c=a(1)+b(t1)	c=2+2 c(t1)=4
    t1: commit
 
    -----------------------------
    Result: a=2, b=4, c=4, d=5, e=2, f=1
     

Read committed

 
    t1: read a		a(t1)=1
    t1: read b		b(t1)=2
    t2: read a		a(t2)=1
    t2: read b		b(t2)=2
    t1: e=b(t1)		e=2
    t2: f=a(t2)		f=1
    t2: b(t2)=b(t2)+2	b=2+2 b(t2)=4
    t2: d=a(t2)+b(t2)	d=1+4 d=5
    t2: commit
     
    t1: re-read a		a(t1)=1
    t1: re-read b		b(t1)=4
    t1: a(t1)=a(t1)+1	a=1+1 a(t1)=2
    t1: c=a(1)+b(t1)	c=2+4 c(t1)=6
    t1: commit
 
    -----------------------------
    Result: a=2, b=4, c=6, d=5, e=2, f=1
     

In summary, the quite different results of each of these cases are:

 
    Serializable #1:	a=2, b=4, c=4, d=6, e=2, f=2
 
    Serializable #2:	a=2, b=4, c=6, d=5, e=4, f=1
 
    Repeatable Read:	a=2, b=4, c=4, d=5, e=2, f=1
 
    Read Committed:	a=2, b=4, c=6, d=5, e=2, f=1