手把手教你处理TX锁.docx
有经验的DBA在遇到TX锁时，第一反响就是查询v$lock和v$session视 图，定位LMODE和REQUEST类型互斥的会话并进行查杀。然而，随着数 据库版本不断地迭代更新，v$session视图的内容越来越丰富，可以直接使用 blocking_sessionx blocking_instances final_blocking_instance 和 finaLblocking_session字段进行定位。对于锁层次的排查可以重复查询 v$session来确定，但如果锁层次有100层，那么通过人工遍历100次的方 式，显然过于低效，不适用于生产环境。下面就来介绍主角：Oracle的SYS_CONNECT_BY_PATH函数。自Oracle 9i开始，DBA就可以使用SYS_CONNECT_BY_PATH函数将父节点到当前 行的内容以“路径”或层次的形式显示出来。该功能刚好符合我们递归查找锁 层次的需求，在这里，笔者模拟了锁环境，可以使用如下语句查询锁信息：SQL> select a.inst_id,a process,a . sid,a serial#a sql_id,a event,a status,a program,a machineconnect_by_isleaf as isleafsys_connect_by_path(a.SID | a.inst_id> 1 <-')treelevel as tree_levelfrom gv$session astart with a.blocking_session is not nullconnect by (a.sid | a.inst_id) = prior(a.blocking_session | | | a.blocking_instance);<!-省略局部列INST_ID PROCESS SID SERIAL# EVENTSTATUS IS LEAF TREETREE_LEVEL17663176749 enq: TX- row lock contentionACTIVE0 < -171116198259989 SQL*Netmessage from clientINACTIVE1 <-171<-2512163102823199 enq: TX- row lock contentionACTIVE0 <-281116198259989 SQL*Netmessage from clientINACTIVE1 <-281<-2512下面对代码段中的局部参数进行说明。 INSTD :会话所在的节点号。 PROCESS :客户端进程号，与v$process中的spid不是同一个。 SID、SERIAL#. SQLD、STATUS. PROGRAM. MACHINE :会话信 息。 ISLEAF :是否为源头，0代表否，1代表是。 TREE :树形结构，锁的层次，例如，<-1522 <- 1532 <- 1611,从左到 右依次表示为节点2的会话152被节点2的会话153堵塞，而节点2的会话 153又被节点1的会话161堵塞。所以节点1的会话161是锁的源头。 TREE_LEVEL :树形层次。锁源头的查杀方法有两种，说明如下。1）通过ISLEAF进行筛选，直接查杀锁源头，语句如下：SQL> select 1 alter system kill session '', | | sid | | ' ' | | ,J | | serial# /inst_id | ''' immediate;' db_kill_sessionfrom (select a.inst_id>a process,a . sid,a serial#/a sql_id,a event,a status,a programa machine,connect_by_isleaf as isleaf,sys_connect_by_path(a.SID | a.inst_id,<-1) tree,level as tree_levelfrom gv$session astart with a.blocking_session is not nullconnect by (a. sid | | a.inst_id) = prior(a.blocking_session |a blocking_instance)where isleaf = 1order by tree_level asc;KILL SESSIONalter system kill session '161,5579,1' immediate;alter system kill session '161,5579,工'immediate;SQL> select inst_id, 'kill -9 ' | spid os_kill_session from (select p,inst_id, p spid, a . sid, a serial#) a sql_id, a event, a status, a program, a.machine, connect_by_isleaf as isleaf sys_connect_by_path(a.SID | a,inst_id,'<-')tree,level as tree_levelfrom gvjsession a, gvjprocess pwhere a.inst_id = p.inst_idand a.paddr = p.addrstart with a.blocking_session is not nullconnect by (a.sid | a.inst_id) = prior(a.blocking_session |a blocking_instance)where isleaf = 1order by tree_level asc;INST_ID OS_KILL_SESSION1 kill -9 300492)借助 v$session 中的 final_blocking_instance 和 final_blocking_session 定 位锁源头，语句如下：SQL> select 'alter system kill session ''' | ss.sid | '' |'/ II ss.serial# /ss.inst_id | ''' immediate;' db_kill_sessionfrom gv$session s, gv$session sswhere s.final_blocking_session is not nulland s.final_blocking_instance = ss.inst_idand s.final_blocking_session = ss.sidand s . sid <> ss.sidDB_KILL_SESSIONalter system kill session '161,5579,1' immediate;alter system kill session '161,5579,1' immediate;SQL> select pinst_id, 1 kill -9 ' | | p.spid os_kill_sessionfrom gv$session s, gv$session ss, gv$process pwhere s.final_blocking_session is not nulland s.final_blocking_instance = ss.inst_idand s.final_blocking_session = ss.sidand ss.paddr = p.addrand ss.inst_id = p.inst_idand s.sid <> ss.sidINST_ID OS_KILL_SESSION1 kill -9 30049执行拼接生成的语句，即可杀掉锁的源头。想必大家都遇到过在数据库层面发起“alter system kill session”（数据库层杀 掉会话，不加immediate关键字）时，经常会出现资源无法及时释放、会话 一直处于killed状态的情况。如果这个会话是锁的源头，那么除了等待 PMON （进程监视器）来清理之外，再没有更好的方法了，而在操作系统层 面杀掉进程的方式，基本上是百试百灵。使用系统命令“k川-9”杀死进程，系 统向该process进程发出sigkill, sigkill信号直接发送给init进程，终止 process进程。这种方式直接终止了 Oracle会话中对应的操作进程，资源也可 以直接释放。下面就来重点讲解“alter system kill session”的过程，以及在“alter system kill session”杀掉会话之后，为何会查不到处于killed状态的会话所对应的系 统进程spido“alter system kill session"（不加immediate关键字）杀掉会话可分为两种场 景进行讨论：会话状态分别是active和inactiveo使用此命令杀掉处于active 状态的会话时，过程可以简单概括如下：会话在收到kill信号后进行回滚， 此过程不可被中断，直至过程完成，该会话会接收到“ORA-00028: yoursession has been killed”信息,PMON清理会话,释放资源。如果1分钟过 后，上述动作还未完成，那么该会话将被标记为killed状态，假设会话拥有的资 源未释放，那么等待PMON进程清理会话。使用此命令杀掉处于inactive状态 的会话时，过程可以简单概括如下：会话在收到kill信号后被标记为killed状 态，会话拥有的资源未释放，等待PMON进程清理会话。如果会话再次发出 查询信号，会话就会接收到“ORA-00028: your session has been killed”信息、， PMON清理会话，释放资源。接下来模拟不加immediate参数，杀掉会话后状态被标记为killed,操作系统 查不到进程的实验场景，过程如下：SQL> select username,sid,serial#,paddr serverstatus from v$session where username = 'SCOTT'USERNAMEUSERNAMESID SERIAL# PADDRSERVER STATUSSCOTT176733 00000000A34C7040 DEDICATEDINACTIVESCOTT1589177 00000000A34D4998 DEDICATEDINACTIVESQL> select bsid,bserial#,cspid,bstatus from v$session b,v$process c whereb.paddr = c.addr and b.sid in (17,158);SIDSERIAL# SPIDSTATUS17176733 23883INACTIVE1589177 24120INACTIVE手动杀掉这两个会话的命令如下：SQL> alter system kill session '17,6733'SQL> alter system kill session '158,9177'再次查询这两个会话的状态，命令及结果如下：SQL> select username, sid, serial#paddrserv/er status from v$session lA/here username = 'SCOTT1;USERNAME SID SERIAL# PADDRSERVER STATUSSCOTT176733 00000000A3551F18 PSEUDOKILLEDSCOTT1589177 00000000A3551F18 PSEUDOKILLED从代码中我们可以发现，当两个会话的状态为killed时，会话的paddr指向同 一地址00000000A3551F18 （虚拟地址），此地址在操作系统层面并无对应 的spid,这就是当会话的状态变为killed之后，使用以下语句查不到spid的 原因，查询例如代码如下所示：SQL> select b.sidb.serial# c.spidj b.status from v$session bj v$process c inhereb.paddr = c.addr and b.sid in (17,158);no rows selected此时，我们就可以使用前文的查询语句，查杀并清理会话，命令及结果如下：SQL> select 'alter system kill session ''' | | c.sid | | ' ' | | J | | c.serial# /immediate; 1 kill_session from v$session c where status=,KILLED,;KILL SESSIONalter system kill session 11767331 immediate;alter system kill session '158,9177' immediate;因此，在查杀会话时，可以考虑直接使用“alter system kill session !sid,serial#1 immediate”命令快速清理会话。需要注意的是，在查杀会话之前一定要再三 确认信息，千万不要误杀了系统核心进程。
