OS环境:windows server 2008 64位
数据库版本:11.2.0
今天在使用rman备份的时候随意的查看了一下等待事件,除了了我们现在系统遇到的IO瓶颈外,还额外的发了enq: TX - row lock contention该等待事件
1:查询当前系统的等待事件
select event,sid,p1,p2,p3 from v$session_wait where event not like 'SQL*%' and event not like 'rdbms%';
EVENT SID P1 P2 P3
---------------------------------- ---- ---------- ---------- ----------
enq: TX - row lock contention 4 1415053318 196638 55836
RMAN backup & recovery I/O 5 1 256 2147483647
enq: TX - row lock contention 12 1415053318 524293 51153
RMAN backup & recovery I/O 25 1 256 2147483647
db file sequential read 27 16 2876703 1
pmon timer 33 300 0 0
db file scattered read 39 33 790536 128
VKTM Logical Idle Wait 49 0 0 0
Streams AQ: qmn slave idle wait 50 1 0 0
asynch descriptor resize 53 1 4294967295 1237
jobq slave wait 54 0 0 0
EVENT SID P1 P2 P3
------------------------------------------- ------- ---------- ----------
db file sequential read 170 33 1100519 1
direct path read 181 44 469892 124
enq: TX - row lock contention 212 1415053318 524293 51153
smon timer 225 300 0 0
enq: TX - row lock contention 232 1415053318 524293 51153
direct path read 234 16 1099776 128
Streams AQ: qmn coordinator idle wait 242 0 0 0
上面的等待事件说明session4,12,212,232想加锁,但是有别的session占着,所以等待。
enq是一种保护共享资源的锁定机制,一个排队机制,先进先出(FIFO)
发生TX锁的原因一般有几个
1.不同的session更新或删除同一个记录。
2.唯一索引有重复索引
3.位图索引多次更新
4.同时对同一个数据块更新
5.等待索引块分裂
2:下面我们通过enq: TX - row lock contention来看看这些session都在等什么
select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention';
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
87556 57 395 88
87564 57 435 0
87564 57 435 0
87564 57 435 0
87564 57 435 0
3:通过上面sql查找出来的对象编号找到对应的对象名称
SQL> select object_name from dba_objects where object_id in (87564);
OBJECT_NAME
-----------
QRTZ_LOCKS
4:通过对象名称找出该对象的对应属性,对象属性为TABLE
SQL> select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID, OBJECT_TYPE from all_objects where object_name='QRTZ_LOCKS';
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
SCHEDULE QRTZ_LOCKS 87564 87564 TABLE
5:通过正在等待的SID查看它们都在执行什么操作
SQL> select sid,sql_text from v$session a,v$sql b where sid in(4,12,41,212,232) and (b.sql_id=a.sql_id or b.sql_id=a.prev_sql_id);
SID SQL_TEXT
---- ----------------------------------------------------------------------------------------------------
4 UPDATE QRTZ_CRON_TRIGGERS SET CRON_EXPRESSION = :1 WHERE TRIGGER_NAME = :2 AND TRIGGER_GROUP = :3
12 SELECT * FROM QRTZ_LOCKS WHERE LOCK_NAME = :1 FOR UPDATE
41 SELECT * FROM QRTZ_LOCKS WHERE LOCK_NAME = :1 FOR UPDATE
212 SELECT * FROM QRTZ_LOCKS WHERE LOCK_NAME = :1 FOR UPDATE
232 SELECT * FROM QRTZ_LOCKS WHERE LOCK_NAME = :1 FOR UPDATE
从上面的结果可以看出,SCHEDULE用户下的五个session同时在执行一条相同的sql语句,对应的对象则是QRTZ_LOCKS 这个表, 所以发生了锁,从而产生等待,通过和同事的交流,得知这个一个ETL程序要访问的表,里面只有五条数据,但是却要时时调度。
6:下面我们去找一下对应sid产生的锁
SQL> select SID,TY,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 or request<>0;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---- -- ---------- ---------- ---------- ---------- ---------- ----------
41 TX 524293 51153 0 6 3846 0
12 TX 524293 51153 0 6 4190 0
232 TX 524293 51153 0 6 4626 0
212 TX 524293 51153 0 6 4749 0
4 TX 196638 55836 0 6 4755 1
44 TX 196638 55836 6 0 4765 1
由此可以查看,BLOCK=1的sid是该等待事件的根源,其他session则等待该锁被释放。
解决方法:
1:通过v$session找到BLOCK=1的用户,告知用户提交事务
2:通过sid找到pid,kill掉该进程
3:更改sql语句,SELECT * FROM QRTZ_LOCKS WHERE LOCK_NAME = :1 FOR UPDATE no wait
加nowait的意思是得到或者得不到,不会等待
posted on 2015-03-20 15:20
坏男孩 阅读(1534)
评论(0) 编辑 收藏 所属分类:
ORACLE篇章