The MySQL server can handle multiple clients at the same time
because it is multithreaded. To deal with contention among clients, the
server performs any necessary locking so that two clients cannot modify
the same data at once. However, as the server executes SQL statements,
it’s very possible that successive statements received from a given
client will be interleaved with statements from other clients. If a
client issues multiple statements that are dependent on each other, the
fact that other clients may be updating tables in between those
statements can cause difficulties. Statement failures can be problematic, too, if a multiple-statement
operation does not run to completion. Suppose that you have a flight
table containing information about
airline flight schedules and you want to update the row for Flight 578
by choosing a pilot from among those available. You might do so using
three statements as follows:
SELECT @p_val := pilot_id FROM pilot WHERE available = 'yes' LIMIT 1; UPDATE pilot SET available = 'no' WHERE pilot_id = @p_val; UPDATE flight SET pilot_id = @p_val WHERE flight_id = 578;
The first statement chooses one of the available pilots, the second marks the pilot as unavailable, and the third assigns the pilot to the flight. That’s straightforward enough in practice, but in principle there are a couple of significant difficulties with the process:
If two clients want to schedule pilots, it’s possible that both of them
would run the initial SELECT
query and retrieve the same pilot ID number before either of them
has a chance to set the pilot’s status to unavailable. If that
happens, the same pilot would be scheduled for two flights at
once.
All three statements must execute successfully as a unit. For example, if
the SELECT
and the first
UPDATE
run successfully, but
the second UPDATE
fails, the
pilot’s status is set to unavailable without the pilot being
assigned a flight. The database will be left in an inconsistent
state.
To prevent concurrency and integrity problems in these types of situations, transactions are helpful. A transaction groups a set of statements and guarantees the following properties:
No other client can update the data used in the transaction while the transaction is in progress; it’s as though you have the server all to yourself. For example, other clients cannot modify the pilot or flight records while you’re booking a pilot for a flight. By preventing other clients from interfering with the operations you’re performing, transactions solve concurrency problems arising from the multiple-client nature of the MySQL server. In effect, transactions serialize access to a shared resource across multiple-statement operations.
Statements in a transaction are grouped and are committed
(take effect) as a unit, but only if they all succeed. If an error
occurs, any actions that occurred prior to the error are rolled
back, leaving the relevant tables unaffected as though none of the
statements had been issued at all. This keeps the database from
becoming inconsistent. For example, if an update to the flights
table fails, rollback causes the
change to the pilots
table to be
undone, leaving the pilot still available. Rollback frees you from
having to figure out how to undo a partially completed operation
yourself.
This chapter shows the syntax for the SQL statements that begin
and end transactions. It also describes how to implement transactional
operations from within programs, using error detection to determine
whether to commit or roll back. The final recipe discusses some
workarounds that you can use to simulate transactions in applications
that use nontransactional storage engines. Sometimes it’s sufficient to
lock your tables across multiple statements using
LOCK
TABLE
and UNLOCK
TABLE
. This prevents other clients from
interfering, although there is no rollback if any of the statements
fail. Another alternative may be to rewrite statements so that they
don’t require transactions.
Scripts related to the examples shown here are located in the
transactions directory of the
recipes
distribution.
18.227.26.217