Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2986
  • Last Modified:

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

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
vish3210
Asked:
vish3210
1 Solution
 
Helena Markováprogrammer-analystCommented:
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
 
Daniel StanleyCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.

 
vish3210Author Commented:
It looks like NV_Kum is correct. Give me little time and then I will accept your answer. Thanks
Vimal
0
 
Daniel StanleyCommented:
i said pretty much the same thing and even told you which v$ view to find that info but ok.

regards,
daniels
0
 
vish3210Author Commented:
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
 
Daniel StanleyCommented:
--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
 
vish3210Author Commented:
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
 
Daniel StanleyCommented:
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
 
vish3210Author Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now