内容简介：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 错误，如下所示：
如果对上面的操作过程的流程有点不直观，那么可以参下面表格：
+--------------------------------------------+--------------------------------------------+
| Session 1 (sid=788) | Session 2 (sid=770) |
+--------------------------------------------+--------------------------------------------+
| update dead_lock_test | |
| set name='kerry1_101' where id=101; | |
+--------------------------------------------+--------------------------------------------+
| | update dead_lock_test |
| | set name='kerry2_102' where id=102; |
+--------------------------------------------+--------------------------------------------+
| | update dead_lock_test |
| | set name='kerry1_101' where id=101;|
+--------------------------------------------+--------------------------------------------+
| update dead_lock_test | |
set name='kerry2_102' where id=102; | |
+--------------------------------------------+--------------------------------------------+
| |ORA-00060: deadlock detected |
| |while waiting for resource |
+--------------------------------------------+--------------------------------------------+
当然，如果你以下面这样的顺序更新，那么会话 1 就会出现 ORA-0060 的错误，会话 1 会被当做牺牲的会话进行回滚。
+--------------------------------------------+--------------------------------------------+
| Session 1 (sid=788) | Session 2 (sid=770) |
+--------------------------------------------+--------------------------------------------+
| update dead_lock_test | |
| set name='kerry1_101' where id=101; | |
+--------------------------------------------+--------------------------------------------+
| | update dead_lock_test |
| | set name='kerry2_102' where id=102; |
+--------------------------------------------+--------------------------------------------+
| update dead_lock_test | |
| set name='kerry1_102' where id=102; | |
+--------------------------------------------+--------------------------------------------+
| | update dead_lock_test |
| | set name='kerry2_101' where id=101; |
+--------------------------------------------+--------------------------------------------+
|ORA-00060: deadlock detected | |
|while waiting for resource | |
+--------------------------------------------+--------------------------------------------+
此时在告警日志中就会出现 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 ： 产生死锁的两个会话信息
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
从上面可以看到 Blocker （ s) 与 Waiter （ s) 的相关信息
Resource Name ： 被持有或等待的锁资源名字
锁资源名字由三部分组成 Type-ID1-ID2 ， ID1 和 ID2 代表的意思由锁类型决定。
具体可以参考 v$lock_type
process : V$PROCESS.PID
session : V$SESSION.SID
holds : 锁持有的模式（ Mode the lock is held in ）
waits : 锁等待的模式（ Mode the lock is requested in (waiting for) ）
解读以上死锁的案例：
SID 770 (Process 15) 以排它模式持有锁： TX-0006002e-001e409f ，以排它模式请求锁： TX-0007002c-001f6346 。
SID 788 (Process 16) 以排它模式持有锁： TX-0007002c-001f6346 ，以排它模式请求锁： TX-0006002e-001e409f 。
这一段可以看到， 778 阻塞了 770 ， 然后 770 又阻塞了 778 刚好构成了死锁的条件。这里要看生成的记录是两行还是一行，是 TX 还是 TM ，如果只有一行那么说明是同一个 SESSION ，可能是自治事务引起的死锁。
2 ：死锁发生在那个对象？
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)
3 ：会话的的机器、应用程序等信息
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@xxxxxx.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
从上面我们可以看到会话 788 是从机器 DB-Server.localdomain 上的 SQL*Plus 应用程序发出的 SQL ，如果是正式环境，你会看到相关的机器和应用程序名称。这个会话最后执行的 SQL 语句为 update dead_lock_test set name='kerry1_102' where id=102 。
另外一个会话执行的最后语句为 update dead_lock_test set name='kerry2_101' where id=101 ，但是如何找到对应的机器、应用程序信息呢？
如下截图所示，我们在 PROCESS STATE 部分，找到对应的 SID 770 的事务，可以看到 user ， term 、 machine 、 program 信息。剩下的事情，就是你和开发人员分析脚本，缕清细节，然后如何避免死锁的问题。
死锁的分类
死锁如何分类呢？在 Metalink 上这篇文章中 "How to Identify ORA-00060 Deadlock Types Using Deadlock Graphs in Trace ( 文档 ID 1507093.1)" 有关于死锁的分类：如下所示：
| "Key Signature" |
Lock Type |
Requested |
Deadlock Graph |
Likely |
Comments |
| Type TX Lock Requesting Mode X (6) |
TX |
X(6) |
TX
X
X TX |
Application |
TX Lock Held in Mode X (6) Requesting Mode X (6) |
| Type TM Lock Requesting Mode SSX (5) |
TM |
SSX (5) |
TM
SX SSX
SX
SSX TM |
Missing Index on Foreign Key (FK) Constraint |
TM Lock Held in Mode SX (3) Held SSX (5) Requested |
| Type TX Lock Requesting Mode S(4) |
TX |
S(4) |
TX
X
S TX |
Insufficient Interested Transaction List (ITL) Provision OR Bitmap Index OR PK/UK Index |
TX Lock Held in Mode X (6) Requesting Mode S (4) ITL, Bitmap Index and PK/UK Index Signatures are the Same. Further Investigation will be required to identify absolute cause |
| Type TX Lock Requesting Mode X (6) |
TX |
X(6) |
TX
X
X Single Row in Deadlock Graph |
Self Deadlock OR Autonomous Transaction Self Deadlock |
This looks the same as a standard application deadlock except that there is only a single row in the deadlock graph. |
| Type UL Lock in Deadlock Graph |
UL |
ANY |
UL ? ? |
Application Deadlock Featuring User Defined Locks |
This is very similar to the standard application deadlock except that it features User Defined Locks |
李华荣这篇博客 Oracle 死锁（ DeadLock ）的分类及其模拟 里面对死锁进行了一个分类，个人觉得是一个通俗、很赞的一个死锁分类。本文很多地方也是参考、借鉴他博客的内容。
那么我们接下来看看这些死锁产生的场景，并进行一些分析，很多知识点都是参考 Metalink 上的一些知识点。
1 ：应用程序死锁（ Application Deadlock ）
其实最上面那个死锁的例子，就属于 Application Deadlock, 这个 Application Deadlock 是发生在 同一个表 ，下面我们介绍一下 Application Deadlock 发生在 两个表之间不同顺序相互更新操作引起的死锁 。下面开始我们的实验。创建两个测试表，并初始化数据。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> create table lock_test_one(name varchar(32));
Table created.
SQL> create table lock_test_two(name varchar(32));
Table created.
SQL> insert into lock_test_one values('aaaaaaaa');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into lock_test_two values('bbbbbbbb');
1 row created.
SQL> commit;
Commit complete.
在会话 1 （ SID=685 ）里更新 lock_test_one 的记录，但是不提交更新（下面是一个模拟死锁出现的过程）
SQL> show user; USER is "TEST" SQL> select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 685 0 1 SQL> update lock_test_one set name='cccccccc' where name='aaaaaaaa'; 1 row updated.
在会话 2(SID=719) 里面更新 lock_test_two 的记录，也不提交更新：
SQL> show user; USER 为 "TEST" SQL> select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 719 0 1 SQL> update lock_test_two set name='dddddddd' where name='bbbbbbbb'; 已更新 1 行。
接下来在会话 1 （ SID=685 ）里面更新 lock_test_two ，你会发现会话 1 被回话 2 阻塞了（开启另外一个会话查询，就会发现阻塞信息，如下截图所示）
SQL> update lock_test_two set name='eeeeeeee' where name='bbbbbbbb';
接下来在会话 2 里面更新 lock_test_one, 模拟死锁就会出现了
SQL> update lock_test_one set name='ffffffff' where name='aaaaaaaa';
最后，在会话 2(SID=719) 里面执行上面语句后，你会发现会话 1 立即就会出现 ORA-00060 的死锁错误信息。
那么接下来我们来分析一下死锁日志，如下所示，死锁有两行记录，发生在两个会话之间，可以判断为 Application Deadlock. 从 objn 的值来看，
有两个值，意味着 两个表之间不同顺序相互更新操作引起了死锁，典型的应用程序死锁有下面特征：
1 ： Deadlock graph 中的记录多于 1 行。
2 ： Deadlock graph 中至少有一行锁类型是 “ TX-X-X ”
SQL> col object_name for a32; SQL> col object_type for a32; SQL> select object_name, object_type from dba_objects 2 where object_id=608789; OBJECT_NAME OBJECT_TYPE -------------------------------- -------------------------------- LOCK_TEST_ONE TABLE SQL> select object_name, object_type from dba_objects 2 where object_id=608790; OBJECT_NAME OBJECT_TYPE -------------------------------- -------------------------------- LOCK_TEST_TWO TABLE SQL>
还有其它一些有用的信息，例如你想知道死锁发生在哪一行，那么可以通过 rowid 等位到死锁发生在哪一行。
Rows waited on:
Session 719: obj - rowid = 00094A15 - AACUoVABEAACLUnAAB
(dictionary objn - 608789, file - 68, block - 570663, slot - 1)
Session 685: obj - rowid = 00094A16 - AACUoWABEAACLUvAAA
(dictionary objn - 608790, file - 68, block - 570671, slot - 0)
SQL> select * from lock_test_one where rowid='AACUoVABEAACLUnAAB'; NAME -------------------------------- cccccccc SQL> select * from lock_test_two where rowid='AACUoWABEAACLUvAAA'; NAME -------------------------------- bbbbbbbb
2 ：缺少外键索引引起的死锁。
创建表 dead_lock_parent 与 dead_lock_foreign ，两者存在主外键关系，分布插入两条测试数据：
SQL> create table dead_lock_parent( id number primary key, name varchar2(32)); Table created. SQL> create table dead_lock_foreign(fid number, fname varchar2(32), foreign key(fid) references dead_lock_parent); Table created. SQL> insert into dead_lock_parent values( 1, 'kerry'); 1 row created. SQL> insert into dead_lock_foreign values(1, 'kerry_fk'); 1 row created. SQL> insert into dead_lock_parent values(2, 'jimmy'); 1 row created. SQL> insert into dead_lock_foreign values(2, 'jimmy_fk'); 1 row created. SQL> commit; Commit complete. SQL>
1 ：在会话 1 （会话 ID 为 789 ）里面执行下面 SQL 语句：
SQL> show user; USER 为 "TEST" SQL> select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 789 0 1 SQL> delete from dead_lock_foreign where fid=1; 已删除 1 行。
2 ：在会话 2 （会话 ID 为 766 ）里面执行下面 SQL 语句：
SQL> show user; USER is "TEST" SQL> select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 766 0 1 SQL> delete from dead_lock_foreign where fid=2; 1 row deleted.
3 ：接着在会话 1 （会话 ID 为 789 ）里执行删除 dead_lock_parent 中 id 为 1 的记录：
SQL> delete from dead_lock_parent where id=1;
此时你会发现会话被阻塞了，我们可以用下面 SQL 查询具体的阻塞信息。
COL MODE_HELD FOR A14;
COL LOCK_TYPE FOR A8;
COL MODE_REQUESTED FOR A10;
COL OBJECT_TYPE FOR A14;
COL OBJECT_NAME FOR A20;
SELECT LK.SID,
DECODE(LK.TYPE,
'TX',
'Transaction',
'TM',
'DML',
'UL',
'PL/SQL User Lock',
LK.TYPE) LOCK_TYPE,
DECODE(LK.LMODE,
0,
'None',
1,
'Null',
2,
'Row-S (SS)',
3,
'Row-X (SX)',
4,
'Share',
5,
'S/Row-X (SSX)',
6,
'Exclusive',
TO_CHAR(LK.LMODE)) MODE_HELD,
DECODE(LK.REQUEST,
0,
'None',
1,
'Null',
2,
'Row-S (SS)',
3,
'Row-X (SX)',
4,
'Share',
5,
'S/Row-X (SSX)',
6,
'Exclusive',
TO_CHAR(LK.REQUEST)) MODE_REQUESTED,
OB.OBJECT_TYPE,
OB.OBJECT_NAME,
LK.BLOCK,
SE.LOCKWAIT
FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE
WHERE LK.TYPE IN ('TM', 'UL')
AND LK.SID = SE.SID
AND LK.ID1 = OB.OBJECT_ID(+)
AND SE.SID IN (766,789)
ORDER BY SID;
接着在会话 2 （会话 ID 为 766 ）里面执行下面 SQL ，删除主表中 id=2 的记录
SQL> delete from dead_lock_parent where id=2;
你会发现会话 1 （会话 ID 为 789 ）就会出现 Deadlock
在 trace 文件里面，你能看到如下一些信息：
特征：
1 ：在 Deadlock Graph 里面有多行记录。
2 ：在 Deadlock Graph 至少一行的锁类型为 TM ，并且 Waiter 等待类型为 “ SSX"(Share Row-eXclusive: Mode 5)
此时需要去查找对应的主外键约束的表，我们可以在 PROCESS STATE 里面找到对应 object 的 object_id, 注意此时这个 object_id 不是十进制数，而是十六进制数。如下所示：
SQL> col object_name for a32;
SQL> select object_id, object_name from dba_objects where object_name =upper('dead_lock_foreign');
OBJECT_ID OBJECT_NAME
---------- --------------------------------
608975 DEAD_LOCK_FOREIGN
SQL> select to_char(608975,'xxxxx') from dual;
TO_CHA
------
94acf
找到对应的外键表，然后添加索引，然后你再重复上面操作，你就会发现死锁不会出现了。
SQL> create index idx_dead_lock_foreign_n1 on dead_lock_foreign(fid);
Index created.
关于外键缺少索引，可以详细参考我这篇博客 " ORACLE中关于外键缺少索引的探讨和总结 "
3 ：主键或唯一索引更新引起的死锁
SQL> create table dead_lock_primary(id number(10) primary key, name varchar2(32));
Table created.
首先在会话（会话 ID 为 930 ）下执行下面 SQL
SQL> show user; USER is "TEST" SQL> select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 930 0 1 SQL> insert into dead_lock_primary values(2, 'jimmy'); 1 row created.
然后在会话 2 （会话 ID 为 926 ）里下面执行下面 SQL
SQL> show user; USER 为 "TEST" SQL> select * from v$mystat where rownum =1; SID STATISTIC# VALUE ---------- ---------- ---------- 926 0 1 SQL> insert into dead_lock_primary values(1, 'kerry'); 已创建 1 行。
然后在会话 1 （会话 ID 为 930 ）下插入 id=1 的记录。
SQL> insert into dead_lock_primary values(1, 'kkk');
此时在会话 3 下执行下面 SQL ，你会看到会话 2 被阻塞了， 如下所示：
SQL> @get_locked_objects_rpt.sql Enter value for 1: 5 old 42: AND locks_t.blocked_secs > &1 new 42: AND locks_t.blocked_secs > 5 ========= $Revision: 1.4 $ ($Date: 2013/09/16 13:15:22 $) =========== Locked object : MONI_DDL_LOG Locked row# : AACg2tAAsAAAdaaAAA Blocked for : 238 seconds Blocker info. : TEST@xxxx\GET253194(SID=926) [sqlplus.exe/PID=14144:10392] Blocked info. : TEST@DB-Server.localdomain(SID=930) [sqlplus@DB-Server.localdomain (TNS V1-V3)/PID=6806] Blocked SQL : insert into dead_lock_primary values(1, 'kkk') Found 1 blocked session(s). Disconnected from Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
接着在会话 2 （会话 ID 为 926 ）下执行下面 SQL ，死锁立马出现：
SQL> insert into dead_lock_primary values(2, 'jimmy');
特征：
· More than one row in the deadlock graph
· At Least 1 Row in the Deadlock graph is "TX X S"
· i.e. The Lock type is TX and the holder holds this in "X" (eXclusive: Mode 6) and waits for nothing. The waiter waits for "S" (Share:Mode 4) and waits for nothing
· At least one of the Involved Objects is a Primary or Unique Key Index
· Data is being Inserted or changed in an inconsistent order
4 ：位图索引更新引起的死锁。
下面我们来看看位图索引的更新为什么会导致死锁。我们先构造下面测试所需的例子：
SQL> create table dead_lock_bitmap_index 2 ( 3 id number(10), 4 name varchar(30), 5 sex number(1) 6 ); Table created. SQL> insert into dead_lock_bitmap_index 2 values(1000, 'kerry', 1); 1 row created. SQL> insert into dead_lock_bitmap_index 2 values(1001, 'Merry', 0); 1 row created. SQL> insert into dead_lock_bitmap_index 2 values(1002, 'Richard', 1); 1 row created. SQL> insert into dead_lock_bitmap_index 2 values(1003, 'Ken', 0); 1 row created. SQL> commit; Commit complete. SQL> SQL> create bitmap index idx_dead_lock_bitmap_index on dead_lock_bitmap_index(sex); Index created. SQL>
在会话 1 （会话 ID 为 14 ）中执行下面 SQL ，此时 sex=1 的所有行都会被锁定。
SQL> show user; USER is "TEST" SQL> select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 14 0 0 SQL> update dead_lock_bitmap_index set sex=3 where id=1000 and sex=1; 1 row updated.
在会话 2 （会话 ID 为 69 ）中执行下面 SQL ，此时 sex=0 的所有行都会被锁定
SQL> show user; USER 为 "TEST" SQL> select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 69 0 0 SQL> update dead_lock_bitmap_index set sex=4 where id=1001 and sex=0; 已更新 1 行。
在会话 1 （会话 ID 为 14 ）中执行下面 SQL ，此时，这个 SQL 语句将会被阻塞
SQL> update dead_lock_bitmap_index set sex=4 where id=1003 and sex=0;
新建会话，执行下面 SQL 语句查看具体阻塞情况，如下所示：
SQL> COL USERNAME FOR A14; SQL> COL MACHINE FOR A26; SQL> COL OBJECT_NAME FOR A26; SQL> SELECT S.SID SID, 2 S.USERNAME USERNAME, 3 S.MACHINE MACHINE, 4 L.TYPE TYPE, 5 O.OBJECT_NAME OBJECT_NAME, 6 DECODE(L.LMODE, 0, 'None', 7 1, 'Null', 8 2, 'Row Share', 9 3, 'Row Exlusive', 10 4, 'Share', 11 5, 'Sh/Row Exlusive', 12 6, 'Exclusive') lmode, 13 DECODE(L.REQUEST, 0, 'None', 14 1, 'Null', 15 2, 'Row Share', 16 3, 'Row Exlusive', 17 4, 'Share', 18 5, 'Sh/Row Exlusive', 19 6, 'Exclusive') request, 20 L.BLOCK BLOCK 21 FROM V$LOCK L, 22 V$SESSION S, 23 DBA_OBJECTS O 24 WHERE L.SID = S.SID 25 AND USERNAME != 'SYSTEM' 26 AND O.OBJECT_ID(+) = L.ID1 27 AND S.SID IN ( 14,69) 28 ORDER BY S.SID; SID USERNAME MACHINE TY OBJECT_NAME LMODE REQUEST BLOCK ---------- -------------- -------------------------- -- -------------------------- --------------- --------------- ---------- 14 TEST myvlnx14uat.localt.com TM DEAD_LOCK_BITMAP_INDEX Row Exlusive None 0 14 TEST myvlnx14uat.localt.com TX None Share 0 14 TEST myvlnx14uat.localt.com TX MRAC_OLAP2_AW_DIMENSIONS_V Exclusive None 0 14 TEST myvlnx14uat.localt.com AE ORA$BASE Share None 0 69 TEST xxxx\GET253194 TM DEAD_LOCK_BITMAP_INDEX Row Exlusive None 0 69 TEST xxxx\GET253194 TX Exclusive None 1 69 TEST xxxx\GET253194 AE ORA$BASE Share None 0 7 rows selected. SQL>
在会话 2 （会话 ID 为 69 ）中执行下面 SQL ，此时，在会话 1 （会话 ID 为 14 ）中，你就会立马看见死锁出现：
SQL> update dead_lock_bitmap_index set sex=3 where id=1002 and sex=1;
死锁情况如下所示：
SQL> show user; USER is "TEST" SQL> select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 14 0 0 SQL> update dead_lock_bitmap_index set sex=3 where id=1000 and sex=1; 1 row updated. SQL> SQL> update dead_lock_bitmap_index set sex=4 where id=1003 and sex=0; update dead_lock_bitmap_index set sex=4 where id=1003 and sex=0 * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource SQL>
有位图索引存在的表上面，非常容易就引发阻塞与死锁。这个阻塞不是发生在表上面，而是发生在索引上。因为位图索引锁定的范围远远比普通的 b-tree 索引锁定的范围大。
特征：
位图索引更新引起的死锁的特征跟主键或唯一索引更新引起的死锁的特征有部分是类似的。
The typical deadlock graph for an issue caused by Bitmap Indexes locking multiple contending rows is characterised by the following:
More than one row in the deadlock graph
At Least 1 Row in the Deadlock graph is "TX X S" i.e. The Lock type is TX and the holder holds this in "X" (eXclusive: Mode 6) and waits for nothing. The waiter waits for "S" (Share:Mode 4) and waits for nothing
At Least one of the Involved Objects is a Bitmap Indexes
COLUMN OBJECT_NAME FORMAT A15 COLUMN OWNER FORMAT A10 SELECT O.OBJECT_ID, O.OWNER, O.OBJECT_NAME, I.INDEX_TYPE FROM DBA_OBJECTS O, DBA_INDEXES I WHERE I.OWNER(+) =O.OWNER AND I.INDEX_NAME(+)=O.OBJECT_NAME AND O.OBJECT_TYPE = 'INDEX' AND O.OBJECT_ID = &OBJECT_ID /
另外关于位图索引的选择是有一些场景。谨慎选择：
B 树索引更适合索引动态表的 OLTP 环境，而位图索引更适合在大型静态表上使用复杂查询的数据仓库环境
位图索引被存储为压缩的索引值，其中包含了一个范围内的 ROWID ，因此 ORACLE 必须针对一个给定值锁定所有范围内的 ROWID ，不支持行级别的锁定。
位图索引被存储为压缩的索引值，其中包含了一个范围内的 ROWID ，因此 ORACLE 必须针对一个给定值锁定所有范围内的 ROWID ，不支持行级别的锁定。
换一种描述方法：使用位图索引时，一个键指向多行（成百上千），如果更新一个位图索引键，会同时将其他行对应位图索引字段进行锁定！
较之 B-Tree 索引优点：
位图以一种压缩格式存放，因此占用的磁盘空间比 B-Tree 索引要小得多
较之 B-Tree 索引缺点：
这种锁定的代价很高，会导致一些 DML 语句出现 “ 锁等待 ” ，严重影响插入、更新和删除的效率，对于高并发的系统不适用。
位图索引使用原则：
位图索引主要用于决策支持系统或静态数据，不支持索引行级锁定。
5 ：自治事务引发的死锁
ORACLE 自制事务是指的存储过程和函数可以自己处理内部事务不受外部事务的影响，用 PRAGMA AUTONOMOUS_TRANSACTION 来声明，要创建一个自治事务，您必须在匿名块的最高层或者存储过程、函数、数据包或触发的定义部分中，使用 PL/SQL 中的 PRAGMA AUTONOMOUS_TRANSACTION 语句。在这样的模块或过程中执行的 SQL 语句都是自治的。
我们先准备测试环境，如下所示。
Connected to: Oracle Database 10g Release 10.2.0.5.0 - 64bit Production SQL> create table pat_deadlock_test(id number, name varchar(32)); Table created. SQL> insert into pat_deadlock_test 2 select 1001, 'kerry' from dual union all 3 select 1002, 'ken' from dual; 2 rows created. SQL> commit; Commit complete. SQL> --创建存储过程，也可以用触发器、匿名块等。 SQL> create or replace procedure prc_test_dead_lock as 2 pragma autonomous_transaction; 3 begin 4 update pat_deadlock_test set name='richard' where id=1002; 5 commit; 6 end; 7 / Procedure created. SQL>
接下来演示一下自治事务发生死锁的情况。如下所示
SQL> show user; USER is "TEST" SQL> select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 837 0 1 SQL> update pat_deadlock_test set name='kkk' where id=1002; 1 row updated. SQL> exec prc_test_dead_lock; BEGIN prc_test_dead_lock; END; * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at "TEST.PRC_TEST_DEAD_LOCK", line 4 ORA-06512: at line 1
检查告警日志，就能发现告警日志里面提示死锁生成了 trace 文件 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/SCM2/udump/scm2_ora_30818.trc.
自治事务引起死锁的特征：
This type of deadlock occurs when a single session manages to deadlock itself and is characterised by the following:
· Only one row in the deadlock graph
· The Row in the Deadlock graph is "TX X X" i.e. The Lock type is TX and the holder holds this in "X" (eXclusive: Mode 6) and waits for nothing. The waiter waits in "X" (eXclusive: Mode 6) and holds nothing.
· The session is NOT involved in an autonomous transaction
You can determine whether the session is involved in an autonomous transaction by searching for an "Autonomous Transaction Frames" section in the trace file:
6 ： ITL 死锁
ITL 概念
ITL （ Interested Transaction List ）是 Oracle 数据块内部的一个组成部分，用来记录该块所有发生的事务，有的时候也叫 ITL 槽位。如果一个事务一直没有提交，那么，这个事务将一直占用一个 ITL 槽位， ITL 里面记录了事务信息、回滚段的入口和事务类型等等。如果这个事务已经提交，那么， ITL 槽位中还保存有这个事务提交时候的 SCN 号。 ITL 的个数受表的存储参数 INITRANS 控制，在一个块内部，默认分配了 2 个 ITL 的个数，如果这个块内还有空闲空间，那么 Oracle 是可以利用这些空闲空间再分配 ITL 。如果没有了空闲空间，那么，这个块因为不能分配新的 ITL ，所以，就可能发生 ITL 等待。如果在并发量特别大的系统中，那么最好分配足够的 ITL 个数，或者设置足够的 PCTFREE ，保证 ITL 能扩展，但是 PCTFREE 有可能是被行数据给消耗掉的，例如 UPDATE ，所以，也有可能导致块内部的空间不够而导致 ITL 等待，出现了 ITL 等待就可能导致 ITL 死锁。
本来想在此总结一下 ITL 死锁，不过个人看了李华荣写的 “ Oracle死锁（DeadLock）的分类及其模拟 ” 中 ITL 死锁，感觉他已经把别人想写的都写完了，所以，如果对 TIL 死锁部分感兴趣，就参考他的博客吧。
The typical ITL deadlock graph is characterised by the following:
- More than one row in the deadlock graph
-
At Least 1 Row in the Deadlock graph is "TX X S"?
i.e. The Lock type is TX and the holder holds this in "X" (eXclusive: Mode 6) and waits for nothing. The waiter waits for "S" (Share:Mode 4) and waits for nothing
- None of the Involved Objects are Bitmap Indexes
Process state is likely to contain waiting for 'enq: TX - allocate ITL entry' as the current wait or as a previous wait in the recent wait stack:
避免发生死锁
数据库的死锁产生是有一定条件的。死锁产生的四个必要条件：
1 ） Mutual exclusion （互斥）：资源不能被共享，只能由一个进程使用。
2 ） Hold and wait （请求并保持）：已经得到资源的进程可以再次申请新的资源。
3 ） No pre-emption （不可剥夺）：已经分配的资源不能从相应的进程中被强制地剥夺。
4 ） Circular wait （循环等待条件）：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。
其实死锁的产生都是应用程序设计问题导致，我们一般通过分析 trace 文件分析死锁产生的原因后，就可以去破坏死锁产生的条件，来防止死锁产生，从而解决死锁问题。例如，上面案例中，调整更新表的顺序，外键增加索引等等。
参考资料：
http://www.cnblogs.com/lhrbest/p/6005702.html
http://www.cnblogs.com/lhrbest/articles/5388514.html
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
开发高质量PHP框架与应用的实际案例解析
Sebastian Bergmann / 刘文瀚、刘海燕 / 清华大学出版社 / 2012-6 / 49.00元
PHP已经成为最受欢迎的编程语言之一，这使得用PHP创建高质量、易维护的应用程序和框架比以往受到更多的青睐。通过使用来自于知名公司的真实案例研究，《开发高质量PHP框架与应用的实际案例解析》为Web软件体系结构的不同层次介绍了规划、执行以及测试自动化方面的内容，并解释了这些公司如何测量和测试软件质量。《开发高质量PHP框架与应用的实际案例解析》作者Sebastian Bergmann、Stefan......一起来看看 《开发高质量PHP框架与应用的实际案例解析》 这本书的介绍吧!
