TRANSACTION ISOLATION LEVELS IN SQL SERVER

Transaction isolation level controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. It works between transactions and stays till the session is opened. Only one of the isolation level options can be set at a time. If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. Default transaction isolation level is READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

::Test Script::

READ COMMITTED:

  • This is the default level of isolation level in SQL Server.
  • This prevents dirty reads.
  • Cannot read data that has been modified but not committed yet by the transactions. Once committed or rolled back, the data will be fetched by SELECT statement.
  •  The second query will not return any result until the previous transaction is committed or rolled back.ISOLATION_LEVEL_COMITTED

READ UNCOMMITTED:

  • This is the lower level isolation level, it does not put any type of lock while fetching data.
  • NOLOCK and READ UNCOMITTED serves the same functionality. Dirty read happens, means uncommitted data gets fetched out.

ISOLATION_LEVEL_UNCOMITTED

REPEATABLE READ:

  • It functions almost similar to READ COMMITTED isolation level and prevents dirty reads too.
  • Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes.

ISOLATION_LEVEL_REPEATABLE_READ

  • Run both the queries on different different windows, first execute the SELECT one, then immediately execute the INSERT one while the first is being executed. Check the result set returned by the first query now, Both SELECTs in a single transaction returned different values even if being in a single transaction, the second’s record set returned with the new records where the first table set having older records. This new row visible in the second record set known as Phantom record or read.

SERIALIZABLE :

  • This isolation level takes Repeatable Read and puts range locks to make sure that no new data will be added during the transaction to avoid the chance of getting Phantom Reads.
  • No other transactions can modify data that has been read by the current transaction until the current transaction completes.

ISOLATION_LEVEL_SERIALIZABLE

  • Now what happened here is, the lock of fist transaction stopped the insert statement till it reaches to the end of its own batch, the second query executed after successful execution of first query. that’s why we don’t see any mismatch between two SELECT list.

SNAPSHOT:

  • Using snapshot isolation doesn’t block other queries from inserting or updating the data done by snapshot transactions.
  • Row versioning is used, so when data is changed, the older version will be kept in tempdb so that existing transactions can be able to see the older version without reading uncommitted data.
  • When all transactions that started before the changes are complete, then the previous row version will be removed from tempdb. This means that, even if another transaction has made changes, you will always get the same results as you did the first time in that transaction.

ISOLATION_LEVEL_SNAPSHOT

  • As you can see, we tried to update the FirstName of studentID 1005 in a transaction which we kept open. Then we executed the SELECT statement for the same studentID in another window and found the previous FirstName instead of updated one because of row versioning, which is being maintained in tempdb.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: