Investigating Hung Queries
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
FOR UPDATE statement:
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.