Re: Why many more deadlocks after upgrade to PG 17.5? - Mailing list pgsql-admin

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