Conflict with Resource Queue memory_limit and statement_mem results in "ERROR: deadlock detected, locking against self"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Running any database query results in a deadlock error message as shown below. The error occurs due to a resource queue memory limit:

flightdata=# select nt.nspname as table_schema,
         a.attname as column_name,
         a.attnum as ordinal_position,
flightdata-#          c.relname as table_name,
flightdata-#          a.attname as column_name,
flightdata-#          a.attnum as ordinal_position,
flightdata-#          format_type(a.atttypid, a.atttypmod) as data_type,
flightdata-#          c.relkind = 'r' AS is_updatable,
flightdata-#          a.atttypid in (23, 20) and a.atthasdef and
flightdata-#              (select position ( 'nextval(' in pg_catalog.pg_get_expr(adbin,adrelid) ) > 0 and
flightdata(#  position ( '::regclass)' in pg_catalog.pg_get_expr(adbin,adrelid) ) > 0
flightdata(#               FROM pg_catalog.pg_attrdef d
flightdata(#               WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as has_sequence
flightdata-#           from pg_catalog.pg_class c join pg_catalog.pg_namespace nt on (c.relnamespace = nt.oid)
flightdata-#              join pg_attribute a on (a.attrelid = c.oid)
flightdata-#          where c.relname = 'xxxxxx' and nt.nspname = 'public'
flightdata-#          and a.attnum > 0 and a.attisdropped = 'f'
flightdata-#          order by a.attnum;
ERROR: deadlock detected, locking against self
Note- The error is also reported in the master log as shown in the display below:

2015-07-12 01:21:50.236455 BST,"xxxxx","xxxxx",p9151,th-572782192,"169.182.13.8","60628",2015-07-12 01:21:50 BST,221957699,con128,cmd4,seg-1,,dx187,x221957699,sx1,"ERROR","40P01","deadlock detected, locking against self",,,,,,"select nt.nspname as table_schema,
         c.relname as table_name,
         a.attname as column_name,
         a.attnum as ordinal_position,
         format_type(a.atttypid, a.atttypmod) as data_type,
         c.relkind = 'r' AS is_updatable,
         a.atttypid in (23, 20) and a.atthasdef and
             (select position ( 'nextval(' in pg_catalog.pg_get_expr(adbin,adrelid) ) > 0 and
                          position ( '::regclass)' in pg_catalog.pg_get_expr(adbin,adrelid) ) > 0
              FROM pg_catalog.pg_attrdef d
              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as has_sequence
          from pg_catalog.pg_class c join pg_catalog.pg_namespace nt on (c.relnamespace = nt.oid)
             join pg_attribute a on (a.attrelid = c.oid)
         where c.relname = 'xxxxxxxxx' and nt.nspname = 'public'
         and a.attnum > 0 and a.attisdropped = 'f'
         order by a.attnum ",0,,"proc.c",1616,

Environment

Cause

As indicated by the error there is a conflicting issue within the user itself and here the issue is related to resource queue memory limit and the statement_mem parameter value.

For example, the resource queue memory limit of this user was set to the following:

RQname   | ActiveStatment | MaxCost | MinCost | CostOvercommit | MemoryLimit | Priority | RQAssignedUsers
----------+----------------+---------+---------+----------------+-------------+----------+---------------
xx        | 1              | -1      | 5000    | 0              | 512MB       | medium   |               1

The statement_mem for the session was set to the following:

flightdata=# show statement_mem;
 statement_mem
---------------
 1GB
(1 row)

Note- The query has 1 GB reserved to run, but the resource queue stops the query from running because the user is not allowed to consume more than 512MB.

 

Resolution

• Decrease the statement_mem so that it is less than or equal to the memory limit in the resource queue to avoid the conflict:

  set statement_mem=<lower-value>

• This issue will be addressed by redesigned Resource Group in GPDB 5.x.