Fun MySQL fact of the day: leveraging
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
SELECTthat does not explicitly lock rows in
autocommitmode is considered an
AC-NL-ROtransaction (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
SELECTs to reduce internal overhead and cut-down on the number of statements you need to send (i.e.
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
COMMITand what could happen in-between (latency, garbage collection, application crashes, etc.). If we can eliminate all this time between the
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.