Skip to content

Instantly share code, notes, and snippets.

@morgo morgo/pfs.txt
Created Mar 31, 2020

Embed
What would you like to do?
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
You can’t perform that action at this time.