Mysql-06-事务

Mysql 事务

事务：==要么都成功，要么都失败==

举例：

1. SQL执行 A给B转账
2. SQL执行 B收到A的钱

1. 核心 ：要么都成功，要么都失败

• 将一组SQL放在同一个批次去执行
• 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
• MySQL事务处理只支持InnoDB和BDB数据表类型

2. 事务的原则：ACID原则

参考博客（https://blog.csdn.net/dengjili/article/details/82468576）

• 原子性：（Atomicity）

要么都成功，要么都失败

这个过程包含两个步骤

A： 800 - 200 = 600
B: 200 + 200 = 400

• 一致性： (Consistency)

事务前后的数据完整性要保持一致

操作前A：800，B：200
操作后A：600，B：400

一致性表示事务完成后，符合逻辑运算

• 持久性 : （Durability）

事务一旦提交则不可逆，被持久化到数据库中

操作前A：800，B：200
操作后A：600，B：400
如果在操作前（事务还没有提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：800，B：200
如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：600，B：400

• 隔离性 （Isolaiton）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他用户访问，事务之间需要相互隔离

3. Mysql事务实现办法

-- 使用set语句来改变自动提交模式
Set autocommit = 0  #关闭事务
Set autocommit = 1  #开启事务

-- 注意：
1.Mysql中默认是自动提交事务
2.使用事务时应该先关闭后自动提交

-- 步骤
-- 1.开启一个事务，标记事务的起始点
Start Transaction;

-- 2.提交一个事务给数据库
commit;

-- 3. 将事务混滚，数据回到本次事务的初始状态
Rollback;

-- 4 .还原mysql数据库的自动提交
Set autocommit = 1;

-- 5. 保存点
Savepoint  保存点名称   -- 设置一个事务的保存点
Rollback to savepoint 保存点名称  -- 回滚到保存点名称
Release  Savepoint  保存点名称   -- 删除保存点

3.1示例联系

/*
课堂测试题目

A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci; 
USE `shop`; 

CREATE TABLE `account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `cash` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)

# 转账实现
SET autocommit = 0;
START TRANSACTION;
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT;
# rollback;
SET autocommit = 1;

6. Mysql事务(面试必问)

• 事务的定义：事务会把数据会把数据库从一种一致的状态转换到另外一种一致的状态，在数据提交的工作时候，可以保证要么所有的修改都已经保存好了，要么所有的修改都不保存（要么成功，要么失败）
• 对于Innodb而言，其默认的事务隔离级别是READ REPEATABLE,完全遵循和符合事务的ACID的特性

6.1 事务的ACID原则

1. 原子性（Atomicity）

参考博客（https://blog.csdn.net/dengjili/article/details/82468576）

核心：要么都成功，要么都失败

• 事务中任何一个SQL语句执行失败，已经成功执行的SQL语句也必须要撤销，数据库状态应该退回到执行事务前的状态。

这个过程包含两个步骤

A： 800 - 200 = 600
B: 200 + 200 = 400

2. 一致性： (Consistency)

核心：事务前后的一致性要保持一致

操作前A：800，B：200
操作后A：600，B：400

一致性表示事务完成后，符合逻辑运算

3. 隔离性（Isolaiton）

核心：事务的隔离性是多个用户并发访问数据库的时候，数据库为每一个用户开启的事务，不能被其他用户访问，事务之间需要相互隔离。（通常使用锁来实现）

• 事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即事务提交前对其他事务都不可见，通常这使用锁来实现。

4. 持久性（Durability）

核心：事务一旦提交则不可逆，被持久化到数据库中

操作前A：800，B：200
操作后A：600，B：400

• 如果在操作前（事务还没提交）服务器宕机或者断电，那么重启数据库之后，数据状态应该为 A：800 B: 200
• 如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库之后，数据状态应该为 A: 600 B : 400

6.2 并发操作带来的隔离性的问题

• 并发操作带来的隔离性的问题：本质上是锁带来的问题

写的还可以的参考博客：https://www.cnblogs.com/shan-kylin/p/9543294.html

1. 丢失更新

很明显的看出，旺财对A添加的20块不翼而飞了，这就是“数据丢失”，对事务不加任何锁（不存在事务隔离），就会导致这种问题。

• 简单的来说就是一个事务的更新操作会被另外一个事务的更新操作所覆盖。
• 要解决这个问题需要让事务的操作变成串行化，而不是并行操作。（Select … for update : 读的时候加一把排他锁）

逻辑意义的丢失更新出现的场景：

1. 事务T1 查询一行数据，放入本地内存，并显示给User1
2. 事务T2 查询同一行数据，放入本地内存，并显示给User2
3. User1修改数据，更新数据库并提交
4. User2修改数据，更新数据库并提交

2. 脏读：一个事务读取了另一个事务未提交的数据

首先分清楚脏页和脏数据的完全不同的概念

• 脏页： 缓冲池中已经修改的页，但还没有刷新到磁盘中，即数据库中的页和磁盘中的页数据是不一样的。
• 脏数据：事务对缓冲池中行记录的修改，并且还没有提交

3. 不可重复读：

• 在一个事务中多次读取同一个数据集合，在这个事务还没有结束的时候，另外一个事务也访问该同一个数据集合，并做了一些DML操作。因此在第一个事务中两次读取数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能不一样，这种情况叫做不可重复读。
• 与脏读的区别是，不可重复读读到的是已提交的数据，脏读读到的是未提交的数据。

不可重复读（Non-repeatable Reads）：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。

解决方法——引入隔离级别更高事务隔离：可重复读

4. 幻读

例如：

有一次旺财对一个“学生表”进行操作，选取了年龄是18岁的所有行， 用X锁锁住， 并且做了修改。

改完以后旺财再次选择所有年龄是18岁的行， 想做一个确认， 没想到有一行竟然没有修改！

这是怎么回事？ 出了幻觉吗？

原来就在旺财查询并修改的的时候， 小强也对学生表进行操作， 他插入了一个新的行，其中的年龄也是18岁！ 虽然两个人的修改都没有问题， 互不影响， 但从最终效果看， 还是出了事。

解决幻读的方式——串行化

• 但是innodb并不是这么做的 ,接下里我们看Mysql如何解决幻读的（因为mysql的隔离级别还是可重复读）

6.2.1 面试官：mysql如何解决幻读

参考博客：https://blog.csdn.net/sinat_27143551/article/details/81736330

MySQL InnoDB支持三种行锁定方式：

• 行锁（Record Lock）：锁直接加在索引记录上面。
• 间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。
• Next-Key Lock：行锁与间隙锁组合起来用就叫做Next-Key Lock。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举例来说，假如user表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

select * from  user where user_id > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的user_id值为101的记录加锁，也会对user_id大于101（这些记录并不存在）的“间隙”加锁。

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

• 是一个范围条件的检索，
  • InnoDB不仅会对符合条件的user_id值为101的记录加锁，
  • 也会对user_id大于101（这些记录并不存在）的“间隙”加锁。

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了user_id大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要

注意：

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。**如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁，仔细想想的话，这个并不难理解，大家也可以自己测试一下。*

6.2.2 快照读和当前读

快照读历史数据－mvcc

innodb的默认事务隔离级别是rr（可重复读）。它的实现技术是mvcc（MVCC只在读提交可重复读两种隔离级别下工作）。基于版本的控制协议。该技术不仅可以保证innodb的可重复读，而且可以防止幻读。但是它防止的是快照读，也就是读取的数据虽然是一致的，但是数据是历史数据。（http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html这个帖子里面就有一个实例）

当前读最新数据－next-key lock

如何做到保证数据是一致的（也就是一个事务，其内部读取对应某一个数据的时候，数据都是一样的），同时读取的数据是最新的数据。innodb提供了next－key lock，也就是结合gap锁与行锁，达到最终目的。

实现：

1. 快照读(snapshot read)

简单的select操作(不包括 select ... lock in share mode, select ... for update)

2.当前读(current read)

select ... lock in share mode

select ... for update

insert

update

delete

在RR级别下，快照读是通过MVCC(多版本控制)和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。

总结：

• MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。

• 很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件；当然，对一条不存在的记录加锁，也会有间隙锁的问题。

• 本事务中第一次读取出一行，做了一次更新后，另一个事务里提交的数据就出现了。也可以看做是一种幻读：

t Session A                 Session B
|
| START TRANSACTION;        START TRANSACTION;
|
| SELECT * FROM innodb_lock;
| +------+-------+
| | a    | b    |
| +------+-------+
| |    1 | a    |
| +------+-------+
|                           INSERT INTO innodb_lock
|                           VALUES (2, 'b');
|
| SELECT * FROM innodb_lock;
| +------+-------+
| | a    | b    |
| +------+-------+
| |    1 | a    |
| +------+-------+
|                           COMMIT;
|
| SELECT * FROM innodb_lock;
| +------+-------+
| | a    | b    |
| +------+-------+
| |    1 | a    |
| +------+-------+
|
| UPDATE innodb_lock SET b='z';
| Rows matched: 2  Changed: 2 Warnings: 0
| (怎么多出来一行)
|
| SELECT * FROM innodb_lock;
| +------+-------+
| | a    | b    |
| +------+-------+
| |    1 | z    |
| |    2 | z    |
| +------+-------+
|

6.3 事务的隔离级别

为了避免上面出现的几种情况，在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。

• 读未提交（Read Uncommitted） ： 只处理更新丢失。如果一个事务已经开始写数据，则不允许其他事务进行写操作，但允许其他事务读数据。可通过“排他写锁”实现。（ 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。）

  简单一句话：操作：写数据的时候添加一个X锁（排他锁），也就是在写数据的时候不允许其他事务进行写操作，但是读不受限制，读不加锁。（这样就可以解决了多个人一起写数据而导致了“数据丢失”的问题，但是会引发新的问题——脏读。）

• 读已提交（Read Committed）：处理更新丢失，脏读。读取数据的事务允许其他事务继续访问修改数据，但是未提交的写事务会禁止其他事务访问修改。可通过“瞬间共享读锁”和“排他写锁”实现。（它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。）

操作：写数据的时候加上X锁（排他锁），读数据的时候添加S锁（共享锁），而且有约定：如果一个数据加了X锁就没法加S锁；同理如果加了S锁就没法加X锁，但是一个数据可以同时存在多个S锁（因为只是读数据），并且规定S锁读取数据，一旦读取完成就立刻释放S锁（不管后续是否还有很多其他的操作，只要是读取了S锁的数据后，就立刻释放S锁）。

这样就解决了脏读的问题，但是又有新的问题出现——不可重复读。

解决方法——引入隔离级别更高事务隔离：可重复读

• 可重复读（Repeatable Read）: 处理更新丢失，脏读和不可重复读。读取数据的事务会禁止写事务，但允许读事务，写事务则禁止任何其他事务。可通过“共享读锁”和“排他写锁”实现。（Mysql在REPEATABLE READ 事务隔离的级别下，使用next-key-lock算法，来避免幻读的产生。）（Mysql 默认的隔离级别，它保证同一个事务在多个实例并发读取的时候，会看到同样的数据行。）

操作：对S锁进行修改，之前的S锁是：读取了数据之后就立刻释放S锁，现在修改是：在读取数据的时候加上S锁，但是要直到事务准备提交了才释放该S锁，X锁还是一致。

这样就解决了不可重复读的问题了，但是又有新的问题出现——幻读。

例如：

有一次旺财对一个“学生表”进行操作，选取了年龄是18岁的所有行， 用X锁锁住， 并且做了修改。

改完以后旺财再次选择所有年龄是18岁的行， 想做一个确认， 没想到有一行竟然没有修改！

这是怎么回事？ 出了幻觉吗？

原来就在旺财查询并修改的的时候， 小强也对学生表进行操作， 他插入了一个新的行，其中的年龄也是18岁！ 虽然两个人的修改都没有问题， 互不影响， 但从最终效果看， 还是出了事。

（码农翻身注： 正是小强的操作， 让旺财出现了“幻读”）

解决幻读的方式——串行化

• 序列化（Serializable） ： 提供严格的事务隔离级别。要求失去序列化执行，事务只能一个一个的执行，不能并发的执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。（主要用于Innodb的分布式事务）（最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读的问题，简而言之，就是在每个读的数据行上加上共享锁。）

事务只能一件一件的进行，不能并发进行。

注意：

• 隔离级别越高，越能保证数据的完整性和统一性，但是对并发性能的影响也越大。
• 对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读，而且具有较好的并发性能。
• 尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

打赏