Shantanu's Blog

How do I know which connection is holding on to the lock?

Identifying what user and thread are holding on to a meta data lock that is preventing other queries from running is not easily traceable. In some cases, a "stale" transaction holds a row lock.

mysql> select trx_mysql_thread_id, trx_started from information_schema.innodb_trx where trx_rows_locked > 0 and trx_query IS NULL order by trx_started;
+---------------------+---------------------+
| trx_mysql_thread_id | trx_started |
+---------------------+---------------------+
| 1 | 2012-06-15 13:48:20 |
| 5 | 2012-06-15 13:58:26 |
+---------------------+---------------------+

mysql> select now() - trx_started as active_period, trx_mysql_thread_id, trx_started from information_schema.innodb_trx where trx_rows_locked > 0 and trx_query IS NULL and (now() - trx_started > 600) order by trx_started;
+---------------+---------------------+---------------------+
| active_period | trx_mysql_thread_id | trx_started |
+---------------+---------------------+---------------------+
| 5689.000000 | 1 | 2012-06-15 13:48:20 |
| 4683.000000 | 5 | 2012-06-15 13:58:26 |
+---------------+---------------------+---------------------+