update 主键造成行锁等待测试

2021-04-09 18:40:08

行锁是Oracle实现事务的一种机制，在数据库操作中无法避免，但是长时间的等待就需要分析，问题原因，是阻塞会话执行时间长，还是外键约束问题或者是其他问题，这个需要具体问题具体分析，但是这个机制原理是不变的，同时只能有一个用户做DML操作，这里我们测试update主键的SQL如何被阻塞，这样的操作对于高并发操作往往带来严重的性能问题。下面是整个测试过程。

insert 阻塞update测试

SQL> create table cat (id number primary key,name varchar2(20));

Table created.

SQL> select * from cat;

ID NAME
---------- ----------------------------------------
101 test101
102 test102
103 test103

会话1 执行插入
SQL> insert into cat values(104,'test104');

1 row created.

会话2执行update
SQL> update cat set id=104 ,name='test4' where id=101;

会话2被阻塞

SQL> select sql_id,event,count(*) from v$active_session_history where event like 'enq: TX%' group by sql_id,event order by count(*)

SQL_ID EVENT COUNT(*)
-------------------------- ---------------------------------------- ----------
7rfyaswu94q7j enq: TX - row lock contention 260

SQL> select sql_id,event,count(*) from v$active_session_history where event like 'enq: TX%' group by sql_id,event order by count(*);

SQL_ID EVENT COUNT(*)
-------------------------- ---------------------------------------- ----------
7rfyaswu94q7j enq: TX - row lock contention 129

查询被阻塞的SQL
SQL> select * from table(dbms_xplan.display_cursor('7rfyaswu94q7j'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7rfyaswu94q7j, child number 0
-------------------------------------
update cat set id=:"SYS_B_0" ,name=:"SYS_B_1" where id=:"SYS_B_2"

Plan hash value: 3342732520

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| |
| 1 | UPDATE | CAT | | | | |
|* 2 | INDEX UNIQUE SCAN| SYS_C0019893 | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=:SYS_B_2)

19 rows selected.

种是 insert阻塞update

第二种是update阻塞update

会话1：
SQL> insert into cat values(104,'test104');

1 row created.

会话2：

SQL> insert into cat values(104,'test103');

查询阻塞关系
SQL> select sql_id,event,blocking_session,count(*) from v$active_session_history where event like 'enq: TX%' group by sql_id,event ,blocking_session order by count(*);

SQL_ID EVENT BLOCKING_SESSION COUNT(*)
-------------------------- ---------------------------------------- ---------------- ----------
8j8smnu59cqs3 enq: TX - row lock contention 50 198
7rfyaswu94q7j enq: TX - row lock contention 50 394

查询被阻塞的SQL

SQL> select * from table(dbms_xplan.display_cursor('8j8smnu59cqs3'));

SQL> select * from table(dbms_xplan.display_cursor('8j8smnu59cqs3'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8j8smnu59cqs3, child number 0
-------------------------------------
insert into cat values(:"SYS_B_0",:"SYS_B_1")

-------------------------------------------------
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | CAT | |
-------------------------------------------------

Note
-----
- cpu costing is off (consider enabling it)

16 rows selected.

这里update阻塞update

下面测试Delete 阻塞insert
会话1
SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
49

SQL> delete from cat where id=103;

1 row deleted.

会话2
SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
809

SQL> insert into cat values(103,'test3');

查阻塞关系
SQL> select session_id,sql_id,event,blocking_session,count(*) from v$active_session_history where event like '%TX%' group by session_id,sql_id,event,blocking_session order by count(*)

SESSION_ID SQL_ID EVENT BLOCKING_SESSION COUNT(*)
---------- -------------------------- ---------------------------------------- ---------------- ----------
809 8j8smnu59cqs3 enq: TX - row lock contention 49 415

测试delete 阻塞update
会话1事务不动

会话2
取消insert操作
SQL> insert into cat values(103,'test3');
^Cinsert into cat values(103,'test3')
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

开启一个update操作
SQL> update cat set name='test' where id=103;

继续查询阻塞关系

SQL> l
1* select session_id,sql_id,event,blocking_session,count(*) from v$active_session_history where event like '%TX%' group by session_id,sql_id,event,blocking_session order by count(*)
SQL> /

SESSION_ID SQL_ID EVENT BLOCKING_SESSION COUNT(*)
---------- -------------------------- ---------------------------------------- ---------------- ----------
809 3hwubna4qnaam enq: TX - row lock contention 49 138
809 8j8smnu59cqs3 enq: TX - row lock contention 49 455

809会话的SQL：3hwubna4qnaam被49号会话阻塞
下面是809号会话的SQL
SQL> select * from table(dbms_xplan.display_cursor('3hwubna4qnaam'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3hwubna4qnaam, child number 0
-------------------------------------
update cat set name=:"SYS_B_0" where id=:"SYS_B_1"

Plan hash value: 3342732520

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)|
| 1 | UPDATE | CAT | | | |
|* 2 | INDEX UNIQUE SCAN| SYS_C0019893 | 1 | 12 | 0 (0)|
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=:SYS_B_1)

19 rows selected.

下面查询49号会话的SQL
事务没有结束
SQL>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=49

SID STATUS PROGRAM SQL_TEXT START_TIME USED_UBLK
---------- ---------------- ---------------------------------- -------------------- ---------------------------------------- ----------
49 INACTIVE sqlplus@rac1 (TNS V1-V3) 04/09/21 17:45:46 1

QL> select s.sid,s.program,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.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 SESS_LOGON_TIME TRANS_START_DATE STATUS SQL_TEXT
---------- ---------------------------------- -------------------- -------------------- -------------------- --------------------
49 sqlplus@rac1 (TNS V1-V3) 2021-04-09 17:44:22 2021-04-09 17:45:46 INACTIVE delete from cat wher
e id=:"SYS_B_0"

发现49号会话没有结束事务的SQL为：delete from cat where id=:"SYS_B_0"
说明该delete操作阻塞了update操作