Performing Transactions from Within Programs

15.4.1 Problem

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

15.4.2 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 query execution mechanism to issue the transactional SQL statements directly.

15.4.3 Discussion

When you run queries interactively from mysql (as in the examples shown in the previous section), 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 section discusses some general background on how to do this. The next sections provide language-specific details for the Perl, PHP, Python, and Java APIs.

Every API supports transactions, even if only in the sense that you can explicitly issue transaction-related SQL statements such as BEGIN and COMMIT. However, some APIs also provide a transaction abstraction that allows you to control transactional behavior without working directly with SQL. This approach hides the details and provides better portability to other databases that support transactions but for which the underlying SQL syntax may differ. The Perl, Python, and Java MySQL interfaces provide such an abstraction. PHP does not; you must issue the SQL statements yourself.

The next few sections each implement the same example to illustrate how to perform program-based transactions. They use a table t that has the following initial contents 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 result is a table that looks 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 example 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

In Perl, the control structure is an eval block that succeeds or fails and returns an error code. Python and Java use a try block that executes to the end if the transaction was successful. If an error occurs, an exception is raised that triggers execution of a corresponding error-handling block to roll back the transaction. PHP does not have these constructs, but you can achieve the same effect by executing the statements of the transaction and a commit operation within a function. If the function fails, roll back.

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 want to deal with that, issue the rollback within another block that has an empty exception handler. The example programs for Perl, Python, and Java do this.

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 by enabling logging in your MySQL server and then watching the query log to see what statements the API executes when you run a transactional program.