Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "isolation and isolation level of MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Isolation and isolation level

When it comes to transactions, you must think of ACID (Atomicity, Consistency, Isolation, Durability, that is, atomicity, consistency, isolation, persistence). Today we are going to talk about Isolation, that is, isolation.

Atomicity: all operations in a transaction are either completed or not completed, and do not end at some point in the middle. An error occurs during the execution of a transaction and is Rollback back to its state before the transaction starts, as if the transaction had never been executed.

Consistency: before the transaction starts and after the transaction ends,. This means that the data written must be completed and the integrity of the database is not compromised in accordance with all the preset rules, including the accuracy and concatenation of the data, and the subsequent database can spontaneously complete the scheduled work.

Isolation: the ability of a database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent data inconsistencies caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including read uncommitted (Read uncommitted), read commit (read committed), repeatable read (repeatable read) and serialization (Serializable).

Persistence: after the end of the transaction, the data will not be lost, even if the system modification is a permanent failure.

When multiple transactions on the database are executed at the same time, the problems of dirty read, non-repeatable read and phantom read may occur. In order to solve these problems, there is the concept of isolation level.

Isolation specific meaning dirty read B transaction change data has not been committed, A transaction has been seen and used. If the B transaction is rolled back, then the A transaction has made a mistake and the key point of non-repeatable reading is modification: the same condition, the data you have read, read it again and find that the value is different. You only need to lock the records that meet the conditions. The focus of fantasy reading is to add or delete the same conditions. The number of records read for the first time and the second time is not the same. Lock records that meet the conditions and similar ones.

Before you talk about the level of isolation, you need to know that the more tightly isolated you are, the less efficient you will be. So most of the time, we have to find a balance between the two. The transaction isolation levels of the SQL standard from low to high include:

Read uncommitted: means that changes made by a transaction can be seen by other transactions before it is committed.

Read commit: means that the changes made by one transaction will not be seen by other transactions until it is committed.

Repeatable (default level): refers to the data seen during the execution of a transaction, which is always consistent with the data seen when the transaction is started. Of course, under the repeatable readable isolation level, uncommitted changes are not visible to other transactions.

Serialization: as the name implies, for the same row of records, write locks are added to write and read locks are added. A read-write lock is added to a record, and in the event of a conflict, the later accessed transaction must wait for the previous transaction to complete before it can continue execution.

Say the above meaning in vernacular:

Read uncommitted: transactions that have been changed by others have not yet been committed, and I can read them in my transactions.

Read committed (Oracle default): a transaction that someone else has changed the data has been committed before I can read it in my transaction.

Readable (MySQL default): transactions that have been altered by others have been committed and I do not read them in my transaction to ensure consistency in repeated reads.

Serial: no one else can change the data before my transaction is committed.

For these four isolation levels, the parallel performance decreases and the security increases in turn.

Read submission and repeatable readings are difficult to understand, so I'll use an example to illustrate these isolation levels. Assuming that there is only one column in the data table T, where the value of a row is 1, here is the behavior of executing two transactions in chronological order.

Mysql > create table T (c int) engine=InnoDB;insert into T (c) values (1)

Let's take a look at the different return results of transaction A under different isolation levels, that is, what are the return values of V1, V2, and V3 in the figure.

At this time, transaction B has not yet committed, but the result has already been seen by A. Updates for V1 commit 2, V2 commit 2, and V3 commit transaction B are not seen by A until they are committed. The data seen by V1, V2, V3 read 2 repeatable transactions must be consistent during execution. V1 serialization, V2 serialization, and V3 serialization are locked when transaction B executes from 1 to 2. Transaction B can not continue execution until transaction A commits. V1, V2, V3, V3

In implementation, a view will be created in the database, and the logical result of the view will prevail when accessing it.

Read the latest value on the record directly returned under the uncommitted isolation level, without the concept of view

At the read commit isolation level, this read-view is created at the beginning of each SQL statement execution.

At the repeatable read isolation level, this view is created at the start of the transaction and is used throughout the existence of the transaction.

Under the serialization isolation level, lock is directly used to avoid parallel access.

We can see that the database behavior is different under different isolation levels. The default isolation level of Oracle database is actually read commit, so for some applications that migrate from Oracle to MySQL (the default is repeatable), in order to ensure the consistency of database isolation level, you must remember to set the isolation level of MySQL to read commit.

Mysql > show variables like 'transaction_isolation' +-+ | Variable_name | Value | +-+-+ | transaction_isolation | READ-COMMITTED | + +-+

To sum up, existence is reasonable, and any isolation level has its own usage scenario, which you should decide according to your own business situation. I think you might ask when do you need a scene that can be read repeatedly? Let's take a look at a case of data proofing logic.

Suppose you are managing a personal bank account. One table has the balance at the end of each month, and one table has the details of the bill. At this time, you need to do data proofreading, that is, to determine whether the difference between last month's balance and the current balance is consistent with this month's bill. You must hope that in the process of proofreading, even if a user has a new transaction, it will not affect the result of your proofreading.

At this point, it is convenient to use the repeatable read isolation level. The view at the start of a transaction can be considered static and unaffected by other transaction updates.

Implementation of transaction isolation

Now that we understand the isolation level of transactions, let's take a look at how transaction isolation is implemented. Here we expand the instructions that can be read repeatedly.

In MySQL, virtually every record is updated with a rollback operation recorded at the same time. The latest value on the record, through the rollback operation, can get the value of the previous state.

If a value is changed from 1 to 2, 3, 4 in order, there will be a record similar to the following in the rollback log.

The current value is 4, but transactions started at different times will have different read-view when querying this record. As can be seen in the figure, in views A, B, and C, the values of this record are 1, 2, and 4 respectively. There can be multiple versions of the same record in the system, that is, multi-version concurrency control (MVCC) of the database. For read-view A, to get 1, you must perform all the rollback operations in the diagram to get the current value in turn.

At the same time, you will find that even if there is another transaction changing from 4 to 5, this transaction will not conflict with the corresponding transactions of read-view A, B, and C.

You must ask, the rollback log can not be kept all the time, when will it be deleted? The answer is to delete only when you don't need it. That is, the system determines that the rollback logs will be deleted when no more transactions are needed.

When will you no longer need it? This is when there is no read-view in the system that is older than this rollback log.

Based on the above instructions, let's discuss why you are advised not to use long transactions as much as possible.

Long transactions mean that there will be very old transaction views in the system. Because these transactions may access any data in the database at any time, the rollback records that may be used in the database must be retained before the transaction is committed, which will lead to a large amount of storage space.

In MySQL 5.5 and previous versions, the rollback log was placed in the ibdata file with the data dictionary, and even if the long transaction was eventually committed and the rollback segment was cleaned up, the file did not get smaller. I've seen libraries with only 20GB for data and 200GB for rollback segments. In the end, the entire library had to be rebuilt in order to clean up the rollback segment.

In addition to the impact on the rollback segment, long transactions also take up lock resources and may bring down the entire library, which we will expand later when we talk about locks.

The way the transaction is started

As mentioned earlier, long-term transactions have these potential risks, and I certainly advise you to avoid them as much as possible. In fact, most of the time, business developers do not intend to use long transactions, usually due to misuse. MySQL can start transactions in the following ways:

Explicitly start a transaction statement, begin or start transaction. The accompanying commit statement is commit and the rollback statement is rollback.

Set autocommit=0, this command turns off the autocommit of this thread. This means that if you execute only one select statement, the transaction starts and does not commit automatically. This transaction persists until you actively execute commit or rollback statements, or disconnect.

Some client connection frameworks default to execute a set autocommit=0 command after a successful connection. This causes the next query to be in the transaction, and if it is a long connection, it leads to an unexpectedly long transaction.

Therefore, I would recommend that you always use set autocommit=1 to start the transaction with explicit statements.

But some developers will struggle with the problem of one more interaction. For a business that needs to use transactions frequently, the second way is that each transaction does not need to execute begin actively at the beginning, which reduces the number of interactions of statements. If you have the same concern, I suggest you use commit work and chain syntax.

In the case of autocommit 1, transactions that are explicitly started with begin commit transactions if commit is executed. If you execute commit work and chain, you commit the transaction and start the next transaction automatically, which saves the overhead of executing the begin statement again. At the same time, the advantage is to know clearly whether each statement is in a transaction from the point of view of program development.

You can query the innodb_trx table of the information_ schema library for long transactions, such as the following statement, to find transactions with a duration longer than 60s.

Select * from information_schema.innodb_trx where TIME_TO_SEC (timediff (now (), trx_started)) > 60 how to avoid the impact of long transactions on business

Now that you know that long transactions should be avoided in the system, if you are the head of business development and database, what can you do to avoid or deal with this situation?

This problem can be seen from the application developer side and the database side.

First of all, from the perspective of the application development side:

Confirm that set autocommit=0 is being used. This confirmation can be carried out in the test environment, open the general_log of MySQL, and then run any business logic to confirm it through the log of general_log. If the general framework sets this value, it will also provide parameters to control the behavior, and your goal is to change it to 1.

Confirm that there are unnecessary read-only transactions. Some frameworks are accustomed to using begin/commit to box any statement first. I have seen some businesses that do not need this, but also put several select statements into the transaction. This kind of read-only transaction can be removed.

When the business connects to the database, according to the estimate of the business itself, the maximum execution time of each statement is controlled by the SET MAX_EXECUTION_TIME command to avoid the unexpected execution of a single statement for too long. Why did you have an accident? Such cases will be mentioned in subsequent articles)

Secondly, from the database side:

Monitor information_schema.Innodb_trx table, set long transaction threshold, alarm / or kill when exceeding

Percona's pt-kill is a good tool and is recommended.

During the business function testing phase, all the general_log is required to be output, and the log behavior is analyzed to find problems in advance.

If you are using MySQL 5.6 or later, set innodb_undo_tablespaces to 2 (or greater). If a large transaction does occur and the rollback segment is too large, it is more convenient to clean up after setting it up.

This is the end of the content of "isolation and isolation level of MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.