Innodb semi-consistent Introduction

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.

In short, semi-consistent read is a combination of Read committed and consistent read. An UPDATE statement that, if read to a row of locked records, INNODB returns a record of the most recently committed version, which is determined by the MySQL upper level to meet the where condition of the update. If satisfied, MySQL will re-initiate a read operation, which will read the latest version of the row (and lock).

Semi-consistent Read only occurs under the Read Committed isolation level, or is a parameter innodb_locks_unsafe_for_binlog=on.

Create Table int  not NULL ); Insert  into Values (1), (2), (3), (4), (5), (6), (7);

1. Meeting where condition read commit level Demo

Session 2 does not need to wait for session 1, although session 1 update latter meet session 2 conditions, but because session 2 is semi-consistent read, the record read to the preceding paragraph is (1-7), The update where condition for session 2 is not met, so session 2 returns directly.

2. Satisfying where condition repeatable read Demo

Session 2 is waiting

3. Do not meet the Where condition repeatable read Demo

Session 1 is submitted before session 2, Session 2 can be semi-consistent read. and read the session 1 update latter, complete lock. However, because the update latter does not satisfy the WHERE condition of Session 2, session 2 releases the lock on all rows (determined by the MySQL server layer and calls the Unlock_row method to release the row lock).

At this point, Session 1 executes the SELECT * from T1 lock in share mode statement again, directly succeeding. Because session 2 has already released all the row locks in advance.

Advantages

• Reduces the conflict when updating the same row of records, reducing lock waits
• Locks can be placed ahead of time to further reduce the likelihood of concurrency collisions

Disadvantages

• Non-conflicting serialization policy, therefore, is not secure for Binlog

  Two statements, depending on the order of execution and the order in which they are submitted, the results of copying to the repository through Binlog will be different. is not a fully conflicting serialization result.

  Therefore it can only be used if the isolation level of the transaction is read committed (or below), or if Innodb_locks_unsafe_for_binlog=on is set.

Source:

Ho Dengcheng: Mysql+innodb semi-consitent read principle and implementation analysis

Innodb semi-consistent Introduction