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

This article will explain in detail how to achieve repeatable operations in MySQL. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The structure of the account table (excluding other fields) is as follows:

CREATE TABLE `roomt` (`id` bigint (20) NOT NULL, `balance` bigint (20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

When deducting the balance, the word order of sql is as follows:

Update the word order of balance sql

Ps: see the word order above, is there a small question mark? Why did you query the same number of times?

In fact, these SQL word orders are not in the same method, and some methods are extracted and reused, so some of the same query results can not be passed down, so they have to be queried from the database again.

In order to prevent concurrent updates of the balance, write locks are used to lock the row record at T3 moment. If the lock is successful, if other threads also execute to T3, it will be blocked until the previous thread transaction is committed.

T5 moment, go to the next method, get the account balance again, then compare the balance with the deduction amount in the Java method, and if the balance is sufficient, perform the update operation at T7 time.

There seems to be nothing wrong with the word order of SQL above, but in fact, the account system has been in production for a long time and there is no problem. But here needs to say a premise, the system database is Oracle.

But from the above table structure, we can know that this time the database has been switched to MySQL, and no other code and configuration of the system have been modified (there are minor changes in sql).

In this case, multi-deduction of the balance occurs in the concurrent execution, that is, the actual balance is obviously less than the deduction amount, but the balance update operation is done, which finally causes the balance to become negative.

Let's reproduce the concurrency situation, assuming that two transactions are executing the word order, as shown in the figure.

Note: the database uses MySQL, and the default transaction isolation level is RR. The database is recorded as id=1 balance=1000, assuming that only these two transactions are executing at that time.

Readers can first think about how much is left at the moment of t2meme3, T4, T5, 6, and T11.

The answers under transaction isolation level RR are posted below.

The query result for transaction 1 is:

T2 (1pc1000)

T4 (1pc1000)

T6 (1pc1000)

The query result for transaction 2 is:

T3 (1pc1000)

T5 (1900)

T11 (1pc1000)

Did it turn out the same way you thought?

Then change the transaction isolation level to RC, and also think about the balance of T2, T3, 4, T5, 6, and T11 moments.

Post the answer under transaction isolation level RC again.

The query result for transaction 1 is:

T2 (1pc1000)

T4 (1pc1000)

T6 (1pc1000)

The query result for transaction 2 is:

T3 (1pc1000)

T5 (1900)

T11 (1900)

There should be no problem with the query results of transaction 1, and the main question point should be transaction 2. Why is the result different when you change the transaction isolation level?

Let's take a look at the relevant principles of MySQL with questions, and you will understand all this after reading it.

MVCC

Consistent view

Snapshot read and current read

MVCC

Let's first look at the next simple example.

Transaction isolation levels are RR, id=1 balance=1000

Update timing

Transaction 1 updates the id=1 record balance to 900, and then transaction 2 queries the result of the row record at T5, which obviously should be id=1 balance=1000.

If T5 queries the latest result id=1 balance=900, it reads the uncommitted data from transaction 1, which obviously does not meet the current transaction isolation level.

From the example above, you can see that there are two versions of the record of id=1, transaction 1 as balance=1000 and transaction 2 as balance=900.

For the above functions, MySQL uses MVCC mechanism to realize the functions.

MVCC:Multiversion concurrency control, multi-version concurrency control. Excerpt an explanation of the monthly report of Taobao database:

Multi-version control: refers to a technique that improves concurrency. In the earliest database system, only read and write can be concurrent, read and write have to be blocked. After the introduction of multiple versions, only writes block each other, and the other three operations can be done in parallel, which greatly improves the concurrency of InnoDB. In the internal implementation, unlike Postgres, which implements multiple versions on data rows, InnoDB is implemented in undolog, and the historical version of the data can be recovered through undolog. The recovered historical version of the data can be read by the user (according to the definition of isolation level, some read requests can only see the older version of the data), or the data on the data page can be overwritten when rolled back. Within InnoDB, a global array of active read and write transactions is recorded, which is mainly used to determine the visibility of transactions.

You can see that MVCC is mainly used to improve concurrency and can also be used to read older versions of data.

Before we learn the principle of MVCC, we first need to understand the MySQL record structure.

Line record

As shown in the figure above, in addition to the real data, there are three hidden fields for recording additional information in one row of the account table.

DB_TRX_ID: transaction id.

DB_ROLL_PTR: roll back the pointer to undolog.

ROW_ID: OK id, it has nothing to do with this time.

Each transaction in MySQL InnoDB will have a unique transaction ID, which will be applied to InnoDB's transaction system at the beginning of the transaction and will be incremented in strict order.

Each time the transaction updates the data, a new version of the data is generated, and then the current transaction id is assigned to the DB_TRX_ID of the current record. And the data update record (1GOOOOOOOOOUMUFUM-> 1900) will be recorded in the undo log (rollback log), and then point to the und olog using the currently recorded DB_ROLL_PTR.

In this way, MySQL can find undolog through DB_ROLL_PTR and deduce the contents of the previous version record.

The search process is as follows:

Search process

If you need to know the V1 version record, first find the undolog based on the DB_ROLL_PTR of the current version V3, and then calculate the previous version V2 based on the content of the undolog. And so on, the V1 version record was finally found.

V1MagneV2 is not a physical record, it doesn't really exist, it just has logical meaning.

Multiple versions of a row of data records may exist at the same time, but not all records are visible to the current transaction. Otherwise, the above T5 may query the latest data. So when looking up the data version, the MySQL must determine whether the data version is visible to the current transaction.

Consistent view

MySQL creates a consistency view (not immediately) after the transaction starts, in which all active transactions (transactions that have not yet been committed) are saved.

Assume that the current transaction saves an array of active transactions as shown in the following figure.

View array

When determining whether the version is visible to the current transaction, it is based on the following rules:

If the version transaction id is less than the minimum value of the current active transaction id array, for example, the version id is 40, less than the minimum value of 45 of the active array. This means that the current version of the transaction has been committed, and the current version is visible to the current transaction.

If the version transaction id is greater than the maximum value of the current active transaction array, for example, the version transaction id is 100, greater than the array maximum transaction id 90. Indicates that this version is generated after the current transaction is created, so this version is not visible to the current transaction.

If version transaction id is one of the currently active array transactions, for example, version transaction id is 56. Represents that the transaction to which the record version belongs has not been committed, so the version is not visible to the current transaction.

If the version transaction id is not one of the currently active array transactions, but the transaction id is at one of the minimum and maximum values of the active array, such as transaction ID 57. Represents that the current record transaction has been committed, so this version is visible to the current transaction.

If the version transaction id is the current transaction id, it means that the data in the row is changed by the current transaction and is, of course, visible.

4 this rule may be relatively round, combined with the above picture is easier to understand.

The above judgment rules may be abstract and incomprehensible, so let's explain them in vernacular again:

The record version generated by the uncommitted transaction is not visible.

The committed transaction generation record version is visible before the view is generated.

After the view is generated, the new transaction generation record version is not visible.

Self-transaction updates are always visible.

Consistency views are generated only under RR and RC, and for RR, they are generated at the time of the first query statement. In the case of RC, each query statement regenerates the view.

Current read and snapshot read

MySQL uses the MVCC mechanism to read data from previous versions. These old version records are not and cannot be modified, just like snapshots. So we call this kind of query snapshot read.

Of course, not all queries are snapshot reads, select.... Locked queries such as for update/ in share mode only query the latest version of the current record. We call this query the current read.

Analysis of problems

After explaining the principle, let's go back and analyze the reasons for the above query results.

Here we will post the above answer again.

The transaction isolation level is RR,t2,t3. Because of the query statement, the consistency views of the two transactions are established.

T4 moment, because transaction 1 uses select.. For update puts a lock on the id=1 line and gets the latest results. At T5 time, because the row is locked, transaction 2 must wait for transaction 1 to release the lock before continuing execution.

T6 cannot read the committed version of other transactions according to the consistent view, so the data has not changed. T8 time balance deduction 100pm T9 moment commit transaction.

The latest version is recorded as id=1 balance=900 at this time.

Because transaction 1 transaction has been committed, the row lock is released, and T5 successfully acquires the lock. Because T5 is the current read, the result of the query is the latest version of the data (1900).

The point is that the latest version of the current record is (1900), but the latest version of transaction id is an uncommitted transaction after transaction 2 was created and is in the active transaction array. So the latest record version is not visible to transaction 2.

There is no way to read the previous version record (1j1000) only according to undolog. This version record happens to be visible to transaction 2, so the record of T11 is (1J1000).

When we change the transaction isolation level to RC, the consistency view is regenerated each time. So T11 rebuilds the consistency view at all times, when transaction 1 is committed and the current version of the record is visible to transaction 2, so the result of T11 will be (1900).

On how to achieve repeatable read operations in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.