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'