MySQL Cookbook

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

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