Transaction Isolation - Part 3 [Read Committed]

READ COMMITTED 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:

 

              

 

The queries are useful to explain this transaction lock on this default isolation levels. I used a sample table, you can use NorthWnd or demo database that you have on your machine. The queries I run at two different client machines (You can run it on the same machine with Different Console window by opening Management console twice).

 

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 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.