Use Update Lock to Escalate a Read Lock to a Write Lock During a Transaction

Use Update Lock to Escalate a Read Lock to a Write Lock During a Transaction

In a high-volume application, there’s a good chance that two separate transactions will both run the SELECT statement and acquire read locks before either can acquire the write lock required for the UPDATE statement. This behavior results in a deadlock. The problem is that the transaction first acquires a read lock on a data item and then tries to convert the lock to a write lock. If two transactions acquire the read lock at the same time, neither party can convert the lock to a write lock.

SQL Server supports another type of lock called an update lock, which solves this problem. Use an update lock whenever you need to escalate a read lock to a write lock during a transaction. An update lock conflicts with write locks and with other update locks. A transaction will block while trying to acquire an update lock if another transaction has already acquired a write lock or an update lock. However, unlike a write lock, an update lock doesn’t conflict with read locks. If a transaction holds a read lock on a data item, another transaction can acquire an update lock. Likewise, if one transaction holds an update lock, other transactions can acquire read locks.

You can explicitly ask for an update lock by using the (UPDLOCK) optimizer_hints in a Transact-SQL SELECT statement. For example:

 SELECT Xfield1 FROM Xtable (UPDLOCK)  WHERE Xfield2= 'XX'
See also  Why ChatGPT Is So Important Today

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist