Como verificar a existência de locks no ambiente ?
SELECT
*
FROM
information_schema.processlist
WHERE
state IN ('Waiting for table flush' , 'Locked', 'Table lock')
OR state REGEXP 'Waiting for .* lock'
ORDER BY time DESC
E como identificar as consultas ou sessões que estão realizando o lock?
SELECT TRX.TRX_MYSQL_THREAD_ID, TRX.TRX_ISOLATION_LEVEL, TRX.TRX_STARTED, TRX.TRX_STATE, PROCESSLIST.USER, PROCESSLIST.HOST, PROCESSLIST.DB, PROCESSLIST.COMMAND, PROCESSLIST.TIME, PROCESSLIST.STATE, LOCK_WAITS.REQUESTING_TRX_ID, LOCK_WAITS.BLOCKING_TRX_ID, LOCK_WAITS.BLOCKING_LOCK_ID FROM INFORMATION_SCHEMA.INNODB_LOCKS LOCKS, INFORMATION_SCHEMA.INNODB_TRX TRX, INFORMATION_SCHEMA.PROCESSLIST PROCESSLIST, INFORMATION_SCHEMA.INNODB_LOCK_WAITS LOCK_WAITS WHERE LOCKS.LOCK_TRX_ID = TRX.TRX_ID AND TRX.TRX_MYSQL_THREAD_ID = PROCESSLIST.ID;