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
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
ROLLBACK explicitly. After that, the next
statement begins a new transaction automatically.
MySQL also provides
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.
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 ...