Re: Why many more deadlocks after upgrade to PG 17.5?

["DINESH NAIR" <Dinesh_Nair@xxxxxxxxxxxxxxxxx>]

Hi 

   >>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 not fully >>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 was not full >>thought through or two different routines process records in a >>different way (which is common, when you have team 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 same invoice.

>>Where as; if you update the invoice table then the invoice_detail; >>then there should not be any deadlocks regardless if two people try >>and update the same invoice.

   Most likely reasons of dead Locks in PostgreSQL incase of partition tables

1. Locking Parent Table and Partition Simultaneously

1. When a query touches both the partitioned parent and one or more child partitions, PostgreSQL may take multiple locks:

1. ACCESS SHARE or ROW EXCLUSIVE on the parent table

1. ROW EXCLUSIVE on the child partitions

      Changing the order of execution can help to resolve 

5. 2. Foreign Key Constraints without Proper Indexes

1. If a foreign key references a partitioned table, and indexes are missing on the referencing or referenced columns (especially in child partitions), PostgreSQL might lock the entire partition or scan multiple rows, causing blocking.

1. 3. Concurrent Inserts into Multiple Partitions

1. When inserting into a partitioned table :
   If two transactions insert into different partitions but one also modifies the parent metadata (e.g., attach/detach partition or analyze), this can deadlock.

1. 4. Row Locking Across Partitions

1. If application logic tries to update rows in multiple partitions in the wrong order, or inconsistently across transactions, deadlocks can occur.

1. 5. Triggers on Parent Table

1. Triggers defined on the partitioned parent may fire on insert/update, even though data lands in a child table. If the trigger queries other partitions, it can create unexpected locks and deadlocks.

1. 
   

Thanks & Regards

Dinesh 

---

From: Ron Johnson <ronljohnsonjr@xxxxxxxxx>
Sent: Monday, July 28, 2025 7:58 PM
To: pgsql-admin <pgsql-admin@xxxxxxxxxxxxxx>
Subject: Why many more deadlocks after upgrade to PG 17.5?

 

Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments.

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.

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!