修改2张表不同SESSION相互持有记录引发的死锁

来源：互联网发布：编程数学编辑：程序博客网时间：2024/04/30 10:47
死锁产生的原因:如果有两个会话，每个会话都持有另一个会话想要的资源，此时就会发生死锁。2张表不同SESSION持有不同记录SQL> create table t1(id int);Table created.SQL> create table t2(id int);Table created.SQL> select * from t1;ID---------- 1 2SQL> select * from t2;ID---------- 2 1开始测试:SESSION 1:SQL> select * from v$mystat where rownum<2;       SID STATISTIC#   VALUE---------- ---------- ----------24    0       0SESSION 2:SQL> select * from v$mystat where rownum<2;       SID STATISTIC#   VALUE---------- ---------- ----------25    0       0SESSION 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 HANGSESSION 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 resourceSESSION 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
0 0