June 29, 2010 at 12:23 pm
Hello,
There is a simple table with primary clustered index (uniqueidentifier).
Locking row on a table identifier gives me a lock on index key, that's fine.
In every session I am using read committed isolation level.
1) When I select a row by primary key "with (updlock)" hint it gives me U-lock on the PK.
In another session I do again select by this PK(without any hint) ... I am able to read it ... Why??
now ..
2) When I do update on a row by primary key it gives me U-lock on the PK (the same lock like in 1 test)
In another session I am not able to read it ..
1- Why in the 1 test I can read record which is locked using updlock hint from the other session?
2- I have a compatibility locks table (from BOL)
there is written(or maybe I read it badly) that if I demand mode (S) for (U)lock I can acquire this mode. Why is that an index key U lock can be acquired by S-SharedLock?
3- is That true that doing an insert to a table, it results in locking: all indexes and RID?
Thanks for help in advance
June 29, 2010 at 1:43 pm
The "U" lock type prevents unnecessary blocking (and deadlocking). It will get converted to an exclusive lock once you actually update the row, but, until then, others can still access the row (to read, not another "U"; or "X", of course).
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply