Click on “Road of Technology for Migrant workers” and choose “Top or Star label”

10 o ‘clock every day for you to share different dry goods

What is a transaction

A transaction is a series of rigorous operations in an application, all of which must complete successfully or all changes made in each operation will be undone. That is, transactions are atomic, and a series of operations in a transaction will either all succeed or none will be done.

There are two ways to end a transaction: when all steps in the transaction have been successfully executed, the transaction commits. If one of the steps fails, a rollback occurs to undo all previous actions up to the beginning of the transaction.

The transaction of the ACID

Transactions have four characteristics: Atomicity, Consistency, Isolation and persistence. These four properties are referred to simply as ACID properties.

  • Atomicity. A transaction is the logical unit of work of a database in which all or none of the operations involved are performed

  • Consistency. The result of transaction execution must be to change the database from one consistent state to another. Therefore, a database is said to be in a consistent state when it contains only the results of successful transaction commits. If a failure occurs during the operation of the database system and some transactions are interrupted before completion, some of the changes made to the database by these unfinished transactions have been written to the physical database, then the database is in an incorrect state, or inconsistent state.

  • Isolation. The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions. Concurrent transactions cannot interfere with each other.

  • Sustainable. Also known as permanence, meaning that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent operations or failures should not have any impact on the results of its execution.

Mysql has four isolation levels

The SQL standard defines four classes of isolation levels, including specific rules that define which changes are visible and which are not, both inside and outside a transaction. Low-level isolation levels generally support higher concurrency and have lower system overhead.

Read Uncommitted

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because its performance is not much better than other levels. Reading uncommitted data is also known as Dirty reads.

Read Committed

This is the default isolation level for most database systems (but not for MySQL). It satisfies a simple definition of isolation: a transaction can only see the changes made by committed transactions. This isolation level also supports what is called Nonrepeatable Read, because other instances of the same transaction may have new COMMITS in the process of that instance, so the same SELECT may return different results.

Repeatable Read (Repeatable Read)

This is MySQL’s default transaction isolation level and ensures that multiple instances of the same transaction will see the same rows when they concurrently read data. In theory, though, this leads to another thorny problem: Phantom Read. Simply put, phantom reading refers to when a user reads a row in a certain range, another transaction inserts a new row in that range, and when the user reads a row in that range, a new phantom row is found. InnoDB and Falcon storage engines address this problem through the Multiversion Concurrency Control (MVCC) mechanism.

Serializable

This is the highest isolation level, and it solves the phantom problem by forcing transactions to be ordered so that they cannot conflict with each other. In short, it places a shared lock on each read row. At this level, a lot of timeouts and lock contention can result.

The four isolation levels are implemented with different lock types, which can be problematic if the same data is read. Such as:

  • Drity Read: a transaction has updated a copy of data, and another transaction has Read the same copy of data. For some reason, the first transaction has rolled back, and the data Read by the second transaction is incorrect.

  • Non-repeatable read: Data inconsistency between two queries of a transaction. This may be because the original data updated by a transaction was inserted between the two queries.

  • Phantom Read: a transaction where the number of pens is inconsistent between two queries. For example, one transaction queries for rows and another transaction inserts new columns. The previous transaction will have unqueried columns in subsequent queries. An error is reported if data is inserted at this time and is inserted by another transaction.

In MySQL, these four isolation levels are implemented, which can cause problems as follows:

Test Mysql isolation level

Next, we’ll test each of these isolation levels using the MySQL client program.

The test database is Demo and the table is test. Table structure:

The two command line clients are A and B respectively. Constantly changing the isolation level of A, modifying the data at B.

Set the isolation level of A to Read uncommitted

A: The transaction is started and the data is in the initial state

B: Starts the transaction, updates the data, but does not commit

A: Reading the data again and finding that the data has been modified is called A “dirty read”

B: Rollback the transaction

A: Read the data again and find that the data changes back to the initial state

According to the above experiment, it can be concluded that transaction B updates A record but does not commit it. At this time, transaction A can query the uncommitted record. Dirty read is caused. Uncommitted reads are the lowest isolation level.

Set client A’s transaction isolation level to Read COMMITTED

A: The transaction is started and the data is in the initial state

B: Starts the transaction, updates the data, but does not commit

A: Read the data again and find that the data is not modified

B: Commit the transaction

A: Read the data again and find that the data has changed, indicating that the change committed by B is read by A in the transaction, which is called “non-repeatable read”.

From the above experiments, it can be concluded that the committed read isolation level solves the problem of dirty reads, but the problem of unrepeatable reads occurs, that is, transaction A has inconsistent data between the two queries because transaction B updated one data between the two queries. Committed reads allow only committed records to be read, but repeatable reads are not required.

Set isolation level of A to REPEATable Read

A: The transaction is started and the data is in the initial state

B: Starts the transaction, updates the data, but does not commit

A: Read the data again and find that the data is not modified

B: Commit the transaction

A: Read the data again and find that the data is still unchanged, which indicates that it can be repeated this time

B: Insert a new piece of data and commit it

A: Read the data again and find that the data is still unchanged. Although the data can be read repeatedly, it is found that the data is not the latest data. This is called “phantom read”.

A: The transaction was submitted and the data was read again. It was found that the data was normal

It can be concluded from the above experiments that the repeatable read isolation level only allows a committed record to be read and updated by other transactions during a transaction reading a record twice. However, this transaction is not required to be serializable with other transactions. For example, when a transaction can find records updated by a committed transaction, phantom read problems can occur (note that this is possible because the isolation level is implemented differently by the database). Like the above experiments, there is no data illusion problem.

Set the isolation level of A to Serializable

A: The transaction is started and the data is in the initial state

B: It is found that B enters the waiting state at this time, because the transaction of A has not been committed and can only wait (at this time, B may have wait timeout).

A: Commit A transaction

B: The disk is successfully inserted

Serializable Fully locked field. If a transaction queries the same data, it must wait until the previous transaction completes and the lock is released. Is the full isolation level, which locks the corresponding data table, thus making the problem efficient. If you need to learn more about Mysql database, you can reply “Mysql” in the background of the public account: Worker brother technology road to obtain a comprehensive Mysql database learning guide.

The original link: https://www.jianshu.com/p/8d735db9c2c0

– MORE excellent articles – |

  • After the server is attacked, such investigation is not handled!

  • 10 tips to improve Kubernetes container efficiency

  • Operation and maintenance engineers to fight strange upgrade road V2.0

  • High salary, helpless pain, this is the current situation of Chinese programmers?

  • Learn Docker, the 11 most common mistakes beginners make!

  • SegmentFault stands out against CSDN download sites

  • Pay list: Don’t eat or drink a year still can’t buy 3 square meters of room?

If you enjoyed this article

Please click the QR code to pay attention to the road of technology

Scan code to pay attention to the public number, reply to the “directory” can view the public number of articles catalog, reply to the “group” can join the reader technical exchange group, communicate with you together.

All the best of the official account is here

You are watching, click here to have a surprise oh ~