Error
Error Code:
1637
MariaDB Error 1637: Too Many Active Transactions
Description
This error indicates that the MariaDB server has reached its configured limit for simultaneously active transactions. It typically occurs under high load or when transactions are held open for too long, preventing new transactions from starting.
Error Message
Too many active concurrent transactions
Known Causes
4 known causesExcessive Transaction Load
Many client applications are simultaneously initiating and maintaining active transactions on the database.
Prolonged Transaction Durations
Individual transactions are taking an unusually long time to complete, holding resources and consuming transaction slots.
Application Transaction Leaks
Application code fails to properly commit or roll back transactions, leaving them open indefinitely.
Insufficient Server Configuration
The MariaDB server's configuration parameters for transaction limits are set too low for the current workload.
Solutions
3 solutions available1. Identify and Terminate Long-Running Transactions medium
Find and kill transactions that are holding resources for too long.
1
Connect to your MariaDB server as a user with appropriate privileges (e.g., root).
2
Query the `information_schema.INNODB_TRX` table to identify active transactions. Look for transactions with a long `TRX_STARTED` timestamp or a high `TRX_ROWS_READ` count.
SELECT trx_id, trx_state, trx_started, trx_rows_read, trx_query FROM information_schema.INNODB_TRX WHERE trx_state = 'ACTIVE';3
If you identify a specific transaction that is problematic and can be safely terminated, use the `KILL` command with the `trx_id` obtained from the previous step.
KILL <trx_id>;4
Monitor the server after terminating transactions to ensure the error is resolved and no critical operations were affected.
2. Tune InnoDB Transaction Management Parameters advanced
Adjust MariaDB configuration to allow for more concurrent transactions.
1
Locate your MariaDB configuration file (e.g., `my.cnf`, `mariadb.conf.d/50-server.cnf`).
2
Increase the `innodb_open_files` parameter. This controls the maximum number of files that InnoDB can open. A higher value can accommodate more transactions. Start with a modest increase and monitor.
[mysqld]
innodb_open_files = 4000 # Example: Increase from default (usually 300)3
Increase the `innodb_buffer_pool_instances` parameter. This can improve concurrency by allowing InnoDB to manage its buffer pool more effectively. The optimal number is often related to the number of CPU cores.
[mysqld]
innodb_buffer_pool_instances = 8 # Example: If you have 8 CPU cores4
Consider increasing `innodb_log_file_size` and `innodb_log_files_in_group` if you have very high write loads, as this can impact transaction commit performance. This is a more involved change and requires a server restart.
[mysqld]
innodb_log_file_size = 1024M
innodb_log_files_in_group = 25
After making changes, restart the MariaDB server for the new settings to take effect.
sudo systemctl restart mariadb6
Monitor server performance and transaction counts after the restart to assess the impact of the changes.
3. Optimize Application Transaction Logic medium
Refactor application code to reduce transaction duration and contention.
1
Analyze application code that performs database operations. Identify areas where long-running transactions might be occurring.
2
Break down large transactions into smaller, more manageable units. Commit frequently to release locks and resources.
3
Avoid performing non-database operations (e.g., external API calls, complex calculations) within a database transaction. Move these operations outside the transaction scope.
4
Ensure that your application is properly handling database connections and not leaving them open unnecessarily.
5
Implement optimistic locking or other concurrency control mechanisms in your application to reduce the need for explicit database locks that can prolong transactions.
6
Test your application thoroughly after making code changes to ensure performance and correctness.