oracle死锁导致的数据库卡死情况处理

DBA狗剩儿

已于 2024-01-24 15:30:58 修改

阅读量2.8k

点赞数 1

分类专栏：运维日常文章标签：数据库 oracle database

于 2022-01-10 17:03:49 首次发布

本文链接：https://blog.csdn.net/weixin_41607523/article/details/122414368

版权

运维日常专栏收录该内容

84 篇文章 11 订阅

订阅专栏

项目场景：

客户的一台oracle 19c rac数据库，其中的一个实例出先hang死的情况，严重影响业务访问使用！应用人员无法操作数据库进行杀死死锁的session，但未起到效果。

问题描述：

接受问题处理第一步还是先观察后台alert日志，发现有ORA-00060错误，并伴随着十几秒很快频率的redo切换，并生成了大量trc文件，数据库redo大小是2G。
根据开发人员的描述是他们修改了一个存储过程，运行存储工程导致，但现在数据库hang死业务人员无法编译存储过程。
所以思路为：优先杀死相应session，再查看trc文件定位死锁位置！
但杀死session后死锁还是存在，OS层杀死对应pid后数据库可以操作！

原因分析：

Oracle上的死锁一般出现于“行级锁”的环形依赖情况下：

有记录A、B，事务T1、T2，现在T1、T2并发执行update（或delete）A+B
事务T1操作的顺序为A-B，正常情况下会先后锁住A和B
事务T2操作的顺序为B-A，正常情况下会先后锁住B和A
T1、T2并发执行，T1锁住A 同时 T2锁住B；
T1操作完A想要锁住B，但B已被T2锁住，T1等待中；T2操作完B想要锁住A，但A已被T1锁住，T2等待中；
由于T1、T2都在等待又都不释放，因此造成死锁。

当死锁越来越多的时候，数据库连接被耗尽，不再能接受新的连接；数据库服务器的CPU和内存也可能会不够用了……程序猿就悲剧了。

解决办法：

在事务开始时或开始前先对操作数据按统一规则排序。

例如：按A-B顺序排序，使得T1、T2操作的顺序都为A-B，假设T1、T2并发执行时T1锁住了A；则T2只能等待A的锁了，B就没有机会被T2锁住了；这样T1就可以顺利锁住B，并顺利执行；当T1执行完成，释放A、B，T2就可以继续执行了。

从线上死锁的情况来看，主要发生在以下两种场景：

① 在同一个事务中无序的update或delete多条记录，事务并发执行可能会有死锁。
② 没有事务，但执行单条update或delete SQL时，如果update或delete所影响的记录数大于1，也有可能出现死锁。

对于第①种场景可以使用前述的解决办法处理，即先排序后执行；

对于第②种场景的解决办法如下：

先用select查询出需要操作的记录主键，按主键排序，再通过主键一条一条迭代执行。

不过将一条语句变成多条语句执行会破坏原有的事务（JDBC默认事务：一条SQL语句即一个事务），无法保证操作的原子性了，这就需要将这些操作放入一个事务中。或者可以使用 in 语句将其改造成一条SQL，例如：update … where id in ( 1,2,3,4… ) …，这样效率会比多条SQL语句高一些，而且 in 是可以命中索引的。

解决方案命令：

死锁查询：

select s.username,l.object_id, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;

在这里插入图片描述

Username：死锁语句所用的数据库用户；
SID: session identifier， session 标示符，session 是通信双方从开始通信到通信结束期间的一个上下文。
SERIAL#: sid 会重用，但是同一个sid被重用时，serial#会增加，不会重复。
Lockwait：可以通过这个字段查询出当前正在等待的锁的相关信息。
Status：用来判断session状态。Active：正执行SQL语句。Inactive：等待操作。Killed：被标注为删除。
Machine：死锁语句所在的机器。
Program：产生死锁的语句主要来自哪个应用程序。

查看引起死锁的语句：

select sql_text from v$sql where hash_value in   (select sql_hash_value from v$session where sid in  (select session_id from v$locked_object));

kill掉死锁：

通过死锁查询得到对应SID、SERIAL#，使用有权限的用户执行：

alter system kill session ‘SID, SERIAL#’;

如果未能成功释放死锁该如何处理：

Kill掉死锁后我们再查询下死锁：

select s.username,l.object_id, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;

那么有可能死锁的status状态变为了killed，但是此状态是无法释放资源的，那就需要通过SID去找到对应的PID进行系统层面的kill。

查V$SESSION视图:
SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID'
查V$PROCESS视图：

SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';

KILL -9 刚才查出的SPID