行锁与表锁

行锁和表锁是两种不同的锁粒度，各有适用场景。

行级锁

定义

行级锁锁定表中的特定行，其他行仍可访问，是InnoDB默认的锁粒度。

加锁方式

-- InnoDB自动加行锁（DML）
UPDATE orders SET amount = 100 WHERE id = 1;  -- 锁id=1行
DELETE FROM orders WHERE id = 1;              -- 锁id=1行

-- 手动加行锁（锁定读）
SELECT * FROM orders WHERE id = 1 FOR UPDATE;      -- X锁
SELECT * FROM orders WHERE id = 1 FOR SHARE;       -- S锁

行锁特点

优点：
- 锁粒度小，并发度高
- 只锁需要的行，其他行可访问
- 适合高并发场景

缺点：
- 锁管理开销大（维护每行锁信息）
- 容易产生死锁（多行交叉锁定）

行锁生效条件

-- ✅ 有索引匹配：行锁生效
CREATE INDEX idx_user ON orders(user_id);
UPDATE orders SET amount = 100 WHERE user_id = 1;
-- 只锁user_id=1匹配的行

-- ❌ 无索引匹配：退化为表锁
UPDATE orders SET amount = 100 WHERE amount + 1 = 100;
-- 无法使用索引，扫描全表加锁
-- 实际锁住所有行（表锁效果）

表级锁

定义

表级锁锁定整张表，阻止其他事务访问表中任何数据。

加锁方式

-- 显式表锁
LOCK TABLE orders READ;   -- 表级S锁
LOCK TABLE orders WRITE;  -- 表级X锁

-- 释放表锁
UNLOCK TABLES;

-- MDL锁（DDL自动获取）
ALTER TABLE orders ADD COLUMN new_col INT;  -- 获取MDL写锁

表锁特点

优点：
- 锁开销小（只需一个锁）
- 无死锁（单一锁）
- 实现简单

缺点：
- 锁粒度大，并发度低
- 阻止所有对表的访问
- 不适合高并发场景

表锁适用场景

-- 1. 全表更新/删除
LOCK TABLE orders WRITE;
UPDATE orders SET status = 0;
UNLOCK TABLES;

-- 2. 批量数据导入
LOCK TABLE orders WRITE;
LOAD DATA INFILE 'data.csv' INTO TABLE orders;
UNLOCK TABLES;

-- 3. MyISAM引擎（默认表锁）
-- MyISAM不支持行锁，只能用表锁

行锁与表锁对比

意向锁

定义

意向锁是表级锁，表示事务打算在行上加锁，用于快速检测锁冲突。

意向锁类型

IS（意向共享锁）：事务将在某些行加S锁
IX（意向排他锁）：事务将在某些行加X锁

作用：
事务请求行锁时，先在表上加意向锁
其他事务请求表锁时，检查意向锁即可判断冲突
无需逐行检查是否有行锁

意向锁加锁过程

-- 事务请求行X锁
UPDATE orders SET amount = 100 WHERE id = 1;

-- 加锁过程：
-- 1. 检查表是否有冲突的表锁
-- 2. 在表上加IX锁（意向排他锁）
-- 3. 在id=1行上加X锁

-- 其他事务请求表锁时：
-- 检查表上有IX锁 → 知道有行X锁 → 阻止表锁

意向锁兼容性

┌────────────────────────────────────────┐
│         意向锁兼容矩阵                   │
├──────────┬──────┬──────┬──────┬───────┤
│          │  S   │  X   │  IS  │  IX   │
├──────────┼──────┼──────┼──────┼───────┤
│    S     │  ✅  │  ❌  │  ✅  │  ❌   │
├──────────┼──────┼──────┼──────┼───────┤
│    X     │  ❌  │  ❌  │  ❌  │  ❌   │
├──────────┼──────┼──────┼──────┼───────┤
│    IS    │  ✅  │  ❌  │  ✅  │  ✅   │
├──────────┼──────┼──────┼──────┼───────┤
│    IX    │  ❌  │  ❌  │  ✅  │  ✅   │
└──────────┴──────┴──────┴──────┴───────┘

IS和IX相互兼容，因为意向锁不真正锁定行
只是声明"打算在行上加锁"
具体冲突在行锁层面检测

InnoDB锁降级

行锁退化为表锁

-- 无索引的条件导致全表扫描
-- 每扫描一行加锁 → 实际锁住所有行

UPDATE orders SET status = 1 WHERE amount + 1 = 100;
-- amount无索引，无法定位
-- 扫描所有行并加锁
-- 效果等同于表锁

-- 建议：确保条件列有索引
CREATE INDEX idx_amount ON orders(amount);
UPDATE orders SET status = 1 WHERE amount = 99;
-- 只锁amount=99的行

锁粒度选择策略

选择决策

高并发OLTP场景 → 行锁
├── 事务短小
├── 频繁更新少量行
└── InnoDB引擎

批量操作/低并发场景 → 表锁
├── 全表更新
├── 批量导入
├── MyISAM引擎
└── 低并发读场景

实际应用

-- 高并发订单更新（行锁）
-- InnoDB自动使用行锁
START TRANSACTION;
UPDATE orders SET status = 2 WHERE id = 1001;
COMMIT;

-- 批量状态更新（考虑表锁）
-- 数据量小可继续用行锁
-- 数据量大可考虑LOCK TABLE
LOCK TABLE orders WRITE;
UPDATE orders SET status = 0 WHERE created_at < '2020-01-01';
UNLOCK TABLES;

查看锁粒度

查看当前锁

-- MySQL 8.0+
SELECT
    OBJECT_NAME AS table_name,
    INDEX_NAME,
    LOCK_TYPE,     -- RECORD(行锁)/TABLE(表锁)
    LOCK_MODE,
    LOCK_DATA      -- 行锁时显示锁定的数据
FROM performance_schema.data_locks;

-- MySQL 5.7
SELECT * FROM information_schema.INNODB_LOCKS;

判断锁类型

-- LOCK_TYPE字段
-- RECORD: 行锁
-- TABLE: 表锁

-- LOCK_MODE字段
-- S: 共享锁
-- X: 排他锁
-- IS/IX: 意向锁
-- S_REC/X_REC: 行级共享/排他锁

MyISAM表锁

MyISAM只支持表锁

-- MyISAM读操作自动加表级共享锁
SELECT * FROM orders;  -- 自动LOCK TABLE orders READ

-- MyISAM写操作自动加表级排他锁
INSERT INTO orders VALUES (...);  -- 自动LOCK TABLE orders WRITE

-- 读读并发，读写/写写串行

要点总结

• 行锁粒度小并发高，表锁粒度大并发低
• InnoDB默认行锁，MyISAM只支持表锁
• 无索引条件时行锁退化为表锁（锁所有行）
• 意向锁（IS/IX）是表级锁，快速检测行锁冲突
• 高并发OLTP使用行锁，批量操作可考虑表锁
• 确保WHERE条件有索引，避免行锁退化为表锁