oracle中ora-02049,ORA-02049 ‘timeout: distributed transaction waiting for lock’ 解决

ORA-02049 ‘timeout: distributed transaction waiting for lock’

ORA-02049是一个分布式事务等待超时的异常，当一个session 持有一个行级锁，另一个分布式事务比如通过dblink也想修改相同行的数据，就会产生 tx队列等待，当等待时间超过了系统参数distributed_lock_timeout的值时，就产生了这个错误，任何通过通过DBLINK的操作都是分布式事物。

下面还原一下这个错误

SQL> create table testdl(id int);

Table created.

SQL> insert into testdl values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into testdl values(2);

1 row created.

SQL> commit;

Commit complete.

【session1】

sys@NCME>select * from testdl@dl_1913;

ID

----------

1

2

sys@NCME>update testdl@dl_1913 set id=10 where id=1;

1 row updated.

【session2】

sys@NCME>update testdl@dl_1913 set id=20 where id=2;

1 row updated.

sys@NCME>commit;

Commit complete.

sys@NCME>update testdl@dl_1913 set id=100 where id=1;

update testdl@dl_1913 set id=100 where id=1

*

ERROR at line 1:

ORA-02049: timeout: distributed transaction waiting for lock

ORA-02063: preceding line from DL_1913

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

SQL> alter table testdl modify id primary key;

Table altered.

【session1】

sys@NCME>insert into testdl@dl_1913 values (3);

1 row created.

【session2】

sys@NCME>insert into testdl@dl_1913 values (3);

insert into testdl@dl_1913 values (3)

*

ERROR at line 1:

ORA-02049: timeout: distributed transaction waiting for lock

ORA-02063: preceding line from DL_1913

sys@NCME>show parameter distributed_lock

NAME TYPE VALUE

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

distributed_lock_timeout integer 60

解决问题的思路

首先还是应用程序的代码设计，减少多个session同时修改相同数据，减小事务，或在代码中加try do something cache exception ,when exception is ORA-02049 retry.

其次就是增加distributed_lock_timeout参数的值，增加事务等待时间

打赏

微信扫一扫，打赏作者吧～