联系：手机/微信(+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