关于数据库的锁相关问题及其引申思想

　　问题背景：考虑到我局内部数据库关系复杂，“死锁”现象时有发生，而据我所知，目前的系统死锁发现机制，全凭人工干预，发现死锁需要手工运行相应脚本或者得到用户的“举报”，然后根据脚本查询结果，调用相应命令杀死要害进程，。
　　
　　
　　实现目标：解决以往人工干预的弊端，及时快速的发现问题，解决问题，在用户“举报”之前解决问题。
　　
　　分析问题：实现目标重在“及时性”。原有思想有两个：
　　1、采用报警机制，即在出现死锁的时候采用邮件通知之类的方式告诉治理员，然后由治理员查杀死锁进程。
　　2、采用自动判定、自动解除死锁的机制，同时可以记录相应操作。
　　结合两种思路，分析其实现的可行性和优劣性。
　　方案1：这种思路主要是出于安全性考虑，在查杀进程方面，人工判定究竟相对来说比较安全可靠，不至于乱操作。但是，对于“及时性”要求还不能满足，因为我们无法保证治理员能按时查看邮件（因为他不可能总是在电脑前盯着屏幕）。
　　方案2：这种思路主要是出于“及时性”考虑，也就是说，对于死锁，一经发现，立毙无疑。
　　
　　个人认为方案2比较可行，在查找资料的过程中，都是以基于方案2的思路展开的。
　　
　　具体解决步骤：
　　死锁，从定义上讲，即：同一进程集合中的每一个进程都在等待同一集合中的其他进程释放资源。这便是真正意义上的死锁，而我们结合具体问题，赋予我们这的“死锁”两种概念。其一、上述概念中的死锁，亦称真正意义上的死锁。其二，“假死锁”，也就是锁等待现象，等待时间过长也被判作死锁。
　　所以，考虑到这两种锁，我们实际上可以用同一种名词予以描述，即“锁等待”，不同的是，第一种锁是“无限期锁等待”，第二种锁是“有限期锁等待”。
　　
　　要实现方案2中的目标，首先我们需要一个驻留在系统中的不断定时执行的进程，该进程的作用是查杀用户见死锁进程，并汇报结果。下面先就如何使一个进程在系统中定时执行展开讨论。
　　【如何使一个自定义的进程定时执行？】
　　方法一，采用Oracle提供的DBMS_JOB包来实现。
　　步骤1、构造一个实现既定目标的自定义过程。例如：
　　SQL>Create or replace procedure test as
　　Begin
　　……
　　语句段；
　　……，
　　end;
　　这样便创建好了一个过程。下面就是要使其自动执行了。
　　步骤二、定义一个返回JOB号的参数,并创建过程。
　　SQL>variable job1 number
　　SQL>begin
　　DBMS_JOB.submit(:job1,’test;’,sysdate,sysdate+1/1440’);
　　――天天1440分钟，即一分钟运行test过程以此，间隔可自定；
　　end;
　　/
　　步骤三，运行该JOB。
　　SQL>begin
　　DBMS_job.run(:job1);
　　End;
　　/
　　这样便创建出了自己定义的进程，而且能够定时执行。
　　当不需要的时候可以删除JOB。
　　SQL> begin
　　Dbms_JOB.remove(:job1);
　　End;
　　要删除特定进程，可以通过查询user/all/dba-jobs视图中的job字段，然后通过DBMS_job.remove删掉。
　　
　　（Oracle提供的程序包很有用，下面还将陆续提到的主要有DBMS_Alert, DBMS_pipe, DBMS_output, UTL_file等，具体用法可以查询帮助信息，或通过OEM工具直接查看这些包的源代码）.
　　
　　方法二：采用快照和触发器结合使用以达到定时执行的目的。
　　已经知道的是快照刷新可以定时执行，这在创建快照的时候便已经定义了其刷新间隔，因此很轻易结合触发器思想定时执行相应操作。
　　例如：
　　create snapshot 快照名 refresh next round(sysdata+0.5)+116/144
　　as select * from dual;
　　天天完19：20执行，这个快照什么也不错，只是为了触发下面的触发器。
　　Create or replace trigger 触发器名 before insert on 快照基表 for each row
　　Begin
　　…..;
　　自定义过程语句；
　　…….
　　Exception when other then
　　错误提示；
　　end;
　　这样就可以根据快照的刷新间隔调用自定义的PL/SQL过程了。
　　
　　说明：通过上述思想实现的定时执行操作，必须结合操作系统中的进程调度算法考虑，也就是说，并不是你定义的间隔是多少，它就多长时间执行一次。
　　因为这其中需要涉及到操作系统的时间片分配，实际的时间间隔最后将会是时间片的整数倍。因此应该意识到这一点，尤其是当前的服务器中运行的进程数量众多的情况下，这种时间“差距”体现的更加明显。

　　
　　到这一步，关于自定义过程的定时执行，我们已经解决了。那么下一步就是发现死锁进程，并对其进行操作。
　　【如何发现死锁进程？】
　　有很多脚本可以发现死锁进程，以下提供的一个脚本，也能实现这项要求。
　　首先创建几个基表及索引：
　　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视图中取出字段，创建my_sqlText表，并在查询要用到的字段上创建索引，加快查询速度)
　　然后构造SQL脚本如下：
　　
　　
　　Set echo off
　　set feedback off
　　prompt '删除旧记录...'
　　truncate table my_session;
　　truncate table my_lock;
　　truncate table my_sqltext;
　　
　　prompt '获取数据....'
　　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;
　　这样便可以查出死锁进程。
　　【如何根据查出的死锁进程自动调用语句杀死进程？】
　　这涉及到查询字段和自定义变量的信息互通，普通的SQL中的查询语句select出的字段只是供屏幕输出之用，而我们最终的实现思想则是要根据上面的那几个查询出来的字段数据作相应处理操作。
因此如何将这些字段数据和DBA命令中的参数挂钩，是能够自动执行杀进程的很重要的一点。
　　对于单条记录，可以采用select…into语句将用户查询的记录数据赋给PL/SQL变量。对于多条记录，则应该采用游标(cursor)来实现。
　　由于查出的死锁进程可能不止一个，因此需要使用游标（cursor）记录下数据，再自动kill掉。
　　这方面的内容需要在自定义过程中完成，具体的细节问题在此不一一细列。
　　【在查杀进程的过程中自动产生报警信息】
　　1．关于DBMS_Alert包的应用。
　　当数据库中发生了一个事件时，用DBMS_ALERT包对一个会话提出警告，用户的会话登记所关心的事件，当该事件发生时，这个警告就给会话发出信号。该包中有以下几个模块。
　　名称说明
　　REGISTER 注册登记用户的会话关心一个警告
　　REMOVE 告诉数据库用户的会话取消警告关联
　　REMOVEALL 从所有的警告注册表中删除用户的会话
　　SET_DEFAULTS 设置包开始一个检查循环时的等待事件
　　SIGNAL 发出警告