The question:
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?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.
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