事务中锁的获取时机探讨：逐条SQL获取还是预先获取全部？

问题背景 #

我们能否认为锁是在执行SQL语句时才获取，还是事务会预先获取全部所需锁？很多人认为为避免死锁，需预先获取事务全程所需的所有预期锁，由此产生了上述疑问。

测试表结构 #

animals表 #

+----------+-------+
| name     | value |
+----------+-------+
| Aardvark |    10 |
+----------+-------+

birds表 #

+---------+-------+
| name    | value |
+---------+-------+
| Buzzard |    20 |
+---------+-------+

会话执行流程 #

会话1 #

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT value FROM Animals WHERE name='Aardvark' FOR SHARE;
+-------+
| value |
+-------+
|    10 |
+-------+
1 row in set (0.00 sec)

会话2 #

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT value FROM Birds WHERE name='Buzzard' FOR SHARE;
+-------+
| value |
+-------+
|    20 |
+-------+
1 row in set (0.00 sec)

--等待获取锁
mysql> UPDATE Animals SET value=30 WHERE name='Aardvark';

会话1后续操作 #

mysql> UPDATE Birds SET value=40 WHERE name='Buzzard';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

核心结论 #

从上述案例可以明确：锁是在执行SQL语句时才逐步获取，而非事务启动时预先获取全部所需锁。

具体拆解：

• 会话1执行SELECT ... FOR SHARE时，仅获取animals表中Aardvark行的共享锁，此时未涉及birds表的锁操作；
• 会话2先获取birds表中Buzzard行的共享锁，之后执行UPDATE尝试获取animals表Aardvark行的排他锁，因该锁被会话1持有，进入等待状态；
• 会话1执行UPDATE尝试获取birds表Buzzard行的排他锁，此时两个会话互相持有对方需要的锁，形成循环等待，触发死锁错误。

所谓"预先获取全部预期锁"是一种死锁规避的人工策略，并非数据库默认的锁获取逻辑。数据库无法提前预判事务后续所有SQL的锁需求，只能在每条SQL执行时，根据语句逻辑申请对应锁，这也是死锁可能发生的核心原因之一。

内容的提问来源于stack exchange，提问作者user19551894