MySQL database transaction ISOLATION level lock condition--repeatable Read && MVCC

This article turns from https://m.imooc.com/article/details?article_id=17289 thanks to the author

Previous section Review

The last two articles recorded my understanding of the MySQL transaction isolation level READ UNCOMMITTED, MySQL transaction isolation level read COMMITTED+MVCC.
This article records my understanding of repeatable Read.

Objective

MySQL is used in Read Committed, repeatable read two levels, and is used only at these two levels.

Directory

1. How to realize repeatable Read by simply adding locks?
2. What is the real situation like?

1. How to realize repeatable Read by simply adding a lock

1. Multiple threads simultaneously update the same record, plus x lock. So the update in the concurrency scenario is executed serially.
2. Industrial definition Select a record, this time the record is read the shared lock (S lock), and to the end of the transaction, because in this case can realize the record on the transaction time span of repeatable read. Do not allow other transactions to modify this record while reading.
3.update A statement, this time will be added to the record row-level exclusive lock (x Lock), and to the end of the transaction, in this scenario, the other read transactions will be blocked.

2. What is the real situation like?

Reading does not affect writing, writing does not affect reading.

1. Read does not affect write: The transaction modifies the original data in the form of an exclusive lock, and reads without locking, because MySQL reads committed, repeatable read under the transaction isolation level, the InnoDB storage engine uses non-locking consistent reads-that is, the read does not occupy and waits for locks on the table. The consistent non-locking read mode in MVCC is used. Because it is read without locking, it does not block other things from adding X locks on the same record to change this line of records.
2. Write does not affect read: The transaction modifies the original data in the form of an exclusive lock, and when the read row is performing a delete or update operation, the read operation does not wait for the row lock to be released. Instead, the InnoDB storage engine reads a snapshot of the row.

MySQL database transaction ISOLATION level lock condition--repeatable Read && MVCC (RPM)