您现在的位置是:首页 >学无止境 >Mysql行锁失效情况分析网站首页学无止境

Mysql行锁失效情况分析

oyezitan 2023-06-19 16:00:02
简介Mysql行锁失效情况分析

一、概述

数据库版本:使用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;

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

风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。