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

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