db2死锁问题

生产环境老是出现死锁，以前是经常宕机，把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

各种锁：

共享锁（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