合 在Oracle中，更新一条记录，可能会遇到哪些故障,请尝试解决
👉 本文共约1774个字，系统预计阅读时间或需7分钟。
题目
一个RAC双节点的实例环境，面试人员使用的是实例2，而在实例1中已经使用“SELECT * FROM SCOTT.EMP FOR UPDATE;”给EMP表加锁：
1 | SQL> SELECT * FROM SCOTT.EMP FOR UPDATE; |
此时在实例2中，如果执行以下SQL语句尝试更新ENAME字段，那么必然会被行锁堵塞：
1 | SQL> UPDATE SCOTT.EMP SET ENAME='ENMOTECH' WHERE EMPNO=7369; |
请尝试解决这个故障。
答案
这道面试题中包含的知识点有：
① 如何在另外一个SESSION中查找被堵塞的SESSION信息；
② 如何找到产生行锁的BLOCKER；
③ 在杀掉BLOCKER进程之前会不会向面试监考人员询问，是否可以KILL掉阻塞者；
④ 在获得可以KILL掉进程的确认回复后，正确杀掉另一个实例上的进程。
正确的思路和解法应该如下：
（1）检查被阻塞会话的等待事件
更新语句回车以后没有回显，明显是被阻塞了，那么现在这个会话当前是什么等待事件呢？可以通过SESSION等待去获取这些信息：
1 2 3 4 5 | SQL> SELECT SID,EVENT,USERNAME,SQL.SQL_TEXT FROM V$SESSION S,V$SQL SQL WHERE S.SQL_ID=SQL.SQL_ID AND SQL.SQL_TEXT LIKE 'UPDATE SCOTT.EMP SET ENAME%'; SID EVENT USERNAME SQL_TEXT ---------- ---------------------------------- ----------- ---------------------------------------------------------------- 31 enq: TX - row lock contention SYS UPDATE SCOTT.EMP SET ENAME='ENMOTECH' WHERE EMPNO=7369 |
说明被阻塞的会话SID为31。以上使用的是关联V$SQL的SQL语句，实际上通过登录用户名等也可以快速定位被锁住的会话。
（2）查找 BLOCKER
得知等待事件是“enq: TX - row lock contention”，行锁，接下来就是要找到谁锁住了这个会话。在Oracle 10gR2以后，只需要查询GV$SESSION视图就可以迅速定位BLOCKER，通过BLOCKING_INSTANCE和BLOCKING_SESSION字段即可。
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> SELECT SID,INST_ID,BLOCKING_INSTANCE,BLOCKING_SESSION FROM gv$session WHERE INST_ID=2 AND SID=31; SID INST_ID BLOCKING_INSTANCE BLOCKING_SESSION ---------- ---------- ----------------- ---------------- 31 2 1 65 SQL> SELECT SID,EVENT,S.USERNAME,SQL.SQL_TEXT FROM GV$SESSION S,GV$SQL SQL WHERE (S.SQL_ID=SQL.SQL_ID OR S.PREV_SQL_ID=SQL.SQL_ID) AND S.INST_ID=SQL.INST_ID AND SID=65; SID EVENT USERNAME SQL_TEXT ---------- ------------------------------ --------- ------------------------------------- 65 SQL*Net message from client LHR SELECT * FROM SCOTT.EMP FOR UPDATE |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
