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?
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.
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.
