1. Basic introduction to transaction

1.1 Concept

1. If a business operation contains multiple steps , Managed by affairs , Either these operations succeed at the same time , Or fail at the same time .
2. Manage by transaction management insert,update,delete sentence .
3. stay mysql In the database , Only used Innodb Only the database or table sum of the database engine supports practical operations .

1.2 operation

1. Open transaction : start transaction;
2. Roll back : rollback;
3. Submit ： commit;
4. savepoint identifier; Save it , There can be multiple ;
5. release savepoint identifier; Delete a transaction savepoint , When there is no specified savepoint , Executing the statement throws an exception ;
6. rollback to identifier; Roll back the transaction to the marked point ;

1.3 Case study ： Transfer accounts

CREATE TABLE account (
                         id INT PRIMARY KEY AUTO_INCREMENT,
                         name VARCHAR(10),
                         money DOUBLE
);
insert into account values(null,'zhangsan',2000),(null,'lisi',2000);
select  * from account;
#  Zhang San transfers money to Li Si 500
#  Open transaction 
start transaction ;
#  Set the savepoint , Something went wrong , Return to this location 
savepoint save_point_b;
# 1. Zhang San's account  -500
update account set money = money - 500 where name='zhangsan';
# 2. Li Si's account  +500
#  Something went wrong ...  

update account set money = money + 500 where name='lisi';
#  Found no problems with execution , Commit transaction 
COMMIT;
#  I found something wrong , Roll back the transaction 
rollback to save_point_b;

1.4 MySQL Transactions in the database are automatically committed by default

Two ways to commit a transaction ：

Automatic submission ：

* mysql It's automatic submission

* One DML( Additions and deletions ) Statement will automatically commit a transaction .

Manual submission ：

* Oracle By default, the database commits transactions manually

* You need to start the transaction first , Submit again

Modify the default commit method of transaction ：

View the default commit method for transactions ：SELECT @@autocommit;

-- 1 On behalf of auto submit   0 On behalf of hand submit

Modify the default submission method ： set @@autocommit = 0 Disable auto submit ;

2. Four characteristics of transactions ACID

Generally speaking , Transactions must be satisfied 4 Conditions （ACID）： Atomicity （Atomicity, Or indivisibility ）、 Uniformity （Consistency）、 Isolation, （Isolation, Also called independence ）、 persistence

1. Atomicity ： Is an indivisible minimum operating unit , Or at the same time , Or fail at the same time .

2. persistence ： When a transaction is committed or rolled back , The database will persist the data .

3. Isolation, ： Between multiple transactions . Are independent of each other .

4. Uniformity ： Before and after transaction operation , The total amount of data remains the same

3. The isolation level of the transaction

3.1 Concept

Isolated... Between multiple transactions , Mutually independent . But if multiple transactions operate on the same batch of data , It will cause some problems , Setting different isolation levels can solve these problems .

3.2 Existing problems

1. Dirty reading ： A business , Read to uncommitted data in another transaction

2. It can't be read repeatedly ( Virtual reading )： In the same transaction , The data read twice is not the same . Usually for data to update （UPDATE） operation .

3. Fantasy reading ： A transaction operation (DML) All records in the data sheet , Another transaction adds a piece of data , Then the first transaction cannot query its own modification . Magic reading is about data Insert （INSERT） In terms of operation .

3.3 Isolation level

1. read uncommitted： Read uncommitted

         The problems that arise ： Dirty reading 、 It can't be read repeatedly 、 Fantasy reading

2. read committed： Read submitted （Oracle）

         The problems that arise ： It can't be read repeatedly 、 Fantasy reading

3. repeatable read： Repeatable （MySQL Default ）

         The problems that arise ： Fantasy reading

4. serializable： Serialization

         Can solve all the problems

1. Be careful ： The isolation level is getting higher and higher from small to large , But the efficiency is getting lower
2. Database set isolation level ：
   1. set global transaction isolation level  Level string ;
3. Database query isolation level ：
   1. select @@tx_isolation;

3.4 Case study

select @@tx_isolation;  View the default transaction isolation level 

 Modify... In the default configuration file （ All modified my.ini）
transaction-isolation =  Isolation level ;

# The command line modifies the isolation level ;
 The statement to modify the isolation level is ：set [ Scope ] transaction isolation level [ Transaction isolation level ],
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}.

set global transaction isolation level read uncommitted;
start transaction;
--  Transfer operation 
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

Summary ： Unrepeatable reading focuses on revision , Unreal reading focuses on adding or deleting . To solve the problem of non repeatable reading, we only need to lock the line that meets the condition , To solve unreal reading, we need to lock the watch .

4. Case test （ Isolation level ）

1. read uncommitted： Read uncommitted

Set up A,B The two transactions are manually committed , The isolation level is read uncommitted;

#1、 Set the global isolation level , Open new console , And set transaction manual submission 
set global transaction isolation level read uncommitted;
#2.A Business 
start transaction;
# 1. Zhang San's account  -500
update account set money = money + 500 where name='zhangsan';
# 2. Li Si's account  +500
update account set money = money - 500 where name='lisi';

#3.B Business 
select * from account;

#4.A Business 
rollback;

#5.B Business 
select * from account;

  Two execution results ：

problem ： There's dirty reading , Non repeatable reading

2. read committed： Read submitted （Oracle）

change A,B The isolation level is read committed

#1、 Set the global isolation level , Open new console , And set transaction manual submission 
set global transaction isolation level read committed;
#2.A Business 
start transaction;
# 1. Zhang San's account  -500
update account set money = money - 500 where name='zhangsan';
# 2. Li Si's account  +500
update account set money = money + 500 where name='lisi';
#3.B Business 
select * from account;

#4.A Business 
commit;

#5.B Business 
select * from account;

Running results ：

Find the problem ： It can't be read repeatedly

3. repeatable read： Repeatable （MySQL Default ）

change A,B The isolation level is repeatable read

#1、 Set the global isolation level , Open new console , And set transaction manual submission 
set global transaction isolation level repeatable read;
#2.A Business 
start transaction;
# 1. Zhang San's account  -500
update account set money = money - 500 where name='zhangsan';
# 2. Li Si's account  +500
update account set money = money + 500 where name='lisi';
#3.B Business 
select * from account;// Read the status of not transferring money 

#4.A Business 
commit;

#5.B Business 
select * from account;// Read the status of not transferring money 

Two execution results ：

  It solves the problems of dirty reading and non repeatable reading

4. serializable： Serialization

change A,B The isolation level is serializable

#1、 Set the global isolation level , Open new console , And set transaction manual submission 
set global transaction isolation level read serializable;
#2、 Start two transactions , They are affairs A And transaction B, In the transaction A Use in  update  sentence , modify  dname  The value of is " Department of defense " The execution of the  update  After statement , In the transaction B Query in  dept  surface , Will see  dname  The value of is already  " Department of defense "  了 , It's time for business A Not yet submitted , And now the business B It's possible to take the modified danem=“ Department of defense ”  To do something else . In the transaction B In the process of operation , It's very likely that the business A For some reason , The transaction rollback operation was performed , That's actually business B What you get is dirty data , Take dirty data and do other calculations , There must be something wrong with the result .
#3、 Execute statement 
start transaction;
# 1. Zhang San's account  -500
update account set money = money - 500 where name='zhangsan';
# 2. Li Si's account  +500
update account set money = money + 500 where name='lisi';
#4、
B Business 
select * from account;// Direct blocking , wait for A End of transaction 
#5、
A Business 
commit;
#6、
B Business 
select * from account;// Normal display 

Serialization is 4 Of the transaction isolation levels, the isolation effect is the best , Solved dirty reading 、 Repeatable 、 The problem of unreal reading , But the effect is the worst , It changes the execution of a transaction into a sequential execution , Compared to the other three isolation levels , It's like a single thread , The execution of the latter transaction must wait for the end of the previous transaction .