On Fri, Mar 11, 2022 at 10:03 PM Michael Lewis <mlewis@entrata.com> wrote:
Could you set lock_timeout, lock table explicitly for SHARE UPDATE EXCLUSIVE (pretty sure that would be the proper level), then retry if it fails because a delete is already going on?
I don't think I can take a lock on the table, there are multiple process that update the status table. While each process does not overlap, I think locking the table would cause them to block each other.
I think we would just have to retry the delete after the deadlock, which is currently done manually by refreshing the web page. The update never seems to be interupted, probably because it longer running and starts before the random delete.
Also, are you confident that before you call 'begin' to do the update, you are not already in a transaction which might have some lock on row(s) in mach, or one of the other tables involved?
For the update processes, all other tables are read-only reference tables, I don't think they would be locked.