Blocked Transactions

Symptom

Certain operations, such as updating data, may become blocked by another transaction, and the application may appear to experience significant performance degradation. This can result in a significant increase in the application’s response time, and in some cases, queries may time out.

Cause

Performance degradation due to long running transactions should be identified and fixed to prevent future occurrences. This issue may be caused by blocked transactions. A blocked transaction occurs when one transaction is waiting to access a resource that is locked by another transaction. This can happen naturally as part of normal database operations, such as when two transactions simultaneously attempt to update the same row in a table, or explicitly when using SQL statements such as LOCK TABLE and SELECT FOR UPDATE. The waiting transaction cannot proceed until the lock is released.

Transient blocking is normal, but if a transaction and the associated lock it holds run for an extended period, blocking chains can develop and appears to the client application as poor performance. The reason for such long running queries must be identified and fixed to prevent future occurences.

A blocked transaction is different from a deadlock. A deadlock occurs when two or more transactions are waiting for each other to release locks, preventing any of them from proceeding. For an example of a deadlock in the NuoDB Node.js driver, see Retrying a Deadlock Exception.

Solution

Identify blocked transactions and resolve them to restore normal application operation.

Use the ACTIVETRANSACTIONS system table to identify blocked transactions.

Blocking can occur in a chain with multiple lead blockers. The output of this query can be used to identify the lead blocker(s).

SELECT
    ID,
    STATE,
    NODEID,
    STARTID,
    BLOCKEDBY,
    STARTEVENT,
    ENDEVENT
FROM
    SYSTEM.ACTIVETRANSACTIONS
WHERE
    BLOCKEDBY<>-1;

ID          STATE   NODEID  STARTID BLOCKEDBY   STARTEVENT  ENDEVENT
17044866    Active    21      21     12960002     88483        NULL

Terminate the lead blocker.

To terminate the blocked statement or the blocking statement without closing the transaction, use:
```
KILL STATEMENT <execid>;
```
KILL STATEMENT only cancels the SQL statement and not the transaction. Since the transaction remains open, it may continue to hold locks and block other sessions. To release the block, the client must handle errors by rolling back and ending the transaction.

To terminate the connection if the lead blocker is not currently executing a SQL statement, use:
```
KILL CONNECTION CONNID <connid>;
```
To get the connection ID of the blocking transaction, use the CONNECTIONS system table. For example:
```
SELECT
    CONNID,EXECID,TRANSID
FROM
    SYSTEM.CONNECTIONS
WHERE
    TRANSID=102345;
```
For more information, see KILL STATEMENT and KILL CONNECTION.

Example

The following example uses the ACTIVETRANSACTIONS and CONNECTIONS system tables to retrieve detailed information about a blocked query. The ACTIVETRANSACTIONS system table may not capture information about the blocking transactions that are not currently executing.

SELECT DISTINCT
       t_blocked.ID AS TRANSACTIONID,
       c_blocked.CONNID,
       c_blocked.USER,
       c_blocked.CLIENTINFO,
       c_blocked.AUTOCOMMITFLAGS,
       c_blocked.TRANSRUNTIME,
       c_blocked.RUNTIME,
       t_blocked.STATE,
       t_blocked.NODEID,
       t_blocked.SOURCENODE,
       t_blocked.ISOLATIONLEVEL,
       t_blocked.BLOCKEDBY,
       IFNULL(
           NULLIF(c_blocked.SQLSTRING, ''),
           'NO STATEMENT EXECUTING'
       ) "SQL STATEMENT",
       c_blocked.EXECID
FROM SYSTEM.ACTIVETRANSACTIONS t_blocked
LEFT JOIN SYSTEM.CONNECTIONS c_blocked
       ON c_blocked.TRANSID = t_blocked.ID
WHERE t_blocked.BLOCKEDBY > 0
   OR EXISTS (
          SELECT 1
          FROM SYSTEM.ACTIVETRANSACTIONS t_blocking
          WHERE t_blocked.ID = t_blocking.BLOCKEDBY
      );

TRANSACTIONID	CONNID	USER            CLIENTINFO	        AUTOCOMMITFLAGS	TRANSRUNTIME	RUNTIME         STATE	NODEID	SOURCENODE	ISOLATIONLEVEL	BLOCKEDBY	    SQLSTRING	                                    EXECID
102345          57	APP_USER	JDBC Client from 10.0.1.25	FALSE	00:02:13	00:02:13	ACTIVE	2	1	    READ_COMMITTED	102344	    UPDATE orders SET status='SHIPPED' WHERE id=5012	    98765
102346          59	REPORT_USER	JDBC Client from 10.0.1.30	TRUE	00:00:45	00:00:45	WAITING	1	1	    READ_COMMITTED	102345	    SELECT * FROM orders WHERE status='SHIPPED'	            98766
102347          60	APP_USER	JDBC Client from 10.0.1.25	FALSE	00:00:12	00:00:12	WAITING	2	1	    READ_COMMITTED	102345	    NO STATEMENT EXECUTING	                            98767