联系：手机/微信(+86 17813235971) QQ(107644445)
标题：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查询数据库时给表加的一个表级锁.