环境介绍：
用户test01 创建表tab01，用户test02创建表tab02。Test01 更新tab01不提交，test02 更新表tab02不提交。然后test01 更新test02下的表tab02，此时有锁阻塞、锁等待情况发生。接着test02 更新test01下的tab01，那么此时就会有 test01、test02 都在等待对方的资源，但是资源无法释放，满足死锁条件死锁产生！
实施步骤
|
SQL> create user test01 identified by test01; User created. SQL> create user test02 identified by test02; User created. SQL> grant create session to test01, test02 Grant succeeded. SQL> grant resource to test01,test02; Grant succeeded. SQL> grant all on test02.tab02 to test01; Grant succeeded. SQL> grant all on test01.tab01 to test02; Grant succeeded. |
|
SQL> conn test01/test01 Connected. SQL> create table tab01 (id number); Table created. SQL> insert into tab01 values(01); 1 row created. SQL> commit; Commit complete. SQL> select * from tab01; ID ---------- 1 |
|
SQL> conn test02/test02 Connected. SQL> create table tab02 (id number); Table created. SQL> insert into tab02 values(02); 1 row created. SQL> select * from tab02; ID ---------- 2 |
|
会话1 SQL> update tab01 set id=id*1; 1 row updated. 会话2 SQL> conn test02/test02 Connected. SQL> update tab02 set id=id*1 ; 1 row updated. 会话2 SQL> update test01.tab01 set id=id*1; 此时该事务被hang住 会话1 SQL> update test02.tab02 set id=id*1; update test02.tab02 set id=id*1 * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource |