1. Introduction to Transaction Isolation

In SQL Server, ‘Transaction Isolation Level’ maintains the data Integrity so that all users of the data are in Sync. This is very useful when multiple users access the same data at the same time. The access can be data read or update. In the example we will know about READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE Transaction Isolations.

SQL 2005 is a relational database model and multiple users can access the data that resides in the server box. Let us think there is an organization conducting a survey by taking help from their employees. Say the question is a kind of “Do you like chocolate or Ice-cream?”. Let us say that the cumulative outcome of their reply is updated in a single row. Now, when two employees from client survey form try to update the same row (A row has chocolate or Ice-cream column with a count of how many people likes it), does SQL Server discard an update from one person?

We will take one more example. There is a big stock market database maintained by ‘ABC’ organization. For Example, A client web application ‘App-A’ is retrieving a row from this database as an inquiry to know how a business stands in the stock market. But let us again assume when a row is read by ‘App-A’; an update is done on the same row by some other application say ‘App-B’. Now in ‘App-A’ perspective, some columns of the row have older values and some columns of the row has the updated values.

SQL Server tackles the above situation by using the ‘Transaction Isolation’. We will look at the different Transaction Isolation and its use in this article.

2. Shared Lock & Exclusive Lock

When reading or writing the data, SQL Server applies a lock on an affected row. The locks are two types. One is ‘Shared Lock’ and other one is ‘Exclusive Lock’. A shared lock is used by the read operation and an exclusive lock is gained by the update operation. When an update puts an exclusive lock, read operation won’t happen on that row and read operation should wait till the update completes.

Have a look at the below picture:

2.1 Updated Operation & Exclusive Lock

The first part of picture shows us an update of row in-process and it is not yet done. The Update operation applied an Exclusive Lock before starting the actual update of the required row. And a read action along the way is waiting, as it cannot able to gain the Shared Lock on that row. The Exclusive Lock not only blocks the read operation, it will also block next coming update operation on the same row. It means only one update operation can have the exclusive lock on a specific row.

2.2 Shared Lock on Read Operation

The second part shows that the update action is done, and SQL Server releases the Exclusive Lock. At this stage either a next waiting update can get an Exclusive Lock or a read operation can take a Shared Lock on the row. In our depiction, a read operation took a Shared Lock and started reading the data. In the Default Isolation Level (We will discuss the remaining later), an Exclusive Lock request by an update operation is not allowed when the row is already got a Shared Lock. Then why we call it a Shared Lock?

2.3 Multiple Read on Shared Lock

Now look at the third portion of the picture. There are two more read operations going on in the same row now. It shows that all three read operation shared a single lock, and that’s why we call it as Shared Lock. SQL Server shares the Shared Lock for the Read operations and stops the updates when the lock is ON.

3. READ COMMITTED Transaction Isolation Level

‘Read Committed’ is the default Transaction Isolation Level of SQL server 2005. It will allow only one update on a row at a time and during that the read also prohibited. Have a look at the below two queries:

With these two queries we can examine the default transaction isolation called ‘Read Committed’. Here, we are using a sample table called student in the above two queries. One can use NorthWnd or another demo database they have on their machine. We run the update query in one machine or in one query window and the select query in a different machine or in a separate query window.

Testing the READ COMMITTED Isolation

Now let us go to the first SQL statement, which is nothing but a simple update on the Student sample table. The Begin Transaction says that we need to do manipulation or retrieval under the Transaction Isolation rules. The update statement within the Begin Transaction and Commit Transaction claims the Exclusive Lock on the student row with student id 103. When we execute the query shown in the red box, that specific row is in locked (Exclusively) state and nobody can touch it except the one who applied that Exclusive Lock. Do not execute the Commit Transaction now, instead go to the other machine (or other Console management studio on your machine) and execute the select * from student. What happens? The read operation waits to gain the Shared Lock when the read scan reaches the student 103 row.

Since we does not execute Commit Transaction, SQL thinks student 103 is still a half updated ‘Dirty Row’ and keeps the Exclusive Lock on it. Select statement waits to pick up the Shared Lock on the exclusively locked row. One can see that other query window waits and waits and waits, not showing the result of the select statement. Now, we can execute the Commit Transaction statement to see the output of the select statement immediately in another window. The Commit Transaction here adhere to the default Read Committed Transaction Isolation Level.

4. Other Three Transaction Isolation Levels

The other three Transaction Isolation Levels are below:

1. READ UNCOMMITTED
2. REPEATABLE READ
3. SERIALIZABLE

Before we go into details of each one, first have a look at the below SQL Statement that will change the Default Read Committed Isolation level to any of the above one. The below example sets ‘Read UnCommitted’ Transaction Isolation Level to SQL Server.

The syntax is a simple Set statement. People usually choose the required Isolation Level, then execute the SQL statements with Begin Transaction  and Commit. After the Batch Execution, they reset the Isolation level back to the default. OK. Now let us go to each one of this Isolation one-by-one.

4.1 Read UNCOMMITTED Transaction Isolation

The ‘Read Uncommitted’ transaction Isolation level does not prevent read and the write operation. This means, when a row is half updated and still in progress, one can perform a read operation ( Select  statement) on that row. We call this as a ‘Dirty Read’ or reading an ‘Uncommitted Data’. Now we will examine the T-SQL statements below:

Query Window 1:

Query Window 2:

In both the T-SQL above (One Select and One Update), we set the ‘Transaction Isolation Level’ to ‘ Read UnCommitted’ using the Set Transaction. This means we request that we want to read data with no delays, and at the same time we don’t care about the Dirty Data. Now we execute the statement till update in the query window 1, and delay executing the Commit statement. After this we will execute all the statements in the Query window 2. What happens? No delay in retrieving the set of records, right? Even though the update in the query window 1 is not committed yet, we are still seeing the updated result in the query window 2. So, what we see here is a ‘Dirty Data’ or UnCommitted Data. Now execute the Commit statement in Query Window 1.

4.2 Repeatable Read Transaction Isolation

The ‘Repeatable Read’ Isolation Level will make sure to lock the retrieved rows for any further updates. In the meantime, it will allow inserts on the Table and read on the locked rows. Here SQL Server applies the lock to all the retrieved rows marked in the transaction. This Repeatable Read Transaction Isolation makes sure of “Inserts? OK, Proceed, Updates? Please Wait” on the locked set of Rows.  Now look at the below two Queries:

Query Window 1:

Query Window 2:

As we did in the previous sections, we can execute the query till the select statement in the query window 1. Then execute the SQL Statements one-by-one from query window 2. We see that SQL Server executes the select and two inserts. But the update statement waits, and you can see that output window does not confirm the ‘Update Succeeded’. Once we execute the Commit Transaction in the Query Window 1, the update in query window 2 proceeds Promoting the Student 108 to 4th standard of class.

Look at the name of the Transaction Isolation. It is Repeatable Read, means that we will have multiple reads in the same table and going to fetch two or three or more number of records and we want to make sure nothing got changed in the retrieved rows until we mark my transaction as finished either by Commit transaction or Roll back transaction. Now look at below two statements:

Query Window 1:

Query Window 2:

In the above transaction, at Query Windows 2, the first update statement gets executed and next one gets blocked until the Transaction in the Query window 1 is committed. Because we locked only the rows with studid = 108 in the Query Window 1 as part of the Repeatable Read Isolation.

4.3 Serializable Transaction Isolation

The ‘Serializable Isolation’ also performs a lock and but the lock is a ‘Table Level Lock’. Here, the table blocks any insert and update on it. That means the entire table is locked for any changes. No Inserts. No Updates. One can check out this by taking the Set of queries shown in the early examples. Before trying the example, do not forget to change the Isolation Level to serializable as shown below:

Note that the Isolation allows Select statements on the table.

5. Summary

In this example, first we studies what is Shared and Exclusive locks. The we studied 4 types of Transaction Isolation Levels supported by SQL Server. Once you tried all the examples, reset Isolation Level to default one.

• MFC CRgn Example – An Elliptical Dialog
• MaskedEdit Control in C# Explained

Categories: SQL Server

Tags: Exclusive Lock, Read Committed, Read UnCommitted, Repeatable Read, Shared Lock, Table Level Lock

This site uses Akismet to reduce spam. Learn how your comment data is processed.