ORA-02049
A reader, December 02, 2005 - 8:45 am UTC
We are using COM+ to handle our transactions from the application server perspective. That basically creates a very similar situation to a database link, and places us in the category of "distributed transactions". This works fine everywhere, except this one place in our application, where we hit this problem. The evidence I have found surrounding this problem does not make sense to me, and I wonder if you can help.
I have your EOOO book, and compiled your locking and transaction information gathering sql statements into a script I called blocker.sql.
Ok - When a tester recreated the error, I was running this blocker script at the same time over and over again to capture the locking and transaction information. Here is what I saw:
user@db> @blocker
THIS IS THE LOCK DISPLAY SECTION
********************************
USERNAME SID RBS SLOT SEQ LMODE REQUEST
============================== ========== ========== ========== ========== ========== ==========
PHAME 26 11 42 6077 6 0
1 row selected.
THIS IS THE TRANSACTION DISPLAY SECTION
***************************************
XIDUSN XIDSLOT XIDSQN
========== ========== ==========
11 42 6077
1 row selected.
THIS IS THE BLOCKER SECTION
***************************
no rows selected
So you can see these match the outputs of the scripts you wrote in your book. The last section would have shown if "some user were blocking some other user", but as you can see, no user blocking user information appears.
I kept running the blocker script over and over, and finally, after a bit of time (probably about the 60 second default that the distributed lock timeout is set for), the lock information disappeared, but the transaction information remained! What does that mean? Here is what I saw:
user@db> @blocker
THIS IS THE LOCK DISPLAY SECTION
********************************
no rows selected
THIS IS THE TRANSACTION DISPLAY SECTION
***************************************
XIDUSN XIDSLOT XIDSQN
========== ========== ==========
11 42 6077
1 row selected.
THIS IS THE BLOCKER SECTION
***************************
no rows selected
Eventually then, the transaction information went away as well.
How did I get a deadlock within the same session? Is that possible to deadlock yourself in the same session? We are executing PL/SQL programs to perform inserts and updates...
What am I seeing here?
Thanks very much for any help!
December 02, 2005 - 11:08 am UTC
what does the "transaction" itself look like. what sql was involved here.
I didn't capture that
A reader, December 05, 2005 - 6:34 pm UTC
I'll try to get that for you.
ora-02049
oracle user, January 12, 2006 - 11:45 am UTC
we are receving ORA-02049 in our application too.Our environment is a trigger based replication and after the upgrade from 8.1.7.4 to 9.2.0.6 we are receving ora-02049 occasionaly. The distributed_lock_timeout is set to very low value (5 secs). We can't raise this value as it impacts application functionality.To troubleshoot the issue I am trying to capture the holding and waiting sessions and the SQLs they are running.
Please verify if the following sqls make any sense.
select l.type,l.id1,l.id2,l.lmode,l.request,s.username,s.sid,o.owner,o.object_name,c.sql_text from v$lock l,v$session
s,dba_objects o,v$open_cursor c where l.type like 'T%' and l.sid=s.sid and l.id1=o.
object_id and l.sid=c.sid;
select d.waiting_session,o.sql_text from dba_waiters d,v$open_cursor o where d.waiting_session=o.sid;
select d.holding_session,o.sql_text from dba_waiters d,v$open_cursor o where d.holding_session=o.sid;
Do you suggest any other method where I can track waiting and holding sessions and the SQLs they are running?
January 12, 2006 - 11:55 am UTC
you'd have to be running that query continously - since the 2049 "breaks out of the lock" - it is not something you can diagnose after the fact. Not sure you want to do that.
Basically, if you are building "do it yourself replication" and constantly hitting locking situations, I would guess you have lost update (update conflict issues) as well - doesn't sound too "healthy".
ora-02049
orauser, January 12, 2006 - 6:20 pm UTC
You are right. The inhouse replication is not exaclty trigger based. Its kind of trigger and procedure based.When ever a insert,update or delete happens on active site table a corresponding trigger will be fired and in turn calls a corresponing insert or delete or update synonym passing the values. These synonyms are defined on remote proceduers via dblink. The procedures on the remote site will perform delete or insert or update based on the triggering event.
The databases are active/passive. Application allways connects to the active database.Lots of dml activity will be going on in the database. Three times we received timeout error on the updates after our weekly purge.I am not sure why we are encountering resource locks on the remote site.
January 13, 2006 - 11:02 am UTC
I don't think it is active/passive then. Something is obviously running on the remote site that is locking data.
Heck, it could be yourself hitting yourself (since the rows are in different orders on the two sites).
session 1 might update rows A and B in T (in that order)
session 2 might update rows B and A in T (in that order)
syncronous replication - man oh man....
timeout
Aarti, October 20, 2008 - 9:26 am UTC
Tom,
We are accessing a remote DB through a job. Often the remote query (a procedure) hangs and because of which our job hangs which results in db locks at our end.
Our call to remote db should timeout if we are not able acquire the lock on remote db in 3 minutes. How do we implement this and what parameters must be set?
October 21, 2008 - 12:01 pm UTC
show parameter distributed_lock_timeout
what is it set to. Also, you have select for update wait N at your disposal.
timeout remote
Aarti, October 22, 2008 - 7:32 am UTC
Tom,
Value is set to 60. And we are not using UPDATE FOR. It's just an simple UPDATE query.
Thanks
October 22, 2008 - 8:58 am UTC
then it would time out on a remote update of longer then 60 seconds.
are you saying "it is not"
and I mentioned "select for update wait N" as a way to control this yourself at whatever level of detail you want. It is a tool YOU can use.
0ra-02049
Deepak Sholapurkar, April 29, 2010 - 2:53 am UTC
Hi Tom,
We are getting the ORA-02049 error in single database(RAC Environment)
In Our RAC environment, Two transactions are updating the table A.
table A is updated by Transaction1-Node1, and Transaction2-Node 2 is waiting for transaction1 to commit. And we are getting the ORA-02049: timeout: distributed transaction waiting for lock.
So can you please help us to understand, is simultanious transactions from different nodes is causing the issue or any other cause for this error.
Thanks & Regards,
April 29, 2010 - 7:38 am UTC
it would not happen that way, transaction 1 and 2 must be more complex than you describe, the client might be doing something "unusual" (eg: they might be XA transactions which are distributed by definition).
You'd need to provide etep by steps to reproduce. Using sqlplus would be best :) Cut and paste - just like I do
0ra-02049
Deepak Sholapurkar, May 05, 2010 - 3:17 am UTC
Thanks Tom,
It was a XA transaction initiated by webserver
May 06, 2010 - 1:53 pm UTC
see, you are a distributed transaction - you are using an external resource manager.
ORA-02049: time-out: distributed transaction waiting for lock
Brijesh, March 06, 2012 - 1:38 am UTC
I have CPUs: 16 Cores: 16 Sockets: 4 2 Node RAC. Getting ORA-02049: time-out: distributed transaction waiting for lock.
AWR stats are
Execute to Parse %: 0.39 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 0.01 % Non-Parse CPU: 98.96
RAC Statistics
Estd Interconnect traffic (KB) 884.69
Wait Events Statistics
Statistic Name Time (s) % of DB Time
DB CPU 9,576.19 75.85
The query is generated by the ATG application and uses the update statement for record update.
Can you please provide your recommendations
March 06, 2012 - 6:53 am UTC
I have no idea what "ATG" is or does.
are you using distributed transactions or XA from the middle tier?
Need some detail to reproduct ORA 02049 Error in dev DB
Pallavi, July 21, 2020 - 3:55 pm UTC
hi
i am facing one issue in which there is a SOA application(Merge statemnet) which is updating ROWS in my DB1.Tab1, and there is a batch job which is also updating records in same dB1.tab1 same Table with a merge statement,some time i am seeing so many instances in SOA is throwing Ditributed transaction waiting for lock error and failing.
Need help in this issue.
I tried to reproduce this issue in my dev environment by using following steps:
1. Ran the batch job
2.tried to update the records which JOB will update by process one by one in SOA, but unable to get that error what i am getting in Prod.
July 24, 2020 - 3:37 am UTC
You could try setting an event to capture more details when you get the lock
ALTER SESSION SET EVENTS '2049 trace name errorstack level 3';
but it sounds to me you have an application issue somewhere