MySQL InnoDB don't use a simple row-locking mechanism. It uses row-level locking in conjunction with multiversion concurrency control (MVCC). MVCC works by keeping a snapshot of the data as it existed at some point in time. This means transactions can see a consistent view of the data, no matter how long they run. It also means different transactions can see different data in the same tables at the same time!
InnoDB implements MVCC by storing with each row two additional, hidden values that record when the row was created (aka inserted) and when it was expired (or deleted). Rather than storing the actual times at which these events occurred, the row stores the system version number at the time each event occurred. This is a number that increments each time a transaction begins. Each transaction keeps its own record of the current system version, as of the time it began. Each query has to check each row's version numbers against the transaction's version. MVCC works only with the REPEATABLE READ and READ COMMITTED isolation levels. Let's see how this applies to particular operations when the transaction isolation level is set to REPEATABLE READ:
SELECT: InnoDB must examine each row to ensure that it meets two criteria:
- a. InnoDB must find a row's creation version is <= the transaction's version to make sure that either the row existed before the transaction began, or the transaction created or altered the row.
- b. The row's deletion version must be undefined or > the transaction's version to make sure that the row wasn't deleted before the transaction began.
Rows that pass both tests may be returned as the query's result.
INSERT: InnoDB records the current system version number as the new row's creation version.
DELETE: InnoDB records the current system version number as the row's deletion version.
UPDATE: InnoDB writes a new copy of the row, using the system version number for the new row's version. It also writes that number as the old row's deletion version.
The result of all this extra record keeping is that most read queries never acquire locks. They simply read data as fast as they can, making sure to select only rows that meet the criteria. The drawbacks are that the storage engine has to store more data with each row, do more work when examining rows, and handle some additional housekeeping operations.