MySQL事务

事务

什么是事务

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

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

模拟转账操作

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

1. 创建一张账户表

-- 创建账户表CREATETABLE ACCOUNT(
    id INTPRIMARYKEYAUTO_INCREMENT,
    NAME VARCHAR(20),
    money DOUBLE);-- 向表中插入两个用户INSERTINTO ACCOUNT(NAME,money)VALUES('tom',1000),('jerry',1000);

1. 模拟转账

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

注意：

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

MySQL事务操作

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

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

手动提交事务

语法：

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

案例：模拟转账

1. 查看account表原本数据

select*from account;

2. 开启事务

starttransaction;

3. 转账

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

4. 提交事务

commit;

5. 查看账户余额变化

事务回滚

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

1. 开启事务

starttransaction;

1. 插入两条数据

INSERTINTO ACCOUNT(NAME,money)VALUES('tony',1000);INSERTINTO ACCOUNT(NAME,money)VALUES('jack',1000);

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

自动提交事务

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

自动提交事务演示

1. 给tom账户余额加500

取消自动提交

show variables like'autocommit';

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

把autocommit修改为off

set @@autocommit=off;

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

事务的四大特性

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

MySQL隔离级别

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

并发访问的问题

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

隔离级别

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

注意

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

隔离级别相关的命令

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

-- 设置隔离级别为读已提交SETGLOBALTRANSACTIONISOLATIONLEVELREADCOMMITTED;-- 隔离级别有以下四种：-- read uncommitted 读未提交-- read committed   读已提交-- repeatable read  可重复读-- serializable     串行化

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

脏读以及解决方案

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

脏读演示：

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

setglobaltransactionisolationlevelreaduncommitted;

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

select @@transaction_isolation;

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

starttransaction;select*from account;

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

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

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

select*from account;

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

rollback;

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

select*from account;

脏读解决方案：

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

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

setglobaltransactionisolationlevelreadcommitted;

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

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

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

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

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

不可重复读以及解决方案

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

不可重复读演示：

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

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

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

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

不可重复读解决方案

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

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

setglobaltransactionisolationlevelrepeatableread;

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

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

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

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

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

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

幻读以及解决方案

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

幻读演示：

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

starttransaction;

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

select*from account where id =3;

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

-- 插入insertinto account values(3,'lucy',1000);-- 提交事务commit;

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

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

幻读解决方案

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

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

setglobaltransactionisolationlevelserializable;

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

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

select*from account where id =4;

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

insertinto account values(4,'jack',1000);

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

insertinto account values(4,'jack',1000);-- 提交事务commit;

执行成功，未出现幻读

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

注意：

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