db2 原因码68

大家好，我是讯享网，很高兴认识大家。 SQL0911N The current transaction has been rolled back because of a deadlock

or timeout. Reason code "68". SQLSTATE=40001’

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

db2 get snapshot for locks on databasename

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

或者：db2 get snapshot for all application >z 抓取当前数据库连接到Z文档

在Z文件里，用more命令查找Lock-wait

cat z | grep "ID of agent holding lock"

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.

…………………………

Application handle = 838

Application ID = GA47AA28.D60B.0154C

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.0194C

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 = 0x0DFF052

Lock Attributes = 0x00000000

Release Flags = 0x00000004

Lock Count = 1

Hold Count = 0

讯享网

Lock Object Name = ＃被锁对象名称

Object Type = Row ＃被锁对象类型

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

Table Schema = DB2INST1

Table Name = t_mytable1 ＃被锁的表名

Mode = X

Lock Name = 0xFF0000000

Lock Attributes = 0x00000000

Release Flags = 0x

Lock Count = 2

Hold Count = 0

Lock Object Name = 0

Object Type = Internal Variation Lock

Mode = S

Lock Name = 0x0FF052

Lock Attributes = 0x00000000

Release Flags = 0x00000002

Lock Count = 1

Hold Count = 0

Lock Object Name =

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.0 MYDB 1

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

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

DB2INST1 db2bp 825 *LOCAL.db2inst1.0 MYDB 1

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

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

DB2INST1 dbdaemon 877 *LOCAL.db2inst1.0 MYDB 1

相关推荐