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

This article introduces the relevant knowledge of "introduction to the transaction 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!

What is a transaction?

Database transaction (transaction for short) is a logical unit in the execution process of database management system, which is composed of a limited sequence of database operations. Wikipedia

The concept of transactions does not seem difficult, but there are a few points to note:

1. First of all, the transaction is to ensure that a set of database operations either succeed or fail.

2. In MySQL, transaction support is implemented at the engine level

3. Not all engines support transactions. For example, MyISAM does not support transactions, but InnoDB does.

Today, our protagonist is isolation, which means that when multiple users operate the database concurrently, the database opens different transactions for each user. These transactions do not interfere with each other and are isolated from each other.

Why do you need isolation?

If transactions are not isolated from each other, the following problems may occur.

1. Dirty reading

Dirty read, to put it simply, is that one transaction reads uncommitted data from another transaction during processing.

This unsubmitted data is called dirty data. Operations based on dirty data are likely to be incorrect.

Remember the dirty page we mentioned in the last section? This kind of temporary processing of unsubmitted, are all "dirty".

Give an example

However, if the transaction is not successfully committed, eventually all operations will be rolled back, and the editor will only see a penny in the mirror. For example, if you praise the editor for a penny, the whole business requires two steps:

① added a penny to the editor's account, and when the editor saw it, he felt very pleased.

②, your account is minus one penny.

2. Non-repeatable

Unrepeatable read (non-repeatable read) means that within the scope of a transaction, some data is queried many times and different results are obtained.

Between the two reads in the first transaction, the data read by the first transaction may be different because of the modification of the second transaction.

Give an example

Then in the previous example, suppose you really gave the editor a penny, and the editor was overjoyed to prepare to withdraw cash. After checking, he found that he really had a penny more.

At the same time, before I successfully withdrew the cash, the editor's wife had already taken away the penny in advance. The editor checked the accounts again and found that there was no money left.

A little confused about dirty reading and unrepeatable reading?

The difference between the two is that dirty reading is that one transaction reads data that is not committed by another transaction, while unrepeatable reading is reading data committed by other transactions.

In fact, in some cases, unrepeatable reading is not a problem, for example, during the editor's withdrawal, a penny is taken away by his wife, which is not a problem!

Dirty reading can be avoided by setting the isolation level.

3. Illusory reading

Phantom read is a phenomenon that occurs when a transaction is not executed independently.

For example, transaction T1 modifies a data item of all rows in a table from "1" to "2", and transaction T2 inserts a row of data with the data item "1" into the table and submits it to the database.

On the other hand, if the user operating transaction T1 looks at the data that has just been modified, he will find that the data is still 1? In fact, this line is added from transaction T2, which is like a hallucination, which is a hallucination.

Give an example

In fact, the above explanation is already an example, but it is still necessary to give an example.

For example, the editor is ready to withdraw a penny of your reward, and when it is finished, other enthusiastic netizens have rewarded a penny. The editor has a look, it is clear that it has been taken out, how can there be another penny?

At this time, the editor thought it was like a dream. I think it could also be called "dream reading", .

Both phantom reading and unrepeatable reading read another committed transaction (this is different from dirty reading), except that the unrepeatable query is the same data item, while phantom reading is for a batch of data as a whole (such as the number of data).

Isolation level of the transaction

In order to solve the problems that may arise above, we need to set the isolation level, that is, what rules are used to isolate transactions and to what extent transactions are isolated.

First of all, you need to understand that the greater the degree of isolation, the lower the execution efficiency of the transaction.

ANSI/ISO SQL defines four standard isolation levels:

① Serializable (serialization): the most expensive but reliable transaction isolation level.

"write" will add "write lock", "read" will add "read lock". When there is a read-write lock conflict, the later accessed transaction must wait for the previous transaction to complete before it can continue execution.

Transactions are 100% isolated, which can avoid dirty reading, unrepeatable reading and phantom reading.

② Repeatable read (repeatable, default level): reading the same range of data multiple times returns a snapshot of the first query, even if other transactions have updated the data. The data that the transaction sees during execution must be consistent.

However, if this transaction reads records in a certain range, and other transactions insert new records in that range, magic rows will occur when the previous transaction reads the records in that range again, which is called phantom reading.

The occurrence of dirty and unrepeatable reading can be avoided. But there may be hallucinations.

③ Read committed (read committed): ensures that one thing is committed before it can be read by another transaction. Another transaction cannot read the uncommitted data of the transaction.

Dirty reading can be avoided, but it may result in unrepeatable reading.

The default level for most databases is Read committed, such as Sql Server, Oracle.

④ Read uncommitted (read uncommitted): the lowest transaction isolation level in which changes made by a transaction can be seen by other transactions before it is committed.

There is no guarantee under any circumstances.

Isolation level

The following figure is a good example of what transaction B can read under the four transaction isolation levels.

Do you still look a little confused? Let's give another example.

The two transactions of Amemery B do some operations respectively. During the operation, check the values of variables under different isolation levels:

If the isolation level is serialized, it will be locked when transaction B executes "change 1 to 2". Transaction B can not continue execution until transaction A commits.

Sum up again

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

Read committed: I can only read it in my transaction if someone else has changed the data.

Repeatable: a transaction in which someone else changed the data has been committed, and I do not read it in my transaction.

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.

Generally speaking, the higher the transaction isolation level, the more able to ensure the integrity and consistency of the data, but the price is the low efficiency of concurrent execution.

Implementation of isolation level

The mechanism of transaction is concurrent version control (MVCC) implemented through read-view. Different transaction isolation levels create views at different points in time.

Repeatable readability is the reconstruction of the read view for each transaction, which is used throughout the existence of the transaction.

Read submitted is a read view created by each SQL, created at the beginning of each SQL statement execution. The isolation scope is limited to this SQL statement.

Read unsubmitted is not created, and directly return the latest value on the record

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

The view here can be understood as a copy of the data. each time a view is created, a copy of the currently persisted data is created and then read directly from the copy, thus achieving the effect of data isolation.

Implementation of isolation level

Each time we modify, we do not operate directly on the row data.

For example, if we set the A property of a row with an id of 3 to 10, we do not directly modify the data in the table, but add a new row.

At the same time, the data table actually has some hidden attributes, such as the transaction id for each row, so there may be multiple versions of each row of data, each transaction that has modified it will have a row, and there will be an associated undo log indicating what the original data of the operation is, which can be used for rollback.

Then why are you doing this?

Because if we modify the data directly, then other transactions will not be able to use the original value, violating the consistency of the transaction.

So what exactly does a transaction return when reading a row of data?

Depending on the isolation level, if it is Read Committed, the commit value of the latest transaction is returned, so the value of the uncommitted transaction modification is not read, which is the principle of the Read Committed implementation.

At the Read Repeatable level, only commit values for transactions that started earlier than the current transaction, and delete values for deleted transactions later than the current transaction can be returned. This is actually the MVCC way.

Undo log

The old version of data is stored in undo log. Suppose you modify the row data of id=2 in the table and change Name='B' to Name=' B2', then the undo log will be used to store the records of Name='B'. If there is an exception in this modification, you can use undo log to implement the rollback operation to ensure transaction consistency.

When an old transaction needs to read data, in order to read the old version of the data, it needs to follow the undo chain to find records that satisfy its visibility. When the version chain is long, you can usually think of this as a time-consuming operation.

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.

In addition, the undo log in the rollback segment is divided into: insert undo log and update undo log:

Insert undolog: the undolog generated by a transaction against a new insert record is only needed when the transaction is rolled back and can be discarded immediately after the transaction is committed. Who has a visibility requirement for the data just inserted! )

Update undo log: the undo log generated when a transaction performs delete and update operations on a record. It is necessary not only for transaction rollback, but also for consistency read, so it cannot be deleted casually. Only when the log record is not involved in the snapshot used by the database, the corresponding rollback log will be deleted by the purge thread.

When will it be deleted?

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.

This is when there is no read-view in the system that is older than this rollback log.

Long transaction

Intuitively feel that a transaction takes a long time can not be finished, is a long transaction, referred to as a long transaction (Long Transaction).

Long transactions are often encountered by database users and are very troublesome. Long transactions need to be carried out properly, such as improper handling may cause the collapse of the database and bring unnecessary losses to users.

According to the above discussion, 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 undo log that may be used in the database must be retained before the transaction is committed, which will result in 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.

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.

Therefore, we should try our best to avoid long-term business.

This is the end of the introduction to the transaction 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.