What causes MySQL "ERROR 1040 (HY000): Too many connections"?

This error occurs when the number of concurrent client connections exceeds the `max_connections` variable defined in your MySQL configuration. It is typically caused by application connection leaks, sudden spikes in traffic, or queries taking too long to execute (holding connections open longer than normal).

How do I find which query is causing a table lock?

You can find blocking queries by querying the `sys.innodb_lock_waits` view in MySQL 5.7 and 8.0. This view provides a human-readable summary of the blocked transaction, the blocking transaction, and the specific queries involved. You can also use `SHOW PROCESSLIST` to look for queries stuck in the "Waiting for table metadata lock" state.

What is the difference between a table lock and a deadlock?

A table lock happens when a transaction locks an entire table (often due to DDL operations or full table scans without indexes), forcing other queries to wait. A deadlock is a specific condition where two or more transactions hold row-level locks that the others need, creating a circular dependency. InnoDB automatically resolves deadlocks by killing one transaction, whereas table locks simply cause other queries to wait until they timeout.

How can I fix MySQL replication lag quickly?

To diagnose the cause, run `SHOW REPLICA STATUS\G` and check the `Seconds_Behind_Master` and the current executing query. If a specific slow query is blocking the replication thread, you may need to optimize it on the primary. For a long-term fix, enable parallel replication (`replica_parallel_workers`) and ensure all replicated tables have explicit primary keys.

What should I do if MySQL crashes due to Out of Memory (OOM)?

First, verify it was an OOM event by checking `dmesg -T | grep -i oom`. To prevent recurrence, review your `innodb_buffer_pool_size` (it should not exceed your available RAM minus OS overhead). Also, check `max_connections`, as each connection consumes memory. If memory is heavily constrained, consider adding swap space or upgrading the server's RAM.

Resolving MySQL Table Locks, Deadlocks, and "Too Many Connections" Errors

Comprehensive troubleshooting guide to fix MySQL table locks, deadlocks, "too many connections" (ERROR 1040), and optimize slow queries for production stability

Last updated: February 24, 2026

Last verified: February 24, 2026

1,333 words

Key Takeaways

Table locks and deadlocks often stem from long-running transactions or unoptimized queries blocking other operations.
"Too many connections" (ERROR 1040) usually indicates application connection leaks or sudden traffic spikes exceeding max_connections.
Diagnose active locks using SHOW ENGINE INNODB STATUS and the sys.innodb_lock_waits view.
Quick fixes include killing blocking queries (KILL <pid>) and temporarily increasing connection limits, but long-term solutions require query optimization and proper connection pooling.
Server crashes due to Out of Memory (OOM) or Disk Full can lead to database corruption, requiring careful crash recovery procedures.

Fix Approaches Compared
Method	When to Use	Time	Risk
KILL <process_id>	Immediate relief from a blocking query, unyielding table lock, or severe replication lag.	Instant	High (aborts transaction abruptly, potential application errors)
Increase max_connections	Temporary mitigation for 'too many connections' or connection refused errors.	Minutes	Medium (can lead to MySQL Out of Memory if server lacks RAM)
Implement Connection Pooling	Long-term fix for high connection churn and frequent timeout issues.	Days/Weeks	Low (improves stability, resource usage, and application throughput)
Optimize Queries & Indexes	Fixing the root cause of slow queries, deadlocks, and excessive locking.	Weeks	Low (best practice for overall database health and scalability)

Understanding the Errors

When managing MySQL in production, you will inevitably encounter a cascade of interconnected issues. A single unoptimized mysql slow query can lead to a sustained mysql table lock. As subsequent queries pile up waiting for the lock to release, your application opens more and more connections, eventually resulting in the dreaded mysql too many connections (ERROR 1040 (HY000)).

If the database server runs out of resources handling these concurrent requests, you might face a mysql out of memory crash, necessitating mysql crash recovery and potentially dealing with mysql corruption upon restart. Furthermore, heavy write locks and long transactions on the primary database can cause severe mysql replication lag on your read replicas, serving stale data to users.

Step 1: Diagnosing Table Locks and Deadlocks

A mysql deadlock occurs when two or more transactions hold locks that the others need, creating a circular dependency. InnoDB automatically detects deadlocks and rolls back one of the transactions (usually the smaller one), but frequent deadlocks severely degrade application performance.

To diagnose recent deadlocks, use the InnoDB status monitor:

SHOW ENGINE INNODB STATUS\G

Look for the LATEST DETECTED DEADLOCK section. This will show you exactly which transactions were involved, what locks they were waiting for, and which query was rolled back.

For active blocking queries (where one query is waiting on a lock held by another), you can query the sys schema:

SELECT * FROM sys.innodb_lock_waits\G

This provides a clear view of the waiting_query and the blocking_query, allowing you to identify the culprit and safely issue a KILL <process_id> if necessary to restore service.

Step 2: Fixing "Too Many Connections"

The immediate symptom of connection exhaustion is your application throwing MySQL connection refused or MySQL timeout errors.

Quick Fix: If you can still log in to the server (e.g., via the dedicated admin port or as a user with the CONNECTION_ADMIN privilege), you can dynamically increase the limit:

SET GLOBAL max_connections = 1000;

Root Cause Solutions:

Fix Connection Leaks: Ensure your application code explicitly closes database connections in finally blocks or uses context managers.
Implement Connection Pooling: Use a connection pooler like ProxySQL. This multiplexes thousands of application connections down to a few hundred actual MySQL connections, drastically reducing overhead.
Tune Timeouts: Review your wait_timeout and interactive_timeout settings. Lowering these (e.g., to 600 seconds) forces MySQL to aggressively drop idle connections.

Step 3: Addressing Out of Memory (OOM) and Disk Full Issues

If MySQL suddenly disappears and restarts, it might have been killed by the Linux OOM killer. Check /var/log/syslog or dmesg for Out of memory: Killed process.

Tune InnoDB Buffer Pool: Your innodb_buffer_pool_size should typically be 50-70% of available RAM. If you experience OOMs, you may need to reduce this value or provision a server with more memory.
Disk Full Recovery: A mysql disk full error (No space left on device) will halt all writes. Monitor your ibdata1, binary logs, and temporary tables. You can quickly free space by safely purging old binlogs:
```
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);
```

Step 4: Crash Recovery and Corruption

If a crash occurs (OOM, power failure), InnoDB automatically performs crash recovery on restart. However, if you encounter mysql corruption (e.g., Database page corruption on disk in the error log), normal startup may fail.

You may need to start MySQL with innodb_force_recovery set to a value between 1 and 6 in your my.cnf. Start at 1 and increment only if necessary. This allows MySQL to start in a read-only state so you can use mysqldump to salvage your data, drop the corrupted tablespaces, and restore from the dump.

Step 5: Mitigating Replication Lag

mysql replication lag happens when the replica cannot execute transactions as fast as the primary generates them.

Check Seconds_Behind_Master in SHOW SLAVE STATUS\G (or SHOW REPLICA STATUS\G).
Long-running queries, lack of primary keys on replicated tables, or structural changes (ALTER TABLE) on the master are common culprits.
Solution: Enable multi-threaded replication by setting slave_parallel_workers (or replica_parallel_workers) to a value > 0, and ensure all your InnoDB tables have primary keys.

Frequently Asked Questions

sql

-- 1. Check current connection limits and usage
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads_connected';

-- 2. Identify queries causing locks (MySQL 5.7+ sys schema)
SELECT 
  w.query AS waiting_query,
  w.waiting_pid,
  b.query AS blocking_query,
  b.blocking_pid
FROM sys.innodb_lock_waits w
JOIN sys.innodb_lock_waits b ON w.blocking_pid = b.blocking_pid\G

-- 3. Kill a stuck or blocking process (replace PID with blocking_pid from above)
KILL 12345;

-- 4. Safely increase max_connections dynamically (Temporary Fix)
SET GLOBAL max_connections = 2000;

-- 5. Show recent deadlocks
SHOW ENGINE INNODB STATUS\G

-- 6. Check replication lag
SHOW REPLICA STATUS\G  -- Use SHOW SLAVE STATUS\G on older versions

Error Medic Editorial

Error Medic Editorial comprises senior DevOps and Site Reliability Engineers dedicated to providing actionable, production-tested solutions for complex infrastructure and database issues.

Sources

Learn how to enable the MySQL slow query log, configure long_query_time dynamically, use mysqldumpslow, and fix database bottlenecks without restarting.

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

Fix MySQL table locks, deadlocks, and 'too many connections' errors. Learn how to diagnose slow queries, clear locks, and recover from crashes.

Cassandra 'Connection Refused' on Port 9042: Complete Troubleshooting Guide

Fix Cassandra connection refused errors on port 9042. Diagnose OOM kills, misconfigured listen_address, firewall blocks, slow queries, and data corruption with

DynamoDB Slow Query, Timeout & Table Lock: Complete Troubleshooting Guide

Fix DynamoDB slow queries, ProvisionedThroughputExceededException, and timeout errors. Step-by-step diagnosis with AWS CLI commands and proven solutions.