> On Jul 28, 2025, at 10:28 AM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> RHEL 8.10
> Prior version: 14.18
>
> There were deadlocks when at PG 14, but a small fraction of the current number of deadlocks.
>
> All tables were vacuumed and analyzed immediately after the pg_upgrade. 😉
>
> Tables are partitioned by range (weekly). Physical replication; no logical replication.
> There have been no code changes since the pg_upgrade (performed 9 nights ago).
>
> Attached is a section of the PG log file. It's the same kind of deadlock, in the same code as before; just now there
are_more_ of them.
>
> I don't control the schema or the application, or the code in the application; we just need to know why there would
be_more_ in 17.5 than in 14.18.
>
Deadlock are an application issue. The application is accesses rows in order that causes the conflict.
i.e.
process 1 attempts to update row: r1,r2,r3
process 2 attempts to update row: r1,r3,r2
That above scenario will cause a deadlock.
The real question is what is the application doing? And how is it updating the records; is there a deterministic order?
All things being equal; here are some things that can cause deadlocks where it use to work fine — if the updates are
notfully deterministic:
1. Execution plan is different thus it changing the order of row being updated.
2. Race conditions; just different performance metrics.
3. Where the row lives in the table.
4. New index; changing the order of execution
etc.
If the application does not guarantee the update order then deadlocks can/will occur for any of those reasons.
In a well defined system there shouldn't be deadlocks. Deadlock usually occur because the of order of execution that
wasnot full thought through or two different routines process records in a different way (which is common, when you
haveteam of developers and they code things differently).
i.e.
If you update the invoice_detail table then invoice table; that it is likely to have deadlocks if two people update the
sameinvoice.
Where as; if you update the invoice table then the invoice_detail; then there should not be any deadlocks regardless if
twopeople try and update the same invoice.
