Como posso mostrar bloqueios do mysql?

Veja o link de Marko para tabelas InnoDB e as advertências.

Para o MyISAM, não há uma solução fácil de "esta é a consulta ofensiva". Você deve sempre começar com uma lista de processos. Mas certifique-se de incluir a palavra-chave completa para que as consultas impressas não sejam truncadas:

SHOW FULL PROCESSLIST;

Isso mostrará uma lista de todos os processos atuais, sua consulta SQL e estado. Agora, geralmente, se uma única consulta está causando o bloqueio de muitas outras, deve ser fácil identificar. As consultas afetadas terão um status de Lockede a consulta incorreta ficará parada sozinha, possivelmente esperando por algo intenso, como uma tabela temporária.

Se não for óbvio, você terá que usar seus poderes de dedução SQL para determinar qual parte do SQL ofensivo pode ser a causa de seus problemas.

Se você usa o InnoDB e precisa verificar as consultas em execução, recomendo

show engine innodb status;

como mencionado no link de Marko. Isso lhe dará a consulta de bloqueio, quantas linhas/tabelas estão bloqueadas por ela etc. Procure em TRANSAÇÕES.

O problema com o uso SHOW PROCESSLISTé que você não verá os bloqueios, a menos que outras consultas estejam na fila.

Experimente SHOW OPEN TABLES:

show open tables where In_Use > 0 ;

Nenhuma das respostas pode mostrar todos os bloqueios atualmente mantidos.

Faça isso, por exemplo, no mysql em um terminal.

start transaction;
update someTable set name="foobar" where ID=1234;
-- but no rollback or commit - just let it sit there

Claramente a transação acima mantém um bloqueio, porque a transação ainda está ativa. Mas nenhuma consulta está acontecendo agora e ninguém está esperando por um bloqueio em qualquer lugar (pelo menos ainda).

INFORMATION_SCHEMA.INNODB_LOCKSestá vazio, o que faz sentido dada a documentação , porque há apenas uma transação e atualmente ninguém esperando por nenhum bloqueio. Também INNODB_LOCKSestá obsoleto de qualquer maneira.

SHOW ENGINE INNODB STATUSé inútil: someTablenão é mencionado em tudo

SHOW FULL PROCESSLISTestá vazio, porque o culpado não está realmente executando uma consulta agora.

Você pode usar INFORMATION_SCHEMA.INNODB_TRXe extrair as consultas que quaisquer transações ativas executaram no passado, conforme descrito em performance_schema.events_statements_historyminha outra resposta , mas não encontrei nenhuma maneira de ver que está bloqueado no cenário acima.performance_schema.threadssomeTable

As sugestões nas outras respostas até agora não ajudarão pelo menos.

Isenção de responsabilidade: não tenho o innotop instalado e não me incomodei. Talvez isso pudesse funcionar.

Referência retirada deste post.

Você pode usar o script abaixo:

SELECT 
    pl.id
    ,pl.user
    ,pl.state
    ,it.trx_id 
    ,it.trx_mysql_thread_id 
    ,it.trx_query AS query
    ,it.trx_id AS blocking_trx_id
    ,it.trx_mysql_thread_id AS blocking_thread
    ,it.trx_query AS blocking_query
FROM information_schema.processlist AS pl 
INNER JOIN information_schema.innodb_trx AS it
    ON pl.id = it.trx_mysql_thread_id
INNER JOIN information_schema.innodb_lock_waits AS ilw
    ON it.trx_id = ilw.requesting_trx_id 
        AND it.trx_id = ilw.blocking_trx_id

Usando este comando

SHOW PROCESSLIST

mostrará todos os processos atualmente em execução, incluindo o processo que adquiriu bloqueio nas tabelas.

AFAIK ainda não existe uma forma nativa no MYSQL, mas eu uso innotop . É gratuito e tem muitas outras funcionalidades também.

Veja também este link para mais informações sobre como usar a ferramenta innotop.