[ucarion]

Do folks have any thoughts on ways of avoiding deadlocking access patterns? In a codebase where folks are sort of adding ad-hoc endpoints left and right, it's hard to avoid the case of two endpoints that more or less want to do:

    tx1: update a
    tx2: update b
    tx1: update b
    tx2: update a

Is there a "discipline" or practice that works well? Like, can you realistically, in a real-world messy business codebase, impose an "ordering" on your tables to avoid dining philosophers?

[rawgabbit]

If you have different endpoints contending over the same rows, I would create “backend batching”. I would force endpoints to call a stored procedure. The stored procedure would append only to a “queue” table. Multiple workers would read from the queue and update the real tables in batches then update the queue with success and error codes. The batches are partitioned by the PK to minimize lock contention.

[forgotmy_login]

Recalling from my previous studies here: I think you can use Serializable Isolation Level, the strictest level - this will cause one of the two to fail (that is; fail only when the two txns affected rows that would logically conflict). And then you build the expectation of such possible transaction failures into the code and treat retries as a first-class expectation. Does this get to what you're trying to solve at all?

[ucarion]

It does get at what I'm talking about. But I've seen retrying in this situation lead to worsening the situation, because your basic problem is two hot paths conflicting with each other and now you're conflicting even more.

[mrkaye97]

(Matt from Hatchet - Hi Ulysse :wave:)

I, at least, don't know of a perfect fix here. Re: the original comment - Postgres will also error on deadlocks after it detects them without setting your isolation level to Serializable, but I agree with you that often retrying doesn't help, and could even cause cascading / snowballing failures if you have a backlog of retries piling up because of deadlocks.

I don't know if there's a good solution, really. We've fixed deadlocks incrementally over time as we've found them, which has worked pretty well, but of course that means also needing to deal with the "finding" part, which has generally come in the form of lots of `deadlock detected` log lines and errors (and retries accompanying those).

One thing that might be worth auditing is why there are two different bits of application code that are updating the same rows in two different tables in different orders. I know it's a contrived example, but it seems like it could be a code smell to me. Maybe this is the kind of thing that arises when two different subteams are working on the same database and are largely siloed.

Alexander will likely have more thoughts here as well, just my two cents!

[malisper]

When I've dealt with this I've generally made sure the transactions are updating rows in a consistent order. You can do that by sorting the rows before you update them