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

This article introduces the relevant knowledge of "what is the meaning of transaction isolation in 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!

A transaction is to ensure that a set of database operations either succeed or fail. In MySQL, transaction support is implemented at the engine level, but not all engines support transactions. For example, MySQL's native MyISAM engine does not support transactions.

I. the characteristics of the transaction

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 the transaction and is rolled back to the state before the start of the transaction, as if the transaction had never been executed

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

Isolation: the ability of a database to allow multiple concurrent transactions to read, write and modify data at the same time. Isolation can prevent data inconsistency due to cross execution when multiple transactions are executed concurrently.

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

II. Isolation level

1. When multiple transactions on the database are executed at the same time, the problems of dirty reading, unrepeatable reading and phantom reading may occur.

Dirty reading: transaction B reads the data that has not been committed by transaction A.

Unrepeatable read: one transaction reads update data committed in another transaction

Phantom / Phantom read: one transaction reads insert data committed in another transaction

two。 The isolation levels of transactions include read uncommitted, read commit, repeatable read, and serialization

Read uncommitted: when a transaction is not committed, its changes can be seen by other transactions

Read commit: after a transaction commits, its changes will be seen by other transactions (resolve dirty reads, Oracle default isolation level)

Repeatable: the data seen during the execution of a transaction is always the same as that seen when the transaction is started, and uncommitted changes are not visible to other transactions (resolve dirty and non-repeatable reads, MySQL default isolation level)

Serialization: for the same row of records, write locks are added for writes and read locks are added for reads. when there is a read-write lock conflict, the later accessed transaction must wait for the previous transaction to be completed before it can continue execution (resolve dirty, non-repeatable and phantom reads)

Security is submitted in turn, performance is degraded in turn.

3. Suppose there is only one column in datasheet T, where the value of a row is 1.

Create table T (c int) engine=InnoDB;insert into T (c) values (1)

The following is the behavior of executing two transactions in chronological order:

If the isolation level is read uncommitted, V1 is 2. At this time, transaction B has not yet committed, but the result has been seen by A. V2 and V3 are both 2.

If the isolation level is read commit, then V1 is 1 and V2 is 2. The update of transaction B can not be seen by A until it is committed. V3 is also 2.

If the isolation level is repeatable, then V1 and V2 are 1 and V3 is 2. The reason why V2 is 1 follows that the data seen by the transaction during execution must be consistent.

If the isolation level is serialized, the V1 and V2 values are 1, and V3 is 2.

In implementation, a view will be created in the database, and the logical result of the view will prevail when accessing it. 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. At the read commit isolation level, this view is created at the beginning of each SQL statement execution. Read the latest value on the record directly under the uncommitted isolation level, without the concept of view, while under the serialization isolation level, lock is directly used to avoid parallel access.

III. Implementation of transaction isolation (taking repeatable readability as an example)

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

If a value is changed sequentially from 1 to 2, 3, 4, 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-viewA, to get 1, you must perform all the rollback operations in the figure to get the current value at once.

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.

The system will judge that when no transactions need to use these rollback logs, the rollback logs will be deleted

IV. How to start a transaction

MySQL can start transactions in the following ways:

Displays the startup transaction statement, begin or start transaction. The 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 only one select statement is executed, the transaction starts and the transaction is not automatically committed. The transaction persists until the commit or rollback statement is actively executed, or the connection is disconnected

It is recommended to use set autocommit=1 to start the transaction by displaying the statement

You can query long transactions in the innodb_trx table in the information_schema library. The following statement queries transactions with a duration of more than 60s

Select * from information_schema.innodb_trx where TIME_TO_SEC (timediff (now (), trx_started)) > 60 V. Transaction isolation or non-isolation

The following is an initialization statement for a table with only two rows:

Mysql > CREATE TABLE `t` (`id` int (11) NOT NULL, `k` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;insert into t (id, k) values (1), (2)

The execution flow of transaction A, B, C is as follows, using repeatable read isolation level.

Begin/start transaction command: not the starting point of a transaction, the transaction really starts when the first statement that operates on the InnoDB table after them is executed, and the consistency view is created when the first snapshot read statement is executed.

Start transaction with consistent snapshot command: start a transaction immediately, and the consistency view is created when this command is executed

Following the flow in the figure above, the value of k found by transaction B is 3, while the value of k found by transaction An is 1.

1. How does a snapshot work in MVCC?

Under the repeatable readable isolation level, a snapshot is taken when the transaction starts. This snapshot is based on the entire library, so how is this snapshot implemented?

Each transaction in InnoDB has a unique transaction ID, called transaction id. It applies to the transaction system of InnoDB at the beginning of the transaction, which is strictly increased in the order of application.

There are also multiple versions of each row of data. Each time a transaction updates data, a new data version is generated and the transaction id is assigned to the transaction ID for that data version, recorded as row trx_id. At the same time, the old data version should be retained, and in the new data version, information can be obtained directly. In other words, there may be multiple versions of a row of records in a data table, each with its own row trx_id

The following figure shows the status of a record after it has been continuously updated by multiple transactions:

The undo log (rollback log) generated by the statement update is which dotted arrow in the above figure, while V1, V2, and V3 are not physically real, but are calculated according to the current version and undo log every time needed. For example, when V2 is needed, it is calculated by performing U3 and U2 in turn through V4.

According to the definition of repeatable readability, when a transaction starts, you can see the results of all transactions that have been committed. But then, during the execution of this transaction, updates from other transactions are not visible to it. In implementation, InnoDB constructs an array for each transaction to hold the transaction startup moment, currently starting ID for all transactions that have not yet been committed. The minimum value of transaction ID in the array is marked as low water level, and the maximum value of transaction ID that has been created in the current system plus 1 is recorded as high water level. This array of views and the high water level form a consistent view of the current transaction. The visibility rules of the data are based on the comparison between the row trx_id of the data and this consistent view.

This view array divides all the row trx_id into several different cases.

There are several possibilities for a data version of row trx_id at the start of the current transaction:

1) if it falls in the green section, it indicates that the version is a committed transaction or is generated by the current transaction itself, and this data is visible

2) if it falls on the red part, it means that this version is generated by a transaction started in the future and must not be visible.

3) if it falls in the yellow part, there are two situations.

If row trx_id is in the array, it means that this version is generated by a transaction that has not been committed and is not visible.

If row trx_id is not in the array, this version is generated by a committed transaction. You can see

InnoDB takes advantage of the feature that all data has multiple versions to achieve the ability to create snapshots in seconds.

2. Why does the query statement of transaction A return the result of kryp1?

Suppose:

1. At the beginning of transaction A, there is only one active transaction in the system, the ID is 99

two。 The version numbers of transaction A, B and C are 100, 101 and 102, respectively.

3. Before the start of the three transactions, (1) the row trx_id of this row is 90

Thus, the array of transaction An is [99100], the view array of transaction B is [99100101], and the view array of transaction C is [99100101102]

As you can see from the figure above, the first valid update is transaction C, which has been changed from (1) to (1). At this time, the latest version of the data is row trx_id 102, and the 90 version has become a historical version.

The second valid update is transaction B, which changes the data from (1p2) to (1p3). At this time, the latest version of this data is 101, and 102 becomes the historical version.

At the time of the transaction A query, transaction B has not yet committed, but the version it generated (1jin3) has become the current version. But this version must not be visible to transaction A, otherwise it will become dirty.

Now transaction An is about to read the data, and its view array is [99100]. The reading data is read from the current version. Therefore, the read data flow of the transaction A query statement is as follows:

When row trx_id=101 is found, it is judged that it is larger than the high water level, is in the red area, and is not visible.

Then, find the previous historical version, look at the row trx_id=102, larger than the high water level, in the red area, invisible

Looking further, I finally found (1), its row trx_id=90, which is smaller than the low water level, is in the green zone, so it can be seen.

Although this row of data has been modified during this period, transaction A sees that the result of this row of data is consistent whenever it is queried, which we call consistent read.

For a data version, there are three situations for a transaction view, except that its own updates are always visible:

Version not submitted, not visible

The version was submitted, but it was submitted after the view was created and is not visible

The version has been submitted, and it was submitted before the view was created, you can see

The view array of query statements for transaction An is generated when transaction A starts, when:

(1p3) has not been submitted yet. It belongs to case 1 and is not visible.

(1) although it was submitted, it was submitted after the view array was created, which belongs to case 2 and is not visible

(1) submitted before the creation of the view array, you can see

3. Why does the query statement of transaction B return the result of kryp3?

When transaction B wants to update the data, it can no longer be updated in the historical version, otherwise the update of transaction C will be lost. Therefore, the set k=k+1 of transaction B at this time is based on (1mem2).

Update data are read before writing, and this read, can only read the current value, called the current read. In addition to the update statement, the select statement, if locked, is also the current read

Suppose transaction C is not committed immediately, but becomes the following transaction C?

In the figure above, transaction C is not committed immediately after it is updated, and the update statement of transaction B is initiated before it commits. Although transaction C has not been committed yet, this version has also been generated and is currently the latest version.

At this time, the two-phase locking protocol is involved, and transaction C is not committed, which means that the write lock on this version has not been released. Transaction B is currently read, must read the latest version, and must be locked, so it is locked. Transaction C must release the lock before it can continue its current read.

7. How is the repeatable reading ability of transactions realized?

The core of repeatable reading is consistent reading, and when a transaction updates data, it can only use the current read. If the row lock of the current record is occupied by another transaction, you need to enter the lock and wait

The logic of read submission is similar to that of repeatable readings, with the main difference being:

At the repeatable readable isolation level, you only need to create a consistency view at the beginning of the transaction, which is shared by all other queries in the transaction

At the read commit isolation level, a new view is calculated repeatedly before each statement is executed.

That's all for "what does transaction isolation in MySQL mean?" Thank you for your 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.