MySQL InnoDB锁机制

最新推荐文章于 2023-12-05 14:15:27 发布

scaling_heights

最新推荐文章于 2023-12-05 14:15:27 发布

阅读量61

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/jz1993/article/details/113531780

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

在我们的日常工作中，经常会遇到各种死锁的场景，有的死锁分析起来是比较容易的，比如同类型的事务并发引起的死锁；而不同类型事务并发引起的死锁，分析起来就不是那么容易了。系统化的了解数据库的加锁机制，不仅有助于对现有问题的分析，在设计阶段也能更好的把握系统的性能与复杂业务场景的解决方案。

要系统化了了解数据库的加锁机制，我们需要弄明白：

有哪些类型的锁
对什么东西加锁
以什么样的方式加锁

1. 有哪些类型的锁

MySQL InnoDB一共有四种锁：共享锁（读锁，S锁）、排他锁（写锁，X锁）、意向共享锁（IS锁）和意向排他锁（IX锁）。其中共享锁与排他锁属于行级锁，另外两个意向锁属于表级锁。

共享锁（读锁，S锁）：

若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放S锁。

排他锁（写锁，X锁）：

若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他事务不能再对A加作何类型的锁，直到T释放A上的X锁。

意向共享锁（IS锁）：

事务T在对表中数据对象加S锁前，首先需要对该表加IS（或更强的IX）锁。

意向排他锁（IX锁）：

事务T在对表中的数据对象加X锁前，首先需要对该表加IX锁。

比如SELECT ... FROM T1 LOCK IN SHARE MODE语句，首先会对表T1加IS锁，成功加上IS锁后才会对数据加S锁。

同样，SELECT ... FROM T1 FOR UPDATE语句，首先会对表T1加IX锁，成功加上IX锁后才会对数据加X锁。

MySQL InnoDB 锁兼容阵列

	X	IX	S	IS
X	✗	✗	✗	✗
IX	✗	✓	✗	✓
S	✗	✗	✓	✓
IS	✗	✓	✓	✓

MySQL官网上有个死锁的例子，但分析得过于概括，这里我们详细分析一下。

首先，会话S1以SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE查询，该语句首先会对t表加IS锁，接着会对数据（i = 1）加S锁。

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 |

+------+

1 row in set (0.10 sec)

接着，会话S2执行DELETE FROM t WHERE i = 1，该语句尝试对t表加IX锁，由于IX锁与IS锁是兼容的，所以成功对t表加IX锁。接着继续对数据（i = 1）加X锁，但数据已经被会话S1事务加了S锁了，所以会话S2等待。

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

接着，会话S1也执行DELETE FROM t WHERE i = 1，该语句首先对t表加IX锁，虽然会话S2已经对t表加了IX锁，但IX锁与IX锁之间是兼容的，所以成功对t表加上IX锁。接着会话S1会对数据（i = 1）加X锁，此时发现会话S2占有的IX锁与X锁不兼容，所以会话S1也等待。就这样，会话S1等S2释放IX锁，而会话S2等S1释放S锁，从而死锁发生。

mysql> DELETE FROM t WHERE i = 1;

Query OK, 1 row affected (0.00 sec)

上例中会话S1虽然执行成功了，但是下面会话S2发生了死锁。这是因为Mysql检测到死锁后，会自动强制其中一个事务退出。

mysql> DELETE FROM t WHERE i = 1;

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

2. 对什么加锁

每一张InnoDB表都有且仅有一个特殊的索引，聚族索引（Clustered Index），表中的数据是直接存放在聚族索引的叶子节点中，这样，根据聚族索引查询就会比普通索引更快，因为少了一次IO操作。

通常，聚族索引就是表的主键；如果表没有主键，那InnoDB会把第一个非空的唯一索引当作聚族索引；如果表既无主键，又无非空的唯一索引，那么InnoDB会创建一个隐藏的索引作为聚族索引。表中的其它索引，都叫做第二索引（Secondary Index），第二索引中只包含自身索引列和聚族索引列的内容，所以当一个表的主键很长时，其它的索引都会受到影响。

为什么要先讲聚族索引呢？因为这对理解InnoDB加锁机制很重要，InnoDB加锁的对象不是返回的数据记录，而是查询这些数据时所扫描过的索引。当我们执行一个锁读（SELECT … LOCK IN SHARE MODE或者SELECT … FOR UPDATE）时，InnoDB不是对最终的返回结果加锁，而是对查询这些结果时所扫描的索引加锁，如果被扫描的索引不是聚族索引，那被扫描的索引以及它所指向的聚族索引也会被加锁。由此可知，当一个锁读无法使用索引的话，InnoDB就是遍历整个表（遍历整个聚族索引），从而把整张表都锁住。

3. 以什么样的方式加锁

MySQL InnoDB支持三种行锁定方式：

行锁（Record Lock）：

锁直接加在索引上。

间隙锁（Gap Lock）：

锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

Next-Key Lock：

行锁与间隙锁组合起来用就叫做Next-Key Lock。

默认情况下，InnoDB工作在可重复读隔离级别下，并且以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合，这样，当InnoDB扫描索引记录的时候，会首先对选中的索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。如果一个间隙被事务T1加了锁，其它事务是不能在这个间隙插入记录的。

还是用之前的员工表举例：

+----+------+--------+--------+

| id | num | depart | name |

+----+------+--------+--------+

| 10 | 1010 | 5100 | 张三 |

| 20 | 1020 | 5200 | 李四 |

| 30 | 1030 | 5300 | 王五 |

| 40 | 1040 | 5100 | 刘大 |

+----+------+--------+--------+

其中，id表示记录主键；num表示员工工号，唯一索引；depart表示员工所在的部门编号，普通索引；name表示员工姓名。测试前插入一些测试数据。

depart索引可以表示为下面一张二维表：

depart索引顺序二维表示

depart 5100 5100 5200 5300

id 10 40 20 30

由上面的表可知，depart索引将整个间隙拆分为五个区间：

(-∞, [5100|10])，([5100|10], [5100|40]), ([5100|40], [5200|20]), ([5200|20], [5300|30])与([5300|30], +∞)

现在我们在可重复读级别下，根据索引字段depart = 5100加锁查询的话，应该会锁定(-∞, [5100|10])，([5100|10], [5100|40]), ([5100|40], [5200|20])这三个间隙

mysql> set autocommit=0;

Query OK, 0 rows affected (0.01 sec)

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee where depart = 5100 lock in share mode;

+----+------+--------+--------+

| id | num | depart | name |

+----+------+--------+--------+

| 10 | 1010 | 5100 | 张三 |

| 40 | 1040 | 5100 | 刘大 |

+----+------+--------+--------+

2 rows in set (0.00 sec)

另起一个会话测试一下：

mysql> insert into employee values (1, 9999, 5000, 'xx');