UPDLOCK Table Hint in SQL Server

The updlock table hint in SQL server is used to enforce update locks on the rows or tables being read. This hint ensures that the data being read cannot be modified by other transaction until the current transaction is complete. It commonly uses to prevent lost update scenarios where our intent to update the record after reading the data.

Update query acquires update lock on the rows or tables being read. Update locks are compatible with shared lock but not with other update locks or exclusive lock.

Prevent lost update

By using UPDLOCK we ensure that no other transaction can modify the data until our transaction is completed. This is useful when we plan to read data and then update it within the same transaction.

It does not block other reads but prevent update by other transaction.

Risk of Using UPDLOCK

Blocking: Other transaction that attempt to acquire update or exclusive lock on the same data will be blocked until the current transaction completes.

Deadlock: Improper use of UPDLOCK can lead to deadlock if multiple transaction lock resource in different order.

 Let’s see the example of UPDLOCK.

We have below table

create table Product_Updlock ( Product_Id int, Quantity int, Status_Cd varchar(20) )  insert into Product_Updlock(Product_Id,Quantity,Status_Cd) values (1,5,'Available'); insert into Product_Updlock(Product_Id,Quantity,Status_Cd) values (2,10,'Available'); insert into Product_Updlock(Product_Id,Quantity,Status_Cd) values (3,15,'Available'); insert into Product_Updlock(Product_Id,Quantity,Status_Cd) values (4,20,'Available'); insert into Product_Updlock(Product_Id,Quantity,Status_Cd) values (5,50,'Available');  select * from Product_Updlock

  

Now we want to update the Quantity and status. Before update we need to check that quantity is greater than 0 then we will update this record.

Begin Tran declare @qt int  set @qt=(select Quantity from Product_Updlock   where Product_Id=1)  waitfor delay '00:00:10';  if @qt>0 begin update Product_Updlock set Quantity=Quantity+1 where Product_Id=1; end;

Record update still we do not commit this transaction.

Running the select on the different window

Still running.

Now we are using the UPDLOCL on the table.

Running this query.

See transaction is still executing and we are getting the result into the another query. Open transition is not yet committed.

Let’s commit this transaction.

Transaction is committed successfully.

Run the query on other window and see the result.

Now we are getting the updated result.

The updlock ensure that no other transaction update the same row until this one committed. It Prevent the lost update problem where two users check availability of the same time and both try to update.