本文介绍了一种在DB2数据库中遇到的应用无法删除表的问题及其解决方法。问题表现为删除特定表时长时间卡住，通过检查锁及锁等待情况发现存在锁冲突。最终通过强制删除引起冲突的应用会话解决了该问题。
锁系统表导致无法drop表
一、现象：
应用反映删除表时，一直卡在那，无法正常删除；
[DWE3:/db2home/db2inst1]db2 "drop TABLE MARTRPT.T_RPT_MG_REV_02_M"
二、分析
打开另一个窗口，通过db2pd工具查看检查锁及锁等待情况
[DWE3:/db2home/db2inst1]db2pd -db bssdb -locks -wlocks
Database Partition 0 -- Database BSSDB -- Active -- Up 6 days 15:13:50
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg
0x078000023F243540 56 0000010C0000000794D8002252 Row ..S G 56 1 0 0x10 0x40000000
0x078000023F244700 56 00000006000000005324000252 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F2430C0 56 00000006000000005324000E52 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F241AC0 56 00000006000000005324000352 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F236240 56 00000006000000005324000F52 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F24E7C0 56 0000000500000000338F000752 Row ..X G 56 4 0 0x20 0x40000000
0x078000023F23CB80 101 535953534832303028EFECDC41 Internal P ..S G 101 1 0 0x00 0x40000000
0x078000023EE26F00 122 535953534832303028EFECDC41 Internal P ..S G 122 1 1 0x00 0x40000000
0x078000023F23C140 7 535953534832303028EFECDC41 Internal P ..S G 7 1 1 0x00 0x40000000
0x078000023EA0EA00 121 535953534832303028EFECDC41 Internal P ..S G 121 1 0 0x00 0x40000000
0x078000023F238900 39 535953534832303028EFECDC41 Internal P ..S G 39 1 0 0x00 0x40000000
0x078000023EA0A200 13 535953534832303028EFECDC41 Internal P ..S G 13 1 0 0x00 0x40000000
0x078000023F233E00 44 535953534832303028EFECDC41 Internal P ..S G 44 1 0 0x00 0x40000000
0x078000023F24E700 114 535953534832303028EFECDC41 Internal P ..S G 114 1 0 0x00 0x40000000
0x078000023F233A00 56 00000111000000002F4D000552 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F23D340 56 00000006000000005324001052 Row ..X G 56 1 0 0x20 0x40000000
0x078000023EE29240 122 000000D1000000010001A70056 Internal V ..S G 122 1 1 0x00 0x40000000
0x078000023F2404C0 114 000000CD0000000100010F0056 Internal V ..S G 114 1 0 0x00 0x40000000
0x078000023F239A80 56 0000000A000000003292001452 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F23D0C0 56 00000111000000002F4D000652 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F2426C0 101 000000E5000000010001E90056 Internal V ..S G 101 2 0 0x00 0x40000000
0x078000023F24E440 114 000000DD000000010001B20056 Internal V ..S G 114 2 0 0x00 0x40000000
0x078000023F23C040 7 000000A8000000010001EC0056 Internal V ..S G 7 1 1 0x00 0x40000000
0x078000023F23F0C0 56 00000013000000002F68001D52 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F2456C0 56 00000111000000002F4D000752 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F24F800 39 00000132000000010001280056 Internal V ..S G 39 1 0 0x00 0x40000000
0x078000023F235840 56 00000000000000000000005453 APM Seq ..S G 56 1 0 0x00 0x40000000
0x078000023F23E680 101 000000F70000000100011F0056 Internal V ..S G 101 2 0 0x00 0x40000000
0x078000023F23D940 101 000000D9000000010001060056 Internal V ..S G 101 3 0 0x00 0x40000000
0x078000023F239480 39 000000D9000000010001060056 Internal V ..S G 39 1 0 0x00 0x40000000
0x078000023F230740 121 000000D9000000010001060056 Internal V ..S G 121 1 0 0x00 0x40000000
0x078000023EA098C0 13 000000D9000000010001060056 Internal V ..S G 13 1 0 0x00 0x40000000
0x078000023F230480 44 000000D9000000010001060056 Internal V ..S G 44 1 0 0x00 0x40000000
0x078000023F24E6C0 114 000000D9000000010001060056 Internal V ..S G 114 3 0 0x00 0x40000000
0x078000023F23B780 56 00000111000000002F4D000852 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F23D040 56 07800003316EF3A00000004953 APM Seq ..X G 56 1 0 0x00 0x40000000
0x078000023F23E900 56 000000D4000000010001A70056 Internal V ..S G 56 1 0 0x00 0x40000000
0x078000023F237CC0 56 00000006000000005324000852 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F23EB80 56 53514C4332463041E2F82CF041 Internal P ..S G 56 1 0 0x00 0x40000000
0x078000023EA067C0 13 0000008C000000010001E80056 Internal V ..S G 13 1 0 0x00 0x40000000
0x078000023F23B140 56 0000008C000000010001E80056 Internal V ..X W 13 1 0 0x00 0x40000000
0x078000023F230B00 56 000000D4000000020001A70056 Internal V ..S G 56 1 0 0x00 0x40000000
0x078000023F23EC80 56 000000D4000000020001A70156 Internal V ..X G 56 1 0 0x00 0x40000000
0x078000023F232640 56 00000006000000005324000A52 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F23F080 56 00000013000000002F68002152 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F233400 56 00000013000000002F68000A52 Row ..X G 56 1 0 0x20 0x40000000
0x078000023EE27A80 122 000000BA000000010001F50056 Internal V ..S G 122 2 0 0x00 0x40000000
0x078000023F2374C0 56 00000006000000005324000B52 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F239AC0 56 0001000007800003295A374043 CatCache ..X G 56 255 0 0x00 0x40000000
0x078000023EE2F140 122 00050A0C0000000743A9001A52 Row .NS G 122 1 1 0x00 0x00000002
0x078000023F238D40 121 000000FA000000010001E60056 Internal V ..S G 121 1 0 0x00 0x40000000
0x078000023F24E780 56 0000000500338F07295A374043 CatCache ..X G 56 255 0 0x00 0x40000000
0x078000023F242E80 56 00000006000000005324000C52 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F24E740 56 00000013000000002F68002352 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F233E40 101 53514C4C39463242C56D27F841 Internal P ..S G 101 1 0 0x00 0x40000000
0x078000023F2307C0 121 53514C4C39463242C56D27F841 Internal P ..S G 121 1 0 0x00 0x40000000
0x078000023F238480 39 53514C4C39463242C56D27F841 Internal P ..S G 39 1 0 0x00 0x40000000
0x078000023E48E080 13 53514C4C39463242C56D27F841 Internal P ..S G 13 1 0 0x00 0x40000000
0x078000023F238CC0 44 53514C4C39463242C56D27F841 Internal P ..S G 44 1 0 0x00 0x40000000
0x078000023F2403C0 114 53514C4C39463242C56D27F841 Internal P ..S G 114 1 0 0x00 0x40000000
0x078000023F242A00 56 00000006000000005324000D52 Row ..X G 56 1 0 0x20 0x40000000
0x078000023F2305C0 44 000000D9000000010001350056 Internal V ..S G 44 1 0 0x00 0x40000000
0x078000023EE28500 122 00050A0C000000000000000054 Table .IS G 122 1 1 0x00 0x00000002
0x078000023F242B00 56 00000013000000000000000054 Table .IX G 56 1 0 0x00 0x40000000
0x078000023F23EAC0 56 0000010C000000000000000054 Table .IS G 56 1 0 0x10 0x40000000
0x078000023F23E240 56 00000111000000000000000054 Table .IX G 56 1 0 0x00 0x40000000
0x078000023F23E840 56 00000005000000000000000054 Table .IX G 56 4 0 0x00 0x40000000
0x078000023F243B00 56 0000000A000000000000000054 Table .IX G 56 1 0 0x00 0x40000000
0x078000023F23C6C0 56 00000108000000000000000054 Table .IX G 56 1 0 0x00 0x40000000
0x078000023F243140 56 00000008000000000000000054 Table .IX G 56 2 0 0x00 0x40000000
0x078000023F23A500 56 00000006000000000000000054 Table .IX G 56 1 0 0x00 0x40000000
0x078000023F241100 56 0000010E000000000000000054 Table .IX G 56 1 0 0x00 0x40000000
Database Partition 0 -- Database BSSDB -- Active -- Up 6 days 15:13:50
Locks being waited on :
AppHandl [nod-index] TranHdl Lockname Type Mode Conv Sts CoorEDU AppName AuthID AppID
1216 [000-01216] 13 0000008C000000010001E80056 Internal V ..S G 1856174 Toad.exe ETL 130.30.3.201.24075.110311003226
1056 [000-01056] 56 0000008C000000010001E80056 Internal V ..X W 1069302 db2bp DB2INST1 *N0.db2inst1.110311075211
查看1216的agent相关信息
[DWE3:/db2home/db2inst1]db2 get snapshot for application agentid 1216|more
Application Snapshot
Application handle = 1216
Application status = UOW Waiting
Status change time = 2011-03-11 15:31:36.710810
Application code page = 1208
Application country/region code = 86
DUOW correlation token = 130.30.3.201.24075.110311003226
Application name = Toad.exe
Application ID = 130.30.3.201.24075.110311003226
Sequence number = 00013
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =
Connection request start timestamp = 2011-03-11 08:32:53.518264
Connect request completion timestamp = 2011-03-11 08:32:53.519331
Application idle time = 40 minutes 1 second
CONNECT Authorization ID = ETL
Client login ID = ADMINISTRATOR
Configuration NNAME of client = ZHANGXUN
Client database manager product ID = SQL09010
Process ID of client application = 2888
Platform. of client application = NT
Communication protocol of client = TCP/IP
Inbound communication address = 130.30.3.201 2910
Database name = BSSDB
Database path = /db2inst1/NODE0000/SQL00001/
Client database alias = BSSDB
Input database alias =
Last reset timestamp =
Snapshot timestamp = 2011-03-11 16:11:37.785090
The highest authority level granted =
Direct DBADM authority
Direct CREATETAB authority
Direct BINDADD authority
Direct CONNECT authority
Direct CREATE_NOT_FENC authority
Direct LOAD authority
Direct IMPLICIT_SCHEMA authority
Direct CREATE_EXT_RT authority
Direct QUIESCE_CONN authority
Indirect SYSADM authority
Indirect CREATETAB authority
Indirect BINDADD authority
Indirect CONNECT authority
Indirect IMPLICIT_SCHEMA authority
Coordinating database partition number = 0
Current database partition number = 0
Coordinator agent process or thread ID = 1856174
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 4
Priority at which application agents work = 0
Priority type = Dynamic
Lock timeout (seconds) = -1
Locks held by application = 4
Lock waits since connect = 0
Time application waited on locks (ms) = 0
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = 0
Total sorts = 19
Total sort time (ms) = 0
Total sort overflows = 0
Buffer pool data logical reads = 1708
Buffer pool data physical reads = 4
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 0
Buffer pool index logical reads = 1019
Buffer pool index physical reads = 8
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool index writes = 0
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Buffer pool xda writes = 0
Total buffer pool read time (milliseconds) = 114
Total buffer pool write time (milliseconds)= 0
Time waited for prefetch (ms) = 0
Unread prefetch pages = 0
Direct reads = 98
Direct writes = 0
Direct read requests = 16
Direct write requests = 0
Direct reads elapsed time (ms) = 13
Direct write elapsed time (ms) = 0
Number of SQL requests since last commit = 13
Commit statements = 2
Rollback statements = 10
Dynamic SQL statements attempted = 43
Static SQL statements attempted = 14
Failed statement operations = 2
Select SQL statements executed = 16
Xquery statements executed = 0
Update/Insert/Delete statements executed = 2
DDL statements executed = 0
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 0
Rows inserted = 0
Rows updated = 0
Rows selected = 330
Rows read = 19428
Rows written = 0
UOW log space used (Bytes) = 0
Previous UOW completion timestamp = 2011-03-11 09:04:34.914239
Elapsed time of last completed uow (sec.ms)= 0.042497
UOW start timestamp = 2011-03-11 15:31:36.676583
UOW stop timestamp =
UOW completion status =
Open remote cursors = 0
Open remote cursors with blocking = 0
Rejected Block Remote Cursor requests = 1
Accepted Block Remote Cursor requests = 13
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = 0.185553
Total System CPU Time used by agent (s) = 0.012515
Host execution elapsed time = 0.381100
Package cache lookups = 27
Package cache inserts = 9
Application section lookups = 48
Application section inserts = 17
Catalog cache lookups = 65
Catalog cache inserts = 0
Catalog cache overflows = 0
Catalog cache high water mark = 0
Workspace Information
Shared high water mark = 109067952
Total shared overflows = 0
Total shared section inserts = 13
Total shared section lookups = 17
Private high water mark = 23120
Total private overflows = 0
Total private section inserts = 4
Total private section lookups = 4
Most recent operation = Close
Cursor name = STATS_DYNH
Most recent operation start timestamp = 2011-03-11 15:31:36.690778
Most recent operation stop timestamp = 2011-03-11 15:31:36.710805
Agents associated with the application = 4
Number of hash joins = 13
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Statement type = Dynamic SQL Statement
Statement = Close
Section number = 97
Application creator = NULLID
Package name = SQLL9F2B
Consistency Token = AAAAANDY
Package Version ID =
Cursor name = STATS_DYNH
Statement database partition number = 0
Statement start timestamp = 2011-03-11 15:31:36.690778
Statement stop timestamp = 2011-03-11 15:31:36.710805
Elapsed time of last completed stmt(sec.ms)= 0.000074
Total Statement user CPU time = 0.011321
Total Statement system CPU time = 0.000043
SQL compiler cost estimate in timerons = 9360
SQL compiler cardinality estimate = 1
Degree of parallelism requested = 1
Number of agents working on statement = 0
Number of subagents created for statement = 1
Statement sorts = 1
Total sort time = 0
Sort overflows = 0
Rows read = 2
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Buffer pool data logical reads = 2
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 600
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Blocking cursor = YES
Dynamic SQL statement text:
SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, NON_UNIQUE, INDEX_QUALIFIER, INDEX_NAME, SYSIND.TYPE, ORDINAL_POSITION, COLUMN_NAME, CASE WHEN ASC_OR_DESC='I' THEN NULL ELSE ASC_OR_DES
C END AS ASC_OR_DESC, INTEGER(CARDINALITY) AS CARDINALITY, PAGES, FILTER_CONDITION FROM SYSIBM.SQLSTATISTICS SYSIND WHERE TABLE_SCHEM = 'MARTRPT ' AND TABLE_NAME = 'T_RPT_MG_REV_
02_M' AND ( NON_UNIQUE = 0 OR NON_UNIQUE IS NULL ) ORDER BY 4,7,5,6,8
Subsection number = 0
Subsection database partition number = 0
Subsection status = Completed
Execution elapsed time (seconds) = 0
Total user CPU time (sec.ms) = 0.000000
Total system CPU time (sec.ms) = 0.000000
Current number of tablequeue buffers overflowed = 0
Total number of tablequeue buffers overflowed = 0
Maximum number of tablequeue buffers overflowed = 0
Rows received on tablequeues = 2
Rows sent on tablequeues = 0
Rows read = 0
Rows written = 0
Number of agents working on subsection = 0
Subsection number = 1
Subsection database partition number = 0
Subsection status = Completed
Execution elapsed time (seconds) = 0
Total user CPU time (sec.ms) = 0.011002
Total system CPU time (sec.ms) = 0.000012
Current number of tablequeue buffers overflowed = 0
Total number of tablequeue buffers overflowed = 0
Maximum number of tablequeue buffers overflowed = 0
Rows received on tablequeues = 0
Rows sent on tablequeues = 1
Rows read = 2
Rows written = 0
Number of agents working on subsection = 0
Subsection number = 2
Subsection database partition number = 0
Subsection status = Completed
Execution elapsed time (seconds) = 0
Total user CPU time (sec.ms) = 0.000168
Total system CPU time (sec.ms) = 0.000015
Current number of tablequeue buffers overflowed = 0
Total number of tablequeue buffers overflowed = 0
Maximum number of tablequeue buffers overflowed = 0
Rows received on tablequeues = 0
Rows sent on tablequeues = 0
Rows read = 0
Rows written = 0
Number of agents working on subsection = 0
Subsection number = 3
Subsection database partition number = 0
Subsection status = Completed
Execution elapsed time (seconds) = 0
Total user CPU time (sec.ms) = 0.000151
Total system CPU time (sec.ms) = 0.000016
Current number of tablequeue buffers overflowed = 0
Total number of tablequeue buffers overflowed = 0
Maximum number of tablequeue buffers overflowed = 0
Rows received on tablequeues = 0
Rows sent on tablequeues = 0
Rows read = 0
Rows written = 0
Number of agents working on subsection = 0
Agent process/thread ID = 1856174
Database partition number = 0
Agent Lock timeout (seconds) = -1
Memory usage for agent:
Memory Pool Type = Other Memory
Current size (bytes) = 131072
High water mark (bytes) = 131072
Configured size (bytes) = 33822867456
Memory Pool Type = Application Heap
Current size (bytes) = 458752
High water mark (bytes) = 458752
Configured size (bytes) = 24772608
Agent process/thread ID = 774308
Database partition number = 0
Agent Lock timeout (seconds) = -1
Memory usage for agent:
Memory Pool Type = Other Memory
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 33822867456
Memory Pool Type = Application Heap
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 24772608
Agent process/thread ID = 1855668
Database partition number = 0
Agent Lock timeout (seconds) = -1
Memory usage for agent:
Memory Pool Type = Other Memory
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 33822867456
Memory Pool Type = Application Heap
Current size (bytes) = 65536
High water mark (bytes) = 131072
Configured size (bytes) = 24772608
Agent process/thread ID = 779146
Database partition number = 0
Agent Lock timeout (seconds) = -1
Memory usage for agent:
Memory Pool Type = Other Memory
Current size (bytes) = 131072
High water mark (bytes) = 131072
Configured size (bytes) = 33822867456
Memory Pool Type = Application Heap
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 24772608
三、处理
删除此应用会话
[DWE3:/db2home/db2inst1/fengsh]db2 "force application(1216)"
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
发现删除表操作立即执行成功
[DWE3:/db2home/db2inst1]db2 "drop TABLE MARTRPT.T_RPT_MG_REV_02_M"
DB20000I The SQL command completed successfully.
[DWE3:/db2home/db2inst1]
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/665930/viewspace-689346/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/665930/viewspace-689346/
扫一扫
781
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
