MySQL semi-consistent read principle analysis-from the source point of view

1, what is semi-consistent read

A type of read operation used for UPDATE statements, which is a combination of Read committed and consistent read. When an UPDATE statement examines a row that's already locked InnoDB returns the latest committed version to MySQL so th At MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must is updated), MySQL reads the row again, and this time InnoDB either locks it or waits for a lock On it. This type of read operation can only happen when the transaction have the Read Committed isolation level, or when the Innod B_locks_unsafe_for_binlog option is enabled.

Is what happens in the UPDATE statement. At the RC isolation level or Innodb_locks_unsafe_for_binlog is set to True when concurrency occurs, if the record of the update has a lock wait, then the prev version of the record is returned (this lock-waiting lock is removed from the TRX before returning), To the MySQL layer for where to judge if the condition is met. If the Where condition is satisfied, then enter the INNODB layer again, really lock or lock wait.

The advantage of this is: reduce the same row record lock conflict and lock wait, no concurrency conflict, directly read the latest version lock, there is conflict, no lock, read prev version does not require lock wait.

Cons: The non-conflicting string jargon strategy is not safe for binlog. Can only occur under the RC isolation level and innodb_lock_unsafe_for_binlog.

2. Principle

3. Explanation

1) Semi-consistent reads need to be used with MySQL layer and InnoDB layer.

2) in the Mysql_update function, the default is to call Try_semi_consistent_read under RC or Innodb_lock_unsafe_for_binlog to try semi-consistent read tags: prebuilt->row _read_type = row_read_try_semi_consistent. The true execution of semi-consistent reads is determined by the INNODB layer.

3) Semi-consistent read condition: The record has a lock wait; must be a full table scan && The index is a level two index

4) Semi-consistent read, build the Prev version, and then call the function lock_trx_handle_wait to remove the lock wait from the TRX.

5) return Prev Rec before, will be placed into a semi-consistent read tag: Prebuilt->row_read_type = row_read_did_semi_consistent

6) Return to the MySQL layer, where you will be judged. If the match, then will enter the INNODB layer again, because prebuilt->row_read_type = = Row_read_did_semi_consistent, at this time no longer go to the semi-consistent reading judgment process, directly into the lock or lock wait.

5) Here Update has an optimization: InnoDB layer If the execution plan is an index push, then determine if the where condition match will advance. If not, call the function Row_unlock_for_mysql to release the lock on the clustered index.

6) Another optimization: After returning to the MySQL layer and judging where does not match, the Unlock_row function is called to release the lock. Note: Here the update does not have a lock conflict on the INNODB layer, and the lock is successfully added. That is, there is no semi-consistent read

MySQL semi-consistent read principle analysis-from the source angle analysis