ORACLE中死锁的知识点总结

死锁的概念

 

    什么是死锁呢？ 其实我们生活中也有很多类似死锁的例子。 我先举一个生活中的例子：过年回家，父亲买了一把水弹枪，儿子和侄子争抢着要先玩，谁也不让谁，拆开包装后，一个抢了枪， 一个逮住了子弹和弹夹。两个都争着要先玩，但是都互不相让。结果两个人都玩不了。如果儿子要先玩，就必须让侄子把子弹和弹夹给他，如果侄子要先玩，就必须让儿子把枪给侄子。他们就这样对峙了十几分钟，互不相让。 我出来调停，让儿子把枪先给侄子玩，每个人玩十分钟。然后两个人开开心心一起玩起来。其实这就是一个活生生的死锁（Dead Lock）的例子。

 

   我们再来看看数据库死锁的概念, 所谓死锁，是指两个会话，每个会话都持有另外一个会话想要的资源，因争夺资源而造成的一种互相等待的现象，此时就会出现死锁，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。Oracle对于“死锁”采取的策略是回滚其中一个事务，让另外一个事务顺利进行。

 

   英文关于deadlock的概念如下：

 

A deadlock occurs when a session (A) wants a resource held by another session (B) , but that session also wants a resource held by the first session (A). There can be more than 2 sessions involved but the idea is the same.

 

   

死锁的模拟

 

 

   上面了解了死锁的概念，接下来，我们先人工构造一个简单的死锁（Dead Lock）案例来加深理解一下死锁（Dead Lock），如下所示，我们先准备测试案例使用的表和数据，测试环境为Oracle Database 10g Release 10.2.0.5.0

 

SQL> create table dead_lock_test( id number(10), name varchar2(32));

Table created.

SQL> insert into dead_lock_test values(101, ‘kerry‘);

1 row created.

SQL> insert into dead_lock_test values(102, ‘ken‘);

1 row created.

SQL> commit;

Commit complete.

SQL> 

 

 

在会话1（SID为788）中执行下面SQL语句：

 

 

SQL> show user;

USER is "TEST"

SQL> select * from v$mystat where rownum=1; 

       SID STATISTIC#      VALUE

---------- ---------- ----------

       788          0          1

SQL> update dead_lock_test set name=‘kerry1_101‘ where id=101;

1 row updated.

SQL> 

 

 

 

 

然后在会话2（SID为770）中执行下面SQL语句：

 

SQL> show user;

USER 为 "TEST"

SQL> select * from v$mystat where rownum=1;

       SID STATISTIC#      VALUE

---------- ---------- ----------

       770          0          1

SQL> update dead_lock_test set name=‘kerry2_102‘ where id=102;

已更新 1 行。

SQL> update dead_lock_test set name=‘kerry2_101‘ where id=101;

 

 

 

 

如上所示，会话2（SID为770）更新id=101这条记录时，会话被阻塞了。然后我们在会话1（SID为788）中执行下面SQL语句：

 

SQL>  update dead_lock_test set name=‘kerry1_102‘ where id=102;

 

 

此时你会立马看到会话2（SID为770）出现ORA-00060错误，如下所示：

 

 

 

如果对上面的操作过程的流程有点不直观，那么可以参下面表格：

 

 

     

 

当然，如果你以下面这样的顺序更新，那么会话1就会出现ORA-0060的错误，会话1会被当做牺牲的会话进行回滚。

 

 

 

                                

 

 

此时在告警日志中就会出现trc文件。注意RAC环境和单机环境稍有不同。在RAC环境中，是由LMD（Lock Manager Daemon）进程统一管理各个节点之间的锁资源的，所以，RAC环境中trace文件是由LMD进程来生成的。

 

Tue Mar 28 15:36:30 CST 2017

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/SCM2/bdump/scm2_s000_15815.trc

 

trace文件的部分内容如下所示：

 

*** 2017-03-28 15:36:30.917

*** ACTION NAME:() 2017-03-28 15:36:30.917

*** MODULE NAME:(SQL*Plus) 2017-03-28 15:36:30.917

*** SERVICE NAME:(SCM2) 2017-03-28 15:36:30.917

*** SESSION ID:(770.8) 2017-03-28 15:36:30.917

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-0006002e-001e409f        15     770     X             16     788           X

TX-0007002c-001f6346        16     788     X             15     770           X

session 770: DID 0001-0010-00000002     session 788: DID 0001-000F-00000001

session 788: DID 0001-000F-00000001     session 770: DID 0001-0010-00000002

Rows waited on:

Session 788: obj - rowid = 00094900 - AACUkAABEAACLUeAAB

  (dictionary objn - 608512, file - 68, block - 570654, slot - 1)

Session 770: obj - rowid = 00094900 - AACUkAABEAACLUeAAA

  (dictionary objn - 608512, file - 68, block - 570654, slot - 0)

Information on the OTHER waiting sessions:

Session 788:

  sid: 788 ser: 9 audsid: 201878652 user: 132/TEST

    flags: (0xe1) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

    flags2: (0x8)

  pid: 16 O/S info: user: oracle, term: UNKNOWN, ospid: 15817

    image: oracle@getlnx14uat.xxxx.com (S001)

  O/S info: user: oracle, term: pts/2, ospid: 23047, machine: DB-Server.localdomain

            program: sqlplus@DB-Server.localdomain (TNS V1-V3)

  application name: SQL*Plus, hash value=3669949024

  Current SQL Statement:

  update dead_lock_test set name=‘kerry1_102‘ where id=102

End of information on OTHER waiting sessions.

Current SQL statement for this session:

update dead_lock_test set name=‘kerry2_101‘ where id=101

===================================================

 

 

死锁的检测

 

关于死锁的检测，对于单实例来说，基本上秒级完成，对于RAC环境，Oracle 10g基本上是1分钟, Oracle 11g是10秒，这个是通过隐含参数_lm_dd_interval控制的。这个参数可以修改，但是不建议修改。

 

COL NAME FOR A32;

COL KSPPDESC FOR A32;

COL KSPPSTVL FOR A32;

SELECT A.INDX,

        A.KSPPINM NAME,

        A.KSPPDESC,

       B.KSPPSTVL

FROM   X$KSPPI  A,

        X$KSPPCV B

WHERE  A.INDX = B.INDX

     AND LOWER(A.KSPPINM) LIKE  LOWER(‘%&PARAMETER%‘);

 

 

 

 

 

 

死锁的分析（DeadLock Troubleshooting)

 

 

以上面的例子来说，数据库一旦出现死锁，立马会在告警日志里面生成这样一条记录“ORA-00060: Deadlock detected. More info in file xxxxx”，那么从trc文件能分析出什么信息呢？ 下面我们以上面的例子来简单分析一下

 

 

其实trc文件里面最重要、最有用的信息是Deadlock graph。从这部分，我们可以分析得到下面一些有用信息：

 

 

1