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.