2021-03-17 18:07:21

后端

Mysql锁与事务隔离级别

1. 锁定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

2. 锁分类

从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
从对数据操作的粒度分，分为表锁和行锁

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

2.1 表锁

每次操作锁住整张表。

开销小，加锁快；

不会出现死锁；

锁定粒度大，发生锁冲突的概率最高，并发度最低；

例如MyISAM中就是表锁

‐‐建表SQL
CREATE TABLE `mylock` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR (20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

‐‐插入数据
INSERT INTO `test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO `test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO `test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');

2.1.1 常用操作

手动增加表锁

lock table 表名称 read(write),表名称2 read(write);

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

2.1.2 加读锁

lock table person read;

当前session和其他session都可以读该表。当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待。

解锁

unlock tables;

2.1.3 加写锁

lock table person write;

当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞。

2.1.4 MyISAM的表锁

MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改
操作前,会自动给涉及的表加写锁。

对MyISAM表的读操作(加读锁) ,不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

2.2 行锁

每次操作锁住一行数据。
开销大，加锁慢；
会出现死锁；
锁定粒度最小，发生锁冲突的概率最低，并发度最高

2.2.1 InnoDB与MYISAM的不同点之处

2.2.2 行锁支持事务

事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。

原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持

而并发事务处理带来的问题

更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。
脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。
一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
不可重读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性。
幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

事务隔离级别
脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。

同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

常看当前数据库的事务隔离级别: show variables like 'tx_isolation';
设置事务隔离级别：set tx_isolation='REPEATABLE-READ';

2.2.3 行锁与隔离级别案例分析

CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`balance` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lilei', '450');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei','16000');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '2400');

1.行锁演示

一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞。

2.读未提交

打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表account的初始值：

客户端A
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> start TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 赵铁柱0 |  18 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.13 sec)

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 当前事务模式为read uncommitted（未提交读）
start TRANSACTION; 开启事务，当然也可以使用begin

此时我再开一个客户端B，对第一条记录进行修改，将名字改为 康师傅 ，但是不提交事务

客户端B
mysql> start TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `person2` SET `name` = '康师傅' WHERE `id` = 1000114;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 康师傅  |  18 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.04 sec)

我们可以看到在客户端B已经将第一条记录改为的name改为康师傅了

再回到客户端A

客户端A
mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 康师傅  |  18 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.04 sec)

客户端A也查到了修改未提交的数据

再去客户端B将数据进行回滚

客户端B
mysql> rollback;
Query OK, 0 rows affected (0.14 sec)

mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 赵铁柱0 |  18 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.04 sec)

mysql>

一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询的数据其实就是脏数据

刚刚说到客户端B进行了回滚，但是客户端A想执行更新语句，试图将康师傅的年龄改为38 UPDATE person2 SET age=38 WHERE id = 1000114;

客户端A
mysql> UPDATE person2 SET age=38 WHERE id  = 1000114;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 赵铁柱0 |  38 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.06 sec)

mysql>

好家伙，此时铁柱0号的年纪变成了38岁，而康师傅人都不见了。因为在客户端A并不知道客户端B的会话已经回滚了

上面的这种问题就是出现了脏读，要想解决这个问题可以采用读已提交的隔离级别

3.读已提交

将上述的数据进行撤回，并打开两个客户端A，客户端B

客户端A，将当前事务模式设置为read committed,查询person2表的所有记录

客户端A
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 赵铁柱0 |  18 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.04 sec)

mysql>

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 设置当前事务模式为read committed

在客户端A提交之前，切换到客户端B，更新person表

客户端B
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `person2` SET `name` = '康师傅' WHERE `id` = 1000114;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 康师傅  |  18 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.03 sec)

mysql>

此时客户端B的事务还没有提交，回到客户端A进行查询

客户端A

mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 赵铁柱0 |  18 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.06 sec)

mysql>

查到的还是赵铁柱0号，客户端A不能查询到客户端B已经更新但是没提交的数据，解决了脏读的问题

此时我们把客户端B的数据进行提交，再回到客户端A进行查询

客户端B
mysql> commit;
Query OK, 0 rows affected (0.75 sec)

mysql>

客户端A
mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 康师傅  |  18 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.06 sec)

mysql>

客户端A查到了客户端B修改已提交的数据，虽然解决了脏读的问题，但是此时出现了不可重复读的问题

4.可重复读

打开一个客户端A，并设置当前事务模式为repeatable read，查询表person2的所有记录

客户端A
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 赵铁柱0 |  18 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.03 sec)

mysql>

打开客户端B，更新person2表的第一条记录，name改为康师傅，提交

客户端B
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `person2` SET `name` = '康师傅' WHERE `id` = 1000114;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.16 sec)

mysql>

回到客户端A，进行查看person2的表记录

客户端A
mysql> select * from person2;
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| id      | name    | age | sex | height | weight | job    | salary | create_time         | create_by | update_time | update_by | remark |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
| 1000114 | 赵铁柱0 |  18 | 1   |    180 |    180 | 搬砖的 |   1000 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000115 | 赵铁柱1 |  18 | 1   |    180 |    180 | 搬砖的 |   1001 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000116 | 赵铁柱2 |  18 | 1   |    180 |    180 | 搬砖的 |   1002 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
| 1000117 | 赵铁柱3 |  18 | 1   |    180 |    180 | 搬砖的 |   1003 | 2021-03-13 16:49:37 | system    | NULL        | NULL      | NULL   |
+---------+---------+-----+-----+--------+--------+--------+--------+---------------------+-----------+-------------+-----------+--------+
4 rows in set (0.04 sec)

mysql>

第一条记录仍然是赵铁柱，解决了不可重复读的问题

可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select操作不会更新版本号，是快照版本（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

3. 【转载】MVCC多版本并发控制

3.1 前提概要

3.1.1 什么是MVCC?

MVCC

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

3.2 什么是当前读和快照读？

在学习MVCC多版本并发控制之前，我们必须先了解一下，什么是MySQL InnoDB下的当前读和快照读?

当前读
像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读
像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

3.2.1 当前读，快照读和MVCC的关系

准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念
而在MySQL中，实现这么一个MVCC理想概念，我们就需要MySQL提供具体的功能去实现它，而快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。而相对而言，当前读就是悲观锁的具体功能实现。
要说的再细致一些，快照读本身也是一个抽象概念，再深入研究。MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志，Read View 等去完成的，具体可以看下面的MVCC实现原理

3.2.2 MVCC能解决什么问题，好处是？

数据库并发场景有三种，分别为：

读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

MVCC带来的好处是？

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。所以MVCC可以为数据库解决以下问题。

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

小结一下咯

总之，MVCC就是因为大牛们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案，所以在数据库中，因为有了MVCC，所以我们可以形成两个组合：

MVCC + 悲观锁
MVCC解决读写冲突，悲观锁解决写写冲突
MVCC + 乐观锁
MVCC解决读写冲突，乐观锁解决写写冲突
这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题

3.3 MVCC的实现原理

MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志，Read View 来实现的。所以我们先来看看这个三个point的概念

3.3.1 隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID等字段

DB_TRX_ID

6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
DB_ROLL_PTR

7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
DB_ROW_ID

6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

如上图，DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前操作该记录的事务ID,而DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本

3.3.2 undo日志

undo log主要分为两种：

insert undo log

代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
update undo log

事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

purge

从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。

为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

对MVCC有帮助的实质是update undo log ，undo log实际上就是存在rollback segment中旧记录链，它的执行流程如下：

一、比如一个有个事务插入person表插入了一条新记录，记录如下，name为Jerry, age为24岁，隐式主键是1，事务ID和回滚指针，我们假设为NULL

二、现在来了一个事务1对该记录的name做出了修改，改为Tom

在事务1修改该行(记录)数据时，数据库会先对该行加排他锁
然后把该行数据拷贝到undo log中，作为旧记录，既在undo log中有当前行的拷贝副本
拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务ID为当前事务1的ID, 我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本记录，既表示我的上一个版本就是它
事务提交后，释放锁

三、又来了个事务2修改person表的同一个记录，将age修改为30岁

在事务2修改该行数据时，数据库也先为该行加锁
然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面
修改该行age为30岁，并且修改隐藏字段的事务ID为当前事务2的ID, 那就是2，回滚指针指向刚刚拷贝到undo log的副本记录
事务提交，释放锁

从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

3.3.3 Read View(读视图)

什么是Read View?

什么是Read View，说白了Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)

所以我们知道 Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本。

那么这个判断条件是什么呢？

如上，它是一段MySQL判断可见性的一段源码，即changes_visible方法（不完全哈，但能看出大致逻辑），该方法展示了我们拿DB_TRX_ID去跟Read View某些属性进行怎么样的比较

在展示之前，我先简化一下Read View，我们可以把Read View简单的理解成有三个全局属性

trx_list（名字我随便取的）
一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID
up_limit_id
记录trx_list列表中事务ID最小的ID
low_limit_id
ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1

首先比较DB_TRX_ID（最近修改/插入事务ID） < up_limit_id（事务ID最小的ID）, 如果小于，则当前事务能看到DB_TRX_ID所在的记录，如果大于等于进入下一个判断
接下来判断 DB_TRX_ID （最近修改/插入事务ID）>= low_limit_id(尚未分配的下一个事务ID) , 如果大于等于则代表DB_TRX_ID 所在的记录在Read View生成后才出现的，那对当前事务肯定不可见 ，如果小于则进入下一个判断
判断DB_TRX_ID （最近修改/插入事务ID）是否在活跃事务之中，trx_list.contains(DB_TRX_ID)，如果在，则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在Read View生成之前就已经Commit了，你修改的结果，我当前事务是能看见的。

给大家翻译一下这段话

	事务B，在事务A之前创建的事务，修改数据已经提交	事务C，修改数据未提交	事务D，在事务A之后创建的事务，并对数据进行了修改提交
当前事务A是否可见	可见	不可见	不可见

3.3.4 整体流程

我们在了解了隐式字段，undo log，以及Read View的概念之后，就可以来看看MVCC实现的整体流程是怎么样了

整体的流程是怎么样的呢？我们可以模拟一下

当事务2对某行数据执行了快照读，数据库为该行数据生成一个Read View读视图，假设当前事务ID为2，此时还有事务1和事务3在活跃中，事务4在事务2快照读前一刻提交更新了，所以Read View记录了系统当前活跃事务1，3的ID，维护在一个列表上，假设我们称为trx_list（维护Read View生成时刻系统正活跃的事务ID）
Read View不仅仅会通过一个列表trx_list来维护事务2执行快照读那刻系统正活跃的事务ID，还会有两个属性up_limit_id（记录trx_list列表中事务ID最小的ID），low_limit_id(记录trx_list列表中事务ID最大的ID，也有人说快照读那刻系统尚未分配的下一个事务ID也就是目前已出现过的事务ID的最大值+1，我更倾向于后者；所以在这里例子中up_limit_id就是1，low_limit_id就是4 + 1 = 5，trx_list集合的值是1,3，Read View如下图
我们的例子中，只有事务4修改过该行记录，并在事务2执行快照读前，就提交了事务，所以当前该行当前数据的undo log如下图所示；我们的事务2在快照读该行记录的时候，就会拿该行记录的DB_TRX_ID去跟up_limit_id（活跃事务ID最小的ID）,**low_limit_id（系统尚未分配的下一个事务ID）**和活跃事务ID列表(trx_list)进行比较，判断当前事务2能看到该记录的版本是哪个。

所以先拿该记录DB_TRX_ID（最近修改/插入事务ID） 字段记录的事务ID 4去跟Read View的up_limit_id（活跃事务ID最小的ID） 比较，看是否小于up_limit_id(1)（系统尚未分配的下一个事务ID），所以不符合条件，继续判断 4 是否大于等于 low_limit_id(5)，也不符合条件，最后判断4是否处于trx_list中的活跃事务, 最后发现事务ID为4的事务不在当前活跃事务列表中, 符合可见性条件，所以事务4修改后提交的最新结果对事务2快照读时是可见的，所以事务2能读到的最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度上最新的版本。

也正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同

4. MVCC相关问题

4.1 RR是如何在RC级的基础上解决不可重复读的？

当前读和快照读在RR级别下的区别：
表1:

表2:

而在表2这里的顺序中，事务B在事务A提交后的快照读和当前读都是实时的新数据400，这是为什么呢？

这里与上表的唯一区别仅仅是表1的事务B在事务A修改金额前快照读过一次金额数据，而表2的事务B在事务A修改金额前没有进行过快照读。

所以我们知道事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读的地方非常关键，它有决定该事务后续快照读结果的能力

我们这里测试的是更新，同时删除和更新也是一样的，如果事务B的快照读是在事务A操作之后进行的，事务B的快照读也是能读取到最新的数据的

4.2 RC,RR级别下的InnoDB快照读有什么不同？

正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同

在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View, 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见；
即RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见
而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因

总之在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。

# mysql 数据库