DB2 主要行级锁模拟

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题: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
此条目发表在 DB2 分类目录。将固定链接加入收藏夹。

评论功能已关闭。