ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 锁系统表导致无法drop表

锁系统表导致无法drop表

原创 Linux操作系统 作者:心飘 时间:2011-03-14 10:23:05 0 删除 编辑

锁系统表导致无法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       

 

查看1216agent相关信息

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    29
  • 访问量
    118785