Fun MySQL fact of the day: leveraging autocommit

By default, MySQL uses autocommit for all new connections, and, as noted yesterday, autocommit can be remarkably powerful when used carefully for two separate reasons:

• Remembering back to last Friday's Fun MySQL fact of the day, InnoDB will optimize read-only transactions to reduce accounting overhead. And, as it so happens, a SELECT that does not explicitly lock rows in autocommit mode is considered an AC-NL-RO transaction (that is, one that is auto-commit, non-locking, and read-only) and is candidate for read-only optimization. This means no transaction ID is assigned and the statement runs in a transaction that starts and ends with the statement. Unless you explicitly require a multi-statement transaction that provides isolation guarantees, consider using autocommit for SELECTs to reduce internal overhead and cut-down on the number of statements you need to send (i.e. BEGIN and COMMIT) to the server.

• Remembering back to Monday's Fun MySQL fact of the day, you may recall that InnoDB uses write locks to provide transaction isolation for writes. These locks are not released until a transaction is committed or fully rolled-back, meaning that the longer a transaction takes to complete, the more likely you are to encounter lock waits and deadlocks. Think about how much time it takes between a BEGIN and COMMIT and what could happen in-between (latency, garbage collection, application crashes, etc.). If we can eliminate all this time between the BEGIN and COMMIT by using autocommit, we can lower the amount of overhead and contention on the database rather substantially. Still, all of this may not seem like a huge deal since InnoDB only locks single records, right? But that's a fun MySQL fact for another day.

I also noted that autocommit can be annoying and dangerous. And it can be: if you don't know autocommit is on and you begin making multi-statement changes, each single statement is immediately committed, potentially leaving you with a corrupt object graph if, for any reason, the application can't finish the change set.