首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

招致死锁的SQL示例

2013-10-29 
导致死锁的SQL示例首先感谢:leonarding的文章,还有晨_chen。死锁定义:从广义上讲包括操作系统 应用程序 数

导致死锁的SQL示例
首先感谢:leonarding的文章,还有晨_chen。

死锁定义:
从广义上讲包括操作系统 应用程序 数据库,如果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);
招致死锁的SQL示例
在会话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
招致死锁的SQL示例

此时的会话38的插入操作是HANG住
招致死锁的SQL示例

数据库的锁定情况如下:
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 ----违反主键约束产生的阻塞,所以申请的是4级锁而非6级。
        38 TX     262157      27843          6          0          0
        39 TM      76864          0          3          0          0
        39 TX     327692      28225          6          0          1     --------会话39阻塞会话38,因为会话38事务未提交,处于未决状态。

6.手动解决38号会话的HANG住问题:
在会话39上提交
18:09:36 BYS@ bys001>commit;

Commit complete.
会话38的HANG住解决,报错如下:
18:08:09 BYS@ bys001>insert into test values(2);
insert into test values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (BYS.PK__TEST_ID) violated
还可以直接在38号上CTRL+C取消语句的执行。

LOCK作用:独占业务资源  保证读一致性  维护事务完整性
LOCK宗旨:没有并发就没有锁,一个人操作数据库是不会产生锁的

热点排行