ORA-02049: time-out: distributed transaction waiting for lock

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.

In our environment a delete statement was running on server on a table and someone

Run delete statement on same table and same row from remote machine. 'row-x'  lock was held by the one session and other requested for it and second session terminate

With error ORA-02049: time-out: distributed transaction waiting for lock

  

We can find the locks held  on table  and  statement behind it.

SQL> select a.sid, a.serial#,sql_id ,(case LOCKED_MODE   when 0 then 'none'

  2  when 1 then  'null'

  3   when  2 then 'row-S'

  4  when 3 then 'row-x'

  5  when 4 then 'share'

  6  when 5 then 'S/Row-X'

  7  when  6 then 'exclusive'

  8   end ) LOCKED_MODE

  9    from v$session a, v$locked_object b, dba_objects c

 10  where b.object_id = c.object_id

 11  and a.sid = b.session_id

 12   and OBJECT_NAME='XYS'

 13  ;

SQL> select sql_text from v$sql where sql_id='8tswhcpv65b35';

SQL_TEXT

--------------------------------------------------------------------------------

 delete from xys where id=2