February 3, 2012

SQL 2005 - Understanding Shared Lock and Exclusive Locks in Transaction Isolation

1. Introduction

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 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 (The client application they use for the survey) 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?

Let us take one more example. There is big stock market database maintained by "Abc" organization. A client web application say "App-A"  is retrieving a row as an enquiry from this database to know how a particular 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 the situation leads some columns of the row has older values and some columns has the updated values in "App-A" perspective.

SQL Server tackles the above situation by using the "Transaction Isolation". We will look at the different Transaction Isolations 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 acquired by the update operation. When a update puts a 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:

Shared and Exclusive locks
Fig 1. Shared and Exclusive Lock

2.1 Exclusive lock by an Update

The first portion of picture shows you an update of row in progress and it is not yet completed. The Update operation applied an exclusive lock before starting the actual update of the required row in discussion. And a read operation along the way is waiting, as it cannot able to acquire the shared lock on that row. The Excusive lock not only blocks the read operation, it will block next coming update operation (On the same row) also. That means, only one update operation can have the exclusive lock on a particular row.

2.2  Read v.s Shared Lock

The second portion shows that the update operation is completed and the exclusive lock is removed. At this stage either an next waiting update can acquire a exclusive lock or a read operation can acquire a shared lock on the row. In our depiction, a read operation acquired 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 permitted when the row is already got a shared lock. Then why we call it as shared lock? Well.

2.3 Multiple Reads

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. Simply, the lock is shared for the Read operations and Update is still not allowed when the lock is ON.


This is the default transaction 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:

Read Committed Isolation
Fig 2. Read Committed Isolation
Fig 3. Select from Student table
With these two queries we can examine the default transaction called "Read Committed". I used a sample table called student in the above two queries. You can use NorthWnd or other demo database that you have on your machine. The update query in Fig.2 and the select query in Fig.3 can be run at two different machines.

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 enclosed within the Begin Transaction and commit transaction applies the exclusive lock on the student row with student id 103. When you execute the query shown in red box, that particular row is in locked (Exclusively) state and nobody can touch it except the one who applied that Exclusive (red) lock. Do not execute the commit transaction now, in stead go to the other machine (Or other Console management studio on you machine) and execute the select * from student. What happens? The read operation waits to acquire the transaction lock (Shared one) when the scanning came to the student 103 row. Now the situation is:

Since commit transaction is not executed SQL thinks student 103 is still a dirty row (Half updated) and keeps the exclusive lock on it. Select statement waits to acquire to shared lock on the exclusively locked row. You can see that other query window waits and waits and waits not showing the result of the select statement. Now, go ahead and execute the commit transaction statement. You will see the output of the select statement.

4. Other three Isolation

The other three isolation levels are listed below:


Before we go into details of each one, first have look at the below SQL Statement that will change the Default Read Committed Isolation level to any of the above one.

Set transaction Isolation level Read UnCommitted;

The syntax is a simple Set statement. People usually decide the Isolation Levels, then execute the SQL statements with "Begin Transaction" and "Commit". After the execution of the batch, they reset the Isolation level back to the default. OK. Now let us go to the each one of these Isolation one-by-one.


The "Read Uncommitted" transaction Isolation level does not prevent read as well as the Write operation. This means that when a row is half updated and still in progress and read operation (Select statement) can still proceed there. We call this as "Dirty Read" or "Uncommitted data". Look at the T-SQL statements below:

Query Window 1:

Set transaction Isolation level Read UnCommitted;
Begin Transaction
Update student set class = 7 where studid = 103;

Query Window 2:

Set transaction Isolation level Read UnCommitted;
Begin Transaction;
      Select * from student;

In both the SQL transaction above (One Select and One Update), we set the transaction isolation level to UnCommitted using the "Set transaction". This means, we are requesting that we want to read data without any delays and at the same time we don’t care about the dirty data. Now execute the statement till update in the query window 1, delaying the execution of commit (Do not execute that now). Now execute the all the statements in the Query window 2. What happens? No delay on retrieving the set of records right? And see, you got Hansika as 7th STD girl. Even though the update in the query window 1 is not committed we are still seeing the updated result in the query window2. Now execute the commit statement in Query Window 1. 

4.2 Repeatable Read

The "Repeatable Read" isolation level will make sure to lock the retrieved rows from any further update. In the mean time, it will allow inserts as well as read on the table. Here the lock is applied to all the retrieved rows marked in the transaction. This transaction isolation make sure of “inserts? OK Proceed, Updates? Please Wait” on the locked set of Rows.  Now look at the below two Queries:

Query Window 1:

Select * from student;
Commit Transaction;

Query Window 2:

Select * from student where Studid = 108;
Insert into student values(110, 'Ramesh', 4);
Insert into student values(111, 'Rakesh', 2);
update student set class = 4 where studid = 108;

As you did in the previous sections, Execute the query till the select statement in the query window 1. Then execute the SQL Statements one-by-one from query window 2. You will see that select and couple of inserts are executed. But, the update statement waits and once you execute the commit transaction on 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 Isolation. Repeatable read, means that I will have multiple reads in the same table and going to fetch two or three or more number of records and I want to make sure nothing got changed in the retrieved rows until I mark my transaction as finished either by Commit transaction or Roll back transaction. Now look at below two statements:

Query Window 1:

Select * from student where studid = 108;
Commit Transaction;

Query Window 2:

update student set class = 3 where studid = 101;
update student set class = 4 where studid = 108; 

In the above transaction, the first update statement gets executed and next one gets blocked until the Transaction in the Query window 1 is committed. Because, we blocked only the rows with stuid = 108 in the Query Window 1 Transaction.

4.3 Serializable

The "Serializable Isolation" also performs a lock and but the lock is a "Table Level Lock" and the table prohibits any insert and update on it. That means the entire table is completely locked for any changes. No Inserts. No Updates. You can examine this by taking the Set of queries shown in the previous example. Make sure to change the Isolation level to serializable as shown below:


Note that the Isolation allows Select statements on the table. That is all in this article. 

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Friend :)