Row-X(SX)锁在Oracle的锁中级别是3,是行级排它锁,即在提交前不允许做DML操作 Insert、Update、 Delete、Lock row share。
这里要说的的是Row-X(SX)锁引起的问题,不过这里部分内容也只是推测,因为之前的没有留足足够的证据来说明这个观点。
之前发生过修改业务系统的一个核心存储过程,导致其他关联的过程也全部无效的情况,并且还不能直接进行编译,需要在OS级别kill 进程后才能编译的情况。
在这两种情况都是kill 掉相关进程才解决问题,因为业务需要,需要在次修改核心存储过程,为避免出现类似问题,提前做了一些准备工作。
1. 在修改之前查看对象持有锁的情况
在这里,我们可以看到都是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_TEXT
FROM V$LOCK L,
SYS.DBA_OBJECTS O,
V$SESSION S,
V$ACCESS A,
V$SQL S2
WHERE L.SID = S.SID
AND L.ID1 = O.OBJECT_ID
AND S.SID = A.SID
AND S2.HASH_VALUE = S.SQL_HASH_VALUE
AND A.OBJECT = 'PROC_VALIDATE_RULE_V3';
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.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
AND 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.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN
(850, 968, 991, 1039, 968, 991, 1039, 1009, 732, 850, 732);
然后在OS 级别直接kill 掉这些进程就可以了:
[oracle@qs-xezf-db1 ~]$ ps -ef|grep 6101
oracle 6101 1 0 May13 ? 00:01:06 oraclexezf (LOCAL=NO)
oracle 16790 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
oracle 6279 1 0 May13 ? 00:00:57 oraclexezf (LOCAL=NO)
oracle 16824 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)全部释放,过程对象上的操作也顺利进行,没有出现等待。
这里注意的是,修改之后会导致一些对象的无效,需要查看并重新编译这些无效对象。