测试的时候数据库外键导致死锁_外键字段未建索引引发的死锁

外键字段未建索引引发的死锁

现象：一个很简单的程序在压力测试过程中发现死锁，查看trace文件，发现如下信息：

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TM-0000627d-00000000 21 1062 SX SSX 22 1092 SX SSX

TM-0000627d-00000000 22 1092 SX SSX 21 1062 SX SSX

session 1062: DID 0001-0015-0000001E session 1092: DID 0001-0016-0005C757

session 1092: DID 0001-0016-0005C757 session 1062: DID 0001-0015-0000001E

Rows waited on:

Session 1092: no row

Session 1062: no row

而引发这个死锁的sql如下：

Current SQL Statement:

DELETE FROM TABLE WHERE COL = :B1

首先注意到的是Deadlock graph中的资源占有情况，可以看到两个session都hold了一个SZ类型的锁，同时在等待SSX类型的锁，而且引发的是一个删除语句，并且这个表是系统的一个关键表，大部分的表的外键都引用自此表的主键。因此猜测是碰到了外键引发的死锁。试验如下：

1、创建一个表，此表作为子表

SQL> create table fk_table as select * from user_objects;

Table created

2、创建一个表，此表作为父表

SQL> create table pk_table as select * from user_objects;

Table created

3、创建父表的主键

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

Table altered

4、创建子表的外键

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

Table altered

5、如下sql取自TOAD工具，用来显示数据库锁的信息

SELECT LK.SID,

SE.USERNAME,

SE.OSUSER,

SE.MACHINE,

DECODE(LK.TYPE,

'TX',

'Transaction',

'TM',

'DML',

'UL',

'PL/SQL User Lock',

LK.TYPE) LOCK_TYPE,

DECODE(LK.LMODE,

0,

'None',

1,

'Null',

2,

'Row-S (SS)',

3,

'Row-X (SX)',

4,

'Share',

5,

'S/Row-X (SSX)',

6,

'Exclusive',

TO_CHAR(LK.LMODE)) MODE_HELD,

DECODE(LK.REQUEST,

0,

'None',

1,

'Null',

2,

'Row-S (SS)',

3,

'Row-X (SX)',

4,

'Share',

5,

'S/Row-X (SSX)',

6,

'Exclusive',

TO_CHAR(LK.REQUEST)) MODE_REQUESTED,

TO_CHAR(LK.ID1) LOCK_ID1,

TO_CHAR(LK.ID2) LOCK_ID2,

OB.OWNER,

OB.OBJECT_TYPE,

OB.OBJECT_NAME,

LK.BLOCK,

SE.LOCKWAIT

FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE

WHERE LK.TYPE IN ('TM', 'UL')

AND LK.SID = SE.SID

AND LK.ID1 = OB.OBJECT_ID(+);

6、执行一个删除操作，这时候在子表和父表上都加了一个Row-S(SX)锁

delete from fk_table where object_id=94716;

delete from pk_table where object_id=94716;

7、执行另一个删除操作，发现这时候第二个删除语句等待

delete from fk_table where object_id=94702;

delete from pk_table where object_id=94702;

执行查询语句，得到锁信息如下：

857 DML Row-S (SS) None 107220 0 BILL TABLE PK_TABLE 0 00000000D555A0E8

872 DML Row-X (SX) None 107220 0 BILL TABLE PK_TABLE 0

857 DML Row-X (SX) S/Row-X (SSX) 107219 0 BILL TABLE FK_TABLE 0 00000000D555A0E8

872 DML Row-X (SX) None 107219 0 BILL TABLE FK_TABLE 1

可以看到session 857在请求一个SSX类型的锁，此时如果执行如下操作：

delete from pk_table where object_id=94716;

死锁马上发生，因为857 SESSION拿到了一个对FK_TABLE的行独占锁，并在请求一个表共享锁，而872 SESSION也拿到了一个FK_TABLE上的行独占锁，并请求一个表共享锁。此时两个session谁都不会释放独占锁，并同时请求表的共享锁，死锁由此引发。因为死锁引发的时候两个session不是在等待对数据行进行加锁，所以可以从trace文件中发现等待的行都为no row，同时可以看到两个session都hold了一个SX锁，并且都在等待SSX锁资源。同时trace文件中还记录了引发死锁的sql。

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TM-0001a2d3-00000000 16 872 SX SSX 20 857 SX SSX

TM-0001a2d3-00000000 20 857 SX SSX 16 872 SX SSX

session 872: DID 0001-0010-000F5EA0 session 857: DID 0001-0014-001D7407

session 857: DID 0001-0014-001D7407 session 872: DID 0001-0010-000F5EA0

Rows waited on:

Session 857: no row

Session 872: no row

Current SQL Statement:

delete from pk_table where object_id=94716

8、当对子表的外键列添加索引后，死锁被消除，因为这时删除父表记录不需要对子表加表级锁，这里不再做测试。

结论：曾经有人讨论过是否所有的数据库设计都应该遵守范式的规范，都把主外键关系建立起来。也有人反对这样做，因为这样复杂的关系在OLTP系统中可能会成为灾难，而提倡通过程序来保证数据的完整性，但程序发生bug导致数据不一致的情况时有发生。而且如果外键设置为级联删除，则不加索引的外键会使得对子表的记录删除走全表扫描。因此，对外键的使用还是要慎重！

btw：为什么删除子表记录的时候要加表级的共享锁呢？还有这个加锁好像只是一瞬间的，期待深入！！！

发表于： 2009-04-09，修改于： 2009-04-09 15:31，已浏览1802次，有评论0条 推荐 投诉