一、什么是阻塞、死锁
1、什么是堵塞
数据库中的阻塞指sql语句执行遇到所需资源不满足,挂起等待所需资源,无法正常往下继续执行。
常见情况为第一个事务占有资源没有释放,而第二个事务需要获取这个资源才能继续执行。如果第一个事务一直没有提交或者回滚完成事务,第二个事务会一直等待下去,直到第一个事务释放该资源为止。
2、什么是死锁
死锁是两个事务都在等待对方持有的资源锁,要等对方释放持有的资源锁之后才能继续工作。但是两个都在互相等待对方完成,而以目前这种状态,双方都完成不了,陷入死循环了。
- 阻塞分析与排查
阻塞发生在数据库中,但是这种情况并不是数据库的故障,是数据库保护数据的一种机制。大部分是由应用程序BUG产生的,需要调整程序的逻辑结构,尽量形成短事务,快速提交,避免锁占用时间过长,阻塞其他事务。不要将其他无关操作放到容易引起阻塞的模块。
- insert 的阻塞
insert 发生阻塞的唯一情况是:当多个事务同时insert 有主键或者唯一索引的表,并且insert的主键或者唯一索引字段的内容相同。
因此,当insert 发现有阻塞的情况下,检查insert 的表主键或者唯一索引字段,根据insert字段内容 发给应用排查业务语句。
此时会话1提交后,会话2会报唯一性冲突, 会话1回滚的话 会话2能正常执行。
2、updat、delete的阻塞
Updat或者delete 的记录,已经被修改或者删除 但没提交的情况,将会发生阻塞,直到占用的事务提交或者回滚。
需要注意的是,更新删除是以行来锁定的。
两个事务,更新同一行但不同列的情况是会阻塞的。
事务1更新的是多行,事务2更新的行包含在事务1的多行中,事务2也是被阻塞。
事务1更新的行,事务2去删除相同的行也是阻塞,update也是能阻塞delete。
3、多语句事务下阻塞的排查
Session1 执行 更新语句后不提交,再执行多条查询语句。Session2 更新对应的行发生阻塞
通过查询阻塞的sql语句。这里显示 select 阻塞了update,其实查询是不能堵塞更新的。是 该事物前面执行的 update阻塞了后面的更新,session视图显示session 1的最后执行语句。
实际环境中,阻塞的事务并不是已知的,需要根据V$SQL_HISTORY或者sql日志来查看。
当 INI 参数 ENABLE_MONITOR=1 V$SQL_HISTORY可以查询历史执行的语句。
根据堵塞的session 和事务id可以查询执行的语句,结合被阻塞的 sql语句,判断 堵塞的语句为update 这条语句。
程序的语句一般是带变量传输进sql语句的。如果要确认变量的语句,只有开启全部sql语句的跟踪,才能确定了。开启跟踪sql语句才会在日志里记录
SP_SET_PARA_VALUE(1,'SVR_LOG',1); 参数开启。
修改 sqllog.ini 的 SQL_TRACE_MASK = 1 记录全部的sql语句,
如之前已经开启跟踪sql语句,只修改了配置,则可以用系统函数SP_REFRESH_SVR_LOG_CONFIG(); 重新读取配置文件。
通过事物id查询到对应的语句。
4、堵塞的处理
查明阻塞原因后,如需手动终止阻塞的语句,可以在数据库层可以通过 SP_CLOSE_SESSION(SESS_ID);
程序逻辑的错误还是要程序修复或者暂时关闭该功能,否则数据库层面kill了, 程序还是会不断发起。
三、死锁分析与排查
下图中,session 1 的语句2执行成功,但是不提交, session2 的语句1执行成功,不提交。
此时 10000009 和10000011 两行已经被锁定。 session 1 的语句3执行后只能等待 session2 的提交或者回滚。 Session 2执行语句4 删除10000011 这一行数据,这一行现在已经被session 1锁定,session2 必须等待session1的提交或者回滚。但是此时session 1又是在等待session 2的提交或者回滚。
因此 session 2 执行 delete 10000011 这一行数据时 报错提示死锁。
通过查询V$DEADLOCK_HISTORY 视图,
确定是否有死锁发生过,以及发生的时间和最后的语句。
通过事物id 去检查sql跟踪日志。确定session2 执行的语句和等待的事务。
通过session 2 等待的事务id查询 sql跟踪日志,看到session 1有一条update语句没提交,第二条语句 在等待session 2 的提交或者回滚。
至此 分析了完整的事务信息和死锁规则,提交应用分析业务场景,从而优化处理逻辑,消除或者降低死锁频率。
四、总结
我们可以通过查询所有等待事件的视图V$SYSTEM_EVENT,通过等待次数和等待时间来确认阻塞和死锁的情况。