SQL 数据库锁超清晰总结：行锁、表锁、意向锁、乐观锁、悲观锁完整讲解

一、锁的核心作用

多线程 / 多请求同时读写同一条数据时，会出现脏读、不可重复读、幻读、数据覆盖等并发问题。 锁本质是资源竞争控制器，通过加锁限制同时修改，保证数据一致性。

二、两大存储引擎锁差异（MyISAM vs InnoDB）

1.MyISAM

1. 只支持表级锁，无行锁、不支持事务；
2. 读共享、写互斥：读的时候可以同时读，写的时候阻塞所有读写；
3. 并发写入性能极差，不适合商城、会员投稿、订单系统；
4. 优点：结构简单，查询速度快。

2.InnoDB（项目主流，MySQL 默认）

1. 支持事务、MVCC、行锁、表锁、意向锁；
2. 读写分离：普通读不加锁，写加行锁，并发性能高；
3. 支持事务隔离级别，解决大部分并发异常；
4. 唯一缺点：锁机制复杂，容易出现死锁、行锁升级表锁。

三、按锁定粒度划分：表锁、行锁、页锁

1. 表级锁（锁住整张数据表）

特点

1. 粒度最大，锁定整张表，所有行无法写入；
2. 开销小，加锁快，无死锁；
3. 并发差，写操作阻塞全部其他请求。

两种表锁

• 共享读锁（S 锁）：多客户端可同时加读锁，不能写；
• 排他写锁（X 锁）：加写锁后，其他客户端读写全部阻塞。

手动加锁 SQL

sql

-- 加共享读锁
LOCK TABLES `table` READ;
-- 加排他写锁
LOCK TABLES `table` WRITE;
-- 释放锁
UNLOCK TABLES;

触发场景

MyISAM 自动使用；InnoDB 无索引更新 / 删除时，行锁升级为表锁。

2. 行级锁（InnoDB 专属，只锁单行记录）

特点

1. 粒度最小，只锁定操作的单行，其他行正常读写；
2. 并发性能极高，适合高并发订单、投稿、库存；
3. 加锁慢、开销大，会产生死锁。

分类

1. 共享行锁 (S)：SELECT ... LOCK IN SHARE MODE；
2. 排他行锁 (X)：UPDATE / DELETE / INSERT 自动加。

关键前提：索引

只有通过索引条件检索数据，InnoDB 才会使用行锁；无索引直接升级表锁。

行锁 SQL 示例

sql

-- 手动加共享行锁，其他事务可读不可改
SELECT * FROM `ecms_news` WHERE id=10 LOCK IN SHARE MODE;

-- 自动加排他行锁，其他事务读写阻塞
UPDATE `ecms_news` SET title='测试' WHERE id=10;

3. 页锁（BDB 引擎，几乎不用）

锁定一页多行，介于行锁和表锁中间，日常开发忽略。

四、意向锁（InnoDB 表锁辅助锁，解决表行锁冲突）

作用

表锁和行锁共存时快速判断冲突，避免逐行遍历检测锁。

1. 意向共享锁 IS：事务加行 S 锁前，先给表加 IS；
2. 意向排他锁 IX：事务加行 X 锁前，先给表加 IX。

冲突规则

1. IS、IX 之间互相兼容；
2. 表 S 锁 与 IX 互斥；
3. 表 X 锁 与 IS/IX 全部互斥。

五、按并发思想划分：悲观锁 & 乐观锁

1. 悲观锁（默认认为一定会并发争抢）

原理

操作数据前直接上锁，全程独占资源，其他请求阻塞等待。 底层依赖 InnoDB 行锁 / 表锁，适用于高争抢场景：库存扣减、订单创建、会员余额变动。

实现方式

1. 手动锁行：SELECT ... LOCK IN SHARE MODE
2. 更新自动排他锁：UPDATE、DELETE

2. 乐观锁（默认认为并发冲突很少）

原理

全程不加锁，提交更新时校验版本，冲突则重试。 无阻塞、无死锁，并发量大、争抢少场景首选（资讯、文章浏览）。

两种实现方案

1. 版本号机制（最常用） 增加 version 字段，更新时判断版本一致才修改，版本自增：

sql

UPDATE goods SET stock=stock-1,version=version+1 
WHERE id=1 AND version=1;

2. 时间戳机制 利用更新时间判断是否被其他事务修改。

优缺点

优点：无锁等待、性能高； 缺点：高并发争抢下大量更新失败，需要业务重试逻辑。

六、MVCC 多版本并发控制（快照读，不加锁）

InnoDB 实现无锁读的核心机制，和行锁搭配使用：

1. 当前读：SELECT ... LOCK IN SHARE MODE / UPDATE，加行锁；
2. 快照读：普通 SELECT，读取历史快照，不加任何锁，互不阻塞。 日常网站查询文章、列表都是快照读，不占用锁资源。

七、死锁产生条件与解决

死锁四大必要条件（同时满足才会死锁）

1. 互斥：资源同一时间只能一个事务持有；
2. 持有并等待：事务持有锁，同时请求其他事务锁；
3. 不可剥夺：锁不能被强制释放；
4. 循环等待：事务之间循环占用对方资源。

死锁解决方案

1. 统一 SQL 操作顺序，所有事务更新表 / 行顺序一致；
2. 缩短事务执行时间，避免长事务；
3. 降低隔离级别，使用 RC 读已提交；
4. 开启死锁检测，超时自动回滚；
5. 业务层使用乐观锁替代悲观锁。

八、建站开发高频踩坑总结（帝国 CMS/WordPress 适用）

1. 更新条件无索引 → 行锁升级表锁，全站写入卡死；
2. 长事务循环更新多条数据，极易触发死锁；
3. 商城库存直接 UPDATE 不加版本号，并发超卖；
4. MyISAM 表大量投稿同时提交，写入阻塞；
5. 后台批量修改文章不带主键索引，锁整张新闻表。

九、快速区分对照表

表格

锁类型	粒度	并发性能	是否死锁	适用场景
表锁	整张表	差	无	少量静态数据、MyISAM
行锁	单行	极高	会	订单、库存、会员数据
悲观锁	依赖行 / 表锁	阻塞等待	会	高争抢业务
乐观锁	无锁	高	无	资讯、文章、低争抢查询
意向锁	表辅助锁	无影响	无	InnoDB 内部机制

十、总结

1. 项目统一使用 InnoDB，抛弃 MyISAM 应对并发；
2. 更新语句必须命中索引，防止行锁升级表锁；
3. 低争抢资讯站点优先乐观锁；商城、余额、库存用悲观锁；
4. 规范事务顺序、缩短事务时长，规避死锁；
5. 普通查询走快照读，不加锁，提升站点并发承载。

本文为aliz.cn原创数据库开发教程，转载请保留原文链接。

本文配套模板、静态源码可前往艾立兹素材库alisucai.com下载