Oracle11g提高了DML修改父表时锁定子表的级别

近日处理了一个电信计费系统的性能故障,因为外键上没有索引,导致出现enq: TM 锁争用,以及不时出现Deadlock问题,处理完后整理资料,发现这篇blog叙述比较清晰,遂用Google Translate翻译如下:

Oracle 11g(自11.1.0.6以来)引入了一个微妙但可能有重大的变化,关于在监管外键约束方面保持锁的方式。 以下内容已在11.2.0.1和11.2.0.2上进行了测试。

为了设置场景并复制我们在工作中遇到的问题,我将创建一个小表(ALBUMS),它有2个FK约束,指向两个父表(ARTISTS和FORMATS)并用几行填充它们。

SQL> CREATE TABLE artists (id NUMBER PRIMARY KEY, artist_name VARCHAR2(30));

Table created.

SQL> CREATE TABLE formats (id NUMBER PRIMARY KEY, format_name varchar2(30));

Table created.

SQL> CREATE TABLE albums (id NUMBER, album_name VARCHAR2(30), artist_id NUMBER CONSTRAINT artist_fk REFERENCES artists(id), format_id number

CONSTRAINT format_fk REFERENCES formats(id));

Table created.

SQL> INSERT INTO artists VALUES (1, 'DAVID BOWIE');

1 row created.

SQL> INSERT INTO artists VALUES (2, 'PINK FLOYD');

1 row created.

SQL> INSERT INTO formats VALUES (1, 'CD');

1 row created.

SQL> INSERT INTO formats VALUES (2, 'DVD');

1 row created.

SQL> INSERT INTO albums VALUES (1, 'LOW', 1, 1);

1 row created.

SQL> INSERT INTO albums VALUES (2, 'DIAMOND DOGS', 1, 1);

1 row created.

SQL> COMMIT;

Commit complete.

好的,在10.2.0.3中的ARTISTS表上运行以下insert语句时:

SQL> insert into artists values (3, 'MUSE');

1 row created.

由于这些表之间的FK关系,检查v$lock视图将显示该事务在子ALBUMS表上的行级-共享(SS)Mode 2 TM(DML Enqueue)锁定。

 

如果另一个会话要么说删除一行或从另一个父FORMATS表更新PK:

SQL> update formats set id = 2 where id = 2;

1 row updated.

当它暂时需要在ALBUMS表上进行TM共享(S)模式4锁定时,它将成功没有问题,它可以成功获取它,因为并发SS锁定不会阻止这种情况发生。 它需要访问此模式4共享锁定以确保当前没有影响ALBUMS表的事务可能违反父FORMATS表上的DML操作之后的约束。
但是,在Oracle 11g中重复相同的练习,我们遇到了细微的差别。 在ARTISTS表中再次运行insert语句时:

SQL> insert into artists values (3, 'MUSE');

1 row created.

现在,检查v$lock视图将显示该事务在子ALBUMS表上的行X(SX)LMODE 3中保持TM(DML Enqueue)锁定,而不是像在10g中那样在LMODE 2 SS级别锁定。 这是一个“更高”级别的锁定模式,对于现在尝试删除或更新FORMATS表中的PK的其他会话具有以下结果:

SQL> update formats set id = 2 where id = 2
;

 

 

会话现在挂起,因为它必须等待另一个会话释放DML Enqueue LMODE 3 SX锁,然后它才能获得所请求的TM模式4共享表锁。 这正是我们用一个写得不好的应用程序试图在两个不同的会话中执行类似于上述系列更新的问题所遇到的问题。
Oracle引入了此更改,以消除在重建引用PK的关联FK索引时从具有PK的表中删除行时可能发生的ORA-600问题。
然而,以这种方式引入更严格的锁定级别具有增加遇到诸如此类的新锁定问题的可能性的副作用,增加了遇到死锁情况的可能性(如Charles Hooper先前所讨论的)并因此可能 降低应用程序的整体并发能力。
在这种情况下,“修复”只需在formats_id FK列上创建一个索引(在这种情况下应该要求这样做,以防止更新父FORMAT表时子表上的锁定问题):

SQL> CREATE INDEX albums_format_i on albums(format_id);

Index created.

SQL> insert into artists values (3, 'MUSE');

1 row created.

在这种情况下,ALBUMS表上不再需要表共享锁(因为Oracle现在可以使用关联的索引在父表上执行此类操作后有效地监视子表的完整性)并且语句不再挂起其他会话(session):

 

SQL> update formats set id = 2 where id = 2;

1 row updated.

如果您可能具有没有关联索引的FK约束,那么在迁移到Oracle 11g时,必须注意监管FK约束的锁定行为的这种变化。

附两条查找外键上没建索引的SQL:
sql 1(这个是Tom Kyte书上提供的):

select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from all_cons_columns where owner='&schema_name') a,
all_constraints b
where a.constraint_name = b.constraint_name
and b.owner='&schema_name'
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from all_ind_columns i
where i.table_name = cons.table_name
and i.table_owner='&schema_name'
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)

sql 2(来自Donald Burlson):

select
case
when b.table_name is null then
'unindexed'
else
'indexed'
end as status,
a.table_name as table_name,
a.constraint_name as fk_name,
a.fk_columns as fk_columns,
b.index_name as index_name,
b.index_columns as index_columns
from
(
select
a.table_name,
a.constraint_name,
listagg(a.column_name, ',') within
group (order by a.position) fk_columns
from
dba_cons_columns a,
dba_constraints b
where
a.constraint_name = b.constraint_name
and
b.constraint_type = 'R'
and
a.owner = '&&schema_owner'
and
a.owner = b.owner
group by
a.table_name,
a.constraint_name
) a
,(
select
table_name,
index_name,
listagg(c.column_name, ',') within
group (order by c.column_position) index_columns
from
dba_ind_columns c
where
c.index_owner = '&&schema_owner'
group by
table_name,
index_name
) b
where
a.table_name = b.table_name(+)
and
b.index_columns(+) like a.fk_columns || '%'
order by
1 desc, 2;

Leave Comment