Atomic Transaction Property: Isolation Level

There are three levels for Atomic transaction's Isolation level property:

1, Read Committed

2, Repeatable Read

3, Serializable

As below illustrate:

READ COMMITTED

It specifies that shared locks are held while the data is being read by the transaction. This avoids dirty reads, but the data can be changed before the end of the transaction. This may result in no repeatable reads or phantom data. A phantom read takes place when a transaction is allowed to read a row for the first read but is unable to modify the same row due to another transaction might be performing a delete on the row in the table. Hence, when a read is executed again within a transaction, the resultant output shows rows missing due to the delete. This is the default level.

REPEATABLE READ

It specifies that shared locks are placed on all data that is used in a query. This prevents other users from updating the data and it also prevents non-repeatable reads. In this scenario a transaction is able to read the same row multiple times and gets a different value each time. But another user can insert new phantom rows into the data set.  

SERIALIZABLE

It places a range lock on the data set, preventing other users from updating or inserting rows that could fall in that range into the data set until the transaction is complete. This is the most restrictive of the three isolation levels. Locking on a higher level than the row/record level - the table level does this. This is the only isolation level that removes the possibility of phantom rows.

COMPARISON OF ISOLATION LEVELS