oracle資料庫死鎖原因及分析

定義: 當兩個用戶希望持有對方的資源時就會發生死鎖.

即兩個用戶互相等待對方釋放資源時,oracle認定為產生了死鎖,在這種情況下,將以犧牲一個用戶作為代價,另一個用戶繼續執行,犧牲的用戶的事務將回滾.

例子：

1：用戶1對A表進行Update，沒有提交。

2：用戶2對B表進行Update，沒有提交。

此時雙反不存在資源共享的問題。

3：如果用戶2此時對A表作update,則會發生阻塞，需要等到用戶一的事物結束。

4：如果此時用戶1又對B表作update，則產生死鎖。此時Oracle會選擇其中一個用戶進行會滾，使另一個用戶繼續執行操作。

起因:

Oracle的死鎖問題實際上很少見，如果發生，基本上都是不正確的程序設計造成的，經過調整後，基本上都會避免死鎖的發生。

在Oracle系統中能自動發現死鎖，並選擇代價最小的，即完成工作量最少的事務予以撤消，釋放該事務所擁有的全部鎖，記其它的事務繼續工作下去。

從系統性能上考慮，應該儘可能減少資源競爭，增大吞吐量，因此用戶在給並發操作加鎖時，應注意以下幾點：

1、對於UPDATE和DELETE操作，應只鎖要做改動的行，在完成修改後立即提交。

2、當多個事務正利用共享更新的方式進行更新，則不要使用共享封鎖，而應採用共享更新鎖，這樣其它用戶就能使用行級鎖，以增加並行性。

3、儘可能將對一個表的操作的並發事務施加共享更新鎖，從而可提高並行性。

4、在應用負荷較高的期間，不宜對基礎數據結構（表、索引、簇和視圖）進行修改

如果死鎖不能自動釋放，就需要我們手工的kill session。 步驟如下：

1. 查看有無死鎖對象，如有kill session

/* Formatted on 2010/8/18 9:51:59 (QP5 v5.115.810.9015) */

SELECT "alter system kill session """ || sid || "," || serial# || """;" "Deadlock"

FROM v$session

WHERE sid IN (SELECT sid

FROM v$lock

WHERE block = 1);

如果有，會返回類似與如下的信息：

alter system kill session "132,731";

alter system kill session "275,15205";

alter system kill session "308,206";

alter system kill session "407,3510";

kill session：

執行alter system kill session "391,48398"(sid為391);

注意： 應當注意對於sid在100以下的應當謹慎，可能該進程對應某個application，如對應某個事務,可以kill.

2. 查看導致死鎖的SQL

/* Formatted on 2010/8/18 0:06:11 (QP5 v5.115.810.9015) */

SELECT s.sid, q.sql_text

FROM v$sqltext q, v$session s

WHERE q.address = s.sql_address AND s.sid = &sid -- 這個&sid 是第一步查詢出來的

ORDER BY piece;

返回：

SID SQL_TEXT

---------- ----------------------------------------------------------------

77 UPDATE PROFILE_USER SET ID=1,COMPANY_ID=2,CUSTOMER_ID=3,NAMED

77 _INSURED_ID=4,LOGIN=5,ROLE_ID=6,PASSWORD=7,EMAIL=8,TIME_ZON

77 E=9 WHERE PROFILE_USER.ID=:34

3 rows selected.

3. 查看誰鎖了誰

/* Formatted on 2010/8/18 0:07:49 (QP5 v5.115.810.9015) */

SELECT s1.username

|| "@"

|| s1.machine

|| " ( SID="

|| s1.sid

|| " ) is blocking "

|| s2.username

|| "@"

|| s2.machine

|| " ( SID="

|| s2.sid

|| " ) "

AS blocking_status

FROM v$lock l1,

v$session s1,

v$lock l2,

v$session s2

WHERE s1.sid = l1.sid

AND s2.sid = l2.sid

AND l1.BLOCK = 1

AND l2.request > 0

AND l1.id1 = l2.id1

AND l2.id2 = l2.id2;

或者

/* Formatted on 2010/8/18 0:03:46 (QP5 v5.115.810.9015) */

SELECT /*+ rule */

LPAD (" ", DECODE (l.xidusn, 0, 3, 0))

|| l.oracle_username

User_name,

o.owner,

o.object_name,

o.object_type,

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

ORDER BY o.object_id, xidusn DESC

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！