1. Introduction

Whether it is system development or interview, transaction knowledge is always an inescapable topic, enough to see the importance of transaction. Because transactions are many and complex, some things are not covered, so this article will give a personal understanding of the transaction isolation level

2. Transaction features

  • Atomicity
  • Consistency
  • Isolation
  • They offer “Durability”

3. Transaction isolation level

  • Read uncommitted: A transaction can read content that has not been committed by another transaction
  • Read Commit: a transaction can read only the content that has been committed by other transactions, but not the content that has not been committed by other transactions
  • Repeatable read: The data read after a transaction is started is the same as the data read during the transaction execution
  • Serialization: read plus read lock, write plus write lock

3.1 Transaction Example

As shown in the figure, there are four transactions executing. What are the results of the two read operations on transaction 5 at the read commit and repeatable read isolation levels?

3.2 Reading the Submission Result

The serial number Read the result for the first time The result is read the second time
1 Li si Cathy

3.3 Repeatable read results

The serial number Read the result for the first time The result is read the second time
1 Li si Li si

3.4 Theoretical Analysis

By comparison, it can be found that the two query results of the same transaction are different under the isolation level of repeatable read. What is the specific reason?

According to the description of the read commit isolation level (one transaction can read data already committed by another transaction), the second query of transaction 5 can read data already committed by transaction 3

According to the description of repeatable read isolation level (the data read after a transaction is started is the same as the data read during the execution of the transaction), it can be known that the result of the second query of transaction 5 is the same as the result of the first query, so the result of the second query is also Li Si

3.5 Technical Analysis

3.5.1 Rolling Back Logs

Each update recorded by MySQL generates rollback logs. For the previous update, the corresponding rollback logs are shown in the following figure

3.5.2 Concurrency Control of Multiple versions

Concurrency Control (Multiversion Concurrency Control)

3.5.3 Read View

Select * from ‘Read View’; select * from ‘Read View’;

  • M_id: set of active transaction ids
  • Min_trx_id: indicates the id of the minimum active transaction
  • Max_trx_id: indicates the pre-allocated transaction ID. The maximum active transaction ID is + 1
  • Creator_trx_id: indicates the id of the current transaction

We learned in 3.5.1 that a record can have multiple versions at the same time. Which version will be read each time? In fact, each read starts from the latest version. Check whether the current version meets the requirements according to the rules. If yes, the system returns.

3.5.4 The following uses READ ViewA as an example

The read commit isolation level is used for analysis here

Read ViewA has a set of active transaction ids (3, 4, 5), a minimum transaction ID (3), a pre-allocated transaction ID (6), and a current transaction ID (5)

Each read starts from the latest version, so here is the read from zhao Liu version, but need to follow certain rules:

  • If the current version transaction ID is equal toRead ViewCurrent transaction ID, accessible (condition satisfied end)
  • If the current version transaction ID is less than the minimum transaction ID, the transaction has been committed and can be accessed.
  • If the transaction ID of the current version is greater than or equal to the pre-allocated transaction ID, the transaction is started after the view is generated and cannot be accessed.
  • Can be accessed if the current version transaction ID is greater than the minimum transaction ID and smaller than the pre-allocated transaction ID, and is not in the active transaction ID collection

Rule by rule, rule by rule

Read ViewA transaction ID = 5

  • Rule number one: 4 does not equal 5
  • Rule 4 is not less than 3 and is not satisfied
  • Rule 3:4 is not greater than or equal to 6
  • Rule 4 is between 3 and 6, but 4 is not satisfied in the active transaction ID set

Read ViewA transaction ID = 5

  • Rule number one: 3 does not equal 5
  • Rule 3 is not less than 3 and is not satisfied
  • Rule 3 is not greater than or equal to 6
  • Rule number four: 3 is not between 3 and 6

Read ViewA transaction id = 5

  • The first rule is that 2 does not equal 5
  • If rule 2 is less than rule 3, the value is createdRead ViewAwhenLi siThis record has been submitted, meets the conditions, you can seeRead ViewAThe read result isLi si

3.5.5 The following uses READ ViewB as an example

The read commit isolation level is used for analysis here

Read ViewB has a set of active transaction ids (4, 5), a minimum transaction ID (4), a pre-allocated transaction ID (6), and a current transaction ID (5)

Read ViewB transaction ID = 5

  • Rule number one: 3 does not equal 5
  • If rule 3 is less than rule 4, the value is createdRead ViewBwhenCathyThis record has been submitted, meets the conditions, you can seeRead ViewBThe read result isCathy

4. Summary

This article mainly introduces transaction characteristics, transaction isolation level, and carries out theoretical and technical analysis for read commit isolation level, hoping to give you a clearer understanding of transaction isolation level.