问题原因

应用反应堵塞，检查数据库等待事件出现’enq: TX - row lock contention’
 业务更新或者删除同一行记录
 对创建位图索引的列值更新
 对主键或唯一键插入相同记录

解决方案

在enq: TX - row lock contention发生的实例上执行查询：

set linesize 180
set pagesize 999
column username format a16
column event format a40
column sql_child_number format 9999 heading 'SQL Child|Number'
column blocking_instance format 9999 heading 'Blocker|Instance'
column blocking_session format 99999 heading 'Blocker|Session'
column FINAL_BLOCKING_INSTANCE format 9999 heading 'F_Blocker|Instance'
column FINAL_BLOCKING_SESSION format 99999 heading 'F_Blocker|Session'
column "Lock" format a6
column "Mode" format a6
Select sid,
       seq#,
       username,
       event,
       sql_id,
       sql_child_number,
       chr(to_char(bitand(p1, -16777216)) / 16777215) ||
       chr(to_char(bitand(p1, 16711680)) / 65535) "Lock",
       to_char(bitand(p1, 65535)) "Mode",
       blocking_instance,
       blocking_session,
       final_blocking_instance,
       final_blocking_session,
       last_call_et
  From v$session
 Where event = 'enq: TX - row lock contention';

关注blocking_instance/blocking_session和final_blocking_instance/final_blocking_session(适用于11g)

1） 如果final_blocking_instance/final_blocking_session没有变化，说明可能是由于某个进程没有提交事务导致的enq: TX = row lock contention
到holder所在的实例执行以下查询：

set lines 180 pages 999
column username format a15
column program format a35
column machine format a20
column event format a30
column sql_id format a13
column status format a10
Select s.sid,
       s.username,
       s.event,
       s.program,
       s.machine,
       s.sql_id,
       s.sql_child_number,
       s.status,
       s.last_call_et
  From v$session s
 Where s.sid in (&sid);

确认不是DB进程，对堵塞者生成kill语句：

set lines 200 pages 999
col username for a15
col machine for a20
col program for a35
col spid for 99999999999
col dbkill for a40
col oskill for a20
col service_name for a20
select a.username,
       a.program,
       b.addr,
       b.spid,
       'alter system kill session ''' || a.sid || ',' || a.serial# || ''';' "dbkill",
       'kill -9 ' || b.spid "oskill"
  from v$session a, v$process b
 where a.paddr = b.addr(+)
   and a.sid = &sid
--   and a.serial#=&serial
 order by a.username, a.program;

执行生成的dbkill命令后继续观察session状态，如果长时间没有消失，在OS级别执行生成的oskill命令(如果是DB 进程，不可随意kill，否则会导致系统crash)：

ps –ef|grep &spid
kill -9 &spid

2） 如果blocking_instance/blocking_session和final_blocking_instance/final_blocking_session均在不停变化，说明可能是由于业务SQL执行慢或者是并发高于正常值导致。
确认应用模块，执行如下查询：

column username format a15
column program format a35
column machine format a20
column event format a30
column sql_id format a13
column status format a10
Select s.sid,
       s.username,
       s.event,
       s.program,
       s.machine,
       s.sql_id,
       s.sql_child_number,
       s.status,
       s.last_call_et
  From v$session s
 Where event = 'enq: TX - row lock contention'
 Order By event, sql_id;

查看SQL执行计划，进一步分析是否是SQL性能问题，如下查询生成查看SQL执行计划的语句：

set lines 160 pages 999
set long 99999
col PLAN_TABLE_OUTPUT for a160 
select sql_fulltext,
       parsing_schema_name,
       'select * from table(dbms_xplan.display_cursor(''' || sql_id ||
       ''',' || child_number || ',''all iostats last +PEEKED_BINDS''));' xplan
  from v$sql
 where sql_id = '&sql_id';

包括SQL本身性能问题或者并发突发性增长，这类情况下kill阻塞者并不能解决问题，通知应用处理，允许的条件下通过临时kill所有等待的会话释放：

col username for a15
col machine for a20
col program for a35
col spid for 99999999999
col dbkill for a40
col oskill for a20
Select a.username,
       a.program,
       a.machine,
       b.addr,
       b.spid,
       'alter system kill session ''' || a.sid || ',' || a.serial# || ''';' "dbkill",
       'kill -9 ' || b.spid "oskill"
  From v$session a, v$process b
 Where a.paddr = b.addr(+)
   And a.event = 'enq: TX - row lock contention'
   And a.type = ‘USER’
 Order By a.username, a.program;

【后续分析】
如果是固定阻塞者，根据blocker session信息，联系相关应用确认该事务未正常提交的原因，避免下次问题发生。
如果是SQL性能变差导致执行慢进而产生等待，进一步分析处理SQL性能问题。
如果SQL执行效率没有问题，那么有可能是并发异常导致的等待，根据应用模块及执行SQL的信息，联系相关应用及时处理并发过高的异常.


查询enq: TX - row lock contention 锁到哪条数据

查询enq: TX - row lock contention 锁到哪条数据

方案:

分解步骤如下

select dbms_rowid.rowid_create(1,data_object_id, current_file#, current_block#,current_row#) from dba_objects o , v$active_session_history h
where h.event = 'enq: TX - row lock contention' and h.SAMPLE_TIME>=to_date('20230625080000','yyyymmddhh24miss')
and o.object_id = h.current_obj#

--输出结果是对应表的ROWID
AADI1mACyAAIqCXAAv

SELECT SQL_ID
  FROM v$active_session_history
WHERE     EVENT = 'enq: TX - row lock contention'
       AND SAMPLE_TIME >= TO_DATE ('20230625080000', 'yyyymmddhh24miss');

---查出SQL_ID是0txjzk4x81wrx

select sql_text from v$sql where sql_id='0txjzk4x81wrx';   

--查出等待事件对应的表是owner.table1

select * from owner.table1 where rowid='AADI1mACyAAIqCXAAv'; 

--将刚刚的rowid带到表里查出具体row lock锁的哪行