测试“奇怪的select” 造成的行锁等待测试----诡异的行为的解答离不开对原理的理解和坚持

2021-04-10 00:00:00 语句 执行 事务 会话 阻塞

测试奇怪的select” 造成的行锁等待



个会话

SQL> select userenv('sid') from dual;


USERENV('SID')

--------------

            70


delete from cat where id=101   此时事务不结束


第二个会话

SQL> select * from cat;


        ID NAME

---------- ----------------------------------------

       101 cat1

       102 cat2

       103 cat3


SQL> select userenv('sid') from dual;


USERENV('SID')

--------------

            58


SQL> update cat set name='testtest' where id=101;

 这个语句被阻塞了


验证阻塞关系

SQL> select session_id,sql_id,event,blocking_session,count(*) from v$active_session_history where blocking_session is not null and event like '%TX%' group by session_id,sql_id,event,blocking_session

  2  ;


SESSION_ID SQL_ID                     EVENT                                              BLOCKING_SESSION   COUNT(*)

---------- -------------------------- -------------------------------------------------- ---------------- ----------

        58 06ppp344arcg7              enq: TX - row lock contention                                    70        575

分析:70号会话阻塞了58号会话,58号会话正在执行SQL06ppp344arcg7


SQL> l

  1* select * from table(dbms_xplan.display_cursor('06ppp344arcg7'))

SQL> /


PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID  06ppp344arcg7, child number 0

-------------------------------------

update cat set name='testtest' where id=101


Plan hash value: 165090673


-----------------------------------------------------------------------------------

| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT   |              |       |       |     1 (100)|          |

|   1 |  UPDATE            | CAT          |       |       |            |          |

|*  2 |   INDEX UNIQUE SCAN| SYS_C0010408 |     1 |    25 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   2 - access("ID"=101)



19 rows selected.


被阻塞的会话正在执行update语句,而造成阻塞的会话显然是70好会话的delete语句,该语句事务没有结束,我们肯能从v$open_cursor抓出该语句,但是我们暂时不考虑这个问题,

感兴趣可以参考我之前的测试,下面我们在70号会话执行一个select * from all_objects;如果该SQL没有结束,我们从造成阻塞的70号会话会抓出该SQL,结合阻塞关系,我们会突然意识到

70号会话正在执行的select语句,竟然阻塞了update语句,并且二者的表根本没有关系,这是个十分容易迷糊人的地方。


SQL> select session_id,sql_id,event,blocking_session,count(*) from v$active_session_history where blocking_session is not null and event like '%TX%' group by session_id,sql_id,event,blocking_session;


SESSION_ID SQL_ID                     EVENT                                              BLOCKING_SESSION   COUNT(*)

---------- -------------------------- -------------------------------------------------- ---------------- ----------

        58 06ppp344arcg7              enq: TX - row lock contention                                    70       1068


SQL> select sql_id from v$session where sid=70;


SQL_ID

--------------------------

25u1mbkcr9rnu


SQL> select * from table(dbms_xplan.display_cursor('25u1mbkcr9rnu',null,'allstats last'));


PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID  25u1mbkcr9rnu, child number 0

-------------------------------------

select * from all_objects


Plan hash value: 2378945577


上面我们从70号会话找出正在执行的SQL :select * from all_objects,而该语句无论如何也无法说服我们它会造成行锁等待,当然从原理上说,肯定是其他DML操作这里是Delete操作导致的。

下面我们从事务角度查询,因为该阻塞意味着造成阻塞的会话的事务没有结束。


SQL> l

  1* select s.sid,s.status,program,(select substr(sql_text,1,80) from v$open_cursor where sql_id=s.sql_id and s.status='ACTIVE') sql_text,t.start_time,t.used_ublk from gv$session s,gv$transaction t  where s.inst_id=t.inst_id and s.taddr=t.addr and s.sid=70

SQL> /


       SID STATUS           PROGRAM                                  SQL_TEXT                       START_TIME            USED_UBLK

---------- ---------------- ---------------------------------------- ------------------------------ -------------------- ----------

        70 INACTIVE         sqlplus@test1 (TNS V1-V3)                                               04/09/21 23:50:08             1



确实这里有start_time

下面视图查询会话70,事务没有结束的SQL,它会依然阻塞会话58update操作。

SQL> select s.sid,s.program,s.event,TO_CHAR(S.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') as sess_LOGON_TIME,TO_CHAR(T.START_DATE,'YYYY-MM-DD HH24:MI:SS') as trans_start_date,s.blocking_session,s.status,(select q.sql_text from v$sql q where q.last_active_time=t.start_date and rownum<=1) as sql_text from v$session s,v$transaction t where s.saddr=t.ses_addr;


       SID PROGRAM                                  EVENT                                              SESS_LOGON_TIME                        TRANS_START_DATE           BLOCKING_SESSION

---------- ---------------------------------------- -------------------------------------------------- -------------------------------------- -------------------------------------- ----------------

STATUS           SQL_TEXT

---------------- ------------------------------

        70 sqlplus@test1 (TNS V1-V3)                SQL*Net message from client                        2021-04-09 23:46:12                    2021-04-09 23:50:08

INACTIVE


不幸我们没有找到这个事务没有结束的SQL


这里我们做个总结,核心问题是造成阻塞的会话执行完delete语句后,没有提交(具体为什么需要跟业务沟通了),后续select语句执行时间比较长,如果终用户在执行完select语句后,70号会话的事务提交。

会话70

SQL> commit;


Commit complete.

会话58,阻塞消除

SQL> update cat set name='testtest' where id=101;


0 rows updated.


继续插锁等待


SQL> l

  1* select session_id,sql_id,event,blocking_session,count(*) from v$active_session_history where blocking_session is not null and event like '%TX%' group by session_id,sql_id,event,blocking_session

SQL> /


SESSION_ID SQL_ID                     EVENT                                              BLOCKING_SESSION   COUNT(*)

---------- -------------------------- -------------------------------------------------- ---------------- ----------

        58 06ppp344arcg7              enq: TX - row lock contention                                    70       1991


SQL> /


SESSION_ID SQL_ID                     EVENT                                              BLOCKING_SESSION   COUNT(*)

---------- -------------------------- -------------------------------------------------- ---------------- ----------

        58 06ppp344arcg7              enq: TX - row lock contention                                    70       1991


SQL> /


SESSION_ID SQL_ID                     EVENT                                              BLOCKING_SESSION   COUNT(*)

---------- -------------------------- -------------------------------------------------- ---------------- ----------

        58 06ppp344arcg7              enq: TX - row lock contention                                    70       1991


从等待时间看,已经不再增加,当然也可以通过v$session


SQL> select sid,event from v$session where sid=58;


       SID EVENT

---------- --------------------------------------------------

        58 SQL*Net message from client


此时由于70号会话的事务提交,58号会话的update得以继续执行。


从这里看出出于各种原因,只要70号会话的事务执行时间增长,并且有类似并发的操作,那么update语句的行锁等待就会被放大。


解决方法:70号会话中的delete及时提交,或者从业务逻辑考虑是否去掉该SQL,此时迷惑我们的select语句造成行锁等待就水落石出了,当然找打问题解决方案也就应用而生。


相关文章