Mysql+innodb Semi-consitent Read principle and realization analysis

Semi-consistent Introduction

For those familiar with MySQL, or have seen InnoDB source of friends, may have heard of a fresh noun: semi-consistent read. What is semi-consistent read? The following paragraph, excerpt from the semi-consistent read article:

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 it is met (need to be updated), 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 the parameter innodb_locks_unsafe_for_binlog is set to true.

How does MySQL server interact with the InnoDB engine? How does the InnoDB engine implement Semi-consistent read? See the detailed analysis below.

Semi-consistent implementing the MySQL server layer

As can be seen from the above description, Semi-consistent read is only for the update operation, so in the sql_update.cc mysql_update method, it is like a downgrade:

Sql_update.cc::mysql_update ()

Notifies the underlying engine to attempt to semi consistent read

Whether to actually perform semi consistent read, determined by the underlying engine

Table->file->try_semi_consistent_read (1);

The InnoDB engine determines whether the current update can be semi-consistent read

Specific treatment methods, in the next section of the analysis

Ha_innodb.cc::try_semi_consistent_read (bool Yes);

Read and update operations for update

...

After the update operation is complete, close semi-consistent read

Table->file->try_semi_consistent_read (0);

MySQL Server layer processing semi-consistent is relatively simple, next look at the InnoDB engine processing method.

InnoDB engine Layer

InnoDB engine level, for the processing of Semi-consistent read, including two aspects of logic:

• Determines whether the current statement can support Semi-consistent read
• Special handling of Semi-consistent read when fetch next

Whether to use Semi-consistent read

As mentioned earlier, when the MySQL server is in the update, it invokes the engine's Try_semi_consistent_read method to try Semi-consistent read, and whether Semi-consistent read, is disposed of at the bottom.

Ha_innodb.cc::try_semi_consistent_read ()

if (yes &&

(Srv_locks_unsafe_for_binlog

|| Prebuilt->trx->isolation_level <= trx_iso_read_committed))

Prebuilt->row_read_type = row_read_try_semi_consistent;

For simple analysis, when the user sets the system parameter Innodb_locks_unsafe_for_binlog to True, or if the transaction isolation level adopted is read committed (or less), the setting Prebuilt->row_read_ The type parameter, which identifies the current UPDATE statement using Semi-consistent Read,fetch next needs to be targeted for special handling.

Fetch Next Special processing logic

The main function entrance of InnoDB fetch next is row_search_for_mysql, how does this function deal with Semi-consistent read?

Row0sel.c::row_search_for_mysql ()

...

Attempt to lock a record to a location

Err = Sel_set_rec_lock ();

...

Case DB_LOCK_WAIT:

If the lock needs to wait, then determine if it can be semi-consistent read

The judging condition is:

1. Prebuilt->row_read_type must be set to Row_read_try_semi_consisten

2. The current scan must be a range scan or a full-table scan, not a unique scan

3. The current index must be a clustered index

4. Do not meet the above three conditions, can not be semi-consistent read, lock wait

Note : If you do not need to lock the wait, then do not need to do semi-consistent read, directly

Read the latest version of the record, without the overhead of lock waiting.

if ((prebuilt->row_read_type! = row_read_try_semi_consistent)

|| Unique_search

|| Index! = clust_index)

Goto Lock_wait_or_error;

Can be semi-consistent read to construct the latest commit version based on the current version of the record

If there is no commit version, the current version is the latest version, the next record is read directly

Set Did_semi_consistent_read to True if a commit version is present

Row_sel_build_committed_vers_for_mysql ();

if (old_vers = = NULL)

Goto Next_rec;

Did_semi_consistent_read = TRUE;

...

If this update scan, because the lock waits, uses the semi-consistent, then sets the corresponding parameter

This parameter, which is mentioned in the next section, is useful for semi-consistent optimizations

if (Did_semi_consistent_read)

Prebuilt->row_read_type = row_read_did_semi_consistent;

Else

Prebuilt->row_read_type = row_read_try_semi_consistent;

At this point, InnoDB's fetch next finishes processing Semi-consistent read.

Optimization: Unlock unmatched row

The above mentioned is the semi-consistent read function implementation, in addition, MySQL for Semi-consistent read, also did the optimization measures: for the update scan returned by the non-satisfied record, the early release lock.

MySQL Server layer process :

Sql_update.cc::mysql_update ()

Determines whether the record returned by the current scan satisfies the where condition of the update

If satisfied, update operation is performed

if (! ( Select && Select->skip_record ())

...

If the Where condition of the update is not met, the row lock on the current record is selected to be released early

Else

Table->file->unlock_row ();

InnoDB engine layer process :

Ha_innobd.cc::unlock_row ();

Switch (prebuilt->row_read_type)

If the system does not set the parameter Innodb_locks_unsafe_for_binlog, and the isolation level is greater than

Trx_iso_read_committed, you cannot prematurely release a row lock that does not meet the criteria

Otherwise, you can release row locks that do not meet the criteria in advance

Case Row_read_with_locks:

if (!srv_locks_unsafe_for_binlog &&

Prebuilt->trx->isolation_level > Trx_iso_read_committed)

Break

If the current system has adopted semi_consistent read, but there is no lock waiting, the lock is directly successful

Then the row lock that does not satisfy the condition is released directly at this time

Case Row_read_try_semi_consistent:

Row_unlock_for_mysql ();

If the current system has semi_consistent read and has a lock wait, a commit version is constructed

There is no lock on the commit version, so there is no lock to put, return directly

Case Row_read_did_semi_consistent:

Prebuilt->row_read_type = row_read_try_semi_consistent;

Break

Semi-consistent advantages of advantages and disadvantages

• Reduces the conflict when updating the same row of records, reducing lock waits.

  No concurrency conflicts, read and record the latest version and lock, there are concurrency conflicts, read the latest commit version of the transaction, no lock, no lock wait.

• Locks can be placed ahead of time, further reducing the likelihood of concurrency collisions.

  For records that do not meet the update condition, you can place locks ahead of time to reduce the probability of concurrency collisions.

• On the basis of understanding semi-consistent read principle and implementation scheme, we can consider using semi-consistent read to improve the concurrency performance of the system.

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 the Innodb_locks_unsafe_for_binlog parameter is set.

Test Case Constructs Semi-consistent read

Set binlog_format=mixed;

Set session transaction ISOLATION level repeatable read;

CREATE TABLE T1 (a int not null) Engine=innodb DEFAULT charset=latin1;

INSERT into T1 VALUES (1), (2), (3), (4), (5), (6), (7);

Session 1:session 2:

Set autocommit=0;

Update T1 Set a = a + 10;

Set binlog_format=mixed;

Set session transaction Isolation level Read Committed;

Update T1 Set a = a + where a > 10;

At this time, 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.

Session 2 returns directly, 0 rows affected.

Construction unlock unmatched row

Set binlog_format=mixed;

Set session transaction ISOLATION level repeatable read;

CREATE TABLE T1 (a int not null) Engine=innodb DEFAULT charset=latin1;

INSERT into T1 VALUES (1), (2), (3), (4), (5), (6), (7);

Session 1:session 2:

Set autocommit=0;

Update T1 Set a = a + 10;

Commit

Set binlog_format=mixed;

Set session transaction ISOLATION level repeatable read;

Set autocommit = 0;

Update T1 Set a = a + where a < 10;

SELECT * from T1 lock in share mode;

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.

Friends can try to change session 2 isolation level to repeatable read, then session 1 will wait for session 2 commit.

More content: http://www.wangzhanjianshegs.com/website construction

Mysql+innodb Semi-consitent Read principle and realization analysis