Difference Between Shared Lock, Exclusive Lock And Update Lock In SQL Sever

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: