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 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:
- Concurrency issues
If two clients want to schedule pilots, it’s possible that both of them would run the initial ...
Get MySQL Cookbook, 2nd 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.