Learning MySQL

Transactions and Locking

When a database is concurrently accessed by several users, you have to consider how you may be affected if other users change the data that you’re accessing, and how changes you make may affect other users. For example, you might get the wrong value for the total sales so far this year if new sales are being added to the database while you’re adding up the sales figures.

Locks can be applied to prevent concurrent users from interacting destructively with one other’s data. A read lock allows you to prevent other users from changing data while you’re reading and processing the data, while a write lock tells other users that the data is being changed and that they should not read or modify it. For example, you need locks to avoid problems with reports when one user is trying to produce a report while another user changes the data the report is derived from.

In some cases, you want all or none of a series of operations to succeed. For example, if you want to travel from Melbourne to Seattle via Los Angeles, you need to have a seat on the flight from Melbourne to Los Angeles, and a seat on the connecting flight from Los Angeles to Seattle. Having a confirmed seat on just one leg of the route is no use to you; you can’t fly without confirmed seats on both legs.

Transactions allow you to batch together SQL statements as an indivisible set that either succeeds or has no effect on the database. This means you can start a transaction and then issue a series of SQL statements. ...