数据库锁 - 快速参考手册

📊 锁粒度对比

锁类型	并发度	开销	适用场景	典型数据库
表级锁	⭐⭐	低	DDL、批量操作	MySQL MyISAM, PostgreSQL
行级锁	⭐⭐⭐⭐⭐	高	高并发 OLTP	MySQL InnoDB, Oracle
页级锁	⭐⭐⭐	中	折中方案	MySQL BDB
库级锁	⭐	极低	备份、维护	所有数据库

🔐 锁模式对比

锁模式	符号	读兼容	写兼容	使用场景
共享锁	S	✅	❌	SELECT ... LOCK IN SHARE MODE
排他锁	X	❌	❌	UPDATE, DELETE, INSERT
意向共享	IS	✅	❌	准备在行上加 S 锁
意向排他	IX	❌	❌	准备在行上加 X 锁

锁兼容性矩阵

        | S    | X    | IS   | IX
--------|------|------|------|-----
S       | ✅   | ❌   | ✅   | ❌
X       | ❌   | ❌   | ❌   | ❌
IS      | ✅   | ❌   | ✅   | ✅
IX      | ❌   | ❌   | ✅   | ✅

🔧 MySQL InnoDB 锁算法

算法	锁定范围	防止问题	隔离级别要求
记录锁	单个索引记录	更新冲突	所有级别
间隙锁	索引间的间隙	幻读	REPEATABLE READ
临键锁	记录 + 前间隙	幻读 + 更新冲突	REPEATABLE READ

⚖️ 锁策略对比

策略	假设	优点	缺点	适用场景
悲观锁	冲突必然发生	数据安全	性能较低，可能死锁	高冲突场景
乐观锁	冲突很少发生	高性能	需要重试机制	低冲突场景

实现方式对比

悲观锁

-- SELECT FOR UPDATE
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

乐观锁

-- 版本号机制
UPDATE accounts SET balance = balance - 100, version = version + 1 
WHERE id = 1 AND version = 5;
-- 检查影响行数，如果为 0 则重试

🔒 特殊锁类型速查

锁名称	作用	触发条件	数据库
元数据锁(MDL)	保护表结构	DDL/DML 自动加锁	MySQL 5.5+
自增锁	保证 AUTO_INCREMENT 唯一性	INSERT 自增列	MySQL InnoDB
插入意向锁	INSERT 前的间隙检查	INSERT 操作	MySQL InnoDB
谓词锁	基于条件的逻辑锁	Serializable 隔离级别	PostgreSQL
键范围锁	锁定键值范围	Serializable 隔离级别	SQL Server

🎛️ 锁管理方式

方式	特点	灵活性	风险	示例
隐式锁	数据库自动管理	低	低	普通 UPDATE
显式锁	用户手动指定	高	高（可能死锁）	SELECT FOR UPDATE

📈 性能影响评估

并发度排序（从高到低）

乐观锁 > 行级锁 > 页级锁 > 表级锁 > 库级锁 > 悲观锁

开销排序（从低到高）

库级锁 < 表级锁 < 页级锁 < 行级锁 < 间隙锁 < 临键锁

🚨 常见问题速查

死锁

症状: 事务互相等待对方释放锁
解决:

• 设置超时: innodb_lock_wait_timeout
• 固定加锁顺序
• 使用 TRY...CATCH 重试

锁等待

症状: 查询长时间阻塞
排查:

-- MySQL
SELECT * FROM performance_schema.data_lock_waits;
SHOW ENGINE INNODB STATUS;

幻读

症状: 同一查询在不同时间返回不同行数
解决: 使用 REPEATABLE READ + 临键锁

活锁

症状: 事务不断重试但无法完成
解决: 引入随机退避或优先级队列

🎯 最佳实践清单

✅ 推荐做法

• [ ] 尽量使用行级锁提高并发
• [ ] 事务尽可能短小，快速释放锁
• [ ] 为频繁查询的字段添加索引，避免锁升级
• [ ] 使用乐观锁处理低冲突场景
• [ ] 设置合理的锁超时时间
• [ ] 监控和分析锁等待日志

❌ 避免陷阱

• [ ] 避免在大事务中持有锁
• [ ] 避免在事务中进行网络调用
• [ ] 避免不一致的加锁顺序
• [ ] 避免忽略死锁异常
• [ ] 避免过度使用显式锁
• [ ] 避免在循环中执行带锁的查询

📊 各数据库锁特性对比

特性	MySQL InnoDB	PostgreSQL	SQL Server	Oracle
默认锁粒度	行级	行级	行级	行级
MVCC	✅	✅	✅ (快照隔离)	✅
间隙锁	✅	❌ (用谓词锁)	✅ (范围锁)	❌
表级锁	支持	支持	支持	支持
死锁检测	✅	✅	✅	✅
锁升级	❌	❌	✅	❌
SELECT FOR UPDATE	✅	✅	✅	✅
NOWAIT	✅	✅	✅	✅
SKIP LOCKED	✅ (8.0+)	✅	✅	✅

🔍 诊断命令速查

MySQL

-- 查看锁信息 (5.7+)
SELECT * FROM performance_schema.data_locks;

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

-- InnoDB 状态
SHOW ENGINE INNODB STATUS\G

-- 当前事务
SELECT * FROM information_schema.innodb_trx;

-- 锁等待超时设置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

PostgreSQL

-- 查看所有锁
SELECT * FROM pg_locks;

-- 查看锁等待
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation = blocked_locks.relation;

-- 活动会话
SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

SQL Server

-- 查看锁
SELECT * FROM sys.dm_tran_locks;

-- 查看阻塞
EXEC sp_who2;

-- 详细锁信息
SELECT request_session_id, resource_type, resource_description,
       request_mode, request_status
FROM sys.dm_tran_locks;

Oracle

-- 查看锁
SELECT * FROM v$lock;

-- 查看锁等待
SELECT * FROM v$session_wait WHERE event LIKE 'enq%';

-- 查看持有锁的会话
SELECT s.sid, s.serial#, l.type, l.lmode, l.request
FROM v$session s, v$lock l
WHERE s.sid = l.sid;

📚 相关资源

• 完整目录 - 查看所有文档
• 使用指南 - 如何使用本知识库
• 导航地图 - 可视化知识结构

---

提示: 此速查表会随知识库更新而同步演进，建议定期查看最新版本。