Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL transactions and MVCC how to achieve isolation level", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "MySQL transactions and MVCC how to achieve isolation level" bar!

Database transactions introduce the four characteristics of transactions (ACID)

Atomicity: the smallest unit of work of a transaction, either completely successful or completely failed.

Consistency: after the transaction starts and ends, the integrity of the database is not compromised.

Isolation: different transactions do not affect each other. The four isolation levels are RU (read uncommitted), RC (read committed), RR (repeatable read), and SERIALIZABLE (serialization).

Durability: after a transaction is committed, the modification of the data is permanent and will not be lost even if the system fails.

Isolation level of the transaction

Read unsubmitted (Read UnCommitted/RU)

Also known as dirty reading, one transaction can read uncommitted data from another transaction. This isolation level is one of the most insecure because uncommitted transactions are rolled back.

Read submitted (Read Committed/RC)

Also known as non-repeatable reading, one transaction reads the modified data committed by another transaction, resulting in inconsistent results of reading the same data at different times of the current transaction.

For example, in the following example, you will find that the data queried by SessionA is different two times during a transaction. The reason is that transactions with the current isolation level of RC,SessionA can read the latest data committed by SessionB.

Occurrence time SessionASessionB1begin

2select * from user where id=1; (Zhang San)

three

Update user set name=' Li Si 'where id=1; (default implicit commit transaction) 4select * from user where id=1; (Li Si)

five

Update user set name=' second 'where id=1; (default implicitly committed transaction) 6select * from user where id=1; (second king)

Repeatable read (Repeatable Read/RR)

Also known as phantom reading, a transaction read can read the data submitted by other transactions, but under the RR isolation level, the current reading of this data can only be read once. In the current transaction, no matter how many times it is read, the data is still the value read for the first time, and it will not change because other transactions modify and commit this data after the first reading. Therefore, it is also an illusion, because the data read is not necessarily the latest data.

For example: when the data is read for the first time in SessionA, other subsequent transactions modify the committed data, which no longer affects the data value read by SessionA. This is repeatable.

Occurrence time SessionASessionB1begin

2select * from user where id=1; (Zhang San)

three

Update user set name=' Li Si 'where id=1; (default implicitly committed transaction) 4select * from user where id=1; (Zhang San)

five

Update user set name=' King 'where id=1; (default implicitly committed transaction) 6select * from user where id=1; (Zhang San)

Serialization (Serializable)

All database read or write operations are executed serially, under the current isolation level, only a single request is supported to be executed at the same time, and all operations need to be performed by the queue. So all the data at this isolation level is the most stable, but the performance is also the worst. The lock implementation of the database is a smaller-grained version of this isolation level.

Occurrence time SessionASessionB1begin

two

Begin;3

Update user set name=' Li Si 'where id=1;4select * from user where id=1; (waiting, wait)

five

Commit;6select * from user where id=1; (Li Si)

Transaction and MVCC principle problems caused by different transactions operating the same piece of data at the same time

Example:

Occurrence time SessionASessionB1begin

two

Begin;3

Query balance = 1000 yuan 4 query balance = 1000 yuan

five

The deposit amount is 100yuan, the modified balance is 1100 yuan, the cash withdrawal is 100yuan, and the revised balance is 900yuan

eight

Commit transaction (balance = 1100) 9 commit transaction (balance = 900)

Occurrence time SessionASessionB1begin

two

Begin;3

Query balance = 1000 yuan 4 query balance = 1000 yuan

five

The deposit amount is 100yuan, the modified balance is 1100 yuan, the cash withdrawal is 100yuan, and the revised balance is 900yuan

eight

Commit transaction (balance = 1100) 9 cancel transaction (balance restored to 1000 yuan)

The above two cases are the problems that may arise when multiple transactions operate at the same time for one piece of data, and the operation of a transaction will be overwritten and the data will be lost.

LBCC solves data loss

LBCC, lock-based concurrency control, Lock Based Concurrency Control.

Using the lock mechanism, when the current transaction needs to modify the data, the current transaction is locked. Only one transaction is allowed to modify the current data at the same time, and other transactions must wait for the lock to be released before they can operate.

MVCC solves data loss

MVCC, multi-version concurrency control, Multi-Version Concurrency Control.

The version is used to control the data problems in the case of concurrency. When the B transaction starts to modify the account and the transaction does not commit, when the A transaction needs to read the account balance, it will read the copy data of the account balance before the B transaction modification operation. However, if A transaction needs to modify the account balance data, it must wait for B transaction to commit the transaction.

MVCC makes the database read will not lock the data, ordinary SELECT requests will not be locked, and improve the concurrent processing ability of the database. With MVCC, the database can achieve isolation levels such as READ COMMITTED,REPEATABLE READ, and users can view the previous or previous historical versions of the current data, ensuring the I feature (isolation) in ACID.

MVCC implementation Logic of InnoDB

MVCC data saved by InnoDB storage engine

InnoDB's MVCC is achieved by keeping two hidden columns after each row of records. A transaction ID (DB_TRX_ID) that holds the row, and a rollback pointer (DB_ROLL_PT) that saves the row. Each time a new transaction is started, a new transaction id is automatically delivered. At the beginning of the transaction, the transaction id is put into the row transaction id affected by the current transaction. When querying, you need to compare the current transaction id with the transaction id of each row record.

Let's take a look at how MVCC works under the REPEATABLE READ isolation level.

SELECT

InnoDB checks each row of records against the following two criteria:

InnoDB only looks for rows whose version is earlier than the current transaction version (that is, the transaction number of the row is less than or equal to the transaction number of the current transaction), which ensures that the rows read by the transaction either exist before the transaction starts, or are inserted or modified by the transaction itself.

The deleted row important transaction ID judges that the version of the state before the start of the transaction is read, and only the records that meet the above two conditions can be returned as query results.

INSERT

InnoDB saves the current transaction number as the line version number for each newly inserted row.

DELETE

InnoDB saves the current transaction number as the row deletion identity for each row deleted.

UPDATE

InnoDB inserts a row of new records, saves the current transaction number as the row version number, and saves the current transaction number to the original row as the row deletion ID.

Save these two additional transaction numbers so that most reads are unlocked. This design makes it easy to read data, has good performance, and ensures that only rows that meet the standard will be read. The deficiency is that each row of records requires additional storage space, more line checking work, and some additional maintenance work.

MVCC only works under two isolation levels, REPEATABLE READ and READ COMMITIED. The other two isolation levels are not compatible with MVCC because READ UNCOMMITIED always reads the latest rows of data rather than rows that match the current transaction version. SERIALIZABLE locks all rows read.

The implementation of MVCC in mysql relies on undo log and read view.

Undo log

According to the behavior, undo log is divided into two types: insert undo log and update undo log

Insert undo log:

The undo log generated in the insert operation, because the insert operation record is only courseware for the current transaction itself and is not visible to other transactions, the insert undo log can be deleted directly after the transaction is committed without the need for purge operation.

The main task of purge is to delete the data that has already been mark del in the database, and also to recycle undo pages in bulk.

The initial state of the data in the database Insert:

Update undo log:

Undo log generated in a update or delete operation. Because of the impact on existing records, in order to provide a MVCC mechanism, update undo log cannot delete a transaction when it commits, but puts it on the history list when the transaction commits, waiting for the purge thread to perform the final delete operation.

When the data is first modified:

When another transaction modifies the current data for the second time:

In order to ensure that there is no conflict when writing their respective undo log during the concurrent operation of transactions, InnoDB uses a rollback segment to maintain the concurrent writing and persistence of undo log. The rollback segment is actually a way of organizing Undo files.

ReadView

For RU (READ UNCOMMITTED) isolation level, all transactions can read the latest values of the database directly, and SERIALIZABLE isolation level, all requests will be locked and executed synchronously. So there is no need to use Read View version control in both cases.

The implementation of the RC (READ COMMITTED) and RR (REPEATABLE READ) isolation levels is accomplished through the above version control. The core processing logic under the two isolated sectors is to determine which version of all versions is visible to the current transaction. In order to solve this problem, InnoDB adds the design of ReadView in the design. ReadView mainly contains the active read and write transactions in the current system. Put their transaction id into a list, and we name this list as m_ids.

The logic to determine whether the version chain data is seen when querying:

If the trx_id attribute value of the accessed version is less than the smallest transaction id in the m_ids list, the transaction that generated the version was committed before the ReadView was generated, so the version can be accessed by the current transaction.

If the trx_id attribute value of the accessed version is greater than the largest transaction id in the m_ids list, the transaction that generated the version is not generated until after the ReadView is generated, so the version cannot be accessed by the current transaction.

If the trx_id attribute value of the accessed version is between the largest transaction id and the minimum transaction id in the m_ids list, you need to determine whether the trx_id attribute value is in the m_ids list. If so, the transaction that generated the version when the ReadView was created is still active and the version cannot be accessed. If not, the transaction that generated the version when the ReadView was created has been committed and the version can be accessed.

For example:

ReadView under READ COMMITTED isolation level

A ReadView (m _ ids list) is generated before each data is read

Time Transaction 777Transaction 888Trasaction 999T1begin

T2

Begin;begin;T3UPDATE user SET name = 'CR7' WHERE id = 1

T4

...

T5UPDATE user SET name = 'Messi' WHERE id = 1

SELECT * FROM user where id = 1th T6commit

T7

UPDATE user SET name = 'Neymar' WHERE id = 1

T8

SELECT * FROM user where id = 1x T9

UPDATE user SET name = 'Dybala' WHERE id = 1

T10

Commit

T11

SELECT * FROM user where id = 1

Here, analyze the ReadView in the above case.

SELECT statement in the case of point-in-time T5:

Version chain of the current point in time:

At this time, the SELECT statement executes, and the version chain of the current data is as above. Because the current transaction 777 and transaction 888 are not committed, the list of ReadView of the active transaction at this time is m_ids: [777,888]. Therefore, the query statement will be based on the largest version data in the current version chain that is less than m_ids, that is, the query is Mbappe.

SELECT statement in case of point-in-time T8:

Version chain at the current time:

At this time, the SELECT statement executes, and the version chain of the current data is as above. Because the current transaction 777 has been committed and transaction 888 has not been committed, the list of ReadView of the active transaction at this time is m_ids: [888]. Therefore, the query statement will be based on the largest version data in the current version chain that is less than m_ids, that is, the query is Messi.

SELECT statement in the case of time point T11:

Version chain information at the current point in time:

At this time, the SELECT statement executes, and the version chain of the current data is as above. Because the current transaction 777 and transaction 888 have been committed, the list of ReadView of the active transaction at this time is empty, so the query statement will directly query the latest data of the current database, that is, the query is Dybala.

Summary: transactions that use the READ COMMITTED isolation level generate a separate ReadView at the beginning of each query.

ReadView under REPEATABLE READ isolation level

Generate a ReadView (m_ids list) when the data is read for the first time after the transaction starts

Time Transaction 777Transaction 888Trasaction 999T1begin

T2

Begin;begin;T3UPDATE user SET name = 'CR7' WHERE id = 1

T4

...

T5UPDATE user SET name = 'Messi' WHERE id = 1

SELECT * FROM user where id = 1th T6commit

T7

UPDATE user SET name = 'Neymar' WHERE id = 1

T8

SELECT * FROM user where id = 1x T9

UPDATE user SET name = 'Dybala' WHERE id = 1

T10

Commit

T11

SELECT * FROM user where id = 1

SELECT statement in the case of point-in-time T5:

Current version chain:

Then a ReadView is generated when the select statement is executed, and the content of the m_ids is: [777888], so the data queried according to the visible version of ReadView is Mbappe.

SELECT statement in case of point-in-time T8:

Current version chain:

At this point in the current Transaction 999 transaction. Since ReadView has been generated at the time point of T5, ReadView will only be generated once in the current transaction, so the m_ids: [777999] of T5 is still used at this time, so the query data is still Mbappe at this time.

SELECT statement in the case of time point T11:

Current version chain:

At this time, the situation is exactly the same as T8. Since ReadView has been generated at the time point of T5, ReadView will only be generated once in the current transaction, so the m_ids: [777999] of T5 is still used at this time, so the query data is still Mbappe at this time.

Thank you for reading, the above is the content of "MySQL transactions and MVCC how to achieve isolation level". After the study of this article, I believe you have a deeper understanding of the problem of MySQL transactions and MVCC how to achieve isolation levels, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.