GPSS job fails with deadlock detected and lock timeout errors in Greenplum

Products

• VMware Tanzu Data Intelligence
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

Greenplum Streaming Server (GPSS) jobs fail unexpectedly without auto-restarting. The failures are accompanied by deadlock detected, lock timeout, or concurrently deleted tuple errors.

Symptoms:

• GPSS jobs fail and do not trigger the configured MAX_RETRIES or AUTO_STOP_RESTART_INTERVAL mechanisms.

• The following errors are observed in the GPSS logs:

error,err exec job batch. Failed to execute batch: pq: deadlock detected  (seg#### ####:#### pid=####)+
error,"start job #### failed, Failed to execute batch: pq: canceling statement due to lock timeout"

Environment

GPSS 1.11.3

Cause

The "concurrently deleted tuple" and "lock timeout" errors are expected behaviors resulting from the Greenplum Database Multi-Version Concurrency Control (MVCC). When GPSS performs high-frequency MERGE (Upsert) operations on Append-Optimized (AO or AOCO) tables, and a VACUUM operation runs concurrently on those same tables, transient exclusive locks or row visibility conflicts occur. The database proactively throws these exceptions to protect data integrity.

Additionally, a specific bug exists in GPSS version 1.11.3. If a database lock timeout occurs at the exact time the GPSS RUNNING_DURATION timer attempts to gracefully restart the job, GPSS mishandles the error. This causes the job to fail entirely without triggering the configured MAX_RETRIES or AUTO_STOP_RESTART_INTERVAL mechanisms.

Resolution

Workaround: To resolve this immediately without upgrading, use one of the following methods:

1. Remove RUNNING_DURATION (Recommended): Remove RUNNING_DURATION and AUTO_STOP_RESTART_INTERVAL from the GPSS job YAML configurations. This allows the job to run continuously. If a lock timeout occurs, the standard MAX_RETRIES mechanism successfully catches the error and retries the job.

2. Schedule VACUUMs During Maintenance Windows: Avoid running VACUUM on target tables while GPSS is actively loading data. Schedule VACUUM operations during off-peak hours when GPSS jobs are paused to prevent lock timeouts from occurring.

 

Permanent Fix: The bug preventing the auto-restart mechanism from triggering during these specific lock timeouts has been permanently fixed in GPSS version 2.2.1 and later. Upgrading to a newer version of GPSS will ensure that if these lock timeouts occur, the job will automatically restart as configured in your YAML file.