Recipe 15.3. Performing Transactions from Within Programs

Problem

You're writing a program that needs to implement transactional operations.

Solution

Use the transaction abstraction provided by your language API, if it has such a thing. If it doesn't, use the API's usual statement execution mechanism to issue the transactional SQL statements directly using the usual API database calls.

Discussion

When you issue statements interactively with the mysql program (as in the examples shown in the previous recipe), you can see by inspection whether statements succeed or fail and determine on that basis whether to commit or roll back. From within a non-interactive SQL script stored in a file, that doesn't work so well. You cannot commit or roll back conditionally according to statement success or failure, because MySQL includes no IF/THEN/ELSE construct for controlling the flow of the script. (There is an IF⁠(⁠ ⁠ ⁠) function, but that's not the same thing.) For this reason, it's most common to perform transactional processing from within a program, because you can use your API language to detect errors and take appropriate action. This recipe discusses some general background on how to do this. The next recipes provide language-specific details for the MySQL APIs for Perl, Ruby, PHP, Python, and Java.

Every MySQL API supports transactions, even if only in the sense that you can explicitly issue transaction-related SQL statements such as START trANSACTION and COMMIT. However, some APIs also provide a transaction abstraction that enables you to control transactional behavior without working directly with SQL. This approach hides the details and provides better portability to other database engines that have different underlying transaction SQL syntax. An API abstraction is available for each of the languages that we use in this book.

The next few recipes each implement the same example to illustrate how to perform program-based transactions. They use a table t containing the following initial rows that show how much money two people have:

+------+------+ | name | amt  | +------+------+ | Eve  |   10 | | Ida  |    0 | +------+------+ 

The sample transaction is a simple financial transfer that uses two UPDATE statements to give six dollars of Eve's money to Ida:

UPDATE money SET amt = amt - 6 WHERE name = 'Eve'; UPDATE money SET amt = amt + 6 WHERE name = 'Ida'; 

The intended result is that the table should look like this:

+------+------+ | name | amt  | +------+------+ | Eve  |    4 | | Ida  |    6 | +------+------+ 

It's necessary to execute both statements within a transaction to ensure that both of them take effect at once. Without a transaction, Eve's money disappears without being credited to Ida if the second statement fails. By using a transaction, the table will be left unchanged if statement failure occurs.

The sample programs for each language are located in the transactions directory of the recipes distribution. If you compare them, you'll see that they all employ a similar framework for performing transactional processing:

• The statements of the transaction are grouped within a control structure, along with a commit operation.

• If the status of the control structure indicates that it did not execute successfully to completion, the transaction is rolled back.

That logic can be expressed as follows, where block represents the control structure used to group statements:

block:   statement 1   statement 2   ...   statement n   commit if the block failed:   roll back 

If the statements in the block succeed, you reach the end of the block and commit them. Otherwise, occurrence of an error raises an exception that triggers execution of the error-handling code where you roll back the transaction.

The benefit of structuring your code as just described is that it minimizes the number of tests needed to determine whether to roll back. The alternativechecking the result of each statement within the transaction and rolling back on individual statement errorsquickly turns your code into an unreadable mess.

A subtle point to be aware of when rolling back within languages that raise exceptions is that it may be possible for the rollback itself to fail, causing another exception to be raised. If you don't deal with that, your program itself may terminate. To handle this, issue the rollback within another block that has an empty exception handler. The sample programs do this as necessary.

Those sample programs that disable auto-commit mode explicitly when performing a transaction take care to enable auto-commit afterward. In applications that perform all database processing in transactional fashion, it's unnecessary to do this. Just disable auto-commit mode once after you connect to the database server, and leave it off.

Checking How API Transaction Abstractions Map onto SQL Statements

For APIs that provide a transaction abstraction, you can see how the interface maps onto the underlying SQL statements: enable the general query log for your MySQL server and then watch the logfile to see what statements the API executes when you run a transactional program. (See the MySQL Reference Manual for instructions on enabling the log.)