Expert Oracle Database Architecture

死锁的头号原因：不加索引的外键(第6章.锁)

[@more@]

http://www.cnblogs.com/angzi/archive/2006/12/09/587612.html

据Tom的经验，导致死锁的头号原因是外键未加索引，第二号原因是表上的位图索引遭到并发更新。在以下两种情况下，Oracle在修改父表后会对子表加一个全表锁：
·如果更新了父表的主键(倘若遵循关系数据库的原则，即主键应当是不可变的，这种情况就很少见。)由于外键上没有索引，所以子表会被锁住。
·如果删除了父表中的一行，整个子表也会被锁住(由于外键上没有索引，比如：如果EMP表有DEPT的一个外键，而且在EMP表的DEPTNO列上没有任何索引，那么更新DEPT时整个EMP表都会被锁定)。

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

因为在关系数据库中，更新主键是一个很大的禁忌，所以在更新主键方面一般没有什么问题。但是如果开发人员用工具生成SQL，而且这些工具会更新每一列，此时更新主键就会成为一个严重的问题。

删除父表中的一行可能导致子表被锁住，由此产生的问题更多。如：
create table p(x int primary key);
Table created.

create table c(x reference p);
Table created.

如果删除p表中的一行，则在DML操作期间，子表c就会被锁定，这样可以避免事务期间对c执行其他更新(当然，这里有一个前提，即没有人在修改c， 如果确实已经有人在修改c，删除会等待)。此时就会出现阻塞和死锁问题。通过锁定整个表c，数据库的并发性就会大幅下降，以至于没有人能够修改c中的任何 内容。另外，出现死锁的可能性则增大了。

Tom说，如果有人抱怨说数据库中存在死锁，他会让他们运行一个脚本，察看是否存在未加索引的外键，而且99%的情况下都会发现表中的确存在这个问 题。只需对外键加索引，死锁(以及大量其他的竞争问题)都会烟消云散。下面的例子展示了如何使用这个脚本来找出表c中未加索引的外键。

ops$tkyte@ORA10G> column columns format a30 word_wrapped
ops$tkyte@ORA10G> column tablename format a15 word_wrapped
ops$tkyte@ORA10G> column constraint_name format a15 word_wrapped

ops$tkyte@ORA10G> select table_name, constraint_name,
2 cname1 || nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
5 nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode( position, 1, column_name, null )) cname1,
10 max(decode( position, 2, column_name, null )) cname2,
11 max(decode( position, 3, column_name, null )) cname3,
12 max(decode( position, 4, column_name, null )) cname4,
13 max(decode( position, 5, column_name, null )) cname5,
14 max(decode( position, 6, column_name, null )) cname6,
15 max(decode( position, 7, column_name, null )) cname7,
16 max(decode( position, 8, column_name, null )) cname8,
17 count(*) col_cnt
18 from (select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30) constraint_name,
20 substr(column_name,1,30) column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type = 'R'
26 group by b.table_name, b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 ( select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1, cname2, cname3, cname4,
33 cname5, cname6, cname7, cname8 )
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36 )
37 /
TABLE_NAME CONSTRAINT_NAME COLUMNS
------------------------------ --------------- ------------------------------
C SYS_C009485 X

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

除了全表锁外，在以下情况下，未加索引的外键也可能带来问题：
·如果有on delete cascade,而且没有对子表加索引：例如,emp是dept的子表，delete deptno=10应该cascade(级联)至emp。如果emp中的deptno没有索引，那么删除dept表中的每一行时都会对emp做一次全表扫 描。这个全表扫描可能是不必要的，而且如果从父表删除多行，父表中每删除一行就要扫描仪一次子表。
·从父表查询子表：在此考虑emp/dept例子。利用deptno查询emp表是相当常见的。如果频繁地运行以下查询，你就会发现没有索引会使查询速度变慢：
select * from dept, emp
where emp.deptno=dept.deptno and dept.deptno=:x;

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

××××××××××××××××××××××××××××××××××××××××××××××××

http://space.itpub.net/17203031/viewspace-701832

××××××××××××××××××××××××××××××××××××××××××××××××

Normal 0 false false false MicrosoftInternetExplorer4

外键是我们经常使用的数据库约束方式。它保证了子表上的特定索引列取值一定与父表列（主键列）相一致对应。而且保证在子表没有对应子记录的情况下，父表数据才能删除。应该说，外键是实现数据库完整性、消除潜在脏数据风险的重要手段。

在使用外键的时候，我们经常会要求在子表外键列上建立索引。这个“江湖定则”有什么样的原因呢？本篇我们一起来进行探讨。还是使用组合实验的方式来进行研究。

1、环境准备

我们在Oracle 11gR2版本上进行试验，构建初步的实验环境。

首先建立父子表关系。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

--环境准备

SQL> create table master (id number, vname varchar2(10));

Table created

SQL> alter table master add constraint pk_master primary key (id);

Table altered

SQL> create table detail (did number, mid number, details varchar2(10));

Table created

SQL> alter table detail add constraint pk_detail primary key (did);

Table altered

SQL> alter table detail add constraint fk_detail_master_mid foreign key (mid) references master(id);

Table altered

上面建立了数据表master和detail，两个数据表分别以id和did作为主键。同时，detail的mid列作为与master表id列的外键关系，建立外键fk_detail_master_mid。

两个数据表对象的基础信息对应为：

SQL> select object_name,object_id from dba_objects where object_name in ('MASTER','DETAIL','IDX_DETAIL_MID');

OBJECT_NAME OBJECT_ID

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

DETAIL 75191

MASTER 75189

基础数据准备：

SQL> select * from master;

ID VNAME

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

1 df

2 dfdf

3

4（后加入该条记录）

SQL> select * from detail;

DID MID DETAILS

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

1 1

2 1 dkff

3 2 fdkff

会话准备，作为并发操作实验，我们准备两个会话窗口。sid1和sid2，其中sid1为主操作会话窗口。sid1会话编号为135，sid2会话编号为18。另外，使用监控语句监控并发锁状态。

select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

2、外键关联下，DML操作锁的特殊性

首先我们探讨一下Oracle条件下外键关联影响DML操作锁的情况。在一般情况下，Oracle使用的是行锁机制，保证进行DML操作的数据行不会被其他会话影响，也不会影响到其他数据行的操作，更不会影响到其他数据表的问题。

在没有外键的情况下，我们观察一下Oracle锁情况（额外实验，非Oracle11g环境）。

SQL> select sid from v$mystat where rownum<2;

SID

----------

144

SQL> insert into t select * from dba_objects;

50367 rows inserted

SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=144;

SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

144 TM 54206 0 3 0 0

144 TX 327716 1068 6 0 0

从这个案例中，可以看到Oracle 进行DML操作的基本规范。在进行DML操作的时候，Oracle首先会尝试在数据表添加一个TM锁，级别是共享锁（lmode=3）。这个TM锁的 id1参数为添加对象的object_id。这个锁的作用是保证在进行DML事务的时候，数据表不会被修改或者独占，体现在被添加3级锁以上级别的锁级 别。这样，如果有其他会话要进行DML操作，在加入lmode=3锁的时候，是不会发生阻塞的。这样也就保证了对同一个数据表，是可以同时并发DML操作 的。

其次，Oracle会独占使用数据行记录。就体现在添加的TX锁上，注意是lmode=6的级别锁。TX锁的参数信息，表示的是对应的事务段信息。

结论：在没有外键的作用下，Oracle进行的是最小粒度的锁控制。不会影响到其他数据表。

如果添加了外键，我们对数据表的DML操作后，将会有何种影响呢？

SQL> insert into master values (4,'dk');

1 row inserted

此时，监控结构。

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

135 TM 75189 0 3 0 0

135 TM 75191 0 3 0 0

135 TX 655390 1000 6 0 0

我们对主表进行操作，除了对主表的共享锁之外，还额外对子表（object_id=75191）进行加共享锁机制。

在主表操作上，delete主表操作不会带来对子表的连带锁操作。

SQL> delete master where id=3;

1 row deleted

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

135 TM 75189 0 3 0 0

135 TX 65564 963 6 0 0

update操作，也是不会影响到子表记录。

SQL> update master set vname='df' where id=3;

1 row updated

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

135 TM 75189 0 3 0 0

135 TX 262156 999 6 0 0

那么，我们对子表进行DML操作的时候，会连带影响到父表锁吗？下面进行DML操作实验。

SQL> insert into detail values (4,2,'d');

1 row inserted

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

135 TM 75189 0 3 0 0

135 TM 75191 0 3 0 0

135 TX 262170 1001 6 0 0

SQL> update detail set details='dkl' where did=3;

1 row updated

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

135 TM 75191 0 3 0 0

135 TX 393236 1219 6 0 0

SQL> delete detail where did=3;

1 row deleted

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

135 TM 75189 0 3 0 0

135 TM 75191 0 3 0 0

135 TX 327713 1218 6 0 0

子表操作的效果上，对子表进行insert和delete操作时候，也是会影响到父表锁结构的。

注意：我们上面的实验，是在子表无索引的情况下进行的。如果我们建立了索引结构，上面实验有什么不同的？

SQL> create index idx_detail_mid on detail(mid);

Index created

进行上述相同实验，由于篇幅原因，不加以累述，只是针对与无索引时候的差异进行阐述。

在对主表进行操作的时候，delete操作会引起连带的锁结构。

SQL> delete master where id=3;

1 row deleted

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');

SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

135 TM 75189 0 3 0 0

135 TM 75191 0 3 0 0

135 TX 524319 1418 6 0 0

对子表进行操作的时候，有无索引引起的锁结构相同。下表表示时候有连带锁效应。