sdruss
asked on
Database Locks
My application is experiencing very frequent database locks, getting ORA-02049 distributed timeout locks. Of course our J2EE developers point to the DBAs, and my DBA team is pointing to bad code produced by inexperienced JAVA coders. Most of the Java software developers have limited knowledge of how a RAC distributed database works. And as you can expect Management is furious.
How can we nail down any locks in the database? How can we query gv$lock and determine, what query/tables are complicit in the application. We need to quickly identify either the offending code, or badly formed queries, or combination of both. I have found these specific queries, but need to know how to take this 2-steps forward and get the query that is part of the lock. We are running under Solaris, with a 3-node Oracle 12.1 RAC database. Things are really bad, please help.
select event,p1,p2,p3 from v$session_wait
where wait_time=0 and event='enqueue';
Show sessions waiting for a TX lock:
select * from v$lock where type='TX' and request>0;
Show sessions holding a TX lock:
select * from v$lock where type='TX' and lmode>0;
How can we nail down any locks in the database? How can we query gv$lock and determine, what query/tables are complicit in the application. We need to quickly identify either the offending code, or badly formed queries, or combination of both. I have found these specific queries, but need to know how to take this 2-steps forward and get the query that is part of the lock. We are running under Solaris, with a 3-node Oracle 12.1 RAC database. Things are really bad, please help.
select event,p1,p2,p3 from v$session_wait
where wait_time=0 and event='enqueue';
Show sessions waiting for a TX lock:
select * from v$lock where type='TX' and request>0;
Show sessions holding a TX lock:
select * from v$lock where type='TX' and lmode>0;
>> ORA-02049 distributed timeout locks
Default value of DISTRIBUTED_LOCK_TIMEOUT parameter is 60 seconds and if you are getting ORA-02049 error, then it clearly indicates that your DML operation is either taking more than 60 seconds to complete or the Remote Distributed Server takes more time to process your query..
If the query works normally and gets into issue only less frequently, then you can try increasing the value of DISTRIBUTED_LOCK_TIMEOUT from 60 seconds to a higher value.
http://orababy.blogspot.com/2013/09/ora-02049-timeout-distributed.html
Before increasing the DISTRIBUTED_LOCK_TIMEOUT parameter, try to see whether the query can be tuned out or not which can help ensure that the query is revised and working well..
Default value of DISTRIBUTED_LOCK_TIMEOUT parameter is 60 seconds and if you are getting ORA-02049 error, then it clearly indicates that your DML operation is either taking more than 60 seconds to complete or the Remote Distributed Server takes more time to process your query..
If the query works normally and gets into issue only less frequently, then you can try increasing the value of DISTRIBUTED_LOCK_TIMEOUT from 60 seconds to a higher value.
http://orababy.blogspot.com/2013/09/ora-02049-timeout-distributed.html
Before increasing the DISTRIBUTED_LOCK_TIMEOUT parameter, try to see whether the query can be tuned out or not which can help ensure that the query is revised and working well..
distributed ?
that means you run transactions over multiple databases
it requires some in depth investigation to where the failure occurs.
if you get return from this query, and the transaction keeps staying in there, then you definitely need to look further
samples:
https://oracleexamples.wordpress.com/2009/08/13/how-can-we-remove-delete-in-doubt-transactions/
http://www.oracle11ggotchas.com/articles/ProceduretoROLLBACKFORCEpendingin-doubttransaction.htm
every situation is different
with microsoft sql, you'll have to look on the ms transaction coordinator to resolve it on that side
(not part of the database, but of the windows os)
we have a mix oracle/mssql with these things
and after some weeks of figuring it out we finally found how to automate the cleanup of the failures with some scripts
that means you run transactions over multiple databases
it requires some in depth investigation to where the failure occurs.
if you get return from this query, and the transaction keeps staying in there, then you definitely need to look further
select * from dba_2pc_pendingsamples:
https://oracleexamples.wordpress.com/2009/08/13/how-can-we-remove-delete-in-doubt-transactions/
http://www.oracle11ggotchas.com/articles/ProceduretoROLLBACKFORCEpendingin-doubttransaction.htm
every situation is different
with microsoft sql, you'll have to look on the ms transaction coordinator to resolve it on that side
(not part of the database, but of the windows os)
we have a mix oracle/mssql with these things
and after some weeks of figuring it out we finally found how to automate the cleanup of the failures with some scripts
ASKER CERTIFIED SOLUTION
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I've only ever seen "ORA-02049 distributed timeout locks" in systems that include at least one database link to a remote database. The remote database could be either another Oracle database or a non-Oracle database. We have two different 3-node RAC systems here, and I have never seen this error in our RAC systems (which do not include database links).
ASKER
Mark,
Definitely, absolutely NO database links in our system.
Definitely, absolutely NO database links in our system.
>> Definitely, absolutely NO database links in our system
ORA-02049 distributed timeout locks error will come only if you have Database Links configured and DML operations are happening across links.
I suspect you might not have complete permissions to see the Database Links or something else..
Kindly check with your DBA to confirm whether there were any Database Links configured or not..
ORA-02049 distributed timeout locks error will come only if you have Database Links configured and DML operations are happening across links.
I suspect you might not have complete permissions to see the Database Links or something else..
Kindly check with your DBA to confirm whether there were any Database Links configured or not..
3-node rac ? nothing to do with that.
it's still only 1 database
we get those distributed tranny's because on the programming side a "distributed" transaction is started on mssql db and continued on oracle
as i said, requires an in-depth investigation of what starts where ...
database links are not required for distributed transactions
you getting a 1591 error too ?
it's still only 1 database
we get those distributed tranny's because on the programming side a "distributed" transaction is started on mssql db and continued on oracle
as i said, requires an in-depth investigation of what starts where ...
database links are not required for distributed transactions
you getting a 1591 error too ?
I've only ever seen "ORA-02049 distributed timeout locks" in systems that include at least one database link to a remote database.There are at least 3 nodes involved here!
https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txns001.htm#ADMIN12211
3-node rac ? nothing to do with that.I doubt that! There are still 3 nodes involved in transactional processing!
it's still only 1 database
There are tons of issues in the web dealing with similar problems using RAC, like:
https://thehelpfuldba.com/index.php/2016/12/02/mysterious-contention/
https://www.hhutzler.de/blog/a-deep-dive-into-2-phase-commit-with-wildfly-and-oracle-rac/
https://www.orafaq.com/forum/t/197769/
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:11530969111365
ASKER
How do I determine which query and what tables are involved in the ORA-2049 lock?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another good idea would be to have your java developers do a thread dump when the locking occurs. They then should be able to use that to help track down the offending code.