MySQL transaction and isolation level

Source: Internet
Author: User

MySQL transaction and isolation level
Preface:

I never thought of writing a good title. I want some important MySQL content. I have met either of the two interviews, but it seems that it is not appropriate to directly use the MySQL title. Simply write the content.

MySQL transaction:

transaction

Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

Database transactions, as implemented by InnoDB, have properties that are collectively known by the acronym ACID, for atomicity, consistency, isolation, and durability.

See Also ACID, commit, isolation level, lock, rollback.



The MySQL official website explains the transaction. (Note: The student ID English is really important, very important, and super important)

A (atomicity) atomicity: that is, the transaction is either completely completed or not done, and only A part of the transaction is not allowed. For example, if A transfers money to B, A will not lose money, if B's money does not increase, C (consistency) consistency means that the transaction is consistent from one State to another. For example, if A is reduced by 100, B cannot only increase by 30. I (isolation) isolation: when a transaction fails to commit and modify data, it is invisible to other transactions. Of course, there is a concept of isolation level. At different isolation levels, there will be different forms of D (durability) Persistence: Once a transaction is committed, the changes will be permanently saved to the database. Four isolation levels:


isolation level

One of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.

From highest amount of consistency and protection to the least, the isolation levels supported by InnoDB are: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.

With InnoDB tables, many users can keep the default isolation level (REPEATABLE READ) for all operations. Expert users might choose the READ COMMITTED level as they push the boundaries of scalability with OLTP processing, or during data warehousing operations where minor inconsistencies do not affect the aggregate results of large amounts of data. The levels on the edges (SERIALIZABLE and READ UNCOMMITTED) change the processing behavior to such an extent that they are rarely used.

See Also ACID, OLTP, READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE, transaction.

Read Uncommitted (Read Uncommitted content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications, because its performance is no better than other levels. Read uncommitted data, also known as Dirty Read ).
Read Committed (Read submitted content)

This is the default isolation level for most database systems (but not for MySQL ). It satisfies the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports the so-called Nonrepeatable Read, because other instances of the same transaction may have a new commit during the processing of this instance, so the same select may return different results.
Repeatable Read (repeable)

This is the default transaction isolation level of MySQL. It ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. However, theoretically, this will lead to another tricky problem: Phantom Read ). In short, phantom read refers to when a user reads data in a certain range, another transaction inserts a new row in this range. When the user reads data in this range, there will be a new Phantom line. The InnoDB and Falcon storage engines solve this problem through the Multi-version Concurrency Control (MVCC, Multiversion Concurrency Control) mechanism.

Serializable (Serializable)
This is the highest isolation level. It forces transaction sorting to make it impossible to conflict with each other, thus solving the phantom read problem. In short, it adds a shared lock to each read data row. At this level, there may be a lot of timeout and lock competition.

Here I will explain the difference between "read/write submitted content" and "repeatable content.

Assume that there are two transactions T1, T2

Read/write submission content:

T1 inserts a piece of data, and T2 does not see it at this time. After T1commit, T2 will see it. This leads to the inconsistency between the two select statements before and after T2, which leads to the unrepeatable reason.

Repeatable:

T1 inserts a piece of data, and T2 does not see it at this time. After T1 commit, T2. After the T2 transaction is committed, when the select statement is executed, it is found that, in the slot, how does one more piece of data come out? It feels like an illusion, that is, "Phantom read ";

MVCC mechanism:

Multiversion Concurrency Control ). The default isolation level of MySQL is Repeatable Read (Repeatable ). So how does MySQL solve phantom read.

The MVCC mechanism is used.

What is multi-version concurrency control? In fact, two hidden columns are added after each row of records to record the creation version number and deletion version number. Each transaction has a unique incremental version number at startup.

MVCC can be used only at two transaction isolation levels: read-committed and repeatable-read.

Read-uncommited because it reads uncommitted data, there is no version issue.

Serializable locks all read rows.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151405.htm


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.