1

As far as I know, SQL Server holds X locks for the duration of a transaction no matter which isolation level is in use. What about the U lock? If a transaction reads 100 records in an UPDATE statement (U locks) but updates 10 records only, will we end up with 90-U and 10-X after the update is done and if the tran is still open?

In response to David's answer below: No those situations are not related (thanks for that answer!). I cannot post the code as I have no my PC at hand, but I will try to describe this case in a nutshell. I have a heap. I update it, there are 10 records updated. Q: will we have any U locks at the ... point or are they released once X locks are obtained? Thanks!

Begin tran;

Update t
Set b = 10
Where a = 1;
...
  • 1
    Can you post some demo code that illustrates the situation you're trying to parse out? I answered a similar question of yours over here -- are they related? – Erik Darling Dec 8 '17 at 12:46
5

An UPDATE will read with U locks, releasing each U lock immediately if it is not going to X lock and change the row. Profiler will show you this with the Lock:Acquired and Lock:Released events.

EG an update on a heap will IX lock the object, then for each page IU lock the page, then for each row U lock the row, and either release the U lock or convert to IX lock on the page, convert to X lock on the row and update it. The page lock will be released when it's read the last row on the page.

use tempdb

select * 
into test 
from sys.objects

go

begin tran
update test set name = 'foo2'
where object_id = 9

Has a lock sequence like this:

Lock:Acquired                               59      8 - IX  5 - OBJECT  
Lock:Acquired   5:64                            59      7 - IU  6 - PAGE    
Lock:Acquired   5:64:0                          59      4 - U   9 - RID 
Lock:Released   5:64:0                          59      4 - U   9 - RID 
Lock:Acquired   5:64:1                          59      4 - U   9 - RID 
Lock:Released   5:64:1                          59      4 - U   9 - RID 
Lock:Acquired   5:64:2                          59      4 - U   9 - RID 
Lock:Released   5:64:2                          59      4 - U   9 - RID 
Lock:Acquired   5:64:3                          59      4 - U   9 - RID 
Lock:Released   5:64:3                          59      4 - U   9 - RID 
Lock:Acquired   5:64:4                          59      4 - U   9 - RID 
Lock:Released   5:64:4                          59      4 - U   9 - RID 
Lock:Acquired   5:64:5                          59      4 - U   9 - RID 
Lock:Acquired   5:64                            59      8 - IX  6 - PAGE    
Lock:Acquired   5:64:5                          59      5 - X   9 - RID 
Lock:Released   5:64:5                          59      0 - NULL    9 - RID 
Lock:Acquired   5:64:6                          59      4 - U   9 - RID 
. . . continue reading rows
  • I thought you were all supposed to use Extended Events? :D – Erik Darling Dec 8 '17 at 17:57
  • 1
    I still use profiler for local debugging because it's convenient and integrated with SSMS. XEvents is really a replacement for SQL Trace ( ie server-side production tracing). – David Browne - Microsoft Dec 8 '17 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.