Chapter 17. Performing Transactions
Introduction
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
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
SELECT
query and ...
Get MySQL Cookbook, 3rd 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.