11.enq: TX - row lock contention 模拟

1.当锁正在发生时，通过gv$session查看阻塞链：

SQL> select  * from test01 where rownum< 10;
	ID NAME 				 AGE
---------- ------------------------------ ----------
       345 345xsq345				  19
       346 346xsq346				  19
       347 347xsq347				  19
       348 348xsq348				  19
       349 349xsq349				  19
       350 350xsq350				  19
       351 351xsq351				  19
       352 352xsq352				  19
       353 353xsq353				  19

--模拟行锁争用

--会话1
SQL> set autocommit off;
SQL> update test01 set name='345xsq345-19' where id=345;

--会话2;被锁定。
SQL>  set autocommit off;
SQL>  update test01 set name='345xsq345-1901' where  id=345; 

--会话3:被锁定。
SQL>  update test01 set name='345xsq345-1902'  where  id=345;

2.过一段时间后 

--会话1:
commit; 
--会话2:
commit; 
--会话3:
commit; 

--3.查询引发锁的源头的会话ID;

--会话情况每秒收集一次,所以会话历史我们一定能看到。
col tree for a40
col event for a30 
set lin 200
select *
  from (select a.inst_id, a.sid, a.serial#,
               a.sql_id,
               a.event,
               a.status,
               connect_by_isleaf as isleaf,
               sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
               level as tree_level
          from gv$session a
         start with a.blocking_session is not null
        connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
 where isleaf = 1
 order by tree_level asc;
 
   INST_ID	  SID	 SERIAL#  SQL_ID  EVENT			               STATUS	   ISLEAF TREE					   TREE_LEVEL
---------- ---------- ------- -----  ------------------------------ -------- ---------- ------------------    ------------
	 1	   125	       7	         SQL*Net message from client    INACTIVE		1  <- 19@1 <- 125@1			2
	 1	   125	       7	         SQL*Net message from client    INACTIVE		1  <- 141@1 <- 125@1		2

--可以看出125,阻塞了19号会话和141号会话。

4.如果锁已经不存在，需要通过ASH视图/ASH备份表来查询阻塞链：

--行锁争用问题查询
set lin 200
col LOCK_CHAIN for a40
col EVENT_CHAIN for a50
col first_seen for a18
col last_seen for a18
col BLOCKING_HEADER for a10
with ash as (
select *
  from gv$active_session_history
 where sample_time>=to_date('2024-01-25 13:00:00','yyyy-mm-dd hh24:mi:ss')
   and sample_time< to_date('2024-01-25 13:50:00','yyyy-mm-dd hh24:mi:ss')),
ash2 as (
select sample_time,inst_id,session_id,session_serial#,sql_id,sql_opname,
       event,blocking_inst_id,blocking_session,blocking_session_serial#,
       level lv,
       connect_by_isleaf isleaf,
   sys_connect_by_path(inst_id||'_'||session_id||'_'||session_serial#||':'||sql_id||':'||sql_opname,'->') lock_chain,
       sys_connect_by_path(EVENT,',') EVENT_CHAIN ,
       connect_by_root(inst_id||'_'||session_id||'_'||session_serial#) root_sess
  from ash
  start with event like 'enq: TX - row lock contention%'
 connect by nocycle 
        prior blocking_inst_id=inst_id
    and prior blocking_session=session_id
    and prior blocking_session_serial#=session_serial#
    and prior sample_id=sample_id)
select lock_chain lock_chain,EVENT_CHAIN,
       case when blocking_session is not null then blocking_inst_id||'_'||blocking_session||'_'||blocking_session_serial# else inst_id||'_'||session_id||'_'||session_serial# end blocking_header,
       count(*) cnt,
       TO_CHAR(min(sample_time),'YYYYMMDD HH24:MI:ss') first_seen,
       TO_CHAR(max(sample_time),'YYYYMMDD HH24:MI:ss') last_seen
   from ash2
  where isleaf=1
group by lock_chain,EVENT_CHAIN,case when blocking_session is not null then blocking_inst_id||'_'||blocking_session||'_'||blocking_session_serial# else inst_id||'_'||session_id||'_'||session_serial# end
having count(*)>1
order by first_seen, cnt desc;


--查看历史的行锁等待。
--被等待的SQL_ID;
LOCK_CHAIN				               EVENT_CHAIN					    BLOCKING_H	      CNT FIRST_SEEN	     LAST_SEEN
---------------------------------------- -------------------------------------------------- ---------- ---------- ------------------ ------------------
->1_19_33:1rv24cd4q69h3:UPDATE		 ,enq: TX - row lock contention 		    1_125_7	     2038 20240125 13:16:01  20240125 13:49:59
->1_141_93:gbjxtzx259j8j:UPDATE 	 ,enq: TX - row lock contention 		    1_125_7	     1936 20240125 13:17:43  20240125 13:49:59



--如下两个SQL刚好是被阻塞的SQL。
SQL> select SQL_ID,SQL_FULLTEXT from v$sqlarea where SQL_ID IN ('1rv24cd4q69h3','gbjxtzx259j8j');

SQL_ID	      SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
gbjxtzx259j8j  update test01 set name='345xsq345-1902'	where  id=345
1rv24cd4q69h3  update test01 set name='345xsq345-1901' where  id=345

--引起阻塞的SQLID=082huz7qyacv3
select SQL_ID,SQL_FULLTEXT from v$sqlarea where SQL_FULLTEXT LIKE 'update test01 set name=%';
SQL_ID	      SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
082huz7qyacv3 update test01 set name='345xsq345-19' where id=345

--它是引起阻塞的，没有人阻塞它。
select SESSION_ID,SQL_ID,BLOCKING_SESSION from gv$active_session_history WHERE SQL_ID='082huz7qyacv3';
no rows selected
SQL> 

SQL> select SESSION_ID,SQL_ID,EVENT,sample_time,BLOCKING_SESSION from gv$active_session_history 
WHERE EVENT='enq: TX - row lock contention'
and BLOCKING_SESSION<>125; 

SESSION_ID SQL_ID	 EVENT								  SAMPLE_TIME			 BLOCKING_SESSION
---------- ------------- ---------------------------------------------------------------- ------------------------------ ----------------
       141 gbjxtzx259j8j enq: TX - row lock contention					  25-JAN-24 01.53.49.683 PM		       19
       141 gbjxtzx259j8j enq: TX - row lock contention					  25-JAN-24 01.53.48.683 PM		       19
       141 gbjxtzx259j8j enq: TX - row lock contention					  25-JAN-24 01.53.47.683 PM		       19

--SID=125 会话阻塞:SID=19的会话。
--SID=19 的会话阻塞:SID=141的会话。
--我们知道引起阻塞的会话是:125,但是不清楚引起阻塞的会话具体执行的是哪个SQL;

5.总结

gv$active_session_history 会话历史表中记录了发送等待的SESSION_ID,SQL_ID引起阻塞的SESSION_ID.但是没有记录引起阻塞的SQL的SQL_ID;

如上语句能够直接找到出现行锁争用的SQLID,从而找到相关SQL语句。非常好用。