Troubleshooting scenarios

• 1: Find long-running SQL
• 2: Investigate lock waits

1 - Find long-running SQL

Captured on 2026-09-27 (UTC+08) on a local KingbaseES V008R006C009B0014 primary, database test, using ~/kbdiag built from Go source commit 93bf65d (static linux/amd64 binary). This is lab evidence, not production validation. PIDs, counts and timings belong to this capture only. A fault-injection script started select pg_sleep(3600);, a statement that only sleeps. It tests detection, not CPU or disk pressure.

kbdiag 2.0 has no slow-SQL threshold yet: a long statement is listed and sorted, but not a WARN on its own. It becomes a finding when it holds a transaction open too long (txn) or makes others wait for a lock (locks).

1. What is running?

~/kbdiag sessions
echo EXIT_CODE=$?

sessions  OK  (KingbaseES V008R006C009B0014, primary, system@local, 2026-09-27T13:36:49+08:00)

connected: 4 client sessions (not counting kbdiag), 1 walsender, 7 background
  count  user    database  application            client
  2      esrep   esrep     internal_rwcmgr        192.168.105.10
  1      esrep   esrep     internal_rwcmgr        192.168.105.11
  1      system  test      kbdiag_inj_long_query  local

not idle: 1
  pid      user    database  application            client  state   xact  query  wait             sql
  1049328  system  test      kbdiag_inj_long_query  local   active  13s   13s    Timeout:PgSleep  select pg_sleep(3600);
EXIT_CODE=0

• connected counts the client sessions by user, database, application and client; walsenders and background processes are only counted (--all lists them).
• not idle lists the sessions doing something or holding a transaction open, longest transaction first. 1049328 has run select pg_sleep(3600); for 13 seconds. query is how long the current statement has been running; wait is what it waits on.
• The sql column is cut at 60 characters; session <pid> shows the full text.

2. What is it waiting on?

~/kbdiag session 1049328
echo EXIT_CODE=$?

session  OK  (KingbaseES V008R006C009B0014, primary, system@local, 2026-09-27T13:36:50+08:00)

session 1049328
  user         system
  database     test
  application  kbdiag_inj_long_query
  client       local
  type         client backend
  state        active  (for 14s)
  xact         14s
  query        14s
  wait         Timeout:PgSleep
  xid / xmin   - / 6353

sql
  select pg_sleep(3600);

waiting for: 0

blocking: 0

holds: 0
EXIT_CODE=0

Timeout:PgSleep says the statement is sleeping on purpose. It waits for nobody, blocks nobody and holds no lock worth showing (every transaction’s own virtualxid lock is left out). For a real statement you would see IO (reading data), Lock (waiting for another session), or no wait event at all (running on CPU).

3. Is it one statement or many?

~/kbdiag waits
echo EXIT_CODE=$?

waits  OK  (KingbaseES V008R006C009B0014, primary, system@local, 2026-09-27T13:36:51+08:00)

not idle: 1
  wait             state   sessions  pids
  Timeout:PgSleep  active  1         1049328

not shown: 3 idle, 8 background
EXIT_CODE=0

waits groups the sessions that are doing something by wait event and state. One Timeout:PgSleep session means a single statement, not a pile-up; the idle sessions and idle background processes are only counted. When dozens of sessions share one wait event, that event is where to look.

4. Decide

Here the sleep is deliberate; there is nothing to tune. For a real statement, check its plan and the data it touches with the application owner. kbdiag does not cancel statements; if you decide to, use select pg_cancel_backend(<pid>); in ksql. The injected session was released afterwards and a follow-up check found none left.

Continue with lock waits

2 - Investigate lock waits

Captured on 2026-09-27 (UTC+08) on a local KingbaseES V008R006C009B0014 primary, database test, using ~/kbdiag built from Go source commit 93bf65d (static linux/amd64 binary). This is lab evidence, not production validation. PIDs, counts and timings belong to this capture only. A fault-injection script opened a transaction that took an ACCESS EXCLUSIVE lock on a test table and then sat idle, and a second session tried to read the table.

1. Who is waiting?

~/kbdiag locks
echo EXIT_CODE=$?

locks  WARN  (KingbaseES V008R006C009B0014, primary, system@local, 2026-09-27T13:36:33+08:00)

[WARN] lock.waiting  session 1048829 has waited 13s for AccessShareLock on public.kbdiag_inj_lock, blocked by 1048819
  verify: kbdiag session 1048819  # what the blocking session is doing

blockers: 1
  pid      blocks  holds
  1048819  1       public.kbdiag_inj_lock AccessExclusiveLock

waiting: 1
  pid      object                  wants            waited  blocked by
  1048829  public.kbdiag_inj_lock  AccessShareLock  13s     1048819
EXIT_CODE=1

• Session 1048829 has waited 13 seconds for AccessShareLock on public.kbdiag_inj_lock; the direct blocker is 1048819. Waits over 10 seconds are a WARN (--lock-wait-warn changes this).
• blockers comes first, because the first question is who is in the way. It shows 1048819 blocks one session and holds AccessExclusiveLock on the table. When one blocker holds up dozens of sessions, it is the top row.
• waiting lists each waiting session, longest wait first. The wait is measured from the waiter’s last state change, so it can overstate the wait a little, never understate it.

2. What is the blocker doing?

Follow the verify line:

~/kbdiag session 1048819
echo EXIT_CODE=$?

session  WARN  (KingbaseES V008R006C009B0014, primary, system@local, 2026-09-27T13:36:34+08:00)

[WARN] lock.waiting  session 1048829 has waited 14s for AccessShareLock on public.kbdiag_inj_lock, blocked by 1048819

session 1048819
  user         system
  database     test
  application  kbdiag_inj_lock_holder
  client       local
  type         client backend
  state        idle in transaction  (for 14s)
  xact         14s
  query        14s
  wait         -
  xid / xmin   6351 / -

last sql
  lock table kbdiag_inj_lock in access exclusive mode;

waiting for: 0

blocking: 1
  pid      object                  wants            waited
  1048829  public.kbdiag_inj_lock  AccessShareLock  14s

holds: 1
  object                  mode
  public.kbdiag_inj_lock  AccessExclusiveLock
EXIT_CODE=1

• The blocker is idle in transaction: its last statement was lock table kbdiag_inj_lock in access exclusive mode; and it has been waiting for the client for 14 seconds with the transaction still open. Nothing is running; the lock is held because nobody committed or rolled back.
• blocking shows who it holds up; holds shows the lock they want.
• If waiting for is not empty, the blocker is itself waiting: run session on its blocker in turn until you reach one that is not waiting. kbdiag shows one hop at a time.
• If a blocker is shown as 2PC, it is a prepared (two-phase) transaction with no session; find it with kbdiag txn on the primary.

3. Decide and act

kbdiag does not end sessions. Confirm with the application owner what the transaction was for. Usually the application should commit or roll back; if it cannot, end the connection yourself, for example with select pg_terminate_backend(1048819); in ksql, after checking the PID still belongs to the same user and application. Ending the blocker rolls back its transaction.

In this lab run the injection script released the holder, which ended the transaction.

4. Confirm

~/kbdiag locks
echo EXIT_CODE=$?

locks  OK  (KingbaseES V008R006C009B0014, primary, system@local, 2026-09-27T13:36:36+08:00)

waiting: 0
EXIT_CODE=0

No waits, OK, exit 0. One clean sample only says the waits are gone now; if they come back, look for the application path that leaves transactions open.

Back to scenarios · locks reference