联系:手机/微信(+86 17813235971) QQ(107644445)
标题:DB2 主要行级锁模拟
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
S行锁和X行锁模拟
--会话1 [db2inst1@xifenfei ~]$ db2 "create table t_xifenfei(id int,name varchar(100))" DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 +c "insert into t_xifenfei values(1,'www.xifenfei.com')" DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:07:02 -- Date 2012-04-29-01.46.48.462742 Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg 0x99B3AE40 8 53514C4332473137315992A241 Internal P ..S G 8 1 0 0x00 0x40000000 0x99B3A690 8 02000700040080000000000052 Row ..X G 8 1 0 0x08 0x40000000 0x99B3A6F0 8 02000700000000000000000054 Table .IX G 8 1 0 0x00 0x40000000 --会话2 [db2inst1@xifenfei ~]$ db2 "select * from t_xifenfei" hang住 --会话3查询等待 [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:08:40 -- Date 2012-04-29-01.48.26.676607 Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg 0x99B3AE40 8 53514C4332473137315992A241 Internal P ..S G 8 1 0 0x00 0x40000000 0x99B3A540 7 53514C4332473137315992A241 Internal P ..S G 7 1 0 0x00 0x40000000 0x99B3A690 8 02000700040080000000000052 Row ..X G 8 1 0 0x08 0x40000000 0x99B40C60 7 02000700040080000000000052 Row .NS W 8 1 0 0x00 0x00000001 0x99B3A420 7 02000000010000000100407056 Internal V ..S G 7 1 0 0x00 0x40000000 0x99B3A6F0 8 02000700000000000000000054 Table .IX G 8 1 0 0x00 0x40000000 0x99B3A510 7 02000700000000000000000054 Table .IS G 7 1 0 0x00 0x00000001 --这里可以发现Sts=W(STATUS=WAIT),会话的NS锁处于等待状态 --会话1 [db2inst1@xifenfei ~]$ db2 commit DB20000I The SQL command completed successfully. --会话2 [db2inst1@xifenfei ~]$ db2 "select * from t_xifenfei" ID NAME ----------- ---------------------------------------------------------------------------------------------------- 1 www.xifenfei.com 1 record(s) selected. --结果出现 --会话3 [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:09:06 -- Date 2012-04-29-01.48.52.069878 Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg
U锁模拟
--会话1 [db2inst1@xifenfei ~]$ db2 +c "declare c1 cursor for select * from t_xifenfei for update" DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 +c open c1 DB20000I The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 +c fetch c1 ID NAME ----------- ---------------------------------------------------------------------------------------------------- 1 WWW.XIFENFEI.COM 1 record(s) selected. --会话2 [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:27:27 -- Date 2012-04-29-02.07.13.594441 Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg 0x99B3A420 7 53514C4332473137315992A241 Internal P ..S G 7 1 0 0x00 0x40000000 0x99B3A510 7 02000700040080000000000052 Row ..U G 7 1 0 0x00 0x00000001 0x99B40C60 7 01000000010000000100807256 Internal V ..S G 7 1 0 0x00 0x40000000 0x99B3A4E0 7 02000700000000000000000054 Table .IX G 7 1 0 0x00 0x00000001 --会话1 [db2inst1@xifenfei ~]$ db2 +c "update t_xifenfei set name='www.xifenfei.com'" DB20000I The SQL command completed successfully. --会话2 [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:27:44 -- Date 2012-04-29-02.07.30.684616 Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg 0x99B3A420 7 53514C4332473137315992A241 Internal P ..S G 7 1 0 0x00 0x40000000 0x99B3A510 7 02000700040080000000000052 Row ..X G 7 2 0 0x00 0x40000001 0x99B40C60 7 01000000010000000100807256 Internal V ..S G 7 1 0 0x00 0x40000000 0x99B3A4E0 7 02000700000000000000000054 Table .IX G 7 2 0 0x00 0x40000001 --行级锁由U升级到X --会话1 [db2inst1@xifenfei ~]$ db2 commit DB20000I The SQL command completed successfully. --会话2 [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:31:43 -- Date 2012-04-29-02.11.29.167659 Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg