MySql--锁

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 0 本文字数： 2.4k 阅读时长 ≈ 9 分钟

摘要

常见面试题

数据库锁

  • 从性能上分为乐观锁(用版本对比来实现)和悲观锁

  • 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)

读锁（共享锁，S锁[Shared]）：针对同一份数据，多个读操作可以同时进行而不会互相影响。读锁可以认为没有加锁，可读但不可写，当写锁锁住数据时，读锁也会不可获取。
写锁（排它锁，X锁[eXclusive]）：当前写操作没有完成前，它会阻断其他写锁和读锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 给表加读锁
# 当前session和其他session都可以读该表,当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待
mysql> lock table actor read;

# 给表加写锁
# 当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞
mysql> lock table actor write;

# 查看哪些表上加了锁
mysql> show open tables;
# 查看指定表
# In_use=1表示被加了写锁
mysql> show open tables like 'actor';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | actor |      1 |           0 |
+----------+-------+--------+-------------+

# 解锁
mysql> unlock tables;

对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

  • 从对数据操作的粒度分，分为表锁和行锁

1
2
# 行锁for update，这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交
select * from actor where id = 10 for update;

注意行锁的查询条件必须走索引，否则会升级为表锁
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

  • 行锁分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度（等待总时长）
Innodb_row_lock_time_avg: 每次等待所花平均时间（等待平均时长）
Innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数（等待总次数）

  • MyISAM不支持事务且只支持表锁，在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。

  • InnoDB支持事务和行锁，在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

  • 读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

  • 间隙锁(Gap Lock): 锁的就是两个值之间的空隙，用于解决幻读。间隙锁是在可重复读隔离级别下才会生效。

如account表的主键id是不连续的（1，2，3，10，20），那么间隙就有id为 (3,10)，(10,20)，(20,正无穷) 这三个区间
在Session_1下面执行 update account set age = 10 where id > 8 and id <18;
则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在 (3,20]区间都无法修改数据，注意最后那个20也是包含在内的。
注意这里锁住的最后一条记录不是id=18，而是18所在的间隙区间都会锁住。
尽可能减少检索条件范围，避免间隙锁

  • 临键锁(Next-key Locks): 是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。可重复读里当前读靠临键锁防幻读，和 MVCC 的关系见 MySql--MVCC。

死锁如何排查

先分清两件事，面试和线上都容易混：

死锁 Deadlock 锁等待超时 Lock wait
现象 两个（或多个）事务互相等对方已经持有的锁，形成环 一方拿着锁不放，另一方干等
InnoDB 怎么处理 立刻选一个事务回滚，打破环 等到 innodb_lock_wait_timeout（默认 50 秒，见 MySql单节点、主从、双主的构建方法）后抛错
错误码 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction ERROR 1205 (HY000): Lock wait timeout exceeded
现场还在不在 通常已经没了，被回滚的那个事务锁已释放 还能在 PROCESSLIST / data_lock_waits 里抓到阻塞链

死锁检测默认开着（innodb_deadlock_detect=ON），靠等待图找环。高并发下检测本身也吃 CPU，极少数场景会关掉它、改用超时兜底，一般不要关。

1. 把现场留下来

死锁发生时 InnoDB 已经自动回滚了其中一个事务，SHOW PROCESSLIST 经常什么都看不到。排查靠的是日志，不是盯着当前线程。

1
2
3
4
5
-- 最近一次死锁的完整报告，重点看 LATEST DETECTED DEADLOCK
SHOW ENGINE INNODB STATUS\G

-- 只留最近一次。线上打开后，每次死锁都打到 error log
SET GLOBAL innodb_print_all_deadlocks = ON;

innodb_print_all_deadlocks 建议写进配置持久化。只靠 INNODB STATUS 会被下一次死锁覆盖。

正在发生的锁等待（还没形成环、或已经超时前）用 8.0 的 performance_schema，老的 information_schema.INNODB_LOCKS 在 8.0 里删了：

1
2
3
4
5
6
7
8
9
10
11
12
-- 谁持有锁、谁在等
SELECT * FROM sys.innodb_lock_waits\G

SELECT ENGINE_TRANSACTION_ID, THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME,
       INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
ORDER BY ENGINE_TRANSACTION_ID;

SELECT * FROM performance_schema.data_lock_waits;

-- 长事务、未提交的 for update，见线程篇
SHOW FULL PROCESSLIST;

线程状态、kill 长事务见 MySql--线程管理。Lock_time 很高的慢 SQL 见 MySql--慢查询。

2. 怎么读 LATEST DETECTED DEADLOCK

报告里通常有两个 TRANSACTION，按下面几行读就够：

  • *** (1) TRANSACTION / *** (2) TRANSACTION：两个事务各自正在执行的 SQL、已持有的锁

  • WAITING FOR THIS LOCK TO BE GRANTED：当前这条语句在等哪一把锁

  • HOLDS THE LOCK(S)：它已经拿着、导致对方也在等的锁

  • lock mode：X 排它、S 共享；后面跟 rec but not gap（纯行锁）、gap（间隙）、insert intention（插入意向）、不带修饰则是临键锁

  • index PRIMARY of table ...：锁在哪个索引上。锁在二级索引还是主键，决定交叉顺序

  • *** WE ROLL BACK TRANSACTION (1)：牺牲者。InnoDB 一般回滚 undo 更少、回滚更便宜的那个

典型环可以画成：事务 A 持有行 1 的 X 锁，在等行 2；事务 B 持有行 2 的 X 锁，在等行 1。把两边 SQL 和 LOCK_DATA（主键值）对上，就能在代码里复现。

3. 常见成因

  • 两个事务更新同一批行，加锁顺序相反。最经典：A 先改 id=1 再改 id=2，B 先 2 后 1。

  • 唯一索引上的并发 INSERT：一个事务插入失败或等间隙，另一个插入意向锁和间隙锁互相等。RR 下更明显。

  • where 没走索引，行锁升级成表锁/锁住大量间隙，冲突面被放大。索引失效场景见 MySql索引。

  • 事务太大、持锁时间太长：中间还打 RPC、循环里一条条 SELECT ... FOR UPDATE。

  • 外键、级联更新把锁扩到父表/子表，表面上在改 A，实际还锁了 B。

4. 怎么处理

  • 应用必须重试 (1213)。错误信息里已经写了 try restarting transaction，死锁被解开后重跑一遍通常就能成功。这是正规解法，不是权宜之计。

  • 业务里约定相同的加锁顺序，例如一律按主键从小到大 FOR UPDATE。

  • 缩小事务：能查的先查，锁语句放最后，尽快 COMMIT；不要在持锁时做远程调用。

  • 让检索走索引，锁住真正要改的那几行，避免 RR 下大范围间隙锁。幻读不敏感的业务可以把隔离级别降到 RC，少掉间隙锁，见 MySql--事务。

  • 热点行（库存、计数器）考虑排队、合并更新，而不是靠无限加大超时。

  • 锁等待（1205）才去 kill 阻塞会话；死锁已经被引擎回滚了，再 kill 没有现场。

MySql死锁问题如何排查？

  • 先分清：死锁是环，InnoDB 立刻回滚一个事务，报 1213；锁等待是单边阻塞，等到 innodb_lock_wait_timeout 报 1205。死锁发生后现场通常没了，不要只靠 PROCESSLIST。
  • 留证据：SHOW ENGINE INNODB STATUS 看 LATEST DETECTED DEADLOCK；线上打开 innodb_print_all_deadlocks 打到 error log。正在等锁用 sys.innodb_lock_waits / performance_schema.data_locks（8.0 已无 INNODB_LOCKS）。
  • 读报告：两个事务各自 HOLD 和 WAIT 的锁模式、索引、LOCK_DATA，再看 WE ROLL BACK TRANSACTION 是哪一方。
  • 成因多半是加锁顺序交叉、唯一键并发插入、没走索引锁范围过大、事务过长。
  • 处理：应用捕获 1213 后重试；统一加锁顺序；缩小事务和锁范围；必要时 RR 改 RC。不要指望靠加大超时消灭死锁。