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

死锁产生的原因:如果有两个会话，每个会话都持有另一个会话想要的资源，此时就会发生死锁。

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