Oracle数据表死锁的解决方法

一个简单的排查和解决方法

死锁时会报错：ORA-00060: deadlock detected while waiting for resource

对应的中文报错是：ORA-00060: 等待资源时检测到死锁

执行下面的SQL，查看被锁的表：

select object_name, machine, s.sid, s.serial#
  from v$locked_object l, dba_objects o, v$session s
 where l.object_id = o.object_id
   and l.session_id = s.sid

执行下面的SQL可以强制解锁

alter system kill session '277,1817'
--其中277对应上句SQL查出来的sid字段， 1817对应serial字段具体的值

具体的操作可以看下面的实例，更加直观的理解死锁，并且去解决死锁

通过一个实例操作来更深入的理解

参考自一个常见的ORA-00060死锁现象

在Oracle数据库中如果出现死锁现象，数据库就会报ORA-00060的错误代码，这种死锁现象通常都是应用逻辑设计出错导致的异常，和数据库本身的设计无关，现在通过实验模拟一个死锁现象：

模拟死锁现象

一定要自己实际动手去操作一下，也就花1个小时的时间，不过可以对死锁有一个很直观的认知！

创建一个用于测试的数据表，并且添加几条测试数据：

create table practice(uno varchar(8), uname varchar(20));

insert into practice values ('198', 'xm198-1');
insert into practice values ('198', 'xm198-2');
insert into practice values ('200', 'xm200-1');
insert into practice values ('200', 'xm200-2');
commit;

打开一个PLSQL，在PLSQL中打开两个Command Window执行下列更新顺序（下面的会话就是指Command Window）

会话1：执行对uno为198的字段更新，注意不执行commit;

SQL> update practice set uname = 'cj' where uno = '198';
2 rows updated

会话2：执行对uno为200的字段更新，注意不执行commit;

SQL> update practice set uname = 'hh' where uno = '200';
2 rows updated

会话1：再执行对uno为200的字段更新，注意不执行commit;，此时语句已经hang住（也就是卡住了，不像上面两次的执行会输出2 rows updated这样的结果信息），需要等到会话2发出commit或者rollback动作

SQL> update practice set uname = 'cj' where uno = '200';   ---会话1在这里hang住了

会话2：一旦执行下面的更新，会话2也会hang住，回到会话1就会发现会话1报错

SQL> update practice set uname = 'sdf' where uno = '198';

回到会话1，可以看到会话1报错信息

SQL> update practice set uname = 'cj' where uno = '200';
update practice set uname = 'cj' where uno = '200'
ORA-00060: 等待资源时检测到死锁

查询alert日志发现报错：ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/prod/udump/prod_ora_4273.trc.

详细解释一下这种情况的死锁，随便问一个人死锁是怎么产生的，懂点计算机知识的人就会说是循环等待，那么这个例子中怎么出现循环等待的呢：

• 会话1先去更新uno = '198'的记录，但是没有commit或者rollback，那么会话1就一直“占用” uno = '198'的记录

• 同样会话2先去更新 uno = '200'的记录，但是也没有commit或者rollback，那么会话2就一直“占用” uno= '200' 的记录

• 接着会话1又去尝试更新uno = '200' 的记录，但是这些记录已经被会话2占用了，所以就出现了会话1 hang住的情况，其实就是会话1在等待会话2“释放”它所“占用”的 uno = '200'的记录

• 接着会话2又去尝试更新 uno = '198' 的记录，这样就和会话1产生了循环等待

• 所以也就产生了死锁

• 数据库使用SQL修改数据时是要加锁的，一般是行级锁，所以上面所谓的占用其实就是对符合条件的行加锁，因为没有执行commit或者rollback，所以就一直加锁

• 这一点需要再去深入学习数据库锁、SQL的执行原理、数据库原理等知识，以更加深入的学习数据库知识，而不要只是停留在目前浅显的层面

• 另外就像上面所说的，如果自己开发的程序在运行过程中出现死锁的问题:

  • 这种死锁现象通常都是应用逻辑设计出错导致的异常，和数据库本身的设计无关

  • 所以需要检查自己的程序在涉及到数据库操作的方面是不是设计的有问题

  • 如果是一个简单的程序可能查起来很简单

  • 但是如果是一个大的项目，有很多的开发组都可能涉及到对数据库的操作，那么查起来可能就会比较难了

  • 这就需要自己有更大的更宏观的角度，能够对整个大的项目架构有一个把握，另外就是能够对数据库原理层面的知识有深入的理解和掌握

补充说明：

如果在会话2中第二次不执行上面的更新SQL，而是执行commit;或者rollback;那么会话1就不会报错

比如会话2执行

SQL> rollback;
Rollback complete

回去检查会话1，发现之前hang住的地方，现在已经解决了

SQL> update practice set uname = 'cj' where uno = '200';    --之前是hang在这里没有办法更新的，现在因为会话2执行rollback，所以会话1从hang住恢复过来了
2 rows updated

强制解锁

注意这里需要使用系统用户，因为一般用户是没有权限的，我是重新打开一个PLSQL，使用SYSTEM用户登陆进行下面的操作的，再在新的PLSQL中打开一个Command Window。之所以重新打开一个PLSQL，是为了保证能够不影响原有的PLSQL中登录用户的情况下，使用SYSTEM用户进行登录。当然新不新开PLSQL只是表面现象而已，没有必要深究什么！

在新的PLSQL的会话中，通过dba_blockers表中的HOLDING_SESSION字段可以查询到hang住会话的ID：

SQL> select * from dba_blockers;
HOLDING_SESSION
---------------
             76

使用v$session视图获取hang住会话的sid和serial#

SQL> select sid,serial#,username from v$session where sid in (select blocking_session from v$session);
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        76        271 TRADE

找到hang住的会话后，执行alter system命令kill掉相应的session就可以了：

SQL> alter system kill session '76,271' immediate;
System altered

检查之前的会话来确认一下

执行后上面的强制解锁之后，返回前一个PLSQL，检查其中的两个会话，会话1中的会话会自动被kill掉

在会话1执行查询SQL，会发现报错：

SQL> select * from practice;
Warning: connection was lost and re-established
UNO      UNAME
-------- --------------------
198      xm198-1
198      xm198-2
200      xm200-1
200      xm200-2

会话2中执行查询发现会话2的更改生效。在会话2中执行下面查询SQL，没有报错，其实当前

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      sdf
198      sdf
200      hh
200      hh

但是再在当前的PLSQL打开一个Command Window（叫会话3），执行查询语句，发现结果和会话2不一致

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      xm198-1
198      xm198-2
200      xm200-1
200      xm200-2

回到会话2，发现还没有提交，所以在提交后，再执行查询看结果

SQL> commit;
Commit complete

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      sdf
198      sdf
200      hh
200      hh

然后再回到会话3，执行查询语句，发现现在和会话2一样了

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      sdf
198      sdf
200      hh
200      hh

实际上，当出现死锁的情况，Oracle也会在一段时间后解锁。这种情况会在alert日志中记载下列信息：ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc.linux