MySQL的UPDATE及SELECT...FOR UPDATE语句关于锁的一些简单验证

已于 2022-08-09 10:18:57 修改
阅读量1.8k 收藏 2
点赞数 1
分类专栏： Mysql 文章标签： mysql 数据库 锁 UPDATE
于 2022-08-04 19:27:45 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_29025955/article/details/126164245
版权

MySQL的UPDATE及SELECT...FOR UPDATE语句关于锁的一些简单验证

测试数据库 （可忽视）

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 80015
 Source Host           : localhost:3306
 Source Schema         : hrm_db

 Target Server Type    : MySQL
 Target Server Version : 80015
 File Encoding         : 65001

 Date: 04/08/2022 17:57:51
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept_inf
-- ----------------------------
DROP TABLE IF EXISTS `dept_inf`;
CREATE TABLE `dept_inf`  (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `REMARK` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `NUM` int(11) NULL DEFAULT 0,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept_inf
-- ----------------------------
INSERT INTO `dept_inf` VALUES (1, '技术部', '技术部', 0);
INSERT INTO `dept_inf` VALUES (2, '运营部', '运营部', 0);
INSERT INTO `dept_inf` VALUES (3, '财务部', '财务部', 0);
INSERT INTO `dept_inf` VALUES (5, '总公办', '总公办', 0);
INSERT INTO `dept_inf` VALUES (6, '市场部', '市场部', 0);
INSERT INTO `dept_inf` VALUES (7, '教学部', '教学部', 0);
INSERT INTO `dept_inf` VALUES (10, '测试001', '这是测试001', 0);
INSERT INTO `dept_inf` VALUES (11, 'UNDOING。。。', '这是测试001', 42);
INSERT INTO `dept_inf` VALUES (13, '测试3', '这是测试001', 16);
INSERT INTO `dept_inf` VALUES (14, '测试005', '这是测试001', 0);
INSERT INTO `dept_inf` VALUES (16, '修修改改。。。', 'CS', 33);

-- ----------------------------
-- Table structure for document_inf
-- ----------------------------
DROP TABLE IF EXISTS `document_inf`;
CREATE TABLE `document_inf`  (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TITLE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `filename` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `REMARK` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `CREATE_DATE` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  `USER_ID` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `FK_DOCUMENT_USER`(`USER_ID`) USING BTREE,
  CONSTRAINT `FK_DOCUMENT_USER` FOREIGN KEY (`USER_ID`) REFERENCES `user_inf` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for employee_inf
-- ----------------------------
DROP TABLE IF EXISTS `employee_inf`;
CREATE TABLE `employee_inf`  (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DEPT_ID` int(11) NOT NULL,
  `JOB_ID` int(11) NOT NULL,
  `NAME` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `CARD_ID` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `ADDRESS` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `POST_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `TEL` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `PHONE` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `QQ_NUM` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `EMAIL` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `SEX` int(11) NOT NULL DEFAULT 1,
  `PARTY` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BIRTHDAY` datetime(0) NULL DEFAULT NULL,
  `RACE` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `EDUCATION` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `SPECIALITY` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `HOBBY` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REMARK` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `CREATE_DATE` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `FK_EMP_DEPT`(`DEPT_ID`) USING BTREE,
  INDEX `FK_EMP_JOB`(`JOB_ID`) USING BTREE,
  CONSTRAINT `FK_EMP_DEPT` FOREIGN KEY (`DEPT_ID`) REFERENCES `dept_inf` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_EMP_JOB` FOREIGN KEY (`JOB_ID`) REFERENCES `job_inf` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee_inf
-- ----------------------------
INSERT INTO `employee_inf` VALUES (1, 1, 8, '爱丽丝', '4328011988', '广州天河', '510000', '020-77777777', '13902001111', '36750066', '251425887@qq.com', 0, '党员', '1980-01-01 00:00:00', '满', '本科', '美声', '唱歌', '四大天王', '2016-03-14 11:35:18');
INSERT INTO `employee_inf` VALUES (2, 2, 1, '杰克', '22623', '43234', '42427424', '42242', '4247242', '42424', '251425887@qq.com', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2016-03-14 11:35:18');
INSERT INTO `employee_inf` VALUES (3, 1, 2, 'bb', '432801197711251038', '广州', '510000', '020-99999999', '13907351532', '36750064', '36750064@qq.com', 1, '党员', '1977-11-25 00:00:00', '汉', '本科', '计算机', '爬山', '无', '2016-07-14 09:54:52');

-- ----------------------------
-- Table structure for job_inf
-- ----------------------------
DROP TABLE IF EXISTS `job_inf`;
CREATE TABLE `job_inf`  (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `REMARK` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of job_inf
-- ----------------------------
INSERT INTO `job_inf` VALUES (1, '职员', '职员');
INSERT INTO `job_inf` VALUES (2, 'Java开发工程师', 'Java开发工程师');
INSERT INTO `job_inf` VALUES (3, 'Java中级开发工程师', 'Java中级开发工程师');
INSERT INTO `job_inf` VALUES (4, 'Java高级开发工程师', 'Java高级开发工程师');
INSERT INTO `job_inf` VALUES (5, '系统管理员', '系统管理员');
INSERT INTO `job_inf` VALUES (6, '架构师', '架构师');
INSERT INTO `job_inf` VALUES (7, '主管', '主管');
INSERT INTO `job_inf` VALUES (8, '经理', '经理');
INSERT INTO `job_inf` VALUES (9, '总经理', '总经理');

-- ----------------------------
-- Table structure for notice_inf
-- ----------------------------
DROP TABLE IF EXISTS `notice_inf`;
CREATE TABLE `notice_inf`  (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TITLE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `CONTENT` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `CREATE_DATE` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  `USER_ID` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `FK_NOTICE_USER`(`USER_ID`) USING BTREE,
  CONSTRAINT `FK_NOTICE_USER` FOREIGN KEY (`USER_ID`) REFERENCES `user_inf` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for user_inf
-- ----------------------------
DROP TABLE IF EXISTS `user_inf`;
CREATE TABLE `user_inf`  (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `loginname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `PASSWORD` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `USERSTATUS` int(11) NOT NULL DEFAULT 1,
  `createdate` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_inf
-- ----------------------------
INSERT INTO `user_inf` VALUES (1, 'admin', '123456', 2, '2016-03-12 09:34:28', '超级管理员');

-- ----------------------------
-- Procedure structure for SAD_LOCK_TEST
-- ----------------------------
DROP PROCEDURE IF EXISTS `SAD_LOCK_TEST`;
delimiter ;;
CREATE PROCEDURE `SAD_LOCK_TEST`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;
SELECT * FROM dept_inf WHERE ID = 11;
WHILE @sum < 1000000 DO
SET @sum = @sum +1;
END WHILE;
SELECT @sum;
COMMIT;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for SELECT...FOR UPDATE_001
-- ----------------------------
DROP PROCEDURE IF EXISTS `SELECT...FOR UPDATE_001`;
delimiter ;;
CREATE PROCEDURE `SELECT...FOR UPDATE_001`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;
-- 111
SELECT * FROM dept_inf WHERE ID=11 FOR UPDATE;

WHILE @sum < 1000000 DO
SET @sum = @sum +1;
END WHILE;
-- 等待222执行完毕
UPDATE dept_inf SET NUM=NUM+1 WHERE ID=13;

SELECT @sum;
COMMIT;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for SELECT...FOR UPDATE_002
-- ----------------------------
DROP PROCEDURE IF EXISTS `SELECT...FOR UPDATE_002`;
delimiter ;;
CREATE PROCEDURE `SELECT...FOR UPDATE_002`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;

-- 333
SELECT * FROM dept_inf WHERE ID=13 FOR UPDATE;

 WHILE @sum < 1000000 DO
 SET @sum = @sum +1;
 END WHILE;
 -- 等待111执行完毕
 UPDATE dept_inf SET NUM=NUM+1 WHERE ID=11;
SELECT @sum;
COMMIT;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for SELECT...FOR UPDATE_003
-- ----------------------------
DROP PROCEDURE IF EXISTS `SELECT...FOR UPDATE_003`;
delimiter ;;
CREATE PROCEDURE `SELECT...FOR UPDATE_003`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;
-- 222
SELECT * FROM dept_inf WHERE ID=16 FOR UPDATE;

WHILE @sum < 1000000 DO
SET @sum = @sum +1;
END WHILE;
-- 等待333执行完毕
UPDATE dept_inf SET NUM=NUM+1 WHERE ID=13;

SELECT @sum;
COMMIT;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for UPDATE_LOCK_001
-- ----------------------------
DROP PROCEDURE IF EXISTS `UPDATE_LOCK_001`;
delimiter ;;
CREATE PROCEDURE `UPDATE_LOCK_001`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;

-- 等待222执行完毕
UPDATE dept_inf SET NUM=NUM+1 WHERE ID=16;

WHILE @sum < 1000000 DO
SET @sum = @sum +1;
END WHILE;

UPDATE dept_inf SET NUM=NUM+1 WHERE ID=11;

SELECT @sum;
COMMIT;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for UPDATE_LOCK_002
-- ----------------------------
DROP PROCEDURE IF EXISTS `UPDATE_LOCK_002`;
delimiter ;;
CREATE PROCEDURE `UPDATE_LOCK_002`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;

-- 等待222执行完毕
UPDATE dept_inf SET NUM=NUM+2 WHERE ID=11;

WHILE @sum < 1000000 DO
SET @sum = @sum +1;
END WHILE;

UPDATE dept_inf SET NUM=NUM+2 WHERE ID=16;

SELECT @sum;
COMMIT;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for UPDATE_LOCK_003
-- ----------------------------
DROP PROCEDURE IF EXISTS `UPDATE_LOCK_003`;
delimiter ;;
CREATE PROCEDURE `UPDATE_LOCK_003`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;

-- 等待222执行完毕
UPDATE dept_inf SET NUM=NUM+1 WHERE ID=13;

WHILE @sum < 1000000 DO
SET @sum = @sum +1;
END WHILE;

SELECT @sum;
COMMIT;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;

说明

当前线程/会话使用UPDATE或SELECT…FOR UPADATE锁住的行（多行），其他线程/会话只能对改行（多行）进行读操作，不能进行写操作（阻塞，等待锁释放后才执行写操作：修改、删除等）。
另，尝试使用SELECT…FOR UPDATE获取已被其他线程/会话锁住的行，需要等待其他线程/会话释放锁之后，点前线程/会话才能够获取到锁。UPDATE，同理。

UPDATE

UPDATE语句自带锁

示例

创建一存储过程，目的主要是使用事务：

CREATE DEFINER=`root`@`%` PROCEDURE `UPDATE_LOCK_001`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;

UPDATE dept_inf SET NUM=NUM+1 WHERE ID=16;

WHILE @sum < 1000000 DO
SET @sum = @sum +1;
END WHILE;

UPDATE dept_inf SET NUM=NUM+1 WHERE ID=11;

SELECT @sum;
COMMIT;
END

新建一查询，同样对上面ID=16的数据进行更新：

UPDATE dept_inf SET NUM=NUM+3 WHERE ID=16;

先执行存储过程，让其锁住ID=16的记录，再执行对ID=16记录进行更新。预期是查询只有在存储过程的事务结束，执行完后，释放锁之后，才会执行。验证如下图：
在这里插入图片描述
通过观察执行完成的先后顺序、用时及最后表的更新结果，可知符合预期。

死锁

既然UPDATE自带锁，那多个UPDATE自然就会产生死锁。

新建两个带UPDATE语句的事务，让其在同时运行时产生死锁：

第一个，（与上面那一个是一致的）

CREATE DEFINER=`root`@`%` PROCEDURE `UPDATE_LOCK_001`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;

UPDATE dept_inf SET NUM=NUM+1 WHERE ID=16;

WHILE @sum < 1000000 DO
SET @sum = @sum +1;
END WHILE;

UPDATE dept_inf SET NUM=NUM+1 WHERE ID=11;

SELECT @sum;
COMMIT;
END

第二个，

CREATE DEFINER=`root`@`%` PROCEDURE `UPDATE_LOCK_002`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;

-- 等待222执行完毕
UPDATE dept_inf SET NUM=NUM+2 WHERE ID=11;

WHILE @sum < 1000000 DO
SET @sum = @sum +1;
END WHILE;

UPDATE dept_inf SET NUM=NUM+2 WHERE ID=16;

SELECT @sum;
COMMIT;
END

验证，同时执行两个存储过程：
首先，看下dept_inf表ID=11和ID=16的记录中NUM的值，
在这里插入图片描述
再观察两个存储过程执行的结果，
在这里插入图片描述
由上图可知，
1、出现了死锁
2、一个成功一个失败

最后看下数据库中表的数据，
在这里插入图片描述
分别加了 1， 这个是有成功的那一个存储过程更新的。

现象解释：
MySQL 8 官方文档
在这里插入图片描述
红线翻译，InnoDB默认会自动侦测死锁，侦测到死锁后会回滚其中一个受影响的事务。

可以使用全局变量innodb_deadlock_detect来禁用死锁的侦测。

关闭死锁侦测：
在这里插入图片描述
再次验证，
在这里插入图片描述

SELECT … FOR UPDATE (把。。。选择出来更新/上锁)

SELECT…FOR UPDATE语句语UPDATE一样，同样会给记录上锁

第一个，

CREATE DEFINER=`root`@`%` PROCEDURE `SELECT...FOR UPDATE_001`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;

SELECT * FROM dept_inf WHERE ID=11 FOR UPDATE;

WHILE @sum < 1000000 DO
SET @sum = @sum +1;
END WHILE;

UPDATE dept_inf SET NUM=NUM+1 WHERE ID=13;

SELECT @sum;
COMMIT;
END

第二个，

CREATE DEFINER=`root`@`%` PROCEDURE `SELECT...FOR UPDATE_002`()
BEGIN
SET @sum:=0;
SET @sum:=0;
START TRANSACTION;

SELECT * FROM dept_inf WHERE ID=13 FOR UPDATE;

 WHILE @sum < 1000000 DO
 SET @sum = @sum +1;
 END WHILE;

 UPDATE dept_inf SET NUM=NUM+1 WHERE ID=11;
SELECT @sum;
COMMIT;
END

验证，
首先，查看当前数据库的 死锁自动侦测 状态
在这里插入图片描述
然后，运行两个存储过程：
在这里插入图片描述
由图可知，其结果与UPDATE是一致。

把innodb_deadlock_detect开启，设置为 ON ，

验证，
与UPDATE 一致， 省略。。。

爱看老照片
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值