今天要给一张日志表(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"
发现并非正在创建的索引表创建的行锁，与开发了解得知，这些被锁的表都是一个事务中的其中一条语句
而同一个事务中用到的最后一条语句正是使用了正在创建索引的表
结论：
大表操作时了解清楚业务逻辑、用途
大表操作时尽量选择空闲时间