GBase 8c Lock Wait Troubleshooting: From Symptoms to Blocking Source Identification

When a query hangs in GBase 8c, the cause is often not a slow execution plan but a lock wait. This guide uses pg_stat_activity, pg_locks, and global transaction IDs to trace lock chains across CNs and DNs in a gbase database. It also covers safe session termination and parameter tuning.

1. Is It a Lock Wait? Start with Session State

The pg_stat_activity view reveals whether a session is waiting for a lock. Pay special attention to the waiting column and idle in transaction state.

SELECT pid, usename, application_name, client_addr,
       xact_start, query_start, state_change,
       waiting, state, query
FROM pg_stat_activity
ORDER BY xact_start NULLS LAST, query_start NULLS LAST;

Filter by table name if known:

SELECT pid, usename, state, waiting,
       xact_start, query_start, query
FROM pg_stat_activity
WHERE query LIKE '%orders_fact%'
ORDER BY query_start;

Key indicators:

• idle in transaction sessions hold locks but appear inactive — a common silent blocker.
• waiting = true confirmed by lock views means a lock wait is in progress.

2. Find Lock Holders by Table Name

Join pg_locks with pg_class and pg_namespace to identify who holds and who waits on a specific table.

SELECT a.pid, a.usename, a.state, a.waiting,
       a.xact_start, a.query_start,
       l.mode, l.granted,
       n.nspname, c.relname,
       a.query
FROM pg_namespace n
JOIN pg_class c ON c.relnamespace = n.oid
JOIN pg_locks l ON l.relation = c.oid
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE n.nspname = 'sales'
  AND c.relname = 'orders_fact'
ORDER BY l.granted DESC, a.xact_start;

3. Map the Blocking Chain

Self‑join pg_locks to reveal exactly who is blocking whom, along with the table involved.

SELECT w.query   AS waiting_query,
       w.pid     AS waiting_pid,
       w.usename AS waiting_user,
       l.query   AS locking_query,
       l.pid     AS locking_pid,
       l.usename AS locking_user,
       t.schemaname || '.' || t.relname AS table_name
FROM pg_stat_activity w
JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted
JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted
JOIN pg_stat_activity l ON l2.pid = l.pid
JOIN pg_stat_user_tables t ON l1.relation = t.relid
WHERE w.waiting;

4. Distributed Tracing Across CNs and DNs

In a gbase database cluster, locks can originate from other CNs or DNs. For stored procedures or distributed DML, follow the global_sessionid downstream.

On the CN:

SELECT pid, usename, query_start, state, query
FROM pg_stat_activity
WHERE query LIKE '%call proc_merge_orders%'
ORDER BY query_start DESC;

Get the global transaction ID:

SELECT pid, sessionid, global_sessionid, mode, granted, locktag
FROM pg_locks
WHERE pid = 140512348217104;

On the DN:

SELECT pid, sessionid, global_sessionid, mode, granted, locktag
FROM pg_locks
WHERE global_sessionid LIKE '%818%';

SELECT pid, usename, state, waiting, xact_start, query_start, query
FROM pg_stat_activity
WHERE pid = 140512314560272;

5. Timeout Parameters: Diagnostic Aids, Not Cures

Session‑level adjustments:

SET deadlock_timeout = '500ms';
SET lockwait_timeout = '60s';
SET statement_timeout = '10min';
SET log_lock_waits = ON;

6. Safe Session Termination

Prefer pg_cancel_backend to cancel the current query. Use pg_terminate_backend only when the session can be safely interrupted and its resources must be released immediately.

-- Cancel the active query
SELECT pg_cancel_backend(140512348217104);

-- Terminate the session if needed
SELECT pg_terminate_backend(140512348217104);

Always verify afterward that the lock has been released.

7. Recommended Troubleshooting Sequence

1. Inspect sessions – pg_stat_activity for lock waits and idle in transaction.
2. Inspect objects – join pg_locks with table names to find holders.
3. Map blocking chains – identify the root blocker, not just the victims.
4. Trace across nodes – use global_sessionid to follow locks to DNs.
5. Act – adjust parameters for diagnostics, or safely terminate the root blocker.

The most common mistake is killing the waiting session while leaving the blocking source untouched. Combining proper lock diagnosis with disciplined transaction boundaries and workload isolation keeps your gbase database performing predictably.