When I queried information_schema.innodb_trx (introduced in 5.1
with the InnoDB Plugin) I noticed there were a few transactions
in LOCK WAIT state.
Example:
mysql [information_schema] > select trx_id,trx_state
-> from information_schema.innodb_trx;
+--------+-----------+
| trx_id | trx_state |
+--------+-----------+
| 7974 | LOCK WAIT |
| 7962 | RUNNING |
+--------+-----------+
2 rows in set (0.00 sec)
Then I made a query to join a few information_schema and
performance_schema tables to find out which query was blocking my
transactions. It turned out that the blocking transaction had a
trx_query=NONE. So my query was block by a transaction doing
nothing. That's not really helpful.
Let's try to recreate the situation and see exactly what
happened. I'll use two sessions for the transactions and a third
to monitor the server.
…