What locks are held during an update
When I have a transaction with multiple queries, I am able to test which locks are held between queries by using
waitforand checking the
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
wherespecifies, and then request a U lock?
inna last edited by
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.