UPDATE locks are not really a separate kind of lock, but rather are a hybrid of SHARED and EXCLUSIVE locks. And contrary to what you might think, UPDATE locks are not just acquired for UPDATE operations. A transaction acquires this kind of lock when SQL Server executes a data modification operation but first needs to perform a search to find the resource to modify.
While SQL Server is searching, it shouldn’t need to acquire an EXCLUSIVE lock; it only needs the EXCLUSIVE lock when the data to be changed is found. Normally, if a SQL Server process was just searching for data, it would acquire a SHARED lock on each resource it encounters and then determines whether it has found the data it is searching for. However, if SQL Server started out with a SHARED lock while searching for the data to modify, there are potential problems. A situation could occur where two processes were both searching for the same resource to modify (for example, the same customer row in the Customers table), using different access paths, and they could both reach the desired resource at the same time. If they both were acquiring SHARED locks on the data they were examining, they could both lock the resource they wanted to change, but before they made the modification they would have to convert their lock to an EXCLUSIVE lock. Since the other process would have a SHARED lock, no EXCLUSIVE lock could be granted. Each process would have a SHARED lock, and each would try to change it to an EXCLUSIVE lock, but neither could proceed because of the presence of the other. This is a deadlock situation, called a ‘conversion deadlock’.
UPDATE locks are really a deadlock avoidance mechanism. If SQL Server uses UPDATE locks, a deadlock will NOT occur. If a SQL Server process begins a search operation with the intention of eventually modifying data, it acquires UPDATE locks until it finds the data to modify. UPDATE locks are compatible with SHARED locks, but are not compatible with EXCLUSIVE locks or other UPDATE locks. So if two processes were searching for the same data resource, the first one to reach it would acquire an UPDATE lock, and then the second process could not get any lock and would wait for the first process to be done. Since the first process was not blocked, it could convert its UPDATE lock to an EXCLUSIVE lock, make the data modification, and finish its transaction and release its locks. Then the second process could make its change.
In the sys.dm_tran_locks view, a request_mode value of ‘U’ indicates an UPDATE lock.
So let's look at UPDATE locks:
[I am using the old sample database pubs. If you want to try this exact code, you can download pubs from here.]
-- Close all existing connections and start a new one
-- Step 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET contract = 0
WHERE au_lname = 'Ringer' ;
-- -- Step 2: Open a second connection window uncomment once,
-- -- so the ROLLBACK is still commented. Execute…
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
--SET city = 'Provo'
--WHERE state = 'UT';
-- -- You should be blocked.
-- -- ROLLBACK TRAN;
-- Step 3: Go back to the first connection window and run the following
SELECT request_session_id AS session_id, DB_NAME(resource_database_id) AS [database],
request_mode AS mode, resource_type as [type],
resource_associated_entity_id AS entity,
resource_description, request_status AS status
You should see output similar to this:
Note the U lock with the status of WAIT for a KEY, with the same resource description as a KEY lock that the first connection has been GRANTed. Now COMMIT or ROLLBACK the first connection, and you should see the second connection will get the X lock on the KEY it is waiting for, plus the X lock on the other KEY.
I mentioned that UPDATE locks indicated an 'intention of eventually modifying data', so you might think that UPDATE locks are similar to INTENT locks. Not really… UPDATE locks indicate an intention to change the lock mode, whereas INTENT locks indicate an intention to change the lock granularity.
Wishing you maximum concurrency,