ORA-00060的示例與若干場景

bisal發表於2013-11-06

create table eg_60 ( num number, txt varchar2(10) );

insert into eg_60 values ( 1, 'First' );

insert into eg_60 values ( 2, 'Second' );

SQL> select rowid, num, txt from eg_60;

ROWID                     NUM TXT
------------------ ---------- ----------
AAAQT2AAHAAAEdYAAA          1 First
AAAQT2AAHAAAEdYAAB          2 Second


Session1：

update eg_60 set txt='ses1' where num=1;

Session2：

update eg_60 set txt='ses2' where num=2;

update eg_60 set txt='ses2' where num=1;

Session1：

update eg_60 set txt='ses1' where num=2;

此時Session2報的錯：

update eg_60 set txt='ses2' where num=1
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Session1的update eg_60 set txt='ses1' where num=2;仍處hang狀態，此時Session2執行exit正常退出，則

Session1：

update eg_60 set txt='ses1' where num=2;

1 row updated.

原因就是正常退出，Oracle的PMON會自動rollback所做的未Commit操作，釋放了num=2的資源，因此Session1可以執行。


出現60錯誤會產生一個trace檔案，檢視trace檔案位置：

show parameter user_dump
NAME                                  TYPE       VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                 string      /opt/app/ora10g/admin/petest/udump


檢視trace檔案：

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090004-00019887        25     478     X             24     459           X
TX-000a002d-00032a8d        24     459     X             25     478           X
session 478: DID 0001-0019-00027AEC     session 459: DID 0001-0018-000CDDD8
session 459: DID 0001-0018-000CDDD8     session 478: DID 0001-0019-00027AEC
Rows waited on:
Session 459: obj - rowid = 000104F6 - AAAQT2AAHAAAEdYAAB
  (dictionary objn - 66806, file - 7, block - 18264, slot - 1)
Session 478: obj - rowid = 000104F6 - AAAQT2AAHAAAEdYAAA
  (dictionary objn - 66806, file - 7, block - 18264, slot - 0)
Information on the OTHER waiting sessions:
Session 459:
  pid=24 serial=34722 audsid=899246 user: 65/DCSOPEN
  O/S info: user: dcsopen, term: pts/0, ospid: 8838, machine: vm-vmw4131-t
            program: sqlplus@vm-vmw4131-t (TNS V1-V3)
  application name: SQL*Plus, hash value=3669949024
  Current SQL Statement:
  update eg_60 set txt='ses1' where num=2
End of information on OTHER waiting sessions.
Current SQL statement for this session:
update eg_60 set txt='ses2' where num=1
===================================================

這裡66806代表的OBJECT_ID對應object是eg_60。當前執行的SQL是update eg_60 set txt='ses2' where num=1，是這條SQL報的60錯誤，原因是由於update eg_60 set txt='ses1' where num=2這條SQL。因為這裡是在同一臺機器開的兩個session，如果是不同機器客戶端訪問資料庫做的這個實驗，就可以根據machine: vm-vmw4131-t知道是哪個客戶端執行的這條SQL。


通過PROCESS STATE節中O/S info: user: dcsopen, term: pts/1, ospid: 13112, machine: vm-vmw4131-t也可以知道是哪個客戶端執行SQL報的60錯誤。


以上是同一張表不同session之間產生的死鎖。還有另外一種場景，也是之前這邊應用碰到的問題，即不同表之間的死鎖，剛剛初步得解的，其實上述兩種都屬於事務級別的死鎖，這裡可能說的不準確，就是因為執行一個SQL後沒有commit或rollback，再執行另外一個SQL，這兩個SQL形成一個事務，造成可能的死鎖。


關於事務，Concept中的解釋：

A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the

SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly

with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.

比如：

Session1：

UPDATE TABLE1，UPDATE TABLE2 ...

Session2：

DELETE TABLE2， DELETE TABLE1 ...

此時碰巧可能出現互相持有對方需要的資源，導致deadlock。

對於這種情況，可能的解決方法就是：將表的順序改為一致，或者拆分更小的事務，避免較差更新的情況。


另外，對於批量更新，和上面一個事務中多個表操作的原理相同，併發大則也會導致deadlock。要麼減少併發，要麼不用批量更新。其實出現deadlock，此時報錯的SQL會自動執行rollback，但注意這裡是語句級rollback，即只會rollback出錯的SQL，之前的SQL不會rollback，所以上述第一個例子中Session2中的SQL報錯，但Session1中的第二條SQL仍舊處於hang狀態，除非Session2中對應的資源釋放。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7192724/viewspace-775902/，如需轉載，請註明出處，否則將追究法律責任。

相關文章