"ERROR","40P01","deadlock detected","Process 87475 waits for ExclusiveLock on resource queue 19834; blocked by process 87143.

Products

• VMware Tanzu Data Suite
• Greenplum
• VMware Tanzu Greenplum

Issue/Introduction

You might notice similar log lines in pg_log 

 

2025-03-02 07:28:50.706678 UTC,"username","dbprd",p91683,th233576576,"10.110.151.101","52563",2025-03-02 07:28:49 UTC,0,con1883301,cmd11,seg-1,,dx2944524,,sx1,"ERROR","40P01","deadlock detected, locking against self","resource queue id: 19834, portal id: 1",,,,,"

2025-03-02 12:59:09.115349 UTC,"username","dbprd",p87475,th233576576,"10.110.139.34","37458",2025-03-02 12:58:48 UTC,0,con1901500,cmd3,seg-1,,dx2951820,,sx1,"ERROR","40P01","deadlock detected","Process 87475 waits for ExclusiveLock on resource queue 19834; blocked by process 87143.

2025-03-02 13:00:44.636725 UTC,"username","dbprd",p102370,th233576576,"10.110.139.53","45550",2025-03-02 13:00:22 UTC,0,con1901646,cmd3,seg-1,,dx2951884,,sx1,"ERROR","40P01","deadlock detected","Process 102370 waits for ExclusiveLock on resource queue 19834; blocked by process 102408.

                        Message    : deadlock detected
2025-03-02 15:31:33.330116 UTC,"username","dbprd",p61196,th233576576,"10.110.139.253","60074",2025-03-02 15:30:22 UTC,32450714,con1910052,cmd6,seg-1,,dx2961000,x32450714,sx1,"ERROR","40P01","deadlock detected","Process 61196 waits for ExclusiveLock on resource queue 19849; blocked by process 50266.

2025-03-02 17:06:13.205804 UTC,"username","dbprd",p147428,th233576576,"10.110.139.240","36640",2025-03-02 14:41:48 UTC,32449526,con1906769,cmd425,seg-1,,dx2957519,x32449527,sx2,"NOTICE","00000","ERROR PURGING PARTITIONS.  Error code raised 40P01. 

Environment

All Greenplum Versions.

Resolution

This is a design limitation of Resource Queues and due to trivial nature of the enhancement to uplift the limiation its not in the roadmap to be lifted.

Workaround is to have more than 1 concurrency in the resource queue the queries are getting deadlock errors.