Database Reference
In-Depth Information
CHAPTER 17
Performing Transactions
17.0. Introduction
The MySQL server can handle multiple clients at the same time because it is multi‐
threaded. 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 executes
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 a
flight
table contains 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:
SET
@
p_val
=
(
SELECT
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 an available pilot, the second marks the pilot as unavailable,
and the third assigns the pilot to the flight. That's straightforward enough in principle,
but in practice there are significant difficulties:
Concurrency issues
If two clients want to schedule pilots, it's possible for both to run the initial
SE
LECT
query and retrieve the same pilot ID number before either has a chance to set
the pilot's status to unavailable. If that happens, the same pilot is scheduled for two
flights at once.
Integrity issues
All three statements must execute successfully as a unit. For example, if the
SE
LECT
and the first
UPDATE
run successfully, but the second
UPDATE
fails, the pilot's
