"Seeing for real" hands-on practice understanding read COMMITTED && MVCC

"Seeing for real" hands-on practice understanding read COMMITTED && MVCC

First set the database isolation level to read Committed (COMMITTED):

setglobaltransactionisolationlevelREADCOMMITTED ;setsessiontransactionisolationlevelREADCOMMITTED

[READ COMMITTED] can solve the problem

Let's see why [Read COMMITTED] solves the problem of dirty reads:
Transaction 1:

STARTTRANSACTIONUPDATESET state=1WHERE id=1SELECT sleep(10);ROLLBACK;

Transaction 2:

STARTTRANSACTIONSELECTFROMWHERE id=1;COMMIT;

Transaction 1 is performed prior to transaction 2.
execution information for transaction 1 :

1]STARTTRANSACTION00.2]UPDATESET state=1WHERE id=110.3]SELECT sleep(10010.4]ROLLBACK00.051s

execution information for transaction 2 :

1]STARTTRANSACTION00.2]SELECTFROMWHERE id=100.3]COMMIT00.001s

Final result :

Conclusion :
Read Committed [Read COMMITTED] Isolation level resolves the problem of dirty reads, but does not appear to be a dirty read problem that is resolved according to the level two blocking protocol.

Analysis :
The two-level blocking protocol that reads the database that submitted the [read COMMITTED] isolation level corresponds. The secondary blocking protocol adds an X lock to the data before it is modified until the end of the transaction releases the X lock. The S lock must be added before reading the data, and the S lock can be released after reading. Because transaction 1 performs the modification before the modification, the application holds the X lock before the transaction ends and the x lock is released. The lock time period is about 10.056s before [SQL 2] starts until [SQL 4]. Transaction 2 reads after transaction 1 and, according to the level two blocking protocol, transaction 2 applies for holding S lock before reading the data. However, transaction 1 holds the X lock for this data, so transaction 2 must wait for Transaction 1 to release the X lock, which is about 10 seconds. But we can see from the execution of transaction 2 that the time to execute the query is 0.005s, which is much less than 10 seconds. so we can boldly infer that MySQL's InnoDB engine does not lock read operations under the [Read COMMITTED] isolation level . But [read COMMITTED] isolation level does solve the problem of dirty read, then how does MySQL solve the dirty read problem?

MVCC (multiple version concurrency control)

The answer is multi-version concurrency control (MVCC), which can be thought of as a variant of row-level locking, but it avoids lock-up operations in many cases and therefore costs less. a non-blocking read operation is implemented, and the write operation only needs to lock the necessary rows .
If we understand the working mechanism of MVCC, we can understand how the [read COMMITTED] Isolation level solves the problem of dirty reads.

MVCC is specific to the following operations:

SELECT

? InnoDB will check the records according to the following two conditions:

? ①innodb only finds rows of data that are earlier than the current version of the transaction (that is, the version number of the row is less than or equal to the system version number of the transaction), which ensures that the data reads the rows that existed before the transaction started, or that the transaction itself was inserted or modified.

? The delete version number of the ② row is either undefined or greater than the current transaction version number. This ensures that the transaction is read to a row that has not been deleted before the transaction begins.

? Only records that meet the above two criteria can be returned as query results.

INSERT

? InnoDB saves the current system version number as the row version number for each newly inserted row.

DELETE

? InnoDB saves the current system version number for each row deleted as a row delete identity.

UPDATE

? InnoDB saves the current system version number as the row version number for each newly inserted row, while saving the current system version number to the original row as the row delete identity.

InnoDB implements three hidden fields for each row of records:

6-byte transaction ID (DB_TRX_ID)
7-byte rollback pointer (DB_ROLL_PTR)
The Hidden ID 6-byte thing ID is used to identify the transaction that the line describes

Transaction 1 performs the following actions:
① Lock the row with an exclusive lock
② Record Redo Log
③ Copy the value of the row before the change to undo log, which is the line below
④ Modify the value of the current row, fill in the transaction number so that the rollback pointer points to the pre-modified line in undo log

If transaction 1 finally performs a commit operation, nothing is done. If you perform a rollback operation, you need to restore the pre-modified data from the undo log by rolling back the pointer.

Read view determines whether the current version data item is visible

In InnoDB, when a new transaction is created, InnoDB creates a copy of the active transaction list (trx_sys->trx_list) in the current system (read view), and the copy holds the system's current A list of other transaction IDs that should not be seen by this transaction . When the user reads the row record in this transaction, InnoDB compares the current version number of the row with the Read view.
The specific algorithm is as follows:

The current transaction ID for this row is trx_id_min for the oldest transaction ID in Trx_id,read view and the latest transaction ID is Trx_id_max.

• If trx_id< trx_id_min, then the transaction that indicates the row record is committed before the new transaction is created, so the current value of the row record is visible.
• If Trx_id>trx_id_max, then the transaction that indicates the row record is open after the new transaction is created, so the current value of the row record is not visible.
• If Trx_id_min <= trx_id <= Trx_id_max, then the transaction in which the row record resides is active at the time the new transaction was created, traversing from trx_id_min to Trx_id_max, if trx_ ID is equal to one of their transaction IDs, then it is not visible.
  Returns the value of the visible row by removing the data from the latest Undo-log version number from the rollback segment pointed to by the db_roll_ptr pointer that the row records.

Note that the new transaction (the current transaction) and the transaction that is in memory commit are not in the active transaction list.

The corresponding source code is as follows:

1 .    　　When the transaction ID of a row record is less than the minimum active ID of the current system, it is visible.    　　if (trx_id < view->up_limit_id) {return (TRUE); }2 .    　　is not visible when the transaction ID of the row record is greater than the current system's maximum activity ID (that is, the ID of the next transaction that has not been assigned).    　　if (trx_id >= view->low_limit_id) {return (FALSE); }3 .    　　When the transaction ID of a row record is in the active range, it is determined if it is not visible in the active list, if it is not. for (i = 0 ; i < n_ids; i++) {trx_id_t view_trx_id = read_view_get_nth_trx_id (v    　　　　Iew, N_ids-i-1 );    　　　　if (trx_id <= view_trx_id) {return (trx_id! = view_trx_id); }}  

Transaction 2 performs the following actions:
Ideally, transaction 1 transactions id=1, transaction 2 transaction id=2. Transaction 1 is in a wait state because transaction 2 is queried at execution time. So read view is {1}, transaction 2 reads the data row Trx_id=1,read view, the oldest transaction ID is trx_id_min=1, and the latest transaction ID is trx_id_max=1. Because Trx_id_min <= trx_id <= trx_id_max, and trx_id_min = trx_id = Trx_id_max, the transaction where the row record is active is not visible at the time the new transaction was created. Therefore, the value of the visible row is returned by removing the data from the latest Undo-log version number from the rollback segment pointed to by the db_roll_ptr pointer that the row records. Therefore, there is no dirty reading phenomenon.

[READ COMMITTED] cannot solve the problem

[Read COMMITTED] isolation level does not solve the problem of non-repeatable read, two reads in a transaction may have different results.
Let's simulate this:
Transaction 1:

STARTTRANSACTIONSELECT sleep(5UPDATESET state=1WHERE id=1;COMMIT;

Transaction 2:

STARTTRANSACTIONSELECTFROMWHERE id=1SELECT sleep(10SELECTFROMWHERE id=1;COMMIT;  

Transaction 1 is performed prior to transaction 2.
Execution Result :

Conclusion :
Read Committed [Read COMMITTED] isolation level does not solve the problem of non-repeatable reads, but if you follow the above, the MySQL InnoDB engine uses read view to determine whether the current version of the data item is visible. The Read committed [read COMMITTED] Isolation level should also not have the problem of non-repeatable reads, but the reality is not.

Analysis :
Read Committed [Read COMMITTED] The non-repeatable reads under the isolation level are caused by the build mechanism of Read view. At the [READ COMMITTED] level, all data submitted before the current statement is executed is visible. During each statement execution, close the Read view and recreate the current read view. This allows you to create a transaction interval for the read view based on the current global transaction list.
So in our simulated transaction, the transaction ID of transaction 1 is trx_id1=1, transaction ID trx_id2=2 for transactions 2. Assume that transaction 2 is the transaction id=0 of this row data before the first read of the data. The read view that was generated before the statement ① execution in transaction 2 was {1},trx_id_min=1,trx_id_max=1. Because trx_id (0) < Trx_id_max (1), this row data is visible to this transaction and returns the value of that visible row state=0. The statement ① waits 10 seconds after execution, the 5th second when Transaction 1 modifies the data plus X lock 0->1, and then commits the transaction release lock. The ② statement generates a read view of {null} before execution, indicating that no other active transaction exists in the current system, and there is no other transaction that should not be seen by this transaction, so the current value of the row record is state=1 visible. There are two read data inconsistency problems, that is, non-repeatable read.

The non-repeatable read problem was addressed in MySQL's default isolation level [repeatable read]. As for how to solve, first sell a xiaoguanzi. can give a small hint, also is related to the generation mechanism of Read view. See the next blog post on how to predict your funeral.

This article for Bo Master Learning sentiment Summary, the level is limited, if improper, welcome correction.

If you think it's good, just click on the " recommend " button below, thanks for your support.

Reprint and quote please specify the source.

"Seeing for real" hands-on practice understanding read COMMITTED && MVCC