解除被锁定的表

解除DB2数据库中表的死锁

某日无法查询DB2的数据库的某一张表，执行查询语句的后返回死锁或超时的错误:

SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001’

 其他的表实行数据库没有什么问题，只有一张或几张表出现这个问题，造成这个原因一般是由于表被锁造成的，也可以使用查看DB2数据库的建康状态来确认，处理方法见以下步骤。

 

步骤一：使用命令get snapshot来查询哪些进程锁了哪些表。命令如下：

db2 get snapshot for locks on databasename

db2inst1@HOST1:~> db2 get snapshot for locks on mydb

 

            Database Lock Snapshot

Database name                              = MYDB

Database path                              = /home/db2data/db2inst1/NODE0000/SQL00001/

Input database alias                       = MYDB

Locks held                                 = 20

Applications currently connected           = 21

Agents currently waiting on locks          = 0

Snapshot timestamp                         = 04/16/2009 14:59:29.185370

 

                …………………………

                …………………………

Application handle                         = 838

Application ID                             = GA47AA28.D60B.0154C5152621

Sequence number                            = 1432

Application name                           = javaw.exe

CONNECT Authorization ID                   = DB2INST1

Application status                         = UOW Waiting

Status change time                         = Not Collected

Application code page                      = 1208

Locks held                                 = 0

Total wait time (ms)                       = Not Collected

 

 

Application handle                         = 1076

Application ID                             = GA47AA28.GF12.0194C4113924

Sequence number                            = 0001

Application name                           = javaw.exe

CONNECT Authorization ID                   = DB2INST1

Application status                         = UOW Waiting

Status change time                         = Not Collected

Application code page                      = 1208

Locks held                                 = 8

Total wait time (ms)                       = Not Collected

 

List Of Locks   ＃在application handle紧跟后面出现list of locks表明该application handle锁了表或对象

 Lock Name                = 0x030039020DFF11000000000052

 Lock Attributes             = 0x00000000

 Release Flags               = 0x00000004

 Lock Count                  = 1

 Hold Count                  = 0

 Lock Object Name            = 1163533    ＃被锁对象名称

 Object Type                 = Row        ＃被锁对象类型

 Tablespace Name             = tbs_data    ＃被锁对象所在的表空间

 Table Schema                = DB2INST1

 Table Name                  = t_mytable1   ＃被锁的表名

 Mode                        = X

 

 Lock Name                   = 0xFF000000010000000100120056

 Lock Attributes             = 0x00000000

 Release Flags               = 0x40000000

 Lock Count                  = 2

 Hold Count                  = 0

 Lock Object Name            = 0

 Object Type                 = Internal Variation Lock

 Mode                        = S

 

 Lock Name                   = 0x0300390200FF11000000000052

 Lock Attributes             = 0x00000000

 Release Flags               = 0x00000002

 Lock Count                  = 1

 Hold Count                  = 0

 Lock Object Name            = 1163520

 Object Type                 = Row

 Tablespace Name             = tbs_data

 Table Schema                = DB2INST1

 Table Name                  = t_mytable2

 Mode                        = X

 

  …………………………………………………………………………………

 

步骤二：使用命令force来断开这些进行了死锁的进程来。命令如下：

db2 "force application (handle id)"

db2inst1@HOST1:~>db2 "force application (838)"

DB20000I  The FORCE APPLICATION command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.

db2inst1@HOST1:~>db2 "force application (1076)"

DB20000I  The FORCE APPLICATION command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.

 

步骤三： 使用命令list application查看是否已经断开了哪些进行了死锁的进程。命令如下：

db2inst1@HOST1:~> db2 list applications

 

Auth Id  Application    Appl.      Application Id                 DB       # of

         Name           Handle                                    Name    Agents

-------- -------------- ---------- ------------------------------ -------- -----

DB2INST1 db2bp          1330       *LOCAL.db2inst1.090416134941   MYDB    1   

DB2INST1 db2jccThread-8 85         GAB40153.G869.0120AF1F5A2E     MYDB    1   

DB2INST1 db2bp.exe      63         GA47AA28.BA0C.018346132405     MYDB    1   

DB2INST1 db2bp         825        *LOCAL.db2inst1.090416103954   MYDB    1   

DB2INST1 javaw.exe      1039       GA47AA28.F411.0194C4113304     MYDB    1   

DB2INST1 db2bp.exe      928        GA47AA28.L710.015844102651     MYDB    1   

DB2INST1 dbdaemon       877        *LOCAL.db2inst1.090408150559   MYDB    1    

 

原文：http://blog.csdn.net/xiaoxu0123/article/details/4993794