Update (U) Lock in SQL Server

An Update Lock (U Lock) is a special lock used by SQL Server during UPDATE operations before converting to an Exclusive Lock. It prevents a common deadlock scenario where multiple sessions read the same row using Shared Locks and later attempt to convert those locks into Exclusive Locks simultaneously. Only one Update Lock can exist on a resource at a time.

Imagine there is only one bathroom in an office.

There are three people:

Ø  Rahul → Just wants to look inside (Read)

Ø  Amit → Wants to use the bathroom if it's free (Update)

Ø  Suresh → Also wants to use the bathroom (Update)

Scenario Without Update Lock

Step 1: Amit opens the bathroom door and checks if it's free. (SQL Server reads the row before updating it.)

Step 2: At the same time, Suresh also checks if it's free. (Both sessions read the same row.)

Step 3: Now both decide, "Bathroom is free. I'll use it." Both try to lock the bathroom simultaneously. This creates a conflict. In SQL Server, this situation can lead to deadlocks.

How Update Lock Solves This

Step 1: Amit arrives first. Instead of just looking, he hangs a sign "I am checking this bathroom and may use it." This sign is the Update Lock (U Lock).

Step 2: Suresh arrives. He sees the sign. He understands "Someone is already evaluating this bathroom for use. I'll wait."  So he waits.

Step 3: Amit decides to use the bathroom. The sign changes from Update Lock (U) to Exclusive Lock (X). meaning "Bathroom is now occupied."

Step 4: After Amit finishes, the lock is released. Now Suresh can proceed.

Bathroom Example	SQL Server
Looking inside	Read row
Sign "I may use this"	Update Lock (U)
Actually, using bathroom	Exclusive Lock (X)
Others waiting	Blocking
Preventing two people from deciding simultaneously	Preventing Deadlock

Update Lock = "Bathroom Reserved, May Occupy Soon" Sign.

When we are updating the records

UPDATE Employee  SET Salary = 70000  WHERE EmployeeID = 1;

SQL Server internally performs the following.

Step 1: Find Row

Step 2: Acquire Update Lock (U)

Step 3: Prepare Modification

Step 4: Convert U → X

Step 5: Update Row

Step 6: Commit

Step 7: Release Lock

Let’s see the demo

session 1

We are select a row

BEGIN TRAN; SELECT * FROM Employee WITH (UPDLOCK) WHERE EmployeeID = 1; --commit

Transaction is not yet committed. This acquired U lock.

SELECT     request_session_id,     resource_type,     request_mode,     request_status FROM sys.dm_tran_locks

 

Session 2

Running the below query

SELECT * FROM Employee WITH (UPDLOCK) WHERE EmployeeID = 1;

 

It is blocked because U + U = NOT Compatible.

See here

Session id 53 is in wait status. This is exactly how SQL Server prevents update deadlocks.

Lock Compatibility Matrix

Existing Lock	S	U	X
S	✔	✔	✘
U	✔	✘	✘
X	✘	✘	✘

Only one Update Lock can exist on a resource.

Ø  Shared Lock: Many readers allowed

Ø  Update Lock: One future writer reserved

Ø  Exclusive Lock: Actual writer working

Updating the record

UPDATE Employee SET Salary = 70000 WHERE EmployeeID = 1;

This is also waiting.

Once we commit the open transaction it will release.  Now committing the open transaction.

Lock has released. Process completed.