性能优化｜MVCC通俗理解与事务隔离级别实战操作

点击上方蓝字关注我们 文末有惊喜

表锁与行锁的理解

表锁

• 定义 进行表操作的时候，mysql会锁住整张表，加锁和解锁效率非常高，不会出现死锁竞争，但是会造成锁阻塞，导致并发低。
• 命令
  • 手动加表锁：lock table tableName1 read[write],tableName2 read[write];
  • 查看表上加过的锁： show open tables where in_use > 0;
  • 解除锁： UNLOCK tables;
• 实战 创建初始化的表：

代码语言：javascript复制

   SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `locak_table`;
CREATE TABLE `locak_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `locak_table` VALUES (1, '刘备', 350);
INSERT INTO `locak_table` VALUES (2, '张飞', 16000);
INSERT INTO `locak_table` VALUES (3, '关羽', 2400);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

图中圆圈在转表示被阻塞

• 会话A给表加写锁，会话B读或者写被阻塞

• 会话A给表加读锁，会话B写会被阻塞，读不阻塞

行锁

定义

为每一行数据添加锁，加锁慢，容易出现死锁竞争，因为锁的每一行数据，锁的力度小，所以并发高，Innodb支持行级锁，行级锁是支持事务的。

行级锁事务的ACID属性

• 原子性：为了完成一个业务，需要执行多条数据库修改语句，这些语句中如果哪条执行失败，将会全部回滚第一条语句执行前的状态，如果没有执行失败的语句，则代表执行成功，一句话理解：要么全部执行，要么全部不执行。
• 一致性：一致性关注的是事务开始前的状态和事物结束时和库中状态要保持一致。
• 隔离性：在执行事物的过程中，对数据库的操作对其他事务是不可见的，只有提交了事务，外界才能看到变化后的数据。
• 持久性：事务对数据库的修改都必须永久保存在磁盘文件中（数据库中）。

多个事务同时操作数据带来的并发问题

• 脏读 有两个事务A和B，如果A事务修改了数据库的内容，被B事务读到了，可这个时候A回滚了事务，这个时候B读到的事务就是不存在，这个就是脏读（读到了不存在的数据）
• 不可重复读 事务A使用同样的查询条件的SQL查询数据库，两次查询的结果不一致，事务A读到了其他事务已提交修改的数据，这就是不可重复读。
• 幻读 在事务中， 1.事务A首先按照id为1的检索条件去库中查询，发现没有此条记录 2.在事务A查询完之后，事务B插入了一条id为1的记录。3.事务A为了保证业务正确性，在进行一系列操作后，又查询了一遍，确保记录不存在，由于可重复的隔离性，这个时候还是查不到该条记录的。3.事务A在判断没有此条记录之后，就往库里面插入一条，但是此时会发现库中有两条记录，违背了业务逻辑。这个只是幻读的其中一个模拟场景。 这纯属个人的理解，如果有不同见解，可以忽略此条解释！！！

如何解决这些并发问题呢?mysql使用四大隔离级别来解决，一级更比一级强，并发量一级比一级低。

事务的隔离级别

• 读未提交：最低级别，也是并发数最高的，有可能出现脏读、不可重读读、幻读，也可以说根本没有解决事务并发带来的问题，写操作比较少的场景可以使用该级别
• 读已提交：有可能出现不可重复读、幻读。
• 可重复读：可能出现幻读
• 可串型化：彻底解决并发问题，它使用“单线程”控制事务的执行顺序。

mysql默认的事务隔离级别是：读已提交（REPEATABLE-READ）设置隔离级别：set tx_isolation='REPEATABLE-READ'; 查询支持的事务隔离级别：show variables like 'tx_isolation';

事务隔离级别的验证

初始化SQL:

代码语言：javascript复制

CREATE TABLE `locak_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `xnyh`.`locak_table`(`id`, `name`, `age`) VALUES (1, '刘备', 350);
INSERT INTO `xnyh`.`locak_table`(`id`, `name`, `age`) VALUES (2, 'hanmei', 16000);
INSERT INTO `xnyh`.`locak_table`(`id`, `name`, `age`) VALUES (3, 'lucy', 2400);

• 验证读未提交

事务A操作

事务B对数据库进行了操作，但是还没有提交事务

在事务A中再执行一下查询语句，发现事务B的修改已经被事务A查询到了

在业务操作中，我们一般会将读到的数据取出来，做更新操作，如果现在需要对age 100，那么事务A更新到库中的结果是400，但是如果事务B回滚了，库中age就会恢复到350的状态，如果这时候事务B把400更新到库中，400相当于350并没有执行加100的操作，所以就出现了脏读，造成业务数据出现问题。

如果需要读到别人确保不会回滚的数据，那就得设置隔离级别为read-committed

• 验证读已提交 set tx_isolation='read-committed'; 事务A执行同样的操作

事务B

事务A再次执行查询语句发现并没有查询到B未提交的语句，解决了脏读问题

事务B提交事务

事务A再进行查询，发现读到了别人提交的修改数据

虽然这个解决了脏读的问题，但是又会造成一个新的问题，就是相同的查询条件，查询结果不一致，产生了不可重复读的问题，这又得设置隔离级别为可重复读来解决。

• 验证可重复读 set tx_isolation='repeatable-read'; 因为在5.7中，mysql默认的事务隔离级别就是可重复读，所以可以不用进行声明设置。需要注意的是验证可重复读，不能再使用navcat客户端来执行sql的，我试了几次发现在navcat验证可重复读会失效，具体原因没有研究，我们现在使用mysql命令行来验证。事务A设置事务为可重读，并查询

事务B修改数据并提交

事务A再次查询:

发现查询结果和之前一致，如果这时候我们在事务A中对age进行修改操作会出现什么效果呢？

大家觉得，现在库里面应该是多少呢，是300 300还是321 300？

可以看到是300 300=600，使用了事务B的修改数据，也就是当事务A执行修改操作的时候，会使用库中最新的状态来进行修改，从而保证了数据的一致性，这里面的机制是在可重复读的隔离级别中，mysql使用MVCC（多版本并发控制）机制来实现的。具体实现机制，我会在最后介绍。 现在我们已经把可重复读验证完了，但是你不要觉得可重复读就是完美的了，它也是有缺陷的，有可能会出现幻读的现象，幻读的理解在前面已经解释过，不理解的同学可以回头再看一遍。

既然有幻读这个问题，mysql又是如何解决的呢？1.使用可串型化,这个没有什么可解释的，像“单线程”一样按照顺序依次执行每个事务；2.在某些场景下可以使用间隙锁解决

• 间隙锁如何解决幻读的问题？间隙锁就是锁住某个范围区间的索引，当你开启事务后，使用了间隙锁 ,那么别的用户就不能对操作者范围区间的数据了。如果在可重读隔离模式下，可以在sql语句上面加上for update，让mysql给索引加上间隙锁，如：我想查询id = 10 的记录是否存在，如果不存在就插入一条select * from locak_table where id = 10 for update; 当mysql发现索引中有这条记录，就会加上行锁，如果没有就会使用间隙锁：如果数据库中最大的自增ID是8，那么这个时候间隙锁的范围就是8-11区间的值都被锁住，其他数据无法插入。如果当前字段没有创建索引则会升级为表锁。

MVCC机制

定义

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

什么是当前读和快照读

• 当前读 读取事务中最新的数据记录，并且能够保证自己在读取之后，其他事务不能并发修改当前数据。
• 快照读 快照读不需要加锁去读取数据库中的数据，它依赖多版本的机制，在开启事务后，他会被分配一个最新的事务ID，它只会读取小于等于当前事务ID之前的数据，也就是有可能他会读到历史版本的数据，但是她读到的数据是不会变化的，这也就是MVCC的实现机制，这也就是为什么MVCC能够解决不可重复读的原因。

MVCC带来的好处

• 多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个事务ID，读操作只读该事务开始前的数据库的快照。所以MVCC可以为数据库解决以下问题
• 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

MVCC的具体实现

Innodb 的MVCC机制会在底层为数据库中的每行记录都扩展两个虚拟字段（创建事务ID、删除事务ID），我们对数据进行添加操作时，会在记录的创建事务ID字段中保存当前事务ID；删除记录的时候，会将当前事务ID保存在删除数据中的删除事务ID中，并不会直接删除数据；更新数据的时候，会将当前修改后的数据复制一条出来插入库中，然后记录的创建事务ID为当前事务ID，并且将旧记录的删除事务ID更新当前事务ID；我们接下来看增删改查是如何操作的。假设当前库中的最新事务ID为1

• 演示添加数据

代码语言：javascript复制

begin;
 
  INSERT INTO `xnyh`.`locak_table`(`id`, `name`) VALUES (1, '张飞');
  INSERT INTO `xnyh`.`locak_table`(`id`, `name`) VALUES (2, '刘备');
  commit;

执行完上面的语句后，库中最新的事务ID为2了，需要注意的是，事务分配ID并不是在begin之后就会被分配新的事务ID，只有你在执行了update or insert or delete语句之后，才会向mysql申请新的事务ID

这时候表中的数据应该是这样的

id	name	创建事务ID	删除事务ID
1	张飞	2	Undefined
2	刘备	2	Undefined

如果这个时候事务ID为1进行查询，是查询不到的，因为库中记录的创建事务ID是大于1 的。

• 演示修改数据

代码语言：javascript复制

begin;
update locak_table set name= '刘禅' where id =1;
commit;

当前已经为事务ID为3 了

• mysql首先复制一条当前记录，插入数据库，并将name修改为新值，创建事务ID保存为当前事务ID,
• 将老数据的删除事务ID更新为当前事务ID 表中当前记录应该是这样：

id	name	创建事务ID	删除事务ID
1	张飞	2	3
2	刘备	2	Undefined
1	刘禅	3	Undefined

如果这个时候事务ID为2 进行查询，是查询不到新修改的数据，它的查询条件是这样的 创建事务ID要小于等于2并且删除事务ID大于2 或者为undefined。所以事务2查出来的数据还是2条

• 删除验证 删除和更新操作是一样的，不过不需要复制一条新的数据，只需要把当前事务ID赋值给删除记录的删除事务ID。 文末给大家留一个疑问： 如果当前有两个事务A和事务B，如果事务A进入开启事务后，并且获得事务ID为10，事务B开启事务后，并且获取事务ID为11，如果这个时候事务ID10向库中添加了一条记录，并提交，请问：事务ID为11的能查到么？如果查不到那是为什么呢？欢迎大家在下面留言回答。

作者：乐哉

图片：来源于网络，如有侵权，联系删除。

云数据库SQLServer mvcc mvc 数据库 sql

0 人点赞