MySQL Cookbook, 2nd Edition

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