Author: Kobayashi Coding

Xiaolincoding.com/

Hello, I’m Xiao Lin.

I’ve written about how MySQL’s MVCC works, but recently some readers have pointed out some problems in the comments section of the site.

This is a very important point, and it’s often asked in interviews, so I’ve rewritten this article! Drive!

The body of the

Here’s my wallet. It’s one million yuan in total.

I’m in a good mood today. I decided to transfer one million yuan to you. The final result is definitely that my balance becomes zero yuan, and your balance is one million yuan more.

Transfer involves a series of operations in the program. Suppose that the process of transferring 1 million to you is composed of the following steps:

You can see that the transfer process involves two database modifications.

Suppose that after the third step, the server suddenly power down, will happen a painful thing, my account deducted 1 million, but the money did not reach your account, that is to say the 1 million disappeared!

To solve this problem, ensure that all database operations in the transfer business are indivisible, either all successful or all failed, and no intermediate data is allowed.

A “Transaction” in a database can do just that.

We open transaction before transfer operations, such as after completion of all database operations to perform, to commit the transaction, for has committed transaction, the transaction on the database changes will take effect a permanent, if midway interruption or error occurs, the changes made to the database during the transaction will be rolled back to the before they didn’t perform this transaction.

What are the features of transactions?

Transactions are implemented by the MySQL engine, the InnoDB engine that we’re used to, it supports transactions.

Not all engines support transactions. For example, MySQL’s native MyISAM engine does not support transactions, which is why most MySQL engines use InnoDB.

Transactions look and feel simple, but to implement them they must comply with four features, which are as follows:

  • Atomicity: All operations in a transaction, either complete or not complete, do not end up somewhere in the middle, and if an error occurs during the transaction’s execution, it is rolled back to the state before the transaction began, as if the transaction had never been executed.
  • Consistency: The integrity of the database will not be damaged because of transaction execution, such as the table has a field for the name, it has a unique constraint, which is in the table name cannot be repeated, if a transaction to modify the name field, but after the transaction is committed, the table name becomes non uniqueness, it destroyed the transaction consistency requirements, the database will cancel the transaction, Returns the initialized state.
  • Isolation: The ability of a database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation prevents data inconsistency caused by overlapping execution of multiple concurrent transactions.
  • Durability: Modifications to data are permanent after transactions end, they are not lost even if systems fail.

What technology does the InnoDB engine use to ensure these four features of transactions?

  • Persistence is guaranteed by redo logs;
  • Atomicity is guaranteed by undo log;
  • Isolation is guaranteed by MVCC (Multi-version Concurrency Control) or locking mechanisms;
  • Consistency is guaranteed by persistence + atomicity + isolation.

This time we will focus on transactional isolation, which is one of the most frequently asked questions in interviews.

Why transactions should be isolated is that we need to know what problems are caused by concurrent transactions.

What problems can parallel transactions cause?

MySQL server allows multiple clients to connect, which means that MySQL can process multiple transactions at the same time.

When multiple transactions are processed at the same time, problems such as dirty read, non-repeatable read and phantom read may occur.

Next, I’ll give you an example of how these problems can occur.

Dirty read

If one transaction “reads” data modified by another uncommitted transaction, a “dirty read” has occurred.

Here’s an example.

Assume that two transactions A and B are processing at the same time. Transaction A first reads Kobayashi’s balance data from the database, and then performs the update operation. If at this moment, transaction A has not committed the transaction, and at this moment, transaction B also reads Kobayashi’s balance data from the database, Then transaction B reads the balance data after transaction A just updated it, even if there is no transaction committed.

Because transaction A has not committed the transaction, that is, it can be rolled back at any time. If transaction A has rolled back, then the data that transaction B just received is expired. This phenomenon is called dirty read.

Unrepeatable read

If the same data is read multiple times in a transaction, it indicates that non-repeatable read occurs.

Here’s an example.

Suppose there is A and B the two transactions in processing at the same time, the transaction is A start from xiaolin balance data read from the database, and then continue to execute code logic processing, in the process if the transaction B to update the data, and submit the transaction, so when A transaction A to read the data again, you will find the two read data is not consistent, This phenomenon is called unrepeatable reading.

Phantom read

If a transaction queries the number of records that meet the query conditions for several times, the number of records queried before and after the two times is different, it indicates that the phantom read phenomenon occurs.

Here’s an example.

Assume that two transactions, A and B, are being processed at the same time. Transaction A first searches the database for records with an account balance greater than 1 million and finds 5 records in total. Then transaction B also searches 5 records according to the same search criteria.

Next, transaction A inserts an account with A balance of more than 1 million and commits the transaction, at which point the number of accounts with A balance of more than 1 million becomes 6.

Then transaction B queries the records with the account balance greater than 1 million again, and the number of records queried at this time is 6. It finds that the number of records is different from that of the previous one, and it feels like an illusion. This phenomenon is called phantom reading.

What are the isolation levels for transactions?

As mentioned earlier, “dirty reads, unrepeatable reads, and phantom reads” can be encountered when multiple transactions are executed concurrently, and these phenomena can affect the consistency of transactions programmatically.

  • Dirty read: read uncommitted data from other transactions.
  • Non-repeatable read: Data read before and after is inconsistent.
  • Phantom read: The number of records read before and after is inconsistent.

The three phenomena are listed in order of severity:

The SQL standard proposes four isolation levels to circumvention these phenomena. The higher the isolation level, the lower the performance efficiency. These four isolation levels are as follows:

  • Read Uncommitted, when changes made by a transaction can be seen by other transactions before a transaction is committed.
  • Read Committed, when changes made by a transaction can only be seen by other transactions.
  • Repeatable read means that the data seen during a transaction is always the same as the data seen when the transaction is started. The default isolation level of MySQL InnoDB engine;
  • Serializable; A read/write lock is added to the record. When multiple transactions perform read/write operations on this record, if a read/write conflict occurs, the later transaction must wait for the previous transaction to complete before it can continue to execute.

In order of isolation level:

Depending on the isolation level, what can happen when concurrent transactions occur is different.

In other words:

  • Dirty reads, unrepeatable reads, and phantom reads may occur at the Read Uncommitted isolation level.
  • At the Read Commit isolation level, unrepeatable reads and phantom reads may occur, but dirty reads are not possible.
  • At the repeatable read isolation level, phantom reads may occur, but dirty reads and unrepeatable reads are not possible.
  • At the serialization isolation level, dirty reads, unrepeatable reads, and phantom reads are not possible.

So, to address dirty reads, upgrade to an isolation level above “read Commit”; To resolve the unrepeatable read problem, upgrade to the repeatable read isolation level.

However, upgrading the isolation level to “serialization” is not recommended to address illusionary phenomena, as this can lead to poor database performance when concurrent transactions occur.

The default isolation level of the InnoDB engine is repeatable reads, but it avoids phantom reads by locking “gaps” between records and the record itself with a next-key lock (a combination of row and gap locks), preventing other transactions from inserting new records between records.

Next, to take a concrete example of the four isolation levels, there is an account balance table with one entry:

Then there are two concurrent transactions. Transaction A will only query my balance, and transaction B will change my balance to 2 million. Here is the behavior of executing both transactions in chronological order:

The balance queried during transaction A execution may differ at different isolation levels:

  • Under the isolation level of “read uncommitted”, transaction B changes the balance, although the transaction is not committed, but the balance can be seen by transaction A, so the value of the balance V1 query in transaction A is 2 million, and the balance V2 and V3 are naturally 2 million.
  • Under the isolation level of “read Commit”, after transaction B changes the balance, the value of the balance V1 in transaction A is still 1 million because there is no transaction committed. After transaction B commits, the latest balance data can be seen by transaction A, so the amount of V2 and V3 are 2 million.
  • At the repeatable read isolation level, transaction A can only see the data when the transaction is started, so the value of balance V1 and balance V2 are 1 million. When transaction A commits the transaction, the value of balance V3 is 2 million.
  • Under the “serialization” isolation level, when transaction B changes the balance from 1 million to 2 million, it will be locked until transaction A commits, and then transaction B can continue to execute. From the perspective of A, The value of the balance V1 and V2 is 1 million, and the value of the balance V3 is 2 million.

How exactly are these four isolation levels implemented?

  • For “read uncommitted” isolation level transactions, since the data modified by the uncommitted transaction can be read, it is good to read the latest data directly;
  • For serialized isolation level transactions, parallel access is avoided by adding read/write locks;
  • For “Read Commit” and “repeatable Read” isolation level transactions, they are implemented through a Read View. The difference lies in the timing of the creation of the Read View. You can think of a Read View as a snapshot of the data, like a camera taking a picture of the landscape at a moment in time. The Read Commit isolation level means that a Read View is regenerated “before each statement is executed,” while the repeatable Read isolation level means that a Read View is generated “when a transaction is started” and then used throughout the transaction.

Note that executing the Start transaction command does not mean that a transaction has been started. MySQL > open transaction ();

  • The first: begin/start transaction command;
  • Second: start transaction with consistent snapshot command;

These two commands start the transaction at different times:

  • After the begin/start transaction command is executed, a transaction does not start. Only after this command is executed, the SQL statement that performs the operation of add, delete and query is the time when the transaction really starts.
  • If the start transaction with consistent snapshot command is executed, the transaction will start immediately.

How does Read View work in MVCC?

How does Read View work in MVCC?

We need to know two things:

  • The function of four fields in Read View;
  • Two transaction-related hidden columns in the clustered index record;

So what exactly is a Read View?

Read View has four important fields:

  • M_ids: refers to a list of active transaction ids ** in the current database when the Read View was created.
  • Min_trx_id: specifies the transaction with the smallest active transaction ID in the current database when the Read View is created, i.e., the minimum value of m_IDS.
  • Max_trx_id: this is not the maximum value of m_IDS, but the value that should be given to the next transaction in the current database when the Read View is created. This is the global maximum transaction ID + 1.
  • Creator_trx_id: Indicates the transaction ID of the transaction that created the Read View.

Knowing the fields of the Read View, we also need to know about the two hidden columns in the clustered index record.

Suppose I insert a record with a kobayashi balance of $1 million into the account balance table, and I draw the two hidden columns as well. The entire diagram of the record looks like this:

For database tables using InnoDB storage engine, its clustered index records contain the following two hidden columns:

  • Trx_id, when a transaction changes a cluster index record, the transaction ID of the transaction will be recorded in the trx_ID hidden column;
  • Roll_pointer, each time a change is made to a clustered index record, writes the old version of the record to the Undo log. The hidden column is then a pointer to each old version of the record, so it can be used to find the previous record.

After creating the Read View, we can divide the trx_id in the record into three cases:

When a transaction accesses a record, in addition to the fact that its own update record is always visible, there are several cases:

  • If the trx_id value recorded is less than that in the Read Viewmin_trx_idValue indicating that this version of the record is creating the Read ViewbeforeAlready committed transactions are generated, so that version is recorded against the current transactionvisible.
  • If the trx_id value is greater than or equal to that in the Read Viewmax_trx_idValue indicating that this version of the record is creating the Read ViewafterOnly started transactions are generated, so this version records the current transactioninvisible.
  • If the trx_id value is recorded in the Read Viewmin_trx_id 和 max_trx_idCheck whether trx_id is in m_IDS list:
    • If the trx_id is recordedin m_idsList, indicating that the active transaction that generated the record for that version is still active (no transaction has been committed yet), so the record for that version is critical to the current transactioninvisible.
    • If the trx_id is recordedNot in m_ids List, indicating that the active transaction that generated the record for that version has been committed, so the record for that version is critical to the current transactionvisible.

This behavior of controlling concurrent transactions accessing the same record through a “version chain” is called MVCC (Multi-version Concurrency Control).

How does repeatable read work?

The repeatable Read isolation level is that a Read View is generated when a transaction is started and then used throughout the transaction.

If transaction A (transaction ID 51) is started and transaction B (transaction ID 52) is started, then the Read View created by these two transactions is as follows:

The Read View for transactions A and B looks like this:

  • In the Read View of transaction A, its transaction ID is 51. Since it is the first transaction to start, the list of active transaction ids is 51. The smallest transaction ID in the list of active transaction ids is transaction A itself, and the next transaction ID is 52.
  • In the Read View of transaction B, its transaction ID is 52. Since transaction A is active, the list of active transaction ids is 51 and 52. The smallest active transaction ID is transaction A, and the next transaction ID should be 53.

Next, at the repeatable read isolation level, transaction A and transaction B perform the following operations in order:

  • Transaction B reads kobayashi’s account balance record and reads that the balance is 1 million;
  • Transaction A changed the account balance record of Kobayashi to 2 million and did not submit the transaction.
  • Transaction B reads Kobayashi’s account balance record and reads that the balance is still 1 million;
  • Transaction A commits the transaction;
  • Transaction B reads kobayashi’s account balance record and reads that the balance is still 1 million.

Next, I’ll analyze it with you.

Transaction B reads kobayashi’s account balance record for the first time. When it finds the record, it looks at the trx_id of the record and finds that trx_id is 50, which is less than min_trx_id (51) in transaction B’s Read View. This means that the transaction that modified this record was committed long before transaction B started, so this version of the record is visible to transaction B, that is, it is available to transaction B.

Then, transaction A uses update statement to modify this record (the transaction has not committed yet), and changes kobayashi’s balance to 2 million. At this time, MySQL will record the corresponding undo log, and form A version chain in A linked list way, as shown in the following figure:

As you can see in “Fields of Records” in the figure above, since transaction A modified the record, the previous record becomes the old version record, so the latest record and the old version record are linked by A linked list, and the latest record’s TRx_id is the transaction ID of transaction A (trx_id = 51).

Trx_id = 51; trx_id = 51; trx_id = 51; trx_id = 51; If the result is yes, it indicates that the record was modified by an uncommitted transaction, and transaction B will not read this version of the record. Instead, it goes down the undo log chain until it finds the first record whose trx_id is “less than” the value of min_trx_id in the Read View of transaction B, so transaction B can Read the record whose trx_id is 50. That’s kobayashi’s balance of $1 million.

Finally, after transaction A commits the transaction, transaction B reads the area record again based on the Read View created when the transaction was started, because the isolation level is “repeatable read-readable”. So, even if Transaction A changes the kobayashi balance to 2 million and commits the transaction, transaction B, when it reads the record for the third time, only reads the kobayashi balance of 1 million.

This is done in such a way that the records read at the repeatable Read isolation level during a transaction are those that were read before the transaction started.

How does read commit work?

The Read commit isolation level is that a new Read View is generated each time data is Read.

It also means that multiple reads of the same data during a transaction may produce inconsistent reads because another transaction may have modified the record and committed the transaction during that time.

So how does read commit isolation level work? Let’s go back to the previous example.

Suppose transaction A (transaction ID 51) is started, followed by transaction B (transaction ID 52), and the following operations are performed in sequence:

  • Transaction B reads the data (create Read View), kobayashi’s account balance is 1 million;
  • Transaction A modifies the data (the transaction has not been submitted) and changes the balance of Kobayashi’s account from 1 million to 2 million;
  • Transaction B reads the data (create Read View), kobayashi’s account balance is 1 million;
  • Transaction A commits the transaction;
  • Transaction B reads the data (create Read View), kobayashi’s account balance is 2 million;

So how do you do that? Let’s focus on the Read View that transaction B creates each time it reads data. The first two Read views created by transaction B are as follows:

When transaction B reads the data for the second time, it cannot read the data modified by transaction A (which has not committed transaction).

Transaction B (max_trx_id, max_trx_id, max_trx_id, max_trx_id, max_trx_id, max_trx_id) If the result is yes, it indicates that the record was modified by an uncommitted transaction, and transaction B will not read this version of the record. Instead, it goes down the undo log chain until it finds the first record whose trx_id is “less than” the value of min_trx_id in the Read View of transaction B, so transaction B can Read the record whose trx_id is 50. That’s kobayashi’s balance of $1 million.

Why can transaction B read the data changed by transaction A after transaction A commits?

After transaction A commits, the isolation level is “Read Commit”, so transaction B will re-create the Read View each time it reads data. At this time, transaction B will create the following Read View on the third Read:

When transaction B finds kobayashi, it will find that the trx_id of the record is 51, which is less than min_trx_id (52) in transaction B’s Read View. This means that the transaction that modified this record was committed before the Read View was created. So this version of the record is visible to transaction B.

Because at the Read commit isolation level, a transaction recreates the Read View each time it reads data, it is possible that multiple reads of the same data during a transaction may be inconsistent because another transaction may have modified the record and committed the transaction.

conclusion

Transactions are implemented in MySQL engine layer, our common InnoDB engine is to support transactions, the four characteristics of transactions are atomicity, consistency, isolation, persistence, we mainly talk about this is isolation.

When multiple transactions concurrently, can lead to dirty reads, non-repeatable reads, phantom read these questions, that in order to avoid these problems, puts forward four kinds of SQL isolation level, read uncommitted, read committed and repeatable read, serialization, increasing order from left to right isolation level, the higher the isolation level, means that the worse performance, The default isolation level of the InnoDB engine is repeatable reads.

To resolve dirty reads, upgrade the isolation level to the isolation level above the read commit, and to resolve non-repeatable reads, upgrade the isolation level to the isolation level above the repeatable read.

Upgrading the isolation level to serialization is not recommended for phantom reading, as this can lead to poor performance when the database is concurrently running. The default isolation level of the InnoDB engine is “repeatable reads”, but it avoids phantom reads by locking the “gaps” between records and the records themselves with a next-key lock (a combination of row and gap locks), preventing other transactions from inserting new records between records.

For “Read Commit” and “repeatable Read” isolation level transactions, which are implemented by **Read View **, the difference is when the Read View is created:

  • The “Read Commit” isolation level means that a new Read View is generated for each SELECT, which means that if the same data is Read multiple times during a transaction, the data may be inconsistent because another transaction may have modified the record and committed the transaction.
  • The repeatable Read isolation level is that a Read View is generated when a transaction is started and then used throughout the transaction, ensuring that the data Read during the transaction is the same as before the transaction was started.

These two isolation levels are implemented by comparing “fields in the transaction’s Read View” with “two hidden columns in the record” to control the behavior of concurrent transactions accessing the same record. This is called MVCC (Multi-version Concurrency Control).

At the repeatable read isolation level, normal SELECT statements are mVCC-based snapshot reads that are unlocked. And the select.. The for update statement is not a snapshot read, but a current read, that is, each read gets the latest version of the data, but it will apply a next-key lock to the read record.