An issue arose when the database of one of our Elliott users became inconsistent and caused some operational difficulty. After investigating the issue, we found out the problem was due to a user performing Warehouse Management Systems (WMS) processing at the same time that another user was performing warehouse receiving posting. The two sides locked each other out. The user choose to terminate the WMS processing so the warehouse receiving posting could finish. We ended up manually fixing the problem through the PSQL backend. But it was a time-consuming process. We wrote the following explanation and best practices on how to handle locking situations like this in the future.

• First, you need to make sure this is indeed a deadlock situation -- that both the posting routine and WMS sides show locked and stuck on the same record. Wait a minute or two to see if the locking clears.
• When two sides are waiting for each other to release the lock, this is called a "deadlock." The only way out is to terminate one side, but which side?
• Our suggestion is that you terminate the posting routine side. This is because the posting side is protected by TTS. If you terminate the posting side (by using Windows Task Manager, normally), PSQL has the ability to reverse back the database to the original state before posting. This is a consistent state.  So you can try the posting again.
• When we implemented Elliott WMS, we were debating whether to use the TTS feature in PSQL or not. It is easy to implement TTS, but there’s one drawback, which is that it will cause extensive page locking on the PSQL database. You can imagine when the system declares the start of TTS to PSQL, then before the system declares an end to TTS -- usually at a logical point where all updates of a transaction are done -- PSQL needs to be able to protect the changes you made with locks on those changed data pages (usually 4K in size) to prepare in case a rollback is necessary. Because of the locks, if another user tries to update the same PSQL data page at the same time, then that user will get the locked message. Based on our experience, the conflict is mostly on the updating of index data pages and not even related to the same record. So it may be confusing to you why locks can take place when two sides are updating different records (e.g., two different items).
• So using TTS is a double-edged sword: It protects your database integrity but it also increases the potential for locking conflicts. 
• We have to implement TTS with Elliott posting routines because they are critical.
• On the other hand, we did not implement TTS with most WMS processes if they are conducted in real time. If we were to implement locking with WMS, it would create extensive locking events and make multi-user concurrent operations difficult.
• So the important takeaway lesson is that when there’s a deadlocking conflict between WMS and other posting routines (like warehouse receiving posting,) you should choose to terminate the posting routine. No harm will happen to the database integrity for the posting routine.
• To further reduce potential locking conflicts, you can also consider using deferred processing for posting, or making sure posting routines take place at a set time where there will be no or minimal WMS operations.