Oracle Row-X(SX) 锁 引起的问题 说明

hbjcmzptri

　　
　　Row-X(SX)锁在Oracle的锁中级别是3，是行级排它锁，即在提交前不允许做DML操作 Insert、Update、Delete、Lock row share。
　　
　　关于Oracle 锁的说明，更多内容参考：
　　
　　ORACLE 锁机制
　　http://blog.csdn.net/tianlesoftware/article/details/4696896
　　
　　这里要说的的是Row-X（SX）锁引起的问题，不过这里部分内容也只是推测，因为之前的没有留足足够的证据来说明这个观点。
　　
　　之前发生过修改业务系统的一个核心存储过程，导致其他关联的过程也全部无效的情况，并且还不能直接进行编译，需要在OS级别kill 进程后才能编译的情况。
　　
　　Oracle 存储过程 无法编译 解决方法
　　http://blog.csdn.net/tianlesoftware/article/details/7412555
　　
　　Oracle shutdown 过程中 DB hang住 解决方法
　　http://blog.csdn.net/tianlesoftware/article/details/7407587
　　
　　在这两种情况都是kill 掉相关进程才解决问题，因为业务需要，需要在次修改核心存储过程，为避免出现类似问题，提前做了一些准备工作。
　　
　　1. 在修改之前查看对象持有锁的情况
　　Oracle 查看 对象 持有 锁 的情况
　　http://blog.csdn.net/tianlesoftware/article/details/6822321
　　



　　
　　
　　在这里，我们可以看到都是Ros-X（SX）的行级排他锁。
　　
　　2. 查看session持有这些锁的session 的情况
　　
　　这个我在之前的脚本里加了一个session的状态，脚本如下：
　　/* Formatted on 2012/6/6 10:59:49 (QP5 v5.185.11230.41888) */SELECT distinct S.SID SESSION_ID,S.STATUS,S.USERNAME,DECODE (LMODE,0, ' None ',1, ' Null ',2, ' Row-S(SS) ',3, ' Row-X(SX) ',4, ' Share',5, 'S/Row-X (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 || ' ) 'AS OBJECT_NAME,S.TYPE LOCK_TYPE,L.ID1 LOCK_ID1,L.ID2 LOCK_ID2,S2.SQL_TEXTFROM V$LOCK L,SYS.DBA_OBJECTS O,V$SESSION S,V$ACCESS A,V$SQL S2WHERE     L.SID = S.SIDAND L.ID1 = O.OBJECT_IDAND S.SID = A.SIDAND S2.HASH_VALUE = S.SQL_HASH_VALUEAND A.OBJECT = 'PROC_VALIDATE_RULE_V3';

　　
　　显示这些session 都是处于killed状态。 在之前的Blog中：
　　Oracle killsessin 说明
　　http://blog.csdn.net/tianlesoftware/article/details/7417058
　　
　　killed状态的会话，被标注为删除，表示出现了错误，正在回滚。这个过程可能需要等待远程事务的回应或者回滚事务，在这个状态会被标记为killed，并且可能需要等待很长时间，等这些操作完成之后才会kill掉。要释放这些状态为killed的session，可以重启DB，也可以直接在OS 级别kill 进程， windows 下使用ORAKILL 命令，UNIX 直接使用kill 命令。
　　
　　因为我们编译过程这些行级排他锁如果没有及时释放，我们的编译也会一直处于等待状态，所以我这里是选择在OS级别kill 掉这些session。
　　
　　
　　3. 确认killed 状态的session是否使用回滚段
　　
　　使用如下SQL：
　　/* Formatted on 2012/6/7 5:47:42 (QP5 v5.185.11230.41888) */SELECT s.username,s.sid,s.serial#,t.used_ublk,t.used_urec,rs.segment_name,r.rssize,r.statusFROM v$transaction t,v$session s,v$rollstat r,dba_rollback_segs rsWHERE     s.saddr = t.ses_addrAND t.xidusn = r.usnAND rs.segment_id = t.xidusnAND s.sid IN(850, 968, 991, 1039, 968, 991, 1039, 1009, 732, 850, 732)ORDER BY t.used_ublk DESC;

　　从查询结果看，确实在使用，不过session 是一个月之前的session，并且这些session 都是写log的操作，根据分析，可以直接在操作系统级别kill 掉进程，来释放相关的锁。
　　
　　4. 在OS 级别kill 进程
　　前面已经获取了对象上持有的session ID，这里根据Session ID 查出相关的系统SPID. Sql 语句如下：
　　/* Formatted on 2012/6/7 5:51:01 (QP5 v5.185.11230.41888) */SELECT spid, osuser, s.programFROM v$session s, v$process pWHERE     s.paddr = p.addrAND s.sid IN(850, 968, 991, 1039, 968, 991, 1039, 1009, 732, 850, 732);


　　
　　然后在OS 级别直接kill 掉这些进程就可以了：
　　
　　[oracle@qs-xezf-db1 ~]$ ps -ef|grep 6101
　　oracle6101 1 0 May13 ? 00:01:06 oraclexezf (LOCAL=NO)
　　oracle16790 16606 0 05:14 pts/2 00:00:00 grep 6101
　　[oracle@qs-xezf-db1 ~]$ kill -9 6101
　　[oracle@qs-xezf-db1 ~]$ ps -ef|grep 6279
　　oracle6279 1 0 May13 ? 00:00:57 oraclexezf (LOCAL=NO)
　　oracle16824 16606 0 05:14 pts/2 00:00:00 grep 6279
　　[oracle@qs-xezf-db1 ~]$ kill -9 6279
　　
　　
　　5. 检查
　　在OS级别kill 掉这些状态为killed 的session 之后，对象的Row-X（SX）全部释放，过程对象上的操作也顺利进行，没有出现等待。
　　这里注意的是，修改之后会导致一些对象的无效，需要查看并重新编译这些无效对象。
　　
　　
　　
　　
　　
　　
　　-------------------------------------------------------------------------------------------------------
　　版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
　　Skype:  tianlesoftware
　　QQ: tianlesoftware@gmail.com
　　Email:  tianlesoftware@gmail.com
　　Blog: http://www.tianlesoftware.com
　　Weibo:  http://weibo.com/tianlesoftware
　　Twitter: http://twitter.com/tianlesoftware
　　Facebook: http://www.facebook.com/tianlesoftware
　　Linkedin: http://cn.linkedin.com/in/tianlesoftware
　　
　　
　　-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----
　　DBA1 群：62697716(满); DBA2 群：62697977(满)DBA3 群：62697850(满)
　　DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823
　　DBA6 群：158654907 DBA7 群：172855474 DBA总群：104207940