Business

• Business profile
• The transaction operations
• Four characteristics of transaction
• Concurrent transaction problems
• Transaction isolation level

A transaction is a collection of operations , It is an indivisible unit of work , The transaction will submit or revoke the operation request to the system as a whole , That is, these operations either succeed at the same time , Or fail at the same time .

-- --------------------------------- The transaction operations -----------------------------------------
--  Data preparation 
create table account(
    id int auto_increment primary key comment ' Primary key ID',
    name varchar(10) comment ' full name ',
    money int comment ' balance '
) comment ' Account form ';
insert into account(id, name, money) VALUES (null, ' Zhang San ', 2000), (null, ' Li Si ', 2000);

--  Restore data 
update account set money = 2000 where name = ' Zhang San ' or name = ' Li Si ';

select @@autocommit;
set @@autocommit = 1; --  Set to manual submit 

--  Transfer operation （ Zhang San gives Li sizhuan 1000）
-- 1. Check the balance of Zhang San 
select * from account where name = ' Zhang San ';
-- 2、 Reduce the balance of Zhang San by 1000
update account set money = money - 1000 where name = ' Zhang San ';

-- 3、 Add... To the balance of Li Si 1000
update account set money = money + 1000 where name = ' Li Si ';

--  Commit transaction 
commit ;

--  Roll back the transaction 
rollback ;

--  Mode two 
--  Transfer operation （ Zhang San transfers money to Li Si 1000）
start transaction ;
-- 1、 Check the balance of Zhang San 
select * from account where name = ' Zhang San ';
-- 2、 Leave three balances -1000
update account set money = money - 1000 where name = ' Zhang San ';
-- 3、 Balance of Li Si +1000
update account set money = money + 1000 where name = ' Li Si ';

--  Commit transaction 
commit ;
--  Roll back the transaction 
rollback ;

--  Four characteristics of transactions 
--  Atomicity 
--  Uniformity 
--  Isolation, 
--  persistence 

--  Concurrent transaction problems 

--  The isolation level of the transaction 

--  View transaction isolation level 
select @@transaction_isolation;
--  Set the transaction isolation level 
set session transaction isolation level read uncommitted;
set session transaction isolation level repeatable read ;