DB2: 解除被锁定的表。

蔚落 2022-08-10 09:50 206阅读 0赞

**解除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

DB2: 解除被锁定的表。

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关大写锁定怎么解除(笔记本大写锁定怎么解除)

相关解除账户锁定，解除账户过期锁定

相关 db2表分区

相关主页解除锁定

相关 DB2: 解除被锁定的表。

相关有关数据库表被锁定的问题

相关 Hbuilder MUI 锁定和解除锁定屏幕方向

相关 DB2表被锁死，如何解锁

相关 db2解除死锁实例

相关 DB2表空间