Queue Posting Reports to Minimize Page Locking

Queue Posting Reports to Minimize Page Locking

Sometimes, multiple posting reports can be started by users at about the same time.  This can result in competition for setting locks to update records on the same page.  Especially, since Elliott posting routine will use the TTS feature in PSQL (Zen) database engine, it greatly increase the page locking in between starting and ending of a TTS transaction.  The worst-case scenario is that a deadlock could occur, one of both process has to be terminated in order for the other to continue.  Luckily, the terminated process is protected by the TTS, so it can be run again without causing data integrity problem. However, the termination of deadlock condition still require IT's involvement and hence it is a nuisance. 

This proposed change would cause posting reports to be queued by the deferred processor, guaranteeing that multiple posting reports could not be run concurrently.

Proposal to Run Posting Reports in Deferred Processing

The purpose of this suggestion can potentially have the following benefits:
  1. Typically, defer processing is to run on the PSQL (Zen) server. By moving the posting routines that currently takes place on the workstation to the server will result in much faster posting performance.
  2. If we can manage to queue the posting tasks in the defer processing to run one at a time, then we can avoid deadlock condition.  On the other hand, it is much more difficult to queue or coordinate the posting on the client side on a workstation by workstation basis.
Some of the programming pieces are already in place.  For example, when a posting report is initiated by a user, the system manager can detect that it is a posting program.  If it is a posting program that is to be run, the system manager could, instead of starting it immediately, add it to the deferred processing queue (as a one-time request with the current date and time).  It effectively would be run immediately after any currently-running posting deferred process completes.  Because the user would not wait to see the results, an automatic email could be sent when the report is completed.  This would have the added benefit of ensuring that posting reports are always on the server, reducing the time to run and minimizing the record-by-record network traffic.

This is similar to the RunNow capability Elliott currently has, except that RunNow does not wait in a queue.  RunNow reports start processing immediately on the server, so multiple RunNow posting reports could experience the same potential for a deadlock.

This proposal is to do the following:

  •  Detect if a report is a posting task.  There is no need to enforce one defer task at a time if it is a regular report.
  • If it is a posting task, add the report to the deferred processing queue:

  • The posting tasks in the defer queue can only have one task running at the same time.
  • Since it is possible the previous posting may be stuck. To prevent the stuck posting task hold up all other pending posting tasks, there's a time out of waiting for maximum 10 mins of previous posting tasks. If a posting task that's longer than 10 minutes should cause an event to be triggered to potentially alert the admin users.  Currently, there's a similar event for any defer processing task that's running over 4 hours.
  • The top pending posting task in the queue is to start as soon as the previous posting task finished.
  • When the defer posting task is done, system should send an email to the person starting the report when the posting report is done. 
Further, a global flag is to be added to System -> Print Opt/report/powersearch, "Defer Wait for Previous Posting to Complete?" should be created to determine whether or not to use this new capability. For backward compatibility, the default value to be set to “No”.