<pre name="code" class="sql"> 如果你有两个会话,每个会话都持有另一个会话想要的资源,此时就会出现死锁(deadlock)。例如,
如果我的数据库中有两个A和B,每个表都只有一行,就可以很容易地展示什么是死锁。
我要做的知识打开两个回话(例如,两个SQL*PLUS会话).在会话A中更更新A表,在会话B中更新B。
现在,如果我想在会话B中更新A,就会堵塞。会话A已经锁定了这一行,这不是死锁:只是堵塞而已。
如果我再回到会话A,试图更新表B,这就会导致一个死锁。要在这个会话中选择一个作为牺牲品,
让它的语句回滚。
创建2张测试表:
SQL> select * from t1;
ID NAME
---------- ----------
1 a
2 b
SQL> select * from t2;
ID NAME
---------- ----------
1 a
2 b
第一种情况 :
两个会话持有同一对象的不同记录
---SESSION 2187
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
2187 0 0
SQL> select * from t1;
ID NAME
---------- ----------
1 a
2 b
SQL> update t1 set id=3 where id=1 and 1=1;
1 row updated.
--SESSION 98
SQL> show user
USER is "DWF"
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
98 0 0
SQL> update t1 set id=4 where id=2 and 2=2;
1 row updated.
SESSION 2187 执行:
SQL> update t1 set id=4 where id=2 and 3=3;
hang住
SESSION 98
执行SQL> update t1 set id=3 where id=1 and 4=4;
此时SESSION 2187报死锁错误
SQL> update t1 set id=4 where id=2 and 3=3;
update t1 set id=4 where id=2 and 3=3
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
查看trace文件
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000a0008-000033cd 23 2187 X 25 98 X
TX-00090011-0000155c 25 98 X 23 2187 X
session 2187: DID 0001-0017-000003E4 session 98: DID 0001-0019-000000F1
session 98: DID 0001-0019-000000F1 session 2187: DID 0001-0017-000003E4
Rows waited on:
Session 2187: obj - rowid = 00019B31 - AAAZsxAAxAAA5jWAAB
(dictionary objn - 105265, file - 49, block - 235734, slot - 1)
Session 98: obj - rowid = 00019B31 - AAAZsxAAxAAA5jWAAA
(dictionary objn - 105265, file - 49, block - 235734, slot - 0)
----- Information for the OTHER waiting sessions -----
Session 98:
sid: 98 ser: 347 audsid: 103737 user: 91/DWF
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 25 O/S info: user: oracle, term: UNKNOWN, ospid: 11545
image: oracle@dwh1 (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/4, ospid: 11544
machine: dwh1 program: sqlplus@dwh1 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update t1 set id=3 where id=1 and 4=4
[oracle@dwh1 trace]$ grep update /oracle/app/diag/rdbms/dwh1/dwh1/trace/dwh1_ora_11486.trc | grep t1
update t1 set id=3 where id=1 and 4=4
update t1 set id=4 where id=2 and 3=3
ObjectName: Name=update t1 set id=4 where id=2 and 3=3
ObjectName: Name=update t1 set id=4 where id=2 and 3=3
update t1 set id=4 where id=2 and 3=3
sql=update t1 set id=4 where id=2 and 3=3
trc文件里只有引起死锁的会话和产生死锁的会话
第二种情况:
两个会话持有不同对象的记录
--SESSION 2187
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
2187 0 0
SQL> update t2 set id=3 where id=1 and 1=1;
1 row updated.
--SESSION 98
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
98 0 0
SQL> update t1 set id=3 where id=1 and 2=2;
1 row updated.
SESSION 2187执行:
SQL> update t1 set id=3 where id=1 and 3=3;
此时HANG
SESSION 98 执行
SQL> update t2 set id=3 where id=1 and 4=4;
此时SESION 2187
SQL> update t1 set id=3 where id=1 and 3=3;
update t1 set id=3 where id=1 and 3=3
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
SQL> SQL> SQL>