Some unknown facts about Update (U) lock

You may have read and found the following statement type – lock type relation easy to understand and logical too:

SELECT – Shared (S)

UPDATE – Update (U)

INSERT & DELETE – Exclusive (X)

 

I was clear about the shared (S) and exclusive (X) but not about update (U) lock. Some of my questions about update lock were following:

Is it used only while updating a record or while deleting the record too?

As update (U) lock is converted to exclusive (X) lock then how long update (U) lock persist on a row?

 

To get the answer of these questions I did some analysis and I concluded that work in following points:

1.   Update lock is used not only with UPDATE statements but also with DELETE statements.

2.       Update lock is used only if a transaction wants to update or delete a record that is already locked (S) by another transaction. Otherwise direct X lock is used. In READ UNCOMMITTED isolation level, no shared (S) lock is used so update (U) lock can never be found in GRANT status (in sp_lock output). We can only find it in WAIT status.

3.       Update lock is “Waiting exclusive lock”. So we can never find an exclusive (X) lock in WAIT status (in sp_lock output) because in that case update (U) lock is used.

Please let me know your analysis and review.