DB2数据库管理最佳实践笔记-锁和并发

锁主要分为两类：读锁和写锁

===================================
锁等待分析
#制造场景
事务1
事务2
update employee set bonus=salary*0.1 where job='MANAGER';
update employee set salary=salary*0.02;

select salary,bonus from employee where job='MANAGER'
select job from employee;
select salary,bonus from employee where job='CLERK'

#检查锁等待情形，分别查看mode(X) sts(g=granted拥有行锁) 定位持有排它锁的事务句柄trandhl，再获取对应tbspaceid tableid字段值。 
db2pd -db sample -locks wait showlocks

#确定所等待情形所涉及的表
select tabschema,tabname from syscat.tables where tbspaceid='2' and tableid='6'

#将事务句柄映射到应用程序，查看对应的apphandl字段值
db2pd -db sample -transactions

#获得关于锁等待涉及的更多应用程序信息，可使用实例级选项-agents，根据字段apphandl定位对应的应用程序
#分别关注apphandl userid字段
db2pd -agents

#获取关于应用程序的更多信息，关注字段Status C-AnchID(当前锚id) C-StmtID(当前语句uid) L-AnchID(最近last) L-StmtUID
db2pd -db sample -applications

#由上边的字段值来定位对应的持有锁的sql语句，等待锁的sql语句。
db2pd -db sample -dynamic

以上是对db2pd分布执行、分析说明，实际中可db2pd执行一次选择收集查看内容
#每隔15秒共执行40次(牛新庄推荐)
db2pd -db sample -locks wait showlocks -tra -agen -appl -dyna -file db2pd.out -repeat 15 40

#徐明伟 王涛推荐
db2pd -db sample -locks wait showlocks -tra -app -dyn -file db2pd.out

经过定位可采取应急措施将持有锁的进程强制关闭，后续可通过对表完善索引扫描表快速提交交易。
通过以上dbpd不一定能找到引起锁等待的sql语句。例如，如果一个事务执行增删改操作后，又进行了多次多操作，而db2pd只能抓出当前正在执行（C-AnchID/C-StmtID）或上一条（L-AnchID/L-StmtID），这时db2pd 抓取的结果就不是真正占有锁的SQL语句了。

在9.5版本，当使用db2pd无法捕获正确的锁等待sql语句时，可考虑设置较小的locktimeout数据库参数，这样当出现锁超时时，就可以用9.5版本新增的db2_capture_locktimeout注册变量来捕获了。

如9.1的锁超时捕获方法主要是db2pd结合db2cos回调脚本来实现，而9.5引入了locktimeout注册变量参数。9.1方法：
#修改db2cos脚本
echo "Lock Timeout Caught"  >>$logfile
if [! -n "$database"]
then
 db2pd -inst   >>$logfile
else
 db2pd -db $database -locks -tra -app -dyn >>$logfile

#通过db2pdcfg配置锁超时事件
db2pdcfg -catch locktimeout count=1

#模拟锁超时，分析db2cos输出文件。

9.5方法：
#首先设置参数，并创建事件监控器
db2set DB2_CAPTURE_LOCKTIMEOUT=ON
db2 update db cfg for sample using locktimeout=15
db2stop force
db2start
db2 connect to sample

mkdir locks
db2 drop event monitor dlockevm
db2 "create event monitor dlockevm for deadlocks with details history write to file '/home/db2inst1/locks'"
db2 set event monitor dlockevm state=1

#模拟锁超时
在第一个窗口中执行
db2 +c "insert into t1 values('ffff','ffff')"
db2 +c "select * from t1 where coll='ffff'"

在第二个窗口中执行
db2 +c "select * from t1 where coll='xxxx'"
sql0911n the current transaction has been rooled back because of a deadlock or timeout.Reason code "68".sqlstate=40001

这时检查<instance_home>/sqllib/db2dump目录，发现生成了db2locktimeout.0.72****
这个文件包含两部分内容
一部分为锁请求相关信息，包含了锁超时的语句，申请的锁类型等。
另一部分为锁拥有者相关信息，包含了当前锁的模式，还包含当前事务所执行的所有sql语句历史信息。

死锁的捕获与诊断分析
#创建事件监控器
cd /home/db2inst1
mkdir deadlock
db2 connect to db1
db2 "create event monitor dlockevm for deadlocks with details history write to file '/home/db2inst1/deadlock'"
db2 set event monitor dlockevm state=1

#模拟一个死锁发生，在第一个窗口中执行
db2 drop table t1
db2 drop table t2
db2 "create table t1(coll char(10))"
db2 "create table t2(coll char(10))"

db2 +c "insert into t1 values('aaa')"

#在第二个窗口中执行
db2 +c "insert into t2 values('bbb')"

#在第一个窗口中执行
db2 +c "select * from t2"

#在第二个窗口执行
db2 +c "select * from t1"

两个窗口命令输入后，同时执行。

死锁结果分析
#分析前，关闭事件监控器，并将结果输入到一个文本文件deadlock.txt
db2 flush event monitor dlockevm
db2 set event monitor dlockevm stato=0
cd ~/deadlock/
00000000.evt db2event.ctl

db2evmon -path /home/db2inst1/deadlock>deadlock.txt
打开deadlock.txt，找出引起死锁的sql语句。
…………

搜索关键词
deadlock event
deadlock id…… ---deadlock id

Rooled back Appl Id ---回滚的应用id
holding the lock ……---拥有锁的应用id

9.7锁事件监控器
db2 9.7引入了全新的锁事件监控器模型，使用统一的方法来捕获锁超时，锁等待和死锁，这就是 CREATE EVENT MONITOR FOR LOCKING
相关参数如下：
Lock timeout events  (MON_LOCKTIMEOUT)=NONE
Deadlock events   (MON_DEADLOCK)=WITHOUT_HIST
Lock wait events  (MON_LOCKWAIT)=NONE
Lock wait event threshold  (MON_LW_THRESH)=50000000

#重新设置其值，其详细含义参看DB2信息中心 （锁等待超过10秒即开始捕获相关信息）
db2 update db cfg for sample using MON_LOCKWAIT hist_and_values MON_DEADLOCK hist_and_values MON_LOCKTIMEOUT hist_and_values MON_LW_THRESH 10000

#创建锁监控器进行锁事件的捕获：
db2 "create event monitor lockevmon for locking write to unformatted event table(table locks)"
db2 set event monitor lockevmon state=1

#编译Java解析程序
cp /home/db2inst1/sqllib/samples/java/jdbc/db2evmonfmt.java /home/db2inst1/sqllib/samples/java/jdbc/DB2Evmonlocking.xsl /home/db2inst1

su - db2inst1
~/sqllib/java/jdk32/bin/javac db2evmonfmt.java #32位平台，如果是64位平台，修改jdk32为jdk64

#查看是否生成db2evmonfmt.class文件
ls -alt db2evmonfmt*

#模拟锁等、锁超时、和死锁，具体步骤省略。

#解析方法
~/sqllib/java/jdk32/jre/bin/java db2evmonfmt -d sample -ue locks -ftext -u db2inst1 -p password>db2locks.out

#除了通过db2evmonfmt解析未格式表，还可以
db2 "call sysproc.EVMON_FORMAT_UE_TO_TABLES('LOCKING',NULL,NULL,NULL,NULL,NULL,'RECREATE_FORCE',-1,'SELECT * FROM locks ORDER BY event_timestamp')"

这个存储过程的结果是一组关系表，以下是这几张表的含义。
LOCK_EVENT 对应发生的锁事件，每个事件对应一条记录。
LOCK_PARTICIPANTS 标识锁事件的参与者，每个参与的应用程序对应一条记录
LOCK_PATICIPANT_ACTIVITIES 包含了参与事件的应用程序曾经和当前正在执行的语句

这些表中都有一个XMLID列来唯一标识每个事件，这个列的内容格式如下：
<event_header>_<event_id>_<event_type>_<event_timestamp>_<partition>
例如：锁事件event_header统一为db2LockEvent，如果是锁等待，event_type为LOCKWEIT，如果是死锁，则为DEADLOCK,如果是锁超时，则为LOCKTIMEOUT。

#可通过sql语句对锁相关事件进行查询：
db2 "select substr(lp.xmlid,1,64),lp.participant_no,lp.participant_type,lp.APPLICATION_HANDLE,lp.paticipant_no_holding_lk,lpa.ACTIVITY_ID,lpa.ACTIVITY_TYPE,varchar(lpa.stmt_text,50) as STATEMENT
from lock_participants lp,lock_participant_activities lpa
where lp.xmlid=lpa.xmlid and lp.participant_no=lpa.participant_no order by lp.xmlid desc,lp.PARTICIPANT_NO,lpa.ACTIVITY_ID"

#以下语句用来统计各类事件发生的次数
db2 "select substr(event_type,1,20) AS EVENT_TYPE,count(*) AS COUNT from LOCK_EVENT group by EVENT_TYPE"

#以下语句用来统计当天各类事件发生的次数
db2 "select substr(event_type,1,20) AS EVENT_TYPE,count(*) AS COUNT from LOCK_EVENT where date(event_timestamp)=(current date) group by event_type"

对格式化表（UE表）的维护，有如下建议：
创建独立的表空间，考虑到内联LOB的高效率，建议创建独立的pagesize为32页的表空间
当锁事件发生频繁的时候，会导致UE表增长很快，建议定期删除不需要的数据。注意：
在删除之前需要首先通过set……state=0将事件监控器先关闭，否则会出现锁等导致数据无法删除。

当不需要锁事件监控器时，可以通过drop命令删除。但删除事件监控器并不会删除 UE表，UE表必须通过手工删除。如果没有删除UE表，以后再创建事件监控器时，不能再使用同样的UE表。

锁和并发调优
减少锁从应用层面上和数据库层面有哪些建议：
1）在应用程序设计时尽可能采用最弱的隔离级别，隔离级别从弱到强依次为：UR-CS-RS-RR。隔离级别越低，锁的范围和持久性就越低，并发性就越高。如果不能从整个应用改变隔离级别，可考虑在sql语句级别通过with设置隔离级别，如select……form……with UR表示通过UR隔离级别查询数据。

2）在应用程序物理设计中尽量避免长事务，尽快提交事务，释放锁资源。

3）数据库物理设计优化，比如针对某些sql语句在表上创建合适的索引。需要搜索的行数越少，需要的行锁就越少。

4）sql语句调优，sql语句执行的越快，潜在占有锁的时间就越短。

5）三个锁注册变量：DB2_EVALUNCOMITTED DB2_SKIPINSERTED DB2_SKIPDELETED。在应用允许的范围内，这3个参数可以减少锁的行数，大大提高并发性能。

DB2_EVALUNCOMITTED---验证未提交的数据
DB2_SKIPINSERTED---跳过插入行
DB2_SKIPDELETED---跳过删除行

后两个参数实际上是某种形式的脏读，以上参数建议在开发阶段就与业务部门确认是否可用，否则上线后再更改，需要对应用进行全面测试。

设置方法：
db2set DB2_EVALUNCOMMITTED=ON
db2set DB2_SKIPINSERTED=ON
db2set DB2_SKIPDELETED=ON

前面我们提到了4种隔离级别，分别是UR CS RS RR，其中RR RS由于锁粒度较大，对并发的影响比较大，在实际生产中使用并不多，UR会造成脏读，最多用于sql语句的级别，而CS作为默认的隔离级别使用比较多。CS游标稳定性隔离级别（在游标所在的行加锁，读完改行就释放锁，有另外一个事物正在进行增删改操作，读取就得需等待，知道前者操作结束。这种锁机制会对系统的并发性造成影响，也是被oracle爱好者诟病的地方之一
DB2 9.7引入了的当前已落实（Currently Committed ,CC）机制从根本上解决了这个问题。具体机制是锁管理器和日志。我们知道，DB2通过日志和锁来保证食物的一致性，比如一个update操作，DB2会将更改前和更改后的数据写入日志，同时在对应好行上加。当启用了CC机制，DB2会在每个行锁上添加一个标识，这个标识是以下几个值的其中之一。

No infomation---表示记录已经加锁，按传统的CS隔离级别机制，在当前行加读锁
Uncommitted insert identifier---表示这行是新加入的行，还没有提交。读取时会忽略改行
Log information---表示这行没有提交，包含了当前已落实数据的日志LSN，从日志文件或日志缓冲池中获取已经提交的数据，即更改之前的数据（before image）

当前已落实 从日志中获取已落实版本的数据，DB2首先从日志缓冲区中查找数据，当更新事务仍处于活动状态时，已落实版本数据会处于日志缓冲区或磁盘上的活动日志文件中。由于locklist包含日志信息，DB2不需要对所有日志文件进行搜索，而直接访问合适的日志文件。当找不到相关记录时，将切换到 当前已落实 不启用的状态，即读操作会等待写操作落实。需要注意的是，启用 当前已落实 特性需要更多的日志表空间。

当前已落实（CC）机制是通过CUR_COMMIT数据库参数来配置的，在默认情况下，这个参数是开启的。如果从低版本升级到高版本时是关闭的，更改该参数需要断开所有连接才会生效。

db2 get db cfg for sample|grep CUR_COMMIT

实例模拟未启用 当前已落实 ：
db2 update db cfg for sample using CUR_COMMIT OFF
db2 force application all

db2 connect to sapple

db2 "create table t1(id int,name char(10))"
db2 "insert into t1 values(1,'aaa'),(2,'bbb'),(3,'ccc')"
db2 commit

然后执行以下，但不提交
db2 +c "update t1 set name='new' where id=2"

这时打开第二个窗口检索id=2的数据，出现锁超时
db2 "select * from t1 where id=2"

实例模拟启用 当前已落实 ：
db2 update db cfg for sample using CUR_COMMIT ON

db2 force applications all

db2 connect to sample

db2 "select * from t1"

继续执行，但不要提交
db2 +c "update t1 set name='new' where id=2"

这时打开第二个窗口检索id=2的数据，出现锁超时
db2 "select * from t1 where id=2"

结果可查询更新前的数据