oracle死锁测试

测试一个死锁，看看oracle会报什么错误，以及oracle如何处理

 

 

1.创建测试表，测试数据

2.第一轮update,delete 两个会话，不同的行

3.第二轮，delete,update两个会话，分别是上述对方操作的行

4.观察alert报错信息

 

 

1.创建测试表，测试数据

SYS@ora122>create table yy_test(id int,name varchar2(20));
SYS@ora122>insert into yy_test values(1,'aa');
SYS@ora122>insert into yy_test values(2,'bb');
SYS@ora122>commit;

 

2.第一轮update,delete 两个会话，不同的行

会话一、操作一 
SYS@ora122>select sid from v$mystat group by sid;

       SID
----------
       391
SYS@ora122>update yy_test set id=3 where name='aa';

1 row updated.
会话二、操作二

SYS@ora122>select sid from v$mystat group by sid;

       SID
----------
        15

Elapsed: 00:00:00.01
14:28:56 SYS@ora122>delete yy_test where id=2;

1 row deleted.

 

3.第二轮，delete,update两个会话，分别是上述对方操作的行

会话一、操作三
14:28:48 SYS@ora122>delete yy_test where id=2;
delete yy_test where id=2
                     *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
会话二、操作四
14:29:09 SYS@ora122>update yy_test set id=4 where name='aa';
--第四个操作hang 住，等待第一个操作update释放资源

发现，当操作四update操作执行时，oracle过了几秒内，瞬间将第三个操作申请的tm 3号锁及delete 操作强制回收，但是会话一并未断开，且之前执行的update操作还是有效的

 

4.观察alert报错信息

 

2019-01-24T14:30:23.183647+08:00
diag_adl:ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in 
file /u01/app/ora122/diag/rdbms/orcl/ora122/trace/ora122_ora_8522.trc.



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 serial  process session holds waits serial
TX-00CF000B-00000075-00000001-00000000         55     391     X         3013      60      15           X  46715
TX-00BD0016-00000079-00000001-00000000         60      15     X        46715      55     391           X   3013

*** 2019-01-24T14:30:22.894735+08:00 (CDB$ROOT(1))
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=8dabarvhbg58k) -----
delete yy_test where id=2