Learn high-performance MySQL

Mysql's InnoDB storage engine is not a simple row-level lock, the implementation of MVCC, multi-version concurrency control, you can understand a variant of the line-level lock.

The MVCC of InnoDB is achieved by saving two hidden columns behind each row of records.
These two columns, one is to save the creation time of the row, one is to save the line's expiration time (delete time), the time is to use the system version number to store.

At select, InnoDB checks each row of records based on two combination of conditions:

A, InnoDB only look for data rows with a version number that is less than or equal to the current transaction version number, which ensures that these read rows are

Either it existed before the start of the transaction, or it was inserted or modified in the transaction.

b, the row's Delete row is either undefined or larger than the transaction's version number, which ensures that the transaction reads the rows that were not deleted before the transaction begins.

Only data rows that satisfy both conditions can be returned as query results.

At insert time, InnoDB saves the current version number as the creation time for the newly inserted data.

At update time, InnoDB creates the current version number to update this row of data while saving the current version number to

Learn high-performance MySQL