A Manager's Blog: Isolation levels in Transactions

Recently I was revisiting my understandings on the Isolation levels in transaction, a topic which I had read long time back and had started to evaporate in my memory.

Read a couple of very interesting articles on the subject (references below) and have summarized my understandings on the subject in this article.

Need for Isolation levels in transactions

A transaction is a series of actions that need to be executed in entirety and the obvious reason for this is data consistency.

Every transaction acquires some resources to do some work on them. Now based upon the need of the actions in the transactions, the desired resources can be acquired either exclusively for the transaction, thus blocking any other transaction that may need the same resources OR if the resources are acquired in sharing mode then other transactions can also use the same resources thus increasing the concurrency but potentially risking data(resource) consistency.

Isolation Levels

Traditionally there are 4 isolation levels but since SQL Server 2005, two more have been added, which are more optimistic in nature (the traditional 4 are labeled as pessimistic in nature).

Read Uncommitted

On specifying this Isolation level for a transaction, the transaction reads data which may have been modified by another transaction but has not been committed yet. This could lead to dirty reads, in case the transaction modifying the data rolls back the update.

Not sure when exactly this type of Isolation level is used but it is there.

Read Committed

This is the default isolation level for all transactions in SQL SERVER (atleast till 2005).

This isolation level ensures that there are no dirty reads i.e. if a record/data is being updated by another transaction then the Transaction with Read Committed will be blocked till the previous transaction commits the data.

The advantages with this Isolation level are that there are no dirty reads and the concurrency high, since once the data is read lock is released and other transactions can use the same resource/data.

The disadvantageous are that since only shared locks are obtained while reading the data and which too are released once the data is read, there can be lost updates i.e. Tx1 can read a data and after some time update it. And in meantime if Tx2 too reads the same data and updates it then only the last update will be retained, others would be lost.

Read Repeatable

We can solve the problem of lost updates and make our reads repeatable with Read Repeatable Isolation level.

This Isolation level ensures that the data read in a transaction with this Isolation level cannot be changed until the transaction is complete.

Voila! Seems like all issues resolved.. not really. Well updates will be restricted on the data being referred or used by this transaction but Inserts matching the criteria of the data being used in the tx can still happen. This is called as Phantom reads.

Serializable

This is the stringiest of all the Isolation levels i.e. Phantom reads too will not occur; no Inserts matching the criteria of the data being used in the tx with this Isolation level can happen.

Yes all issues in above 3 txs are resolved and consistency is ensured fully but a price of very low concurrency.

Read Committed Snapshot (Since SQL Server 2005)

This and the below one are 2 new Isolation levels introduced in SQL SERVER 2005 and more optimistic in nature, with the goal of maximizing concurrency along with data consistency. These 2 Isolation levels use a technique called as Row Versioning.

Read Committed Snapshot Isolation level allows a transaction to read the original value of a record/data that may have been updated by another txn but not yet committed. But if Txn2 ties to read the values, which have been updated by Txn1, it (Txn2) will be shown the updated values.

In fact the Default Isolation Level has been changed from Read Committed to Read Committed Snapshot.

Snapshot (Since SQL Server 2005)

This Isolation level is same as Read Committed Snapshot except with the difference that Txn2 will see the original copy of the data throughout its life, even if midway Txn1 updates and commits it.

Even if inserts are made matching the criteria of data being read in TXN2, they will never be visible to TXN2 ever.

This ISOLATION LEVEL also prevents lost updates i.e. if a txn2 reads the original value, which is being updated by txn1 and later if txn2 tries to update the values that have been modified by txn1, an error will be thrown to txn2.

Locks

In order to operate at the desired Isolation levels, the transactions will need to acquire certain locks. Below are various types of locks that are generally acquired:

Sharing: This type of lock is acquired generally during reads. Multiple Shared locks can be acquired by various transactions. And during the time these locks are ON, exclusive locks would have to wait till all shared locks are released.

Exclusive: This lock is generally used during updates and no other locks are allowed on the data holding this lock, until it is released.

Intent: This lock specifies that there is an intent to acquire Exclusive lock on a data already having Shared lock and thus disallowing any other further Shared locks, till the Exclusive lock is obtained on the data.

Schema: This lock is used by the DB and compiling queries to ensure that the Schema is not modified when these locks are ON.

	Dirty Reads	Lost-Updates/ Nonrepeatable reads	Phantom reads	Concurrency model	Conflict Detection
Read Uncommitted	Yes	Yes	Yes	Pessimistic	No
Read Committed	No	Yes	Yes	Pessimistic	No
Repeatable Read	No	No	Yes	Pessimistic	No
Serializable	No	No	No	Pessimistic	No
Snapshot	No	No	No	Optimistic	Yes
Read Committed Snapshot	No	Yes	Yes	Optimistic	No