This article will take about 5.6 minutes to read.


Source: Internet

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 the 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.


End


Before I sent a [Meituan] Java post 154 interview questions of the article, did not analyze, next ready to release the analysis in the public account below (weekdays: interview questions analysis +IT interesting picture, weekend: IT interesting picture), use the fragmented time to see the interview questions analysis, continue to improve, quickly scan the two-dimensional code at the end of the article to pay attention to a wave.


, END,

The growth path of programmers

Though the road is long, the journey is sure to come

This article was originally posted on the wechat public account of the same name “The Growth of programmers”, reply to “1024” you know, give a thumbs up.

Reply [520] to receive the best learning method for programmers

Reply to [256] for Java programmer growth plans