update 主键造成行锁等待测试
行锁是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操作
相关文章