Mysql行锁失效情况分析

原创已于 2023-04-27 16:45:50 修改 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #服务器

于 2023-04-27 15:21:52 首次发布

本文通过Mysql8.0.32的测试展示了行锁在特定条件下如何升级为表锁。在更新使用主键的记录时，不同窗口的并发更新可以并行执行，而使用非索引字段更新会导致行锁失效，升级为表锁，造成其他操作阻塞，直至提交事务释放锁。

一、概述

数据库版本：使用Mysql8.0.32作为测试版本，InnoDB引擎。
本文阐述Mysql行锁失效升级为表的场景。

二、测试

使用远程终端打开两个数据库服务器（Linux）窗口，使用如下命令登录：

mysql -u root -p

回车，输入数据库密码.

测试数据库为: test

show databases;
use test;

关闭自动提交

set autocommit=0;

测试数据表为：

CREATE TABLE `t_run_work_order`  (
  `id` bigint(0) UNSIGNED NOT NULL AUTO_INCREMENT,
  `work_no` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '工单编号',
  `data_guid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '报警ID',
  `dept_id` bigint(0) NOT NULL COMMENT '组织结构ID',
  `flow_id` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '流程ID',
  `node_id` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '当前流程',
  `work_title` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '工单标题',
  `work_state` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '状态',
  `is_exception` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '是否异常',
  `is_invalid` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '是否有效',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `version` int(0) NOT NULL DEFAULT 0 COMMENT '版本号',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `u_data_guid`(`data_guid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11903 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = 'XX信息表' ROW_FORMAT = Dynamic;

其中id是主键。

插入两条id分别为7742、7743的记录。

对比测试：
在左侧窗口执行：

mysql> update t_run_work_order set work_title='OTHE-L'' where id='7742';
Query OK, 1 row affected (16.49 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在右侧窗口执行：

update t_run_work_order set work_title='OTHE-R' where id='7742';

我们发现，右侧命令会阻塞。
阻塞原因：左侧命令执行后，尚未提交，锁住了id为7742的记录；
接下来，在左侧窗口执行：

commit;

观察右侧窗口，右侧窗口命令执行成功。

那么，如果我们同时对不同的记录进行操作呢？

在左侧窗口执行：

mysql> update t_run_work_order set work_title='OTHE-L'' where id='7742';
Query OK, 1 row affected (16.49 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在右侧窗口执行：

update t_run_work_order set work_title='OTHE-R' where id='7743';
Query OK, 1 row affected (12.49 sec)
Rows matched: 1  Changed: 1  Warnings: 0

我们发现，两边都执行成功，没有阻塞。
这说明：分表对不同主键的记录进行更新操作，互不影响。因为他们只是锁住了各自的记录，也就是行锁。

接下来，我们测试一下使用非索引字段作为条件进行更新操作。
在左侧窗口执行：

mysql> update t_run_work_order set work_title='OTHE-L'' where data_guid='56c24ba9-49c7-471c-b77f-a3f38043d429'';
Query OK, 1 row affected (16.49 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在右侧窗口执行：

update t_run_work_order set work_title='OTHE-R' where data_guid='56c24ba9-49c7-471c-b77f-a3f38043d428';

右侧窗口阻塞。

我们在左侧窗口执行

commit；

命令提交。然后观察右侧窗口，右侧窗口立即执行成功。
这说明了，对使用非索引字段作为条件对记录进行操作时，行锁会失效，升级为表锁，此时，其他更新、删除操作都会处于阻塞状态，直到锁释放。