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 ...
Get MySQL Cookbook, 2nd Edition 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.