达梦混合操作事务阻塞排查

在一些项目中经常有用户反馈为什么一些查询SQL会导致阻塞，在数据库中查询是不应该阻塞读写操作的，现根据下面示例模拟相关业务场景。

create table dmtest (id int, name varchar(10));

insert into dmtest values(1,'a');

insert into dmtest values(2,'b');

insert into dmtest values(3,'c');

commit;

update dmtest set name='d' where id=1;

select * from dmtest;

这里不要提交

打开另外一个窗口如下sql，该sql一直在执行中

delete from dmtest where id=1;

 查询v$trxwait存在阻塞，事务335435被335433阻塞

 SELECT * FROM V$TRXWAIT;

通过下面查询，的确可以看到有select阻塞了delete

select trx_id,sql_text,sess_id,state from v$sessions where trx_id in('335435','335433');

根据V$LOCK可以看到该事务335433有个排他锁（x),一般用于写操作，而锁粒度为事务锁（TID）， 类似行锁,以防止多个事务同时修改同一行记录。前面有个数据修改并且没有提交，然后又做了查询操作，从而导致上面select阻塞了delete现象

SELECT * FROM V$LOCK where trx_id= 335433

查看该事务是否有提交

SELECT t1.sql_text, t1.state, t1.sess_id

  FROM v$sessions t1, v$trx t2

 WHERE t1.trx_id = t2.id AND t1.state = 'IDLE' AND t2.status = 'ACTIVE'

 and t1.trx_id=335433

下面介绍两种方法来找到当前阻塞事务执行了哪些sql，定位到造成阻塞的源头

（1）通过V$SQL_HISTORY ，v$sessions 视图查询，可以看到是前面一个update没提交导致了阻塞，

select listagg2(top_sql_text||char(10))

 from  V$SQL_HISTORY sh,v$sessions block_sess

 where sh.TRX_ID=block_sess.trx_id and sh.sess_id=block_sess.sess_id

 and sh.TRX_ID=335433

注意：由于V$SQL_HISTORY只能保留10000条历史记录， 如存在长时间未提交事务或者系统业务负载高，可能查不到结果或者结果不全

（2）需要通过sql追踪日志去排查。也可以找到对应的sql

注意：需要开启sql追踪日志来监控所有的sql，在sqllog.ini 里面将MIN_EXEC_TIME改成0，如果业务负载高，会产生大量的日志文件。定位完成之后需要修改MIN_EXEC_TIME的值