Investigating Hung Queries

Is the Query Blocked?

What if you have a query that is seemingly hung and you have no reason to question its optimization level - How do you determine if another transaction is blocking your query? There is very useful information in the SYSTEM tables for this. First, take a look at Examples of Selecting Data for Update After a Concurrent Transaction Updates Data . This is a good example of how one transaction can block another. Say we have Client A start a transaction performing updates that blocks another transaction in Client B with a SELECT… FOR UPDATE statement:

Client A starts transaction

USE TEST;
CREATE TABLE t1(f1 INTEGER);
INSERT INTO t1 VALUES (1),(3),(5);
COMMIT;
SET AUTOCOMMIT OFF;
START TRANSACTION ISOLATION LEVEL CONSISTENT READ;
SELECT * FROM t1;

 F1
 ---

  1
  3
  5

Client B views data in table t1

USE TEST;
SET AUTOCOMMIT OFF;
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM t1;

 F1
 ---

  1
  3
  5

Client A transaction updates data in table t1

UPDATE t1 SET f1 = 42 WHERE f1=5;
SELECT * FROM t1;

 F1
 ---

  1
  3
 42

Client B now hangs because of the updates done by Client A.

SELECT * FROM t1;

 F1
 ---

  1
  3
  5

SELECT f1 FROM t1 WHERE f1=5 FOR UPDATE;
# Hangs!

Let’s do a query on SYSTEM.CONNECTIONS to find out more about this query that is hanging.

SET OUTPUT VERTICAL;
SELECT sqlstring,transid FROM system.connections;
...
==================================== Row #3 ====================================
SQLSTRING: select f1 from t1 where f1=5 for update;
TRANSID: 1055748
...

Now we have the transaction id, we can find out if it is being blocked. In the example below, we see the entry twice, one for each Transaction Engine (TE) and we have two TEs in this particular database.

SELECT nodeid,blockedby FROM system.transactions WHERE id=1055748;
==================================== Row #1 ====================================
NODEID: 4
BLOCKEDBY: 49282
==================================== Row #2 ====================================
NODEID: 4
BLOCKEDBY: 49282

Let’s find out about the transaction running with ID 49282:

SELECT * FROM system.connections WHERE transid=49282;
==================================== Row #1 ====================================
SQLSTRING:
COUNT: 0
RUNTIME: 0
USER: CLIENTA
SCHEMA: TEST
NUMPARAM: 0
PARAMS:
CONNID: 10
OPEN: 2
HANDLE: -1
OPENRESULTS: 0
NODEID: 2
EXECID: 0
TRANSID: 49282
TRANSRUNTIME: 4808382195
AUTOCOMMITFLAGS: 0
ISOLATIONLEVEL: 7

This gives us a few clues. We know that the user is CLIENTA. There’s no SQL string which means it is an open transaction that is not currently running anything, but otherwise there would be a SQL string that would also be a clue as to which client is blocking this transaction. We also know it’s connected to the TE with node ID 2.