Shulou(Shulou.com)05/31 Report--

Editor to share with you the MySQL semi-consistent reading principle of the example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

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.

The above is all the contents of the article "sample Analysis of MySQL semi-consistent Reading principle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.