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

导读:本篇文章讲解 MySQL中的 ”SELECT FOR UPDATE“ 一次实践,希望对大家有帮助,欢迎收藏,转发!站点地址:www.bmabk.com

背景

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

就用了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; 不会锁
  • 选取多行记录1
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; 会锁
  • 选取多行记录2
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; 不会锁
  • 选取多行记录1
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; 不会锁
  • 选取多行记录2
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; 不会锁

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

文章由半码博客整理,本文链接:https://www.bmabk.com/index.php/post/5961.html

(0)

相关推荐

半码博客——专业性很强的中文编程技术网站,欢迎收藏到浏览器,订阅我们!