Oracle資料表死鎖的解決方法

xumenger發表於2016-02-19
Oracle

一個簡單的排查和解決方法

死鎖時會報錯：ORA-00060: deadlock detected while waiting for resource

對應的中文報錯是：ORA-00060: 等待資源時檢測到死鎖

執行下面的SQL，檢視被鎖的表：

select object_name, machine, s.sid, s.serial#
  from v$locked_object l, dba_objects o, v$session s
 where l.object_id = o.object_id
   and l.session_id = s.sid

執行下面的SQL可以強制解鎖

alter system kill session `277,1817`
--其中277對應上句SQL查出來的sid欄位， 1817對應serial欄位具體的值

具體的操作可以看下面的例項，更加直觀的理解死鎖，並且去解決死鎖

通過一個例項操作來更深入的理解

參考自一個常見的ORA-00060死鎖現象

在Oracle資料庫中如果出現死鎖現象，資料庫就會報ORA-00060的錯誤程式碼，這種死鎖現象通常都是應用邏輯設計出錯導致的異常，和資料庫本身的設計無關，現在通過實驗模擬一個死鎖現象：

模擬死鎖現象

一定要自己實際動手去操作一下，也就花1個小時的時間，不過可以對死鎖有一個很直觀的認知！

建立一個用於測試的資料表，並且新增幾條測試資料：

create table practice(uno varchar(8), uname varchar(20));

insert into practice values (`198`, `xm198-1`);
insert into practice values (`198`, `xm198-2`);
insert into practice values (`200`, `xm200-1`);
insert into practice values (`200`, `xm200-2`);
commit;

開啟一個PLSQL，在PLSQL中開啟兩個Command Window執行下列更新順序（下面的會話就是指Command Window）

會話1：執行對uno為198的欄位更新，注意不執行commit;

SQL> update practice set uname = `cj` where uno = `198`;
2 rows updated

會話2：執行對uno為200的欄位更新，注意不執行commit;

SQL> update practice set uname = `hh` where uno = `200`;
2 rows updated

會話1：再執行對uno為200的欄位更新，注意不執行commit;，此時語句已經hang住（也就是卡住了，不像上面兩次的執行會輸出2 rows updated這樣的結果資訊），需要等到會話2發出commit或者rollback動作

SQL> update practice set uname = `cj` where uno = `200`;   ---會話1在這裡hang住了

會話2：一旦執行下面的更新，會話2也會hang住，回到會話1就會發現會話1報錯

SQL> update practice set uname = `sdf` where uno = `198`;

回到會話1，可以看到會話1報錯資訊

SQL> update practice set uname = `cj` where uno = `200`;
update practice set uname = `cj` where uno = `200`
ORA-00060: 等待資源時檢測到死鎖

查詢alert日誌發現報錯：ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/prod/udump/prod_ora_4273.trc.

詳細解釋一下這種情況的死鎖，隨便問一個人死鎖是怎麼產生的，懂點計算機知識的人就會說是迴圈等待，那麼這個例子中怎麼出現迴圈等待的呢：

  • 會話1先去更新uno = `198`的記錄，但是沒有commit或者rollback，那麼會話1就一直“佔用” uno = `198`的記錄

  • 同樣會話2先去更新 uno = `200`的記錄，但是也沒有commit或者rollback，那麼會話2就一直“佔用” uno= `200` 的記錄

  • 接著會話1又去嘗試更新uno = `200` 的記錄，但是這些記錄已經被會話2佔用了，所以就出現了會話1 hang住的情況，其實就是會話1在等待會話2“釋放”它所“佔用”的 uno = `200`的記錄

  • 接著會話2又去嘗試更新 uno = `198` 的記錄，這樣就和會話1產生了迴圈等待

  • 所以也就產生了死鎖

  • 資料庫使用SQL修改資料時是要加鎖的，一般是行級鎖，所以上面所謂的佔用其實就是對符合條件的行加鎖，因為沒有執行commit或者rollback，所以就一直加鎖

  • 這一點需要再去深入學習資料庫鎖、SQL的執行原理、資料庫原理等知識，以更加深入的學習資料庫知識，而不要只是停留在目前淺顯的層面

  • 另外就像上面所說的，如果自己開發的程式在執行過程中出現死鎖的問題:

    • 這種死鎖現象通常都是應用邏輯設計出錯導致的異常，和資料庫本身的設計無關

    • 所以需要檢查自己的程式在涉及到資料庫操作的方面是不是設計的有問題

    • 如果是一個簡單的程式可能查起來很簡單

    • 但是如果是一個大的專案，有很多的開發組都可能涉及到對資料庫的操作，那麼查起來可能就會比較難了

    • 這就需要自己有更大的更巨集觀的角度，能夠對整個大的專案架構有一個把握，另外就是能夠對資料庫原理層面的知識有深入的理解和掌握

補充說明：

如果在會話2中第二次不執行上面的更新SQL，而是執行commit;或者rollback;那麼會話1就不會報錯

比如會話2執行

SQL> rollback;
Rollback complete

回去檢查會話1，發現之前hang住的地方，現在已經解決了

SQL> update practice set uname = `cj` where uno = `200`;    --之前是hang在這裡沒有辦法更新的，現在因為會話2執行rollback，所以會話1從hang住恢復過來了
2 rows updated

強制解鎖

注意這裡需要使用系統使用者，因為一般使用者是沒有許可權的，我是重新開啟一個PLSQL，使用SYSTEM使用者登陸進行下面的操作的，再在新的PLSQL中開啟一個Command Window。之所以重新開啟一個PLSQL，是為了保證能夠不影響原有的PLSQL中登入使用者的情況下，使用SYSTEM使用者進行登入。當然新不新開PLSQL只是表面現象而已，沒有必要深究什麼！

在新的PLSQL的會話中，通過dba_blockers表中的HOLDING_SESSION欄位可以查詢到hang住會話的ID：

SQL> select * from dba_blockers;
HOLDING_SESSION
---------------
             76

使用v$session檢視獲取hang住會話的sid和serial#

SQL> select sid,serial#,username from v$session where sid in (select blocking_session from v$session);
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        76        271 TRADE

找到hang住的會話後，執行alter system命令kill掉相應的session就可以了：

SQL> alter system kill session `76,271` immediate;
System altered

檢查之前的會話來確認一下

執行後上面的強制解鎖之後，返回前一個PLSQL，檢查其中的兩個會話，會話1中的會話會自動被kill掉

在會話1執行查詢SQL，會發現報錯：

SQL> select * from practice;
Warning: connection was lost and re-established
UNO      UNAME
-------- --------------------
198      xm198-1
198      xm198-2
200      xm200-1
200      xm200-2

會話2中執行查詢發現會話2的更改生效。在會話2中執行下面查詢SQL，沒有報錯，其實當前

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      sdf
198      sdf
200      hh
200      hh

但是再在當前的PLSQL開啟一個Command Window（叫會話3），執行查詢語句，發現結果和會話2不一致

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      xm198-1
198      xm198-2
200      xm200-1
200      xm200-2

回到會話2，發現還沒有提交，所以在提交後，再執行查詢看結果

SQL> commit;
Commit complete

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      sdf
198      sdf
200      hh
200      hh

然後再回到會話3，執行查詢語句，發現現在和會話2一樣了

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      sdf
198      sdf
200      hh
200      hh

實際上，當出現死鎖的情況，Oracle也會在一段時間後解鎖。這種情況會在alert日誌中記載下列資訊：ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc.linux

相關文章