死锁产生的原因:如果有两个会话,每个会话都持有另一个会话想要的资源,此时就会发生死锁。
2张表不同SESSION持有不同记录
SQL> create table t1(id int);
Table created.
SQL> create table t2(id int);
Table created.
SQL> select * from t1;
ID
----------
1
2
SQL> select * from t2;
ID
----------
2
1
开始测试:
SESSION 1:
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
24 0 0
SESSION 2:
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
25 0 0
SESSION 1执行:
SQL> update t1 set id=100 where id=1;
1 row updated.
SESSION 2 执行:
SQL> update t2 set id=100 where id=1;
1 row updated.
SESSION 1 继续执行:
SQL> update t2 set id=100 where id=1;
此时SESSION 1 HANG
SESSION 2继续执行:
SQL> update t1 set id=100 where id=1;
此时SESSION 1出现:
SQL> update t2 set id=100 where id=1;
update t2 set id=100 where id=1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
--------------------------------------------------------------------
SESSION 1执行:
SQL> update t1 set id=100 where id=1;
1 row updated.
SQL> update t2 set id=100 where id=1;
update t2 set id=100 where id=1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
SESSION 2 执行:
SQL> update t2 set id=100 where id=1;
1 row updated.
SQL> update t1 set id=100 where id=1;
查看trace日志:
session 25:
sid: 25 ser: 16 audsid: 1450028 user: 91/TEST flags: 0x45
pid: 23 O/S info: user: oracle, term: UNKNOWN, ospid: 5732
image: oracle@june (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/3, ospid: 5731
machine: june program: sqlplus@june (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update t1 set id=100 where id=1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=75ag6bf3qxyh7) -----
update t2 set id=100 where id=1