合 Oracle死锁的分类及其模拟过程
前言部分
导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,\~O(∩_∩)O\~:
① 死锁的概念及其trace文件
② 死锁的分类
③ 行级死锁的模拟
④ ITL的概念、ITL结构
⑤ ITL引发的死锁处理
⑥ ITL死锁的模拟
本文简介
写了近大半年的书了,碰到了各种困难,不过幸运的是基本上都一一克服了。前段时间工作上碰到了一个很奇怪的死锁问题,由业务发出来的SQL来看是不太可能产生死锁的,不过的的确确实实在在的产生了,那作者是碰到了哪一类的死锁呢?ITL死锁!!有关当时的案例可以参考:http://blog.itpub.net/26736162/viewspace-2124771/和http://blog.itpub.net/26736162/viewspace-2124735/。于是,作者就把死锁可能出现的情况都分类总结了一下,分享给大家,欢迎大家指出错误。本文内容也将写入作者的新书中,欢迎大家提前订阅。
死锁(DeadLock)的分类及其模拟
死锁简介
什么是死锁?
所谓死锁,是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。Oracle对于“死锁”是要做处理的,而不是不闻不问。
1 2 3 4 5 | [ZFLHRDB1:oracle]:/oracle>oerr ora 60 00060, 00000, "deadlock detected while waiting for resource" // *Cause: Transactions deadlocked one another while waiting for resources. // *Action: Look at the trace file to see the transactions and resources // involved. Retry if necessary. |
Cause: Your session and another session are waiting for are source locked by the other. This condition is known AS a deadlock. To resolve the deadlock, one or more statements were rolled back for the other session to continue work.
Action Either: l. Enter arollback statement and re—execute all statements since the last commit or 2. Wait until the lock is released, possibly a few minutes, and then re—execute the rolled back statements.
死锁的trace文件
Oracle中产生死锁的时候会在alert告警日志文件中记录死锁的相关信息,无论单机还是RAC环境都有Deadlock这个关键词,而且当发生死锁时都会生成一个trace文件,这个文件名在alert文件中都有记载。由于在RAC环境中,是由LMD(Lock Manager Daemon)进程统一管理各个节点之间的锁资源的,所以,RAC环境中trace文件是由LMD进程来生成的。
在RAC环境中,告警日志的形式如下所示:
1 2 3 | Mon Jun 20 10:10:56 2016 Global Enqueue Services Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/raclhr/raclhr2/trace/raclhr2_lmd0_19923170.trc. |
在单机环境中,告警日志的形式如下所示:
1 2 | Mon Jun 20 12:10:56 2016 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/dlhr/dlhr/trace/dlhr_ora_16973880.trc. |
通常来讲,对于单机环境,当有死锁发生后,在trace文件中会看到如下的日志信息:
图 2-1 单机环境下的死锁
当看到trace文件时,需要确认一下产生锁的类型,是两行还是一行,是TX还是TM,如果只有一行那么说明是同一个SESSION,可能是自治事务引起的死锁。
对于RAC环境,当有死锁发生后,在trace文件中会看到如下的日志信息:
图 2-2 RAC环境下的死锁
死锁的检测时间
死锁的监测时间是由隐含参数_lm_dd_interval来控制的,在Oracle 11g中,隐含参数_lm_dd_interval的值默认为10,而在Oracle 10g中该参数默认为60,单位为秒。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SYS@oraLHRDB2> SELECT A.INDX, 2 A.KSPPINM NAME, 3 A.KSPPDESC, 4 B.KSPPSTVL 5 FROM X$KSPPI A, 6 X$KSPPCV B 7 WHERE A.INDX = B.INDX 8 AND LOWER(A.KSPPINM) LIKE LOWER('%&PARAMETER%'); ENTER VALUE FOR PARAMETER: _lm_dd_interval OLD 8: AND LOWER(A.KSPPINM) LIKE LOWER('%&PARAMETER%') NEW 8: AND LOWER(A.KSPPINM) LIKE LOWER('%_LM_DD_INTERVAL%') INDX NAME KSPPDESC KSPPSTVL ---------- ------------------ ------------------------------ -------------------- 578 _lm_dd_interval dd time interval in seconds 10 |
可以看到该隐含参数的值为10。
死锁的分类
有人的地方就有江湖,有资源阻塞的地方就可能有死锁。Oralce中最常见的死锁分为:行级死锁(Row-Level Deadlock)和块级死锁(Block-Level Deadlock),其中,行级死锁分为①主键、唯一索引的死锁(会话交叉插入相同的主键值),②外键未加索引,③表上的位图索引遭到并发更新,④常见事务引发的死锁(例如,两个表之间不同顺序相互更新操作引起的死锁;同一张表删除和更新之间引起的死锁),⑤自治事务引发的死锁。块级死锁主要指的是ITL(Interested Transaction List)死锁。
死锁分类图如下所示:
图 2-3 死锁的分类图
行级死锁
行级锁的发生如下图所示,在A时间,TRANSACRION1和TRANSCTION2分别锁住了它们要UPDATE的一行数据,没有任何问题。但每个TRANSACTION都没有终止。接下来在B时间,它们又试图UPDATE当前正被对方TRANSACTION锁住的行,因此双方都无法获得资源,此时就出现了死锁。之所以称之为死锁,是因为无论每个TRANSACTION等待多久,这种锁都不会被释放。
行级锁的死锁一般是由于应用逻辑设计的问题造成的,其解决方法是通过分析trace文件定位出造成死锁的SQL语句、被互相锁住资源的对象及其记录等信息,提供给应用开发人员进行分析,并修改特定或一系列表的更新(UPDATE)顺序。
以下模拟各种行级死锁的产生过程,版本都是11.2.0.4。
主键、唯一索引的死锁(会话交叉插入相同的主键值)
主键的死锁其本质是唯一索引引起的死锁,这个很容易模拟出来的,新建一张表,设置主键(或创建唯一索引)后插入一个值,然后不要COMMIT,另一个会话插入另一个值,也不要COMMIT,然后再把这两个插入的值互相交换一下,在两个会话中分别插入,死锁就会产生。
会话1,sid为156:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S1> " SYS@lhrdb S1> DROP TABLE T_DEADLOCK_PRIMARY_LHR; Table dropped. ====>>>>> CREATE UNIQUE INDEX IDX_TEST_LHR ON T_DEADLOCK_PRIMARY_LHR(ID); SYS@lhrdb S1> CREATE TABLE T_DEADLOCK_PRIMARY_LHR(ID NUMBER PRIMARY KEY); Table created. SYS@lhrdb S1> select userenv('sid') from dual; USERENV('SID') -------------- 156 SYS@lhrdb S1> INSERT INTO T_DEADLOCK_PRIMARY_LHR VALUES(1); 1 row created. SYS@lhrdb S1> |
会话2,sid为156:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S2> " SYS@lhrdb S2> select userenv('sid') from dual; USERENV('SID') -------------- 191 SYS@lhrdb S2> INSERT INTO T_DEADLOCK_PRIMARY_LHR VALUES(2); 1 row created. SYS@lhrdb S2> INSERT INTO T_DEADLOCK_PRIMARY_LHR VALUES(1); ====>>>>> 产生了阻塞 |
1 2 3 4 5 6 7 8 9 | SELECT NVL(A.SQL_ID, A.PREV_SQL_ID) SQL_ID, A.BLOCKING_SESSION, A.SID, A.SERIAL#, A.LOGON_TIME, A.EVENT FROM GV$SESSION A WHERE A.SID IN (156,191) ORDER BY A.LOGON_TIME; |
156阻塞了191会话,即会话1阻塞了会话2。
会话1再次插入数据:
1 2 3 | SYS@lhrdb S1> INSERT INTO T_DEADLOCK_PRIMARY_LHR VALUES(2); ====>>>>> 产生了阻塞 |
此时,去会话2看的时候,已经报出了死锁的错误:
此时的阻塞已经发生了变化:
告警日志:
1 2 | Fri Sep 23 09:03:11 2016 ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/lhrdb/lhrdb/trace/lhrdb_ora_25886852.trc. |
其内容可以看到很经典的一段:
1 2 3 4 5 6 7 8 9 10 11 12 | Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-0008000c-000008dc 38 191 X 29 156 S TX-00030016-00000892 29 156 X 38 191 S session 191: DID 0001-0026-00000115 session 156: DID 0001-001D-0000000D session 156: DID 0001-001D-0000000D session 191: DID 0001-0026-00000115 Rows waited on: Session 191: no row Session 156: no row |
这就是主键的死锁,模拟完毕。
此时,若是会话2执行提交后,会话1就会报错,违反唯一约束:
1 2 3 4 5 6 | SYS@lhrdb S1> INSERT INTO T_DEADLOCK_PRIMARY_LHR VALUES(2); INSERT INTO T_DEADLOCK_PRIMARY_LHR VALUES(2) * ERROR at line 1: ORA-00001: unique constraint (SYS.SYS_C0011517) violated |
脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 会话1: DROP TABLE T_DEADLOCK_PRIMARY_LHR; CREATE TABLE T_DEADLOCK_PRIMARY_LHR(ID NUMBER PRIMARY KEY); --CREATE UNIQUE INDEX IDX_TEST_LHR ON T_DEADLOCK_PRIMARY_LHR(ID); select userenv('sid') from dual; INSERT INTO T_DEADLOCK_PRIMARY_LHR VALUES(1); 会话2: select userenv('sid') from dual; INSERT INTO T_DEADLOCK_PRIMARY_LHR VALUES(2); INSERT INTO T_DEADLOCK_PRIMARY_LHR VALUES(1); 会话1:---死锁产生 INSERT INTO T_DEADLOCK_PRIMARY_LHR VALUES(2); SELECT NVL(A.SQL_ID, A.PREV_SQL_ID) SQL_ID, A.BLOCKING_SESSION, A.SID, A.SERIAL#, A.LOGON_TIME, A.EVENT FROM GV$SESSION A WHERE A.SID IN (156,191) ORDER BY A.LOGON_TIME; |
外键的死锁(外键未加索引)
外键未加索引很容易导致死锁。在以下两种情况下,Oracle在修改父表后会对子表加一个全表锁:
1. 如果更新了父表的主键,由于外键上没有索引,所以子表会被锁住。
2. 如果删除了父表中的一行,由于外键上没有索引,整个子表也会被锁住。
总之,就是更新或者删除父表的主键,都会导致对其子表加一个全表锁。
如果父表存在删除记录或者更改外键列的情形,那么就需要在子表上为外键列创建索引。
除了全表锁外,在以下情况下,未加索引的外键也可能带来问题:
1. 如果有ON DELETE CASCADE,而且没有对子表加索引:例如,EMP是DEPT的子表,DELETE DEPTNO = 10应该CASCADE(级联)至EMP[4]。如果EMP中的DEPTNO没有索引,那么删除DEPT表中的每一行时都会对EMP做一个全表扫描。这个全表扫描可能是不必要的,而且如果从父表删除多行,父表中每删除一行就要扫描一次子表。
2. 从父表查询子表:再次考虑EMP/DEPT例子。利用DEPTNO查询EMP表是相当常见的。如果频繁地运行以下查询(例如,生成一个报告),你会发现没有索引会使查询速度变慢:
SELECT * FROM DEPT, EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND DEPT.DEPTNO = :X;
那么,什么时候不需要对外键加索引呢?答案是,一般来说,当满足以下条件时不需要加索引: