Understanding Isolation levels in SQL Server

A transaction is a group of T-SQL statements performing single or multiple operations on objects as a single unit. If any of the statements in the transaction fails, then the whole operation reverts due to transaction rollback. This assures that those multiple statements in the transaction work as a single unit and either commit as a single unit or rollback all the modifications it was performing and return with an error. And we also know that we have 3 types of Transaction Modes namely Auto-commit transactions, Implicit transactions, and Explicit transactions. The integrity of the Transactions and concurrent users is determined by the Isolation level. The Isolation level defines how the Transaction must be isolated from other Transactions and their data modifications. These Isolation levels manage the locking of the resources between Transactions. The isolation levels we will be understanding in this post are

• Read Uncommitted

• Read Committed (The default)

• Repeatable Read

• Serializable

• Snapshot Isolation and its example are already covered in another post links to them are at the end of this post. Before moving forward to Isolation levels one by one, let's create a table with some mock data for some illustrations later.

drop table if exists product_listing;
create table product_listing(
    id int identity(1,1),
    item_name varchar(50) not null,
    description varchar(100),
    unit_price decimal(10,2)
);
insert into product_listing(item_name, unit_price) values ('Compactor', 7145.00);
insert into product_listing(item_name, unit_price) values ('Crawler', 9549.00);
insert into product_listing(item_name, unit_price) values ('Excavator', 39549.00);
insert into product_listing(item_name, unit_price) values ('Scraper', 4883.00);
insert into product_listing(item_name, unit_price) values ('Grader', 9549.00);
insert into product_listing(item_name, unit_price) values ('Trencher', 7347.00);
insert into product_listing(item_name, unit_price) values ('Backhoe', 1275.00);
insert into product_listing(item_name, unit_price) values ('Dragline', 9549.00);
insert into product_listing(item_name, unit_price) values ('Dump Truck', 25241.00);

Now, let's start.

1. Read Uncommitted; Read Uncommitted is the lowest level of isolation, it enables the Transaction to read uncommitted data modifications made by other transactions concurrently on the same resources. The transaction can read the row values even when locks are applied on that row by other transactions and that other transaction has not committed or rollbacked either yet. This case is also known as Dirty Reads. Since the other transaction modifying the rows hasn't yet made a commit or rollback, we may see unexpected results. However, there are cases where we might be required to read uncommitted data.

Run both the below transactions in two separate query editor tabs. In our example, let's try to update the unit_price of the product having id as 1 in the product_listing table. And let's try to read that product id row in another transaction.

Begin Transaction

Update product_listing set unit_price = 10000.00 where id = 1;

waitfor delay '00:00:10';

Rollback Transaction ;

Now in another window tab, try to read the product_listing table for that same id, i.e. 1

set transaction isolation level read uncommitted;

select * from product_listing where id = 1; 

waitfor delay '00:00:10';

In the first transaction, we updated the unit_price of product id 1 of the product_listing table and simulated a delay so we can read the value of product id 1 in the second transaction. On the other query editor screen when executing the second transaction we can see that even though the update statement in the first transaction has an exclusive lock for the row, it's readable by the select statements in the second transaction. The first select statement in the second transaction returns the updated unit_price of the product_id 1 and then waits for the first transaction to roll back, to read again for the same product and find out that the unit_price is changed again and it reverted to the old value. Simply saying the second transaction returned the value that was there at that time. Another syntax for reading uncommitted data is to use the NOLOCK table hint, which then read uncommitted data even if the isolation level is not mentioned in the transaction.

select * from product_listing where id = 1 with(NOLOCK);

1. Read Committed: This is the default isolation level and it guarantees that the return of any data read is the committed value at the time of reading. In simpler terms, the read operation will wait, for any pending transaction on the same table to complete and will return the committed value. The shared lock request on the data will wait for the exclusive lock to commit if any pending in other transactions for the same data. Let's simulate it with example table data.

BEGIN TRANSACTION

Update product_listing set unit_price = 10000.00 where id = 1;

waitfor delay '00:00:10';

ROLLBACK TRANSACTION;

In another query editor window tab, we will read the data for the same id.

select * from product_listing where id = 1;

Executing both transactions in a different window, we can see that the second transaction, even though it's only a read operation, it waits for the first transaction to complete (i.e. either commit or rollback) and on completion of the first transaction, the second transaction can read the data. We have to remember that the read operation will issue shared lock requests against data we want to read, and it will wait if another transaction already has an exclusive lock on that data. If both the transactions working on the same data want only a shared lock, then there won't be any execution delay for the transaction, as they don't need to wait for the other transaction to complete. The transaction holds a read or write lock on the current row, and thus prevents other transactions from reading, updating, or deleting it.

1. Repeatable Read IN THIS ISOLATION LEVEL, every time we read the same record set it returns the same value till the end of the transaction. It blocks the write operation trying to update the same record set we are reading, by holding a shared lock till the transaction is complete. Thus we see the same results within the transaction. It is to note here that, Repeatable Read blocks update and delete operation for the same recordset which obtained the shared lock, but it doesn't block insert operation. If the new records are satisfying the condition of the select statement, it will also return those newly added records along with the previous record set. Let's simulate both cases.

begin transaction;
set transaction isolation level repeatable read;

select * from product_listing where unit_price < 2000; 

waitfor delay '00:00:10';

select * from product_listing where unit_price < 2000;

commit transaction;

In the second transaction in the new window, we will try to update the unit_price of product id 7 to 1900.

-- transaction 2
Update product_listing set unit_price = 1900 where id = 7;

But if we read the data after all the transactions are completed, we can see that unit_price of the product id 7 is updated to 1900.00 This shows us how Repeatable Read Isolation prevents data modification till the end of the transaction. Let's also simulate the second case, to see how the insert operation will work with the isolation level.

begin transaction;
set transaction isolation level repeatable read;

select * from product_listing where unit_price < 2000;

waitfor delay '00:00:10'; 

select * from product_listing where unit_price < 2000; 

commit transaction;

In the second transaction, another window, we will insert another new product having unit_price below 2000.

insert into product_listing(item_name, unit_price) values ('new item 1', 1500.00);

As we can see on both the transaction execution, even though the first transaction has obtained a shared lock on the same read condition, still the second transaction returns immediately without waiting for the first transaction execution to finish. Now when we perform the read operation for the second time, in transaction one after transaction two completes its insertion, we get two rows returned, old records along with new records as it satisfies the WHERE conditions. Do remember that, Repeatable Read doesn't block insert operation, thus leading to phantom read in some cases.

1. Serializable This is similar to the Repeatable Read isolation level with an extra added layer that prevents insert operations also. This eradicates the problem of phantom reads. Serializable Isolation level uses range locks on the record set, so it can't be modified, inserted, or deleted till the end of the transaction. Let's simulate the previous example to see if the insert operations are blocked till the end of the transaction.

begin transaction;
set transaction isolation level serializable;

select * from product_listing where unit_price < 2000;

waitfor delay '00:00:10'; 

select * from product_listing where unit_price < 2000;

commit transaction;

In the second transaction, another window, we will insert another new product having unit_price below 2000.

insert into product_listing(item_name, unit_price) values ('new item 2', 1700.00);

With both transaction execution, we can see that the second transaction which has an insert operation doesn't return immediately like in Repeatable Read Isolation. The second transaction waits till the first transaction is complete and on completion of the first transaction, the second transaction performs the insert operation for adding a new record. This demonstrates the blocking of all DML operations from another transaction, till the current transaction is completed under Serializable Isolation. It is to be noted that, if the table has an index on it, then the records are locked based on those index ranges used in the WHERE clause, otherwise it locks the complete table for the transaction.

This completes our small post of transaction isolation level. For the Snapshot Isolation, kindly look at the below-given links.

• SQL Server Concurrency with Row Versioning