ANBOB™

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参数的值，增加事务等待时间

打赏

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