|
Just trying to understand locking/blocking in sqlserver. Microsoft SQL Server 2008 isolation level : READ COMMITTED SNAPSHOT Created a table with 2 columns. No primary keys, no indexes create table TEST(COL1 INT, COL2 CHAR(1000)) Populated the table TEST with 6 rows INSERT INTO TEST (COL1) VALUES (1) INSERT INTO TEST (COL1) VALUES (2) INSERT INTO TEST (COL1) VALUES (3) INSERT INTO TEST (COL1) VALUES (4) INSERT INTO TEST (COL1) VALUES (5) INSERT INTO TEST (COL1) VALUES (6) In one session, begin a transaction and updated the first 5 rows BEGIN TRAN UPDATE TEST SET COL1 = 10 WHERE COL1 = 1 UPDATE TEST SET COL1 = 20 WHERE COL1 = 2 UPDATE TEST SET COL1 = 30 WHERE COL1 = 3 UPDATE TEST SET COL1 = 40 WHERE COL1 = 4 UPDATE TEST SET COL1 = 50 WHERE COL1 = 5 without commiting this session, connect another session, begin a transaction and try to update the 6th row. BEGIN TRAN UPDATE TEST with (rowlock) SET COL1 = 60 WHERE COL1 = 6 Find that the 2nd session is blocked by 1st session. The lockinfo shows that 2nd session is waiting on a row that is updated by 1st session. I don't understand why? In the lockinfo information, 1st session spid is 138 and 2nd session spid is 141. spid 138 has RID 1:291596:0 (is this the first row) locked in exclusive mode, and spid 141 wants the same row to be locked in update mode? I am not sure why? spid ecid ObjId IndId Type Resource Mode Status TransID 141 0 0 0 DB S GRANT 0 138 0 0 0 DB S GRANT 0 141 0 1016415111 0 TAB IX GRANT 1130132693 138 0 1016415111 0 TAB IX GRANT 1130132437 138 0 1016415111 0 RID 1:291596:4 X GRANT 1130132437 138 0 1016415111 0 RID 1:291596:1 X GRANT 1130132437 141 0 1016415111 0 PAG 1:291596 IU GRANT 1130132693 138 0 1016415111 0 PAG 1:291596 IX GRANT 1130132437 138 0 1016415111 0 RID 1:291596:0 X GRANT 1130132437 141 0 1016415111 0 RID 1:291596:0 U WAIT 1130132693 138 0 1016415111 0 RID 1:291596:3 X GRANT 1130132437 138 0 1016415111 0 RID 1:291596:2 X GRANT 1130132437 Any input as to why the 2nd session wants a lock on the same RID when the update statemet for the 2nd session is for an entirely different row is appreciated? I am guessing RID 1:291596:0 is for 1st row since I see 1,2,3, and 4 for updates done by 1st session. Thanks |
