MySQL transaction

MySQL Business ：

One 、 Transaction execution process ：

Two 、 The basic elements of a business （ACID）
1、 Atomicity （Atomicity）： All operations after the transaction begins , Or all of them , Or not at all , It's impossible to stay in the middle . An error occurred during the execution of the transaction , It rolls back to the state it was in before the transaction started , All the operations are as if they didn't happen . That is to say, a business is an indivisible whole , It's like atoms in chemistry , Is the basic unit of material composition , Is the smallest particle of chemical change .

2、 Uniformity （Consistency）： Before and after the transaction starts , The database integrity constraint is not broken . This means that the data written must fully comply with all the default rules , such as A towards B Transfer accounts , impossible A Buckle the money ,B Has received .

3、 Isolation, （Isolation）： At the same time , Only one transaction is allowed to request the same data , There is no interference between the different transactions . such as A Withdrawing money from a bank card , stay A Before the withdrawal process is over ,B You can't transfer money to this card .

4、 persistence （Durability）： After the transaction completes , All updates to the database by the transaction are saved to the database , Cannot be rolled back .

3、 ... and 、 Concurrency of transactions

1、 Dirty reading ： Dirty read refers to reading uncommitted data from other transactions , Uncommitted means that the data may be rolled back , That is, it may not be stored in the database eventually , That is, data that doesn't exist . Read the data that must eventually exist , This is dirty reading .

2、 It can't be read repeatedly ： Contrast can be read repeatedly , Nonrepeatable reading refers to within the same transaction , The same batch of data read at different times may be different , May be affected by other things , For example, other transactions change this batch of data and commit . Usually for data updates （UPDATE） operation .

3、 Repeatable ： Repeatable reading refers to within a transaction , The first read data is consistent with the same batch of data read at any time before the end of the transaction . Usually for data updates （UPDATE） operation .

4、 Fantasy reading ： Unreal reading is for data insertion （INSERT） In terms of operation . What if A View the contents of some lines , But it hasn't been submitted yet , At this point, the transaction B Insert transaction A What to view , And in business A Submitted before submitting , And then , In the transaction A If you query again in, you will find that it is different , But it's business B Just inserted in , It makes users feel magical , I feel like I'm hallucinating , This is called Unreal reading .

Four 、 Transaction isolation level
SQL The standard defines four levels of isolation ,MySQL All support . The four isolation levels are ：

1. Read uncommitted （READ UNCOMMITTED）
2. Read the submission （READ COMMITTED）
3. Repeatable （REPEATABLE READ）
4. Serialization （SERIALIZABLE）

From the top down , The intensity of isolation gradually increases , Performance is getting worse . Which isolation level to adopt depends on the trade-off between system requirements , among , Repeatable reading is MySQL Default level for .
Transaction isolation is actually to solve the above-mentioned dirty reading 、 It can't be read repeatedly 、 Some questions about illusory reading , The following shows 4 The degree to which these three problems are solved by the isolation level .