生产环境老是出现死锁,以前是经常宕机,把JVM调大后,不宕机了,数据库死锁还是挺严重,于是就研究了一下,下面记录一下日志:
查看了db2官网关于死锁监控
1. 首先建立一个死锁事件监控器
Session Monitor
db2 connect to sample db2 "create event monitor dlmon for tables, deadlocks with details write to file '/home/ibmpro/zhangna'"
命令执行后,出现错误: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL1614N An I/O error occurred when activating an event monitor. Reason code = "3". SQLSTATE=58030
执行:chmod 777 /home/ibmpro/zhangna后,正常
db2 "set event monitor dlmon state 1"
2:
通过 db2evmon 工具可以获得死锁信息的日志,并且把日志文件导入到本地机器的文件系统当中。在下面一节,我们将详细分析导出的日志文件。
db2 connect reset
db2evmon -path /home/ibmpro/zhangna > /home/ibmpro/zhangna/20160608.txt
more 20160608.txt
查找:Table of lock waited on
查找:Deadlocked Statement
另外,避免死锁,查询时候可以加上:with ur 另外,尽量把需要提交的数据提交
Current Mode : NS - Share (and Next Key Share)
Deadlock detection time: 2016-06-08 15:49:28.795826
Table of lock waited on : TSSO_TMP_SESSION
Schema of lock waited on : DB2INST2
Data partition id for table : 0
Tablespace of lock waited on : TAPP
Type of lock: Row
//共享锁
Mode of lock: NS - Share (and Next Key Share)
//在共享锁上请求排他锁
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 312684183559
Application Handle: 52066
Deadlocked Statement:
Type : Dynamic
Operation: Execute
Section : 3
Creator : NULLID
Package : SYSSN300
Cursor : SQL_CURSN300C3
Cursor was blocking: FALSE
Text : delete from TSSO_TMP_SESSION where SESSIONID = ?
List of Locks:
Lock Name : 0x0000000100008C4F00016B0056
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 0
Object Type : Internal - Variation
Data partition id : -1
Mode : S - Share
Lock Name : 0x0003041900000051F041000452
Lock Attributes : 0x00000000
Release Flags : 0x00000002
Lock Count : 1
Hold Count : 0
Lock Object Name : 351923142660
Object Type : Row
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSI_SVC
Data partition id : 0
Mode : NS - Share (and Next Key Share)
Lock Name : 0x000300080000004A1E01001552
Lock Attributes : 0x00000000
Release Flags : 0x00000002
Lock Count : 1
Hold Count : 0
Lock Object Name : 318330961941
Object Type : Row
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSSO_HOST
Data partition id : 0
Mode : NS - Share (and Next Key Share)
Lock Name : 0x0003031100000048CD6E000752
Lock Attributes : 0x00000000
Release Flags : 0x40000001
Lock Count : 1
Hold Count : 0
Lock Object Name : 312684183559
Object Type : Row
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSSO_TMP_SESSION
Data partition id : 0
Mode : X - Exclusive
Status : Converting
Current Mode : NS - Share (and Next Key Share)
Lock Name : 0x535953534E333030065C96FC41
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 0
Object Type : Internal - Plan
Data partition id : -1
Mode : S - Share
Lock Name : 0x00030419000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x00000003
Lock Count : 1
Hold Count : 0
Lock Object Name : 1049
Object Type : Table
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSI_SVC
Data partition id : 0
Mode : IS - Intent Share
Lock Name : 0x00030311000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x40000001
Lock Count : 2
Hold Count : 0
Lock Object Name : 785
Object Type : Table
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSSO_TMP_SESSION
Data partition id : 0
Mode : IX - Intent Exclusive
Lock Name : 0x00030008000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x40000003
Lock Count : 1
Hold Count : 0
Lock Object Name : 8
Object Type : Table
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSSO_HOST
Data partition id : 0
Mode : IS - Intent Share
Locks Held: 8
Locks in List: 8
Locks Displayed: 8
db2 connect reset
db2evmon -path /home/ibmpro/zhangna > /home/ibmpro/zhangna/20160608.txt
more 20160608.txt
查找:Table of lock waited on
查找:Deadlocked Statement
另外,避免死锁,查询时候可以加上:with ur 另外,尽量把需要提交的数据提交
Current Mode : NS - Share (and Next Key Share)
Deadlock detection time: 2016-06-08 15:49:28.795826
Table of lock waited on : TSSO_TMP_SESSION
Schema of lock waited on : DB2INST2
Data partition id for table : 0
Tablespace of lock waited on : TAPP
Type of lock: Row
//共享锁
Mode of lock: NS - Share (and Next Key Share)
//在共享锁上请求排他锁
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 312684183559
Application Handle: 52066
Deadlocked Statement:
Type : Dynamic
Operation: Execute
Section : 3
Creator : NULLID
Package : SYSSN300
Cursor : SQL_CURSN300C3
Cursor was blocking: FALSE
Text : delete from TSSO_TMP_SESSION where SESSIONID = ?
List of Locks:
Lock Name : 0x0000000100008C4F00016B0056
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 0
Object Type : Internal - Variation
Data partition id : -1
Mode : S - Share
Lock Name : 0x0003041900000051F041000452
Lock Attributes : 0x00000000
Release Flags : 0x00000002
Lock Count : 1
Hold Count : 0
Lock Object Name : 351923142660
Object Type : Row
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSI_SVC
Data partition id : 0
Mode : NS - Share (and Next Key Share)
Lock Name : 0x000300080000004A1E01001552
Lock Attributes : 0x00000000
Release Flags : 0x00000002
Lock Count : 1
Hold Count : 0
Lock Object Name : 318330961941
Object Type : Row
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSSO_HOST
Data partition id : 0
Mode : NS - Share (and Next Key Share)
Lock Name : 0x0003031100000048CD6E000752
Lock Attributes : 0x00000000
Release Flags : 0x40000001
Lock Count : 1
Hold Count : 0
Lock Object Name : 312684183559
Object Type : Row
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSSO_TMP_SESSION
Data partition id : 0
Mode : X - Exclusive
Status : Converting
Current Mode : NS - Share (and Next Key Share)
Lock Name : 0x535953534E333030065C96FC41
Lock Attributes : 0x00000000
Release Flags : 0x40000000
Lock Count : 1
Hold Count : 0
Lock Object Name : 0
Object Type : Internal - Plan
Data partition id : -1
Mode : S - Share
Lock Name : 0x00030419000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x00000003
Lock Count : 1
Hold Count : 0
Lock Object Name : 1049
Object Type : Table
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSI_SVC
Data partition id : 0
Mode : IS - Intent Share
Lock Name : 0x00030311000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x40000001
Lock Count : 2
Hold Count : 0
Lock Object Name : 785
Object Type : Table
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSSO_TMP_SESSION
Data partition id : 0
Mode : IX - Intent Exclusive
Lock Name : 0x00030008000000000000000054
Lock Attributes : 0x00000000
Release Flags : 0x40000003
Lock Count : 1
Hold Count : 0
Lock Object Name : 8
Object Type : Table
Tablespace Name : TAPP
Table Schema : DB2INST2
Table Name : TSSO_HOST
Data partition id : 0
Mode : IS - Intent Share
Locks Held: 8
Locks in List: 8
Locks Displayed: 8
各种锁:
共享锁(S锁):如果事务T对数据A加上共享锁后,则其他事务只能对A再加共享锁,不能加排他锁。
获准共享锁的事务职能读取数据,不能修改数据。
排他锁(X锁):如果事务T对数据A加上排他锁后,则其他事务不能在对A加任何类型的封锁。
获准排他锁的事务既能读取数据,也能修改数据。
数据库死锁的原因:若干事务相互等待对方释放封锁,就陷入了无限期等待状态,系统进入死锁。
预防数据库死锁的方法:1、要求一个事务必须一次性封锁所需要的所有数据(要么全成功,要么全部成功)
2、规定封锁数据的顺序,所有事务必须按照这个顺序实行封锁
解除数据库死锁的方法:允许死锁发生,然后解除它,如果发现死锁,则将其中一个代价较小的事务撤销,回滚这个事务,并释放此事务持有的封锁,使其他事务继续进行。
DB2支持的行锁如下所示:
名称缩写 全名 需要表锁最低级别 描述
S 共享锁(Share) IS 该行正在被读取,其他程序只能执行读操作
U 更改锁(Update) IX 某个程序正在读取并有可能修改该行,其他程序只能读取该行
X 排他锁(eXclusive) IX 该行正在被某个程序修改,其他程序不能访问该行
W 弱排他锁(Weak eXclusive) IX 一行被插入表后,该行会加上W锁,只有锁的拥有者可以修改该行,与X锁的不同在于该锁与NW锁兼容
NS 下一键共享锁(Next Share) IS 拥有者与其他程序都可以读取该行,但不能进行修改,当程序处于RS或者CS隔离级别下时,该锁可以代替S锁
NX 下一键排他锁(Next eXclusive) IX 一行的数据被插入到索引或者从索引被删除时,该行的下一行会被加上NX锁,锁的拥有者可以读该行的数据但不能修改。该锁与X锁类似,但与NS锁兼容
NW 下一键弱排他锁(Next Weak eXclusive) IX 一行的数据被插入到索引时,该行的下一行会被加上NW锁,锁的拥有者可以读但不能修改该行的数据,与X锁及NX锁类似,但与W锁以及NS锁兼容
默认情况下,DB2总是尝试获取行锁,但可以使用ALTER TABLE语句修改为总是获取表锁,也可以使用LOCK TABLE语句获取表锁。
避免死锁的方法
越早地考虑数据库设计中的并发性问题,就越可以提高代码执行的效率,降低程序开发和维护的成本,这里我们提出了一些避免死锁,提高应用程序并发性的方法。
- 设置隔离级别,根据应用程序的业务逻辑和数据完整性需求来决定合适的隔离级别,包括:RR,RS,CS,UR。该决定需要对应用程序需求和相关的业务规则具有基本理解
- 尽量避免锁升级,正确调整参数LOCKLIST, MAXLOCKS
- SQL0911N返回码68(LOCKTIMEOUT参数)的原因是锁等待超时,而SQL0911返回码2(DLCHKTIME参数)的原因则是因为死锁被强制回滚,避免这两种错误的方法就是合理设计数据库和建立合理的索引
- 尽快提交事务,不要在事务中加入不必要的执行时间过长的代码,比如大的代码循环和远程调用,或者一些没有用处的SELECT语句
- 应用程序的框架实现保证一旦发现SQL错误,立刻执行回滚事务,释放锁。
- 如果多个应用程序访问同一资源,最好以相同的次序访问。这样,即使前一个访问资源的应用程序会延迟其他应用程序的访问,也不会导致死锁的发生
- 设定外键索引,如果想删除父表中的行,就需要扫描多个子表中的多行数据,这样就需要占用多个子表的锁,我们可以通过在外键上建立索引来减少扫描子表的行数,否则若不建立索引,如果从父表中删除一行的时候,就需要扫描整个子表
在启动监听的时候,最常见的就是目录路径与读写权限的问题,以下内容可供参考,根据错误返回码做对应调整即可。
PS: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.messages.sql.doc/doc/msql01614n.html SQL1614N An I/O error occurred when activating an event monitor. Reason code = reason-code. Explanation An I/O error was detected when an event monitor was activated. >reason-code< is one of the following: 1 Encountered an unknown event monitor target type. 2 The Event monitor target path was not found. 3 Access to event monitor target path was denied. 4 Event monitor target path is not the name of a pipe. 5 No process has opened the event monitor target pipe for reading. 6 Encountered an unexpected I/O error. User response Where possible, fix the problem described by the reason code, and resubmit the SET EVENT MONITOR statement. sqlcode: -1614 sqlstate: 58030