理解事务隔离级别与锁机制

七旅之言
211 阅读11分钟

理解事务隔离级别与锁机制

最开始学习MySQL的时候，我们很多情况是只执行单一的SQL语句，但在实际的生产环境中经常会遇到许多SQL语句需要按顺序一起执行的情况，例如银行转账，A给B转200元，那在数据库中的操作就是A需要减去200元，B需要加上200元，那如果在执行A减去200元的情况下，服务器突然发生故障，就不会执行B需要加上200元的操作，也就是说A的200元莫名其妙丢失了。很显然，这种情况是我们不允许发生的，所以有了事务的概念。

在生产环境中，有了一个事务自然会有多个事务（毕竟如果是银行的话，同一时刻不可能只有你一个人在转钱），会涉及到对同一个数据的增删改操作，如果处理不好则会导致脏读、脏写、不可重复读、幻读的问题， 所以要考虑到事务的并发性。为此MySQL设计了事务的隔离机制、锁机制、MVCC多版本并发控制隔离机制。

事务的ACID属性

  • 原子性（Atomicity）：事务是一个原子操作单元，对其数据进行修改，要么全部执行，要么不执行
  • 一致性（Consistent）：事务开始和完成时，数据都必须保持一致状态。逻辑需要保持一致（银行的例子中不能只减钱，而不加钱）
  • 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作的影响，独立环境运行
  • 持久性（Durable）：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

并发事务处理带来的问题

  • 脏写：最后的更新覆盖了其他事务所做的更新。 例如有事务A和事务B，此时AB事务对库存进行减少，A和B同时开始事务，查询库存数据为10，A事务减去5库存，将数据从10该到5，提交事务，此时数据库库存为5；因为B和A同时开始事务，所以查询库存数据也为10，B事务对库存减1，得到9，将事务提交更新，此时库存从5变为9。我们可以看到数据发生了覆盖，B事务的更新覆盖了A事务的更新，出现了脏写问题。
  • 脏读：读到了其他事务修改但未提交的数据
  • 不可重复读：在一个事务内，读同一条数据，每次读出来的结果不一样
  • 幻读：一个事务读取到了另一个事务新增的数据

并发事务处理带来的问题

  • 脏写：最后的更新覆盖了其他事务所做的更新。 例如有事务A和事务B，此时AB事务对库存进行减少，A和B同时开始事务，查询库存数据为10，A事务减去5库存，将数据从10该到5，提交事务，此时数据库库存为5；因为B和A同时开始事务，所以查询库存数据也为10，B事务对库存减1，得到9，将事务提交更新，此时库存从5变为9。我们可以看到数据发生了覆盖，B事务的更新覆盖了A事务的更新，出现了脏写问题。
  • 脏读：读到了其他事务修改但未提交的数据
  • 不可重复读：在一个事务内，读同一条数据，每次读出来的结果不一样
  • 幻读：一个事务读取到了另一个事务新增的数据

事务的隔离级别

image.png

事务级别越高，并发程度就越低，主要看业务需求来决定使用的隔离级别

查看当前事务隔离级别

show variables like 'tx_isolation';

设置事务隔离级别

set tx_isolation='REPEATABLE-READ';

锁

锁的分类

  • 从性能上分为乐观锁和悲观锁

  • 从对数据库操作上分为读锁和写锁

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

  • 从数据操作的粒度分为 表锁 和 行锁

表锁

开销小、加锁快，不会出现死锁，锁的粒度大，锁冲突概率最高，并发性能低

创建实验环境并添加数据

 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');

手动添加表锁

lock table mylock read/write;

查看表上加过的锁

show open tables

删除表锁

unlock tables
增加表读锁

image.png

我们会发现 当前用户 和 其他用户都可以读这张表

image.png

但是当前用户对表进行增删改会报错，其他用户则需要等待

image.png

增加表写锁

image.png

当前用户对表可以进行增删查改操作，其他用户任何对该表的操作都会被阻塞

image.png

总结
  • MyISAM引擎表的读锁，不会阻塞任何用户的读请求，但会阻塞写请求，只有当表读锁释放后，才会执行其他操作。
  • MyISAM引擎表的写锁，会阻塞其他用户的任何操作，但不会阻塞当前用户的读写操作，只有当表写锁被释放后，才会执行其他用户读写操作

行锁

行锁开销大，加锁慢，会发生死锁，锁的粒度小，发生冲突的概率最小，并发能力最高。

注意： MyISAM引擎和InnoDB引擎的不同

  • InnoDB支持事务
  • InnoDB支持行级锁

创建实验环境

  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');

行锁演示

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

image.png

总结
  • MYISAM引擎执行SELECT语句，会给涉及到的表加上读锁（当前用户和其他用户都不能增删改），在执行增删改操作会自动给表加写锁（当前用户可以增删改查，其他用户不能任何操作）
  • InnoDB引擎执行SELECT语句（不是串行级别）不会加锁，但是增删改操作会加行锁
  • 读锁会阻塞写，但不会阻塞读，而写锁会把读和写都阻塞

事务隔离级别分析

读未提交

设置客户端为 读未提交模式

set tx_isolation='read-uncommitted';

用事务A更新account表用户的余额，然后用事务B查询account表用户的余额

image.png

我们可以看到，事务A虽然还没有提交，但是事务B就可以查询到B已经更新的数据。

一旦事务A因为某种原因发生回滚，所有操作都会被取消，那么事务B查询到的数据则是脏数据，发生了脏读

image.png

例如：

事务A修改 id = 3 的 balance 为 200

image.png

因为隔离级别为读未提交，所以事务B能查询到 id = 3 的 余额 为 200

若事务A因为某种原因发生了回滚

而事务B正需要修改该数据

UPDATE account SET balance = balance - 200 WHERE id = 3

以事务B视角来看，得到 id = 3的 balance 应该为 0

但是实际上balance为 2400 - 200 = 2200，也就是说事务B不知道事务A发生了回滚，但却执行了该操作，发生数据不一致。

image.png

读已提交

设置客户端为读已提交

set tx_isolation='read-committed';

在事务A中修改id = 3 的balance = 200 ， 同时在事务B中查看该表，会发现事务B读不到事务A未提交的数据。

image.png

只有当事A提交事务后，在事务B中再次查询该表，则该数据发生了更新。解决了读到脏数据的问题

image.png

看上去很正常也合理对吧？但是他违背了ACID中的隔离性，出现了幻读，也就是说在同一个事务中，每次读到的数据是不一样的，在本例子中，事务B中，在事务A提交前的数据 和 事务A提交后的数据不一致，出现了不可重复读的问题

可重复读

设置客户端为可重复读

set tx_isolation='repeatable-read';

重复 在读已提交中的例子

可重复读案例：

  1. 事务A和事务B同时开启事务
  2. 事务B查询account表

image.png

  1. 事务A修改 id = 3 的 balance 为 200 ，并且提交

image.png

  1. 在事务B中，再次查询account表

image.png

我们会发现，在事务A修改数据并且已经提交后，事务B仍然查询出是事务A未作修改时候的数据，

两次结果是一致的，因此说解决了不可重复读的问题。

image.png

但是，如果我们在事务B中更新lucy的数据

UPDATE account SET balance = balance - 200 WHERE id = 3;

再在事务B中查询account表数据，会发现在执行更新语句时候，balance的值是以事务A提交后的数据来计算的，所以是 200 - 200 = 0 ，数据的一致性没有被破坏。

image.png

幻读案例：

在上述例子中，再次开启事务A，事务B仍然保持上述例子的状态

此时在事务A中新增一条记录，并且提交

image.png

在到事务B中查询记录

image.png

会发现没有幻读现象

此时如果我们用一条语句来更新 新插入的数据 即 id = 4 的数据

image.png

会发现居然更新成功了，但是我们在更新前查询确实是没有这条记录的

更新后再查询就发现有了这条数据，也就是说出现了幻读现象

串行化

设置客户端为串行化

set tx_isolation='serializable';

案例：

image.png 在该隔离模式下，事务A对id = 1 的数据进行了查询，

事务B想要对事务A的查询的数据更新会发现被阻塞了无法更新

但是如果事务B更新id = 2 的数据 却成功了

由此可知，当隔离级别为可串行时，被查询行会被加行锁

如果客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围(就算该行数据 还未被插入也会加锁，这种是间隙锁)都会被加锁。此时如果客户端B在该范围内插入数据都会被阻塞，所以就 避免了幻读。

解决幻读问题

间隙锁（Gap Lock）

间隙锁在某些情况下可以解决幻读问题

间隙锁在可重复读的隔离级别生效

现在向表中插入几条数据

INSERT INTO account VALUES(10,"Yan",999);
INSERT INTO account VALUES(20,"Lin",1400);

image.png

下面在事务A执行修改语句

UPDATE account SET name = "Hop" WHERE id > 8 and id < 18;

通过id我们可以看到间隙有以下区间

（4，10）、（10，20），（20，正无穷）这三个区间

而上述修改语句的区间在（4，10）、（10，20）中

也就是说在加了间隙锁的情况下，

处于（4，20] 的区间内增删改操作都会被阻塞

而id = 21时候则不会

image.png

image.png

临键锁（Next-key Locks）

临键锁是行锁与间隙锁的组合，上述（4,20]可被称作临键锁。

注意：无索引的行锁会变成表锁

在事务A有一条SQL语句如下

UPDATE account SET balance = 100 WHERE name = 'lilei'

对于事务B，对该表的任意一行进行增删改操作都会导致阻塞。

结论：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为 表锁。

对于分析行锁，有三个重要的变量

Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）

可以帮助我们分析等待的情况

优化建议

  • 让数据检索尽量通过索引完成，避免升级成表锁，降低效率
  • 在业务允许的情况下，事务隔离级别不要太高，影响性能
  • 尽量控制事务的大小，减少锁定长度，涉及加锁的SQL语句尽量放在事务最后执行
  • 尽量缩小锁的范围
avatar
文章
阅读
粉丝