Skip to content
DBAExperts
MySQL Diagnostics

How to see and resolve locks in MySQL

When queries hang, it is almost always because one transaction is waiting on a lock that another one holds. MySQL 8.0 lets you see who is blocking whom without guessing, using processlist, the InnoDB status, and the data locks views.

  1. 1

    See which queries are running or waiting

    The State column and a high Time give away the stuck queries. This is your first look at the problem.

    SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS consulta
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;
  2. 2

    Identify who is blocking whom

    The performance_schema data_lock_waits view shows the waiting transaction and the one blocking it, without parsing raw text.

    SELECT waiting_pid, blocking_pid, 
       waiting_query, blocking_query
FROM sys.innodb_lock_waits;
  3. 3

    Check the last detected deadlock

    SHOW ENGINE INNODB STATUS includes a LATEST DETECTED DEADLOCK section with the two transactions and the conflicting keys.

    SHOW ENGINE INNODB STATUS\G
  4. 4

    Kill the blocking transaction only if necessary

    KILL ends the connection and rolls back its transaction. It is a destructive action: the uncommitted work of that session is lost. Use it as a last resort and on the right connection.

    KILL 1234;   -- 1234 es el id de la conexión bloqueadora

// common mistake

Killing the wrong connection can interrupt a legitimate process and trigger a long rollback that makes everything worse. Confirm the id against processlist before running KILL, and attack the cause (long transactions, missing indexes) not just the symptom.

// when it's worth an expert

Recurring deadlocks are usually a transaction-design or indexing problem, not bad luck. At dba.mx we analyze the locking pattern and adjust the access model, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.