Performing Transactions - MySQL - page 571

block:
statement 1
statement 2
...
statement n
commit
if the block failed:
roll back
If the statements in the block succeed, you reach the end of the block and perform a
commit. Otherwise, occurrence of an error raises an exception that triggers execution
of the error-handling code where you roll back the transaction.
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 alternative—checking the result
of each statement within the transaction and rolling back on individual statement errors
—quickly 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 deal with that, your program itself may terminate. To handle this,
execute the rollback within another block that has an empty exception handler. The
sample programs do this as necessary.
Those sample programs that disable auto-commit mode explicitly when performing a
transaction enable auto-commit afterward. In applications that perform all database
processing in transactional fashion, it's unnecessary to do this. Just disable auto-commit
mode once after you connect to the database server, and leave it off.
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 statements: enable the general query log for your MySQL
server, then watch the log to see what statements the API executes when you run a
transactional program. For instructions on enabling the log, see Recipe 22.3 .
17.4. Using Transactions in Perl Programs
Problem
You want to perform a transaction in a Perl DBI script.
Solution
Use the standard DBI transaction support mechanism.