最近工作中遇到一个问题，两个不同的线程会对数据库里的一条数据做修改，如果不加锁的话，会得到错误的结果。 就用了MySQL中for update 这种方式来实现 本文主要测试主键、唯一索引和普通索引使用for update 会锁哪些数据 使用两个console来模拟两个事务运行的情况
背景
最近工作中遇到一个问题，两个不同的线程会对数据库里的一条数据做修改，如果不加锁的话，会得到错误的结果。
就用了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
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; 不会锁
- 选取多行记录1
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE name >= 'b' AND name
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; 会锁
- 选取多行记录2
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE name >= 'c' AND name
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; 不会锁
- 选取多行记录1
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE age >= 2 AND age
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; 不会锁
- 选取多行记录2
console1
START TRANSACTION;
SELECT * FROM select_for_update_test WHERE age >= 3 AND age
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; 不会锁
Original: https://www.cnblogs.com/eaglelihh/p/14156817.html
Author: eaglelihh
Title: MySQL中的 ”SELECT FOR UPDATE“ 一次实践
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/587228/
转载文章受原作者版权保护。转载请注明原作者出处！