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

Lock Promotion

Erik Darling tries to figure out why his locks can’t get ahead in the rat race: The first thing I found is that there were 16 attempts at promotion, and four successful promotions.Why did this seem weird? I dunno.Why would there be only 4 successful attempts with no competing locks from other queries?Why wouldn’t all […]

Read More

Thoughts On Exclusive Locks

Louis Davidson shares some thoughts on exclusive locks in SQL Server: You will find that the SELECT statement executes, ignoring the exclusive lock, because it is not a write lock, and the data on the page has not been changed.The main reason people try to do this is to force access to a row in […]

Read More

Categories

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