db2 锁相关

原理：

  锁是数据库为了控制并发数据的完整性而引入的机制，在并发应用中出现锁现象并不可怕，锁现象通常分为死锁和锁等待两种情形。 

  死锁是因为两个并发的进程或者线程同时各自占有一个资源，又需要占有对方资源，但又都各不相让造成的，这通常是因为程序在并发上考虑不周造成的。 

  锁等待则是数据库中最普通的情况，一个应用使用数据期间必然要加锁，防止其他进程或应用破坏数据，其他进程或应用在此期间不得不等待前一个应用释放锁。锁等待时间参数是可调的，但要视实际应用情况而定，比如在网络环境中，复杂应用环境，或者对实时性要求不高的环境中，可以将锁等待时间调大一些，有些情况要调小一些。锁等待不同于死锁，死锁属于程序并发不当，需要调整程序并发机制，锁等待则属于性能问题，可能需要调整程序的sql语句。 

  锁升级

  lock escalation出现的情况：应用的锁请求超过允许该应用获得锁的最大数量。

  当发生lock escalation时，数据库管理器释放所有行锁，以释放内存，保持表锁。

  跟锁升级有关的参数：locklist，锁内存数量，以4k页为单位，用以存放锁信息；maxlocks，单一应用能使用的最大锁内存比例

  题外话：连接数据库：db2 connect to databasename; 启动数据库：db2start; 停止数据库：db2stop

 

锁类型：

锁状态确定了对锁的所有者允许的访问类型，以及对锁定数据资源的并发用户许可的访问类型。下面的列表说明了可用的锁状态，按照递增控制排序：

• 锁状态（模式）：  意向无（Intent None，IN）  

适用对象：  表空间和表  
描述：  锁的拥有者可以读取锁定表中的数据（包括未提交数据），但不能更改这些数据。在这种模式中，锁的拥有者不获取行级别的锁；因此，其它并发应用程序可以读取和更改表中的数据。 

• 锁状态（模式）：  意向共享（Intent Share，IS）  

适用对象：  表空间和表  
描述：  锁的拥有者可以读取锁定表中的数据，但不能更改这些数据。同样，因为锁的拥有者不获取行级别锁；所以，其它并发的应用程序仍可以读取和更改表中的数据。（当事务拥有表上的意向共享锁时，就在它所读取的每个行上进行共享锁定。）当事务不传达更新表中行的意图时，就获取这种锁。 

• 锁状态（模式）：  下一键共享（Next Key Share，NS）  

适用对象：  行  
描述：  锁拥有者和所有并发的事务都可以读（但不能更改）锁定行中的数据。这种锁用来在使用“读稳定性”或“游标稳定性”事务隔离级别读取的数据上代替共享锁。 

• 锁状态（模式）：  共享（S）  

适用对象：  表和行  
描述：  锁拥有者和任何其它并发的事务都可以读（但不能更改）锁定的表或行中的数据。只要表不是使用共享锁锁定的，那么该表中的单个行可以使用共享锁锁定。但是，如果表是用共享锁定的，则锁拥有者不能在该表中获取行级别的共享锁。如果表或行是用共享锁锁定的，则其它并发事务可以读取数据，但不能对它进行更改。 

• 锁状态（模式）：  意向互斥（Intent Exclusive，IX）  

适用对象：  表空间和表  
描述：  锁拥有者和任何其它并发的应用程序都可以读取和更改被锁定表中的数据。当锁拥有者从表读取数据时，它在所读取的每一行上获取一个共享锁，而在它更新的每一行上获取更新和互斥锁。其它并发的应用程序可以读取和更新锁定的表。当事务传达更新表中行的意图时，就获取这种锁。（SELECT FOR UPDATE、UPDATE ... WHERE 和 INSERT 语句传达更新的意图。） 

• 锁状态（模式）：  带意向互斥的共享（Share With Intent Exclusive，SIX）  

适用对象：  表  
描述：  锁拥有者可以读取和更改被锁定表中的数据。锁拥有者在它更新的行上获取互斥锁，但不获取它读取的行上的锁；因此，其它并发的应用程序可以读取但不能更新被锁定表中的数据。 

• 锁状态（模式）：  更新（Update，U）  

适用对象：  表和行  
描述：  锁的拥有者可以更新被锁定表中的数据，并且锁的拥有者在它所更新的任何行上自动获得互斥锁。其它并发的应用程序可以但不能更新被锁定表中的数据。 

• 锁状态（模式）：  下一键互斥（Next Key Exclusive，NX）  

适用对象：  行  
描述：  锁的拥有者可以读取但不能更新被锁定的行。当在表的索引中插入或删除行时，表中的下一行上将获得这种锁。 

• 锁状态（模式）：  下一键弱互斥（Next Key Weak Exclusive，NW）  

适用对象：  行  
描述：  锁的拥有者可以读取但不能更新被锁定的行。当向非目录表的索引插入行时，表中下一行上就获得这种锁。 

• 锁状态（模式）：  互斥（Exclusive，X）  

适用对象：  表和行  
描述：  锁的拥有者可以读取和更改被锁定的表或行中的数据。如果获取了互斥锁，则只允许使用“未提交的读”隔离级别的应用程序访问被锁定的表或行（多行）。对于用 INSERT、UPDATE 和／或 DELETE 语句操作的数据资源，将获取互斥锁。 

• 锁状态（模式）：  弱互斥（Weak Exclusive，WE）  

适用对象：  行  
描述：  锁的拥有者可以读取和更改被锁定的行。当向非目录表中插入行时，该行上将获得这种锁。 

• 锁状态（模式）：  超级互斥（Super Exclusive，Z）  

适用对象：  表空间和表  
描述：  锁的拥有者可以更改表、删除表、创建索引或删除索引。当事务尝试执行上述任何一种操作时，表上就自动获得这种锁。在除去这个锁之前，不允许其它并发事务读取或更新该表。 

相关修改：

 1．查看和更改与锁相关的主要配置参数。 

  CLP方式： db2 get db cfg 

  在参数列表中寻找DLCHKTIME和LOCKTIMEOUT两个参数。 

  －DLCHKTIME 单位是毫秒，是DB2检查死锁的间隔时间，假设该值为10000ms（默认值），则意味着每隔10秒钟检查一下当前数据库中有无死锁存在，如有死锁，会选择回滚其中的某一个事务，让另外一个事务完成交易。 通常设置的比LOCKTIMEOUT时间小一些，否则未等发现死锁，就会被以锁等待超时而返回错误，推荐使用默认值。

  －LOCKTIMEOUT单位是秒，是锁等待最长时间，超过该时间仍未获得锁，则返回错误SQL0911。默认为-1（永远不超时），推荐值为15s

更改示例（CLP方式） 

     db2 update db cfg using locktimeout 10 

 

  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 

  输出中将包含以下参数： 

  监控开关数据库管理器参数注释 

  BUFFERPOOLDFT_MON_BUFPOOL缓冲区的读写情况和发生时间 

  LOCK        DFT_MON_LOCK锁持有，锁等待，以及死锁的发生情况 

  SORT        DFT_MON_SORTHeap的使用情况，排序性能 

  STATEMENTDFT_MON_STMTsql语句信息，语句起始时间，语句内容 

  TABLE        DFT_MON_TABLEMeasure of activity (rows read/written) 

  UOW        DFT_MON_UOWStart/end times, completion status 

  TIMESTAMPDFT_MON_TIMESTAMPTimestamps 

  为了观察快照中的锁和执行语句情况，一般把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 

  步骤： 

  -创建事件监控器 db2 create event monitor evmname for eventtype write to file ‘directory’ 

    例：db2 create event monitor mymonitor for deadlocks, statements write to file ‘c:\temp’ 

  -把事件监控器打开 

    接上例： db2 set event monitor mymonitor state 1  注：1为打开，0为关闭 事件监控器开始工作，当所有应用断掉连接后，将事件记录下来 

  -查看事件细节 

    db2evmon –path ‘c:\temp’ 

 

建议：

  1．正确调整locklist，maxlocks，dlchktime和locktimeout等和锁有关的数据库配置参数（locktimeout最好不要等于-1）。如果锁内存不足会报SQL0912错误而影响并发。

  2．写出高效而简洁的SQL语句（非常重要）。

  3．在业务逻辑处理完后尽可能快速commit释放锁。

  4．对引起锁等待（SQL0911返回码68）和死锁（SQL0911返回码2）的SQL语句创建最合理的索引（非常重要，尽量创建复合索引和包含索引）。

  5．使用 ALTER TABLE 语句的 LOCKSIZE 参数控制如何在持久基础上对某个特定表进行锁定。检查syscat.tables中locksize字段，尽量在符合业务逻辑的情况下，每个表中该字段为"R"（行级锁）。

  6．根据业务逻辑使用正确的隔离级别（RR，RS，CS和UR）。

  7．当执行大量更新时，更新之前，在整个事务期间锁定整个表（使用 SQL LOCK TABLE 语句）。这只使用了一把锁从而防止其它事务进行这些更新，但是对于其他用户它的确减少了数据并发性。

  8. 尽量避免锁升级

 

锁定的处理：

  db2pd -db dbname -locks wait 查看当前等待的进程，动态

  锁定的一般处理方法：

  1. 查看数据库的是否有大量的锁等待: db2 get snapshot for database on databasename | grep(find) lock

  2. 利用db2pd 进行锁的定位:db2pd -db sample -locks showlocks wait;

  3. 找到持有该锁的事务:db2pd -d sample tansactions;(识别标志:AppHandl,TranHdl);

  4. 然后:db2pd -agents (根据AppHandl,找到Agentid); 

  5. db2 get  snapshot for application agentid xxxxx 定位SQL Statement;

  6. 找到相关责任人(SQL 语句)后,考虑调整SQL语句,或者配置一些相关的参数进行性能调整.(DB2_SKIPINSERTED ET.)

  7. 类似的可以调整locklist,maxlocks,locktimeout, dlchktime 等常用参数;如果调整了locklist,maxlocks依然不解决问题的,就要检查锁是否产生了升级,事务是否提交等.