Resolving MySQL Table Locks, Deadlocks, and Connection Issues: A Complete Troubleshooting Guide

Understanding MySQL Table Locks and Deadlocks

MySQL uses locking to ensure data consistency during concurrent operations. While InnoDB uses row-level locking by default, table-level locks can still occur, particularly during DDL operations (like ALTER TABLE), full table scans, or when explicitly requested. A deadlock happens when two or more transactions hold locks that the others need, creating a circular dependency. MySQL automatically detects deadlocks and rolls back one of the transactions (usually the smaller one), but frequent deadlocks degrade performance.

Diagnosing Table Locks and Deadlocks

When applications start timing out or you see errors like Lock wait timeout exceeded; try restarting transaction, your first step is to identify the blocking query.

1. Checking for Active Locks

You can query the information_schema to find out which transactions are waiting for locks and which transactions are holding them.

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

2. Analyzing InnoDB Status

For deep diagnostics, especially for deadlocks, the InnoDB status engine is invaluable.

SHOW ENGINE INNODB STATUS\G

Look for the LATEST DETECTED DEADLOCK section. It will show the exact queries involved, the locks they held, and the locks they were waiting for.

Troubleshooting "Too Many Connections"

The ERROR 1040 (HY000): Too many connections error means MySQL has reached the limit defined by the max_connections variable. This can happen due to a sudden spike in traffic, connection leaks in your application, or slow queries holding connections open for too long.

1. Identify the Current State

SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

2. Investigate Who is Connected

SHOW PROCESSLIST;

Look for connections in a Sleep state for extended periods. These are prime candidates for connection leaks in the application layer.

Fixing MySQL Connection and Lock Issues

Immediate Mitigation

If a specific query is blocking others and causing a massive pile-up, you may need to kill it to restore service.

-- Replace <thread_id> with the blocking_thread ID found earlier
KILL <thread_id>;

For too many connections, you can temporarily increase the limit (if you have sufficient RAM) without restarting the server.

SET GLOBAL max_connections = 500;

Long-Term Solutions

1. Index Optimization: Ensure your queries are using indexes efficiently. Full table scans often escalate row locks to table locks or take so long that they cause lock wait timeouts.
2. Transaction Scope: Keep transactions as short as possible. Do not perform external API calls or long computations while holding open a database transaction.
3. Connection Pooling: Implement connection pooling in your application (e.g., HikariCP, PgBouncer, or application-framework equivalents) to reuse connections instead of constantly opening and closing them.
4. Deadlock Retries: Since deadlocks are a normal part of concurrent database operations, your application code must be prepared to catch deadlock exceptions and retry the transaction.

Handling MySQL Crash Recovery and Corruption

If the server crashes (e.g., Out of Memory, Power Loss), InnoDB performs crash recovery automatically upon restart. However, if data files become corrupted, you might see errors like Database page corruption on disk or a failed file read.

InnoDB Recovery Modes

If MySQL refuses to start due to corruption, you can use innodb_force_recovery in your my.cnf. Warning: This is a destructive operation and should only be used to dump data.

1. Set innodb_force_recovery = 1 in my.cnf.
2. Attempt to start MySQL.
3. If it starts, immediately mysqldump your data.
4. If it fails, increment the number (up to 6) until it starts.
5. Once data is dumped, rebuild the database from scratch and import the dump.

Always ensure you have regular, verified backups to prevent data loss during catastrophic failures.