MySQL_Transaction

Article Directory

1. Description of transaction application scenarios

什么是事务: 在实际的开发过程中,一个业务操作如:转账,往往是要多次访问数据库才能完成的。转账是一个用户扣钱,另一个用户加钱。如果其中有一条 SQL 语句出现异常,这条 SQL 就可能执行失败。
事务执行是一个整体,所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常,则所有的SQL 语句都要回滚,整个业务执行失败。
Insert picture description here

2. Manually commit the transaction

There are two ways to perform transaction operations in MYSQL:

  1. Commit the transaction manually
  2. Auto commit transaction

2.1 Manually commit the SQL statement of the transaction

Insert picture description here

2.2 The process of manually committing the transaction:

Insert picture description here


Insert picture description here

2.2.1 Case demonstration 1: Transaction commit

Insert picture description here


Insert picture description here

2.2.2 Case demonstration 2: Transaction rollback

Insert picture description here


Insert picture description here

3 Automatically commit the transaction

Insert picture description here

3.1 Case demonstration 3: Auto commit transaction

Insert picture description here

3.2 Cancel automatic submission

Insert picture description here

4 Principles of Transaction

事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表
中,其他任何情况都会清空事务日志(rollback,断开连接) 

5 Isolation level of the transaction

5.1 Four characteristics of transaction ACID

Insert picture description here

5.2 The problem of concurrent access caused by transactions

Insert picture description here

5.3 MySQL database has four isolation levels

Insert picture description here

Set the transaction isolation level, you need to log out of MySQL and log in again to see the change in isolation level

设置隔离级别 set global transaction isolation level 级别字符串;

5.5 Demonstration of Dirty Read

Restore the data: UPDATE account SET balance = 1000;

  1. Open window A to log in to MySQL, and set the global isolation level to the lowest
mysql -uroot -proot
set global transaction isolation level read uncommitted;
Insert picture description here


2. Open the B window, and the AB window will open the transaction

use day23;
start transaction;
Insert picture description here


3. Window A updates the account data of 2 individuals, but they have not been submitted

update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;
Insert picture description here


4. B window query account

select * from account;
Insert picture description here


5. A window rollback

rollback;
Insert picture description here


6. Check the account in window B, and the money is gone.

Insert picture description here


Solve the problem of dirty reading: improve the global isolation level
and restore the data:

UPDATE account SET balance = 1000;
  1. Set the global isolation level in window A to read committed
set global transaction isolation level read committed;
Insert picture description here


8. A updated the accounts of 2 individuals, but did not submit

update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;
Insert picture description here


9. Check account in

Insert picture description here


B window 4. Check account in B window

5.6 Demonstration of non-repeatable reading

Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here

5.7 Demonstration of phantom reading

Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here