ora-60錯誤可以發生在兩個會話之間，即兩個會話之間互相持有一個對方等待的資源（A deadlock occurs when a session (A) wants a resource held by another session (B) , but that session also wants a resource held by the first session (A). ）。如果幾個會話之間的資源爭用是環形，這也是死鎖，並且oracle同樣會監測到並處理。

以下演示：

DB Version:11.2.0.4

建測試表：

create table scott.tb_60

(

num NUMBER,

txt VARCHAR2(10)

);

insert into scott.tb_60 values ( 1, 'First' );

insert into scott.tb_60 values ( 2, 'Second' );

insert into scott.tb_60 values ( 3, 'Third' );

commit;

會話1：

更新num=1的記錄

update scott.tb_60

set txt='a'

where num=1;

會話2：

更新num=2的記錄

update scott.tb_60

set txt='b'

where num=2;

更新num=1的記錄，此時會話2等待會話1提交或回滾來釋放num=1上的tx鎖

update scott.tb_60

set txt='a'

where num=1;

會話3：

更新num=3的記錄

update scott.tb_60

set txt='c'

where num=3;

更新num=2的記錄，此時會話3等待會話2提交或回滾來釋放num=2上的tx鎖

update scott.tb_60

set txt='b'

where num=2;

會話1：

更新num=3的記錄，此時會話1等待會話3提交或回滾來釋放num=3上的tx鎖，死鎖產生

update scott.tb_60

set txt='c'

where num=3;

報錯資訊：

ORA-00060: deadlock detected while waiting for resource

資料庫alert.log中記錄錯誤

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/ct6601z3/ct6601z3/trace/ct6601z3_ora_6528.trc.

對應的trace file中記錄死鎖的相關資訊

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-000a0005-00000c72 27 197 X 28 8 X

TX-0001001a-00000ca7 28 8 X 26 132 X

TX-00030001-00000d4e 26 132 X 27 197 X

session 197: DID 0001-001B-00000004 session 8: DID 0001-001C-00000002

session 8: DID 0001-001C-00000002 session 132: DID 0001-001A-00000004

session 132: DID 0001-001A-00000004 session 197: DID 0001-001B-00000004

Rows waited on:

Session 197: obj - rowid = 00038F6D - AAA49tAAEAAABlkAAA

(dictionary objn - 233325, file - 4, block - 6500, slot - 0)

Session 8: obj - rowid = 00038F6D - AAA49tAAEAAABlkAAB

(dictionary objn - 233325, file - 4, block - 6500, slot - 1)

Session 132: obj - rowid = 00038F6D - AAA49tAAEAAABlkAAC

(dictionary objn - 233325, file - 4, block - 6500, slot - 2)

...

oracle ora-60 deadlock發生在多個會話的情況

相關文章