mysql lock、transaction 和 mvcc

发表于 | 分类于

一、Locking 锁

1、共享锁和独占锁

InnoDB实现了两种 行级锁：共享锁（S） 和 独占锁（X）

共享锁：允许 事务 持有 read 一行 的锁；

独占锁：允许 事务 持有 update 或 delete 一行的锁；


2、Intention Locks 意向锁

​ InnoDB 支持 多颗粒度加锁，允许 行锁 和 表锁 共存。例如， LOCK TABLES ... WRITE 就是在指定表上加独占锁。为了支持多颗粒度加锁，InnoDB设计了 意向锁。意向锁 是 一种 表级锁，用来 指示 一个事务对表中的一行数据需要加 哪种类型（shared 或 exclusive）的锁。

意向锁有两种：

  • intention shared lock (IS)意向共享锁：表示 一个事务 倾向于 对一行数据 使用共享锁；在 transaction 获取一个 共享锁（S） 之前 必须先获得一个 IS 锁；

  • intention exclusive lock (IX)意向独占锁：表示 一个事务 倾向于 对一行数据 使用独占锁；在 transaction 获取一个 独占锁（X） 之前 必须先获得一个 IX 锁；

例如：SELECT ... LOCK IN SHARE MODE 使用的就是 IS 锁， SELECT ... FOR UPDATE 使用的是 IX 锁；

表级锁的兼容性：

X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

如果请求事务与现有锁兼容，则授予它锁，但如果与现有锁冲突，则不授予它锁。事务将一直等待，直到释放冲突的现有锁。如果锁请求与现有锁发生冲突，并且由于会导致死锁而无法被授予，则会发生错误。


3、Record Locks 记录锁

record lock 是 对 index record 加锁的一种锁。例如 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 阻止其他事务 插入、更新、删除 t.c1=10 的行；

record lock 总是对 index record 加锁，即使没有对表定义任何索引。在这种情况下 InnoDB 会添加一个隐藏主键；


4、Gap Locks 间隙锁

​ 一个 gap lock 是 加在 索引记录之间的 锁。例如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE可以防止其他事务把 t.c1=15的记录插入表中；

​ 一个 gap 可能是 一行记录，多行记录，或者 为空；

​ gap lock 是 性能 和 并发 之间的一种折中方案，只在某些隔离级别下生效；

SELECT * FROM child WHERE id = 100;

  • 如果id本身是唯一索引，那么 gap lock 是不生效的；
  • 如果 id 和 其他的列 组成的联合索引是 位移索引， 那么 gap lock 是生效的，锁定了 child[100][x] ~ child[100][y] 之间的记录；
  • 如果 id 上 没有加索引 或者 不是唯一索引，gap lock 生效；

简单来说就是 gap lock 锁定的是一个范围（0~N条记录），而不是单条记录；

​ InnoDB中的Gap锁是“纯粹的抑制性锁”，这意味着它们的唯一目的是防止其他事务插入到Gap中。间隙锁可以共存。一个事务所采取的间隙锁并不会阻止另一个事务对同一间隙采取间隙锁。共享锁和独占锁之间没有区别。它们彼此不冲突，并且执行相同的功能。

禁用gap lock：

  • 将 隔离级别 设为 READ COMMITTED ；
  • 启用 innodb_locks_unsafe_for_binlog

5、Next-Key Locks

next-key lock 是 record lock 和 gap lock 的组合

当 InnoDB 搜索索引时，它会在 index 上加一个行级锁；因此 行级锁 实际上 是 index-record lock。

假设 一个 index 包含 10，11，13，20。那么可能的 next-key lock 如下：

1
2
3
4
5
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

在 REPEATABLE READ 隔离级别下，并且禁用了 innodb_locks_unsafe_for_binlog, InnoDB 使用 next-key lock 扫描索引的时候 可以防止幻读；


6、Insert Intention Locks

child表包含90，102两条数据，事务A获取一个 gap lock （100, max_value）

1
2
3
4
5
6
7
8
9
10
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

如果事务B 想插入 101，则需要等待事务A提交

1
2
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

7、AUTO-INC Locks

auto-inc lock 是一种特殊的表级锁。如果一个事务正在向表中插入值，那么任何其他事务都必须等待，以便由第一个事务插入的行接收连续的主键值。

innodb_autoinc_lock_mode 用于


二、不同sql语句设置的锁



三、Deadlocks 死锁

​ 死锁是指由于每个事务都持有对方需要的锁而无法进行其他事务的情况。因为这两个事务都在等待资源变得可用，所以都不会释放它持有的锁。

1、死锁示例

该示例涉及两个客户端A和B。

首先，客户端A创建一个包含一行的表，然后开始事务。在事务中，A通过S在共享模式下选择该行来获得对该行的 锁定：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

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

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+

接下来，客户端B开始事务并尝试从表中删除该行：

1
2
3
4
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

删除操作需要一个X锁。无法授予该S锁，因为它与客户端A持有的锁不兼容 ，因此该请求进入针对行和客户端B块的锁请求队列中。

最后，客户端A还尝试从表中删除该行：

1
2
3
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

此处发生死锁是因为客户端A需要 X锁才能删除该行。但是，不能授予该锁定请求，因为客户端B已经有一个X锁定请求，并且正在等待客户端A释放其S锁定。由于B事先要求锁，所以SA持有的锁也不能 升级 X为X锁。结果， InnoDB为其中一个客户端生成错误并释放其锁。客户端返回此错误：

1
2
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

届时，可以授予对另一个客户端的锁定请求，并从表中删除该行。


2、死锁检测和回滚

​ InnoDB自动检测事务 死锁并回滚一个或多个事务以打破死锁。 InnoDB尝试选择要回滚的小事务，其中事务的大小由插入，更新或删除的行数确定。

​ 如果死锁无法检测，通过设置 innodb_lock_wait_timeout 来解决；

​ 如果 一个事务被完整的会滚，那么它所持有的所有锁都会被释放；但如果由于出错仅仅一条sql被会滚，那么某些锁可能不会被释放；这是因为 InnoDB存储行锁的格式 无法确定后续的锁会被哪些sql持有；

  • 要查看InnoDB用户事务中的最后一个死锁，请使用SHOW ENGINE INNODB STATUS命令。

  • 如果频繁出现死锁，则说明事务结构或应用程序错误处理存在问题，请在innodb_print_all_deadlocks 启用该设置的情况下运行，以 将有关所有死锁的信息打印到 mysqld错误日志中


3、如何最小化并处理死锁

InnoDB使用自动行级锁定。即使在仅插入或 删除 单行的事务中，也可能会遇到死锁。这是因为这些操作并不是真正的“ 原子 ”操作。它们会自动对插入或删除的行的（可能是多个）索引记录设置锁定。

您可以使用以下技术来处理死锁并减少发生死锁的可能性：

  • 在任何时候，发出 SHOW ENGINE INNODB STATUS命令以确定最近死锁的原因。这可以帮助您调整应用程序以避免死锁。

  • 如果频繁出现死锁警告引起关注，请通过启用innodb_print_all_deadlocks 配置选项来收集更广泛的调试信息 。有关每个死锁的信息，而不仅仅是最新的死锁，都记录在MySQL 错误日志中。完成调试后，请禁用此选项。

  • 如果由于死锁而失败，请始终准备重新发出事务。死锁并不危险。请再试一次。

  • 保持交易小巧且持续时间短，以使交易不易发生冲突。

  • 进行一系列相关更改后立即提交事务，以减少冲突的发生。特别是，不要长时间未提交事务而使交互式 mysql会话保持打开状态。

  • 如果您使用锁定读取（SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE），请尝试使用较低的隔离级别，例如 READ COMMITTED。

  • 修改事务中的多个表或同一表中的不同行集时，每次都要以一致的顺序执行这些操作。然后，事务形成定义明确的队列，并且不会死锁。例如，组织数据库操作到功能在应用程序中，或调用存储程序，而不是编码的多个相似序列 INSERT，UPDATE以及 DELETE在不同的地方语句。

  • 将选择好的索引添加到表中。然后，您的查询需要扫描更少的索引记录，并因此设置更少的锁。使用EXPLAIN SELECT以确定哪些索引MySQL认为最适合您的查询。

  • 使用较少的锁定。如果你能负担得起，以允许 SELECT从一个旧的快照返回数据，不要添加条款FOR UPDATE或LOCK IN SHARE MODE给它。在READ COMMITTED这里使用隔离级别是件好事，因为同一事务中的每个一致性读取均从其自己的新快照读取。

  • 如果没有其他帮助，请使用表级锁序列化事务。LOCK TABLES与事务表（例如InnoDB 表）一起使用的正确方法 是，以SET autocommit = 0（not START TRANSACTION）后跟来开始事务，直到明确提交事务后才LOCK TABLES调用 UNLOCK TABLES。例如，如果您需要写表 t1和从表中读取数据 t2，则可以执行以下操作：

    1
    2
    3
    4
    SET autocommit=0;
    LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...
    COMMIT;
    UNLOCK TABLES;

    表级锁可防止对表的并发更新，从而避免死锁，但代价是对繁忙系统的响应速度较慢。

  • 序列化事务的另一种方法是创建一个仅包含一行的辅助“ 信号量 ”表。在访问其他表之前，让每个事务更新该行。这样，所有事务都以串行方式发生。请注意，InnoDB 在这种情况下，即时死锁检测算法也适用，因为序列化锁是行级锁。对于MySQL表级锁，必须使用超时方法来解决死锁。


四、Transaction Model 事务模型

​ InnoDB 的事务模型 的目标是 将 multi-versioning 和two-phase locking 的最佳属性结合起来。默认情况下 InnoDB 的查询以 “非锁定一致性读” 和 行级锁 的方式运行。

1、autocommit, commit, rollback

​ 在 InnoDB 中 所有的 用户操作都包裹在事务中；如果 启用了 autocommit，那么每条语句都是一个事务；默认情况下，每创建一个session，autocommit 都是启用的；如果SQL语句正确执行，那么就会自动条，否则会滚之前的操作，然后报错；

在 autocommit 情况下 如果 想把多条语句包裹在一个事务里，则需要如下格式：

1
2
3
4
5
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
# 不想提交，则执行 ROLLBACK;

禁用 autocommit：

1
SET autocommit=0;

2、存在的问题

​ 平常开发过程中免不了对数据库的操作，并且还会有多个线程同时开启事务后对数据库进行访问，那此时不可避免就会出现多个线程之间交叉访问而导致数据的不一致，通过对数据库的隔离级别进行设置可以保证各线程数据获取的准确性。

在介绍隔离级别之前先要弄清楚数据库在并发事务下会出现的一些状态：

1)、脏读

脏读就是一个事务读取了另外一个事务未提交的数据。

事务2读取了事务1未提交的数据。

2)、不可重复读

在同一事务中，两次读取同一数据，得到内容不同

例如事务1读取了某个数据，然后事务2更新了这个数据并提交，然后事务1又来读取了一次，那这两次读取的结果就会不一样。

3)、幻读

在一个事务的两次查询中数据记录数不一致，例如有一个事务1查询了几列数据，而事务2在此时插入了新的几列数据，事务1在接下来的查询中，就会发现有几列数据是它先前所没有的。

不可重复读是针对于多次读取同一条数据出现不同结果，幻读是多次读取而产生的记录数不一样


3、隔离级别（Isolation）

​ Isolation 是 ACID 中的 I；Isolation 是在多个事务同时进行更改和执行查询时，对性能与可靠性、一致性和结果再现性之间的平衡进行微调的设定。

​ InnoDB 提供了 4 种隔离级别：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, 和 SERIALIZABLE。默认的 隔离级别是 REPEATABLE READ。

​ InnoDB 使用不同的锁策略 来实现隔离级别。REPEATABLE READ 用来操作重要的数据，保证 ACID；如果使用 READ UNCOMMITTED 或READ COMMITTED 可以降低锁的开销；SERIALIZABLE是一种比 SERIALIZABLE更严格的规则，一般用在专门的场景，比如 XA事务 或者 解决并发问题和死锁。

1)、read uncommitted

可以看到未提交的数据（脏读），举个例子：别人说的话你都相信了，但是可能他只是说说，并不实际做。

2)、read committed

读取提交的数据。但是，可能多次读取的数据结果不一致（不可重复读，幻读）。用读写的观点就是：读取的行数据，可以写。

3)、repeatable read(默认)

可以重复读取，但有幻读。读写观点：读取的数据行不可写，但是可以往表中新增数据。在MySQL中，其他事务新增的数据，看不到，不会产生幻读。采用多版本并发控制（MVCC）机制解决幻读问题。

4)、serializable

可读，不可写。像java中的锁，写数据必须等待另一个事务结束。


4、查看隔离级别

1
2
3
4
-- 1.查看当前会话隔离级别
select @@tx_isolation;
-- 查看系统当前隔离级别
select @@global.tx_isolation;

设置隔离级别

1
2
3
4
5
6
7
8
-- 设置当前会话隔离级别
set session transaction isolatin level repeatable read;
-- 设置系统当前隔离级别
set global transaction isolation level repeatable read;
set tx_isolation='read-uncommitted';
set tx_isolation='read-committed';
set tx_isolation='repeatable-read';
set tx_isolation='serializable';

五、隔离级别实现原理

1、MVCC

参考： https://www.cnblogs.com/cjsblog/p/8365921.html

　　MVCC的全称是“多版本并发控制”。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证，换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大的技术，因为这样的一来的话查询就不用等待另一个事务释放锁。这项技术在数据库领域并不是普遍使用的。一些其它的数据库产品，以及mysql其它的存储引擎并不支持它。

1)、说明

​ 网上看到大量的文章讲到MVCC都是说给每一行增加两个隐藏的字段分别表示行的创建时间以及过期时间，它们存储的并不是时间，而是事务版本号。

​ 事实上，这种说法并不准确，严格的来讲，InnoDB会给数据库中的每一行增加三个字段，它们分别是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

​ 但是，为了理解的方便，我们可以这样去理解，索引接下来的讲解中也还是用这两个字段的方式去理解。

2)、增删查改

​ 在InnoDB中，给每行增加两个隐藏字段来实现MVCC，一个用来记录数据行的创建时间，另一个用来记录行的过期时间（删除时间）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。

于是乎，默认的隔离级别（REPEATABLE READ）下，增删查改变成了这样：

  • SELECT：读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
  • INSERT：将当前事务的版本号保存至行的创建版本号
  • UPDATE：新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号
  • DELETE：将当前事务的版本号保存至行的删除版本号

3)、快照读和当前读

  • 快照读：读取的是快照版本，也就是历史版本

  • 当前读：读取的是最新版本

​ 普通的SELECT就是快照读，而UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

2、一致性非锁定读和锁定读

1)、锁定读

　　在一个事务中，标准的SELECT语句是不会加锁，但是有两种情况例外。SELECT … LOCK IN SHARE MODE 和 SELECT … FOR UPDATE。

  • SELECT … LOCK IN SHARE MODE：给记录假设共享锁，这样一来的话，其它事务只能读不能修改，直到当前事务提交；

  • SELECT … FOR UPDATE：给索引记录加锁，这种情况下跟UPDATE的加锁情况是一样的；

2)、一致性非锁定读

一致性读（consistent read）意味着 InnoDB 对一个 query 展示的数据是 多版本中一个时间点的 snapshot。

  • 如果隔离级别是REPEATABLE READ，那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照；

  • 如果是READ COMMITTED，那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。

Consistent read（一致性读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们。

3、悲观锁和乐观锁

  • 悲观锁，正如它的名字那样，数据库总是认为别人会去修改它所要操作的数据，因此在数据库处理过程中将数据加锁。其实现依靠数据库底层。

  • 乐观锁，如它的名字那样，总是认为别人不会去修改，只有在提交更新的时候去检查数据的状态。通常是给数据增加一个字段来标识数据的版本。

4、锁

有这样三种锁我们需要了解

  • Record Locks（记录锁）：在索引记录上加锁。
  • Gap Locks（间隙锁）：在索引记录之间加锁，或者在第一个索引记录之前加锁，或者在最后一个索引记录之后加锁。
  • Next-Key Locks：在索引记录上加锁，并且在索引记录之前的间隙加锁。它相当于是Record Locks与Gap Locks的一个结合。

假设一个索引包含以下几个值：10,11,13,20。那么这个索引的next-key锁将会覆盖以下区间：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

了解了以上概念之后，接下来具体就简单分析下REPEATABLE READ隔离级别是如何实现的

5、理论分析

​ 之所以说是理论分析，是因为要是实际操作证明的话我也不知道怎么去证明，毕竟作者水平实在有限。但是，这并不意味着我在此胡说八道，有官方文档为证。

​ 这段话的大致意思是，在默认的隔离级别中，普通的SELECT用的是一致性读不加锁。而对于锁定读、UPDATE和DELETE，则需要加锁，至于加什么锁视情况而定。如果你对一个唯一索引使用了唯一的检索条件，那么只需锁定索引记录即可；如果你没有使用唯一索引作为检索条件，或者用到了索引范围扫描，那么将会使用间隙锁或者next-key锁以此来阻塞其它会话向这个范围内的间隙插入数据。

​ 作者曾经有一个误区，认为按照前面说MVCC下的增删查改的行为就不会出现任何问题，也不会出现不可重复读和幻读。但其实是大错特错。

​ 举个很简单的例子，假设事务A更新表中id=1的记录，而事务B也更新这条记录，并且B先提交，如果按照前面MVVC说的，事务A读取id=1的快照版本，那么它看不到B所提交的修改，此时如果直接更新的话就会覆盖B之前的修改，这就不对了，可能B和A修改的不是一个字段，但是这样一来，B的修改就丢失了，这是不允许的。

​ 所以，在修改的时候一定不是快照读，而是当前读。

​ 而且，前面也讲过只有普通的SELECT才是快照读，其它诸如UPDATE、删除都是当前读。修改的时候加锁这是必然的，同时为了防止幻读的出现还需要加间隙锁。

  • 一致性读保证了可用重复读
  • 间隙锁防止了幻读

回想一下

1、利用MVCC实现一致性非锁定读，这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题

2、利用Gap Locks和Next-Key可以阻止其它事务在锁定区间内插入数据，因此解决了幻读问题

综上所述，默认隔离级别的实现依赖于MVCC和锁，再具体一点是一致性读和锁。