Sunday, June 4, 2017

Understanding Isolation Levels



Understanding isolation levels in SQL server –

 * How to check current isolation level –
Query: dbcc useroptions
There are five types of isolation levels in SQL server those are:
Isolation Level
Dirty Read
Non Repeatable Read
Phantom
Read uncommitted
Yes
Yes
Yes
Read committed
No
Yes
Yes
Repeatable read
No
No
Yes
Snapshot
No
No
No
Serializable
No
No
No

* DIRTY READS: Reading uncommitted modifications are call Dirty Reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction, thus getting you incorrect or wrong data.
This happens at READ UNCOMMITTED transaction isolation level, the lowest level. Here transactions running do not issue SHARED locks to prevent other transactions from modifying data read by the current transaction. This also do not prevent from reading rows that have been modified but not yet committed by other transactions.
To prevent Dirty Reads, READ COMMITTED or SNAPSHOT isolation level should be used.
 
* PHANTOM READS: Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get different number of rows by firing same query in current transaction.
In REPEATABLE READ isolation levels Shared locks are acquired. This prevents data modification when other transaction is reading the rows and also prevents data read when other transaction are modifying the rows. But this does not stop INSERT operation which can add records to a table getting modified or read on another transaction. This leads to PHANTOM reads.
PHANTOM reads can be prevented by using SERIALIZABLE isolation level, the highest level. This level acquires RANGE locks thus preventing READ, Modification and INSERT operation on other transaction until the first transaction gets completed.

Read Committed
In select query it will take only commited values of table. If any transaction is opened and incompleted on table in others sessions then select query will wait till no transactions are pending on same table.
Read Committed is the default transaction isolation level.
If the transaction is not using update or delete commands then there won’t be any lock.
Read Uncommitted
If any table is updated (insert or update or delete) under a transaction and same transaction is not completed that is not committed or roll backed then uncommitted values will display (Dirty Read) in select query of "Read Uncommitted" isolation transaction sessions. There won't be any delay in select query execution because this transaction level does not wait for committed values on table.

Query: set transaction isolation level read committed
If you want to maintain Isolation level "Read Committed" but you want dirty read values for specific tables then use with(nolock) in select query
Repeatable Read
Select query data of table that is used under transaction of isolation level "Repeatable Read" can not be modified from any other sessions till transaction is complete.
This isolation level allows to insert new data but does not allow to modify data that is used in select query executed in transaction.

Serializable
Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on range lock. If table has index then it locks records based on index range used in WHERE clause(like where ID between 1 and 3). If table doesn't have index then it locks complete table.

Snapshot
Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold lock on table during the transaction so table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data in case of any data modification occurs in other sessions then existing transaction displays the old data from Tempdb.
References:

 http://www.besttechtools.com/articles/article/sql-server-isolation-levels-by-example



Share this:

0 comments:

Post a Comment