环境:一套4节点rac环境
模拟: instance4对表t进行锁定
instance1对表t进行更新,需要获取实例4中的锁,然后处于等待状态。
此时,dba连接上instance2后,开始分析进行干涉。
instance 4, session 1:
- SQL> conn / as sysdba
- Connected.
- SQL> select instance_name from v$instance;
- INSTANCE_NAME
- ----------------
- kevin4
- SQL> conn kevin
- Enter password:
- Connected.
- SQL> create table t (id number primary key,name varchar2(10));
- SQL> insert into t values (1,'kevin');
- SQL> insert into t values (2,'wind');
- SQL> commit;
- SQL> select * from t;
- ID NAME
- ---------- ----------
- 1 kevin
- 2 wind
- SQL> select * from t for update; --锁表
- ID NAME
- ---------- ----------
- 1 kevin
- 2 wind
- SQL>
instance 1, session 1:
- SQL> conn / as sysdba
- Connected.
- SQL> select instance_name from v$instance;
-
- INSTANCE_NAME
- ----------------
- kevin1
-
- SQL> conn kevin
- Enter password:
- Connected.
- SQL> select * from t;
-
- ID NAME
- ---------- ----------
- 1 kevin
- 2 wind
-
- SQL> update t set name='chunfeng' where id=2; --此更新操作会处于挂起,要等待实例4的锁。
instance 2, session1:
发现该问题后,dba登陆系统开始排查。
- SQL> conn / as sysdba
- Connected.
- SQL> select instance_name from v$instance;
-
- INSTANCE_NAME
- ----------------
- kevin2
-
- SQL> select s.sid,s.serial#,s.event,q.sql_text
- 2 from v$session s,v$sql q
- 3 where s.sql_id=q.sql_id
- 4 and q.sql_text like 'update t set%';
-
- no rows selected
-
- SQL> 2
- 2* from v$session s,v$sql q
- SQL> c/v$session/gv$session/
- SQL> c/v$sql/gv$sql/
- SQL> l
- 1 select s.sid,s.serial#,s.event,q.sql_text
- 2 from gv$session s,gv$sql q
- 3 where s.sql_id=q.sql_id
- 4* and q.sql_text like 'update t set%'
- SQL> set linesize 200
- SQL> col event for a50
- SQL> col sql_text for a70
- SQL> /
-
- SID SERIAL# EVENT SQL_TEXT
- -------- --------- -------------------------------- ----------------------------------------------------------------------
- 54 11 enq: TX - row lock contention update t set name='chunfeng' where id=2
- SQL> select sid,inst_id,blocking_instance,blocking_session from gv$session where sid=54;
-
- SID INST_ID BLOCKING_INSTANCE BLOCKING_SESSION
- ------ ---------- ---------------------- --------------------------
- 54 1 4 34
- 54 2
说明,根据查询出的结果可知,实例1中sid为54的session在等待实例4中sid为34session的锁。
深入查询以下:
可以清楚的看到,实例4持有,实例1 请求。
- SQL> select type,id1,id2,lmode,request from gv$lock where sid=54;
-
- TY ID1 ID2 LMODE REQUEST
- ----- ------------- ------------- ----------- ---------------
- TM 87477 0 3 0
- AE 100 0 4 0
- TX 2162702 27 0 6
-
- SQL> select inst_id,sid,type,lmode,request from gv$lock where id1=2162702 and id2=27;
-
- INST_ID SID TY LMODE REQUEST
- ---------- ------------- ---- ---------- -------------
- 1 54 TX 0 6
- 4 34 TX 6 0
- SQL> select sid,serial# from gv$session where inst_id=4 and sid=34;
-
- SID SERIAL#
- ---------- ----------
- 34 13
-
- SQL> alter system kill session '34,13,@4'; --kill掉实例4中sid为34,serial为13的会话,11g及以后的版本可以用该语法。
-
- System altered.
-
- SQL>
会话34,13kill后,instance1中session1得到锁后执行成功。
- SQL> update t set name='chunfeng' where id=2;
-
- 1 row updated.
-
- SQL>
感悟:
单实例与rac多实例,处理上思路一样,但是会增加一些集群特性,比如kill session需要指定kill哪一个实例的,不然很可能kill错了,而单实例不需要考虑这个问题。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30223222/viewspace-2136867/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30223222/viewspace-2136867/