数据库核心术语知识库

定义

锁粒度 (Lock Granularity) 是指数据库锁定的数据范围大小。锁粒度越粗(如表锁),同时被阻塞的事务越多,并发度越低,但锁管理的开销越小;锁粒度越细(如行锁),并发度越高,但锁管理的开销越大。

常见的锁粒度(从粗到细):

数据库锁:锁定整个数据库
表锁 (Table Lock):锁定整张表
页锁 (Page Lock):锁定一个数据页(通常 8-16KB)
行锁 (Row Lock):锁定单行记录
字段锁 (Field Lock):锁定单个字段(极少使用)

详细笔记

核心原理

锁粒度的权衡

锁粒度粗(如表锁):
  ✅ 优点:
  - 锁管理简单
  - 内存占用少
  - 获取/释放锁快
  
  ❌ 缺点:
  - 并发度低
  - 锁冲突多
  - 容易阻塞其他事务

锁粒度细(如行锁):
  ✅ 优点:
  - 并发度高
  - 锁冲突少
  - 适合 OLTP
  
  ❌ 缺点:
  - 锁管理复杂
  - 内存占用多
  - 获取/释放锁慢

各种锁粒度详解

一、表锁(Table Lock)

特点:

锁定整张表
开销最小
并发度最低

MySQL 示例:

sql

-- 显式表锁
LOCK TABLES users READ; -- 读锁(共享锁)
SELECT * FROM users;
UNLOCK TABLES;

LOCK TABLES users WRITE;  -- 写锁(排他锁)
UPDATE users SET name = '新名' WHERE id = 1;
UNLOCK TABLES;

-- 隐式表锁(MyISAM 引擎)
CREATE TABLE myisam_users (...) ENGINE=MyISAM;
-- MyISAM 只支持表锁,不支持行锁

适用场景:

批量导入/导出数据
DDL 操作(ALTER TABLE)
MyISAM 引擎(不支持行锁)
全表扫描的 UPDATE/DELETE

性能对比:

sql

-- 场景:更新 10000 行

-- 行锁(InnoDB)
UPDATE orders SET status = 1 WHERE create_time < '2024-01-01';
-- - 锁定 10000 行
-- - 内存: 10000 个锁结构
-- - 并发: 其他事务可访问未锁定的行

-- 表锁(MyISAM)
UPDATE orders SET status = 1 WHERE create_time < '2024-01-01';
-- - 锁定整张表
-- - 内存: 1 个锁结构
-- - 并发: 其他事务完全无法访问该表

二、页锁(Page Lock)

特点:

锁定一个数据页(通常 8-16KB)
折中方案
MySQL InnoDB 不使用页锁,但 SQL Server 和 BerkeleyDB 使用

SQL Server 示例:

sql

-- SQL Server 可以指定锁粒度
SELECT * FROM users WITH (PAGLOCK) WHERE id > 100;
-- 使用页锁而非行锁

-- 查看锁信息
SELECT 
  resource_type,
  request_mode,
  request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

-- 输出:
-- +-----------------+--------------+----------------+
-- | resource_type | request_mode | request_status |
-- +-----------------+--------------+----------------+
-- | PAGE    | X    | GRANT    |  ← 页锁
-- +-----------------+--------------+----------------+

页锁的优势:

比行锁开销小
比表锁并发度高
适合中等粒度的访问

三、行锁(Row Lock)

特点:

锁定单行记录
开销最大
并发度最高
InnoDB 默认使用

InnoDB 行锁实现:

cpp

// storage/innobase/lock/lock0lock.cc

/**
 * 添加行锁
 */
void lock_rec_add(
  ulint   type_mode,  // 锁类型(X/S) + 模式
  trx_t*  trx,    // 事务
  dict_index_t* index,  // 索引
  const rec_t* rec)   // 记录
{
  // 1. 计算记录的 hash 值
  ulint hash_value = lock_rec_hash(index->space, index->page_no, rec);
  
  // 2. 在锁哈希表中查找或创建锁对象
  lock_t* lock = lock_rec_create(type_mode, trx, index, rec);
  
  // 3. 检查是否与现有锁冲突
  if (lock_has_conflicts(lock)) {
    // 冲突,等待
    lock_wait(trx, lock);
  } else {
    // 无冲突,授予锁
    lock_grant(lock);
  }
}

/**
 * 锁数据结构
 */
struct lock_t {
  trx_t*  trx;    // 持有锁的事务
  ulint   type_mode;  // 锁类型和模式
  hash_node_t hash;   // 哈希节点
  // ... 其他字段
};

行锁的内存开销:

每个行锁约占用:
- lock_t 结构: 约 100 字节
- 哈希表开销: 约 50 字节
- 总计: 约 150 字节/行

如果锁定 100 万行:
- 内存占用: 100万 × 150字节 ≈ 150MB

InnoDB 配置:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 默认: 128MB
-- 建议: 物理内存的 50-70%

如果锁太多,可能耗尽内存!

行锁升级:

sql

-- InnoDB 不会自动升级行锁为表锁
-- 但如果锁定行数太多,可能导致:
-- 1. 内存耗尽
-- 2. 性能下降
-- 3. 死锁风险增加

-- 监控锁数量
SELECT 
  COUNT(*) AS lock_count
FROM performance_schema.data_locks;

-- 如果 lock_count > 100000,需要优化

不同数据库的锁粒度

MySQL InnoDB

支持的锁粒度:
- 行锁(默认)
- 表锁(LOCK TABLES)
- 间隙锁(Gap Lock)
- Next-Key Lock

不支持:
- 页锁

特点:
- 通过索引加锁
- 无索引时退化为表锁
- MVCC 减少锁竞争

无索引时的锁退化:

sql

CREATE TABLE users (
  id INT,
  name VARCHAR(50)
);
-- 没有索引!

-- 查询
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 因为没有索引,InnoDB 无法定位具体行
-- 只能锁定整张表!❌

-- EXPLAIN 查看
EXPLAIN SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- type: ALL (全表扫描)
-- Extra: Using where

-- 解决方案:添加索引
CREATE INDEX idx_id ON users(id);

-- 再次查询
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 现在只锁定一行 ✅

PostgreSQL

支持的锁粒度:
- 行锁(默认,通过 MVCC 实现)
- 表锁(LOCK TABLE)
- 页锁(内部使用,不对外)

特点:
- MVCC 实现无锁读取
- 写操作才需要锁
- 无死锁检测(靠超时)

SQL Server

支持的锁粒度:
- 行锁(RID/Key)
- 页锁(Page)
- 表锁(Table)
- 数据库锁(Database)
- 自动锁升级

特点:
- 动态锁升级
- 行锁 → 页锁 → 表锁
- 可配置升级阈值

SQL Server 锁升级:

sql

-- 配置锁升级阈值
ALTER TABLE users SET (LOCK_ESCALATION = AUTO);

-- 默认:
-- - 单个语句锁定 5000 行以上
-- - 或锁内存超过 40% buffer pool
-- → 自动升级为表锁

-- 禁用锁升级
ALTER TABLE users SET (LOCK_ESCALATION = DISABLE);

-- 强制表锁
ALTER TABLE users SET (LOCK_ESCALATION = TABLE);

锁粒度的选择策略

策略一:根据访问模式选择

全表扫描/批量更新:
  → 表锁
  → 理由:反正要访问所有行,表锁更简单

范围查询:
  → 行锁或页锁
  → 理由:只访问部分数据

点查询:
  → 行锁
  → 理由:精确访问单行

混合负载:
  → 行锁(默认)
  → 理由:平衡并发和开销

策略二:根据并发需求选择

高并发 OLTP:
  → 行锁
  → 目标:最大化并发

数据仓库 OLAP:
  → 表锁或分区锁
  → 目标:简化锁管理,批量处理

读写分离:
  → 读无锁(MVCC),写行锁
  → 目标:读写互不干扰

策略三:根据数据量选择

小表(< 10000 行):
  → 表锁也可接受
  → 锁开销占比小

中等表(10000 - 1000000 行):
  → 行锁
  → 平衡并发和开销

大表(> 1000000 行):
  → 行锁 + 分区
  → 避免锁太多行

监控锁粒度

MySQL Performance Schema

sql

-- 查看当前锁的粒度分布
SELECT 
  lock_type,
  COUNT(*) AS lock_count
FROM performance_schema.data_locks
GROUP BY lock_type;

-- 输出:
-- +-----------+------------+
-- | lock_type | lock_count |
-- +-----------+------------+
-- | RECORD  | 12345  |  ← 行锁
-- | TABLE   | 5    |  ← 表锁
-- +-----------+------------+

-- 查看锁等待
SELECT 
  OBJECT_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS,
  LOCK_DATA
FROM performance_schema.data_locks
WHERE LOCK_STATUS = 'WAITING';

SQL Server 动态视图

sql

-- 查看锁粒度分布
SELECT 
  resource_type,
  request_mode,
  COUNT(*) AS lock_count
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
GROUP BY resource_type, request_mode;

-- 输出:
-- +-----------------+--------------+------------+
-- | resource_type | request_mode | lock_count |
-- +-----------------+--------------+------------+
-- | RID     | X    | 5000   |  ← 行锁
-- | PAGE    | X    | 100    |  ← 页锁
-- | OBJECT    | IX     | 2    |  ← 表锁
-- +-----------------+--------------+------------+

锁粒度的性能影响

测试对比

sql

-- 测试表
CREATE TABLE test_locks (
  id INT PRIMARY KEY,
  value INT
) ENGINE=InnoDB;

-- 插入 100000 行
INSERT INTO test_locks VALUES (...);

-- 测试一:行锁
START TRANSACTION;
UPDATE test_locks SET value = 1 WHERE id BETWEEN 1 AND 1000;
COMMIT;

-- 结果:
-- - 锁定 1000 行
-- - 内存: 约 150KB
-- - 并发: 其他事务可访问 id > 1000 的行
-- - TPS: 1000

-- 测试二:表锁
LOCK TABLES test_locks WRITE;
UPDATE test_locks SET value = 1 WHERE id BETWEEN 1 AND 1000;
UNLOCK TABLES;

-- 结果:
-- - 锁定整张表
-- - 内存: 约 100 字节
-- - 并发: 其他事务完全阻塞
-- - TPS: 5000 (单次操作更快,但并发差)

实际案例

案例一:电商库存扣减的锁粒度选择

sql

-- 场景:秒杀活动,大量并发扣减库存

-- 方案一:行锁
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
IF stock > 0 THEN
  UPDATE products SET stock = stock - 1 WHERE id = 1;
  COMMIT;
ELSE
  ROLLBACK;
END IF;

-- 问题:
-- - 高并发下,大量事务等待同一行锁
-- - 形成热点,性能瓶颈
-- - TPS: 约 1000

-- 方案二:分段锁(优化)
-- 将库存分为 100 个段
CREATE TABLE product_stock_segments (
  product_id INT,
  segment_id INT,
  stock INT,
  PRIMARY KEY (product_id, segment_id)
);

-- 初始化:每段 10 个库存
INSERT INTO product_stock_segments VALUES (1, 1, 10), (1, 2, 10), ..., (1, 100, 10);

-- 扣减时随机选择一段
START TRANSACTION;
SELECT @seg := FLOOR(1 + RAND() * 100);
UPDATE product_stock_segments 
SET stock = stock - 1 
WHERE product_id = 1 AND segment_id = @seg AND stock > 0;

IF ROW_COUNT() = 0 THEN
  ROLLBACK;
  -- 重试或其他段
ELSE
  COMMIT;
END IF;

-- 效果:
-- - 锁分散到 100 个段
-- - 并发提升 100 倍
-- - TPS: 约 100000

案例二:批量导入的锁优化

sql

-- 问题:批量导入 100 万条数据很慢

-- 原始方式(行锁)
START TRANSACTION;
FOR i IN 1..1000000 LOOP
  INSERT INTO users VALUES (...);
END LOOP;
COMMIT;

-- 问题:
-- - 100 万个行锁
-- - 内存占用: 约 150MB
-- - 锁管理开销大
-- - 执行时间: 10 分钟

-- 优化一:分批提交
FOR batch_start IN 1..1000000 STEP 10000 LOOP
  START TRANSACTION;
  FOR i IN batch_start..batch_start+9999 LOOP
    INSERT INTO users VALUES (...);
  END LOOP;
  COMMIT;
END LOOP;

-- 效果:
-- - 每批 10000 个行锁
-- - 内存占用: 约 1.5MB
-- - 执行时间: 5 分钟

-- 优化二:使用 LOAD DATA(表锁)
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ',';

-- 效果:
-- - 表锁,无行锁开销
-- - 顺序写入,效率高
-- - 执行时间: 1 分钟

最佳实践

优先使用行锁:大多数 OLTP 场景的最佳选择
避免无索引的行锁:会导致表锁退化
批量操作考虑表锁:如果独占表,表锁更高效
监控锁数量:避免锁定太多行导致内存问题
SQL Server 注意锁升级:合理配置阈值
热点数据分段:分散锁竞争
缩短事务长度:减少锁持有时间
选择合适的隔离级别:RC 比 RR 锁更少

关联术语

[[死锁]]
[[间隙锁]]
[[事务隔离级别]]
[[MVCC]]

参考资料

MySQL 官方文档: InnoDB Locking
Microsoft Docs: Lock Granularity
《高性能 MySQL》第 7 章:事务与锁
PostgreSQL 文档: Explicit Locking

定义 ​

详细笔记 ​

核心原理 ​

锁粒度的权衡 ​

各种锁粒度详解 ​

一、表锁(Table Lock) ​

二、页锁(Page Lock) ​

三、行锁(Row Lock) ​

不同数据库的锁粒度 ​

MySQL InnoDB ​

PostgreSQL ​

SQL Server ​

锁粒度的选择策略 ​

策略一:根据访问模式选择 ​

策略二:根据并发需求选择 ​

策略三:根据数据量选择 ​

监控锁粒度 ​

MySQL Performance Schema ​

SQL Server 动态视图 ​

锁粒度的性能影响 ​

测试对比 ​

实际案例 ​

案例一:电商库存扣减的锁粒度选择 ​

案例二:批量导入的锁优化 ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义