First, 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.

Transaction 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

■ One cause. The result of the transaction execution must be to change the database from one uniform state to another consistent state. Therefore, a database is said to be in an -induced state when it contains only the results of a successful transaction commit. 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 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.
■ Sustainability. Also known as permanence, it means 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 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.

1. Read unconnrepeatable (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 real-world applications because its performance is not much better than other levels. Reading uncommitted data is also known as Dirty reads.

2. Read connrepeatable (Read commits)

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 high level of isolation also supports so-called unrepeatable reads

(Nonrepeatable Read), because other instances of the same transaction may have new commits in the process of that instance, 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 row when they concurrently read data. In theory, though, this leads to another thorny problem: Phantom Read. To put it simply, 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 will be found. InnoDB and Falcon storage engines address this problem through the Multiversion Concurrency Control (MVCC) mechanism.

4. 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. ■ 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 one has rolled back, and the second one has Read the data incorrectly.

■ Non-repeatable read: data is not repeatable read between two queries of a transaction. This may be because a transaction updates the original data between the two queries.

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

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

Four, the last

Thank you for reading here, the article is inadequate, welcome to point out; If you feel well written, welcome to forward and like