♣
题目部分
在Oracle中,模拟死锁产生的一个场景。
♣
答案部分
Oracle中的死锁比较复杂,产生死锁的原因也有很多种,曾经有面试官让面试人员口头模拟死锁产生的一个场景。
下面给出一个基于事务相互更新导致死锁的模拟实验:
1、创建两个简单的表A和B,每个表中仅仅包含一个字段ID,这里的实验环境为集群。
SYS@RACLHR2> CREATE TABLE A (ID INT);
Table created.
SYS@RACLHR2> CREATE TABLE B (ID INT);
Table created.
2、每张表中仅插入一条数据,A表插入1,B表插入2。
SYS@RACLHR2> INSERT INTO A VALUES (1);
1 row created.
SYS@RACLHR2> INSERT INTO B VALUES (2);
1 row created.
SYS@RACLHR2> COMMIT;
Commit complete.
SYS@RACLHR2> SELECT * FROM A;
ID
----------
1
SYS@RACLHR2> SELECT * FROM B;
ID
----------
2
3、在第一个会话SESSION1中更新表A中的记录“1”为“10000”,且不提交;在第二个会话SESSION2中更新表B中的记录“2”为“20000”,且不提交。
SESSION1的情况如下所示:
SYS@RACLHR2> SELECT A.SID,
2 B.SERIAL# ,
3 C.SPID
4 FROM V$MYSTAT A,
5 V$SESSION B ,
6 V$PROCESS C
7 WHERE A.SID = B.SID
8 AND B.PADDR=C.ADDR
9 AND ROWNUM = 1;
SID SERIAL# SPID
---------- ---------- ------------------------
133 3 20906088
SYS@RACLHR2> UPDATE A SET ID = 10000 WHERE ID = 1;
1 row updated.
SESSION2的情况如下所示:
SYS@RACLHR2> SELECT A.SID,
2 B.SERIAL# ,
3 C.SPID
4 FROM V$MYSTAT A,
5 V$SESSION B ,
6 V$PROCESS C
7 WHERE A.SID = B.SID
8 AND B.PADDR=C.ADDR
9 AND ROWNUM = 1;
SID SERIAL# SPID
---------- ---------- ------------------------
195 21 11010172
SYS@RACLHR2> UPDATE B SET ID = 20000 WHERE ID = 2;
1 row updated.
此时,执行过程没有任何问题。现在接着执行下面的操作,首先回到会话SESSION1中,更新表B的记录,此时出现了会话阻塞,更新不能继续:
SYS@RACLHR2> UPDATE B SET ID = 10000 WHERE ID = 2;
这里出现了锁等待(阻塞)的现象,因为在SESSION2中已经对这条数据执行过UPDATE操作,没有提交表示已经对该行加了行级锁,如下所示:
SYS@RACLHR2> SET LINE 9999
SYS@RACLHR2> SELECT A.INST_ID,
2 A.SID,
3 A.SERIAL#,
4 A.SQL_ID,
5 A.BLOCKING_INSTANCE,
6 A.BLOCKING_SESSION,
7 A.EVENT
8 FROM GV$SESSION A
9 WHERE A.USERNAME IS NOT NULL
10 AND A.STATUS = 'ACTIVE'
11 AND A.BLOCKING_SESSION IS NOT NULL ;
INST_ID SID SERIAL# SQL_ID BLOCKING_INSTANCE BLOCKING_SESSION EVENT
---------- ---------- ---------- ------------- ----------------- ---------------- ------------------------------
2 133 3 6k793mj0duubw 2 195 enq: TX - row lock contention
可以通过V$SESSION视图看到,实例2的195阻塞了实例2的133会话,即本实验中的SESSION2阻塞了SESSION1。
4、接下来再执行一条SQL后,死锁就会产生了。在SESSION2中,更新表A的记录。
SYS@RACLHR2> UPDATE A SET ID = 10000 WHERE ID = 1;
由于SESSION1没有提交,所以,SESSION2这里还是长时间地等待即会话HANG住,但是SESSION2这里已经发生了死锁,此时去第一个会话SESSION1可以看到原先一直在等待的SQL语句报了如下的错误:
SYS@RACLHR2> UPDATE B SET ID = 10000 WHERE ID = 2;
UPDATE B set id = 10000 WHERE id = 2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
若此时查询V$SESSION视图,则可以看到实例2的133阻塞了实例2的195会话,即本实验中的SESSION1阻塞了SESSION2,和刚才的阻塞情况相反,说明Oracle做了自动处理:
SYS@RACLHR2> SET LINE 9999
SYS@RACLHR2> SELECT A.INST_ID,
2 A.SID,
3 A.SERIAL#,
4 A.SQL_ID,
5 A.BLOCKING_INSTANCE,
6 A.BLOCKING_SESSION,
7 A.EVENT
8 FROM GV$SESSION A
9 WHERE A.USERNAME IS NOT NULL
10 AND A.STATUS = 'ACTIVE'
11 AND A.BLOCKING_SESSION IS NOT NULL ;
INST_ID SID SERIAL# SQL_ID BLOCKING_INSTANCE BLOCKING_SESSION EVENT
---------- ---------- ---------- ------------- ----------------- ---------------- ------------------------------
2 195 21 5q7t3877fdu3n 2 133 enq: TX - row lock contention
& 说明:
有关行级死锁更详细的内容可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-1744705/、http://blog.itpub.net/26736162/viewspace-1744719/
有关ITL死锁的更多内容请参考:http://blog.itpub.net/26736162/viewspace-2124771/
有关行级死锁的模拟参考:http://blog.itpub.net/26736162/viewspace-2127247/
本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗
详细内容可以添加麦老师微信或QQ私聊。
About Me:小麦苗
● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用
● 作者博客地址:http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
● QQ:646634621 QQ群:618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处,还望各位朋友批评指正,共同进步