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!
2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE waiting PostgreSQL JDBC Driver 00000 LOG: 00000: process 2829641 detected deadlock while waiting for ShareLock on transaction 257268332 after 1000.063 ms 2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE waiting PostgreSQL JDBC Driver 00000 DETAIL: Process holding the lock: 2831377. Wait queue: 2829642, 2833054, 2833080. 2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE waiting PostgreSQL JDBC Driver 00000 CONTEXT: while rechecking updated tuple (6,2) in relation "snoggle_default_partition_y2025w29" 2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE waiting PostgreSQL JDBC Driver 00000 LOCATION: ProcSleep, proc.c:1565 2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE waiting PostgreSQL JDBC Driver 00000 STATEMENT: UPDATE snoggle CI SET LINKED_CHECK_ID = C.CHECK_ID FROM snoggle CI1 INNER JOIN CHK C ON CI1.BATCH_IDENTIFIER='0105515580_0056216634_07252025' AND CI1.PROCESS_DATE='07/25/2025' AND CI1.PARTITION_TYPE=1 INNER JOIN BATCH B ON B.BATCH_IDENTIFIER='0105515580_0056216634_07252025' AND B.PROCESS_DATE='07/25/2025' AND B.PARTITION_TYPE=1 WHERE C.BATCH_IDENTIFIER='0105515580_0056216634_07252025' AND C.PROCESS_DATE='07/25/2025' AND C.PARTITION_TYPE=1 AND C.BATCH_FILE_TYPE_CODE_ID in (2, 5, 15) AND CI.PROCESS_DATE='07/25/2025' AND CI.PARTITION_TYPE=1 AND CI1.PROCESS_DATE='07/25/2025' AND CI1.PARTITION_TYPE=1 AND CI1.ITEM_IDENTIFIER=C.ITEM_IDENTIFIER AND CI1.BATCH_ID=B.BATCH_ID AND B.PROCESS_DATE='07/25/2025' AND B.PARTITION_TYPE=1 AND B.BATCH_FILE_TYPE_CODE_ID in (1, 4, 14) AND B.ITEM_STATE>0 2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE PostgreSQL JDBC Driver 40P01 ERROR: 40P01: deadlock detected 2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE PostgreSQL JDBC Driver 40P01 DETAIL: Process 2829641 waits for ShareLock on transaction 257268332; blocked by process 2831377. Process 2831377 waits for ShareLock on transaction 257266199; blocked by process 2829641. Process 2829641: UPDATE snoggle CI SET LINKED_CHECK_ID = C.CHECK_ID FROM snoggle CI1 INNER JOIN CHK C ON CI1.BATCH_IDENTIFIER='0105515580_0056216634_07252025' AND CI1.PROCESS_DATE='07/25/2025' AND CI1.PARTITION_TYPE=1 INNER JOIN BATCH B ON B.BATCH_IDENTIFIER='0105515580_0056216634_07252025' AND B.PROCESS_DATE='07/25/2025' AND B.PARTITION_TYPE=1 WHERE C.BATCH_IDENTIFIER='0105515580_0056216634_07252025' AND C.PROCESS_DATE='07/25/2025' AND C.PARTITION_TYPE=1 AND C.BATCH_FILE_TYPE_CODE_ID in (2, 5, 15) AND CI.PROCESS_DATE='07/25/2025' AND CI.PARTITION_TYPE=1 AND CI1.PROCESS_DATE='07/25/2025' AND CI1.PARTITION_TYPE=1 AND CI1.ITEM_IDENTIFIER=C.ITEM_IDENTIFIER AND CI1.BATCH_ID=B.BATCH_ID AND B.PROCESS_DATE='07/25/2025' AND B.PARTITION_TYPE=1 AND B.BATCH_FILE_TYPE_CODE_ID in (1, 4, 14) AND B.ITEM_STATE>0 Process 2831377: UPDATE snoggle CI SET LINKED_CHECK_ID = C.CHECK_ID FROM snoggle CI1 INNER JOIN CHK C ON CI1.BATCH_IDENTIFIER='0105541014_0056429013_07252025' AND CI1.PROCESS_DATE='07/25/2025' AND CI1.PARTITION_TYPE=1 INNER JOIN BATCH B ON B.BATCH_IDENTIFIER='0105541014_0056429013_07252025' AND B.PROCESS_DATE='07/25/2025' AND B.PARTITION_TYPE=1 WHERE C.BATCH_IDENTIFIER='0105541014_0056429013_07252025' AND C.PROCESS_DATE='07/25/2025' AND C.PARTITION_TYPE=1 AND C.BATCH_FILE_TYPE_CODE_ID in (2, 5, 15) AND CI.PROCESS_DATE='07/25/2025' AND CI.PARTITION_TYPE=1 AND CI1.PROCESS_DATE='07/25/2025' AND CI1.PARTITION_TYPE=1 AND CI1.ITEM_IDENTIFIER=C.ITEM_IDENTIFIER AND CI1.BATCH_ID=B.BATCH_ID AND B.PROCESS_DATE='07/25/2025' AND B.PARTITION_TYPE=1 AND B.BATCH_FILE_TYPE_CODE_ID in (1, 4, 14) AND B.ITEM_STATE>0 2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE PostgreSQL JDBC Driver 40P01 HINT: See server log for query details. 2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE PostgreSQL JDBC Driver 40P01 CONTEXT: while rechecking updated tuple (6,2) in relation "snoggle_default_partition_y2025w29" 2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE PostgreSQL JDBC Driver 40P01 LOCATION: DeadLockReport, deadlock.c:1135 2025-07-25 14:02:50.659 EDT 10.55.96.150 blarge blatt 2829641 UPDATE PostgreSQL JDBC Driver 40P01 STATEMENT: UPDATE snoggle CI SET LINKED_CHECK_ID = C.CHECK_ID FROM snoggle CI1 INNER JOIN CHK C ON CI1.BATCH_IDENTIFIER='0105515580_0056216634_07252025' AND CI1.PROCESS_DATE='07/25/2025' AND CI1.PARTITION_TYPE=1 INNER JOIN BATCH B ON B.BATCH_IDENTIFIER='0105515580_0056216634_07252025' AND B.PROCESS_DATE='07/25/2025' AND B.PARTITION_TYPE=1 WHERE C.BATCH_IDENTIFIER='0105515580_0056216634_07252025' AND C.PROCESS_DATE='07/25/2025' AND C.PARTITION_TYPE=1 AND C.BATCH_FILE_TYPE_CODE_ID in (2, 5, 15) AND CI.PROCESS_DATE='07/25/2025' AND CI.PARTITION_TYPE=1 AND CI1.PROCESS_DATE='07/25/2025' AND CI1.PARTITION_TYPE=1 AND CI1.ITEM_IDENTIFIER=C.ITEM_IDENTIFIER AND CI1.BATCH_ID=B.BATCH_ID AND B.PROCESS_DATE='07/25/2025' AND B.PARTITION_TYPE=1 AND B.BATCH_FILE_TYPE_CODE_ID in (1, 4, 14) AND B.ITEM_STATE>0
