MySQL事务

什么是事务

事务，是由一条或多条SQL 语句组成的一个整体，这些SQL语句要么都执行成功，要么都执行失败，只要有一条SQL出现异常,整个操作就会进行回滚，整个业务执行失败。

回滚：在事务运行过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库已完成的操作全部撤销，滚回到事务开始之前的状态。

模拟转账操作

现有一项业务，tom 要给 jerry 的银行卡中转 500 块钱，那么银行就至少需要操作两次数据库，第一次 tom 账户余额 - 500元，第二次 jerry 账户 + 500 元，且要保证不出任何问题，才能保证银行和个人都无任何损失。

1. 创建一张账户表

-- 创建账户表
CREATE TABLE ACCOUNT(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    money DOUBLE
);

-- 向表中插入两个用户
INSERT INTO ACCOUNT(NAME,money) VALUES('tom',1000),('jerry',1000);
1
2
3
4
5
6
7
8
9

2. 模拟转账

-- tom账户余额-500
UPDATE ACCOUNT SET money = money - 500 WHERE NAME = 'tom';
-- jerry账户余额+500
UPDATE ACCOUNT SET money = money + 500 WHERE NAME = 'jerry'; 
1
2
3
4

注意：当在执行任意一条语句时出现问题，都会造成银行或用户的损失，所以必须保证整个事务执行的完整性，要么都成功，要么都失败。

MySQL事务操作

MySQL事务操作的方式有两种：

• 手动提交事务
• 自动提交事务

手动提交事务

语法：

• start transcation 开启事务（或begin）
  • 事务的起点
• commit 提交事务
  • 提交事务的所有操作，就是将事务中所有对数据库的操作都写到磁盘上的物理数据库中，事务正常结束。
• rollback 回滚事务
  • 撤销事务，事务在运行过程中出现某种异常，事务无法继续执行，系统将事务中对数据库已完成的操作全部撤销，滚回到事务开始之前的状态。

案例：模拟转账

1. 查看account表原本数据

select * from account;
1

2. 开启事务

start transaction;
1

3. 转账

 -- tom账户余额-500
 update account set money = money - 500 where name = 'tom';
 -- jerry账户余额+500
 update account set money = money + 500 where name = 'jerry';
1
2
3
4

4. 提交事务

commit;
1

5. 查看账户余额变化

事务回滚

如果事务中，某条sql语句执行时报错了，我们没有手动提交事务，那么整个事务会自动回滚

1. 开启事务

start transaction;
1

2. 插入两条数据

INSERT INTO ACCOUNT(NAME,money) VALUES('tony',1000);
INSERT INTO ACCOUNT(NAME,money) VALUES('jack',1000);
1
2

3. 不手动提交事务，发生异常（关闭命令窗口），查看数据

自动提交事务

MySQL默认每一条DML语句都是一个单独的事务，每条语句会自动开启一个事务，语句执行完毕自动提交事务，MySQL默认是自动提交事务。

自动提交事务演示

1. 给tom账户余额加500

取消自动提交

show variables like 'autocommit';
1

ON ：自动提交
OFF：手动提交

把autocommit修改为off

set @@autocommit=off;
1

修改数据，提交之后才能生效。

事务的四大特性

• 原子性：每个事务都是一个整体，不可分割，事务中的所有sql，要么都执行成功，要么都执行失败；
• 一致性：事务在执行之前，数据库的状态与执行后的状态要保持一致
• 隔离性：事务与事务之间互不影响，是相互隔离的状态
• 持久性：事务执行成功之后，那么对数据的修改就是持久的

MySQL隔离级别

各个事务之间是相互隔离的，但是如果多个事务对数据库中的同一批数据进行并发访问的时候，就会引发一些问题，这时就需要设置不同的隔离级别来解决对应的问题。

并发访问的问题

• 脏读：一个事务读取到另一个事务还没提交的数据
• 不可重复读：一个事务中，两次读取到的数据不一样
• 幻读：一个事务中，一次读取到的数据无法支撑后续的业务操作

隔离级别

• read uncommitted ：读未提交
  • 不能防止任何问题
• read committed：读已提交 （Oracle默认的隔离级别）
  • 可以防止：脏读
• repeatable：可重复读 （MySQL默认的隔离级别）
  • 可以防止：脏读，不可重复读
• serializable：串行化
  • 可以防止：脏读，不可重复读，幻读

注意：隔离级别从小到大，隔离级别越来越高，执行效率越来越低，根据不同的情况选择不同的隔离级别。

隔离级别相关的命令

• 查看隔离级别
  • select @@transcation_isolation;
• 修改隔离级别
  • set global transcation isolation level 隔离级别;

-- 设置隔离级别为读已提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 隔离级别有以下四种：
-- read uncommitted 读未提交
-- read committed   读已提交
-- repeatable read  可重复读
-- serializable     串行化
1
2
3
4
5
6
7

隔离性问题演示以及解决方案

脏读以及解决方案

脏读，一个事务读取到了另一个事务未提交的数据

脏读演示：

1. 打开并登录MySQL，设置全局的隔离级别为最低的read uncommitted

set global transaction isolation level read uncommitted;
1

2. 重启该窗口（窗口A），在窗口A查询隔离级别

select @@transaction_isolation;
1

3. 再打开一个新窗口（窗口B），开启事务，查看表中数据

start transaction;
select * from account;
1
2

4. 在窗口A开启事务，并进行转账操作，但不要提交

start transaction;

update account set money = money - 500 where name = 'tom';

update account set money = money + 500 where name = 'jerry';
1
2
3
4
5

5. 在窗口B查看表中账户数据，发现查询到了没有提交的数据

select * from account;
1

6. 窗口A转账异常，进行回滚

rollback;
1

7. 窗口B再次查看账户数据

select * from account;
1

脏读解决方案：

将全局的隔离级别提升为读已提交：read committed

1. 在窗口A设置隔离级别为read committed

set global transaction isolation level read committed;
1

2. 重启窗口A查看隔离级别是否设置成功

3. 打开新的窗口B，窗口A B选择数据库，并开启事务

4. 窗口A只更新账户信息，不提交事务

5. 窗口B进行账户查询，没有查到未提交的数据

6.窗口A提交事务之后，窗口B再次查询账户信息，查询到提交之后的数据

不可重复读以及解决方案

不可重复读，就是在一个事务中，进行查询操作，每次查询到的数据都不一致

不可重复读演示：

1. 打开两个窗口A和B，选择数据库，并开启事务

2. 窗口B进行一次账户数据查询

3. 窗口A对tom的账户进行修改，并提交事务

4. 窗口B再次查询，发现同一个事务中，两次查询到的数据不一样，这就是不可重复读问题。

不可重复读解决方案

将全局的隔离级别升为：repeatable read

1. 打开窗口A，设置隔离级别为repeatable read

set global transaction isolation level repeatable read;
1

2. 重启窗口A，查看隔离级别，并选择数据库，开启事务

3. 打开新的窗口B，选择数据库，开启事务，然后进行第一次查询

4. 窗口A更新数据，并提交事务

 update account set money = money - 500 where name = 'tom';
 commit;
1
2

5. 窗口B进行第二次查询，与第一次数据一致，没有出现不可重复读问题

同一事务中，为了保证对此查询的数据一致，必须使用 repeatable read 隔离级别

幻读以及解决方案

幻读，查询某条数据发现不存在，然后准备插入该条数据，结果发现该记录已存在，无法插入，就发生了幻读。

幻读演示：

1. 打开两个窗口A、B，并开启事务

start transaction;
1

2. 窗口 A 中执行查询操作，

 select * from account where id = 3;
1

3. 窗口 B 插入一条数据，提交事务

-- 插入
insert into account values(3,'lucy',1000);
-- 提交事务
commit;
1
2
3
4

4. 窗口 A 执行插入操作，发现报错，出现幻读

窗口A查询没查到，插入为什么不可以，还报错说主键重复，为什么？

幻读解决方案

将全局的隔离级别升为：serializable

1. 打开两个窗口 A ，设置隔离级别为serializable

set global transaction isolation level serializable;
1

2. 重启窗口A，再打开一个窗口B，查询隔离级别，选择数据库并开启事务

3. 窗口 A 先执行查询操作

select * from account where id = 4;
1

4. 窗口 B 插入一条数据，发现光标一直闪烁，无法插入

insert into account values(4,'jack',1000);
1

5. 窗口 B 执行插入操作并提交事务

insert into account values(4,'jack',1000);
-- 提交事务
commit;
1
2
3

执行成功，未出现幻读

6. 窗口 B 在窗口 A 提交事务之后，开始执行，主键重复报错

注意：serializable串行化可以彻底解决幻读，但是事务只能排队执行，严重影响效率。