Choosing Between statement_timeout and pg_cancel_backend for Safe Query Cancellation in Distributed YugabyteDB Transactions

Direct answer

For YugabyteDB 2.12+ distributed YSQL transactions, pg_cancel_backend is the more reliable choice for prompt, operator-initiated abort propagation to tablet leaders. statement_timeout is an automatic wall-clock safeguard scoped to one statement, but it measures client-to-coordinator and coordinator-to-tablet network time, so geo-distributed latency can trigger false-positive cancellations. There is no supported session setting that makes pg_cancel_backend automatic and superuser-free; cancellation remains tied to superuser or backend ownership.

Confirmed facts

• statement_timeout is a YSQL session GUC. The timer starts when the coordinator receives the statement and counts wall-clock time, including network round trips.
• pg_cancel_backend(pid) sends a cancellation signal to a backend. In YugabyteDB 2.12+, that signal is intended to propagate from the transaction coordinator to participating tablet leaders, which is the path for releasing distributed locks.
• pg_cancel_backend requires the caller to be superuser or the owner of the target backend under the PostgreSQL-compatible privilege model YugabyteDB follows.
• No built-in GUC converts pg_cancel_backend into an automatic per-statement cancel without superuser rights.

Likely explanation and uncertainty

Statement timeouts work well as a first defense against runaway queries, but in multi-region clusters the timer can expire while remote tablets are still executing, producing a coordinator-initiated abort and a brief window where locks remain until cleanup or garbage collection observes the abort. Manual cancellation lets an operator abort after confirming actual progress. Exact 2.12+ propagation timings and whether pre-2.12 behavior differed materially should be confirmed against your YugabyteDB release notes, because this draft relies on model knowledge rather than live verification.

Practical steps for this case

1. Confirm the YugabyteDB and YSQL version on every master and tserver.
2. Use statement_timeout for automatic, non-superuser protection:

SET statement_timeout = '60s';
ALTER ROLE app_user SET statement_timeout = '60s';

1. Use a privileged role for operator-initiated cancellation:

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb'
  AND state = 'active'
  AND query_start < now() - interval '2 minutes';

1. Verify lock release afterward:

SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

Network latency and false positives

statement_timeout includes the time spent waiting for client-to-coordinator responses and coordinator-to-tablet RPCs. In multi-region deployments with jitter, elapsed time can exceed the limit even while execution progresses normally, increasing false-positive cancellations. Mitigate by raising the timeout for cross-region queries or using SET LOCAL statement_timeout for known long-running workloads.

Missing diagnostic detail

Can you create a dedicated cancellation role and grant it signaling privileges, or is superuser access strictly prohibited? That determines whether a semi-automated watchdog using pg_cancel_backend is feasible in your environment.

Conservative draft: verify exact cancellation propagation and privilege behavior in YugabyteDB 2.12+ release notes before relying on it in production.