Chapter 15. Performing Transactions
Introduction
The
MySQL server can service 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 statements,
it’s very possible that successive queries received
from a given client will be interleaved with queries 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 you have a flight
table
containing information about airline flight schedules and you want to
update the record 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:
Concurrency issues.
The MySQL server can handle multiple clients at the same time. ...
Get MySQL Cookbook 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.