oracle多进程阻塞,生产系统oracle数据库出现大量锁进程的一次问题排查分享

异常问题：

数据库后台出现大量锁进程，从AWR报告可发现删除A表记录时操作发生长时间表级锁等待

 

测试：

查看代码，根据业务逻辑执行SQL语句(同一事务)：

.....

DELETEFROM A WHERE ZCDH=?;     ----删除子表记录

DELETEFROM B WHERE ZCDH=?;       ----删除父表记录

发现在A表上建立了外键关联B表，并且在代码里一个事务会先执行删除A表记录，再删除B表记录，根据此逻辑进行测试：

测试库建子表fk_table父表pk_table，父表主键OBJECT_ID为字表外键关联，分别插入相同数据

create table fk_table as select * from user_objects;

create table pk_table as select * from user_objects;

alter table PK_TABLE add constraint pk_pktable primary key (OBJECT_ID);

alter table FK_TABLE add constraint fk_fktable foreign key (OBJECT_ID) references pk_table(OBJECT_ID);

目前数据库无锁：

select

'alter system kill session '''|| sess.sid ||''||','|| sess.serial# ||''';',

sess.sid,

sess.serial#,

lo.oracle_username,

lo.os_user_name,

ao.object_name,

lo.locked_mode

from v$locked_object lo,

dba_objects ao,

v$session sess

whereao.object_id = lo.object_id and lo.session_id = sess.sid;

得出：

模仿业务逻辑，在同一事务中，先删除一条子表记录，再删除一条外键关联的父表的记录，不提交事务

delete from fk_table where object_id=85191;

delete from pk_table where object_id=85191;

打开第二个PL/SQL会话，模拟第二个用户登录，再次执行删字表及父表另一条记录

delete from fk_table where object_id=94702;

delete from pk_table where object_id=94702;

当执行第二条delete时发生等待

打开第三个PL/SQL会话，执行对子表的任何DML语句均发生等待，因为无法获取子表上的表级共享锁，出现锁争用

此时查看数据库锁进程情况：

第一个会话事务结束后第二个会话正常执行delete操作

解决办法：

在子表外键列上创建常规索引：

CREATE INDEX fk_supplier ON fk_table(OBJECT_ID);

再按实验方法测试发现无等待，因为这时删除父表记录不需要对子表加表级锁

建议：

1、 外键使用要慎重，在DML语句频繁的表中最好不要建立外键约束

2、 建立外键约束养成习惯在外键列上建立索引