MySQL Cookbook, 4th Edition

Chapter 20. Performing Transactions

20.0 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 one another, 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 ...