Oerr--ORA-2049 小记一下

最新推荐文章于 2022-05-13 07:47:15 发布

cuifan5176

最新推荐文章于 2022-05-13 07:47:15 发布

阅读量959

点赞数

文章标签：数据库

有用户报告说交易失败,遇到ora-2049及相关的错误,当时第一感觉是和distributed_lock_timeout这个参数据相关,不知道是google有问题,还是就是很少有遇到这个错的, 对distributed_lock_timeout和2049的错误说明一个没有查到,失望中,直接metalink上去找之,大约有20多条相关的内容.还好最后确认是应用的workflow有问题(因为其它的site有类似的问题),感谢经验分享呀.不过还要记录一些关于distributed_lock_timeout和ora-2049的信息.

Parameter: DISTRIBUTED_LOCK_TIMEOUT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Obsoleted: Oracle8i
Parameter type: integer
Parameter class: static
Default value: 60 seconds
Range of values: 1 - unlimited
Related:

Description
~~~~~~~~~~~
Number of seconds a distributed transaction waits for a lock.
If a session waits longer than this for the lock then ORA-2049 is signalled.

This parameter can be useful to set a time-limit on statements.
The session only needs to be using a DBLINK for this behaviour (timeouts) to be enabled.

=====================================================

Error: ORA 2049
Text: timeout: distributed transaction waiting for lock
-------------------------------------------------------
Cause: Exceeded seconds waiting for lock.
Action: treat as a deadlock

Explanation:
Ignore the "Action" above - this is non-sense.

Basically ORA 2049 is signalled if:

a) you are waiting on another sessions TX enqueue
(Eg: usually you are waiting for a row lock)

AND b) you are performing a distributed operation.
Eg: You are using a DB link for something, even if it is only aselect

AND c) You wait for longer than 'distributed_lock_timeout'

The use of a DB Link opens you up to distributed rules of operation even if you only READ from it.

You can either increase the timeout OR handle the ORA 2049 as a 'try again' exception that is not fatal. This mechanism exists to prevent deadlock
so any handling of 'TRY AGAIN' should include an escape clause to prevent deadlock.

Example:
Session 1 Session 2
~~~~~~~~~ ~~~~~~~~~
update t2@LINK set b=4 where b=3;
select * from dual@LINK;
update t2 set b=4 where b=3;
^^ ORA 2049
OR EVEN
update t2 set b=4 where b=3;
select * from dual@LINK;
update t2 set b=4 where b=3;
^^ ORA 2049