Declare Trigger - Committed Save - firing before commit?
We have a process when a work object is 'Committed Save', we run an activity to pick this object and put in a queue and another activity will read this object (without lock) and push (copy) to a reporting env.
This works good in most cases. In some cases however the object is 'not found'.
How is this possible? We would expect that the 'Committed Save' trigger would fire when the actual commit has occurred, so how can this work object not be found?
Declare Trigger 'CreateHMReportQueue' is triggered when an instance is 'Committed Save' and triggers activity 'CreateHMReportQueue' with Execute 'Immediately'
Activity CreateHMReportQueue puts this work object in a queue.
Activity ProcessHMReportQueue reads the work object (without lock) from queue, and this when it sometimes fails to open with error 'Database-Instance-NotFound'
We have a process when a work object is 'Committed Save', we run an activity to pick this object and put in a queue and another activity will read this object (without lock) and push (copy) to a reporting env.
This works good in most cases. In some cases however the object is 'not found'.
How is this possible? We would expect that the 'Committed Save' trigger would fire when the actual commit has occurred, so how can this work object not be found?
Declare Trigger 'CreateHMReportQueue' is triggered when an instance is 'Committed Save' and triggers activity 'CreateHMReportQueue' with Execute 'Immediately'
Activity CreateHMReportQueue puts this work object in a queue.
Activity ProcessHMReportQueue reads the work object (without lock) from queue, and this when it sometimes fails to open with error 'Database-Instance-NotFound'
2016-10-28 06:36:13,989 [http-/ Proprietary information hidden:7000-3] [ STANDARD] (FW_HMFW_Work.Action) INFO - PEGA_OUTPUT activity CreateHMReportQueue, adding to Queue work object WRD-CTSC-HM-WORK CS-1554 (pxRequestor=Kommareddy,Hareesha)
2016-10-28 06:36:14,042 [ PegaRULES-Batch-2] [ STANDARD] (FW_HMFW_Work.Action) INFO - PEGA_OUTPUT Activity ProcessHMReportQueue, about to open without acquiring lock the work object WRD-CTSC-HM-WORK CS-1554 (pxRequestor=Agent(System-Queue-DefaultEntry.ProcessHMReportQueues2))
2016-10-28 06:36:14,048 [ PegaRULES-Batch-2] [ STANDARD] (FW_HMFW_Work.Action) INFO - PEGA_OUTPUT activity ProcessHMReportQueue, unable to open work object WRD-CTSC-HM-WORK CS-1554, getMessagesAll: , pxMethodStatus: Fail: Database-Instance-NotFound WRD-CTSC-HM-WORK CS-1554
Is there a possibility that 'Committed Save' trigger fired before the actual database commit?
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Pegasystems Inc.
US
I created Pega support ticket and this is what I learned
"If the trigger activity queues the work object via queue-for-agent, the insert to the queue table is part of the same database transaction as the actual update/save of the work object – they should all fail or succeed"
The CommittedSave trigger does NOT run it's activity AFTER the Commit. It runs the activity in the same transaction. At this point if you open the object in the activity it will see the state of work object BEFORE the commit (either it's stale or doesn't exist).
I created Pega support ticket and this is what I learned
"If the trigger activity queues the work object via queue-for-agent, the insert to the queue table is part of the same database transaction as the actual update/save of the work object – they should all fail or succeed"
The CommittedSave trigger does NOT run it's activity AFTER the Commit. It runs the activity in the same transaction. At this point if you open the object in the activity it will see the state of work object BEFORE the commit (either it's stale or doesn't exist).
In the activity I am adding the name of the work object to Queue-For-Agent. The fix to my issue is to check the 'Deferred' parameter to the Queue-For-Agent method. This ensures that the item gets added to the queue only if the Commit succeeds. This was when an activity reads the item from queue and does obj-open-by-handle, it reads the committed data.
Pegasystems
US
Perhaps you could temporarily (as an experiment) change your design so that instead opening the object right from the trigger activity, you have your trigger activity spin off an asynch activity that runs 3 seconds later. Make sure it's an asynch spinoff rather than merely waiting 3 seconds within the trigger activity. In other words, in this experiment, make sure the trigger activity returns before the asynch one starts.
If the activity that is starting 3 seconds later also encounters the error occasionally, then you know that the issue is not merely that the trigger occurred before the data was written. /Eric
Updated: 7 Nov 2016 13:25 EST
Pegasystems Inc.
US
Consider the log entries:
Here activity CreateHMReportQueue is called by CommitedSave trigger. This activity then calls ProcessHMReportQueue to open this work object.
A1. 2016-11-06 03:44:17,097 [ajp-/ Proprietary information hidden:20619-3] [TABTHREAD1] INFO sh.*****.com|XX.XX.XX.XX hirshf - PEGA_OUTPUT activity CreateHMReportQueue, adding to Queue work object WRD-CTSC-HM-WORK OR-44017, pxUpdateDateTime=20161106T084417.067 GMT, pxSaveDateTime=20161106T084417.068 GMT, pxCommitDateTime= (pxRequestor=Hir, Fe)
A2. 2016-11-06 03:44:21,917 [ PegaRULES-Batch-2] [ STANDARD] INFO - PEGA_OUTPUT activity ProcessHMReportQueue, unable to open work object WRD-CTSC-HM-WORK OR-44017, getMessagesAll: , pxMethodStatus: Fail: Database-Instance-NotFound WRD-CTSC-HM-WORK OR-44017
A3. 2016-11-06 03:46:43,647 [jp-/ Proprietary information hidden:20619-19] [TABTHREAD1] INFO sh.*****.com|XX.XX.XX.XX hirshf - PEGA_OUTPUT activity CreateHMReportQueue, adding to Queue work object WRD-CTSC-HM-WORK OR-44017, pxUpdateDateTime=20161106T084643.620 GMT, pxSaveDateTime=20161106T084643.621 GMT, pxCommitDateTime=20161106T084425.000 GMT (pxRequestor=Hir, Fe)
A4. 2016-11-06 03:46:43,831 [ PegaRULES-Batch-4] [ STANDARD] INFO - PEGA_OUTPUT activity ProcessHMReportQueue, opened work object WRD-CTSC-HM-WORK OR-44017, pxUpdateDateTime=20161106T084643.620 GMT, pxSaveDateTime=20161106T084643.621 GMT, pxCommitDateTime=20161106T084643.000 GMT (pxCoverInsKey=WRD-CTSC-HM-WORK CS-880)
Consider the log entries:
Here activity CreateHMReportQueue is called by CommitedSave trigger. This activity then calls ProcessHMReportQueue to open this work object.
A1. 2016-11-06 03:44:17,097 [ajp-/ Proprietary information hidden:20619-3] [TABTHREAD1] INFO sh.*****.com|XX.XX.XX.XX hirshf - PEGA_OUTPUT activity CreateHMReportQueue, adding to Queue work object WRD-CTSC-HM-WORK OR-44017, pxUpdateDateTime=20161106T084417.067 GMT, pxSaveDateTime=20161106T084417.068 GMT, pxCommitDateTime= (pxRequestor=Hir, Fe)
A2. 2016-11-06 03:44:21,917 [ PegaRULES-Batch-2] [ STANDARD] INFO - PEGA_OUTPUT activity ProcessHMReportQueue, unable to open work object WRD-CTSC-HM-WORK OR-44017, getMessagesAll: , pxMethodStatus: Fail: Database-Instance-NotFound WRD-CTSC-HM-WORK OR-44017
A3. 2016-11-06 03:46:43,647 [jp-/ Proprietary information hidden:20619-19] [TABTHREAD1] INFO sh.*****.com|XX.XX.XX.XX hirshf - PEGA_OUTPUT activity CreateHMReportQueue, adding to Queue work object WRD-CTSC-HM-WORK OR-44017, pxUpdateDateTime=20161106T084643.620 GMT, pxSaveDateTime=20161106T084643.621 GMT, pxCommitDateTime=20161106T084425.000 GMT (pxRequestor=Hir, Fe)
A4. 2016-11-06 03:46:43,831 [ PegaRULES-Batch-4] [ STANDARD] INFO - PEGA_OUTPUT activity ProcessHMReportQueue, opened work object WRD-CTSC-HM-WORK OR-44017, pxUpdateDateTime=20161106T084643.620 GMT, pxSaveDateTime=20161106T084643.621 GMT, pxCommitDateTime=20161106T084643.000 GMT (pxCoverInsKey=WRD-CTSC-HM-WORK CS-880)
A5. 2016-11-06 03:51:39,575 [ajp-/ Proprietary information hidden:20619-4] [TABTHREAD1] INFO sh.*****.com|XX.XX.XX.XX hirshf - PEGA_OUTPUT activity CreateHMReportQueue, adding to Queue work object WRD-CTSC-HM-WORK OR-44017, pxUpdateDateTime=20161106T085139.558 GMT, pxSaveDateTime=20161106T085139.559 GMT, pxCommitDateTime=20161106T084425.000 GMT (pxRequestor=Hir, Fe)
A6. 2016-11-06 03:56:41,065 [jp-/ Proprietary information hidden:20619-24] [TABTHREAD1] INFO sh.*****.com|XX.XX.XX.XX hirshf - PEGA_OUTPUT activity CreateHMReportQueue, adding to Queue work object WRD-CTSC-HM-WORK OR-44017, pxUpdateDateTime=20161106T085641.013 GMT, pxSaveDateTime=20161106T085641.014 GMT, pxCommitDateTime=20161106T084425.000 GMT (pxRequestor=Hir, Fe)
In Line A1 is called after CommitedSave is triggered, but here the pxCommitDateTime is null and therefore in the next line A2 it failed to open the object
In line A3 CommitedSave is triggered, but here the pxCommitDateTime is close to the time when A1 was run (and not A3) and pxSaveDateTime is current. Also, the CommitDate is older than Save time which hints that the commit time was not updated.
From both the above it's apparent that when we read an object after CommitedSave trigger, we don't really get the latest commited time.
This is so wrong!
***Updated by Moderator: Marissa to remove proprietary information***
Accepted Solution
Pegasystems Inc.
US
I created Pega support ticket and this is what I learned
"If the trigger activity queues the work object via queue-for-agent, the insert to the queue table is part of the same database transaction as the actual update/save of the work object – they should all fail or succeed"
The CommittedSave trigger does NOT run it's activity AFTER the Commit. It runs the activity in the same transaction. At this point if you open the object in the activity it will see the state of work object BEFORE the commit (either it's stale or doesn't exist).
I created Pega support ticket and this is what I learned
"If the trigger activity queues the work object via queue-for-agent, the insert to the queue table is part of the same database transaction as the actual update/save of the work object – they should all fail or succeed"
The CommittedSave trigger does NOT run it's activity AFTER the Commit. It runs the activity in the same transaction. At this point if you open the object in the activity it will see the state of work object BEFORE the commit (either it's stale or doesn't exist).
In the activity I am adding the name of the work object to Queue-For-Agent. The fix to my issue is to check the 'Deferred' parameter to the Queue-For-Agent method. This ensures that the item gets added to the queue only if the Commit succeeds. This was when an activity reads the item from queue and does obj-open-by-handle, it reads the committed data.
Bank of Singapore
SG
Hi,
we are also similar issue, declare trigger(run on back ground copy) missing some workobjects commits in production environment, approximately 10 in a day. we need to push the data into reporting DB. unable to replicate in lower environments.
is your approach working fine in production environment, i.e. multi node environment with 10k work objects processing in day. if so please suggest me steps to do that, are you using run on background copy or immediate execution?
i'm not seeing any errors in log files.
Thanks,
Madhu
Pegasystems Inc.
US
Hi Madhu, checking the option 'Deferred' parameter to the Queue-For-Agent method resolved the issue. If I remember, we had Execute 'Immediately' turned on.
Thanks,
-Sri