oracle 11g的ae锁,一次ORACLE数据库行级锁异常故障 | 信春哥，系统稳，闭眼上线不回滚！...

今天通过监控发现，促销数据库存在大量的行级锁等待，详见下图。

通过监控发现，导致问题的是BI的两条UPDATE语句造成的。

以下是相关的SQL信息：

update MEDA_PROM.ZT_YPH_USER a set a.level_id= (select b.cst_gd_cd from MEDA_PROM.TEMP_USER_OLD b where b.CST_ID=a.id) where a.id in(select d.cst_id from MEDA_PROM.TEMP_USER_OLD d)

update MEDA_PROM.ZT_YPH_USER a set a.total_buy_count= (select b.total_buy_count from MEDA_PROM.TEMP_USER_JE b where b.CST_ID=a.id) where a.id in(select b.CST_ID from MEDA_PROM.TEMP_USER_JE b)

以下是锁的相关信息：

通过查询，发现只有ZT_YPH_USER表的ID字段存在主键，其他表的字段均没有索引。

SQL> select index_name,COLUMN_NAME,table_name from dba_ind_columns where TABLE_OWNER='MEDA_PROM' and table_name in ('ZT_YPH_USER','TEMP_USER_OLD','TEMP_USER_JE');

INDEX_NAME COLUMN_NAME TABLE_NAME

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

ZT_YPH_USER_PK ID ZT_YPH_USER

以下是这三张表的数据量相关信息。

SQL> select count(*) from MEDA_PROM.ZT_YPH_USER;

COUNT(*)

----------

14555416

SQL> select count(*) from MEDA_PROM.TEMP_USER_OLD;

COUNT(*)

----------

481

SQL> select count(*) from MEDA_PROM.TEMP_USER_JE;

COUNT(*)

----------

670

以下是第一条SQL的执行计划信息。

通过查看，表关联使用的字段，字段类型不匹配，该SQL需要进行隐式转换，ZT_YPH_USER表的ID字段是NUMBER类型，而和它关联的TEMP_USER_OLD表的CST_ID和TEMP_USER_JE表的CST_ID都是VARCHAR2类型。

SQL> desc MEDA_PROM.ZT_YPH_USER

Name Null? Type

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

ID NOT NULL NUMBER(20)

USER_NAME VARCHAR2(200)

GNR_TP_CD VARCHAR2(7)

BRY_DATE VARCHAR2(12)

LEVEL_ID NUMBER(20)

PVC VARCHAR2(300)

CITY VARCHAR2(300)

JOIN_DTM DATE

TOTAL_BUY_COUNT NUMBER(20)

TOTAL_BUY_MONEY NUMBER(20)

TOTAL_BUY_POINT NUMBER(20)

CHANNEL VARCHAR2(40)

SYNC_DATE NOT NULL DATE

SQL> desc MEDA_PROM.TEMP_USER_OLD

Name Null? Type

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

CST_ID VARCHAR2(40)

CST_GD_CD VARCHAR2(7)

SQL> desc MEDA_PROM.TEMP_USER_JE;

Name Null? Type

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

CST_ID VARCHAR2(40)

TOTAL_BUY_COUNT NUMBER(38)

TOTAL_BUY_MONEY NUMBER(20)

通过在TEMP_USER_OLD和TEMP_USER_JE表的CST_ID字段创建函数索引(TO_NUMBER)，问题即可解决。

SQL> select index_name,COLUMN_NAME,table_name from dba_ind_columns where TABLE_OWNER='MEDA_PROM' and table_name in ('ZT_YPH_USER','TEMP_USER_OLD','TEMP_USER_JE');

INDEX_NAME COLUMN_NAME TABLE_NAME

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

IDX_TEMP_USER_OLD_CST_ID CST_ID TEMP_USER_OLD

IDX_TEMP_USER_JE_CST _ID CST_ID TEMP_USER_JE

ZT_YPH_USER_PK ID ZT_YPH_USER

经过和BI的负责人沟通，知道这条两条SQL为每小时运行一次，从BI数据库向这个数据库推送数据，经沟通后，最终将TEMP_USER_OLD和TEMP_USER_JE表的CST_ID字段修改为NUMBER类型，问题解决。