Skip to content

Instantly share code, notes, and snippets.

@morgo
Created March 31, 2020 17:28
Show Gist options
  • Save morgo/0c67674590b66c10d974039273233a8c to your computer and use it in GitHub Desktop.
Save morgo/0c67674590b66c10d974039273233a8c to your computer and use it in GitHub Desktop.
Performance Schema: summarize row locks of oldest 100 transactions
SELECT
thr.processlist_id AS mysql_thread_id,
FORMAT_PICO_TIME(trx.timer_wait) as trx_duration,
COUNT(case when lock_status='GRANTED' then 1 else null end) AS row_locks_held,
COUNT(case when lock_status='PENDING' then 1 else null end) AS row_locks_pending,
GROUP_CONCAT(DISTINCT CONCAT(object_schema, '.', object_name)) AS tables_with_locks
FROM
performance_schema.events_transactions_current trx
INNER JOIN performance_schema.threads thr USING (thread_id)
LEFT JOIN data_locks USING (thread_id)
GROUP BY thread_id, timer_wait
ORDER BY TIMER_WAIT DESC LIMIT 100;
..
+-----------------+--------------+----------------+-------------------+-------------------+
| mysql_thread_id | trx_duration | row_locks_held | row_locks_pending | tables_with_locks |
+-----------------+--------------+----------------+-------------------+-------------------+
| 8 | 33.57 min | 3 | 0 | test.t0 |
| NULL | 184.07 us | 0 | 0 | NULL |
| NULL | 5.63 us | 0 | 0 | NULL |
+-----------------+--------------+----------------+-------------------+-------------------+
3 rows in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment