ORA-02049: timeout: distributed transaction waiting for lock

dml锁+主外键约束+dblink 导致 ORA-02049: timeout: distributed transaction waiting for lock
 

该 sql 主要是 通过一个 dblink 向 远程表 B 里插入数据,
而 B 的外键 引用了 A 表的主键,  并且此时 A 表有个 DML 操作,一直没有 commit/rollback,
从而导致了以下这个错误.
Caused by: java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock

 

最终也通过实验模拟验证了这个结果 , 主要由如下两个操作会导致这个错误 :

1.  主外键表的dml 操作会导致一个session一直hang在那里

首先是主外键的锁与一般DML 的锁不同 ,

 session 1:  delete from a where id=1002; -- 对主键表做一个dml操作
 
 session 2:  lock table a in share mode;  -- 当对外键表做插入操作时,会自动对主键表加一个表级别的share lock.
                                          -- 如果session 1 不commit/rollback, session 2 会一直等待.

 

create table a (id int primary key);
insert into a values (1001);
insert into a values (1002);
insert into a values (1003);

create table b ( name char(10),fk int );
alter table b add constraint fk_id FOREIGN KEY(fk) REFERENCES a(id);

session 1 :
insert into a values (1004);
session 2 一直处于等待 :
insert into b values ('test1',1004);

session 1 :
delete from a where id=1003;
session 2 同样会一直等待 :
insert into b values ('test1',1003);

2.  ORA-02049: timeout: distributed transaction waiting for lock  出现的原因  -- db link 的影响

session 1 :
delete from b where fk=1001;

session 2 :
delete from b where fk=1001;   -- 会一直等待下去

 

session 1 : delete from a where id=1001;

session2 :  select * from ;          -- 使用db_link 随意访问一个表
            delete from a where id=1001;                       -- 如果没有 上面的db_link远程查询,会一直等待

等待 distributed_lock_timeout 指定的时间后报如下错误 :
SQL Error: ORA-02049: timeout: distributed transaction waiting for lock
02049. 00000 -  "timeout: distributed transaction waiting for lock"
*Cause:    exceeded INIT.ORA distributed_lock_timeout seconds waiting for lock.
*Action:   treat as a deadlock

SQL> show parameter distributed_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout             integer     60

网上有人做过trace file 分析, 在产生的trace文件里，我们看到了XCTEND rlbk=1, rd_only=1，
这表示"select count(*) from "已经用到了回滚段了 -- 即一个db_link的select 语句会产生一个回滚段，
也就是产生了一个transaction，并且这个transaction是read only的。

 

我遇到的情形如下
session 1 :

insert into a values (1004);

session 2 :

select * from ;
insert into b values ('test1',1004);    -- insert into select * from local_table ;是同一个问题

SQL Error: ORA-02049: timeout: distributed transaction waiting for lock
02049. 00000 -  "timeout: distributed transaction waiting for lock"
*Cause:    exceeded INIT.ORA distributed_lock_timeout seconds waiting for lock.
*Action:   treat as a deadlock

参考 :
http://blog.itpub.net/781883/viewspace-700169/
http://blog.csdn.net/dba_waterbin/article/details/11384379