Database Reference
In-Depth Information
status is set to unavailable without the pilot being assigned a flight. The database
becomes inconsistent.
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 prop‐
erties:
• 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. Transactions solve concurrency problems arising from the multiple-client
nature of the MySQL server. In effect, transactions serialize access to a shared re‐
source across multiple-statement operations.
• Statements grouped within a transaction 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 executed. 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.
Scripts related to the examples shown here are located in the
transactions
directory of
the
recipes
distribution.
17.1. Choosing a Transactional Storage Engine
Problem
You want to use transactions.
Solution
Check your MySQL server to determine which transactional storage engines it supports.
Discussion
MySQL supports several storage engines, but to use transactions, you must use a
transaction-safe engine. Currently, the transactional engines include InnoDB and NDB.
To see which your MySQL server supports, use this statement: