如果你有两个会话,每个会话都持有另一个会话想要的资源,此时就会出现死锁(deadlock)。例如,
如果我的数据库中有两个A和B,每个表都只有一行,就可以很容易地展示什么是死锁。
我要做的知识打开两个回话(例如,两个SQL*PLUS会话).在会话A中更更新A表,在会话B中更新B。
现在,如果我想在会话B中更新A,就会堵塞。会话A已经锁定了这一行,这不是死锁:只是堵塞而已。
如果我再回到会话A,试图更新表B,这就会导致一个死锁。要在这个会话中选择一个作为牺牲品,
让它的语句回滚。
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
35 0 0
SQL> create table t3(id int);
Table created.
SQL> insert into t3 values(1);
1 row created.
SQL> insert into t3 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> update t3 set id=10 where id=1;
1 row updated.
SQL> update t3 set id=30 where id=2;
update t3 set id=30 where id=2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
###############################################################
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
36 0 0
SQL> update t3 set id=20 where id=2;
1 row updated.
SQL> update t3 set id=40 where id=1;
hang-------
查看trace 文件:
----- Information for the OTHER waiting sessions -----
Session 36:
sid: 36 ser: 145 audsid: 1531179 user: 91/TEST flags: 0x45
pid: 28 O/S info: user: oracle, term: UNKNOWN, ospid: 29718
image: oracle@june (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/0, ospid: 29714
machine: june program: sqlplus@june (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update t3 set id=10 where id=1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=27jv5jpf0wh5z) -----
update t3 set id=20 where id=2
