Transaction Isolation levels.

What is Read Uncommitted Isolation Level?

The Read Uncommitted Isolation level permits you to read uncommitted data.
The transaction is completed or not it doesn’t matter because It has never issued the share locks and it allows other transactions to modify data that you are reading.

Anytime you can read your data without any locking issues, but sometimes it generates unexpected results because the data returned by the SELECT are kind of dirty data or in a half state only.

Below is a small demonstration to read uncommitted data.

Now test this READ UNCOMMITTED isolation level:

First, create a table with sample data:

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE tbl_Employee ( EmpID INTEGER ,EmpName VARCHAR(50)) GO INSERT INTO tbl_Employee VALUES (1,'Anvesh'),(2,'Neevan'), (3,'Roy'),(4,'Martin') GO

Open a new query window or session and executing this script:

1 2 3 4

BEGIN TRANSACTION UPDATE tbl_Employee SET EmpName ='Jenny' WHERE EmpID=1 WAITFOR DELAY '00:00:15'   COMMIT

During this 15 second delay, Open a new query window or session and try to SELECT this table under READ UNCOMMITED isolation level:

1 2	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   SELECT *FROM tbl_Employee

The result is, You can SELECT Uncommitted data.

What is SERIALIZABLE Isolation level?

THE SERIALIZABLE Isolation level is one kind of extended version of the REPEATABLE READ Isolation level of the SQL Server.

In REPEATABLE READ you get the same result by executing same SELECT statement because during your reading operation other cannot update the data, but others can insert the new records.

Now with the SERIALIZABLE Isolation level, you cannot modify the data while another transaction is reading the same data.You can not even read uncommitted data.

Other transactions cannot insert new rows with the key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

The lock also works based on key value range,
For example, you are selecting your data between ID 200 and ID 500 so all this data has been locked and another transaction cannot perform INSERT or UPDATE in this range data until the completion of the reading transaction.

Now test this REPEATABLE READ isolation level:

First, create a table with sample data:

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE tbl_Employee ( EmpID INTEGER PRIMARY KEY ,EmpName VARCHAR(50) ) GO INSERT INTO tbl_Employee VALUES (1,'Anvesh'),(2,'Neevan') ,(3,'Roy'),(5,'Martin') GO

Open a new query window or session and executing this script:

1 2 3 4 5 6 7

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE   BEGIN TRAN   SELECT *FROM tbl_Employee WHERE EmpID BETWEEN 2 AND 5 WAITFOR DELAY '00:00:15' ROLLBACK

During this 15 second delay, Open a new query window or session and try to UPDATE data which are in key range:

1	UPDATE tbl_Employee SET EmpName ='Loother' WHERE EmpID=2

The result is, You cannot UPDATE the data between ID 2 and ID 5.

During this 15 second delay, Open a new query window or session and try to INSERT a record which is in key range:

1 2 3

INSERT INTO tbl_Employee VALUES (4,'Henny') GO

The result is, You cannot INSERT the data with ID 4 because it is between ID 2 and ID 5.

Important Note: here, The Primary Key is mandatory on the ID column because without primary key it locks the whole table so range value should be a Primary Key.

READ COMMITTED Isolation Level with the READ_COMMITTED_SNAPSHOT option

The READ COMMITTED is the default isolation level of SQL Server and it prevents the dirty reads.
Your SELECT statements always returns committed data.

It issues shared lock against the data where data are updating or having an exclusive lock so for selecting those data you have to wait to complete that transaction.

Now test this READ COMMITTED isolation level:

First, create a table with sample data:

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE tbl_Employee ( EmpID INTEGER ,EmpName VARCHAR(50) ) GO INSERT INTO tbl_Employee VALUES (1,'Anvesh'),(2,'Neevan') ,(3,'Roy'),(4,'Martin') GO

Open a new query window or session and executing this script:

1 2 3 4

BEGIN TRANSACTION UPDATE tbl_Employee SET EmpName ='Jenny' WHERE EmpID=1 WAITFOR DELAY '00:00:15'   COMMIT

During this 15 second delay, Open a new query window or session and try to SELECT this table:

1	SELECT *FROM tbl_Employee

The result is, you can not SELECT your data until the UPDATE is not completed. As we have set a 15 second delay, so you have to wait for 15 second.

What is READ_COMMITTED_SNAPSHOT option?

This is very important and READ COMMITTED isolation level depends on this option ON / OFF setting.
It is by default OFF.

ALTER command to switch it ON / OFF:

1 2 3 4 5

-- Switch to ON ALTER DATABASE Database_Name SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE -- Switch to OFF ALTER DATABASE Database_Name SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE

READ_COMMITTED_SNAPSHOT OFF:

This is the default setting and issues the shared locks to prevent other transaction when we are reading or updating a table data.
Above small demonstration represents the behaviour of this default setting.

READ_COMMITTED_SNAPSHOT ON:

When the READ_COMMITTED_SNAPSHOT database option is ON, read committed isolation uses row versioning.

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. When data is updating, you can still read old version of data and there is no any share lock, but you cannot update that same data until the running update is not finished.

This is very different than Snapshot Isolation level.
It consumes less tempdb space than snapshot isolation.
In the Snapshot Isolation level, the same data causes an update conflict because same version of the row can be updated by two different transactions.

Now test the READ_COMMITTED_SNAPSHOT ON:

ALTER command to ON a READ_COMMITTED_SNAPSHOT:

1	ALTER DATABASE Database_Name SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

Open a new query window or session and executing this script:

1 2 3 4

BEGIN TRANSACTION UPDATE tbl_Employee SET EmpName ='Jenny' WHERE EmpID=1 WAITFOR DELAY '00:00:15'   COMMIT

During this 15 second delay, Open a new query window or session and try to SELECT this table:

1	SELECT *FROM tbl_Employee

The result is, You can SELECT an old version of the data.

During this 15 second delay, Open a new query window or session and try to UPDATE same row:

1	UPDATE tbl_Employee SET EmpName ='Jekvlin' WHERE EmpID=1

The result is, You cannot perform an UPDATE on the same data.

REPEATABLE READ allows you to read the same data repeatedly and it makes sure that any transaction cannot update this data until you complete your reading.

If you are selecting same row twice in a transaction, you will get the same results both times.

If someone are modifying the data, you can not even read those data until they complete the update.
This is very similar to READ COMMITTED Isolation level.

What is REPEATABLE READ Isolation Level?

The REPEATABLE READ issues the shared locks on all statements which are in the transaction.
The shared locks do not release lock at each statement level, but it applies at transaction level.

The REPEATABLE READ does not stop insertion of newer records so when we are reading data with this isolation level, there is a chance to get Phantom or Dirty Reads.

For example,
You are selecting range data between ID 200 and ID 500 and in this range we have one free ID which is 394.

Now user is going to insert a new record with ID 394 and you are selecting data from multiple source so there are chances to get different results for your both SELECT statements.
Like one data set with ID 394 and other data set may be without ID 394.