VERIFIED SOLUTION i

Error 'ORA-02049: timeout: distributed transaction waiting for lock' in Portrait Dialogue

Product Feature: Errors
 

Issue

Intermittently some operations failed due to 'ORA-02049: timeout: distributed transaction waiting for lock' in Portrait Dialogue.

The Portrait Dialogue Server error logs show following entry in it:

====================================================================


Exception: ORA-02049: timeout: distributed transaction waiting for lock
Time: 6/14/2017 2:23:28 AM
Module: C:\PROGRA~2\PST\MILLIO~1\DIALOG~1\MHDIAL~1.DLL
Version: 6.0.1.283
Filedate: 12/11/2013 2:00:18 PM
Instancename: PROD
Username: internal
ProcessID: 3812
ThreadID: 5520
Exceptiontype: EMHSQLException
Unit: MHSQLAccessLayer.pas
Procedure: MHSQLAccessLayer.TMHSQLAccessLayer.DoExecuteSQLRaw
Line: 328
SQLStatement: UPDATE dlg_participant
SET
  dp_dg_id = :param3,
  dp_moved_timestamp = {TIMESTAMP},
  dp_internal_id = :param4
WHERE
  (dlg_participant.dp_dg_id = :param5) AND
  (dlg_participant.dp_active = :param6)  AND
  EXISTS (SELECT
  *
FROM
  (select t.account as mh_customer_id
from
cmdm.transaction_FLEXIPAY t
where
(
(t.posting_date = TRUNC(sysdate-1-1))
OR
(t.posting_date = TRUNC(sysdate-8-1))
OR
(t.posting_date = TRUNC(sysdate-15-1))
)
and t.amount>0
and account not in ( /* Control + Block Code 2 */  
   select dp_customer_id as account from dlg_participant where dp_dg_id in (219121,219181,219251)
   union
   select account from cmdm.customer_information where transfer_account in
    (select dp_customer_id as account from dlg_participant where dp_dg_id in (219121,219181,219251))
)) mh_customselection
WHERE
  (mh_customselection.mh_customer_id = dlg_participant.dp_customer_id))

[02E22861] MHSQLAccessLayer.TMHSQLAccessLayer.DoExecuteSQLRaw (Line 328, "MHSQLAccessLayer.pas")
[02E236D5] MHSQLAccessLayer.TMHSQLAccessLayer.ExecuteSQLRaw (Line 540, "MHSQLAccessLayer.pas")
[03653F1B] MHCustomerContainer.TMHActiveParticipantContainer.DoAcceptAll (Line 3874, "MHCustomerContainer.pas")
[03657EB2] MHCustomerContainer.TMHBaseActiveContainer.AcceptAll (Line 4790, "MHCustomerContainer.pas")
[02B8ED8E] System.Win.ComObj.TAutoObject.Invoke
[038235FF] MHPluginAccessLayer.TMHScriptExecuter.InternalRunScriptFunction (Line 1227, "MHPluginAccessLayer.pas")
[038237CA] MHPluginAccessLayer.TMHScriptExecuter.RunScriptFunction (Line 1243, "MHPluginAccessLayer.pas")
[0381FD34] MHPluginAccessLayer.TMHPluginAdapter.ExecuteBranch (Line 579, "MHPluginAccessLayer.pas")
[0315D0E0] MHDialogAccessLayer.TMHDialogAccessLayer.DoExecuteOperation (Line 547, "MHDialogAccessLayer.pas")
[031610B3] MHDialogAccessLayer.TMHDialogAccessLayer.ProcessTask (Line 1377, "MHDialogAccessLayer.pas")
[03986DFE] MHInternalServiceSupport.TMHInternalServiceSupport.ProcessTask (Line 437, "MHInternalServiceSupport.pas")

====================================================================

Cause

A Data Manipulation (DML) statement that requires locks on a remote database can be blocked if another transaction own locks on the requested data. If these locks(from 1st transaction) continue to block the requesting SQL statement(2nd transaction) then the error is observed.This situation is treated as a deadlock beacuse the time to wait on a lock in a distributed transaction has been exceeded.

Resolution

UPDATED: October 16, 2018
The standard description of this is that the transaction did not modify data, therefore no actions are necessary as a result of the timeout. The user who executed the statement can try to re-execute the statement/operation later.  If the lock persists, then the user should contact an database administrator to report the problem.

What this means in Dialogue is that since the transaction failed, no data was written, the operation can be run again.
It is possible that this can be averted by a combination of retry and timeout settings.

Engineering has added an Extra Feature (SynchronizeUpdatingOperationLog) in Dialogue 6.1.4 HotFix8 and higher, to deal with this.

Extra features in Dialogue are enabled by an entry in the ExtraFeatures.ini file, and they are all disabled by default.
This feature is specifically to address the issue with hanging because of the deadlock while updating operation log.
If it does not exist, the ExtraFeatures.ini file should be created here "C:\Program Files (x86)\PST\Million Handshakes\Dialog Server".
The entry for SynchronizeUpdatingOperationLog  looks like this to enable it:
; CES-53396 - hanging because of the deadlock while updating operation log
SynchronizeUpdatingOperationLog=1