This content originally appeared on DEV Community and was authored by Michael
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 transactionsessions hold locks but appear inactive — a common silent blocker. -
waiting = trueconfirmed 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
| Parameter | Purpose | Recommendation |
|---|---|---|
deadlock_timeout |
Deadlock detection start (default 1s) | Lower temporarily for diagnostics |
lockwait_timeout |
Max wait per lock (default 20min) | Use as a safety boundary for business |
statement_timeout |
Max total execution time | Don't use as a lock‑governance substitute |
log_lock_waits |
Log lock waits | Enable during troubleshooting |
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
-
Inspect sessions –
pg_stat_activityfor lock waits andidle in transaction. -
Inspect objects – join
pg_lockswith table names to find holders. - Map blocking chains – identify the root blocker, not just the victims.
-
Trace across nodes – use
global_sessionidto follow locks to DNs. - 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.
This content originally appeared on DEV Community and was authored by Michael
Michael | Sciencx (2026-06-11T10:06:19+00:00) GBase 8c Lock Wait Troubleshooting: From Symptoms to Blocking Source Identification. Retrieved from https://www.scien.cx/2026/06/11/gbase-8c-lock-wait-troubleshooting-from-symptoms-to-blocking-source-identification/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.