MySQL锁

场景导入

想象一下，双十一零点，1000个用户同时抢购最后10件商品。如果没有锁机制，会发生什么？

• 用户A查询库存：10件
• 用户B查询库存：10件
• 用户A扣减库存：UPDATE SET stock=9
• 用户B扣减库存：UPDATE SET stock=9（实际应该是8）

结果：库存超卖，商家损失！这就是锁机制要解决的核心问题——并发安全。

MySQL锁机制

先介绍锁的分类体系：

按粒度划分：

1. 全局锁（FTWRL）：整库只读，用于备份

2. 表级锁：

   1. 表锁（LOCK TABLES）：手动锁表
   2. 元数据锁（MDL）：DDL操作自动加
   3. 自增锁（AUTO-INC）：自增列专用

3. 行级锁（InnoDB核心）

   1. 记录锁（Record Lock）：锁单条记录
   2. 间隙锁（Gap Lock）：锁索引间隙
   3. 临键锁（Next-Key Lock）：记录锁+间隙锁

按模式划分：

1. 共享锁(S锁)：读锁，多个事务可同时持有
2. 排他锁（X锁）：写锁，独占资源

行级锁

记录锁

类比：电影院座位票——你买了10排5座的票，这个座位就归你独享。

CREATE DATABASE IF NOT EXISTS `learn_mysql_lab`;
USE `learn_mysql_lab`;

-- 创建测试表
CREATE TABLE `user_account` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `username` VARCHAR(50) UNIQUE,
  `balance` DECIMAL(10,2) DEFAULT 0.00,
  `version` INT DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO `user_account` VALUES 
(1, '张三', 1000.00, 1),
(2, '李四', 2000.00, 1),
(3, '王五', 3000.00, 1);

-- 记录锁示例：精准锁定id=2的记录
BEGIN;
SELECT * FROM `user_account` WHERE `id` = 2 FOR UPDATE;
-- 此时其他事务无法修改或删除id=2的记录
-- 但可以操作id=1或id=3的记录

间隙锁

类比：地铁站排队栏杆——栏杆划定了排队区域，防止有人插队。

间隙锁和记录锁不同，它锁住的是索引记录之间的间隙，锁住时不允许插入，只在可重复读隔离级别发生

-- 假设表中数据：id=1, 5, 10, 15
-- 事务A：查询id在5-10之间的数据（实际不存在id=7）
BEGIN;
SELECT * FROM `user_account` WHERE `id` BETWEEN 5 AND 10 FOR UPDATE;

-- 此时锁定的间隙：(5, 10)
-- 事务B尝试插入id=7会被阻塞
-- INSERT INTO `user_account` (id, username) VALUES (7, '赵六'); -- 阻塞！

-- 但可以修改已存在的记录
UPDATE `user_account` SET `balance` = 1500 WHERE `id` = 5; -- 允许

临建锁

类比：小区门禁系统——既锁定了具体房间（记录锁），又锁定了楼道区域（间隙锁）。

-- 假设表中数据：id=1, 5, 10, 15
-- 事务A：查询id在5-10之间的数据（实际不存在id=7）
BEGIN;
SELECT * FROM `user_account` WHERE `id` BETWEEN 5 AND 10 FOR UPDATE;

-- 此时锁定的间隙：(5, 10)
-- 事务B尝试插入id=7会被阻塞
-- INSERT INTO `user_account` (id, username) VALUES (7, '赵六'); -- 阻塞！

-- 但可以修改已存在的记录
UPDATE `user_account` SET `balance` = 1500 WHERE `id` = 5; -- 允许