?
Solved

..i_err_code = ORA-02049: timeout: distributed transaction waiting for lock

Posted on 2006-11-28
10
Medium Priority
?
2,979 Views
Last Modified: 2008-01-09
Experts,

When I try to run one of the package it threw following error. I would like to know the reason and solution.

..i_err_code = ORA-02049: timeout: distributed transaction waiting for lock

Thank you
Vimal
0
Comment
Question by:vish3210
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 18034822
Maybe this will be a little help for you:

ORA-02049: timeout: distributed transaction waiting for lock
Cause: exceeded INIT.ORA distributed_lock_timeout seconds waiting for lock.
Action: treat as a deadlock

(http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/ds_txnman.htm):
Transaction Timeouts
A DML statement that requires locks on a remote database can be blocked if another transaction own locks on the requested data. If these 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 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 contact an administrator to report the problem.

0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 18034827
would also help to know what package you are calling, and which database version you are using. this error often occurs when distributed_lock_timeout is set too low and/or if a particular object is locked for update when you are attempting to access it. if you can, look in V$LOCKED_OBJECT for any objects you are trying to access via the package you're calling.

good luck,
daniels
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18038346
just check/verify whether there is any other program which is trying to update/delete the existing records which are modified by some other program but not yet commited/rolledback.

Is this issue seen everytime you call your package.procedure or package.function or only during a certain time saying only between night 10 to 11 PM. If the last is the case, then there could be someother program running during that time which will be the cause of this issue.

thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:vish3210
ID: 18041051
It looks like NV_Kum is correct. Give me little time and then I will accept your answer. Thanks
Vimal
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 18041065
i said pretty much the same thing and even told you which v$ view to find that info but ok.

regards,
daniels
0
 

Author Comment

by:vish3210
ID: 18057040
Daniels,

What do you understand by this. I ran this

SQL> select * from V$LOCKED_OBJECT

XIDUSN      XIDSLOT      XIDSQN      OBJECT_ID      SESSION_ID      ORACLE_USERNAME      OS_USER_NAME      PROCESS      LOCKED_MODE
1      46      157      10019      302      OPS$NSCDPRAP      nscdprap      27309      3
1      46      157      9985      302      OPS$NSCDPRAP      nscdprap      27309      3
1      46      157      9984      302      OPS$NSCDPRAP      nscdprap      27309      3


Please tell me what is wrong and how to get out with it. Pleaase

Thanks
Vimal
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 18057135
--find out which objects are locked
select owner, object_name
from dba_objects
where object_id in
(select object_id from V$locked_object);

--identify hash value for session_id in v$locked_object
select sid, PREV_HASH_VALUE
from v$session
where sid=302;


--use hash value from previous query in this
--statement to find out which statement has the object locked.
select sql_text
from v$sqlarea
where hash_value = <PREV_HASH_VALUE>;


good luck, let me know if i can help further.

daniels
0
 

Author Comment

by:vish3210
ID: 18057268
Great Danial,

I've ran that query I got where it has been locked.

select sql_text
from v$sqlarea
where hash_value = 1872360291;

Answer for above query:

SELECT CSCYM.BLNK_STY_DSP_NBR FROM CDB_STY_CYC CSCYM WHERE CSCYM.STY_ID = :B2 AND CSCYM.CYC_ID = :B1

Now what??? and how should I get away with that. Today is last day for this. I need to wrap this up. Thanks again. Please help.

--Vimal


0
 
LVL 7

Accepted Solution

by:
Daniel Stanley earned 500 total points
ID: 18057512
you could just kill that session.  do you know who the OPS$NSCDPRAP/nscdprap user is?  that is who has the object locked..  it may be that the terminal is vacant and that object will be locked until that user logs off.  you could kill his session if you feel you will not interrupt anyone else's work.  

--sid should still be 302 as is above, you'll  need to query v$session to get the serial#
select sid, serial# from v$session where osuser = 'nscdprap';
or
select serial#, osuer from v$session where sid=302;


alter system kill session '302, serial#';


let me know if i can help further.

good luck,
daniels
0
 

Author Comment

by:vish3210
ID: 18057831
Daniel,
I killed the session and now again I started running my package in OPS$NSCDPRAP schema. It running right now. Let's seeeeeeee. I will mail you tommoroow, the reason being this is one of the package running more than 8 hours sometime in the development schema. If this runs success I will let you by then.

See you tommorrow. Have a great weekend

--Vimal
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question