Saturday, November 15, 2008

Troubleshooting - ORA-02049: timeout: distributed transaction waiting for lock

The following steps/observations are involved in solving this issue:

1. Application team asked DBA for help to solve the following error:

We are connecting to NCAMASTERCON/xxxxx@PRDDB1



SQL> update au1011
2 set rerate_request_cd = '5',dl_service_code = 'FXLSA', sys_update_date = sysdate
3 where ban in (select ban from TEMP_LSAFF_US1011)
4 and record_type = '1';
commit;
update au1011
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock
ORA-02063: preceding line from NCAUSAGECON.PRDDB2





2. I found out that au1011 on PRDDB1 is a synonym for an object on a remote database PRDDB2

The synonym definition is as follows:
CREATE OR REPLACE SYNONYM "NCAMASTERCON"."AU1011" FOR "NCAUSAGECON"."AU1011"@"NCAUSAGECON.PRDDB2";


3. Then, I found out on PRDDB2 the "AU1011" is also a synonym, it is defined as:
CREATE OR REPLACE SYNONYM "NCAUSAGECON"."AU1011" FOR "NCAUSAGEOWN"."ACCUMULATED_USAGE_10_B";

4. Checked if there are any locks on the table: ACCUMULATED_USAGE_10_B



system@PRDDB2> @l2/locked_object_by_name.sql
Enter value for object_name: ACCUMULATED_USAGE_10_B
Lock Lock Time Object
SID Type Held Owner Object Name Held (min) Status
------ ---- ------ ---------------- ------------------------------ ---------- ------------
2278 DML Row-X NCAUSAGEOWN ACCUMULATED_USAGE_10_B 1622 VALID




5. The SQL running by session 2278 is:



system@PRDDB2> @sql_cur_sid
Enter value for sid: 2278

SQL_TEXT SQL_ID SID USER_NAME

---------------------------------------- ------------- ------ ------------
UPDATE /*+ PARALLEL ("A1",4) */ "NCAUSAG 93vbp0jv2c7w4 2278 NCAUSAGECON
ECON"."AU1011" "A1" SET "RERATE_REQUEST_
CD" = '5',"DL_SERVICE_CODE" = 'FXLSA',"S
YS_UPDATE_DATE" = SYSDATE@! WHERE ("A1".
"BAN","A1"."SUBSCRIBER_NO")=ANY (SELECT
DISTINCT "A2"."BAN","A1"."SUBSCRIBER_NO"
FROM "TEMP_LSAFF_US1011"@! "A2") AND "A
1"."RECORD_TYPE"='1'



6. Further check revealed that session 2278 on PRDDB2 was started 97784 seconds ago
and the last call it made was 97773 seconds ago



7. The application DBA observed the following:
The session (sid=7265) shows as KILLED on PRDDB1, but the corresponding remote session on PRDDB2(sid=2278) is still active.

8. Confirmed by application team, we killed the session 2278 on PRDDB2

No comments: