34.3 失控查询、锁与事务

SELECT pid,
       usename,
       application_name,
       state,
       wait_event_type,
       wait_event,
       clock_timestamp() - query_start AS query_age,
       clock_timestamp() - xact_start AS xact_age,
       backend_xid,
       backend_xmin,
       query_id
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST, query_start NULLS LAST;

SELECT queryid,
       calls,
       total_exec_time,
       mean_exec_time,
       rows,
       shared_blks_read,
       shared_blks_written,
       temp_blks_read,
       temp_blks_written,
       wal_bytes
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

WITH RECURSIVE lock_tree AS (
  SELECT a.pid,
         a.application_name,
         a.xact_start,
         pg_blocking_pids(a.pid) AS blockers,
         ARRAY[a.pid] AS path
  FROM pg_stat_activity AS a
  WHERE cardinality(pg_blocking_pids(a.pid)) > 0

  UNION ALL

  SELECT b.pid,
         b.application_name,
         b.xact_start,
         pg_blocking_pids(b.pid),
         t.path || b.pid
  FROM lock_tree AS t
  CROSS JOIN LATERAL unnest(t.blockers) AS p(pid)
  JOIN pg_stat_activity AS b ON b.pid = p.pid
  WHERE NOT b.pid = ANY(t.path)
)
SELECT * FROM lock_tree;

is this the root blocker or a victim?
is its client deadline already expired?
is it OLTP, migration, maintenance, backup, recovery, or batch?
what locks and objects does it own?
what rows/WAL/temp/I/O has it already produced?
does it carry a business idempotency key?
who owns the decision?

SELECT pg_cancel_backend(:pid);
SELECT pg_terminate_backend(:pid);

application cooperative cancel/deadline
  -> pg_cancel_backend exact PID
      -> wait and verify
          -> pg_terminate_backend exact PID when justified

pid + backend_start
database + user + application_name
query_id / transaction age
incident run id or ticket

SELECT pid, backend_start, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE pid = :pid;

root blocker disappeared?
dependent waiters made progress?
pool stopped recreating the work?
rollback/recovery still consuming I/O?
user success and tail latency recovered?

long but small
  idle transaction holds snapshot/locks for hours

short but large
  bulk UPDATE changes millions of rows in minutes

long and large
  migration/ETL both retains old state and creates rollback work

SELECT gid,
       prepared,
       owner,
       database,
       clock_timestamp() - prepared AS age
FROM pg_prepared_xacts
ORDER BY prepared;

identity:
  pid_backend_start: ...
  application_owner: ...
  transaction_or_job_id: ...
business:
  partial_external_effects: ...
  idempotency_or_reconciliation: ...
database:
  locks: ...
  xmin_retention: ...
  rows_wal_temp_estimate: ...
  replicas_and_archive_effect: ...
rollback:
  expected_resource_cost: ...
  observation_query: ...
  escalation_timeout: ...