`
up2pu
  • 浏览: 223707 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

【转】外键缺少索引引发的死锁

    博客分类:
  • DB
阅读更多
客户的10.2.0.4 RAC for AIX环境频繁出现ORA-60死锁问题,导致应用程序无法顺利执行。


经过一系列的诊断,发现最终问题是由于外键上没有建立索引所致,由于程序在主子表上删除数据,缺少索引导致行级锁升级为表级锁,最终导致大量的锁等待和死锁。

下面通过一个例子简单模拟一下问题:

SQL> create table t_p (id number primary key, name varchar2(30));

Table created.

SQL> create table t_f (fid number, f_name varchar2(30), foreign key (fid) references t_p);

Table created.

SQL> insert into t_p values (1, 'a');

1 row created.

SQL> insert into t_f values (1, 'a');

1 row created.

SQL> insert into t_p values (2, 'b');

1 row created.

SQL> insert into t_f values (2, 'c');

1 row created.

SQL> commit;

Commit complete.

SQL> delete t_f where fid = 2;

1 row deleted.

这时在会话2同样对子表进行删除:

SQL2> delete t_f where fid = 1;

1 row deleted.

回到会话1执行主表的删除:

SQL> delete t_p where id = 2;

会话被锁,回到会话2执行主表的删除:

SQL2> delete t_p where id = 1;

会话同样被锁,这时会话1的语句被回滚,出现ORA-60死锁错误:

delete t_p where id = 2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


SQL> rollback;

Rollback complete.

将会话1操作回滚,会话2同样回滚并建立外键列上的索引:


1 row deleted.

SQL2> rollback;

Rollback complete.

SQL2> create index ind_t_f_fid on t_f(fid);

Index created.

重复上面的步骤会话1删除子表记录:

SQL> delete t_f where fid = 2;

1 row deleted.

会话2删除子表记录:

SQL2> delete t_f where fid = 1;

1 row deleted.

会话1删除主表记录:

SQL> delete t_p where id = 2;

1 row deleted.

会话2删除主表记录:

SQL> delete t_p where id = 1;

1 row deleted.

所有的删除操作都可以成功执行,关于两种情况下锁信息的不同这里就不深入分析了,重点就是在外键列上建立索引。

虽然有一些文章提到过,如果满足某些情况,可以不在外键列上建立的索引,但是我的观点一向是,既然创建了外键,就不要在乎再多一个索引,因为一个索引所增加的代价,与缺失这个索引所带来的问题相比,是微不足道的。

参考:
http://yangtingkun.itpub.net/post/468/524858
http://www.cnblogs.com/angzi/archive/2006/12/09/587612.html


【补充】Oracle 10g和Oracle 9i trc日志内容的差别

Oracle 10g 10.2.0.3.0:
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000dd55-00000000        16     146    SX   SSX       17     148    SX   SSX
TM-0000dd55-00000000        17     148    SX   SSX       16     146    SX   SSX
session 146: DID 0001-0010-00000008	session 148: DID 0001-0011-00000006
session 148: DID 0001-0011-00000006	session 146: DID 0001-0010-00000008
Rows waited on:
Session 148: no row
Session 146: no row
Information on the OTHER waiting sessions:
Session 148:
  pid=17 serial=39 audsid=540046 user: 54/SCOTT
  O/S info: user: SKYHOME\sky, term: SKYHOME, ospid: 3028:7000, machine: WORKGROUP\SKYHOME
            program: plsqldev.exe
  application name: PL/SQL Developer, hash value=1190136663
  action name: Command Window - New, hash value=254318129
  Current SQL Statement:
  
delete t_p where id = 1
End of information on OTHER waiting sessions.
Current SQL statement for this session:
delete t_p where id = 2


Oracle 9i 9.2.0.7.0:
DEADLOCK DETECTED
Current SQL statement for this session:
delete t_p where id = 2
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000260e-00000000        21      51    SX   SSX       23      20    SX   SSX
TM-0000260e-00000000        23      20    SX   SSX       21      51    SX   SSX
session 51: DID 0001-0015-0000043D	session 20: DID 0001-0017-00000397
session 20: DID 0001-0017-00000397	session 51: DID 0001-0015-0000043D
Rows waited on:
Session 20: no row
Session 51: no row
Information on the OTHER waiting sessions:
Session 20:
  pid=23 serial=53179 audsid=197296 user: 87/scott
  O/S info: user: sky, term: SKYHOME, ospid: 5540:4984, machine: WORKGROUP\SKYHOME
            program: plsqldev.exe
  client info: 127.0.0.1
  application name: PL/SQL Developer, hash value=1190136663
  action name: Command Window - New, hash value=254318129
  Current SQL Statement:
  
delete t_p where id = 1
End of information on OTHER waiting sessions.


分享到:
评论

相关推荐

    Oracle外键不加索引引起死锁示例

    如果没有索引,这个检查过程可能需要扫描整个子表,这可能引发死锁。 具体来说,假设在两个并发会话(Session1 和 Session2)中,每个会话都在试图删除不同的记录。Session1 先对子表`fk_t`的记录进行删除,然后...

    删除数据库的所有表、存储过程、主键、外键、索引等

    这个过程通常涉及删除数据库中的所有表、存储过程、主键、外键以及索引等对象。下面我们将详细探讨这些概念及其在数据库中的作用,以及如何安全有效地执行删除操作。 首先,让我们逐一了解这些数据库元素: 1. 表...

    删除数据库的所有表、存储过程、主键、外键、索引等 .sql

    删除数据库的所有表、存储过程、主键、外键、索引等 .sql

    ARTools.Oracle抽取指定库的主键、外键、索引

    另附以下功能 * 生成目录树 * 生成迁移脚本 * 查找所有System.out,.err * 批量替换多关键字 详情见 http://blog.csdn.net/amosryan/article/details/6684465

    sql学习 主外键设计1_外键索引与性能.sql

    sql学习 主外键设计1_外键索引与性能.sql

    sql学习 主外键设计2_外键索引与锁.sql

    sql学习 主外键设计2_外键索引与锁.sql

    Mysql增加外键的方法

    在数据库管理系统中,外键是用来建立和加强两个表数据之间链接的一类约束。在MySQL数据库中,可以通过特定的语法结构来增加外键,即便在建表时未设置外键也能在之后进行添加。本文详细介绍了在MySQL中增加外键的方法...

    sql学习 监控哪些外键未建索引.sql

    sql学习 监控哪些外键未建索引.sql

    sql学习 哪些表的外键未建索引.sql

    sql学习 哪些表的外键未建索引.sql

    MySQL SQL高级特性 字段约束-索引-视图-外键学习实践

    其中,字段约束、索引、视图和外键是常用的几个重要概念。本文档将围绕MySQL中这些高级特性的学习实践进行详细探讨。 首先,字段约束是数据库中对字段应该满足的规则的一种描述。它用于保证数据库中数据的完整性和...

    MySQLSQL高级特性字段约束-索引-视图-外键学习实践.rar

    本教程主要探讨MySQL中的四大核心高级特性:字段约束、索引、视图和外键。这四个概念是数据库设计和管理的基础,对于提升数据库性能和确保数据完整性至关重要。 ### 字段约束 字段约束是定义数据库表结构时用来限制...

    java获取数据库主外键

    ### Java 获取 MySQL 数据库主外键详解 在Java开发中,有时我们需要动态地获取数据库的元数据信息,比如表结构、主键、外键等。本文将详细介绍如何使用Eclipse和JDBC API来获取MySQL数据库中的表结构以及主外键信息...

    查看数据库死锁信息

    在MySQL中,外键用于维护表之间的引用完整性,如果没有索引,进行关联操作时可能会导致大量的全表扫描,增加锁定的机会,从而引发死锁。第二大原因是并发修改位图索引,这在多用户并发访问时尤其常见,如果两个事务...

    主键和外键.doc

    #### 二、主键、外键和索引的区别 - **主键**:唯一标识一条记录,不允许有重复,不允许为空。 - **外键**:表的外键是另一表的主键,外键可以有重复,可以是空值。 - **索引**:索引字段没有重复值,但可以有一个...

    外键.sql ~MySQL创建外键的操作

    MySQL 外键

    MySQL中利用外键实现级联删除、更新

    在创建外键时,要求父表必须有对应的索引,子表在创建外键时也会自动创建对应的索引。在创建索引时,可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT、NO ACTION、SET NULL 和 CASCADE。其中 ...

    外键关联整理脚本

    在数据库设计中,外键(Foreign Key)是一个重要的概念,它用于建立和维护两个表之间的关联。外键关联是数据库关系模型中的核心要素,保证了数据的一致性和完整性。Grails是一个基于Groovy语言的开源Web应用框架,它...

    数据主键和外键的错误总结

    7. **缺乏外键指标**:外键通常需要有一个索引,以支持高效的查询。如果缺乏外键索引,可能会影响查询性能。 - **解决方法**:为所有的外键创建适当的索引,特别是在涉及大量查询的情况下。 通过以上对主键和外键...

Global site tag (gtag.js) - Google Analytics