Background:
I was asked this question before the interview. There are two transactions in the database. When transaction1 is updating a row, Will transaction2 block during select. I used MySQL for tests before, but I was impressed. But the interviewer raised a question. Today, I used MySQL to verify this problem and carefully studied MySQL background implementation, later, I found the following article on the Internet, but in some parts of the article it seems that it is not logical. I don't have time to read the MySQL source code, so I will give my own speculation based on the actual results, share the answer if you have a clear answer.
First, I will summarize the incorrect content in the text (marked with red as my guess). You can read the text first and then look back at my summary:
- READ_UNCOMMITTED
When the read is not committed, the read transaction reads the master record directly, regardless of whether the update transaction is complete.
- READ_COMMITTED
When a read transaction is committed, each time the read Transaction checks whether there is a lock on the master record, if there is no lock, it reads the master record; if there is a lock, it reads the latest version of the undo log. In this way, the latest COMMITTED data is read each time. Therefore, two reads to the same field may read different data (phantom read), but the latest data can be read every time.
- REPEATABLE_READ
During the first read, check whether there is a lock on the master record. If there is no lock, read the master record. If there is a lock, read the latest version of the undo log. I guess a new record is created during update, and then the original primary record content is copied to the current primary record. The original primary record becomes the latest undo log. Read the first read version each time. This ensures that no phantom read is generated, but the latest data may not be read.
- SERIALIZABLE
Lock tables, read/write mutual blocking, less used
The body link is as follows:
How does Mysql implement MVCC? There are countless people asking this question, but there is no answer in google. This article attempts to find the answer from the Mysql source code.
In Mysql, MVCC is supported in the Innodb Storage engine. Innodb implements three hidden fields for each row of records:
- 6-byte transaction ID (
DB_TRX_ID)
- 7-byte rollback pointer (DB_ROLL_PTR)
- Hidden ID
A 6-byte transaction ID is used to identify the transactions described in this row. A 7-byte rollback pointer needs to understand the transaction model of Innodb. 1. Innodb Transaction-related concepts in order to support transactions, Innbodb introduces the following concepts:
- Redo log
Redo log is used to save the executed SQL statement to a specified Log file. when Mysql executes the recovery, it can re-execute the SQL operation of the redo log record. When the client executes each SQL statement (update statement), The redo log is first written into the log buffer. When the client executes the COMMIT command, the content in the log buffer is refreshed to the disk as needed. Redo log exists as an independent file on the disk, that is, the Innodb log file.
- Undo log
Opposite to redo log, undo log is used for rollback. The specific content is to copy the database content (ROW) before the transaction to the undo buffer, refresh the content in the undo buffer to the disk at the appropriate time. Like the redo buffer, the undo buffer is also a ring buffer, but when the buffer is full, the content in the undo buffer will be refreshed to the disk. Unlike the redo log, there is no separate undo log file on the disk. All undo logs are stored in the primary ibd data file (tablespace), even if each table is set to a data file on the client.
- Rollback segment
The concept of rollback segments comes from the Oracle transaction model. In Innodb, the undo log is divided into multiple segments, and the undo log of a Specific Row is stored in a specific segment, it is called a rollback segment. It can be considered that the undo log and rollback segment share the same meaning.
- Lock
Innodb provides row-based locks. If the number of rows is very large, the number of locks under high concurrency may be relatively large. According to Innodb documentation, innodb effectively optimizes the lock space. Even if the concurrency is high, the memory will not be exhausted.
There are two types of row locks: exclusive locks and shared locks. The exclusive lock and the exclusive lock are completely equivalent to the read/write lock. If a transaction is updating a row (exclusive lock), other transactions, whether read or write, must wait. If a transaction reads a row (shared lock ), other read tasks do not have to wait, but write tasks do. The shared lock ensures no waiting among multiple reads, but the lock application depends on the transaction isolation level of Mysql.
- Isolation level
The isolation level is used to limit the degree of direct interaction of transactions. Currently there are several industrial standards:
-READ_UNCOMMITTED: Dirty read
-READ_COMMITTED: Read and submit
-REPEATABLE_READ: Repeated read
-SERIALIZABLE: SERIALIZABLE
Innodb supports all four types of data. There are not many dirty read and serialized application scenarios, and it is widely used for read submission and repeated read. The implementation method will be introduced later.
Next, let's take a look at the highlights of page 2nd:
