如何进行等待事件enq TX row lock contention的分析

66次阅读
没有评论

共计 10702 个字符,预计需要花费 27 分钟才能阅读完成。

本篇文章为大家展示了如何进行等待事件 enq TX row lock contention 的分析,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。

通常情况下,Oracle 数据库的等待事件 enq: TX – row lock contention 会在下列三种情况下会出现。
第一种情况,是真正的业务逻辑上的行锁冲突,如一条记录被多个人同时修改。这种锁对应的请求模式是 6。
第二种情况,是唯一键冲突,如主键字段相同的多条记录同时插入。这种锁对应的请求模式是 4。这也是应用逻辑问题。
第三种情况,是 bitmap 索引的更新冲突,就是多个会话同时更新 bitmap 索引的同一个数据块。此时会话请求锁的对应的请求模式是 4。

bitmap 索引的物理结构和普通索引一样,也是 B-tree 结构。但它存储的数据记录的逻辑结构为 key_value,start_rowid,end_rowid,bitmap。
其内容类似这样:
‘8088’,00000000000,10000034441,1001000100001111000
Bitmap 是一个二进制,表示 START_ROWID 到 END_ROWID 的记录,1 表示等于 key_value 即‘8088’的 ROWID 记录,0 则表示不是这个记录。
在了解 bitmap 索引的结构之后,我们就能理解同时插入多条记录到拥有 bitmap 索引的表时,就会同时更新 bitmap 索引中一个块中的记录,等于某一个记录被同时更新,自然就会出现行锁等待。插入并发量越大,等待越严重。

等待事件 enq: TX – row lock contention 中的 enq 是 enquence 的简写。enquence 是协调访问数据库资源的内部锁。
所有以“enq:”打头的等待事件都表示这个会话正在等待另一个会话持有的内部锁释放,它的名称格式是 enq:enqueue_type – related_details。这里的 enqueue_type 是 TX,related_details 是 row lock contention。数据库动态性能视图 v$event_name 提供所有以“enq:”开头的等待事件的列表。
虽然在 awrrpt 中看到大量 enq: TX – row lock contention 的等待,但这些是事后看到的信息。根据 AWRRPT,我们无法只能该等待事件的请求模式是什么,是 6 还是 4。
如果数据库一出现 enq: TX – row lock contention 等待,可以去看 v$session 和 v$session_wait 等视图。

在 v$session 和 v$session_wait 中,如果看到的 event 列是 enq: TX – row lock contention 的,就表示这个会话正处于行锁等待。该等待事件的请求模式可以从 v$session 和 v$session_wait 的 p1 列中得到。
select sid,
  chr(bitand(p1, -16777216) / 16777215) ||
  chr(bitand(p1, 16711680) / 65535) Name ,
  (bitand(p1, 65535)) Mode
  from v$session_wait
 where event like enq%
通过这个 SQL 可以将 p1 转换为易阅读的文字。

针对这三种情况,分别进行测试:

首先,我准备一下测试表和数据。
– 创建测试表和数据
SQL create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;

Table created.

SQL alter table T_ALL_OBJS  add constraint pk_t_all_objs primary key (OBJECT_ID);

Table altered.

SQL insert into t_all_objs(owner,object_id,object_name) values(TEST ,2013011701, test1

1 row created.

SQL insert into t_all_objs(owner,object_id,object_name) values(TEST ,2013011702, test2

1 row created.

SQL commit;

Commit complete.

第一种情况,不同会话同时更新同一条记录
session1:
SQL select sid from v$mystat where rownum

  SID
———-
  46

SQL select * from t_all_objs;   

OWNER  OBJECT_ID OBJECT_NAME
—————————— ———- ——————————
TEST  2013011701 test1
TEST  2013011702 test2

SQL update t_all_objs set object_name= test11 where object_id=2013011701;

1 row updated.

未 commit

session 2:
SQL select sid from v$mystat where rownum

  SID
———-
  52

SQL update t_all_objs set object_name= test101 where object_id=2013011701;
 
一直等待。。。。。。。。。   

session 3:查询
SQL select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (46, 52);

  SID SQL_ID  BLOCKING_SESSION EVENT  P1TEXT  P1 P2TEXT  P2 P3TEXT  P3 WAIT_CLASS  STATE
—– ————- —————- —————————— ———- ———- ————— ———- ———- ———- ————— ———-
  46  SQL*Net message from client  driver id  1650815232 #bytes  1  0 Idle  WAITING
  52 c53uad8st2u8t  46 enq: TX – row lock contention  name|mode  1415053318 usn 16 | slot  65556 sequence  13548 Application  WAITING

– 得到 sql_id 值,查询出 SQL 信息
SQL select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = c53uad8st2u8t

SQL_TEXT  EXECUTIONS S.ELAPSED_TIME/1000000
———————————————————————- ———- ———————-
update t_all_objs set object_name= test101 where object_id=2013011701  0  501.178747

– 再查询锁信息:
SQL select * from v$lock where sid in (46,52) order by sid, type;

ADDR  KADDR  SID TY  ID1  ID2  LMODE  REQUEST  CTIME  BLOCK
—————- —————- —– — ———- ———- ———- ———- ———- ———-
000000009398FE58 000000009398FEB0  46 AE  100  0  4  0  3706  0
00007FABEA622FC0 00007FABEA623020  46 TM  87875  0  3  0  597  0
0000000091E37248 0000000091E372C0  46 TX  65556  13548  6  0  597  1
000000009398F820 000000009398F878  52 AE  100  0  4  0  573  0
00007FABEA622FC0 00007FABEA623020  52 TM  87875  0  3  0  543  0
000000009398FBB8 000000009398FC10  52 TX  65556  13548  0  6  543  0

6 rows selected.

查询得到未 46 的 session 最终阻塞了会话,是根源。

第二种情况,不同会话中同时插入主键字段相同的记录
session 1;
SQL select sid from v$mystat where rownum=1;

  SID
———-
  43

SQL select * from t_all_objs;

OWNER  OBJECT_ID OBJECT_NAME
—————————— ———- ——————————
TEST  2013011701 test1
TEST  2013011702 test2

SQL insert into t_all_objs(owner,object_id,object_name) values(TEST ,2013011703, test1

1 row created.
未 commit

session 2:
SQL select sid from v$mystat where rownum=1;

  SID
———-
  55

SQL   insert into t_all_objs(owner,object_id,object_name) values(TEST ,2013011703, test11

一直等待。。。。。。。

session 3:查询    
SQL select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (43, 55);

  SID SQL_ID  BLOCKING_SESSION EVENT  P1TEXT  P1 P2TEXT  P2 P3TEXT  P3 WAIT_CLASS  STATE
—– ————- —————- —————————— ———- ———- ————— ———- ———- ———- ————— ———-
  43  SQL*Net message from client  driver id  1650815232 #bytes  1  0 Idle  WAITING
  55 bsddu35jkskbz  43 enq: TX – row lock contention  name|mode  1415053316 usn 16 | slot  262149 sequence  13576 Application  WAITING

SQL select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = bsddu35jkskbz

SQL_TEXT  EXECUTIONS S.ELAPSED_TIME/1000000
—————————————————————————————————- ———- ———————-
 insert into t_all_objs(owner,object_id,object_name) values(TEST ,2013011703, test11)  0  213.079739

SQL select * from v$lock where sid in (43, 55) order by sid, type;

ADDR  KADDR  SID TY  ID1  ID2  LMODE  REQUEST  CTIME  BLOCK
—————- —————- —– — ———- ———- ———- ———- ———- ———-
000000009398F660 000000009398F6B8  43 AE  100  0  4  0  376  0
00007FABEA621F88 00007FABEA621FE8  43 TM  87875  0  3  0  266  0
0000000091E292E0 0000000091E29358  43 TX  262149  13576  6  0  266  1
000000009398F040 000000009398F098  55 AE  100  0  4  0  371  0
00007FABEA621F88 00007FABEA621FE8  55 TM  87875  0  3  0  256  0
0000000091DDB308 0000000091DDB380  55 TX  327688  13773  6  0  256  0
000000009398F900 000000009398F958  55 TX  262149  13576  0  4  256  0

7 rows selected.

最后查询得是会话 43 阻塞了别的会话,是根源。

第三种情况,不同会话中同时 bitmap 索引列值相同的记录
session 1:
SQL select sid from v$mystat where rownum=1;

  SID
———-
  44

SQL select * from T_ALL_OBJS ;

OWNER  OBJECT_ID OBJECT_NAME
—————————— ———- ——————————
TEST  2013011701 test1
TEST  2013011702 test2

SQL   create bitmap index ind_T_ALL_OBJS on T_ALL_OBJS (owner);

Index created.

SQL insert into t_all_objs(owner,object_id,object_name) values(TEST ,2013011703, test11

1 row created.

未 commit

session 2:
SQL select sid from v$mystat where rownum=1;

  SID
———-
  40

SQL insert into t_all_objs(owner,object_id,object_name) values(TEST ,2013011703, test12

一直等待。。。。。。。。。。

session 3:查询
SQL select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (44, 40);

  SID SQL_ID  BLOCKING_SESSION EVENT  P1TEXT  P1 P2TEXT  P2 P3TEXT  P3 WAIT_CLASS  STATE
—– ————- —————- —————————— ———- ———- ————— ———- ———- ———- ————— ———-
  40 8s2tzhjpgx1nc  44 enq: TX – row lock contention  name|mode  1415053316 usn 16 | slot  655390 sequence  13564 Application  WAITING
  44  SQL*Net message from client  driver id  1650815232 #bytes  1  0 Idle  WAITING

SQL select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 8s2tzhjpgx1nc

SQL_TEXT  EXECUTIONS S.ELAPSED_TIME/1000000
—————————————————————————————————- ———- ———————-
insert into t_all_objs(owner,object_id,object_name) values(TEST ,2013011703, test12)  0  96.034408

SQL select * from v$lock where sid in (44, 40) order by sid, type;

ADDR  KADDR  SID TY  ID1  ID2  LMODE  REQUEST  CTIME  BLOCK
—————- —————- —– — ———- ———- ———- ———- ———- ———-
000000009398E688 000000009398E6E0  40 AE  100  0  4  0  415  0
00007FABEA622FC0 00007FABEA623020  40 TM  87875  0  3  0  110  0
000000009398FBB8 000000009398FC10  40 TX  655390  13564  0  4  110  0
0000000091E54F48 0000000091E54FC0  40 TX  589844  13794  6  0  110  0
000000009398F3C0 000000009398F418  44 AE  100  0  4  0  410  0
00007FABEA622FC0 00007FABEA623020  44 TM  87875  0  3  0  126  0
0000000091E18128 0000000091E181A0  44 TX  655390  13564  6  0  126  1

7 rows selected.

最终查询得会话 44 是阻塞根源。

模拟故障:
session 1:
SQL select sid from v$mystat where rownum

  SID
———-
  46

SQL select * from t_all_objs;

OWNER  OBJECT_ID OBJECT_NAME
—————————— ———- ——————————
TEST  2013011701 test1
TEST  2013011702 test2

– 插入数据,不提交
SQL update t_all_objs set object_name= test11 where object_id=2013011701;

1 row updated.

session 2:
SQL select sid from v$mystat where rownum

  SID
———-
  52

SQL select * from t_all_objs;

OWNER  OBJECT_ID OBJECT_NAME
—————————— ———- ——————————
TEST  2013011701 test1
TEST  2013011702 test2

– 对同一行数据进行更新,不提交
SQL update t_all_objs set object_name= test101 where object_id=2013011701;

等待 ing…………………

session 3:
– 查询 tx 锁会话 sid,row_wait_object# 信息
select sid,sql_id,status,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event= enq: TX – row lock contention

  SID SQL_ID  STATUS  BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
—– ————- ——– —————- ————- ————– ————— ————-
  52 c53uad8st2u8t ACTIVE  46  87875  1  143649  0

– 查询被锁的对象信息:
select object_name from dba_objects where object_id in (87875);

OBJECT_NAME
——————————————————————————————————————————–
T_ALL_OBJS

select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID, OBJECT_TYPE from all_objects where object_name= T_ALL_OBJS

OWNER  OBJECT_NAME  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
—————————— —————————— ———- ————– ——————-
SYS  T_ALL_OBJS  87875  87875 TABLE

– 查询被锁的 sesson 所执行的 sql
select sql_text from v$sql where sql_id in (select sql_id from v$session where sid=52);

SQL_TEXT
—————————————————————————————————-
update t_all_objs set object_name= test101 where object_id=2013011701

– 最后查询 V$lock:
select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 or request 0;

  SID TY  ID1  ID2  LMODE  REQUEST  CTIME  BLOCK
—– — ———- ———- ———- ———- ———- ———-
  46 TX  524304  13916  6  0  296  1
  52 TX  524304  13916  0  6  284  0

或者通过如下 SQL 查询会话之间锁等待的关系:
select a.sid hold_sid, b.sid wait_sid, a.type, a.id1, a.id2, a.ctime
  from v$lock a, v$lock b
 where a.id1 = b.id1
  and a.id2 = b.id2
  and a.block = 1
  and b.block = 0;

  HOLD_SID  WAIT_SID TY  ID1  ID2  CTIME
———- ———- — ———- ———- ———-
  46  52 TX  524304  13916  2717

或者如下
 select decode(request,0, holder: , waiter:) ||
 sid session_id, id1, id2, lmode, request, type
 from v$lock
 where (id1, id2, type) in (select id1, id2, type from v$lock where request 0)
 order by id1, request;

SESSION_ID  ID1  ID2  LMODE  REQUEST TY
———————————————— ———- ———- ———- ———- —
holder: 46  524304  13916  6  0 TX
waiter: 52  524304  13916  0  6 TX

   
最后可知 sid 为 46 的会话是阻塞根源。解决联系应用是不是会话为提交,或者 kill 掉

上述内容就是如何进行等待事件 enq TX row lock contention 的分析,你们学到知识或技能了吗?如果还想学到更多技能或者丰富自己的知识储备,欢迎关注丸趣 TV 行业资讯频道。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-17发表,共计10702字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)