Transactions
MySQL supports transactions at the storage engine level. The most popular among the officially supported storage engines, InnoDB, provides transaction support. In this section, we discuss how to troubleshoot InnoDB transactions.
In MySQL, you can start a transaction using a START TRANSACTION or BEGIN statement. To commit a transaction, call
COMMIT, and to roll it back (cancel
it), call ROLLBACK.
An alternate way to start a multistatement transaction is to
set the variable autocommit to zero.
This will override the default MySQL behavior, which sends an implicit
commit after each statement. With autocommit set to 0, you need to call COMMIT or ROLLBACK explicitly. After that, the next
statement begins a new transaction automatically.
MySQL also provides SAVEPOINT and
XA transaction interfaces. Although
InnoDB supports both, I will not
describe them in this book, because it would not offer any extra insight
related to our troubleshooting techniques. In other words, the same
techniques I describe can be applied to such transactions.
Hidden Queries
InnoDB treats every request for data as a transaction. It does not matter whether the transaction is a single statement or if it is multiple statements. With regard to troubleshooting, you can handle single-query transactions as described in Row Locks. You need to find out which queries run concurrently and which locks get in the way of each other.
Things change when you have transactions that consist of multiple statements. In this ...
Get MySQL Troubleshooting now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
