Oracle Sql Error 2049
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about oracle distributed_lock_timeout Stack Overflow the company Business Learn more about hiring developers or posting ads with
Ora 02049 Distributed_lock_timeout
us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is sql error: 2049, sqlstate: 42000 a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to troubleshoot ORA-02049 and lock problems in general with Oracle up vote 9
Distributed Lock Timeout Parameter In Oracle
down vote favorite 4 I am getting ORA-02049 occasionally for some long-running and/or intensive transactions. There is seemingly no pattern to this, but it happens on a simple INSERT. I have no clue how to get any sort of information out or Oracle, but there has to be a way? A log over locking or atleast a way to see current locks? sql oracle deadlock ora-02049 share|improve this question asked ora-02049 dblink Mar 24 '10 at 9:12 sandos 5671821 Metalink has a lot of information about all of the ORA- codes, which you should look at. Ultimately, if you are getting this error on a production environment, you should open a support request with Oracle. –a'r Mar 24 '10 at 9:54 1 I'm not posting this as an answer because it's more of a guess, but do you see ORA-600 errors in the alert log for the remote database? If so, you might be running into ITL (interested transaction list) deadlocks. I'm only suspecting this because you mention intensive transaction activity. This might be due to low INITRANS values used when creating the tables and indexes. –dpbradley Mar 24 '10 at 12:44 add a comment| 4 Answers 4 active oldest votes up vote 8 down vote accepted One possible way might be to increase the INIT.ORA parameter for distributed_lock_timeout to a larger value. This would then give you a longer time to observe the v$lock table as the locks would last for longer. To achieve automation of this, you can either Run an SQL job every 5-10 seconds that logs the values of v$lock or the query that sandos has given above into a table and then analy
however, and remains locked for a long time, a timeout occurs. Consider the following scenarios involving data access failure: Transaction Timeouts
Ora-02049 Timeout Distributed Transaction Waiting For Lock Weblogic
Locks from In-Doubt Transactions Transaction Timeouts A DML statement that requires locks
Ora-02049 Websphere
on a remote database can be blocked if another transaction own locks on the requested data. If these ora-02049 weblogic locks continue to block the requesting SQL statement, then the following sequence of events occurs: A timeout occurs. The database rolls back the statement. The database returns this error http://stackoverflow.com/questions/2506411/how-to-troubleshoot-ora-02049-and-lock-problems-in-general-with-oracle message to the user: ORA-02049: time-out: distributed transaction waiting for lock Because the transaction did not modify data, no actions are necessary as a result of the timeout. Applications should proceed as if a deadlock has been encountered. The user who executed the statement can try to reexecute the statement later. If the lock persists, then the user should https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txnman008.htm contact an administrator to report the problem. Locks from In-Doubt Transactions A query or DML statement that requires locks on a local database can be blocked indefinitely due to the locked resources of an in-doubt distributed transaction. In this case, the database issues the following error message: ORA-01591: lock held by in-doubt distributed transaction identifier In this case, the database rolls back the SQL statement immediately. The user who executed the statement can try to reexecute the statement later. If the lock persists, the user should contact an administrator to report the problem, including the ID of the in-doubt distributed transaction. The chances of these situations occurring are rare considering the low probability of failures during the critical portions of the two-phase commit. Even if such a failure occurs, and assuming quick recovery from a network or system failure, problems are automatically resolved without manual intervention. Thus, problems usually resolve before they can be detected by users or database administrators. Scripting on this page enhances content navigation, but does not change the content in any way.
occurs when a session that is involved in a distributed transaction waits for another session’s TX enqueue (row lock) for longer than the distributed_lock_timeout value. Any operation that uses a database link, even a https://oracletuningcorner.com/2012/03/16/ora-02049-timeout-distributed-transaction-waiting-for-lock/ query starts a distributed transaction. In the below POC, if there was a COMMIT after the [select 1 from dual@database_link], the situation would become a normal lock situation – the second session will proceed once there is a COMMIT in the 1st session. In a situation which were in a session was waiting on a row lock and involved in distributed transaction, it would timeout after the distributed_lock_timeout period. sql error If the transaction was a normal one, sooner or later a DBA will notice the lock and could kill the holding session. Workarounds: There are two ways to deal with this problem: Increase the distributed_lock_timeout value hoping that the session holding the lock releases it before the timeout. Code the application to capture this ORA error and then issue a TRY AGAIN directive. Another workaround would be to add oracle sql error more COMMIT’s as they tend to reset the distributed transaction. Identifying the Root Cause: To identify the session holding the resources, what oracle suggests is to dump a system state trace BEFORE the ORA-02049 occurs. However, I have devised another approach based on oracle’s original suggestion. The basic idea will be to monitor v$session for sessions waiting on the event ‘enq: TX – row lock contention’. If the seconds_in_wait value for such sessions approaches the distributed_lock_timeout value and the session is involved in a distributed transaction we can find the session holding the lock by joining v$lock and v$session. POC Session 1 SQL> update dummy_table set table_name ='UPDATED_DUMMY'; -->A LOCAL UPDATE STATEMENT 24 rows updated. -->NO COMMIT ISSUED SQL> Session 2 SQL> select 1 from dual@database_link; ------>THIS STARTS THE DISTRIBUTED TRANSACTION 1 ---------- 1 SQL> update dummy_table set table_name ='UPDATED_DUMMY'; ---->THIS IS A LOCAL UPDATE, BUT ERRORS OUT WITH ORA-02049 update dummy_table set table_name ='UPDATED_DUMMY' * ERROR at line 1: ORA-02049: timeout: distributed transaction waiting for lock SQL> 12.918260 77.614995 Share this:Share on Facebook (Opens in new window)Click to share on Twitter (Opens in new window)Click to share on Google+ (Opens in new window)Click to share on LinkedIn (Opens in new wind