大表建立索引引发enq: TX - row lock contention等待

时间：2022-01-14 03:33:30

今天要给一张日志表(6000w数据)建立索引，导致生产系统行锁部分功能卡住

create index idx_tb_cid on tb_login_log(user_id);

开始执行后大概花费了20分钟

中途功能卡住无法使用

SQL> select sid,event from v$session_wait where event='enq: TX - row lock contention';

       SID EVENT

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

         2 enq: TX - row lock contention

         4 enq: TX - row lock contention

         7 enq: TX - row lock contention

         8 enq: TX - row lock contention

        12 enq: TX - row lock contention

        14 enq: TX - row lock contention

 .....省略

111 rows selected.

随机取几个sid查看语句

SQL> @getsqlbysid

Enter value for sid: 271

old   1: select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.sid='&sid') order by piece asc

new   1: select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.sid='') order by piece asc

SQL_TEXT

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

UPDATE TB_LOGIN_01 SET USER_ID = :USER_ID, ga

L_DATE = sysdate WHERE USER_ID = :"SYS_B_0" A

ND CP_GD_ID = :CP_GD_ID AND (P_ID is nu

ll OR P_ID = :"SYS_B_1" OR P_ID = :P_ID) AN

D (W_USED IS NULL OR W_USED = :"SYS_B_2") AND rownum

 < :"SYS_B_3"

6 rows selected.

SQL> /277

Enter value for sid: 277

old   1: select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.sid='&sid') order by piece asc

new   1: select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.sid='') order by piece asc

SQL_TEXT

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

UPDATE TB_LOGIN_01 SET USER_ID = :USER_ID, ga

L_DATE = sysdate WHERE USER_ID = :"SYS_B_0" A

ND CP_GD_ID = :CP_GD_ID AND (P_ID is nu

ll OR P_ID = :"SYS_B_1" OR P_ID = :P_ID) AN

D (W_USED IS NULL OR W_USED = :"SYS_B_2") AND rownum

 < :"SYS_B_3"

6 rows selected.

SQL> /

Enter value for sid: 272

old   1: select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.sid='&sid') order by piece asc

new   1: select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.sid='') order by piece asc

SQL_TEXT

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

UPDATE TB_LOGIN_01 SET USER_ID = :USER_ID, ga

L_DATE = sysdate WHERE USER_ID = :"SYS_B_0" A

ND CP_GD_ID = :CP_GD_ID AND (P_ID is nu

ll OR P_ID = :"SYS_B_1" OR P_ID = :P_ID) AN

D (W_USED IS NULL OR W_USED = :"SYS_B_2") AND rownum

 < :"SYS_B_3"

发现并非正在创建的索引表创建的行锁，与开发了解得知，这些被锁的表都是一个事务中的其中一条语句

而同一个事务中用到的最后一条语句正是使用了正在创建索引的表

结论：

大表操作时了解清楚业务逻辑、用途

大表操作时尽量选择空闲时间

相关文章