陈老师:1415968548 郑老师:2735197625 乐老师:354331153
客服热线:
19941464235/19906632509(微信同号)

客服微信

【技术文章】enq TX row lock contention等待事件分析

作者:刘晓峰
发布时间:2023-02-14 10:22
浏览量:1318

(1)问题背景:

MRP请求一直执行不动


(2)问题分析:

检查v$session_waits显示等待事件enq: TX - row lock contention


(3)问题重现:

创建表和索引

create table cux_index_test(id number )
create unique index cux_index_test_u1 on cux_index_test(id)

窗口1执行insert,但是不提交,不会报错

insert into cux_index_test(id ) values(1)—会话id 2328


窗口2同样执行insert ,写入相同的值

insert into cux_index_test(id ) values(1)—会话ID 1814

此时窗口2不会报错:违反唯一性约束,而是出现等待:enq: TX - row lock contention


使用下面SQL,查询结果如下

SELECT s.event,
       s.p1text,
       chr(bitand(p1, -16777216) / 16777215) ||
       chr(bitand(p1, 16711680) / 65535) "Name",
       (bitand(p1, 65535)) "Mode",
       s.p2text,
       to_char(p2, 'xxxxxxxxxxxxxxxxxxxxxx'), --等于v$transaction.xidsqn XIDUSN  ||  XIDSLOT 
 s.p3text,
       s.p3 --等于v$transaction.xidsqn
  FROM v$session_wait s
 WHERE s.sid = 1814


   	EVENT	P1TEXT	Name	Mode	P2TEXT	TO_CHAR(P2,'XXXXXXXXXXXXXXXXXXXXXX')	P3TEXT	P3
1	enq: TX - row lock contention	name|mode	TX	4	usn<<16 | slot	                 22001f	sequence	36062450


根据这三个P1P2P3,我们就能去找到对应的事务了,现在我们拿到了USN=22,SLOT=1f,转换成10进制,USN=34,SLOT=31,加上sqn=36062450,放入我们的查询中

SELECT *
 FROM v$transaction t
WHERE t.xidusn = 34
  AND t.xidslot = 31
  AND t.xidsqn = 36062450


找到事务后,就能找到会话,传入事务表找到的ses_addr=0000001932AAC5C0


就能查到那个会话写入了相同的数据,却又不提交

select * from v$session s where s.saddr='0000001932AAC5C0'
  SID
1 2328

刚好是窗口1的会话ID


MODE=4解释如下:

TX – row lock contention mode 4 is genarally is because of unique/primary key violations ,ITL shortage and bitmap index.

唯一性约束,ITL事务槽,或者位图索引

TX – row lock contention mode 6 is because of session is waiting row level lock that is held by another session that performs operation (update or delete );

等待别的会话释放


(4)问题解决:


怎么解决呢,

1简单的方法就是杀掉旧会话,让我们的新会话能成功提交过去。

1,1传入请求ID,找到阻塞的会话(按问题分析的路子,也能找到卡住的会话,不过最快还是下面的方法)

SELECT s.sid, s.serial#,
  FROM v$session s
 WHERE s.sid =
       (SELECT s.blocking_session
          FROM v$session s
         WHERE s.audsid = (SELECT fcr.oracle_session_id
                             FROM fnd_concurrent_requests fcr
                            WHERE fcr.request_id = 208990725))

1.2执行kill命令

比如我们通过上面的SQL找到的会话ID和序列号分别是3592,8959

则执行下面SQL就行了

alter system kill session '3592,8959';

检查会话状态是KILLED表示已经杀掉


不过假如是我预测的是有人写入的数据,违反了唯一性约束,且不提交insert,那么需要从业务代码分析,为什么会出现此情况,从业务角度避免



补充说明

昨天说一了一大段,说错了,跟事务槽有关的的是这个事件enq: TX - allocate ITL entry

事务槽解释:


Oracle 在每个数据块顶部的存储区域中跟踪每个事务锁定的行,该存储区域称为感兴趣的事务列表 (ITL)。两个参数 initrans 和 maxtrans 控制 Oracle 中对象的任何块中的 ITL 槽数。

initrans 包含第一次使用时在 Oracle 数据库块中最初创建的插槽总数。maxtrans 参数设置块的感兴趣事务列表中允许的条目总数的上限。因此,每个计划修改数据库块的事务都需要在块中包含的 ITL 列表中添加一个新槽。maxtrans 参数还设置 Oracle 数据库块内任何单个时间点可能处于活动状态的并发事务数的上限。

initrans 参数还授予每个块的最小并发设置。如果 initrans 的值大于同一块内同时活动的事务的 maxtrans 可用的值,则 ITL 列表将进一步扩展。但是,数据库块中必须有可用空间才能扩展 ITL 列表



Initrans 指定最初在数据块头中保留空间的 DML 事务条目的数量。在相关段中的所有数据块的标头中保留空间。


如果一个块是稀疏填充的,那么 Oracle 将动态地将 ITL 的数量增加到 MAXTRANS。如果一个块可用空间不足,则事务将序列化,等待可用的 ITL。这是数据库块等待的原因之一。通过将 INITRANS 设置为单个块的预期同时 DML(数据操作语言 - 插入、更新和删除)事务的数量,您可以避免 ITL 插槽的序列化。


由于多个事务同时访问同一数据块的行,因此为块中每个 DML 事务的条目分配空间。一旦INITRANS用完保留的空间,如果可用,则从块中的可用空间中分配用于附加事务条目的空间。一旦分配,这个空间就有效地成为块头的永久部分。该MAXTRANS参数限制了一个数据块中可以同时使用数据的事务条目的数量


换成人话,就是一行DML发起一个事务,这个事务信息(回滚序号,事务ID,时间戳等信息)需要在块的事务槽上记录,默认情况事务槽可以动态扩展满足多个会话修改同一个块。但是这个动态扩展一点要有可用空间,没有可用空间,就不要扩展,就会产生等待,事务就会挂起,直到有会话commit,释放事务槽,新来的虽然不能扩展,但是可以重复使用已被释放的事务槽。


按前面的kill会话的方式也能解决,不够根据原理分析,增加事务槽初始值就行了,预先分配好,避免扩展的时候出现问题。就是这个东西

 pctfree 20
 initrans 10
 maxtrans 255


因这里只给出修改代码(一般是创建表设置initrans大一点,修改只对新增的块有效。同时如果表更新比较频繁,建议增加pctfree,增加预留空间,MAXTRANS不要动,系统默认255)

Alter table mrp.mrp_plan initrans 20 pctfree 30;--增加初始事务槽为20,同时增加更新的预留空间30%

Alter index xxxxx initrans 20 pctfree 30 ;-修改索引初始化参数