MySQL 的锁和事务隔离级别

心城以北
696 阅读15分钟

概述

我们的数据库一般会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能出现我们常说的脏写、脏读、不可重复读、幻读这些问题。

这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制。用了一整套机制来解决多事务并发问题，接下来，我们会深入讲解这些机制，让大家彻底理解数据库的内部执行原理。

说明：本文中提到的概念和案例是基于 mysql-5.7.x 展开

事务以及其 ACID 属性

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

  • 原子性（Atpmicity） ： 事务是一个原子操作单元，其对数据的修改，要么全部执行成功，要么全部失败回滚，主要是体现事务操作层面。
  • 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态，着意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性。
  • 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务不在受外部并发操作影响的 “独立” 环境执行。这意味着事务处理过中的中间状态对外部是不可见的。反之亦然。
  • 持久性（Durable）: 事务处理完成之后，它对于数据的修改是永久性的， 即出现系统故障也能保持。

并发事务处理带来的问题

更新丢失（Lost Update）或脏写

当两个或者多个事务选择同一个行， 然后给予最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新的问题-最后的更新覆盖了其他事务所做的更新

脏读（Dirty Reads）

一个事务正在对一条数据做修改，但是这个事务在完成并提前之前，这条数据就已经就处于不一致的状态；这时，另一个事务也来取同一条记录，如果不加以控制，第二个事务读取了这些 “脏” 数据， 并且做进一步的处理，就会产生未提交的数据以来关系。这种现象叫做“脏”读。

简单理解：事务 A 读取到了事务 B 已经修改但是尚未提交的数据，并且在这个数据的基础上做了操作。此时如果 B 回滚了， A读取的数据就无效了，不符合一致性原理。

不可重复读（No-Repeatable Reads）

一个事务在读取某些数据后的某个事件，再次读取以前读取过的数据，却发现了读出的数据已经发生的改变、或某些数据已经被删除了，这种现象就叫做 “不可重复读”

简单理解： 事务 A 内部相同查询语句在不同的时刻读取出来的结果不一致，不符合隔离性。

幻读（Phantom Reads）

一个事务按相同的时间查询重新读取以前检索过的数据， 却发现其他事务插入了满足查询条件的新数据，这种现象称为“幻读”。

简单理解：事务 A 读取到了事务 B 提交的新增数据，不符合隔离性。

事务隔离级别

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

事务隔离级别

数据库的事务隔离越严格，并发带来的问题就越小，但是付出的代价也就越大，因为事务隔离实质上就是事务在一定程度上 “串行化” 进行，这显然与 “并发” 是矛盾的。同时，不同的应用对读一致性和事务隔离成都的要求也是不同的，比如许多应用对 “不可重复读” 和 “幻读” 并不敏感， 可能更关心数据并发访问的能力。

可以通过一下命令查询数据库的当前事务隔离级别：

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

设置事务隔离级别：

set tx_isolation = 'REPEATABLE-READ';

MySQL 默认的事务隔离级别是可重复读，用 Spring 开发程序时，如果不设置隔离级别默认采用 MySQL 的隔离级别，如果 Spring 配置中已经设置的就用配置的隔离级别

MySQL 中的锁详解

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

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

锁的分类

  • 性能上分为 乐观锁（用版本号对比来实现）和 悲观锁

  • 对数据操作类型分，可以分为 读锁和写锁（都属于悲观锁）

    读锁（共享锁， S 锁 （Shared））: 针对同一份数据，多个读操作可以同时进行而不会互相影响。

    写锁（排他锁， X 锁（eXclusive））: 当前写操作没有完成前，它会阻隔其他写锁和读锁。

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

表锁

每次操作会锁住整张表。 开销小，加锁快；不会出现死锁；锁定粒度打，发生锁冲突的概率最高，并发度最低；一般用在整张表数据迁移的场景。

基本操作

-- 创建表 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 `mylock` (`id`, `name`) values ('1' , 'a');
insert into `mylock` (`id`, `name`) values ('2' , 'b');
insert into `mylock` (`id`, `name`) values ('3' , 'c');
insert into `mylock` (`id`, `name`) values ('4' , 'd');

手动增加表锁

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

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

加读锁

mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)

当前 session 和其他 session 都可以读取该表

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

mysql> insert into `mylock` (`id`, `name`) values ('5' , 'd');
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

加写锁

mysql> lock table mylock write;
Query OK, 0 rows affected (0.00 sec)

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

结论

  1. 对 MyISAM 表的读操作（加锁读），不会阻塞其他进程对同一个表的读请求，但是会阻塞对同一个表的写请求。只有当读锁匙放之后，才会执行其他进程的写操作。
  2. 对 MyISAM 表的写操作（加写锁），会阻塞其他进程对同一个表的读和写操作，只有当写锁释放后，才会执行其他进程的写操作。

行锁

每次操作都锁住一行数据。开销大(会涉及到表的查询、所以开销大)，加锁慢；会出现死锁；锁定粒度最小，发生冲突的概率最低，并发高度最高。

InnoDB 与 MySIAM 的最大不同点

  • InnoDB 支持事务（Transaction）
  • InnoDB 支持行级锁

行锁场景

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

下图是我两个客户端开启事务，另外事务等待的一个过程。

总结：

MyISAM 在执行查询语句 SELECT 之前，会自动给涉及到的所有表加读锁，在执行 update 、insert 、delete 操作会自动给涉及到的表加写锁。

InnoDB 在执行查询语句 SELECT 时，因为有 MVCC 机制不会加锁。但是 update、insert 、delete 操作会加行锁。

总之，读锁阻塞写，但是不会阻塞读， 而写锁则会吧读和写都阻塞。

行锁和事务隔离级别分析

-- 创建表 SQL
create table `account` (
	`id` int(11) not null auto_increment,
  `name` varchar(20) default null,
  `balance` int(11) default null,
  primary key(`id`)
) engine = InnoDB default charset = utf8;

-- 插入数据
insert into `account` (`id`, `name`, `balance`) values ('1' , 'zhangsan', 100);
insert into `account` (`id`, `name`, `balance`) values ('2' , 'lisi', 200);
insert into `account` (`id`, `name`, `balance`) values ('3' , 'wangwu', 300);
insert into `account` (`id`, `name`, `balance`) values ('4' , 'zhaoliu', 400);

读未提交

  1. 打开一个客户端 A， 并且设置当前事务模式为 read uncommitted (未提交读)， 查询表 account 的初始值：
--客户端 A
mysql> set tx_isolation ='read-uncommitted';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |     120 |
|  2 | lisi     |     200 |
|  3 | wangwu   |     300 |
|  4 | zhaoliu  |     400 |
+----+----------+---------+
4 rows in set (0.00 sec)
  1. 在客户端 A 的事务提交以前，打开另外一个客户端 B , 对张三的余额进行修改。
-- 客户端 B
mysql> set tx_isolation ='read-uncommitted';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> update account set balance = 100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |     100 |
|  2 | lisi     |     200 |
|  3 | wangwu   |     300 |
|  4 | zhaoliu  |     400 |
+----+----------+---------+
4 rows in set (0.00 sec)
  1. 这时候， 虽然客户端 B 没有提交，但是客户端 A 已经可以查询到 B 未提交的数据此现象就是发生了 脏读
-- 客户端 A
mysql> set tx_isolation ='read-uncommitted';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |     120 |
|  2 | lisi     |     200 |
|  3 | wangwu   |     300 |
|  4 | zhaoliu  |     400 |
+----+----------+---------+
4 rows in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |     100 |
|  2 | lisi     |     200 |
|  3 | wangwu   |     300 |
|  4 | zhaoliu  |     400 |
+----+----------+---------+
4 rows in set (0.00 sec)
  1. 一但，客户端的事务因为某种原因可能会滚，所有的操作都会被测小，那么客户端 A查询到的数据就是 脏数据
-- 客户端 B
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |     120 |
|  2 | lisi     |     200 |
|  3 | wangwu   |     300 |
|  4 | zhaoliu  |     400 |
+----+----------+---------+
4 rows in set (0.00 sec)
  1. 在客户端 A执行更新语句 update account set balance = balance - 50 where id =1; id = 1 的数据变成了，70 , 不是。50， 之数据不一致， 在应用程序中如果我们用。100 - 50  = 50。 如果不知道其他的程序回滚，想要解决这个问题可以采用读已提交的隔离级别
-- 客户端A
mysql> update account set balance = balance - 50 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |      70 |
|  2 | lisi     |     200 |
|  3 | wangwu   |     300 |
|  4 | zhaoliu  |     400 |
+----+----------+---------+
4 rows in set (0.00 sec)

读已提交

  1. 客户端 A 查询 account 表，然后进行数据修改提交。

    set tx_isolation ='read-committed';

    
-- 客户端 A
mysql> set tx_isolation ='read-committed';
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 更新 id = 1 的账户余额为 700
mysql> update account set balance = 700 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 事务提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

  2. 客户端 B 先查询 A 客户端提交前后的数据，事务内部相同查询语句在不同的时刻读取出来的结果不一致，不符合隔离性。出现了不可重复读的问题

    set tx_isolation ='read-committed';

    -- 客户端 B

-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 客户端 A 提交之前查询
mysql> select * from account;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  1 | zhangsan  |      70 |
|  2 | lisi      |     200 |
|  3 | wangwu    |     300 |
|  4 | huayi     |     400 |
|  6 | wangmazi  |     600 |
|  8 | zhaoer    |     800 |
|  9 | wangmazi9 |     600 |
+----+-----------+---------+
7 rows in set (0.00 sec)

-- 客户端 A 提交后查询
mysql> select * from account;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  1 | zhangsan  |     700 |
|  2 | lisi      |     200 |
|  3 | wangwu    |     300 |
|  4 | huayi     |     400 |
|  6 | wangmazi  |     600 |
|  8 | zhaoer    |     800 |
|  9 | wangmazi9 |     600 |
+----+-----------+---------+
7 rows in set (0.00 sec)

可重复读

  1. 客户端 A 开启事务，客户端 B 也是开启事务，并且插入一条数据
-- 客户端 B
set tx_isolation = 'REPEATABLE-READ';

mysql>  select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |      70 |
|  2 | lisi     |     200 |
|  3 | wangwu   |     300 |
|  4 | huayi    |     400 |
|  6 | wangmazi |     600 |
|  8 | zhaoer   |     800 |
+----+----------+---------+
6 rows in set (0.00 sec)
  1. 客户端 A 对 B 新增的数据进行修改，然后在查询就可以查询到了。所以存在 幻读 , 违背了隔离性。
-- 客户端 A
-- 客户端提交后查询
mysql>  select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |      70 |
|  2 | lisi     |     200 |
|  3 | wangwu   |     300 |
|  4 | huayi    |     400 |
|  6 | wangmazi |     600 |
|  8 | zhaoer   |     800 |
+----+----------+---------+

-- 更新数据 9
mysql> update account set balance =   9000 where id = 9;
Query OK, 1 row affected (7.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 重新查询可见， 违背隔离性
mysql>  select * from account;
+----+-----------+---------+
| id | name      | balance |
+----+-----------+---------+
|  1 | zhangsan  |      70 |
|  2 | lisi      |     200 |
|  3 | wangwu    |     300 |
|  4 | huayi     |     400 |
|  6 | wangmazi  |     600 |
|  8 | zhaoer    |     800 |
|  9 | wangmazi9 |    9000 |
+----+-----------+---------+
7 rows in set (0.00 sec)

可重复读是 MySQL 的默认隔离级别

可串行化

  1. 打开一个客户端 A， 并且设置当前事务模式为 serializable ， 查询表 account 的初始值。

set tx_isolation = 'serializable';

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

mysql> select * from account where id = 1;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |     700 |
+----+----------+---------+
1 row in set (0.00 sec)

  1. 打开一个客户端 B ，并且设置当前事务模式为 serializable，更新相同的 id 为 1 的记录会被阻塞等待，更新 id 为 2 的记录可以成功， 说明串行模式下 innondb 的查询也会被加上行锁。

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

    这种隔离级别并发性极低，开发中很少用到

set tx_isolation = 'serializable';

mysql> set tx_isolation = 'serializable';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

-- 客户端 B会发生阻塞， 是由于是可串行化，直到锁超时
mysql> update account set balance = balance - 100 where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

间隙锁

间隙锁，锁是锁住连个值之间的空隙。 MySQL 默认的隔离级别是 REPEATABLE-READ， 有办法解决幻读吗？间隙锁在某种情况下可以解决幻读问题。

假设数据 account 的数据如下：

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |      70 |
|  2 | lisi     |     200 |
|  3 | wangwu   |     300 |
|  4 | zhaoliu  |     400 |
|  6 | wangmazi |     600 |
|  8 | zhaoer   |     800 |
+----+----------+---------+
6 rows in set (0.00 sec)

那么间隙的 id 就有（4，6）和（6，7），（8， 正无穷）这三个区间

在 session_1 下面执行 update account set name = 'huayi' where id>=4 and id < 6; 。则其他的 session 没有办法在这个范围所包含的所有行记录锁在的间隙中插入或修改任何数据，即 ID [4, 6]无法修改，注意最后的那个 6 也被包含在内的。

间隙锁是在可重复读的隔离级别上才会产生。

如果条件为 where id < 25 那么这个表不能插入 100 的数据，意思就是说，范围命中的间隙区间不能被数据插入。

临键锁（Next-ley Locks）

Next-ley Locks 是行锁约间隙锁的组合。像上面上那个例子这样 [4, 6] 的整个区间可以叫做临键锁。

无索引行会升级为表锁

锁主要是加在索引上，如果非索引字段更新，锁可能会变成表锁

session1 执行： update account set balance = 800 where name = 'zhangsan'

session2 对该表任何一行的操作都会被阻塞

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

结论

InnoDB 存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来的性能损耗可能比表级锁定会要更高一点，但是整体的并发处理能力方面要远远优于 MyISAM 的表级别锁定。当系统并发量高的时候， InnoDB 的整体性能和 MyISAM 相比就会有明显的优势了。

但是，InnoDB 的行级锁定同样也有脆弱的一面，我们使用不当的时候，可能会让 InnoDB 的整体性能表现不仅不能比 MyISAM 高，甚至还会更差

行锁分析

通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁争夺情况。

show status like 'innodb_row_lock%';

+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 101256 |
| Innodb_row_lock_time_avg      | 50628  |
| Innodb_row_lock_time_max      | 51129  |
| Innodb_row_lock_waits         | 2      |
+-------------------------------+--------+
5 rows in set (0.00 sec)

对各个状态质量的描述

Innodb_row_lock_time_avg 平均等待时间

Innodb_row_lock_waits 等待总次数

Innodb_row_lock_time 等待总时长

需要注意的的是等待次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统为什么会有这么多的等待，然后根据分析结果制定优化计划。

查询 INFORMRAMATION_SCHEMA 数据库锁相关

-- 查看事务
select * from information_schema.innodb_trx;

-- 查看锁
select * from information_schema.innodb_locks;

-- 查看锁等待
select * from information_schema.innodb_lock_waits;

-- 释放锁 trx_mysql_thread_id 可以从 innodb_trx 表中查看到
kill trx_mysql_thread_id

死锁

set tx_isolation = 'REPEATABLE-READ';

-- session1 执行：
select * from account where id = 1 for update;
-- session2 执行
select * from account where id = 2 for update;
-- session1 执行
select * from account where id = 2 for update;
-- session2 执行
select * from account where id = 1 for update;

-- 查询近期死锁日志信息
show engine innodb status\G;

大多数情况喜爱 mysql 可以自动检测死锁并且回滚产生死锁的那个事务，但是有些情况 mysql 没法自动检测死锁

锁的优化

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  • 合理设计缩影，减少锁的范围
  • 尽可能减少索引条件范围，避免间隙锁
  • 精良控制事务大小，减少锁资源的时间和长度，涉及事务加锁的 sql 尽量放在事务的最后执行
  • 尽可能降低事务隔离

参考资料

avatar
文章
阅读
粉丝