论坛 产品库 视频 专题 CIO俱乐部 Windows8 实验室 CMO俱乐部 案例

解除DB2数据库中表的死锁方法

发布时间:2013-05-09 00:00:00 来源:比特网 作者:悠虎
关键字: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


比特微信账号
比特微信账号

微信扫一扫
关注Chinabyte

返回首页 长微博 返回顶部