DB2 主要表级锁模拟

模拟X锁

[db2inst1@xifenfei ~]$ db2 connect to xff    Database Connection Information  Database server        = DB2/LINUX 9.5.9  SQL authorization ID   = DB2INST1  Local database alias   = XFF [db2inst1@xifenfei ~]$ db2 list tables Table/View                      Schema          Type  Creation time             ------------------------------- --------------- ----- -------------------------- T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478 T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326 T_03XFF                         DB2INST1        T     2012-04-11-21.33.12.479480   3 record(s) selected. [db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in exclusive mode" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:28:32 -- Date 2012-04-29-01.08.18.056347 Locks: Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg 0x99B43210 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 0x99B42F30 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          1   0          0x00 0x40000000 0x99B43240 7          53514C4445464C5428DD630641 Internal P ..S  G   7          1   0          0x00 0x40000000 0x99B430F0 7          02000400000000000000000054 Table      ..X  G   7          255 0          0x00 0x40000000 --还有db2内部P锁和CatCache锁 [db2inst1@xifenfei ~]$ db2 commit DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:28:44 -- Date 2012-04-29-01.08.30.152903 Locks: Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟S锁

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in share mode" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:29:23 -- Date 2012-04-29-01.09.09.610865 Locks: Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg 0x99B43240 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 0x99B43030 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          1   0          0x00 0x40000000 0x99B431B0 7          02000400000000000000000054 Table      ..S  G   7          255 0          0x00 0x40000000 [db2inst1@xifenfei ~]$ db2 commit DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:29:41 -- Date 2012-04-29-01.09.27.402678 Locks: Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟Z锁

[db2inst1@xifenfei ~]$ db2 +c drop table t_02xff DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:31:14 -- Date 2012-04-29-01.11.00.399066 Locks: Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg 0x99B41270 7          000006000E006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42870 7          00000600010071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42CF0 7          0100000000000000B0FA119F43 CatCache   ..X  G   7          255 0          0x00 0x40000000 0x99B43240 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 0x99B418D0 7          00000600000070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B424B0 7          00000600060071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B415D0 7          00000600050070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42090 7          000006000B0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41AB0 7          0000060004006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B412D0 7          000006000A0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42930 7          00000600100071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42ED0 7          00000C0107004C030000000052 Row        ..S  G   7          2   0          0x10 0x40000000 0x99B41A50 7          0000060003006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B425D0 7          0000060009006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B420C0 7          000006000F0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42750 7          0000060008006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42630 7          000006000E006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B425A0 7          00000600010072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41F30 7          000006000D006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41B70 7          00000600000071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B40CF0 7          0200000000000000000000004F ObjTab     .IN  G   7          255 0          0x00 0x40000000 0x99B41C60 7          00000600050071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41630 7          00000600040070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42390 7          000006000A0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42030 7          0000060003006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B416F0 7          00000600090070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41690 7          000006000F0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41990 7          0000060008006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B429F0 7          000006000E0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B426F0 7          0000060007006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41E10 7          000006000D006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B428D0 7          00000600000072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42E40 7          000006000C006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42B10 7          00001101100057120000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41CC0 7          00000600040071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B417B0 7          000006000F00CE1A0000000052 Row        ..X  G   7          1   0          0x00 0x40000000 0x99B42960 7          00000600030070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B422D0 7          00000600090071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41570 7          000006000F0072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41B10 7          0000060002006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41510 7          00000600080070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B413F0 7          000006000E0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B40E70 7          00000E006E003B010000000052 Row        ..S  G   7          1   0          0x10 0x40000000 0x99B41150 7          0000060001006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41BD0 7          0000060007006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42540 7          000006000D0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41210 7          0000060006006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41E70 7          000006000C006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B43090 7          00000500076E0D00B0FA119F43 CatCache   ..X  G   7          255 0          0x00 0x40000000 0x99B421B0 7          000006000B006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42C30 7          000013000A00C81A0000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B427B0 7          00000600030071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42D50 7          00000A00080063000000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41870 7          00000600020070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B423F0 7          00000600080071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42690 7          0000060001006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B40F30 7          00000600070070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41DB0 7          000006000D0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B410F0 7          0000060000006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41F90 7          0000060006006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42A50 7          000006000C0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B40F90 7          0000060005006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41ED0 7          000006000B006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41750 7          00000600110070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B414B0 7          000006000A006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B411B0 7          0000060010006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42450 7          000006000F006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B40DB0 7          02000000000000000000000070 Pool       .IX  G   7          255 0          0x00 0x40000000 0x99B42810 7          00000600020071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42F90 7          0000050007006E0D0000000052 Row        ..X  G   7          7   0          0x20 0x40000000 0x99B41CF0 7          00000600010070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42330 7          00000600070071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41030 7          0000060000006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41D50 7          00000600060070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41810 7          000006000C0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41090 7          0000060005006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42270 7          000006000B0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B419F0 7          0000060004006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41330 7          000006000A006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42210 7          00000600100070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B41450 7          0000060009006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42150 7          000006000F006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B42C90 7          00001300000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 0x99B43210 7          00000C01000000000000000054 Table      .IS  G   7          2   0          0x10 0x40000000 0x99B42BD0 7          00000E01000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 0x99B430F0 7          00000500000000000000000054 Table      .IX  G   7          7   0          0x00 0x40000000 0x99B42AB0 7          00000600000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 0x99B42B70 7          00001101000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 0x99B42E70 7          00000800000000000000000054 Table      .IX  G   7          2   0          0x00 0x40000000 0x99B42DE0 7          00000A00000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 0x99B40D50 7          02000500000000000000000054 Table      ..Z  G   7          255 0          0x00 0x40000000 0x99B40E10 7          00000C00000000000000000054 Table      .IS  G   7          1   0          0x00 0x40000000 0x99B40ED0 7          00000E00000000000000000054 Table      .IS  G   7          1   0          0x10 0x40000000 0x99B413C0 7          00000801000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 --除了Z锁之外,因为DDL操作会修改系统表,因此还出现很多在系统表上表锁和行锁 [db2inst1@xifenfei ~]$ db2 rollback DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:31:41 -- Date 2012-04-29-01.11.27.194147 Locks: Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟IX锁

[db2inst1@xifenfei ~]$ db2 +c "DELETE FROM T_02XFF where tabname LIKE 'T_%XFF'" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:43:47 -- Date 2012-04-29-01.23.33.163605 Locks: Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg 0x99B3A510 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 0x99B40C30 7          0200050008006F010000000052 Row        ..X  G   7          1   0          0x20 0x40000000 0x99B3A4B0 7          02000500000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 [db2inst1@xifenfei ~]$ db2 rollback DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:45:06 -- Date 2012-04-29-01.24.52.429166 Locks: Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟SIX锁

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in share mode" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 +c "DELETE FROM T_01XFF" SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000 [db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:39:47 -- Date 2012-04-29-01.19.33.620920 Locks: Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg 0x99B40E10 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 0x99B40C30 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          5   0          0x00 0x40000000 0x99B40D50 7          02000400FFFF01000000000052 Row        ..S  G   7          1   0          0x10 0x00000001 0x99B431B0 7          02000400000000000000000054 Table      SIX  G   7          255 0          0x10 0x40000001

因为IS锁不太好模拟,在本实验中没有体现出来,其本质就是数据库在select查询数据库时给表加的一个表级锁.