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!

I'll try to get that for you.

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?

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.

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?

Tom,
Value is set to 60. And we are not using UPDATE FOR. It's just an simple UPDATE query.

Thanks

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,

Thanks Tom,

It was a XA transaction initiated by webserver

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

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.