This is the second day of my participation in Gwen Challenge

One, foreword

In a transaction, multiple SQL statements are executed, as shown below:

  1. Dirty read

Both dirty writes and dirty reads are caused by a transaction updating or querying data that has been updated by another transaction that has not yet committed.

Because the other transaction has not been committed, it can be reversed and rolled back at any time, which will inevitably result in the loss of your updated data, or the loss of the data you previously queried.

  1. Unrepeatable read

The value changed by the committed transaction is read by your transaction, and the value changed by other committed transactions is read multiple times within your transaction, resulting in unrepeatable reads.

  1. Phantom read

Phantom read: a query for data not seen in the previous query!

A transaction queries the same SQL multiple times, and each query finds some previously unseen data.





Ii. Isolation level

SQLThere are four transaction isolation levels specified in the standard:

That is, how multiple transactions run concurrently are isolated from each other to avoid some transaction concurrency problems.

  • Read Uncommitted The read is not committed

  • Read COMMITTED: This level can read committed values after other transactions have committed, but never read values when other transactions have not committed.

  • Repeatable read Repeatable read

  • The serializable serialization

  1. read uncommitted(RU) : Dirty writing is not allowed

It is not possible for two transactions to update the value of the same row without committing, but at this level of isolation, dirty reads, unrepeatable reads, and phantom reads can occur.

  1. read committed(RC) : Dirty writes and dirty reads do not occur

That is, you can never read a value modified by someone else’s transaction without committing it. However, non-repeatable reads and phantom read problems can occur because your transaction will read once someone else’s transaction changes the value and commits.

  1. repeatable read(RR) : Repeatable read level

At this level, dirty write, dirty read, and unrepeatable read problems do not occur.

  1. serializable: a serial

Multiple transactions are not allowed to be executed concurrently, only sequentially.


Modify theMySQLDefault transaction isolation level for:

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
Copy the code

Transactional(isolation= Isolation.default) : The DEFAULT value is the isolation level supported by MySQL. The DEFAULT isolation level is RR.

Of course, you can manually change the isolation.read_uncommitted level to read values that others did not commit.


The locking mechanism solves the problem of multiple transactions updating a row of data simultaneously.


MySQLimplementationMVCCWhen the mechanism is based onundo logMulti-version chain +ReadViewMechanism, the default isRRIsolation level.

Based on this mechanism, the RR level can avoid dirty writes, dirty reads, and unrepeatable reads, as well as phantom reads.


undo logVersion chain story:

Understanding: The concept of txr_id and roll_pointer hidden fields when multiple transactions serial update a row of data, including the concept of undo log concatenated multi-version chains.

Each piece of data has two hidden fields:

  • Trx_id: indicates the id of the transaction that last updated this data

  • Roll_pointer: points to the undo log generated before you update the transaction

  1. Transaction A, insert A data

  1. Transaction B, modify the data corresponding to transaction A

  1. Transaction C, modify the data corresponding to transaction B


Based on theundo logMulti-version chain implementationReadViewmechanism

When a transaction is executed, a ReadView is generated that contains four key things:

  • M_ids: indicates which transactions executed in MySQL have not yet been committed

  • Min_trx_id: indicates the minimum value of m_IDS

  • Max_trx_id: specifies the maximum transaction ID of the next mysql transaction to be generated

  • Creator_trx_id: indicates the id of the current transaction

Suppose the database already has a row of data, as shown below:

There are two transactions executing concurrently:

  • A transaction A(id = 45) : reads the row

  • A transaction B(id = 59) : updates the row

Transaction A opens A ReadView, which looks like this:

M_ids = 45, 59 min_trx_OID = 45 max_trx_id = 60 creator_trx_id = 45Copy the code

If the trx_id of the current row is less than min_trx_id in ReadView, then transaction A queries the row for the first time.

Txr_id = 32, < min_trx_id = 45; Note Before transaction A is enabled, the transaction that modifies the data in this row is submitted long ago. Therefore, you can query the data in this row.

As shown in figure:

Transaction B changes the value of the row to B, sets txr_id to its id = 59, and roll_pointer points to an undo log generated before the change, and transaction B commits.

As shown in figure:

Txr_id = min_txr_id = 45(ReadView); txr_id < max_trx_id = 60 (max_trx_id = 60); Txr_id = 59 is in ReadView m_IDS list. ReadView m_IDS is in the ReadView m_IDS list, so transaction A cannot query this row of data!

As shown in figure:

Trx_id < min_trx_id = 45 trx_id = 32 trx_id = 32 trx_id = 32 trx_id = 45 trx_id = 32 The undo log version must have been executed and committed before transaction A started.

Then, query the undo log directly, as shown in the following figure:

This is where the undo log multiversion chain comes in. You can save a snapshot chain so that you can read the previous snapshot value.





Three,Read Committed(RCHow is the isolation level based onReadViewMechanism implementation?

RC isolation level: While a transaction is running, data modified by another transaction can be read as long as it has been modified and committed by another transaction.

So this can cause unrepeatable and phantom reading problems.

When a transaction is set to the RC isolation level, it regenerates a ReadView each time it initiates a query.

Suppose the database is as follows:

  • The transaction ID = 50 is added

  • Active transaction A id = 60

  • Active transaction B id = 70

As shown in figure:

Transaction B performs an update (not committed yet) to update this data, as shown in the figure below:

Transaction A initiates A query operation, and A ReadView will be generated, as shown in the following figure:

min_trx_id = 60
max_trx_id = 71
creator_trx_id = 60
Copy the code

Transaction B commits commit, and transaction B is no longer active in the database.

According to the RC isolation level definition, once transaction B commits, it means that transaction A can read the value changed by transaction B the next time it queries.

Transaction A initiates the query again, at which point A ReadView is generated again, as shown below:

[60] min_trx_id = 60, max_trx_id = 71, m_ids = [60] Note Transaction B was committed before the ReadView was generated.





Four,Read Repeatable(RRHow is the isolation level based onReadViewMechanism implementation?

In RR isolation, a transaction reads a piece of data. No matter how many times a transaction reads a piece of data, the value is the same. After other transactions modify and commit the data, they cannot see the value, avoiding the problem of unrepeatable reads.

At the same time, if other transactions insert some new data, also cannot read, can avoid the phantom read problem.

Suppose the database is as follows:

  • The transaction ID = 50 is added

  • Active transaction A id = 60

  • Active transaction B id = 70

As shown in figure:

Transaction A initiates A query operation, the first query will generate A ReadView, which can be queried, as shown in the figure:

Because trx_id of this data is 50 < min_trx_id, it indicates that the operation on this data has been submitted before the query is initiated.

min_trx_id = 60
max_trx_id = 71
creator_trx_id = 60
m_ids = [60, 70]
Copy the code

Transaction B initiates the update operation and commits it, then changes trx_id = 70 and generates an undo log. Transaction B ends, as shown in the figure below:

Now m_ids = [60, 70] in ReadView. Transaction B is finished, but transaction A still has transaction ids 60 and 70 in its ReadView

At this time, transaction A queries again, transaction B with ID 70 is still running, and then this transaction B updates this data, so transaction A cannot query the value updated by transaction B at this time, so it continues to follow the pointer to the historical version chain, as shown in the figure:

How to solve non-repeatable read?

Since ReadView is only generated once, transaction A reads the same data multiple times, reading the same value each time, and reading the same value unless it modifies the value itself.

How to solve illusory reading?

Since ReadView is only generated once and can only read transaction ids less than max_trx_id, this problem does not occur.