15.4 Performing Transactions from Within Programs
15.4.1 Problem
You're writing a
program that needs to implement transactional operations.
15.4.2 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.
15.4.3 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 with SQL.
This approach hides the details and provides better portability to
other databases that support transactions but for which the
underlying SQL syntax may differ. The Perl, Python, and Java MySQL
interfaces provide such an abstraction. PHP does not; you must issue
the SQL statements yourself.
The next few sections each implement the same example to illustrate
how to perform program-based transactions. They use a table
t that has the following initial contents that
show how much money two people have:
+------+------+
| name | amt |
+------+------+
| Eve | 10 |
| Ida | 0 |
+------+------+
The sample transaction is a simple financial transfer that uses two
UPDATE statements to give six dollars of
Eve's money to Ida:
UPDATE money SET amt = amt - 6 WHERE name = 'Eve';
UPDATE money SET amt = amt + 6 WHERE name = 'Ida';
The result is a table that looks like this:
+------+------+
| name | amt |
+------+------+
| Eve | 4 |
| Ida | 6 |
+------+------+
It's necessary to execute both statements within a
transaction to ensure that both of them take effect at once. Without
a transaction, Eve's money disappears without being
credited to Ida if the second statement fails. By using a
transaction, the table will be left unchanged if statement failure
occurs.
The example programs for each language are located in the
transactions directory of the
recipes distribution. If you compare them,
you'll see that they all employ a similar framework
for performing transactional processing:
The statements of the transaction are grouped within a control
structure, along with a commit operation.
If the status of the control structure indicates that it did not
execute successfully to completion, the transaction is rolled back.
That logic can be expressed as follows, where
block represents the control structure used to
group statements:
block:
statement 1
statement 2
...
statement n
commit
if the block failed:
roll back
In Perl, the control structure is an
eval block that succeeds or fails and returns an
error code. Python and Java use a try block that
executes to the end if the transaction was successful. If an error
occurs, an exception is raised that triggers execution of a
corresponding error-handling block to roll back the transaction. PHP
does not have these constructs, but you can achieve the same effect
by executing the statements of the transaction and a commit operation
within a function. If the function fails, roll back.
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 want to deal with that,
issue the rollback within another block that has an empty exception
handler. The example programs for Perl, Python, and Java do this.
For APIs that provide a
transaction abstraction, you can see how the interface maps onto the
underlying SQL by enabling logging in your MySQL server and then
watching the query log to see what statements the API executes when
you run a transactional program.
|
| 
No comments:
Post a Comment