Learn MySQL in one step-Consistency non-locked read and locked read, mysql-Consistency

Learn MySQL in one step-Consistency non-locked read and locked read, mysql-Consistency
Consistent nonlocking read)

Consistent non-locked read is the InnoDB Storage engine that reads data from the database at the current execution time through multi versioning. If an exclusive lock is applied to the read data row, a snapshot of the row is read instead of waiting for the lock to be released.

It is called non-locked read because it does not need to wait for the release of the X lock of the accessed row. Snapshot data refers to the data version before the modification, which is completed through the undo segment.

The non-locked read method greatly improves the database concurrency. In the InnoDB Storage engine, this is the default read method.

Snapshot data is a historical version of the current row data. Each row may have multiple versions. This technology has become a multi-version technology. The resulting Concurrency Control becomes Multi-Version Concurrency Control (MVCC ).

 

At the transaction isolation level, snapshot data is defined differently under read commited and repeatable read. At the read committed transaction isolation level, for snapshot data, non-consistent READ always reads the latest snapshot data of the locked row. At the repeatable read transaction isolation level, for snapshot data, non-consistent READ always reads the data version at the beginning of the transaction.

 

That is to say, at the read committed transaction isolation level, the unlocked READ data is the latest snapshot version data, that is, the snapshot data that has been COMMITTED by another transaction can be READ. In repeatable read, only the data before the start of the transaction is READ.

 

Consistent read locking

By default, the InnoDB Storage engine uses consistent non-locked reads for data. However, in some cases, the SELECT operation must be locked to ensure data logic consistency. The InnoDB Storage engine supports two consistent locking read operations for SELECT statements.

 

1. SELECT ...... FOR UPDATE

2. SELECT ...... LOCK IN SHARE MODE

 

SELECT ...... For update adds an X lock to the read records. Other transactions cannot apply any lock to the locked rows. And SELECT ...... Lock in share mode adds an s lock to the read record.

 

Even if the read row is added with a consistent lock read, if there is another consistent non-locked read operation to read the data of the row, it will not be blocked. Read the snapshot version of the modified row.

 

SELECT ...... For update and SELECT ...... Lock in share mode must be IN a transaction. When a transaction is committed, the LOCK is released. Therefore, you must enable the transaction when using the two SELECT lock statements.

 

Foreign key lock

To insert and update foreign key values, you must first find the records in the parent table, that is, SELECT parent table. However, for the SELECT operation of the parent table, the consistency non-lock read method is not used, because data inconsistency may occur. In this case, SELECT... is used ...... Lock in share mode to add an s lock to the records of the parent table. If an X lock is applied to the parent table, the lock is blocked.