How to Check Blocking Queries in YugabyteDB

Environment

• YugabyteDB (YSQL)

Issue

When running concurrent transactions in YugabyteDB, queries may become blocked waiting for locks held by other transactions. This can cause performance degradation and application timeouts. You need to identify which transaction is blocking others and what query is holding the lock.

Additional Information:
YugabyteDB stores lock information in pg_locks with YugabyteDB-specific details in the ybdetails JSONB column. Unlike single-node PostgreSQL, the blocking query may be running on a different tablet server (tserver) than where you're connected.

Resolution

Overview

Identifying blocking queries in YugabyteDB requires two steps:

1. Query pg_locks to find the blocking transaction and determine which tserver it's running on
2. Connect to that specific tserver and query pg_stat_activity to get the actual blocking query

Steps

1. Run the following query to identify blocking transactions:

SELECT
    blocked.ybdetails ->> 'transactionid' AS blocked_txn,
    blocked.ybdetails -> 'blocked_by' ->> 0 AS blocking_txn,
    MAX(blocking.ybdetails ->> 'node') AS blocking_node_uuid,
    MAX(servers.public_ip) AS blocking_tserver_ip,
    STRING_AGG(DISTINCT blocked.locktype, ',') AS lock_types,
    STRING_AGG(DISTINCT blocked.ybdetails -> 'keyrangedetails' ->> 'cols', ',') AS locked_keys,
    MAX(now() - blocked.waitstart) AS blocked_duration
FROM pg_locks blocked
LEFT JOIN pg_locks blocking
  ON (blocked.ybdetails -> 'blocked_by' ->> 0) = blocking.ybdetails ->> 'transactionid'
 AND blocking.granted = true
LEFT JOIN yb_servers() servers
  ON blocking.ybdetails ->> 'node' = servers.uuid
WHERE blocked.granted = false
GROUP BY blocked.ybdetails ->> 'transactionid',
         blocked.ybdetails -> 'blocked_by' ->> 0
ORDER BY blocked_duration DESC;

Example output:

 blocked_txn |             blocking_txn             |        blocking_node_uuid        | blocking_tserver_ip |     lock_types      | locked_keys | blocked_duration 
-------------+--------------------------------------+----------------------------------+---------------------+---------------------+-------------+------------------
             | 1f90fbe7-fa17-4b0d-8ee0-24356897b31a | a4c45d57bd564608b81c861bd927e593 | 127.0.0.2           | column,relation,row | ["1"]       | 00:02:47.327834

2. Note the blocking_tserver_ip and blocking_txn from the output.
3. Connect to the tserver identified in blocking_tserver_ip:

ysqlsh -h <blocking_tserver_ip>

4. Query pg_stat_activity to find the blocking query using the transaction ID:

SELECT pid, query 
FROM pg_stat_activity 
WHERE yb_backend_xid = '<blocking_txn>';

Example:

SELECT pid, query 
FROM pg_stat_activity 
WHERE yb_backend_xid = '1f90fbe7-fa17-4b0d-8ee0-24356897b31a';

Example output:

  pid  |                          query                          
-------+---------------------------------------------------------
 12362 | UPDATE test_lock SET value = 'locked row' WHERE id = 1;

5. Take appropriate action based on the blocking query identified:
   • Wait for the transaction to complete naturally
   • Ask the application owner to commit or rollback the transaction
   • Cancel the blocking transaction using the transaction ID (can be run from any node):

SELECT yb_cancel_transaction('<blocking_txn>');

• Alternatively, terminate the blocking session using pg_terminate_backend(pid) (must be run from the same tserver)

Additional Information:

• The blocked_duration column shows how long the transaction has been waiting for the lock
• The lock_types column indicates the type of locks involved (e.g., row, relation, column)
• The locked_keys column shows which key values are locked
• If blocking_tserver_ip is NULL, the blocking transaction may have already completed or the lock information is not yet propagated
• yb_cancel_transaction() is the preferred method to cancel a blocking transaction as it can be run from any node using just the transaction ID
• Use pg_terminate_backend(pid) with caution as it will abort the blocking transaction and may cause application errors

• Internal Reference Ticket: SUPPORT-876