ORACLE外键和锁

2022-10-04 09:36:35

在oracle中，如果外键未加索引，对父表的修改，会导致子表被加上全表锁。这包括两种情况：

1.删除父表中的行，如果外键上没有索引，会导致子表被加上全表锁

2.更新父表的主键（根据关系数据库的原则，更新主键是一个巨大的”禁忌”，所以一般不会出现这种情况），如果外键上没有索引，会导致子表被加上全表锁

虽然，在Oracle9i及以上的版本中，这些全表锁都是短期的，他们仅在DML操作期间存在，而不是在整个事务的期间都存在。但是即使如此，这些全表锁还是可能(而且确实会)导致很严重的锁定问题。

可以通过如下语句，查询是否存在未加索引的外键：

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 USER_CONS_COLUMNS) A,
               USER_CONSTRAINTS B
         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
           AND B.CONSTRAINT_TYPE = ‘R‘
         GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
 WHERE COL_CNT > ALL (SELECT COUNT(*)
          FROM USER_IND_COLUMNS I
         WHERE I.TABLE_NAME = CONS.TABLE_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)

这个脚本将最多处理8列外键约束(如果你的外键有更多的列，可能就得重新考虑一下你的设计了)！

除了全表锁外，在以下情况下，未加索引的外键也可能带来问题：
1.如果有on delete cascade,而且没有对子表加索引：例如,emp是dept的子表，delete deptno=10应该cascade(级联)至emp。如果emp中的deptno没有索引，那么删除dept表中的每一行时都会对emp做一次全表扫描。这个全表扫描可能是不必要的，而且如果从父表删除多行，父表中每删除一行就要扫描仪一次子表。

2.从父表查询子表：在此考虑emp/dept例子。利用deptno查询emp表是相当常见的。如果频繁地运行以下查询，你就会发现没有索引会使查询速度变慢：

select *
     from dept, emp
    where emp.deptno=dept.deptno 
      and dept.deptno=:x;

所以，要特表注意是否需要对外键加索引，防止出现这种Oracle“过分地锁定了”数据的情况。

--参考自《Oracle专家高级编程》

ORACLE外键和锁

码农公寓

相关文章