前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql行级锁

Mysql行级锁

作者头像
一个架构师
发布2022-06-27 15:20:28
3.3K0
发布2022-06-27 15:20:28
举报
文章被收录于专栏:从码农的全世界路过

锁是计算机协调多个进程或纯线程并发访问某一资源的机制. 在mysql中更是用处多多, 今天就一起看下mysql中的行级锁. 它主要包括行锁, 间隙锁, 临键锁三种. 首先我们先了解几个基础概念.

1. 间隙锁(gap lock)

间隙锁是innodb在可重复读提交(RR)事务级别下为了解决幻读问题时引入的锁机制,它锁定一段范围内的索引记录,而不仅仅是这个区间中的每一条数据.

2. 幻读

幻读是因为其他事务进行插入或者更新操作时, 当前事务进行多次范围查询的结果不一致.

3. 记录锁(record lock)

记录锁,也叫行锁,是为某行记录加锁, 它是依赖索引实现的, 一旦某个加锁操作没有使用到索引,那么该锁就会退化为表锁.

一般唯一索引列或主键索引在进行精准匹配(=)时, 会使用记录锁; 其他( >, <, like)等条件会退化成临键锁.

4. 临键锁(next-key lock)

临键锁是普通索引上的记录锁和间隙锁的组合, 与唯一索引无关.

5. 示例

有了上面的概念作为基础, 我们一起看下间隙锁都锁住了哪些数据.

5.1 基础数据

先创建一个带有普通索引的表tab, 索引字段为b.

代码语言:javascript
复制
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;

添加数据

代码语言:javascript
复制
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并执行查询语句.

代码语言:javascript
复制
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两种锁锁住.

代码语言:javascript
复制
insert into tab value(19,19,19);

锁信息

代码语言:javascript
复制
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)是没有被锁住的.

代码语言:javascript
复制
update tab set c =c+1 where b = 10;

5.2.3 在当前节点间与后一节点间插入数据

事务B执行插入sql 被block了.证明区间(20,30)是被锁住的.

查看锁信息, 是数据(30,30)被X,GAP两种锁锁住, 同时影响到了数据区间(20,30).

代码语言:javascript
复制
insert into tab value(21,21,21);

锁信息

代码语言:javascript
复制
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)节点.

代码语言:javascript
复制
update tab set c =c+1 where b = 30;

5.3 普通索引范围查询

索引范围查询的临界值的取舍因版本的不同会有所不同. 当前使用版本5.7.36.

开启事务并执行查询sql.

代码语言:javascript
复制
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更新前一节点数据并执行成功, 说明对前一节点不进行加锁操作.

代码语言:javascript
复制
update tab set c =c+1 where b = 10;

5.3.2 在前一节点与当前节点之间插入数据

在前一节点与当前节点之间插入数据, sql操作被阻塞.锁类型为数据(20,20)上的X,GAP

代码语言:javascript
复制
insert into tab value(19,19,19);

锁信息

代码语言:javascript
复制
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

代码语言:javascript
复制
update tab set c =c+1 where b = 20;

锁信息

代码语言:javascript
复制
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

代码语言:javascript
复制
insert into tab value(29,29,29);

锁信息

代码语言:javascript
复制
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

代码语言:javascript
复制
update tab set c =c+1 where b = 30;

锁信息

代码语言:javascript
复制
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)后面未加锁.

代码语言:javascript
复制
insert into tab value(31,31,31);
Query OK, 1 row affected (0.01 sec)

6. INNODB_LOCKS表

mysql提供的查看锁信息的元数据表.

代码语言:javascript
复制
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. 哪个方向还有命中的等值判断,再‍‍‍‍向同方向拓展外开里闭的区间.

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2022-05-05,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 从码农的全世界路过 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档