MySQL锁机制全解析

MYSQL存储引擎支持的锁

1. InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁。
2. MyISAM采用表级锁(table-level locking)

锁的基本分类

1. 按照锁的使用方式 , Mysql的锁大致分为共享锁和排它锁

a. 共享锁(S)

共享锁，Share lock，又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改

当前事务加锁之后，其他事务也可以加锁

select ... lock in share mode;

select … for shore;  （mysql8.0）

共享锁案例：

数据准备：

-- 查询事务隔离级别
SELECT @@transaction_isolation;

-- 首先检查当前事务是否自动提交
SELECT @@autocommit;

-- 状态说明
-- 1 表示自动提交已启用（每个SQL语句都会在执行后立即提交）。
-- 0 表示自动提交已禁用（需要手动执行 COMMIT; 才能提交事务）。
-- 开启自动提交：
SET autocommit = 1;
-- 关闭自动提交：
SET autocommit = 0;

-- 开启事务
START TRANSACTION;
BEGIN;

-- 释放方式：
COMMIT;      -- 提交事务时释放
ROLLBACK;    -- 回滚事务时释放


-- 创建账户表
CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    balance DECIMAL(12,2) NOT NULL,
    version INT DEFAULT 0,
    INDEX idx_username (username)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO accounts (username, balance) VALUES 
('Alice', 1000.00),
('Bob', 2000.00),
('Charlie', 3000.00);

-- 创建交易记录表
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    from_user VARCHAR(50),
    to_user VARCHAR(50),
    amount DECIMAL(12,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

基础共享锁场景

场景1：多事务共享读取

事务A:

START TRANSACTION;
SELECT * FROM accounts WHERE username = 'Alice' FOR SHARE;
-- 保持事务不提交

事务B:

START TRANSACTION;
-- 可以同时加共享锁
SELECT * FROM accounts WHERE username = 'Alice' FOR SHARE;  -- 成功
COMMIT;

-- 普通查询不受影响
SELECT * FROM accounts WHERE username = 'Alice';  -- 成功

场景2：共享锁与修改冲突

事务A (保持共享锁):

START TRANSACTION;
SELECT * FROM accounts WHERE username = 'Bob' FOR SHARE;
-- 保持锁不释放

事务B (尝试修改):

START TRANSACTION;
-- 尝试更新被锁记录
UPDATE accounts SET balance = balance + 100 WHERE username = 'Bob';  -- 阻塞

-- 在另一个会话查看锁等待
SELECT * FROM performance_schema.data_lock_waits

当事务A释放锁后，事务B更新成功

此时查看锁等待为空

当事务B提交事务后数据发生改变

DQL操作在共享锁下的表现

场景3：不同查询类型的共享锁

事务A:

START TRANSACTION;
-- 加共享锁
SELECT * FROM accounts WHERE username = 'Charlie' FOR SHARE;

事务B 测试各种查询:

-- 1. 普通SELECT
SELECT * FROM accounts WHERE username = 'Charlie';  -- 成功

-- 2. 聚合查询
SELECT SUM(balance) FROM accounts;  -- 成功

-- 3. 子查询
SELECT * FROM accounts WHERE balance > 
    (SELECT balance FROM accounts WHERE username = 'Charlie' FOR SHARE);  -- 成功

-- 4. JOIN查询
SELECT a.* FROM accounts a JOIN accounts b 
ON a.id = b.id WHERE a.username = 'Charlie' FOR SHARE;  -- 成功

DML操作在共享锁下的表现

场景4：INSERT 操作

事务A:

START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR SHARE;

事务B:

-- 插入新记录(不受共享锁影响)
INSERT INTO accounts (username, balance) VALUES ('David', 4000.00);  -- 成功

-- 尝试插入冲突的唯一键
INSERT INTO accounts (username, balance) VALUES ('Bob', 5000.00);  -- 唯一键冲突错误(非锁导致)

场景5：UPDATE 操作

事务A:

START TRANSACTION;
-- 范围共享锁
SELECT * FROM accounts WHERE balance BETWEEN 1000 AND 3000 FOR SHARE;

事务B:

-- 更新被锁定的记录
UPDATE accounts SET balance = 1500 WHERE username = 'Alice';  -- 阻塞

ALTER TABLE accounts ADD INDEX idx_balance (balance);

-- 更新未被锁定的记录
UPDATE accounts SET balance = 5000 WHERE username = 'David';  -- 预测成功(如果David存在)

预测是成功的，但是实际是被阻塞的，为什么呢？

我们查看当前锁情况：

-- 查看当前锁情况
SELECT * FROM performance_schema.data_locks;

-- 查看锁等待情况
SELECT * FROM performance_schema.data_lock_waits;

-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;

锁等待链条分析

1. 事务1（SELECT操作）

• 持有idx_balance索引上4条记录的共享锁(S锁)：

• • balance=1000（ID=1）
  • balance=2000（ID=2）
  • balance=3000（ID=3）
  • balance=4000（ID=4） ← 关键阻塞点

• 持有主键索引上ID 1-3的共享锁
• 持有表级意向共享锁(IS)

2. 事务2（UPDATE操作）

• 已成功获取：

• • 表级意向排他锁(IX)
  • username索引上'David'记录的排他锁(X)
  • 主键ID=4的排他锁(X)

• 正在等待：

• • idx_balance索引上balance=4000记录的排他锁(X) （导致阻塞）

关键冲突点

• 事务1的SELECT查询意外锁定了balance=4000的记录（尽管查询条件是1000-3000）
• 事务2需要修改这条索引记录，但被事务1的S锁阻塞

如果将事务隔离级别调整为读已提交，则不再使用间隙锁，即不在阻塞：

间隙锁(Gap Lock)的消除：

REPEATABLE READ：默认使用间隙锁防止幻读

READ COMMITTED：禁用间隙锁，仅锁定实际存在的记录

-- 设置当前会话为读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

场景6：DELETE 操作

事务A:

START TRANSACTION;
SELECT * FROM accounts WHERE username = 'Alice' FOR SHARE;

事务B:

-- 删除被锁定的记录
DELETE FROM accounts WHERE username = 'Alice';  -- 阻塞

-- 删除未被锁定的记录
DELETE FROM accounts WHERE username = 'David';  -- 成功(如果David存在)

共享锁其他场景

场景7：索引对共享锁的影响

事务A (无索引条件):

START TRANSACTION;
-- 不使用索引的查询会导致表锁
SELECT * FROM accounts WHERE balance = 2000.00 FOR SHARE;

事务B:

-- 所有修改操作都会被阻塞
UPDATE accounts SET username = 'Bobby' WHERE id = 2;  -- 阻塞
INSERT INTO accounts (username, balance) VALUES ('Eve', 5000.00);  -- 阻塞

场景8：共享锁导致的死锁

事务A:

START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- 然后尝试更新
UPDATE accounts SET balance = 1500 WHERE id = 2;  -- 需要等待事务B的锁

事务B:

START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR SHARE;
-- 然后尝试更新
UPDATE accounts SET balance = 2500 WHERE id = 1;  -- 需要等待事务A的锁
-- 此时会发生死锁

1213 - Deadlock found when trying to get lock; try restarting transaction

在尝试获取锁时发现死锁；请重新启动事务。

查看死锁日志:

SHOW ENGINE INNODB STATUS

b. 排它锁(X)

排它锁，Exclusive Lock,又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排它锁，其他事务就不能再获取该行的其他锁，包括共享锁和排它锁，但是获取排它锁，但是获取排它锁的事务是可以对数据就行读取和修改。

当前事务加锁之后，其他事务不可以加锁

select … for update;

排他锁(X锁)的特性：

• 独占性：一个数据行上只能有一个X锁
• 排他性：持有X锁时，其他事务不能获取任何锁(S/X)
• 读写权限：持有X锁的事务可以读取和修改数据

案例1：基本排他锁使用

-- 事务1
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 获取id=1的排他锁
-- 此时可以修改这条记录
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

-- 事务2 (同时运行)
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 会被阻塞，直到事务1提交

案例2：排他锁与共享锁的互斥

-- 事务1
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- 获取共享锁

-- 事务2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 会被阻塞，因为与共享锁冲突

案例3：不同索引上的排他锁

-- 表结构：accounts(id PK, username UNIQUE, balance INDEX)

-- 事务1
START TRANSACTION;
SELECT * FROM accounts WHERE username = 'Alice' FOR UPDATE; -- 在username索引上加X锁

-- 事务2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 可以执行(如果id=1不是Alice的记录)

案例4：排他锁的死锁场景

-- 事务1
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 获取id=1的X锁
-- 假设此时事务2执行了下面的语句

-- 事务2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 获取id=2的X锁
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 等待事务1释放id=1的锁

-- 此时事务1执行：
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 等待事务2释放id=2的锁
-- 形成死锁，InnoDB会自动检测并回滚其中一个事务

案例5：排他锁的范围锁定

-- 事务1 (REPEATABLE READ隔离级别)
START TRANSACTION;
SELECT * FROM accounts WHERE balance BETWEEN 1000 AND 3000 FOR UPDATE;
-- 锁定balance在1000-3000之间的所有记录及间隙

-- 事务2
START TRANSACTION;
INSERT INTO accounts VALUES(NULL, 'Bob', 1500); -- 会被阻塞(因为1500在锁定范围内)

案例6：排他锁与DDL操作

-- 事务1
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- 事务2
ALTER TABLE accounts ADD COLUMN last_login DATETIME; -- 会被阻塞，需要等待元数据锁

2. 按照加锁的范围 , Mysql的锁大致可以分为全局锁和表级锁和行锁。

a. 全局锁 (Global Lock)

全局锁是对整个 MySQL 数据库实例加锁，加锁后数据库处于只读状态，所有数据变更操作(增删改)都会被阻塞。

FLUSH TABLES WITH READ LOCK;  -- 加全局读锁
UNLOCK TABLES;                -- 释放全局锁

特性：

• 阻塞所有写操作：包括 DML(INSERT/UPDATE/DELETE) 和 DDL(ALTER/CREATE 等)
• 允许读操作：SELECT 查询可以正常执行
• 影响范围：整个 MySQL 实例的所有数据库
• 自动释放：当会话断开时自动释放(除非使用 SET GLOBAL read_only=ON)

使用场景：

• 全库逻辑备份：确保备份数据的一致性
• 主从同步初始化：保证主库在导出数据时不发生变化
• 数据库维护：执行需要数据静止状态的操作

注意：

• 长时间持有全局锁会导致业务停滞
• 在 InnoDB 引擎下，推荐使用 mysqldump --single-transaction 进行热备份替代全局锁
• 执行 FTWRL 时会自动提交当前活动事务

测试数据模拟：

-- 创建用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    balance DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 创建订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- 创建商品表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    description TEXT
) ENGINE=InnoDB;




-- 插入用户数据
INSERT INTO users (username, email, balance) VALUES
('john_doe', 'john@example.com', 1000.00),
('jane_smith', 'jane@example.com', 1500.50),
('bob_johnson', 'bob@example.com', 750.25),
('alice_williams', 'alice@example.com', 2000.00);

-- 插入商品数据
INSERT INTO products (name, price, stock, description) VALUES
('Laptop', 999.99, 50, 'High performance laptop'),
('Smartphone', 699.99, 100, 'Latest model smartphone'),
('Headphones', 149.99, 200, 'Noise cancelling headphones'),
('Tablet', 399.99, 75, '10-inch tablet');

-- 插入订单数据
INSERT INTO orders (user_id, amount, status) VALUES
(1, 999.99, 'completed'),
(2, 699.99, 'completed'),
(3, 149.99, 'pending'),
(4, 399.99, 'completed'),
(1, 149.99, 'completed');

ⅰ. 全局锁实际应用场景模拟：

1. 场景：全库逻辑备份

-- 会话1（管理员连接） - 执行备份操作

-- 1. 首先查看当前活动事务
SELECT * FROM information_schema.INNODB_TRX;

-- 2. 加全局读锁（会自动提交当前活动事务）
FLUSH TABLES WITH READ LOCK;
然后执行
INSERT INTO users (username, email, balance) VALUES
('bow', 'Bow@example.com', 1000.00)


-- 3. 查看锁状态（在新会话中执行）
-- 在另一个终端连接MySQL执行：
SHOW PROCESSLIST;
/* 4	event_scheduler	localhost		Daemon	13163	Waiting on empty queue	
107	root	localhost:58662	sql_lock1_demo	Sleep	18		
108	root	localhost:58668	sql_lock1_demo	Query	10	Waiting for global read lock	INSERT INTO users (username, email, balance) VALUES
('bow', 'Bow@example.com', 1000.00)
109	root	localhost:58672	sql_lock1_demo	Query	0	starting	SHOW PROCESSLIST
*/

-- 4. 执行备份操作（这里用SELECT模拟）
-- 编辑 my.ini  重启
[mysqld]
secure_file_priv = ""


-- 备份用户数据
SELECT * FROM users INTO OUTFILE '/users_backup.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 备份订单数据
SELECT * FROM orders INTO OUTFILE '/orders_backup.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 备份商品数据
SELECT * FROM products INTO OUTFILE '/products_backup.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 5. 释放全局锁
UNLOCK TABLES;

-- 6. 验证备份文件
-- 在系统shell中执行：
-- cat /users_backup.csv
-- cat /orders_backup.csv
-- cat /products_backup.csv

b. 表级锁 (Table Lock)

表级锁是对整张表加锁，MyISAM 引擎默认使用表级锁，InnoDB 也支持手动表锁。

ⅰ. 主要类型：

1. 表共享读锁 (Table Read Lock)-读锁

特性：

• 允许多个会话同时获取读锁
• 持有读锁的会话只能读表，不能写
• 其他会话可以读表，但不能写表

LOCK TABLES table_name READ;  -- 加表读锁
UNLOCK TABLES;                -- 释放锁

表共享读锁(Table Read Lock)案例：

-- 会话1（报表生成）
-- 加表读锁
LOCK TABLES employees READ;

-- 可以正常读取数据
SELECT * FROM employees WHERE department = '研发部';
/*
+----+------+------------+----------+------------+
| id | name | department | salary   | join_date  |
+----+------+------------+----------+------------+
|  1 | 张三 | 研发部     | 15000.00 | 2020-05-10 |
|  2 | 李四 | 研发部     | 18000.00 | 2019-03-15 |
+----+------+------------+----------+------------+
*/

-- 尝试修改数据会报错
UPDATE employees SET salary = 16000 WHERE id = 1;
-- 错误：Table 'employees' was locked with a READ lock and can't be updated

-- 保持锁不释放，继续在会话2测试

-- 会话2（其他操作）
-- 可以加读锁（允许多个会话同时持有读锁）
LOCK TABLES employees READ;
SELECT * FROM employees WHERE department = '市场部'; -- 成功
UNLOCK TABLES;

-- 可以不加锁直接读
SELECT * FROM employees; -- 成功

-- 尝试写操作会被阻塞
UPDATE employees SET salary = salary + 1000 WHERE id = 3; -- 被阻塞

-- 会话1释放锁后
UNLOCK TABLES;
-- 会话2的更新操作会继续执行

2. 表独占写锁 (Table Write Lock)-写锁

特性：

• 只有一个会话能获取写锁
• 持有写锁的会话可以除select以外的所有操作
• 其他会话不能读也不能写表

LOCK TABLES table_name WRITE;  -- 加表写锁

表独占写锁(Table Write Lock)案例：

-- 会话1（数据维护）
-- 加表写锁
LOCK TABLES employees WRITE;

-- 不可以读写数据  阻塞
SELECT * FROM employees WHERE salary < 13000;

-- 执行批量更新
UPDATE employees SET salary = salary * 1.1 WHERE salary < 13000;

-- 保持锁不释放，继续在会话2测试

-- 会话2（其他操作）
-- 尝试读操作会被阻塞
SELECT * FROM employees; -- 被阻塞

-- 尝试写操作会被阻塞
INSERT INTO employees VALUES (NULL, '钱七', '市场部', 15000.00, CURDATE()); -- 被阻塞

-- 会话1释放锁
UNLOCK TABLES;
-- 会话2的操作会继续执行

3. 元数据锁 (Metadata Lock, MDL)

元数据锁：metadata lock，简称MDL，它是在MySQL 5.5版本引进的。元数据锁不用像表锁那样显式的加锁和释放锁，而是在访问表时被自动加上，以保证读写的正确性。加锁和释放锁规则如下：

MDL读锁之间不互斥，也就是说，允许多个线程同时对加了 MDL读锁的表进行CRUD(增删改查)操作；

MDL写锁，它和读锁、写锁都是互斥的，目的是用来保证变更表结构操作的安全性。也就是说，当对表结构进行变更时，会被默认加 MDL写锁，因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MDL读写锁是在事务commit之后才会被释放；

自动加锁，无需显式命令：

• 当访问表时自动加 MDL 读锁
• 当修改表结构时自动加 MDL 写锁

元数据锁(MDL)案例：

-- 会话1：
START TRANSACTION;
-- 获取MDL读锁
SELECT * FROM employees WHERE id = 1;
-- 不提交事务，保持连接

-- 会话2：
-- 尝试修改表结构（需要MDL写锁）
ALTER TABLE employees ADD COLUMN bonus DECIMAL(10,2); -- 被阻塞

-- 会话3：
-- 可以正常查询（MDL读锁兼容）
SELECT * FROM employees; -- 成功

-- 会话1提交后
COMMIT;
-- 会话2的ALTER操作会继续执行

ⅱ. 使用场景

• MyISAM 表的读写操作(自动加锁)
• 需要锁定整表的特殊操作
• 表结构变更(自动 MDL 锁)

ⅲ. 注意事项

• 表锁粒度大，并发性能差
• LOCK TABLES 会隐式提交当前事务
• 使用 UNLOCK TABLES 会释放当前会话持有的所有表锁
• MDL 锁可能导致长时间阻塞(如长事务中执行 DDL)

c. 行级锁 (Row Lock)

行级锁是 InnoDB 引擎特有的锁机制，可以精确锁定表中的单行或多行记录，大大提高了并发性能。

ⅰ. 特性

• 粒度小：只锁定需要的行，并发度高
• 开销大：加锁需要更多资源
• 死锁风险：容易出现循环等待
• 自动释放：事务结束时自动释放

CREATE TABLE `lock_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_value` (`value`)
) ENGINE=InnoDB;

INSERT INTO `lock_test` VALUES 
(5, 100, 'A'),
(10, 200, 'B'),
(15, 300, 'C'),
(20, 400, 'D');

ⅱ. 主要类型

1. 记录锁 (Record Lock)

Record Lock，记录锁，它是针对索引记录的锁，锁定的总是索引记录。在多用户数据库系统中，多个事务可能会同时尝试读取或修改同一条记录，Record Lock确保只有一个事务能在某一时刻修改该记录，其他事务只能读取，或者在写锁释放后再进行修改。

• 锁定索引中的单条记录
• 总是锁定索引记录，即使表没有定义索引，InnoDB 也会创建隐藏的聚簇索引

-- 会话1
START TRANSACTION;
SELECT * FROM lock_test WHERE id = 10 FOR UPDATE;  -- 对id=10加X锁

-- 会话2
UPDATE lock_test SET name = 'B2' WHERE id = 10;  -- 被阻塞
UPDATE lock_test SET name = 'A2' WHERE id = 5;   -- 成功（未锁定）

2. 间隙锁 (Gap Lock)

Gap Lock，间隙锁，它是一种行级锁，锁住两个索引记录之间的间隙，而不是实际的数据记录，由InnoDB隐式添加。

• 锁定索引记录之间的间隙
• 防止其他事务在间隙中插入数据
• 只在 REPEATABLE READ 隔离级别下有效

间隙读锁(FOR SHARE)：

-- 会话1（获取间隙读锁）
START TRANSACTION;
SELECT * FROM lock_test WHERE value BETWEEN 150 AND 350 FOR SHARE;
-- 锁定(100,200], (200,300], (300,400]区间

-- 会话2（测试插入）
INSERT INTO lock_test VALUES (34, 90, 'Z');   -- 未阻塞
INSERT INTO lock_test VALUES (8, 110, 'E');  -- 被阻塞（落在(100,200]）
INSERT INTO lock_test VALUES (12, 250, 'F'); -- 被阻塞（落在(200,300]）
INSERT INTO lock_test VALUES (17, 380, 'G'); -- 被阻塞（落在(300,400]）

INSERT INTO lock_test VALUES (31, 500, 'I');   -- 阻塞

INSERT INTO lock_test VALUES (39, 100, 'X');   --- 阻塞

-- 会话3（测试读取）
SELECT * FROM lock_test WHERE value = 200; -- 成功（间隙读锁不阻塞读）

说明：由 InnoDB 间隙锁的具体实现机制决定的

• 现有数据：(5,100), (10,200), (15,300), (20,400)
• 查询条件：value BETWEEN 150 AND 350

1. 区间锁定规则：

• • 查询条件 BETWEEN 150 AND 350 会锁定所有扫描过的区间
  • 区间：(100,200]，(200,300]，(300,400]

1. 边界处理：

• • 不锁定查询范围之外的区间（如 (-∞,100] 和 (400,+∞)）
  • 对边界值200和300采用"闭"原则（包含这些值）

1. 为什么 INSERT INTO lock_test VALUES (39, 100, 'X'); 无法插入呢？

InnoDB 实际上使用的是 Next-Key Lock（临键锁），它是 记录锁(Record Lock) + 间隙锁(Gap Lock) 的组合。插入 value=100 时需要获取 插入意向锁、插入意向锁会与 (100,200] 区间锁冲突、InnoDB 的锁检查是 保守型 的，会阻止潜在的冲突

间隙写锁(FOR UPDATE)

InnoDB在REPEATABLE READ隔离级别下实际使用的是临键锁(Next-Key Lock)，它是记录锁和间隙锁的组合。

场景1：记录+间隙锁定

-- 会话1
START TRANSACTION;
SELECT * FROM lock_test WHERE value = 200 FOR UPDATE;
-- 锁定记录200和(100,200)间隙

-- 会话2
INSERT INTO lock_test VALUES (8, 150, 'K'); -- 被阻塞（在间隙中）
UPDATE lock_test SET name = 'B2' WHERE value = 200; -- 被阻塞（记录被锁）
INSERT INTO lock_test VALUES (12, 250, 'L'); -- 成功（不在锁定范围）

场景2：范围锁定

-- 会话1
START TRANSACTION;
SELECT * FROM lock_test WHERE value > 200 AND value < 300 FOR UPDATE;
-- 锁定[200,300]区间及周围间隙

-- 会话2
INSERT INTO lock_test VALUES (6, 90, 'T');   -- 成功
INSERT INTO lock_test VALUES (7, 199, 'M');  -- 被阻塞（锁定到左边界扩展）
INSERT INTO lock_test VALUES (12, 250, 'N'); -- 被阻塞（区间内）
INSERT INTO lock_test VALUES (17, 301, 'O'); -- 被阻塞（右边界扩展）

-- 明确排除边界
SELECT * FROM lock_test 
WHERE value > 200 AND value < 300
AND value NOT IN (200,300) FOR UPDATE;

INSERT INTO lock_test VALUES (22, 200, 'X');
INSERT INTO lock_test VALUES (299, 300, 'X');

3. 临键锁 (Next-Key Lock)

Next-Key Lock，称为临键锁，它是Record Lock + Gap Lock的组合，用来锁定一个范围，并且锁定记录本身锁，它是一种左开右闭的范围，可以用符号表示为：(a,b]。

• 记录锁 + 间隙锁的组合
• 锁定记录本身和记录前面的间隙
• InnoDB 默认的行锁类型

-- 创建测试表
CREATE TABLE next_key_lock_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    range_val INT NOT NULL,
    data VARCHAR(100),
    INDEX idx_range (range_val)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO next_key_lock_demo (range_val, data) VALUES 
(10, 'A'), (20, 'B'), (30, 'C'), (40, 'D'), (50, 'E');

场景1：普通范围查询：

-- 会话1
START TRANSACTION;
-- 使用临键锁锁定20-40范围（不包含边界）
SELECT * FROM next_key_lock_demo 
WHERE range_val > 20 AND range_val < 40 
FOR UPDATE;

-- 查看实际获取的锁
SELECT 
    INDEX_NAME, 
    LOCK_TYPE, 
    LOCK_MODE, 
    LOCK_DATA 
FROM performance_schema.data_locks 
WHERE OBJECT_NAME = 'next_key_lock_demo';
-- 测试不同位置的插入
INSERT INTO next_key_lock_demo VALUES (NULL, 15, 'F');  -- ✅ 成功 (在10-20区间)
INSERT INTO next_key_lock_demo VALUES (NULL, 25, 'G');  -- ❌ 被阻塞 (在20-30区间)
INSERT INTO next_key_lock_demo VALUES (NULL, 35, 'H');  -- ❌ 被阻塞 (在30-40区间)
INSERT INTO next_key_lock_demo VALUES (NULL, 45, 'I');  -- ✅ 成功 (在40-50区间)

-- 测试边界值
INSERT INTO next_key_lock_demo VALUES (NULL, 20, 'J');  -- ❌ 被阻塞 (临键锁保护)
INSERT INTO next_key_lock_demo VALUES (NULL, 40, 'K');  -- ✅ 成功 (上边界不锁)

对比不同查询条件

为什么左边界(20)要锁定？

1. 防止幻读的需要：

• • 假设表中有数据：10, 20, 30, 40, 50
  • 你的查询条件是 >20 AND <40，应该返回30
  • 如果不锁定20之后的间隙，其他事务可以插入25这样的值，导致你的第二次查询可能返回25和30，这就是幻读

1. 锁定的是"20之后"的间隙：

• • 不是锁定20这个值本身(因为条件是>20，不包含等于)
  • 锁定的是(20,30)这个间隙
  • 技术上实现为：在20这条记录上加临键锁，锁定的是20到下一个实际存在的值(30)之间的间隙

1. 实际锁定范围：

• • 锁定所有大于20的记录，直到遇到第一个大于等于40的记录
  • 在示例数据中：锁定(20,30)间隙，锁定30这条记录本身，(30,40)间隙

1. 为什么不是从21开始：

• • 数据库索引不是按连续整数组织的
  • 无法预知20和下一个值之间有多大间隔(可能是20.0001，也可能是29)
  • 锁定20之后的间隙是唯一可靠的方法
  • 因为索引判断"是否可能进入锁定间隙"时，会把等于左边界的插入也视为潜在冲突

右边界(40)为什么不锁定？

1. 条件是不包含40(<40)
2. 只需要保证没有记录插入到小于40的位置
3. 已经通过锁定30和(30,40)间隙实现了这一点
4. 不需要锁定40本身，因为条件明确排除了40

锁升级案例

-- 会话1：大范围查询导致锁升级
START TRANSACTION;
SELECT * FROM next_key_lock_demo 
WHERE range_val > 10 
FOR UPDATE;

-- 会话2：所有插入都被阻塞
INSERT INTO next_key_lock_demo VALUES (NULL, 5, 'R');   -- ❌ 被阻塞
INSERT INTO next_key_lock_demo VALUES (NULL, 60, 'S');  -- ❌ 被阻塞

4. 插入意向锁 (Insert Intention Lock)

意向锁(Intention Lock)是InnoDB中一种特殊的表级锁，但它与行锁密切相关。

当一个事务在需要获取资源的锁定时，如果该资源已经被排他锁占用，则数据库会自动给该事务申请一个该表的意向锁。如果自己需要一个共享锁定，就申请一个意向共享锁。如果需要的是某行（或者某些行）的排他锁定，则申请一个意向排他锁。

意向锁的存在是为了协调行锁和表锁的关系，用于优化InnoDB加锁的策略。意向锁的主要功能就是：避免为了判断表是否存在行锁而去全表扫描。

意向锁是由InnoDB在操作数据之前自动加的，不需要用户干预；

• 意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
• 意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

特点：

• 特殊的间隙锁
• 表示事务想在某个间隙插入记录
• 多个事务可以在同一间隙的不同位置插入

-- 创建测试表
CREATE TABLE intention_lock_demo (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT,
    INDEX idx_name (name)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO intention_lock_demo VALUES 
(1, 'Alice', 25),
(2, 'Bob', 30),
(3, 'Charlie', 35);

意向共享锁(IS)演示：

-- 会话1  获取行共享锁(S锁)
START TRANSACTION;
SELECT * FROM intention_lock_demo WHERE id = 1 LOCK IN SHARE MODE;

-- 会话2 查看锁状态
-- 在新会话中执行
SELECT 
    ENGINE_TRANSACTION_ID,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM 
    performance_schema.data_locks
WHERE 
    OBJECT_NAME = 'intention_lock_demo';

意向排他锁(IX)演示：

-- 会话1  获取行排他锁(X锁)
START TRANSACTION;
SELECT * FROM intention_lock_demo WHERE id = 2 FOR UPDATE;

-- 会话2 查看锁等待
SELECT 
    ENGINE_TRANSACTION_ID,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM 
    performance_schema.data_locks
WHERE 
    OBJECT_NAME = 'intention_lock_demo';