关于mysql:mysql事务

事务

数据筹备

-- 创立数据表
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    balance DOUBLE
);
-- 增加数据
INSERT INTO account (name, balance) VALUES ('张三', 1000), ('李四', 1000);

概述

在理论的开发过程中，一个业务操作如：转账，往往是要屡次拜访数据库能力实现的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。

事务执行是一个整体，所有的 SQL 语句都必须执行胜利。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。

代码演示

/* 模仿转账的操作 */

-- 张三账号 -500
update account set balance = balance - 500 where name = '张三';
-- 李四账号 +500
update account set balance = balance + 500 where name = '李四';

假如当张三账号上 -500 元, 服务器解体了。李四的账号并没有 +500 元，数据就呈现问题了。咱们须要保障其中 一条 SQL 语句呈现问题，整个转账就算失败。只有两条 SQL 都胜利了转账才算胜利。这个时候就须要用到事务.

分类

MySQL中能够有两种形式进行事务的操作：

1. 手动提交事务
2. 主动提交事务

手动提交

MySQL默认事务是主动提交的，所以想要开启事务，就须要执行对应的命令。相干的命令如下：

• 开启事务

  start transaction;

• 提交事务

  commit;

• 回滚事务

  rollback;

代码演示

-- 重置表数据
update account set balance = 1000;

-- 开启事务
start transaction;

-- 张三账户 -500
update account set balance = balance - 500 where name = '张三';

-- 李四账号 +500
update account set balance = balance + 500 where name = '李四';

-- 提交
commit;

-- 查看表数据, 数据已发现扭转
select * from account;

-- 重置表数据
update account set balance = 1000;

-- 开启事务
start transaction;

-- 张三账户 -500
update account set balance = balance - 500 where name = '张三';

-- 李四账号 +500
update account set balance = balance + 500 where name = '李四';

-- 回滚
rollback;

-- 查看表数据, 数据未发现扭转
select * from account;

如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行扭转。如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

主动提交

MySQL 默认每一条 DML(增删改)语句都是一个独自的事务，每条语句都会主动开启一个事务，语句执行结束 主动提交事务，MySQL 默认开始主动提交事务。

• 查看是否了开启主动提交事务

  SELECT @@AUTOCOMMIT;-- @@示意全局变量，1 示意开启，0 示意敞开

• 勾销主动提交事务

  SET @@AUTOCOMMIT = 0;

• 开启主动提交事务

  SET @@AUTOCOMMIT = 1;

代码演示

-- 重置表数据
update account set balance = 1000;

-- 查看是否开启了主动提交事务
SELECT @@AUTOCOMMIT;

-- 勾销主动提交事务
SET @@AUTOCOMMIT = 0;

-- 张三账户 -500
update account set balance = balance - 500 where name = '张三';

-- 李四账号 +500
update account set balance = balance + 500 where name = '李四';

-- 回滚
rollback;

-- 查问表数据，数据未产生扭转（主动提交事务已敞开，回滚，会导致后面的 SQL 生效）select * from account;

-- 重置表数据
update account set balance = 1000;

-- 查看是否开启了主动提交事务
SELECT @@AUTOCOMMIT;

-- 张三账户 -500
update account set balance = balance - 500 where name = '张三';

-- 李四账号 +500
update account set balance = balance + 500 where name = '李四';

-- 提交
commit;

-- 查问表数据，数据已产生扭转（主动提交事务已敞开，只有 commit，能力提交数据）select * from account;

-- 重置表数据
update account set balance = 1000;

-- 查看是否开启了主动提交事务
SELECT @@AUTOCOMMIT;

-- 开启主动提交事务
SET @@AUTOCOMMIT = 1;

-- 张三账户 -500
update account set balance = balance - 500 where name = '张三';

-- 李四账号 +500
update account set balance = balance + 500 where name = '李四';

-- 回滚
rollback;

-- 查问表数据，数据已产生扭转(主动提交事务已开启，回滚有效)
select * from account;

事务原理

事务开启之后, 所有的操作都会长期保留到事务日志中, 事务日志只有在失去 commit 命令才会同步到数据表 中，其余任何状况 (rollback，断开连接) 都会清空事务日志。

事务步骤

1. 客户端连贯数据库服务器，创立连贯时创立此用户长期日志文件
2. 开启事务当前，所有的操作都会先写入到长期日志文件中
3. 所有的查问操作从表中查问，但会通过日志文件加工后才返回
4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

回滚点

概述

在某些胜利的操作实现之后，后续的操作有可能胜利有可能失败，然而不论胜利还是失败，后面操作都曾经胜利，能够在以后胜利的地位设置一个回滚点。能够供后续失败操作返回到该地位，而不是返回所有操作，这个点称之为 回滚点。

设置回滚点

SAVEPOINT 回滚点名字;

返回回滚点

ROLLBACK TO 回滚点名字;

代码演示

-- 重置表数据
update account set balance = 1000;

-- 开启事务
start transaction;

-- 李四账号 -100
update account set balance = balance - 100 where name = '李四';

-- 设置回滚点
savepoint one_time;

-- 李四账号再 -100
update account set balance = balance - 100 where name = '李四';

-- 返回回滚点
rollback to one_time;

-- 提交
commit;

-- 查问表数据，发现李四账号只缩小了 100
select * from account;

事务特色

1. 原子性(Atomicity)

   每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行胜利，要么都失败。

2. 一致性(Consistency)

   事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前 2 集体的总金额是 2000，转账后 2 集体总金额也是 2000

3. 隔离性(Isolation)

   事务与事务之间不应该相互影响，执行时放弃隔离的状态。

4. 持久性(Durability)

   一旦事务执行胜利，对数据库的批改是长久的。就算关机，也是保留下来的。

事务隔离级别

事务在操作时的现实状态是：所有的事务之间放弃隔离，互不影响。

然而因为并发操作，多个用户同时拜访同一个数据。就可能引发并发拜访的问题，如下：

• 脏读

  一个事务读取到了另一个事务中尚未提交的数据。

• 不可反复读

  一个事务中两次读取的数据内容不统一（update 时引发的问题）

• 幻读

  一个事务中两次读取的数据的数量不统一（insert 或 delete 引发的问题）

下表为 MySQL 数据库中的四种隔离级别，每种级别可能又不同的拜访问题：

隔离级别越高，性能越差，安全性越高。

相干命令

• 查问全局事务隔离级别

  SELECT @@TX_ISOLATION;

• 设置事务隔离级别（需重登 MySQL，能力看出隔离级别变动）

  SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

代码演示

/* 脏读演示 */

-- 关上 A 窗口登录 MySQL，重置表格
update account set balance = 1000;

-- A 窗口设置全局的隔离级别为最低(read uncommitted——读未提交)
set global transaction isolation level read uncommitted;

-- 再关上 B 窗口登录 MySQL, 进入指定数据库，而后 A B 窗口都开启事务
start transaction;

-- A 窗口更新 2 集体的账户数据，不提交
update account set balance = balance - 500 where name = '张三';
update account set balance = balance + 500 where name = '李四';

-- B 窗口查问账户, 后果读取了 A 窗口尚未提交的事务，这就叫作脏读
select * from account;

-- A 窗口回滚
rollback;

-- B 窗口查问，表数据又变为原来的样子
select * from account;

脏读十分危险的，比方张三向李四购买商品，张三开启事务，向李四账号转入 500 块，而后打电话给李四说钱 曾经转了。李四一查问钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

解决脏读的方法：将全局的隔离级别进行晋升为read committed

/* 解决脏读演示 */

-- 关上 A 窗口登录 MySQL，重置表格
update account set balance = 1000;

-- A 窗口设置全局的隔离级别为 read committed- 读已提交
set global transaction isolation level read committed;

-- B 窗口重登 MySQL，A B 窗口都开启事务
start transaction;

-- A 窗口更新 2 人账号，不提交
update account set balance = balance - 500 where name = '张三';
update account set balance = balance + 500 where name = '李四';

-- B 窗口查问账户，并没有读取到 A 窗口尚未提交的数据(脏读已被解决)
select * from account;

-- A 窗口提交事务
commit;

-- B 窗口查看账户，才读取到 A 窗口曾经提交的数据(脏读已被解决)
select * from account;

隔离级别为 read committed 能够防止 脏读 的产生

/* 不可反复读演示 */

-- 关上 A 窗口登录 MySQL，重置表格
update account set balance = 1000;

-- A 窗口设置全局的隔离级别为 read committed- 读已提交
set global transaction isolation level read committed;

-- B 窗口重登 MySQL，并开启事务
start transaction;

-- B 窗口查问表数据
select * from account;

-- A 窗口也开启事务
start transaction;

-- A 窗口更新 2 人账号，并提交
update account set balance = balance - 500 where name = '张三';
update account set balance = balance + 500 where name = '李四';
commit;

-- B 窗口查问，发现查问到的数据与前一次不同
select * from account;

两次查问输入的后果不同，这也是一种危险状况！咱们能够思考这样一种状况，比方银行程序须要将查问后果别离输入到电脑屏幕和发短信给客户，后果在一个事务中针对不同的输入目的地进行的两次查问不统一，导致文件和屏幕中的后果不统一，这样就可能会导致账务谬误。

解决不可反复读的方法：将全局的隔离级别晋升为repeatable read

/* 解决不可反复读演示 */

-- 关上 A 窗口登录 MySQL，重置表格
update account set balance = 1000;

-- A 窗口设置全局的隔离级别为 repeatable read- 可反复读
set global transaction isolation level repeatable read;

-- B 窗口重登 MySQL，并开启事务
start transaction;

-- B 窗口都查问表数据
select * from account;

-- A 窗口也开启事务

-- A 窗口更新 2 人账号，并提交
update account set balance = balance - 500 where name = '张三';
update account set balance = balance + 500 where name = '李四';
commit;

-- B 窗口查问，发现查问到的数据与前一次统一
select * from account;

隔离级别为 repeatable read 能够防止 不可反复读 的产生

首先须要申明的是：在 MySQL 中，咱们是无奈看到幻读的成果的。然而咱们能够将事务隔离级别设置到最高，以挡住幻读的产生，将数据进行复原。

/* 幻读演示 */

-- 关上 A 窗口登录 MySQL，重置表格
update account set balance = 1000;

-- A 窗口设置全局的隔离级别为最高 serializable- 串行化
set global transaction isolation level serializable;

-- A 窗口重登 MySQL, 并开启事务
start transaction;

-- A 窗口查问表记录总数
select count(*) from account;

-- B 窗口登录 MySQL，并开启事务
start transaction;

-- B 窗口增加一条记录(此时回车确认时，会发现操作没有持续进行，光标仍在闪动)
insert into account (name, balance) values ('Jason', 500);

-- A 窗口中执行 commit 提交事务后，B 窗口中的 insert 语句就立刻执行结束
commit;

-- A 窗口中查问表记录总数，发现总数未扭转，B 窗口的 insert 没起作用
select count(*) from account;

-- B 窗口中执行 commit 提交事务
commit;

-- A 窗口查问表记录总数，发现总数曾经扭转，B 窗口的 insert 起作用了
select count(*) from account;

应用 serializable 隔离级别，一个事务没有执行完，其余事务的 SQL 执行不了，能够挡住幻读

总结

1. 默认状况下 autocommit（主动提交）= 1 时：
   执行 sql 批改语句（insert into，update…set，delete from）后，是立刻失效的；

2. 若手动批改 autocommit（主动提交）=0，即手动提交时：
   执行 sql 批改语句（insert into，update，delete from）后，不会失效；

   在以后终端查看到的数据是存在缓存里的数据；

   从新关上一个终端查看，会发现数据没有失效；

   须要输出 commit 命令，提交数据到数据库，这样才会失效；

3. 不论是 autocommit= 1 或 = 0 时；开启事务应用了 begin 命令，执行了 sql 批改语句后，都必须要应用 commit 命令提交数据
   给数据库，不然不会失效；
   如果应用了 begin，没有应用 commit, 而是用 begin 另外开启了一个事务，之前的批改会被提交，隐式提交；
   即：应用 begin 开启事务 -> 前面必须应用 commit/begin(其余命令不行)，sql 批改语句能力失效；
4. 以上要应用到 commit 的，在 commit 之前，应用命令 rollback，都能够使数据回滚
5. 事务开启到完结：begin+commit/rollback

6. 对于 set autocommit= 0 的设置的无效范畴（）：

   • 只在以后操作的 mysql 数据库无效，长期无效
   • 退出以后 mysql 数据库（未退出终端），生效
   • 退出以后终端，再从新应用 mysql，生效
7. 事务的开启，与是否抉择了特定的数据库无关。即：在执行 use database_name 命令前，就能够通过 begin/start transaction 来开启事务，而后在抉择你须要应用的数据库，进行数据操作。
8. begin和 start transaction 等价，都是显式开启一个事务；commit 和 commit work 等价，都是对事物进行提交；rollback 和 rollback work 等价，都是对事物进行回滚；