1、锁等待的诊断及排除=在 ORACLE 中，为了保证数据的一致性，在对数据库中的数据进行操作时，系统会进行对数据相应的锁定。当程序对所做的修改进行提交(commit)或回滚后(rollback)后，锁住的资源便会得到释放，从而允许其它用户进行操作。 但是，有时，由于程序中的原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，如调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现“死机“，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，影响到其它用户的操作。 这时，我们需要迅速地诊断出锁住资源的用户并解决其锁定。 1. 诊断系统中的锁为了找出
2、系统中那些用户锁住资源以及那些用户在等待相应的资源，可使用以下语句(其中的/*+ NO_MERGE(.) */千万不可省略, 否则会很慢) ：- looklock.sql- use the NO_MERGE hints can speed up the queryselect /*+ 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$sessio
3、n a, v$lock b, v$sqltext cwhere a.username is not nulland a.lockwait = b.kaddrand c.hash_value =a.sql_hash_valueunionselect /*+ 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,
4、v$sqltext cwhere b.id1 in(select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1from v$session d, v$lock ewhere d.lockwait = e.kaddr)and a.username is not nulland a.sid = b.sidand b.request=0and c.hash_value =a.sql_hash_value;执行后的结果如下所示：Stat USERNAME MACHINE SID SERIAL# Seconds ID1- - - - - - -SQL-Loc
5、k CIQUSR CIQDULMACER 12 966 245 131089select * from c_trade_mode for updateWait CIQUSR CIQDULMACER 10 735 111 131089update c_trade_mode set x_name = zzz where x_code=5Wait CIQUSR CIQDULMACER 15 106 1094 131089select * from c_trade_mode for update其中：Status 有两种状态，LOCK 表明该进程锁住了某个资源，WAIT 表示该进程正在等待某个资源。U
6、sername, Machine 分别为 ORACLE 用户名及机器名SID，SERIAL#可用于随后的解锁操作Seconds 表示该进程最后一次进行操作至当前的时间(秒)ID1, 锁标识。某个 LOCK 状态的 ID1 与某个 WAIT 状态的 ID1 相同，可说明锁的正是另一个进程等待的。SQL: 锁住资源的 SQL 语句2. 解除锁诊断出锁的状态后，若发现该阻塞其它用户进程的进程是正常操作中，则可通知该用户对其进行提交，从而达到释放锁资源的目的；若为非正常操作，即，其状态为“inactive“，且其 Seconds 已为较多长时间，则可执行以下语句将该进程进行清除，系统会自动对其进行回滚
7、，从而释放锁住的资源。 alter system kill session sid, serial#; 例如: 对于上例中显示的结果, 可用以下语句清除锁住资源的进程:alter system kill session 12, 966; 关于你所说：在网络断掉（通过拔掉网线）或非正常终止进程（通过 task manager 强行关闭 sql*plus)时，oracle 在有限的时间内（我只观查了 5-10 分）内，oracle 未能对该进程作任何处理。这个处理与 TCP 协议有关，因为 SQL NET 在使用 TCP/IP 协议进行网络连接时是一种短连接,当ORACLE 连接异常终止时，因为是
8、异常终止，终止信号并没有通过网络通知 server 端，因此只有下次server 有结果从服务器端返回需与 client 通信时，server 才会发现此 client 已经端掉。因此出现你前面所提 ORACLE 处理异常终止进程延时情况.死锁：你可以试验一条彼此存在依赖关系的 update 语句，ORACLE 处理这种锁时不是很好。查锁语句：查询产生锁的用户锁 sqlselect a.username username, a.sid sid, a.serial# serial,b.id1 id1, c.sql_text sqltextfrom v$session a, v$lock b, v
9、$sqltext cwhere b.id1 in(select distinct e.id1from v$session d, v$lock ewhere d.lockwait = e.kaddr)and a.sid = b.sidand c.hash_value = a.sql_hash_valueand b.request = 0;死锁：当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就 出现死锁。如事务 1 在表 A 行记录#3 中有一排它锁，并等待事务 2 在表 A 中记录 #4 中排它锁的释放，而事务 2在表 A 记录行#4 中有一排它锁，并等待事务 1 在表 A 中记录#3
10、 中排它锁的释放，事务 1 与事务 2 彼此等待，因此就造 成了死锁。死锁一般是因拙劣的事务设计而产生select * from v$lock ,dba_objects,v$session where object_id=v$lock.id1 and v$lock.sid=v$session.sid查看被锁的表： select p.spid,c.object_name,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a, v$locked_object b,all_objects c where
11、p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id 解锁： alter system kill session 146;(其中 146 为锁住的进程号) select al.owner “所有者“,al.object_name “表名“,al.object_type “表类型“,lo.SESSION_ID “会话 id“,s.OSUSER “登录者机器的用户“,s.MACHINE “登录者机器名“,l.CTIME “被锁时间“,l.block “=1 的是制造者“,l.type “锁类型“,p.SPID “系统进
12、程号“,s.SERIAL#from v$locked_object lojoin all_objects al on al.object_id=lo.OBJECT_IDjoin v$session s on s.SID=lo.SESSION_IDjoin v$lock l on l.SID=lo.SESSION_IDjoin v$process p on p.ADDR=s.PADDRSELECT S.SID SESSION_ID, S.USERNAME, DECODE(LMODE,0,None,1,Null,2,Row-S (SS),3,Row-X (SX),4,Share,5,S/Row-X
13、 (SSX),6,Exclusive,TO_CHAR(LMODE) MODE_HELD, DECODE(REQUEST,0,None,1,Null,2,Row-S (SS),3,Row-X (SX),4,Share,5,S/Row-X (SSX),6,Exclusive,TO_CHAR(REQUEST) MODE_REQUESTED, O.OWNER|.|O.OBJECT_NAME|(|O.OBJECT_TYPE|), S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O. OBJECT_ID;执行上记 SQL 语句,可以查寻到数据库中的锁的情报 .SESSION_ID, USERNAME, MODE_HELD, MODE_REQUESTED, OBJECT_NAME, LOCK_TYPE, LOCK_ID分别是 拥有锁的 SESSION_ID,拥有锁的 USERNAME,锁的执行模式 MODE_HELD,锁的请求 MODE_REQUESTED,锁所在的数据库对象名,锁的类型,锁的 ID
