oracle 锁等待的诊断及排除

j90cng0727

　　在ORACLE中，为了保证数据的一致性，在对数据库中的数据进行操作时，系统会进行对数据相应的锁定。
当程序对所做的修改进行提交(commit)或回滚后(rollback)后，锁住的资源便会得到释放，从而允许其它用户进行操作。
但是，有时，由于程序中的原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，如调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现"死机"，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，影响到其它用户的操作。
这时，我们需要迅速地诊断出锁住资源的用户并解决其锁定。

1. 诊断系统中的锁
为了找出系统中那些用户锁住资源以及那些用户在等待相应的资源，可使用以下语句(其中的/*+ NO_MERGE(..) */千万不可省略, 否则会很慢)：

-- looklock.sql
-- use the NO_MERGE hints can speed up the query
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Wait' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where a.username is not null
and a.lockwait = b.kaddr
and c.hash_value =a.sql_hash_value
union
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Lock' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.username is not null
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value;

执行后的结果如下所示：
Stat USERNAME MACHINE SID SERIAL# Seconds ID1
---- ------------------------------ ---------------- --------- --------- --------- ---------
SQL
----------------------------------------------------------------
Lock CIQUSR CIQ\DULMACER 12 966 245 131089
select * from c_trade_mode for update

Wait CIQUSR CIQ\DULMACER 10 735 111 131089
update c_trade_mode set x_name = 'zzz' where x_code='5'

Wait CIQUSR CIQ\DULMACER 15 106 1094 131089
select * from c_trade_mode for update


其中：
Status有两种状态，LOCK表明该进程锁住了某个资源，WAIT表示该进程正在等待某个资源。
Username, Machine分别为ORACLE用户名及机器名
SID，SERIAL#可用于随后的解锁操作
Seconds表示该进程最后一次进行操作至当前的时间(秒)
ID1, 锁标识。某个LOCK状态的ID1与某个WAIT状态的ID1相同，可说明锁的正是另一个进程等待的。
SQL: 锁住资源的SQL语句

2. 解除锁

诊断出锁的状态后，若发现该阻塞其它用户进程的进程是正常操作中，则可通知该用户对其进行提交，从而达到释放锁资源的目的；若为非正常操作，即，其状态为"inactive"，且其Seconds已为较多长时间，则可执行以下语句将该进程进行清除，系统会自动对其进行回滚，从而释放锁住的资源。

alter system kill session 'sid, serial#';

例如: 对于上例中显示的结果, 可用以下语句清除锁住资源的进程:
alter system kill session '12, 966';