O'Reilly logo

MySQL Troubleshooting by Sveta Smirnova

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required