MySQL select...for update的理解

      最近有在面试中有被问到，对MySQL中select…for update的理解，之前有简单用过，但理解不深，今天来系统梳理一下。

为什么需要for update

      我们知道保证程序并发读写过程中会用到锁，比如Synchronized、ReentrantLock等，那同样的，在数据库的并发读写过程中，也会涉及到锁，而这个for update，就是加一把锁，也叫排它锁。它能保证在两个事务对同一份数据进行进行并发修改时的同步性，在一个事物对查询数据加了for update的时候，另一个事务对所选数据只能进行查询，而不能进行修改或删除，总结来说就是：

• 范围锁定：它会锁定被select出来的所有行记录。如果查询条件导致多个行被选中，这些行都会被锁定。
• 锁定等待：当一个事务已经锁定了某些行，其他事务尝试锁定相同的行时，会进入等待状态，直到锁被释放即获取锁的事务提交或回滚。当然，这会导致性能消耗。
• 举个例子，最直接的转账操作：
  • START TRANSACTION;
  • 锁定发送账户的余额：SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
  • 锁定接收账户的余额：SELECT balance FROM accounts WHERE account_id = 2 FOR UPDATE;
  • 扣除发送账户的金额：UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
  • 增加接收账户的金额：UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
  • COMMIT;·

原理

      for update加的排他锁，在MySQL InnoDB底层，会根据不同场景氛围不同类型：

• 行锁（Record Lock）：InnoDB 会对表中所有行加上行锁，举个例子：
  • SELECT * FROM accounts FOR UPDATE;
  • 如果 accounts 表中有 1000 行，且没有 WHERE 条件，MySQL 会锁住表中所有 1000 行。
    即使某些行没有被实际修改，它们仍然会被锁住，直到事务提交或回滚。
• 间隙锁（Gap Lock）：在RR隔离级别下，InnoDB 可能会对索引间隙加锁，防止其他事务插入新行，这也是避免幻读的一种方式：
  • 当查询条件是范围查询（如 <、>、BETWEEN、LIKE 等）或者精确查询但对应数据不存在的时候，MySQL 会锁定范围内的所有行以及范围之间的间隙，举个例子，假设数据库中有id为1，5，10的三条记录，在事务中执行以下sql：SELECT * FROM users WHERE id = 7 FOR UPDATE;
    • 查询条件是 id = 7，但表中没有 id = 7 的记录。
    • MySQL 会加一个间隙锁，锁住 (5, 10) 的间隙，防止其他事务在这个范围内插入新行。
  • 如果查询条件是唯一索引的精确匹配（如 =）且对应数据存在，则不会加间隙锁，如查id=5。

注意点

• 必须在事务中使用：for update只能在事务中使用。如果不在事务中使用，锁定的行会在查询完成后立即释放，相当于没锁。
• 索引的影响：如果查询使用了索引，锁的范围会更小，只锁住符合条件的行。
  如果查询没有使用索引（如全表扫描），MySQL 会锁住表中所有行，行锁升级为表锁，影响并发性能。
• 死锁风险：当多个事务相互等待对方释放锁时，会产生死锁。比如事务A查了一部分数据dataA，并且加了for update，但是要修改另一部分数据dataB，而这另一部分数据dataB被事务B查询了，同样加了for update，B也需要修改事务A的数据dataA，这就导致了循环等待问题，产生了死锁。这就需要我们在应用层面设计好锁的获取顺序，尽量避免死锁的发生。