db2死锁分析与处理

QQ叫紫珊

　　  在数据库中，锁的主要功能是为了控制并发数据的完整性而引入的机制，在并发应用中出现锁现象并不可怕，锁现象通常分为死锁和锁等待两种情形。  
　　死锁是因为两个并发的进程或者线程同时各自占有一个资源，又需要占有对方资源，但又都各不相让造成的，这通常是因为程序在并发上考虑不周造成的。
　　锁等待则是数据库中最普通的情况，一各应用使用数据期间必然要加锁，防止其他进程或应用破坏数据，其他进程或应用在此期间不得不等待前一个应用释放锁。锁等待时间参数是可调的，但要视实际应用情况而定，比如在网络环境中，复杂应用环境，或者对实时性要求不高的环境中，可以将锁等待时间调大一些，有些情况要调小一些。锁等待不同于死锁，死锁属于程序并发不当，需要调整程序并发机制，锁等待则属于性能问题，可能需要调整程序的sql语句。
　　不管是DB2死锁还是锁等待，数据库都有相应参数可调，也有相应的工具可以捕获和分析，以下是锁处理的通常办法。
　　1.查看和更改与锁相关的主要配置参数
　　$db2 get db cfg
　　在参数列表中寻找DLCHKTIME和LOCKTIMEOUT两个参数。(grep -i "LOCKTIMEOUT")
　　－DLCHKTIME 单位是毫秒，是DB2死锁的间隔时间，假设该值为10000ms，则意味着每隔10秒钟检查一下当前数据库中有无死锁存在，如有死锁，会选择回滚其中的某一个事务，让另外一个事务完成交易。
　　－LOCKTIMEOUT单位是秒，是锁等待最长时间，超过该时间仍未获得锁，则返回错误。
　　设置提示：
　　－缺省情况下，LOCKTIMEOUT是-1，意味着锁等待时间无限期，这和实际应用需求一般是不太相符的，需要将其值设为大于0的一个数。可以将 LOCKTIMEOUT 设置为很短的时间值，例如 10 或 15 秒。在锁上等待过长时间会在锁上产生雪崩效应。
　　首先，用以下命令检查 LOCKTIMEOUT 的值:
　　db2 "get db cfg for DBNAME"
　　并查找包含以下文本的行:
　　Lock timeout (sec) (LOCKTIMEOUT) = -1
　　如果值是 -1，考虑使用以下命令将它更改为 15 秒(一定要首先询问应用程序开发者或供应商以确保应用程序能够处理锁超时):
　　db2 "update db cfg for DBNAME using LOCKTIMEOUT 15"
　　－DLCHKTIME时间通常要设得比LOCKTIMEOUT时间小一些，否则未等发现死锁，就会被以锁等待超时而返回错误。
　　2．查看当前并发应用
　　CLP方式：
　　db2 list applications
　　或db2 list applications show detail
　　或 db2 list applications for database dbname [ show detail]
　　该命令可以查看当前是否有多个应用在连接着数据库，从而排查是否有并发的存在。
　　注意Application Name 和Application Id两栏，Application Name栏列出了应用的名字，db2bp通常意味着目前有CLP在连接数据库，java则意味着可能有db2cc或用户自己的java应用在连接数据库，在application Id栏中可以看到这些应用来自于哪些机器，本机的就显示为 LOCAL + 用户名 + 开始连接上的时间，远程的就会显示为16进制的IP地址＋用户名＋开始连接上的时间。通过排查并发应用从而消除测试中不必要的锁现象。
　　3．查看和更改快照参数
　　如果在合理设置了DLCHKTIME和LOCKTIMEOUT参数仍然出现锁现象，可以查看快照或者创建事件监控器来分析原因。要采用快照，首先要打开快照开关

• 　　db2 get monitor switches
  
  

　　输出中将包含以下参数：
　　监控开关 数据库管理器参数 注释
　　BUFFERPOOL DFT_MON_BUFPOOL 缓冲区的读写情况和发生时间
　　LOCK DFT_MON_LOCK 锁持有，锁等待，以及DB2死锁的发生情况
　　SORT DFT_MON_SORT Heap的使用情况，排序性能
　　STATEMENT DFT_MON_STMT 语句起始时间，语句内容

• 　　TABLE DFT_MON_TABLE Measure of activity (rows read/written)  
  
  
• 　　UOW DFT_MON_UOW Start/end times, completion status  
  
  
• 　　TIMESTAMP DFT_MON_TIMESTAMP Timestamps  
  
  

　　为了观察快照中的锁和执行语句情况，一般把LOCK和STATEMENT选项设为ON，也可以酌情把其他开关打开，示例如下： db2 update monitor switches using lock on statement on
　　4．查看快照信息
　　-查看数据库管理器级别快照信息

• 　　db2 get snapshot for dbm
  
  

　　-查看数据库级别快照信息

• 　　db2 get snapshot for database on dbname
  
  

　　-查看应用级别快照信息

• 　　db2 get snapshot for application agentid appl-handler
  
  

　　注：appl-handler可以从list applicaitions的输出中得到
　　-查看表级别快照信息

• 　　db2 get snapshot for tables on dbname
  
  

　　注：需要把tables快照开关设为ON才会有作用
　　-查看锁快照信息

• 　　db2 get snapshot for locks on dbname
  
  

　　或

• 　　db2 get snapshot for locks on for application agentid appl-handler
  
  

　　-查看动态sql语句快照信息

• 　　db2 get snapshot for dynamic sql on dbname
  
  

　　5.使用事件查看器
　　可以使用时间查看器收集锁事件，SQL语句事件，从而根据事件分析锁原因。
　　事件类型
　　使用事件监控器，首先要选定所关注的事件类型，DB2中有很多事件类型，可以用于锁分析的通常会用到以下三种：

• 　　DEADLOCKS  
  
  
• 　　DEADLOCKS WITH DETAILS  
  
  
• 　　STATEMENTS  
  
  

　　步骤：
　　-创建事件监控器

• 　　create event monitor evmname for eventtype write to file ‘directory’
  
  

　　例：create event monitor mymonitor for deadlocks, statements

• 　　write to file ‘c:\temp’
  
  

　　-把事件监控器打开
　　接上例：

• 　　set event monitor mymonitor state 1
  
  

　　注：1为打开，0为关闭
　　事件监控器开始工作，当所有应用断掉连接后，将事件记录下来
　　-查看事件细节

• 　　db2evmon –path ‘c:\temp’
  
  

　　上述的相关内容就是对DB2死锁和锁等待两种情形的描述，希望会给你带来一些帮助在此方面。
　　补充：锁的简单处理
　　$ db2 connect to portaldb
　　  Database Connection Information
　　Database server        = DB2/LINUXX8664 9.7.6
　　SQL authorization ID   = DB2INST1
　　Local database alias   = PORTALDB
　　$db2 get snapshot for locks on portaldb|grep -i "Application handle"
　　Application handle                         = 46046
　　Application handle                         = 46013
　　Application handle                         = 45934
　　Application handle                         = 45776
　　Application handle                         = 26
　　Application handle                         = 45664
　　Application handle                         = 45598
　　强行终止锁
　　$db2 force application'('45598')'
　　或
　　$db2 "force application(45598)"
　　
　　监控运行时间长排序次数多读最多运行频率高的SQL
   要想查看这些SQL，可以通过表函数（DB2 V8）或系统管理视图（DB2 V9）来实现。
   在DB2 V9中增加了管理视图，可以如下使用：
查看执行时间最长的 5 个动态 SQL 语句：





select AVERAGE_EXECUTION_TIME_S , SUBSTR(STMT_TEXT,1,200) AS STMT_TEXTfrom SYSIBMADM.

TOP_DYNAMIC_SQL orderby AVERAGE_EXECUTION_TIME_S descfetch first 5 rowsonly;　　查看执行频率最高的 5 个动态 SQL 语句：





select NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS, SORTS_PER_EXECUTION,

SUBSTR(STMT_TEXT,1,200) AS STMT_TEXT from SYSIBMADM.

TOP_DYNAMIC_SQL ORDERBY NUM_EXECUTIONS descfetch first 5 rows only;　　查看排序次数最多的 5 个动态 SQL 语句：





select STMT_SORTS, SORTS_PER_EXECUTION, substr(STMT_TEXT,1,200) asSTMT_TEXT from SYSIBMADM.

TOP_DYNAMIC_SQL orderby STMT_SORTS descfetch first 5 rows only;　　在DB2 V8中增加了表函数，可以如下使用：
查看执行时间最长的 5 个动态 SQL 语句：





select TOTAL_EXEC_TIME/NUM_EXECUTIONS, SUBSTR(STMT_TEXT,1,200)

AS STMT_TEXT FROMTABLE( SNAPSHOT_DYN_SQL (CAST(NULLASVARCHAR(1)), CAST(NULLASINTEGER)))

as SNAPSHOT_DYN_SQL orderby TOTAL_EXEC_TIME/NUM_EXECUTIONS descfetchfirst 5 rows only;　　查看执行频率最高的 5 个动态 SQL 语句：





select NUM_EXECUTIONS, TOTAL_EXEC_TIME/NUM_EXECUTIONS, STMT_SORTS,

STMT_SORTS/NUM_EXECUTIONS as SORTS_PER_EXECUTION,

SUBSTR(STMT_TEXT,1,200) AS STMT_TEXT fromTABLE( SNAPSHOT_DYN_SQL (CAST(NULLASVARCHAR(1)),

CAST (NULLASINTEGER))) as SNAPSHOT_DYN_SQL ORDERBY NUM_EXECUTIONSdescfetch first 5 rows only;;　　查看排序次数最多的 5 个动态 SQL 语句：





select STMT_SORTS, STMT_SORTS/NUM_EXECUTIONS as SORTS_PER_EXECUTION,

substr(STMT_TEXT,1,200) as STMT_TEXT fromTABLE( SNAPSHOT_DYN_SQL (CAST(NULLASVARCHAR(1)),

CAST (NULLASINTEGER))) as SNAPSHOT_DYN_SQL orderby STMT_SORTSdescfetch first 5 rows only;　　如果发现了运行成本比较高的SQL，就要来优化这些SQL的执行效率，来降低持有锁的锁产生的资源消耗，进一步降低死锁和锁等待的产生。
　　注意：死锁是没法保证100%避免的，但可以做到尽量避免，在一定程度上减少死锁。死锁的频繁发生主要需要从应用角度入手，比如选择合适的隔离级别，是否对表采用乐观锁，以及将对于同一张表的操作放到不同的时间上执行等方法。