Microsoft® SQL Server 2008 R2 Unleashed

Update Locks

Update locks are used to lock rows or pages that a user process intends to modify. When a transaction tries to update a row, it must first read the row to ensure that it is modifying the appropriate record. If the transaction were to put a shared lock on the resource initially, it would eventually need to get an exclusive lock on the resource to modify the record and prevent any other transaction from modifying the same record. The problem is that this could lead to deadlocks in an environment in which multiple transactions are trying to modify data on the same resource at the same time. Figure 37.5 demonstrates how deadlocks can occur if lock conversion takes place from shared locks to exclusive locks. When both processes attempt ...