导致死锁的SQL示例
导致死锁的SQL示例
死锁定义：
从广义上讲包括操作系统 应用程序 数据库，如果2个进程（会话）相互持有对方的资源，都一直等待对方释放，这种情况会造成死锁。
误解：会话的阻塞不是死锁，因为其中有一个会话还是可以继续操作的。
释放：Oracle会自动检测死锁并强制干预释放-只释放了死锁的第一个，另一个还在HANG住。
1.需要创建一个表，用主键或惟一约束。
BYS@ bys001>create table test(id varchar2(10) constraint pk__test_id primary key);
Table created.
BYS@ bys001>desc test;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL VARCHAR2(10)
2.在会话38执行插入操作，值1.
BYS@ bys001>set time on
17:43:47 BYS@ bys001>select distinct sid from v$mystat;
SID
----------
38
17:47:13 BYS@ bys001>select * from test;
no rows selected
17:48:49 BYS@ bys001>insert into test values(1);
1 row created.
此时没有阻塞，可以查询到38号会话持有了一个模式3的TM表锁，
因为事务未提交，还持有模式6的事务锁
17:50:12 BYS@ bys001> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
38 TM 76864 0 3 0 0
38 TX 262157 27843 6 0 0
17:54:07 BYS@ bys001>select object_name from dba_objects where object_id=76864;
OBJECT_NAME
----------------------------------------------------------------------------------------------------
TEST
3.在会话39插入值2
17:49:46 BYS@ bys001>select distinct sid from v$mystat;
SID
----------
39
17:51:07 BYS@ bys001>insert into test values(2);
1 row created.
此时可以查询到每个会话都产生的有两个锁定，BLOCK列全为0，此时没有阻塞。
17:51:25 BYS@ bys001> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
38 TM 76864 0 3 0 0
38 TX 262157 27843 6 0 0 -----会话38有排他锁未阻塞会话
39 TM 76864 0 3 0 0
39 TX 327692 28225 6 0 0--------会话39有排他锁未阻塞会话
4.在会话39插入，值1，此时hang住
17:51:10 BYS@ bys001>insert into test values(1);
在会话38进行查询数据库阻塞：
17:57:18 BYS@ bys001> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
38 TM 76864 0 3 0 0
38 TX 262157 27843 6 0 1 ------会话38阻塞会话39，因为会话38未提交，处于未决状态。
39 TM 76864 0 3 0 0
39 TX 262157 27843 0 4 0----------违反主键约束产生的阻塞，所以申请的是4级锁而非6级。
39 TX 327692 28225 6 0 0
5.在会话38插入，值2，hang住，死锁产生
此时死锁被系统检测到，会话39插入值1的操作被释放--这个操作在会话38插入值2之前，但是会话38的锁定还在，需要手工释放。
也说明死锁被系统检测到后，ORACLE会回滚第一个，第二个仍在HANG住，需要手工回滚或提交来解决。
17:51:10 BYS@ bys001>insert into test values(1);
insert into test values(1)
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
此时的会话38的插入操作是HANG住
数据库的锁定情况如下：
18:02:40 BYS@ bys001> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
38 TM 76864 0 3 0 0
38 TX 327692 28225 0 4 0 ----违反主键约束产生的
