oracle死锁模拟

 

 

环境介绍：

用户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