Transaction Isolation - Part 2 [Shared Lock. Exclusive Lock]

When reading or writing the data SQL Server applies the lock on an affected row. The locks are two types. One is shared lock 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:

 

The first portion of picture shows you an update in progress and it is not yet completed. The update operation applied an exclusive lock before starting the actual update 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.

 

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 excusive 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 it is called shared lock. Well.

 

Now look at the third portion of the picture. There are three more read operations going on in the same row. It shows that all three read operation shared a single lock and that's why we call it as Shared Lock.