問題背景：考慮到我局內(nèi)部數(shù)據(jù)庫關(guān)系復(fù)雜，“死鎖”現(xiàn)象時(shí)有發(fā)生，而據(jù)我所知，目前的系統(tǒng)死鎖發(fā)現(xiàn)機(jī)制，全憑人工干預(yù)，發(fā)現(xiàn)死鎖需要手工運(yùn)行相應(yīng)腳本或者得到用戶的“舉報(bào)”，然后根據(jù)腳本查詢結(jié)果，調(diào)用相應(yīng)命令殺死要害進(jìn)程，。
實(shí)現(xiàn)目標(biāo)：解決以往人工干預(yù)的弊端，及時(shí)快速的發(fā)現(xiàn)問題，解決問題，在用戶“舉報(bào)”之前解決問題。
分析問題：實(shí)現(xiàn)目標(biāo)重在“及時(shí)性”。原有思想有兩個(gè)：
1、 采用報(bào)警機(jī)制，即在出現(xiàn)死鎖的時(shí)候采用郵件通知之類的方式告訴治理員，然后由治理員查殺死鎖進(jìn)程。
2、 采用自動判定、自動解除死鎖的機(jī)制，同時(shí)可以記錄相應(yīng)操作。
結(jié)合兩種思路，分析其實(shí)現(xiàn)的可行性和優(yōu)劣性。
方案1：這種思路主要是出于安全性考慮，在查殺進(jìn)程方面，人工判定究竟相對來說比較安全可靠，不至于亂操作。但是，對于“及時(shí)性”要求還不能滿足，因?yàn)槲覀儫o法保證治理員能按時(shí)查看郵件（因?yàn)樗豢赡芸偸窃陔娔X前盯著屏幕）。
方案2：這種思路主要是出于“及時(shí)性”考慮，也就是說，對于死鎖，一經(jīng)發(fā)現(xiàn)，立斃無疑。
個(gè)人認(rèn)為方案2比較可行，在查找資料的過程中，都是以基于方案2的思路展開的。
具體解決步驟：
死鎖，從定義上講，即：同一進(jìn)程集合中的每一個(gè)進(jìn)程都在等待同一集合中的其他進(jìn)程釋放資源。這便是真正意義上的死鎖，而我們結(jié)合具體問題，賦予我們這的“死鎖”兩種概念。其一、上述概念中的死鎖，亦稱真正意義上的死鎖。其二，“假死鎖”，也就是鎖等待現(xiàn)象，等待時(shí)間過長也被判作死鎖。
所以，考慮到這兩種鎖，我們實(shí)際上可以用同一種名詞予以描述，即“鎖等待”，不同的是，第一種鎖是“無限期鎖等待”，第二種鎖是“有限期鎖等待”。
要實(shí)現(xiàn)方案2中的目標(biāo)，首先我們需要一個(gè)駐留在系統(tǒng)中的不斷定時(shí)執(zhí)行的進(jìn)程，該進(jìn)程的作用是查殺用戶見死鎖進(jìn)程，并匯報(bào)結(jié)果。下面先就如何使一個(gè)進(jìn)程在系統(tǒng)中定時(shí)執(zhí)行展開討論。
【如何使一個(gè)自定義的進(jìn)程定時(shí)執(zhí)行？】
方法一，采用Oracle提供的DBMS_JOB包來實(shí)現(xiàn)。
步驟1、構(gòu)造一個(gè)實(shí)現(xiàn)既定目標(biāo)的自定義過程。例如：
SQL>Create or replace PRocedure test as
Begin
……
語句段；
……，
end;
這樣便創(chuàng)建好了一個(gè)過程。下面就是要使其自動執(zhí)行了。
步驟二、定義一個(gè)返回JOB號的參數(shù),并創(chuàng)建過程。
SQL>variable job1 number
SQL>begin
DBMS_JOB.submit(:job1,’test;’,sysdate,sysdate+1/1440’);
――天天1440分鐘，即一分鐘運(yùn)行test過程以此，間隔可自定；
end;
/
步驟三，運(yùn)行該JOB。
SQL>begin
DBMS_job.run(:job1);
End;
/
這樣便創(chuàng)建出了自己定義的進(jìn)程，而且能夠定時(shí)執(zhí)行。
當(dāng)不需要的時(shí)候可以刪除JOB。
SQL> begin
Dbms_JOB.remove(:job1);
End;
要?jiǎng)h除特定進(jìn)程，可以通過查詢user/all/dba-jobs視圖中的job字段，然后通過DBMS_job.remove刪掉。
（Oracle提供的程序包很有用，下面還將陸續(xù)提到的主要有DBMS_Alert, DBMS_pipe, DBMS_output, UTL_file等，具體用法可以查詢幫助信息，或通過OEM工具直接查看這些包的源代碼）.
方法二：采用快照和觸發(fā)器結(jié)合使用以達(dá)到定時(shí)執(zhí)行的目的。
已經(jīng)知道的是快照刷新可以定時(shí)執(zhí)行，這在創(chuàng)建快照的時(shí)候便已經(jīng)定義了其刷新間隔，因此很輕易結(jié)合觸發(fā)器思想定時(shí)執(zhí)行相應(yīng)操作。
例如：
create snapshot 快照名 refresh next round(sysdata+0.5)+116/144
as select * from dual;
天天完19：20執(zhí)行，這個(gè)快照什么也不錯(cuò)，只是為了觸發(fā)下面的觸發(fā)器。
Create or replace trigger 觸發(fā)器名 before insert on 快照基表 for each row
Begin
…..;
自定義過程語句；
…….
Exception when other then
錯(cuò)誤提示；
end;
這樣就可以根據(jù)快照的刷新間隔調(diào)用自定義的PL/SQL過程了。
說明：通過上述思想實(shí)現(xiàn)的定時(shí)執(zhí)行操作，必須結(jié)合操作系統(tǒng)中的進(jìn)程調(diào)度算法考慮，也就是說，并不是你定義的間隔是多少，它就多長時(shí)間執(zhí)行一次。
因?yàn)檫@其中需要涉及到操作系統(tǒng)的時(shí)間片分配，實(shí)際的時(shí)間間隔最后將會是時(shí)間片的整數(shù)倍。因此應(yīng)該意識到這一點(diǎn)，尤其是當(dāng)前的服務(wù)器中運(yùn)行的進(jìn)程數(shù)量眾多的情況下，這種時(shí)間“差距”體現(xiàn)的更加明顯。
到這一步，關(guān)于自定義過程的定時(shí)執(zhí)行，我們已經(jīng)解決了。那么下一步就是發(fā)現(xiàn)死鎖進(jìn)程，并對其進(jìn)行操作。
【如何發(fā)現(xiàn)死鎖進(jìn)程？】
有很多腳本可以發(fā)現(xiàn)死鎖進(jìn)程，以下提供的一個(gè)腳本，也能實(shí)現(xiàn)這項(xiàng)要求。
首先創(chuàng)建幾個(gè)基表及索引：
create table My_session as
select a.username,a.sid,a.serial#,a.lockwait,a.machine,a.status,a.last_call_et,
a.sql_hash_value,a.program
from v$session a
where 1=2;
create unique index my_session_ul on my_session(sid);
create index my_session_n2 on my_session(lockwait);
create index my_session_n3 on my_session(sql_hash_value);
Create table my_lock as
Select id1,kaddr,sid,request,type
From v$lock
Where 1=2;
Create index my_lock_n1 on my_lock(sid);
Create index my_lock_n2 on my_lock(kaddr);
Create table my_sqltext as
Select hash_value,sql_text
From v$sqltext
Where 1=2;
Create index my_sqltext_n1 on my_sqltext(hash_value);
(從V$session,V$lock,V$sqltext視圖中取出字段，創(chuàng)建my_sqlText表，并在查詢要用到的字段上創(chuàng)建索引，加快查詢速度)
然后構(gòu)造SQL腳本如下：
Set echo off
set feedback off
prompt '刪除舊記錄...'
truncate table my_session;
truncate table my_lock;
truncate table my_sqltext;
prompt '獲取數(shù)據(jù)....'
insert into my_session
select a.username,a.sid,a.serial#,a.lockwait,a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
From v$session a
where nvl(a.username,'NULL')<>'NULL';
insert into my_lock
select id1,kaddr,sid,request,type
from v$lock;
insert into my_sqltext
select hash_value,sql_text
from v$sqltext s,my_session m
where s.hash_value=m.sql_hash_value;
column username format a10
column machine format a15
column last_call_et format 99999 heading "seconds"
column sid format 9999
prompt "正在等待別人的用戶"
select a.sid,a.serial#,
a.machine,a.last_call_et,a.username,b.id1
from my_session a,my_lock b
where a.lockwait=b.kaddr;
prompt "被等待的用戶..."
select a.sid,a.serial#,a.machine,a.last_call_et,a.username,
b.type,a.status,b.id1
from my_session a,my_lock b
where b.id1 in
(select distinct e.id1
from my_session d,my_lock e
where d.lockwait=e.kaddr)
and a.sid=b.sid
and b.request=0;
prompt "查出其 SQL.."
select a.username,a.sid,a.serial#,a.status,a.last_call_et,b.id1,b.type,c.sql_text
from my_session a,my_lock b, my_sqltext c
where b.id1 in
(select distinct e.id1
from my_session d,my_lock e
where d.lockwait=e.kaddr)
and a.sid=b.sid
and b.request=0
and c.hash_value=a.sql_hash_value;
這樣便可以查出死鎖進(jìn)程。
【如何根據(jù)查出的死鎖進(jìn)程自動調(diào)用語句殺死進(jìn)程？】
這涉及到查詢字段和自定義變量的信息互通，普通的SQL中的查詢語句select出的字段只是供屏幕輸出之用，而我們最終的實(shí)現(xiàn)思想則是要根據(jù)上面的那幾個(gè)查詢出來的字段數(shù)據(jù)作相應(yīng)處理操作。
因此如何將這些字段數(shù)據(jù)和DBA命令中的參數(shù)掛鉤，是能夠自動執(zhí)行殺進(jìn)程的很重要的一點(diǎn)。
對于單條記錄，可以采用select…into語句將用戶查詢的記錄數(shù)據(jù)賦給PL/SQL變量。對于多條記錄，則應(yīng)該采用游標(biāo)(cursor)來實(shí)現(xiàn)。
由于查出的死鎖進(jìn)程可能不止一個(gè)，因此需要使用游標(biāo)（cursor）記錄下數(shù)據(jù)，再自動kill掉。
這方面的內(nèi)容需要在自定義過程中完成，具體的細(xì)節(jié)問題在此不一一細(xì)列。
【在查殺進(jìn)程的過程中自動產(chǎn)生報(bào)警信息】
1． 關(guān)于DBMS_Alert包的應(yīng)用。
當(dāng)數(shù)據(jù)庫中發(fā)生了一個(gè)事件時(shí)，用DBMS_ALERT包對一個(gè)會話提出警告，用戶的會話登記所關(guān)心的事件，當(dāng)該事件發(fā)生時(shí)，這個(gè)警告就給會話發(fā)出信號。該包中有以下幾個(gè)模塊。
名稱 說明
REGISTER 注冊登記用戶的會話關(guān)心一個(gè)警告
REMOVE 告訴數(shù)據(jù)庫用戶的會話取消警告關(guān)聯(lián)
REMOVEALL 從所有的警告注冊表中刪除用戶的會話
SET_DEFAULTS 設(shè)置包開始一個(gè)檢查循環(huán)時(shí)的等待事件
SIGNAL 發(fā)出警告
