Lock Question - 'U' lock vs. 'X' lock

Question

I have a couple questions concerning Update (U) locks and Exclusive (X) locks.

1) Am I correct that an 'X' lock is put on a resource when the resource is about to get updated?

2) I'm a little fuzzy on U locks. Am I correct that a U lock is applied when a resource is read and SQL Server thinks it might need to update the resource later? If this is correct, would a 'U' lock only get applied when a read is being done within the context of a transaction? I guess I'm trying to understand under what circumstances SQL Server thinks it might need to update later a row it just read now.

Thanks - Randy

Answer 1

1) Am I correct that an 'X' lock is put on a resource when the resource is about to get updated?

Yes.

2) I'm a little fuzzy on U locks. Am I correct that a U lock is applied when a resource is read and SQL Server thinks it might need to update the resource later? If this is correct, would a 'U' lock only get applied when a read is being done within the context of a transaction? I guess I'm trying to understand under what circumstances SQL Server thinks it might need to update later a row it just read now.

U locks are compatible with the read locks but not with each other, X locks are not compatible even with the read locks.

U locks are placed by DML queries (UPDATE, DELETE, MERGE) while scanning the table rows (no decision to update is made yet), while X locks are placed when the decision is made to update the row.

In READ COMMITTED isolation mode, update locks are lifted after the record was evaluated to be left as is, in higher isolation modes they are kept until the end of the transaction.