Section 15.0. Introduction

15.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 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 SELECT query and retrieve the same pilot ID number before either of them has a chance to set the pilot's status to unavailable. If that happens, the same pilot would be scheduled for two flights at once.

Integrity issues

All three statements must execute successfully as a unit. For example, if the SELECT and the first UPDATE run successfully, but the second UPDATE fails, the pilot's status is set to unavailable without the pilot being assigned a flight. The database will be left in an inconsistent state.

To prevent concurrency and integrity problems in these types of situations, transactions are helpful. A transaction groups a set of statements and guarantees the following properties:

• No other client can update the data used in the transaction while the transaction is in progress; it's as though you have the server all to yourself. For example, other clients cannot modify the pilot or flight records while you're booking a pilot for a flight. By preventing other clients from interfering with the operations you're performing, transactions solve concurrency problems arising from the multiple-client nature of the MySQL server. In effect, transactions serialize access to a shared resource across multiple-statement operations.

• Statements in a transaction are grouped and are committed (take effect) as a unit, but only if they all succeed. If an error occurs, any actions that occurred prior to the error are rolled back, leaving the relevant tables unaffected as though none of the statements had been issued at all. This keeps the database from becoming inconsistent. For example, if an update to the flights table fails, rollback causes the change to the pilots table to be undone, leaving the pilot still available. Rollback frees you from having to figure out how to undo a partially completed operation yourself.

This chapter shows the syntax for the SQL statements that begin and end transactions. It also describes how to implement transactional operations from within programs, using error detection to determine whether to commit or roll back. The final recipe discusses some workarounds that you can use to simulate transactions in applications that use nontransactional storage engines. Sometimes it's sufficient to lock your tables across multiple statements using LOCK TABLE and UNLOCK TABLE. This prevents other clients from interfering, although there is no rollback if any of the statements fail. Another alternative may be to rewrite statements so that they don't require transactions.

Scripts related to the examples shown here are located in the transactions directory of the recipes distribution.