oracle TX等待事件的解决方法

查询数据库等待事件：


EVENT   COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client  1
heartbeat redo informer  1
pmon timer  1
Streams AQ: qmn slave idle wait  1
Streams AQ: waiting for time management or cleanup tasks  1
Streams AQ: qmn coordinator idle wait  1
smon timer  1
lreg timer  1
SQL*Net message to client  1
VKTM Logical Idle Wait  1
AQPC idle  1
DIAG idle wait  2
Space Manager: slave idle wait 10
rdbms ipc message 17


14 rows selected.

现有表a;
SQL> select * from a;


ID NAME
---------- ----------
2 a


SQL> 

session1:--执行不提交
update a set id=1 where id=2;
SQL> update a set id=1 where id=2;


1 row updated.


SQL> 

session2:--hang住
  update a set id=3 where id=2;
SQL> 
SQL> update a set id=3 where id=2;

 查询数据库等待事件：
EVENT   COUNT(*)
---------------------------------------------------------------- ----------
enq: TX - row lock contention  1
Streams AQ: waiting for time management or cleanup tasks  1
Streams AQ: qmn coordinator idle wait  1
AQPC idle  1
heartbeat redo informer  1
pmon timer  1
smon timer  1
lreg timer  1
Streams AQ: qmn slave idle wait  1
SQL*Net message to client  1
VKTM Logical Idle Wait  1
DIAG idle wait  2
SQL*Net message from client  2
Space Manager: slave idle wait 10
rdbms ipc message 17


15 rows selected.


分析：比之前多了tx等待事件，行级锁

解决：
个人写的sql，觉得很好用，特此分享：
select  l.session_id, s.SERIAL#,o.object_name,s.PREV_SQL_ID,pr.SPID
  from v$session s,v$locked_object l, dba_lock lo,dba_objects o,v$process pr
 where l.session_id = s.SID
 and lo.session_id=l.SESSION_ID
 and lo.blocking_others not in ('Not Blocking')
 and pr.ADDR=s.PADDR
 and o.object_id=l.OBJECT_ID


或者

 select se.sid, se.SERIAL#,lo.type,lo.ctime
  from v$lock lo, v$session se
 where lo.sid = se.sid
   and lo.block = 1 

SQL> /


SESSION_ID    SERIAL# OBJECT_NAME       PREV_SQL_ID   SPID
---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------------------
74  839 A

然后kill session，先不急着kill，考虑下kill的是哪个？没错，肯定是第一条执行未提交的，第二个session成功提交。kill验证

alter system kill session ' 74 ,839';
SQL> alter system kill session '74,839';


System altered.


SQL> 
第二个窗口：
SQL> update a set id=3 where id=2;


1 row updated.


SQL> commit;


Commit complete.


SQL> 

第一个窗口:
SQL> /
update a set id=1 where id=2
*
ERROR at line 1:
ORA-00028: your session has been killed




SQL> 

成功kill源头，完美解决。哈哈哈，下午没白搞

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30536096/viewspace-1991398/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30536096/viewspace-1991398/