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

This article mainly explains "how to understand mysql feature semi consistent read". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand mysql feature semi consistent read".

Let's take a look at the official saying:

Semi consistent read

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.

To put it simply, semi-consistent read is a combination of read committed and consistent read. A update statement, if you read a row of locked records, InnoDB returns the most recently submitted version of the record, and the upper layer of MySQL determines whether this version meets the where condition of update. If it is met (update is required), MySQL will restart a read operation, which will read the latest version of the row (and lock it).

Semi-consistent read only occurs at or below the read committed isolation level, or the parameter innodb_locks_unsafe_for_binlog is set to true.

The role of semi consistent read:

1. In RC, RU mode, or innodb_locks_unsafe_for_binlog = 1

2. Execute non-UPDATE SQL first, and then UPDATE, which will not block. If UPDATE is executed first and then other non-UPDATE SQL is executed, the lock will still be added

3. Only the actual rows are affected, and the rows that do not exist are also OK.

My understanding:

In the three cases of rc level or below (ru), or innodb_locks_unsafe_for_binlog = 1 (RR), semi_consistent_read.

Because innodb is a row-level lock, if the field does not have an index, it will be promoted to a table lock when it is locked. At this point, if S1 performs any locking operation, S2 performs update operation, and S2 will not block S2 if there is no row lock in S1 in the update condition of S2. The principle is: in the update initiated by S2, the upper layer of mysql determines whether it is satisfied according to the update condition. If there is no row lock in the condition, mysql will re-initiate a read operation and add a lock after update.

Similarly: if the column has an index, innodb automatically generates row locks, so the performance of semi_consistent_read doesn't make any sense.

So. Semi _ consistent_read occurs only in columns that do not have an index, or when there is a full table lock; as long as there is a row lock on the update data, the semi_consistent_read is invalidated

Thank you for your reading, the above is the content of "how to understand mysql feature semi consistent read". After the study of this article, I believe you have a deeper understanding of how to understand mysql feature semi consistent read, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.