Troubleshooting Table Locks in GBase 8s: Find and Release Exclusive Locks Fast

Exclusive locks (X locks) are a common cause of stalled transactions, SQL timeouts, and blocked writes in GBase 8s. When a session holds an X lock on a table, no other session can insert, update, or delete rows. Here's a step‑by‑step workflow to identify and resolve the blockage.

1. Symptoms

• DML operations hang and eventually time out.
• The monitoring layer shows heavy lock contention on a particular table.
• One or more sessions hold unreleased X locks, blocking business tables from being written to.

2. Locate the X‑Lock Session via System Table

The syslocks table records every lock in the database. Query it to find who is holding an exclusive lock on a given table.

SELECT
    s.owner,              -- session ID holding the lock
    s.hex(rowidlk) rowid  -- row ID locked
FROM syslocks s
WHERE dbsname = 'car3gdb'    -- database name
  AND tabname = 'prpcmain'   -- table name
  AND type = 'X';            -- X = exclusive lock

• owner is the key piece needed to release the lock.
• If the query returns nothing, the blocking is caused by something other than an X lock.

3. Bulk Lock Diagnosis with onstat (Preferred)

onstat provides real‑time lock visibility and can automatically chain into session details. The single command below filters on a table name, extracts the session IDs, and writes a full report to a timestamped log file.

onstat -k | grep e0136b | grep 'HDR+X' | awk '{print "onstat -u|grep "$3}' | sh | awk '{print "onstat -g ses "$3}' | sh >> LK-$(date +%m%d%H%M).txt

What each part does:

1. onstat -k — lists all locks.
2. grep <table> | grep 'HDR+X' — narrows to exclusive locks on the target table.
3. Pipes to onstat -u and onstat -g ses — retrieves session ID, client IP, current SQL, connection time, and lock wait time.

Open the resulting LK-<timestamp>.txt file to see the full picture.

4. Releasing the Blocking Session

Once you have the session ID, kill it using one of the two methods below.

Option 1: Command line

onmode -z 331543

Option 2: SQL function

EXECUTE FUNCTION sysadmin:task('onmode','z','331543');

After the command succeeds, business operations resume and the lock disappears from the lock list.

5. Important Considerations

• Verify that the session does not belong to a critical job or a legitimate long transaction before killing it.
• Frequent X locks usually indicate long uncommitted transactions, missing indexes causing full‑table locks, or concurrent updates to the same row.
• Monitor with onstat -k, onstat -g sql, and onstat -g ses in real time.
• Keep the lock logs for later SQL tuning and root‑cause analysis.

Lock‑Troubleshooting Workflow

1. Find the lock — query syslocks.
2. Profile the session — use the onstat one‑liner.
3. Release the lock — onmode -z the session.

This three‑step approach resolves most table‑blocking incidents quickly in a gbase database, helping you restore normal operations with minimal downtime.