Shulou(Shulou.com)06/01 Report--

The following mainly brings you to analyze the MySQL semi-consistent reading principle based on the source code point of view. I hope that the MySQL semi-consistent reading principle based on the source code angle can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

1. What is semi-consistent reading

A type of read operation used for UPDATE statements, that is a combination of read committed and consistent read. When an UPDATE statement examines a row that is already locked, InnoDB returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be 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 has the read committed isolation level, or when the innodb_locks_unsafe_for_binlog option is enabled.

It happens in the update statement. When the RC isolation level or innodb_locks_unsafe_for_binlog is set to true, if a lock wait occurs in the update record, the prev version of the record is returned (the lock waiting for the lock is deleted from the trx before returning), and the where is used to determine whether the condition is met in the mysql layer. If the where condition is met, enter the innodb layer again, and the lock is actually added or a lock wait occurs.

This has the advantage of reducing lock conflicts and lock waits for the same row of records; when there is no concurrency conflict, the latest version is locked directly; when there is a conflict, there is no lock, and there is no need for lock waiting to read the prev version.

Cons: the non-conflicting serial jargon policy is not secure for binlog. Can only occur under RC isolation level and innodb_lock_unsafe_for_binlog.

2. Principle

3. Explanation

1) semi-consistent reading requires mysql layer and innodb layer to be used together.

2) in the mysql_update function, try_semi_consistent_read is called by default with an attempt to semi-consistent read tag under RC or innodb_lock_unsafe_for_binlog: prebuilt- > row_read_type = ROW_READ_TRY_SEMI_CONSISTENT. It is up to the innodb layer to actually perform semi-consistent reads.

3) the condition of semi-consistent read: lock waiting occurs for the record; it must be a full table scan & & the index is a secondary index.

4) for semi-consistent reading, build the prev version, and then call the function lock_trx_handle_wait to remove the lock from the trx.

5) before returning prev rec, it will be set to a semi-consistent read tag: prebuilt- > row_read_type = ROW_READ_DID_SEMI_CONSISTENT

6) when you return to the mysql layer, where judgment will be made. If there is a match, then you will enter the innodb layer again. Because prebuilt- > row_read_type = = ROW_READ_DID_SEMI_CONSISTENT, you will no longer follow the semi-consistent read judgment process and go directly to locking or waiting.

5) here update has an optimization: if the execution plan of the innodb layer is index push-down, then it will determine whether the where conditions match in advance. If it does not match, the function row_unlock_for_mysql is called in advance to release the lock on the clustered index.

6) another optimization: when you return to the mysql layer and determine that the where does not match, the unlock_row function will be called to release the lock. Note: there is no lock conflict in the innodb layer of update here, and the lock is successfully added. That is, there is no semi-consistent read.

For the above analysis of MySQL semi-consistent reading principle based on the source code point of view, do you think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.