Transaction management and autocommit - Amazon Aurora MySQL Database Administrator’s Handbook

Transaction management and autocommit

With autocommit enabled, each SQL statement runs within its own transaction. When the statement ends, the transaction ends as well. Between statements, the client connection is not in transaction. If you need a transaction to remain open for more than one statement, you explicitly begin the transaction, run the statements, and then commit or roll back the transaction.

With autocommit disabled, the connection is always in transaction. You can commit or roll back the current transaction, at which point the server immediately opens a new one.

Refer to the MySQL Reference Manual for details.

Running with autocommit disabled is not recommended because it encourages long-running transactions where they’re not needed. Open transactions block a server’s internal garbage collection mechanisms, which are essential to maintaining optimal performance. In extreme cases, garbage collection backlog leads to excessive storage consumption, elevated CPU utilization, and query slowness.

Recommendations:

  • Always run with autocommit mode enabled. Set the autocommit parameter to 1 on the database side (which is the default) and on the application side (which might not be the default).

  • Always double-check the autocommit settings on the application side. For example, Python drivers such as MySQLdb and PyMySQL disable autocommit by default.

  • Manage transactions explicitly by using BEGIN/START TRANSACTION and COMMIT/ROLLBACK statements. You should start transactions when you need them and commit as soon as the transactional work is done.

Note that these recommendations are not specific to Aurora MySQL. They apply to MySQL and other databases that use the InnoDB storage engine.

Long transactions and garbage collection backlog are easy to monitor:

  • You can obtain the metadata of currently running transactions from the INFORMATION_SCHEMA.INNODB_TRX table. The TRX_STARTED column contains the transaction start time, and you can use it to calculate transaction age. A transaction is worth investigating if it has been running for several minutes or more. Refer to the MySQL Reference Manual for details about the table.

  • You can read the size of the garbage collection backlog from the InnoDB’s trx_rseg_history_len counter in the INFORMATION_SCHEMA.INNODB_METRICS table. Refer to the MySQL Reference Manual for details about the table. The larger the counter value is, the more severe the impact might be in terms of query performance, CPU usage, and storage consumption. Values in the range of tens of thousands indicate that the garbage collection is somewhat delayed. Values in the range of millions or tens of millions might be dangerous and should be investigated.

Note

In Amazon Aurora, all DB instances use the same storage volume, which means that the garbage collection is cluster-wide and not specific to each instance. Consequently, a runaway transaction on one instance can impact all instances. Therefore, you should monitor long transactions on all DB instances.