MySQL InnoDB中的锁机制深入讲解

Nice MySql

2022-05-22 0 668

写在前面

数据库本质上是一种共享资源，因此在最大程度提供并发访问性能的同时，仍需要确保每个用户能以一致的方式读取和修改数据。锁机制（Locking）就是解决这类问题的最好武器。

首先新建表 test，其中 id 为主键，name 为辅助索引，address 为唯一索引。

CREATE TABLE `test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` int(11) NOT NULL,
 `address` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `idex_unique` (`address`),
 KEY `idx_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

INSERT 方法中的行锁

MySQL InnoDB中的锁机制深入讲解

可见，如果两个事务先后对主键相同的行记录执行 INSERT 操作，因为事务 A 先拿到了行锁，事务 B 只能等待直到事务 A 提交后行锁被释放。同理，如果针对唯一索引字段 address 进行插入操作，也需要获取行锁，图同主键插入过程类似，不再重复。

但是，如果两个事务都针对辅助索引字段 name 进行插入，不需要等待获取锁，因为辅助索引字段即使值相同，在数据库中也是操作不同的记录行，不会冲突。

Update 方法与 Insert 方法结果类似。

SELECT FOR UPDATE 下的表锁与行锁

MySQL InnoDB中的锁机制深入讲解

事务 A SELECT FOR UPDATE 语句会拿到表 test 的 Table Lock，此时事务 B 去执行插入操作会阻塞，直到事务 A 提交释放表锁后，事务 B 才能获取对应的行锁执行插入操作。

但是如果事务 A 的 SELECT FOR UPDATE 语句紧跟 WHERE id = 1 的话，那么这条语句只会获取行锁，不会是表锁，此时不阻塞事务 B 对于其他主键的修改操作

辅助索引下的间隙锁

先看下 test 表下的数据情况：

mysql> select * from test;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 3 | 1 |  3 |
| 6 | 1 |  2 |
| 7 | 2 |  4 |
| 8 | 10 |  5 |
+----+------+---------+
4 rows in set (0.00 sec)

间隙锁可以说是行锁的一种，不同的是它锁住的是一个范围内的记录，作用是避免幻读，即区间数据条目的突然增减。解决办法主要是：