首页 > 代码库 > ORACLE外键和锁
ORACLE外键和锁
在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外键和锁