MySQL中的 ”SELECT FOR UPDATE“ 一次实践

背景

最近工作中遇到一个问题，两个不同的线程会对数据库里的一条数据做修改，如果不加锁的话，会得到错误的结果。

就用了MySQL中for update 这种方式来实现

本文主要测试主键、唯一索引和普通索引使用for update 会锁哪些数据

使用两个console来模拟两个事务运行的情况

表结构

/*Navicat Premium Data TransferSource Server         : localhostSource Server Type    : MySQLSource Server Version : 50730Source Host           : localhost:3306Source Schema         : testTarget Server Type    : MySQLTarget Server Version : 50730File Encoding         : 65001Date: 18/12/2020 20:28:58*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for select_for_update_test-- ----------------------------DROP TABLE IF EXISTS `select_for_update_test`;CREATE TABLE `select_for_update_test` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,`age` int(11) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `name` (`name`),KEY `age` (`age`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;-- ------------------------------ Records of select_for_update_test-- ----------------------------BEGIN;INSERT INTO `select_for_update_test` VALUES (1, \'a\', 1);INSERT INTO `select_for_update_test` VALUES (2, \'b\', 2);INSERT INTO `select_for_update_test` VALUES (3, \'c\', 3);INSERT INTO `select_for_update_test` VALUES (4, \'d\', 4);INSERT INTO `select_for_update_test` VALUES (5, \'e\', 5);INSERT INTO `select_for_update_test` VALUES (6, \'f\', 6);INSERT INTO `select_for_update_test` VALUES (7, \'g\', 7);INSERT INTO `select_for_update_test` VALUES (8, \'h\', 8);COMMIT;SET FOREIGN_KEY_CHECKS = 1;

主键的影响

• 选一行数据

console1START TRANSACTION;SELECT * FROM select_for_update_test WHERE id = 1 FOR UPDATE;

console2SELECT * FROM select_for_update_test WHERE id = 1 FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE id = 2 FOR UPDATE; 不会锁

• 选取多行记录

console1START TRANSACTION;SELECT * FROM select_for_update_test WHERE id >= 2 AND id <= 5 FOR UPDATE;

console2SELECT * FROM select_for_update_test WHERE id = 1 FOR UPDATE; 不会锁SELECT * FROM select_for_update_test WHERE id = 3 FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE id = 6 FOR UPDATE; 会锁

唯一索引的影响

• 选一行数据

console1START TRANSACTION;SELECT * FROM select_for_update_test WHERE `name` = \'a\' FOR UPDATE;

console2SELECT * FROM select_for_update_test WHERE `name` = \'a\' FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE `name` = \'b\' FOR UPDATE; 不会锁

• 选取多行记录1

console1START TRANSACTION;SELECT * FROM select_for_update_test WHERE `name` >= \'b\' AND `name` <= \'e\' FOR UPDATE;

console2SELECT * FROM select_for_update_test WHERE `name` = \'a\' FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE `name` = \'c\' FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE `name` = \'f\' FOR UPDATE; 会锁

• 选取多行记录2

console1START TRANSACTION;SELECT * FROM select_for_update_test WHERE `name` >= \'c\' AND `name` <= \'e\' FOR UPDATE;

console2SELECT * FROM select_for_update_test WHERE `name` = \'b\' FOR UPDATE; 不会锁SELECT * FROM select_for_update_test WHERE `name` = \'c\' FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE `name` = \'f\' FOR UPDATE; 会锁

普通索引的影响

• 选一行数据

console1START TRANSACTION;SELECT * FROM select_for_update_test WHERE age = 1 FOR UPDATE;

console2SELECT * FROM select_for_update_test WHERE age = 1 FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE age = 2 FOR UPDATE; 不会锁

• 选取多行记录1

console1START TRANSACTION;SELECT * FROM select_for_update_test WHERE age >= 2 AND age <= 5 FOR UPDATE;

console2SELECT * FROM select_for_update_test WHERE age = 1 FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE age = 3 FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE age = 6 FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE age = 8 FOR UPDATE; 不会锁

• 选取多行记录2

console1START TRANSACTION;SELECT * FROM select_for_update_test WHERE age >= 3 AND age <= 5 FOR UPDATE;

console2SELECT * FROM select_for_update_test WHERE age = 2 FOR UPDATE; 不会锁SELECT * FROM select_for_update_test WHERE age = 3 FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE age = 6 FOR UPDATE; 会锁SELECT * FROM select_for_update_test WHERE age = 8 FOR UPDATE; 不会锁