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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.