Mysql行级锁
锁是计算机协调多个进程或纯线程并发访问某一资源的机制. 在mysql中更是用处多多, 今天就一起看下mysql中的行级锁. 它主要包括行锁, 间隙锁, 临键锁三种. 首先我们先了解几个基础概念.
1. 间隙锁(gap lock)
间隙锁是innodb在可重复读提交(RR)事务级别下为了解决幻读问题时引入的锁机制,它锁定一段范围内的索引记录,而不仅仅是这个区间中的每一条数据.
2. 幻读
幻读是因为其他事务进行插入或者更新操作时, 当前事务进行多次范围查询的结果不一致.
3. 记录锁(record lock)
记录锁,也叫行锁,是为某行记录加锁, 它是依赖索引实现的, 一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁.
一般唯一索引列或主键索引在进行精准匹配(=)时, 会使用记录锁; 其他( >, <, like)等条件会退化成临键锁.
4. 临键锁(next-key lock)
临键锁是普通索引上的记录锁和间隙锁的组合, 与唯一索引无关.
5. 示例
有了上面的概念作为基础, 我们一起看下间隙锁都锁住了哪些数据.
5.1 基础数据
先创建一个带有普通索引的表tab, 索引字段为b.
CREATE TABLE `tab` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT 0,
`c` int(11) DEFAULT 0,
PRIMARY KEY (`id`),
KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;添加数据
INSERT INTO tab (id, b, c)VALUES (10, 10, 10),(20, 20, 20),(30, 30, 30),(40, 40, 40),(50, 50, 50);5.2 普通索引, 精准匹配(=)查询
我们在mysql5.7.36版本中实际操作下,看看结果如何.
开启事务A并执行查询语句.
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab where b = 20 for update;
+----+------+------+
| id | b | c |
+----+------+------+
| 20 | 20 | 20 |
+----+------+------+当前锁的范围是(10,30), 且不包括前后两节点数据.
下面我们看下验证过程
5.2.1 前一节点数据与当前节点间插入数据
事务B执行插入sql 被block了.证明区间(10,20)是被锁住的.
查看锁信息, 数据(20,20)被X,GAP两种锁锁住.
insert into tab value(19,19,19);锁信息
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 20789:222:4:4 | 20789 | X,GAP | RECORD | `test`.`tab` | b | 222 | 4 | 4 | 20, 20 |
| 20786:222:4:4 | 20786 | X | RECORD | `test`.`tab` | b | 222 | 4 | 4 | 20, 20 |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)5.2.2 更新前一节点数据
事务B执行sql 成功.证明区间(10,10)是没有被锁住的.
update tab set c =c+1 where b = 10;5.2.3 在当前节点间与后一节点间插入数据
事务B执行插入sql 被block了.证明区间(20,30)是被锁住的.
查看锁信息, 是数据(30,30)被X,GAP两种锁锁住, 同时影响到了数据区间(20,30).
insert into tab value(21,21,21);锁信息
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 20792:222:4:5 | 20792 | X,GAP | RECORD | `test`.`tab` | b | 222 | 4 | 5 | 30, 30 |
| 20786:222:4:5 | 20786 | X,GAP | RECORD | `test`.`tab` | b | 222 | 4 | 5 | 30, 30 |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+5.2.4更新下一节点数据
事务B更新下一节点数据操作成功, 说明锁范围并不包括(30,30)节点.
update tab set c =c+1 where b = 30;5.3 普通索引范围查询
索引范围查询的临界值的取舍因版本的不同会有所不同. 当前使用版本5.7.36.
开启事务并执行查询sql.
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab where b>=20 and b < 30 for update;
+----+------+------+
| id | b | c |
+----+------+------+
| 20 | 20 | 20 |
+----+------+------+
1 row in set (0.01 sec)当前锁范围是(10,30], 注意是包含后一节点数据的. 关于是否包含右侧临界数据, 与mysql版本有关, 8.0之后版本是不包含的.
5.3.1 更新前一节点数据
事务B更新前一节点数据并执行成功, 说明对前一节点不进行加锁操作.
update tab set c =c+1 where b = 10;5.3.2 在前一节点与当前节点之间插入数据
在前一节点与当前节点之间插入数据, sql操作被阻塞.锁类型为数据(20,20)上的X,GAP
insert into tab value(19,19,19);锁信息
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 20811:222:4:4 | 20811 | X,GAP | RECORD | `test`.`tab` | b | 222 | 4 | 4 | 20, 20 |
| 20797:222:4:4 | 20797 | X | RECORD | `test`.`tab` | b | 222 | 4 | 4 | 20, 20 |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)5.3.3 更新包含节点数据
在节点(20,20)进行更新操作, 操作被阻塞,锁类型为X
update tab set c =c+1 where b = 20;锁信息
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 20812:222:4:4 | 20812 | X | RECORD | `test`.`tab` | b | 222 | 4 | 4 | 20, 20 |
| 20797:222:4:4 | 20797 | X | RECORD | `test`.`tab` | b | 222 | 4 | 4 | 20, 20 |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)5.3.4 在当前节点与后一节点间插入数据
插入sql操作被阻塞.锁类型为数据(30,30)上的X,GAP
insert into tab value(29,29,29);锁信息
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 20810:222:4:5 | 20810 | X,GAP | RECORD | `test`.`tab` | b | 222 | 4 | 5 | 30, 30 |
| 20797:222:4:5 | 20797 | X | RECORD | `test`.`tab` | b | 222 | 4 | 5 | 30, 30 |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)5.3.5 更新后一节点数据
在节点(30,30)进行更新操作, 操作被阻塞,锁类型为X
update tab set c =c+1 where b = 30;锁信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 20798:222:4:5 | 20798 | X | RECORD | `test`.`tab` | b | 222 | 4 | 5 | 30, 30 |
| 20797:222:4:5 | 20797 | X | RECORD | `test`.`tab` | b | 222 | 4 | 5 | 30, 30 |
+---------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)对节点(30,30)后面的数据进行插入操作, 操作成功, 说明节点(30,30)后面未加锁.
insert into tab value(31,31,31);
Query OK, 1 row affected (0.01 sec)6. INNODB_LOCKS表
mysql提供的查看锁信息的元数据表.
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;具体可参考: https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-locks-table.html
7. 加锁原则
原则 1: 加锁的基本单位是 next-key lock. next-key lock 是前开后闭区间.
原则 2: 只有访问到的对象才会加锁.
优化 1: 索引上的等值查询，
命中唯一索引，退化为行锁.
命中普通索引，左右两边的gap lock + record lock.
优化 2:
索引上的等值查询，未命中，所在的next-key lock，退化为gap lock .
索引在范围查询:
1.等值和范围分开判断.
2.索引在范围查询的时候 都会访问到所在区间不满足条件的第一个值为止.
3.如果使用了倒叙排序，按照倒叙排序后，检索范围的右边多加一个gap. 哪个方向还有命中的等值判断，再向同方向拓展外开里闭的区间.