低調

本文轉貼自 http://www.dbmotive.com/oracle_error_codes.php?errcode=00060
關於 DeadLock 的發生在這篇有不錯的範例說明：

Transactions deadlocked one another while waiting for resources.

Once Oracle detects a conflict between transactions, it will automatically stop the transaction of one of the sessions so they will not block each other for ever.

You will find a trace file in the user_dump_dest containing the objects involved.

eg:


First setup 2 tables:

SQL> create table t1(n number);

Table created.

SQL> create table t2(n number);

Table created.

SQL> insert into t1 values(1);

1 row created.

SQL> insert into t2 values(2);

1 row created.

SQL> commit;

Commit complete.

Now you need to have 2 sessions (S1 and S2)

Timestamp   session   action

T1          S1        update t1 set n=3;
T2          S2        update t2 set n=4;
T3          S2        update t1 set n=2; --> This will wait until S1 commits the action done in T1
T4          S1        update t2 set n=4; --> This will wait until S2 commits the action done in T2

Now S2 is waiting for S1 to commit the transaction done in T1
and S1 is waiting for S2 to commit the transaction done in T2

This is a deadlock situation. After a while, S2 will time out with following error:

SQL> update t1 set n=2;
update t1 set n=2
      *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Remark: Only the action done at T3 will timeout, the lock set by S2 in T2 is still there.
S1 will still wait for the commit/rollback of the transaction done in T2