Db2中，为什么ALTER TABLE需要X类型的internal Plan lock?
徐学潞
Db2日常运维中，有时候会发现ALTER TABLE操作由于锁超时而失败，如果在锁超时之前查看锁等情况，可能会看到它需要X类型的internal plan lock，而别的应用已经以S模式hold住该锁，两者不兼容导致ALTER TABLE锁等。本文介绍了一种场景，重现了 ALTER TABLE语句需要X类型的internal P lock。
首先，需要知道什么是internal plan lock，或者说internal p lock，它的含义可以在下面的链接中找到
也就是说，任何SQL语句执行前都需要一个'package lock'，以防止SQL在执行的时候，有别的应用把它的package删掉。
下面的场景中，创建了一个存储过程，存储过程需要访问表T2，因此存储过程对应的包依赖于表T2，当表T2的结构发生变化时，存储过程和package就会失效，这个过程是需要该package的X锁的。
3. 提交之后，由于修改了表结构，导致依赖于这个表的package失效：
附1：
要查看package依赖了哪些对象，可以参考视图 syscat.packagedep
inst97@db2a:~$ db2 "select substr(BSCHEMA,1,20) as BSCHEMA, substr(BNAME, 1,30) as BNAME, BTYPE from syscat.packagedep where PKGNAME='P917055792' and PKGSCHEMA='INST97'"
BSCHEMA BNAME BTYPE
-------------------- ------------------------------ -----
INST97 T1 T
INST97 T2 T
2 record(s) selected.
附2：
还有一个technote，讲述了另一种要在package上面加X锁的场景：如果package不在package cache里，那么把它装载到package cache的过程中，需要加上X锁，装载完成之后，就会释放。
Why exclusive (X) mode "Internal P" locks can be seen while applications are running?
http://www-01.ibm.com/support/docview.wss?uid=swg21611947
首先，需要知道什么是internal plan lock，或者说internal p lock，它的含义可以在下面的链接中找到
http://www-01.ibm.com/support/docview.wss?uid=swg21503717
Internal Plan Lock(P lock) (Package Cache/ Plan Lock): Any execution of SQL, whether static or dynamic, must be done while holding a 'package lock'. This ensures that no-one can drop the package that is being executed.
也就是说，任何SQL语句执行前都需要一个'package lock'，以防止SQL在执行的时候，有别的应用把它的package删掉。
下面的场景中，创建了一个存储过程，存储过程需要访问表T2，因此存储过程对应的包依赖于表T2，当表T2的结构发生变化时，存储过程和package就会失效，这个过程是需要该package的X锁的。
1. 创建存储过程PROCEDURE01，并找到对应的package name和状态，可以看到 PKGNAME为P917055792，状态有效
inst97@db2a:~$ cat create.pro
CREATE OR REPLACE PROCEDURE PROCEDURE01 ()
DYNAMIC RESULT SETS 2
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN for
SELECT ID FROM INST97.T1 with RR;
DECLARE cursor2 CURSOR WITH RETURN for
SELECT ID FROM INST97.T2 with RR;
-- Cursor left open for client application
OPEN cursor1;
OPEN cursor2;
END P1
@
inst97@db2a:~$ db2 -td@ -f create.pro
DB20000I The SQL command completed successfully.
inst97@db2a:~$ db2 "select trim (substr (r.routineschema, 1, 10)) as routineschema,trim (substr (r.routinename, 1, 30)) as routinename, r.valid, trim (substr (p.pkgschema, 1, 15)) as pkgschema, trim (substr (p.pkgname, 1, 15)) as pkgname, p.valid from syscat.routines r, syscat.packages p, syscat.procedures a,syscat.routinedep b where b.specificname=r.specificname and r.specificname=a.specificname and r.routinetype = 'P' and b.bname=p.pkgname and a.procname='PROCEDURE01' order by p.create_time desc fetch first 5 rows only"
ROUTINESCHEMA ROUTINENAME VALID PKGSCHEMA PKGNAME VALID
------------- ------------------------------ ----- --------------- --------------- -----
INST97 PROCEDURE01 Y INST97 P917055792 Y
1 record(s) selected.
inst97@db2a:~$ db2 +c "ALTER TABLE t2 alter column name set data type char(21)"
DB20000I The SQL command completed successfully.
inst97@db2a:~$ db2pd -db sample -locks
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:01:33 -- Date 2017-11-03-01.59.52.615142
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
0x00007FC65BE05A00 2 00000E0001002E000000000052 Row ..S G 2 2 0 0x00000010 0x40000000 1
0x00007FC65BE01F00 2 00000705040000000000000052 Row ..X G 2 1 0 0x00000008 0x40000000 0
0x00007FC65BE02E80 2 00000C0106002F000000000052 Row .NS G 2 2 0 0x00000000 0x40000000 0
0x00007FC65BE02280 2 41414141414242636F724AC241 Internal P ..S G 2 1 0 0x00000000 0x40000000 0
0x00007FC65BE06200 2 00000500063600006018076543 CatCache ..X G 2 255 0 0x00000000 0x40000000 0
0x00007FC65BE02580 2 0000000103820000E01D076543 CatCache ..X G 2 255 0 0x00000000 0x40000000 0
0x00007FC65BE05E80 2 00000A00150005000000000052 Row ..X G 2 1 0 0x00000010 0x40000000 5
0x00007FC65BE05C00 2 00000D0002002C000000000052 Row ..X G 2 1 0 0x00000000 0x40000000 0
0x00007FC65BE02780 2 02000000C67F0000E01D076543 CatCache ..X G 2 255 0 0x00000000 0x40000000 0
0x00007FC65BE04C80 2 00000500060036000000000052 Row ..X G 2 12 0 0x00000000 0x40000000 0
0x00007FC65BE02A80 2 00000001030082000000000052 Row ..X G 2 4 0 0x00000000 0x40000000 0
0x00007FC65BE02C00 2 6A41483542444C68F77CF90A41 Internal P ..X G 2 1 0 0x00000000 0x40000000 0
0x00007FC65BE05F80 2 01000000C67F00006018076543 CatCache ..X G 2 255 0 0x00000000 0x40000000 0
0x00007FC65BE01900 2 000006000C0038010000000052 Row ..X G 2 1 0 0x00000010 0x40000000 1
0x00007FC65BE00100 2 00000E0008002D000000000052 Row ..S G 2 1 0 0x00000010 0x40000000 1
0x00007FC65BE02180 2 00000E0000002E000000000052 Row ..S G 2 2 0 0x00000010 0x40000000 1
0x00007FC65BE02380 2 00000E00000000000000000054 Table .IS G 2 2 0 0x00002010 0x40000000 0
0x00007FC65BE06300 2 00000C01000000000000000054 Table .IS G 2 7 0 0x00002000 0x40000000 0
0x00007FC65BE02080 2 00000705000000000000000054 Table .IX G 2 1 0 0x00002000 0x40000000 0
0x00007FC65BE02D80 2 03000400000000000000000054 Table ..Z G 2 4 0 0x00002000 0x40000000 0
0x00007FC65BE02880 2 00000C00000000000000000054 Table .IS G 2 2 0 0x00002000 0x40000000 0
0x00007FC65BE05B00 2 00000500000000000000000054 Table .IX G 2 12 0 0x00002000 0x40000000 0
0x00007FC65BE02C80 2 00000001000000000000000054 Table .IX G 2 4 0 0x00002000 0x40000000 0
0x00007FC65BE05D00 2 00000D00000000000000000054 Table .IX G 2 1 0 0x00002000 0x40000000 0
0x00007FC65BE01C00 2 00000600000000000000000054 Table .IX G 2 1 0 0x00002010 0x40000000 0
0x00007FC65BE02480 2 00000A00000000000000000054 Table .IX G 2 1 0 0x00002010 0x40000000 0
inst97@db2a:~$ db2pd -db sample -static | grep -i 6A41483542444C68F77CF90A41
0x00007FC6651FFBC0 INST97 P917055792 jAH5BDLh 2 0 3 CS 5 U 6A41483542444C68F77CF90A413. 提交之后，由于修改了表结构，导致依赖于这个表的package失效：
inst97@db2a:~$ db2 commit
DB20000I The SQL command completed successfully.
inst97@db2a:~$ db2 "select trim (substr (r.routineschema, 1, 10)) as routineschema,trim (substr (r.routinename, 1, 30)) as routinename, r.valid, trim (substr (p.pkgschema, 1, 15)) as pkgschema, trim (substr (p.pkgname, 1, 15)) as pkgname, p.valid from syscat.routines r, syscat.packages p, syscat.procedures a,syscat.routinedep b where b.specificname=r.specificname and r.specificname=a.specificname and r.routinetype = 'P' and b.bname=p.pkgname and a.procname='PROCEDURE01' order by p.create_time desc fetch first 5 rows only"
ROUTINESCHEMA ROUTINENAME VALID PKGSCHEMA PKGNAME VALID
------------- ------------------------------ ----- --------------- --------------- -----
INST97 PROCEDURE01 N INST97 P917055792 N
1 record(s) selected.附1：
要查看package依赖了哪些对象，可以参考视图 syscat.packagedep
inst97@db2a:~$ db2 "select substr(BSCHEMA,1,20) as BSCHEMA, substr(BNAME, 1,30) as BNAME, BTYPE from syscat.packagedep where PKGNAME='P917055792' and PKGSCHEMA='INST97'"
BSCHEMA BNAME BTYPE
-------------------- ------------------------------ -----
INST97 T1 T
INST97 T2 T
2 record(s) selected.
附2：
还有一个technote，讲述了另一种要在package上面加X锁的场景：如果package不在package cache里，那么把它装载到package cache的过程中，需要加上X锁，装载完成之后，就会释放。
Why exclusive (X) mode "Internal P" locks can be seen while applications are running?
http://www-01.ibm.com/support/docview.wss?uid=swg21611947
类似资料：