MySQL 事务、并发问题、隔离级别

罐装面包
328 阅读7分钟

参考文献

官方文档

共享锁和排他锁

InnoDB 实现了标准的行级锁，有两种类型：shared locks、exclusive locks，有时会简写为 S、X。

  1. 共享锁允许持有该锁的事务读取某一行；
  2. 排他锁允许持有该锁的事务更新或删除某一行。

如果事务 T1 持有某一行 r 的 S 锁，来自事务 T2 对于锁的请求，不同情况如下：

  1. 事务 T2 请求 S 锁立即被授予。如此，T1 和 T2 同时持有对  r 的 S 锁；
  2. 事务 T2 请求 X 锁无法被立即授予。

如果事务 T1 持有某一行 r 的 X 锁，来自事务 T2 对于任意类型的锁都无法立即授予。事务 T2 必须等待 T1 释放在行 r 上的 X 锁。

 

意向锁

InnoDB 支持多粒度锁，行锁和表锁可以共存。例如，LOCK TABLES ... WRITE 语句在指定表上设置排他锁（X 锁）。

为了确保多粒度锁的可行性，InnoDB 采用意向锁的概念。意向锁是表级锁，表示之后某一行需要某一种锁（S 或者 X）。有两种意向锁：

  1. 意向共享锁（IS）表示事务意图在某一行上设置 S 锁；
  2. 意图排他锁（IX）表示事务意图在某一行上设置 X 锁

例如，SELECT ... FOR SHARE 可以设置 IS 锁，SELECT ... FOR UPDATE 可以设置 IX 锁。

意向锁的协议如下：

  1. 在事务可以获取 S 锁之前，它必须先获取 IS 锁或者更强的锁。
  2. 在事务可以获取 X 锁之前，它必须先获取 IX 锁或者更强的锁。

 

表级锁的兼容性如下：

XIXSIS
XConflictConflictConflictConflict
IXConflict CompatibleConflictCompatible
SConflictConflictCompatibleCompatible
ISConflictCompatibleCompatibleCompatible

某事务请求锁，如果该锁与现有锁兼容，则可以授予；如果冲突则拒绝。事务等待，直到释放锁。

如果锁的请求与现有锁冲突，并且因为死锁的原因无法授予，这就发生了错误。

意向锁不会阻止任何请求，除了全表请求（例如，LOCK TABLES ... WRITE）。意向锁的目的在于，表明某个事务正在锁定某一行，或者即将锁定某一行。

 

 

 

 

表结构

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY idx_name(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', 'name01');
INSERT INTO `user` VALUES ('5', 'name05');
INSERT INTO `user` VALUES ('7', 'name07');
INSERT INTO `user` VALUES ('11', 'name11');

 

记录锁（Record Locks）

记录锁是对于索引记录的锁。例如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 防止其他事务 insert、update、delete 那些 t.c1 = 10 的行。

记录所始终锁定的是索引记录，即使是没有索引定义的表。对于这种情况，InnoDB 创建隐藏的聚簇索引，并让这个索引用于记录锁。

记录锁是封锁记录，记录锁也叫行锁，例如：select * from goodswhereid = 1 for update;    

记录锁、间隙锁、临键锁都属于排他锁

间隙锁是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的返回。

产生间隙锁的条件（RR 隔离级别）

  1. 使用普通索引锁定；

  2. 使用多列唯一索引；

  3. 使用唯一索引锁定多行记录

对于使用唯一索引来搜索某一行记录加锁的语句，不会产生间隙锁，只会使用记录锁。

 

间隙锁

间隙锁是一种索引之间的间隙形成的锁，第一个索引记录之前或者最后一个索引记录之后也会产生间隙锁。

例如，对于语句 SELECT c1 FROM t BETWEEN 10 AND 20 FOR UPDATE; 如果其他事务意图插入 t.c1 列值为 15 的值，将会被阻止。无论是否列中是否存在此类值，因为范围内的值都会被锁定。当然，这里的列 c1 一定添加了索引，如果 c1 是一个没有索引的普通列，那么该表的所有记录都会加上记录锁，相当于锁住全表。

间隙可能跨域 1 个索引值，多个索引值，甚至也可能是空的。

间隙锁是性能与并发之间权衡的一部分，并且仅仅用于某些事务隔离级别。

对于那些使用唯一索引去搜索某一行记录的语句，间隙锁是没有必要的。例如，username 列是唯一索引，那么语句 SELECT * FROM child WHERE username = 'admin'; 对于 username = 'admin' 的记录仅仅使用一个 record lock，其他会话依然可以插入任何记录。

如果 id 具有非唯一索引，则该语句锁定前面的间隙。比如 SELECT * FROM user WHERE id = 5; 表中的记录 id 分别是 1 5 7， 那么将会锁住 (1, 5]

 

临键锁

临键锁是记录锁与间隙锁的组合，其实就是某一条索引的记录锁以及这条索引记录之前的间隙锁（左开右闭）。

InnoDB 以这样的方式执行行级锁定：当 InnoDB 扫描表索引时，它会在它遇到的索引记录上设置 S 锁或者 X 锁。因此，行级锁实际上时索引记录锁。索引记录上的临键锁也会影响索引记录之前的间隙。也就是说，临键锁是某一个索引记录锁 + 这条索引记录之前的间隙锁。如果一个会话持有记录 R 的索引 S 锁或者 X 锁，那么以索引的顺序排序，其他会话无法在记录 R 之前的间隙中立即插入一个新的索引记录。

假设索引值包含 10、11、13、20。该索引可能的临键锁包含以下间隔：

  • (negative infinity, 10]
  • (10, 11]
  • (11, 13]
  • (13, 20]
  • (20, positive infinity)   默认情况下，InnoDB 以 REPEATABLE READ 隔离级别运行。在这种情况下，InnoDB 使用临键锁进行搜索和索引扫描，可以防止幻影行。  

事务一般满足 ACID：

Atomicity、Consistency、Isolation、Durability。

脏读：事务 A 执行过程中，对某数据进行了修改，能被事务 B 及时获取到，事务 B 出现脏读；

不可重复读：事务 A 第一次获取某值与第二次某值之间被事务 B 修改了这个值，因此事务 A 不可以重复读取这个值；

幻读

事务 A 检查不存在主键为 1 的记录后意图插入，事务 B 抢先一步插入了主键为 1 的数据，导致事务 A 主键冲突失败；或者取款时，明明看到卡里有余额，却取款失败。

幻影行

幻影行发生在同一个事务在不同时间进行查询时，产生了不同的结果集。例如，如果 SELECT 执行了两次，但是第二次返回了第一次没有出现过的一行记录，这一行被称之为“幻影行”。

假设在 child 表的 id 列上有索引，您希望读取并锁定所有 id > 100 的所有行，稍后打算更新这些行：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

查询扫描索引时，从 id > 100 的第一个记录开始。假定，表中包含 id = 90 和 102 的记录。如果在扫描时，仅仅在索引上设置记录锁，并没有锁定间隙，即这里的 (90, 102)，其他会话就可以插入 id = 101 的新行。如果你在同一个事务中执行相同的 SELECT 语句，你就会在查询返回的结果集中看到 id = 101 的“幻影行”。

为了防止幻影行，InnoDB 使用临键锁，这是一种将索引行和间隙一同锁定的锁。当 InnoDB 搜索或扫描一张表的索引时，它会在遇到的索引记录上设置 S 锁或者 X 锁。因此，行级锁实际是索引记录锁。此外，索引记录上的临键锁也会影响到索引前面的“间隙”。也就是说，临键锁是索引记录锁，以及索引记录前面的间隙锁。如果一个会话持有记录 R 的索引 S 锁或者 X 锁，其他会话无法在按照索引顺序在记录 R 之前的间隙插入一条新的索引记录。  

读未提交几乎没有进行任何隔离，数据随意共享。 读已提交进行了一定的隔离，但是会出现如下图的情况，隔离的并不很好，因此不可重复读：

可重复读，通过 MVCC 机制实现。 MySQL 默认的隔离级别是 repeatable-read，而且 MySQL 的可重复读也一定程度保证了不会出现幻读的导致的错误，但是幻读依然存在。

avatar
文章
阅读
粉丝