MySQL Cookbook, 2nd Edition

Performing Transactions Using SQL

Problem

You need to issue a set of statements that must succeed or fail as a unit—that is, you need to perform a transaction.

Solution

Manipulate MySQL’s auto-commit mode to enable multiple-statement transactions, and then commit or roll back the statements depending on whether they succeed or fail.

Discussion

This recipe describes the SQL statements that control transactional behavior in MySQL. The immediately following recipes discuss how to perform transactions from within programs. Some APIs require that you implement transactions by issuing the SQL statements discussed in this recipe; others provide a special mechanism that enables transaction management without writing SQL directly. However, even in the latter case, the API mechanism will map program operations onto transactional SQL statements, so reading this recipe will give you a better understanding of what the API is doing on your behalf.

MySQL normally operates in auto-commit mode, which commits the effect of each statement immediately as soon as it executes. (In effect, each statement is its own transaction.) To perform a transaction, you must disable auto-commit mode, issue the statements that make up the transaction, and then either commit or roll back your changes. In MySQL, you can do this two ways:

• Issue a START TRANSACTION (or BEGIN) statement to suspend auto-commit mode, and then issue the statements that make up the transaction. If the statements succeed, record their effect in the ...