Database transactions

1. Definition of transaction

Transaction provides a mechanism to integrate all operations involved in an activity into an indivisible execution unit , All operations that make up a transaction can only be committed if all operations can be executed normally , As long as any of these operations fail , Will cause the entire transaction to roll back . To put it simply , A transaction provides a kind of Or do nothing , Or do the whole thing All or Nothing The mechanism of

2. The nature of transactions -ACID

• Atomicity Atomicity Atomicity means that a transaction is an indivisible unit of work , The operations in the transaction are either all successful , All or nothing

• Uniformity Consistency The integrity of the data before and after the transaction must be consistent .

• Isolation, Isolation Transaction isolation is when multiple users access the database concurrently , A transaction opened by a database for each user , Can't be disturbed by the operation data of other transactions , Multiple concurrent transactions should be isolated from each other .

• persistence Durability Persistence means that once a transaction is committed , It changes the data in the database permanently , Next, even if the database fails, it should not have any impact

3. Problems arising from concurrent transactions

• Dirty reading Business A Read to transaction B Data not yet submitted

From a macro point of view , It's business A Read out a piece of non-existent data , This problem is very serious .

• It can't be read repeatedly A data is read twice in the same transaction , The two readings are inconsistent

Business A Read the same data multiple times , But the business B In the transaction A During multiple reads , The data has been updated and submitted , Cause transaction A When reading the same data multiple times , Inconsistent results .

• Fantasy reading Unreal reading means A Transaction read B Add or delete data of transaction , Lead to A Transaction in B The number of records read before and after adding data is inconsistent

The study of unreal reading must be in RR Research at the level of ( Repeatable ), because RU / RC There will also be dirty reads 、 It can't be read repeatedly , So we take RR Level to study fantasy reading , Eliminate other interference .

Description of unreal reading ：

1. Business A Query exists id=5 The record of , If not, insert , This is the normal business logic we expect .
2. This is the time Business B A new one id=5 The record of , And commit the transaction .
3. Business A Check again id=5 When , Found that there is still no record （ Because this is where RR Research at the level of ( Repeatable ), So read that there is still no data ）
4. Business A Insert a id=5 The data of .

Final transaction A Commit transaction , I found the wrong report . That's strange , There was no record when I checked , But when you insert But tell me , It's like an illusion . This is all fantasy reading .

4. The isolation level of the transaction

The isolation level of a transaction is 4 Kind of ： Read uncommitted , Read submitted , Repeatable , Serialization . The isolation level of transactions is to solve the problem of data inconsistency under concurrent transactions .

First, read uncommitted , It's the best performance , It can also be said that it is the most barbaric way , Because it's not locked at all , So there's no isolation at all , There is no isolation . Let's talk about serialization . Serialization is equivalent to , When dealing with a person's request , Everyone else is waiting . Read with shared lock , Other transactions can be read concurrently , But I can't write . Lock it when you write it , Other transactions cannot be written or read concurrently . Finally, read submit and repeatable read . These two levels of isolation are complex , It is necessary to allow certain concurrency , I want to solve the problem at the same time .MySQL The default transaction isolation level is repeatable (RR), The more strict the transaction isolation of the database , The less side effects , But the more it costs ; Because the essence of transaction isolation is to make transactions in serial state to a certain extent , This in itself contradicts concurrency . meanwhile , Different applications have different levels of read consistency and transaction isolation , For example, many applications do not have such high requirements for data consistency , contrary , There are certain requirements for concurrency