Error
Error Code:
1637
MySQL Error 1637: Transaction Limit Exceeded
Description
This error indicates that the MySQL server has reached its configured limit for simultaneously active transactions. It typically occurs under heavy database load or when existing transactions are not being committed or rolled back efficiently, exhausting available transaction slots.
Error Message
Too many active concurrent transactions
Known Causes
4 known causesHigh Application Load
A sudden surge in user requests or application activity can lead to a large number of transactions being initiated concurrently.
Stalled or Long-Running Transactions
Transactions that remain open for extended periods without committing or rolling back can exhaust available transaction slots, preventing new ones from starting.
Server Configuration Limits
The MySQL server's configured limits for concurrent connections or InnoDB thread concurrency might be set too low for the current workload.
Resource Contention
High contention for database resources, including deadlocks, can cause transactions to stall and accumulate, consuming available slots.
Solutions
4 solutions available1. Investigate and Optimize Long-Running Transactions medium
Identify and terminate or optimize slow transactions that are holding up resources.
1
Connect to your MySQL server as a user with sufficient privileges (e.g., root).
2
Query the `information_schema.innodb_trx` table to find active transactions. Look for transactions with a long `trx_started` time or large `trx_rows_read` / `trx_rows_written` values.
SELECT * FROM information_schema.innodb_trx WHERE trx_state != 'COMMITTED' ORDER BY trx_started ASC;3
If a specific transaction is identified as problematic and needs to be terminated (use with extreme caution, as this will roll back any changes made by that transaction), use the `ROLLBACK` command with the transaction ID.
ROLLBACK <transaction_id>;4
Analyze the queries associated with these long-running transactions. Optimize them by adding appropriate indexes, rewriting inefficient logic, or breaking them into smaller, manageable chunks.
2. Increase `innodb_open_files` and `open_files_limit` easy
Allow MySQL to open more files, which can indirectly affect transaction handling by providing more resources.
1
Edit your MySQL configuration file (`my.cnf` or `my.ini`). The location varies by operating system and installation method.
2
Under the `[mysqld]` section, increase the `innodb_open_files` setting. A common starting point is `innodb_open_files = 4000` or higher, depending on your system's capabilities. Ensure this value is not excessively high, as it consumes memory.
[mysqld]
innodb_open_files = 40003
On Linux systems, you also need to increase the system's open file limit. This is typically done by editing `/etc/security/limits.conf` or using `ulimit` commands. For example, add these lines to `limits.conf`:
* soft nofile 65536
* hard nofile 655364
Restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql3. Tune `innodb_thread_concurrency` medium
Limit the number of threads that can execute concurrently within InnoDB, preventing excessive contention.
1
Connect to your MySQL server.
2
Check the current value of `innodb_thread_concurrency`.
SHOW VARIABLES LIKE 'innodb_thread_concurrency';3
If the value is set to 0 (meaning no limit, or it's automatically determined), you might consider setting it to a reasonable value. A common recommendation is to set it to the number of CPU cores available to MySQL. For example, if you have 8 CPU cores:
SET GLOBAL innodb_thread_concurrency = 8;4
To make this change permanent, add `innodb_thread_concurrency = 8` (replace 8 with your desired value) to the `[mysqld]` section of your MySQL configuration file (`my.cnf` or `my.ini`) and restart the MySQL server.
[mysqld]
innodb_thread_concurrency = 84. Review and Adjust `max_connections` easy
Ensure that `max_connections` is set appropriately to avoid exhausting connection resources.
1
Connect to your MySQL server.
2
Check the current value of `max_connections`.
SHOW VARIABLES LIKE 'max_connections';3
If the current value is too low for your application's needs, you can increase it temporarily. Be mindful that each connection consumes memory.
SET GLOBAL max_connections = 200; -- Adjust value as needed4
To make this change permanent, add `max_connections = 200` (replace 200 with your desired value) to the `[mysqld]` section of your MySQL configuration file (`my.cnf` or `my.ini`) and restart the MySQL server.
[mysqld]
max_connections = 200