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

今天要给一张日志表(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='271') 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='277') 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='272') 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"

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

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

结论：

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

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