A transaction refers to a set of operations that meet the ACID properties, either by committing a transaction or by rolling back with Rollback.

ACID

From a business perspective, a set of operations on a database are required to maintain four characteristics:

Atomicity:

A transaction must be treated as an indivisible, minimal unit of work in which all operations are either committed successfully or rolled back on failure. It is not possible for a transaction to perform only some of the operations.

Consistency:

The database remains in a consistent state before and after a transaction. In a consistent state, all transactions read the same data.
A database always transitions from one consistent state to another.

Isolation:

Typically, changes made by one transaction are not visible to other transactions until they are finally committed. Note the “generally speaking” here, as described later in the transaction isolation level.

“Durability” :

Once a transaction commits, its changes are permanently stored in the database. Even if the system crashes, the results of the transaction execution cannot be lost. Use redo logging for persistence. (The security of persistence is also related to the refresh log level, which corresponds to different data security levels.)


The ACID property of transactions is conceptually simple, but not well understood, mainly because the properties are not hierarchical:
  • The execution result of a transaction is correct only if consistency is satisfied.
  • In the absence of concurrency, transactions are executed sequentially and isolation must be satisfied. So long as atomicity can be satisfied, consistency can be satisfied.
  • In the case of concurrency, multiple transactions are executed in parallel. Transactions must satisfy not only atomicity but also isolation to satisfy consistency.
  • Transactions are persisted in the event of a database crash.




AUTOCOMMIT

MySQL uses automatic commit mode by default. That is, if a TRANSACTION is not explicitly started using the START TRANSACTION statement, each query is automatically committed as a TRANSACTION.

Concurrency consistency problem

In a concurrent environment, the isolation of transactions is difficult to guarantee, so there are many concurrency consistency issues.

1. Lost Update:

Definition: When two or more transactions select the same row and then update the row based on the value originally selected, a lost update problem occurs because each transaction is unaware of the existence of the other transactions, and the last update overwrites updates made by the other transactions.

Such as:
Two editors made electronic copies of the same document. Each editor independently changes its copy and then saves the changed copy, overwriting the original document. The editor who last saved a copy of his changes overwrites the changes made by the other editor. This problem can be avoided if another editor cannot access the same file until one editor has completed and committed the transaction.
Both T1 and T2 transactions modify a data, T1 first, T2 later, and T2 overrides T1.



2. Dirty Reads

Definition: a transaction is making changes to a record whose data is in an inconsistent state before the transaction completes and commits. At this point, another transaction reads the same record, and if left unchecked, the second transaction reads the “dirty” data and performs further processing accordingly, resulting in uncommitted data dependencies. This phenomenon is aptly called “dirty reading”.

T1 modifies a piece of data, which T2 then reads. If T1 reverses the change, then T2 reads dirty data.



3. Non-repeatable Reads:

Definition:At some point after reading some data, a transaction reads the previously read data again, only to discover that the read data has changed or some records have been deleted! This phenomenon is called unrepeatable reading.

T2 reads a piece of data, and T1 modifies it. If T2 reads this data again, it will not read the same data as it did the first time.



4. Phantom Reads:

Definition: When a transaction re-reads previously retrieved data under the same query criteria, only to find that another transaction inserts new data that meets its query criteria, this phenomenon is called “phantom read”.

T1 reads data in a range, T2 inserts new data in that range, and T1 reads data in that range again, with a different result from the first read.



The difference between phantom and unrepeatable reads:

Non-repeatable reading is the focus of modification:In the same transaction, the data read for the first time is different from the data read for the second time under the same condition. (Because other transactions committed changes in the middle)
Hallucination focuses on adding or deleting:In the same transaction, under the same conditions, the number of records read in the first and second time is different. (Because other transactions committed insert/delete in the middle)

The difference between unrepeatable and dirty reads:

Dirty reads read uncommitted data, while unrepeatable reads read committed data.

Isolation level

The SQL standard defines four classes of isolation levels, each of which specifies what changes are made within a transaction, which are visible within and between transactions, and which are not. Low-level isolation levels generally support higher concurrency and have lower system overhead.

Level 1: Read Uncommitted

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
The problem with this level is Dirty Read: uncommitted data has been Read

Level 2: 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 read changes made by committed transactions. In other words, changes made by one transaction are not visible to other transactions until they are committed.

The problem with this isolation level is Nonrepeatable Read: Non-repeatable reads mean that we may see different results when executing exactly the same SELECT statement in the same transaction. The reasons for this may include:

There is a new commit for an crossed transaction, resulting in data changes;

When a database is operated on by multiple instances, other instances of the same transaction may have new commits during the processing of that instance

Repeatable Read level 3: Repeatable Read

This is MySQL’s default transaction isolation level
Ensure that the same data is read multiple times in the same transaction with the same result.
It ensures that multiple instances of the same transaction see the same rows of data when they concurrently read data


Possible problems at this level — Phantom Read: when a user reads a row in a 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 the illusion problem through the Multiversion Concurrency Control (MVCC) mechanism. InnoDB also addresses illusionary issues through gap locking

MVCC does not solve the phantom problem; next-Key Locks exist to solve it. At the REPEATABLE READ isolation level, the phantom problem is solved using MVCC + next-key Locks.

Level 4: Serializable

This is the highest isolation level and forces transactions to be executed serially.


It solves the illusion 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



summary

The default isolation level for MySQL is repeatable reads. The default isolation level for MySQL is repeatable reads. The default isolation level for MySQL is repeatable reads. It uses MVCC+next-key LOCKS to avoid the phantom problem. I will introduce the principle in the next article.


Refer to the

zhuanlan.zhihu.com/p/29166694

Github.com/CyC2018/CS-…