Learning MySQL, 2nd Edition

Chapter 6. Transactions and Locking

Using locks for transaction isolation is a pillar of SQL databases—but this is also an area that can cause a lot of confusion, especially for newcomers. Developers often think that locking is a database issue and belongs to the DBA realm. The DBAs, in turn, believe this is an application issue and consequently the responsibility of the developers. This chapter will clarify what happens in situations where different processes are trying to write in the same row at the same time. It will also shed light on the behavior of read queries inside a transaction with the different types of isolation levels available in MySQL.

First, let’s define the key concepts. A transaction is an operation performed (using one or more SQL statements) on a database as a single logical unit of work. All the SQL statements’ modifications in a transaction are either committed (applied to the database) or rolled back (undone from the database) as a unit, never only partially. A database transaction must be atomic, consistent, isolated, and durable (the famous acronym ACID).

Locks are mechanisms used to ensure the integrity of the data stored in the database while applications and users are interacting with it. We will see that there are different types of lock, and some are more restrictive than others.

Databases would not need transactions and locks if requests were issued serially and processed in order, one at a time (a SELECT, then an INSERT, then an UPDATE