Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Advanced
Jul 7, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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'
Jai Bardhan
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date