MySQL中的 」SELECT FOR UPDATE「 一次實踐
- 2020 年 12 月 18 日
- 筆記
- MySQL, select for update
背景
最近工作中遇到一個問題，兩個不同的執行緒會對資料庫里的一條數據做修改，如果不加鎖的話，會得到錯誤的結果。
就用了MySQL中for update 這種方式來實現
本文主要測試主鍵、唯一索引和普通索引使用for update 會鎖哪些數據
使用兩個console來模擬兩個事務運行的情況
表結構
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50730
Source Host : localhost:3306
Source Schema : test
Target Server Type : MySQL
Target Server Version : 50730
File Encoding : 65001
Date: 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;
主鍵的影響
- 選一行數據
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE id = 1 FOR UPDATE;
console2
SELECT * FROM select_for_update_test WHERE id = 1 FOR UPDATE; 會鎖
SELECT * FROM select_for_update_test WHERE id = 2 FOR UPDATE; 不會鎖
- 選取多行記錄
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE id >= 2 AND id <= 5 FOR UPDATE;
console2
SELECT * 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; 會鎖
唯一索引
- 選一行數據
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE `name` = 'a' FOR UPDATE;
console2
SELECT * FROM select_for_update_test WHERE `name` = 'a' FOR UPDATE; 會鎖
SELECT * FROM select_for_update_test WHERE `name` = 'b' FOR UPDATE; 不會鎖
- 選取多行記錄
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE `name` >= 'b' AND `name` <= 'e' FOR UPDATE;
console2
SELECT * 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; 會鎖
- 選取多行記錄
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE `name` >= 'c' AND `name` <= 'e' FOR UPDATE;
console2
SELECT * 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; 會鎖
普通索引
- 選一行數據
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE age = 1 FOR UPDATE;
console2
SELECT * FROM select_for_update_test WHERE age = 1 FOR UPDATE; 會鎖
SELECT * FROM select_for_update_test WHERE age = 2 FOR UPDATE; 不會鎖
- 選取多行記錄
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE age >= 2 AND age <= 5 FOR UPDATE;
console2
SELECT * 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; 不會鎖
- 選取多行記錄
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE age >= 3 AND age <= 5 FOR UPDATE;
console2
SELECT * 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; 不會鎖
