Only Allow One User to Post Receiving That Affect IMLSTRXS at a Time
User reported that in the warehouse, when they perform receiving posting, if they perform the posting at the same time, it may result in deadlock situation. When that happen, user has to choose a victim session to terminate in order to allow another session to proceed.
The issue is related to the fact that:
- The PSQL engine use “page locking” on index page. A index page may contains multiple records. In your case, your IMLSTRXS.BTR file page size is 4K. The record size is 249 bytes. Each index page can store up to index of multiple different items.
- We use TTS (transaction) with posting. The TTS is a feature offered by PSQL engine. Elliott programs would declare “Start TTS” when we start posting, and “End TTS” when we finishing posting. If there’s a failure in between, TTS can reversed the updates back to before the start TTS. Therefore, all transactions integrity are maintained. While this is good, it also has a consequence on database locking. All record and index pages get updated in between the start TTS and end TTS will be locked. PSQL do this because it needs to make sure they can restore these data and index pages can be reversed back if the transaction failed. So PSQL has to locked all pages that was updated in between. So the posting with TTS will cause vast amount of locking. From programming point of view, we can either choose to use TTS to get the posting integrity protection, or we can do without TTS to reduce the potential locking. There’s really no good choice for us in this case. Under the normal circumstance, we choose to use TTS because database integrity has to be our top priority.
It is suggested that user should coordinate with one another if warehouse posting receiving to avoid this deadlock.
It is also suggested that Elliott can develop a "Posting Lock" logic to prevent multiple users from posting the receiving at the same time. This is especially if the posting is related to IMLSTRXS. This is similar to A/R cash receipt posting lock logic. Probably both warehouse receiving, legacy PO receiving posting, as well as I/M transaction processing posting should all check this lock record, so system will only allow one posting at a time.
We probably should have a global setup flag to turn on this feature. If the feature is turn, we implement the logic in each posting program opening procedure to check the lock. If the lock checking fail, we should fail the opening procedure and exit. We should also support an event so users can subscribe to this even in case there are repeated failure which may be a the result of uncompleted posting session to cause the lock record not released.