MySQL transaction

Business

Definition ： If a business operation contains multiple steps , Managed by affairs , So these operations are either successful at the same time , Or fail at the same time .
characteristic ： ACID

• Atomic： Atomicity , Is an indivisible minimum operating unit , Or at the same time , Or fail at the same time .
• Consistency： persistence , When a transaction is committed or rolled back , The database will persist the data .
• Isolation： Isolation, , Between multiple transactions . Are independent of each other .
• Durability： Uniformity , Before and after transaction operation , The total amount of data remains the same

Concurrency problems

In a typical application , Multiple transactions run concurrently , Often operate the same data to complete their own tasks （ Multiple users operate on unified data ）. Concurrency is necessary , But it may cause the following problems .
Dirty reading （Dirty read）: When a transaction is accessing data and modifying the data , This modification has not yet been committed to the database , At this time, another transaction also accesses the data , And then I used this data . Because this data is not submitted yet , So the data read by another transaction is “ Dirty data ”, basis “ Dirty data ” The operation may not be correct .
Missing changes （Lost to modify）: When a transaction reads a data , Another transaction also accesses the data , After modifying the data in the first transaction , The second transaction also modifies the data . In this way, the modification result in the first transaction will be lost , So it's called lost modification . for example ： Business 1 Read data from a table A=20, Business 2 Also read A=20, Business 1 modify A=A-1, Business 2 Also modify A=A-1, final result A=19, Business 1 The modification of is lost .
It can't be read repeatedly （Unrepeatableread）: Reading the same data multiple times in a transaction . Before the end of the business , Another transaction also accesses the data . that , Between two reads in the first transaction , Due to the modification of the second transaction, the data read by the first transaction twice may be different . This happens when the data read twice in a transaction is different , So it's called unrepeatable reading .
Fantasy reading （Phantom read）: Unreal reading is similar to nonrepeatable reading . It happens in a transaction （T1） Read a few lines of data , Then another concurrent transaction （T2） When some data is inserted . In the subsequent query , The first thing （T1） There will be more records that don't exist , It's like an illusion , So it's called Unreal reading .

SQL Isolation level

READ-UNCOMMITTED( Read uncommitted )： Lowest isolation level , Allow read of uncommitted data changes , Can cause dirty reading 、 Phantom or unrepeatable reading
READ-COMMITTED( Read committed ): Allow to read data submitted by concurrent transactions , Can prevent dirty reading , But phantom or unrepeatable reads can still occur
REPEATABLE-READ（ Repeatable ）: Multiple reads of the same field are consistent , Unless the data is modified by the transaction itself , Can prevent dirty and unrepeatable read , But phantom reading can still happen .
SERIALIZABLE( Serializable ): Highest isolation level , Completely obey ACID Isolation level . All transactions are executed one by one , In this way, there is no interference between transactions , in other words , This level prevents dirty reads 、 Unrepeatable reading and phantom reading .