What's the duration of U lock 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;
...
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