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

Today, Xiaobian shared with you a detailed introduction of MVCC. I believe many people don't know much about MVCC. In order to let you know more about MVCC, I summarized the following contents for you. Let's not say much. Let's look down together.

MVCC is implemented by saving a snapshot of the data at a certain point in time, that is, no matter how long it takes to execute, the data seen by each transaction is consistent, and according to the start time of the transaction, the data seen by each transaction for the same table at the same time may be different. innodb implements MVCC technology on lock-based concurrency control technology. innodb's MVCC technology has the following characteristics: The identity of the transaction, depending on the transaction ID, is a globally unique 64bits numeric multiversion, tuple-level multiversion, not page-level multiversion implemented by oracle. The latest data is stored in the data page, and older versions of other data are stored in the rollback segment. Because multiversions of innodb are tuple-level versions, on each record, there are some implicit fields related to concurrency and rollback equal to the transaction. DATA_TRX_ID: Data_ROLL_PTR: 7 bytes long, indicating that the old version of the data is located in the rollback segment, pointing to an old version. Only when the tuple is updated, a new version will be generated, and the old version will be placed in the rollback segment. Therefore, when the consistent lock-free read operation needs to read the old version according to the "read view" snapshot, it can only go back to the rollback segment according to the transaction ID to find the old version DATA_ROW_ID: 6 bytes long, indicating the ID identification of the monotonic self-growing row generated after the insert operation. If there is a clustered index, the index entry includes this DB_ROW_ID value Delete_BIT: Delete flag position UNDO log in the rollback segment is divided into two types of INSERT UNOD LOGS: log inserted into the rollback segment, only used for transaction submission. When the transaction is committed, the corresponding INSTR UNDO LOGS content is cleared UNDO LOGS: UPDATE The MVCC, which is used for consistent lockless reads, provides snapshots of old version data that can be read in isolation for consistent reads. Some old version data is cleaned up when there are no snapshots that need to satisfy consistent reads. The MVCC of innodb is implemented by keeping two hidden columns behind each row record. These two columns, one for the creation time of the saved row and one for the expiration time of the saved row, of course, do not store the actual time value. It's the system version number.(system version number), every time a new transaction starts, the system version number will be incremented. The system version number at the start of the transaction will be used as the version number of the transaction to record with the version number of each row of records queried. Here is how MVCC operates under the REPEATABLE READ isolation level. SELECTinnodb checks each row of records according to the following two conditions. Only records that meet the following two conditions To return innodb values as query results only find rows of data for which the system already has the current transaction version (That is, the system version number of the row is less than or equal to the system version number of the transaction). This ensures that the row read by the transaction either exists before the transaction begins, is not the deleted version of the row inserted or modified by the transaction itself, is undefined, or is not greater than the version number of the current transaction. This ensures that the row read by the transaction Insertinodb saves the current system version number for each newly inserted row as row version number DELETEinnodb saves the current system version number for each deleted row as row deletion identifier UPDATEinnodb saves the current system version number for each newly inserted row as row version number, and saves the current system version number to the original row as deletion identifier Two additional system version numbers are kept, so that most read operations do not require locking. This design makes reading data operations very simple, performs well, and ensures that only rows that conform to the standard will be read. The downside is that extra storage space is required for each row of records, more row checking work needs to be done, and some extra maintenance work. MVCC only works at REPEATABLE READ and READ COMMIT isolation levels. The other two isolation levels are incompatible with MVCC, because READ UNCOMMIT always reads the latest data row, not the data row that conforms to the current transaction version. SERIALIZEABLE locks all rows read. Here's how to start:

* We found a problem when executing step 7. How can id=4 not be detected? According to the above rules, the transaction ID of id=3 is 2, and the transaction ID of T3 is 3. In this case, id=4 can be detected. Therefore, the above rules must not be very detailed. There are missing places. We can look down and we can know that innodb will create a snapshot when executing select. The isolation level is greater than or equal to repeatable reading: All SELECT operations of a transaction block use the same snapshot, which is the isolation level established at the time of the first SELECT operation is less than or equal to committed read: All SELECT operations in a transaction block create their own snapshots, so each read is different. The read of the subsequent SELECT operation can read the data that has been submitted before this read. You can see which data is determined by this snapshot. Snapshots have the following attributes, and visibility is also judged according to these attributes. m_up_limit_id A snapshot has left and right boundaries. The left boundary is the minimum value, and the right boundary is the maximum value. This variable is the left boundary m_low_limit_id right boundary m_createor_trx_id transaction IDtrx_ids active at the time of snapshot creation, i.e. collection of read and write transactions that have not been completed. Create a snapshot. Suppose the transaction ID of the current transaction is 6. At this time, there are {3,5,6,10} active transactions in the read-write transaction chain (this is global), excluding read-only transactions. When calling the method to create a snapshot,{3,5,10} will be stored in trx_ids in the current view.(6 is not recorded in the view because it is the ID of the current transaction), the value of m_up_limit_id is 3, m_low_limit_id is 10, m_creator_trx_id is 6trx_idm_low_limit_id is invisible, It means that the transaction trx_ids corresponding to the transaction is still active after the snapshot, i.e., it has not been committed. trx_id=m_creator_trx_id Visible formula: Determine whether the left side is visible by comparing whether the value of the transaction ID is in the left, middle or right side of the [m_up_limit_id,m_low_limit_id] interval. The middle is invisible in trx_ids equal to m_creator_trx_id. The right side is invisible. The multiple versions in MVCC technology are implemented according to UNDO logs. As we said above, DATA_ROLL_PTR indicates where the old version of data is located in the rollback segment. DB_ROLL_PTR is structured as follows: Multi-version generation For a logical multi-version generation process, the way is as follows: The oldest version must be inserted into the version of the UNDO log (for clustered indexes, not all fields of the record are read into the rollback segment, but the primary key information is temporarily stored) Update operation, the old value is stored in the UNDO log. The same log is updated repeatedly, and each time an old value (previous image) is read into the UNDO log, there will be multiple versions, and between the versions, use DATA_POLL_PTR to execute the version according to, so that all versions form a linked list, the chain head is the record on the index, and the chain tail is the UNDO information generated when it is first inserted. Delete operation, save delete flag in UNDO log Multi-version search For clustered indexes, you can find the previous version record from the rollback segment according to DATA_ROLL_PTR, and know whether this record is generated by update operation or insert operation. If it is generated by insert operation, it means that this version is the most original version. Even if it is invisible, there is no need to go back to find the old version. However, the process of finding is closely related to the isolation level. If the isolation level is not committed, the old version is not found at all. The records read on the index are directly used. If the isolation level is not committed, you need to enter the UNDO rollback segment to find according to DATA_POLL_PTR. Also, determine whether it is visible. If it is visible, return. If it is invisible, always look according to DATA_POLL_PTR.

With the previous supplement, let's see if we can explain the problem that appeared in step 7 above.

We modify the title of id=1 in step8 of T2, but step 9, step 10 do not see, step9 does not see, we will not have any accident, because the transaction ID corresponding to step9 is less than the transaction ID of step8, but the transaction ID of step 10 is greater than the transaction ID of step8, so let's analyze what caused it, we do not say the format of undo log here, we only need to know that according to undo log we can know multiple versions of the record at the beginning, id=1 is such a T1 step4m_up_limit_id=1, m_low_limit_id=1, m_creator_trx_id=1, trx_ids={},DATA_TRX_ID=0DATA_TRX_ID

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.