What Update Locks Do

Guy Glantser explains the process around updating data in SQL Server, particularly the benefit of having update locks:

In order to update a row, SQL Server first needs to find that row, and only then it can perform the update. So every UPDATE operation is actually split into two phases – first read, and then write. During the read phase, the resource is locked for read, and then it is converted to a lock for write. This is better than just locking for write all the way from the beginning, because during the read phase, other sessions might also need to read the resource, and there is no reason to block them until we start the write phase. We already know that the SHARED lock is used for read operations (phase 1), and that the EXCLUSIVE lock is used for write operations (phase 2). So what is the UPDATE lock used for?

If we used a SHARED lock for the duration of the read phase, then we might run into a deadlock when multiple sessions run the same UPDATE statement concurrently.

Read on for more details.

Related Posts

Defining Intent Locks

David Fowler explains what an intent lock is and why it’s useful: Let’s just imagine a World without intent locks for a moment. In that World, a user has just decided to select a row from our database. SQL at that point is going to put down a shared lock against the row. Now what’s […]

Read More

Sleeping Sessions Holding Locks

Jonathan Kehayias walks us through an interesting scenario: A recent consulting engagement was focused on blocking issues inside of SQL Server that were causing delays in processing user requests from the application. As we started to dig into the issues being experienced, it became clear that from a SQL Server standpoint, the problem revolved around […]

Read More

Categories

November 2017
MTWTFSS
« Oct Dec »
 12345
6789101112
13141516171819
20212223242526
27282930