Most often I have come across these locks while experiencing
deadlocks. And there had to be a lot of time to understand what is happening exactly at the row-level while fetching the data. Let’s look at the concept
and difference between a Shared Lock, an Exclusive Lock and an Update Lock in
SQL Server.
Shared Lock (S)
A Shared Lock is basically a read-only lock for a row-level.
Any number of resources can fetch the data to read when the shared lock is
present on the resource. That means that many process IDs can have a shared
lock on the same resource to read the respective data.
Exclusive Lock (X)
The Exclusive Lock is used and valid on a single transaction,
that locks either row or a page depending on the data. The mechanism for
understanding is simple, where an exclusive lock can be applied only on a
single resource. There cannot be more than one exclusive lock on the same
resource. Either Insert, Update or Delete commands happen over with the Exclusive
lock, and these commands will not be in effect until the exclusive lock is released
from the resource.
Update Lock (U)
An Update Lock is used and valid when there is a shared lock
applicable for a resource. In other words, the update lock cannot be placed
until there are no other offending exclusive or update locks for the fetching
resource. Additionally, the update lock happens to be acquiring an exclusive when
all other locks are released from a resource.
You can find these interview topics interesting too:
