Menu
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
492
At the table level, there are 5 different types of locks. i.e, Exclusive (X), Shared (S), Intent exclusive (IX), Intent shared (IS), and Shared with intent exclusive (SIX) and these locks have already been discussed above.
U-locks are very difficult to open by hammering or using force, but they are weakened and become brittle when cooled by air spray, meaning that they can be smashed open.
Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released.
A lock has a mode that determines its power--whether it prevents other users from reading or changing the locked resource. There are six lock modes: X. Exclusive locks can be read or write. Only one transaction can hold an exclusive lock on a resource at a given time.
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.
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
