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:
![Pic02a.JPG]()
![Pic02b.JPG]()
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.