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