enq: TX - row lock contention 等待事件

小灰灰 2022-02-04 05:45 165阅读 0赞

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的意思是得到或者得不到，不会等待