What locks are held during an update

All we need is an easy explanation of the problem, so here it is.

When I have a transaction with multiple queries, I am able to test which locks are held between queries by using waitfor and checking the dm_tran_locks.

But I cannot "pause" one query in half. Specifically, I am wondering how this query will hold locks:

update my_table set column1=new_value
where column2=filter_value

Will this take an update lock from the start? Or will it take a shared lock until it determines the rows which the where specifies, and then request a U lock?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

For each row that is has to look at, it first acquires an update lock. It now checks if this row qualifies for the modification.

  • If it does then it acquires an exclusive lock and releases the update lock.
  • If not, it just releases the update lock.

I.e., an update lock is very short-lived, and it is difficult to catch one in the wild unless it happens to be blocked (by another update or exclusive lock, for instance).

Regarding which rows it has to look at: Imagine you have an index on the lastname column and your search predicate for the update is:

WHERE lastname = 'Menoutis'
AND firstname = 'George'

SQL Server can use the index on lastname to "drive" the update. I.e., it finds the the first Menoutis using the index on the lastname column and see if that Menoutis’ firstname is George; and takes action as I described above. And then does the same for each Menoutis found through that index.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply