如何将PostgreSQL的FOR NO KEY UPDATE转为MySQL语法以避免锁冲突？

问题场景 #

我需要将这条PostgreSQL查询转换为MySQL语法：

SELECT * FROM accounts WHERE id = 1 LIMIT 1 FOR NO KEY UPDATE;

由于表存在外键约束，我需要明确告知MySQL：后续更新操作不会修改主键id，仅修改其他列。当前MySQL下，事务1执行加锁查询后，事务2执行UPDATE accounts SET balance = ? WHERE id = ?时会被阻塞甚至触发死锁，但PostgreSQL的FOR NO KEY UPDATE可以避免这类问题，不清楚MySQL的对应实现方式。

表结构 #

CREATE TABLE `accounts` (
      `id` bigint PRIMARY KEY AUTO_INCREMENT,
      `owner` varchar(255) NOT NULL,
      `balance` bigint NOT NULL,
      `currency` varchar(255) NOT NULL,
      `created_at` timestamp NOT NULL DEFAULT (now())
    );

CREATE TABLE `entries` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `account_id` bigint NOT NULL,
  `amount` bigint NOT NULL COMMENT 'can be negative or positive',
  `created_at` timestamp NOT NULL DEFAULT (now())
);

CREATE TABLE `transfers` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `from_account_id` bigint NOT NULL,
  `to_account_id` bigint NOT NULL,
  `amount` bigint NOT NULL COMMENT 'must be positive',
  `created_at` timestamp NOT NULL DEFAULT (now())
);

CREATE INDEX `accounts_index_0` ON `accounts` (`owner`);

CREATE INDEX `entries_index_1` ON `entries` (`account_id`);

CREATE INDEX `transfers_index_2` ON `transfers` (`from_account_id`);

CREATE INDEX `transfers_index_3` ON `transfers` (`to_account_id`);

CREATE INDEX `transfers_index_4` ON `transfers` (`from_account_id`, `to_account_id`);

ALTER TABLE `entries` ADD FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`);

ALTER TABLE `transfers` ADD FOREIGN KEY (`from_account_id`) REFERENCES `accounts` (`id`);

ALTER TABLE `transfers` ADD FOREIGN KEY (`to_account_id`) REFERENCES `accounts` (`id`);

解决方案 #

核心原理与替代方案 #

MySQL InnoDB 没有直接对应 PostgreSQL FOR NO KEY UPDATE 的语法，但可以通过以下思路解决锁冲突和死锁问题：

1. 基础行锁替代
   直接使用SELECT ... FOR UPDATE，确保后续仅更新非主键列：

   SELECT * FROM accounts WHERE id = 1 LIMIT 1 FOR UPDATE;
   

   InnoDB 会自动识别后续操作是否修改主键，仅对目标行加行级排他锁，不会因外键约束扩大锁范围。只要你的更新操作不涉及主键id，就不会触发额外的外键保护锁。

2. 解决死锁的关键：统一事务锁顺序
   你提到的死锁问题，大概率是多事务交叉获取不同行的锁导致（比如事务1先锁id=1再锁id=2，事务2先锁id=2再锁id=1）。解决办法是强制所有事务按相同顺序获取行锁，比如统一先锁id更小的行，再锁id更大的行，从根源避免死锁。

3. 弱锁场景的替代（非必要）
   如果确实需要更宽松的锁（允许其他事务读取并加共享锁），可以使用LOCK IN SHARE MODE，但注意这种锁下其他事务无法修改行，仅适用于只读或共享读场景：

   SELECT * FROM accounts WHERE id = 1 LIMIT 1 LOCK IN SHARE MODE;
   

额外说明 #

• InnoDB 的行锁机制依赖索引，你的查询通过主键id精确匹配，会触发记录锁而非表锁或间隙锁，无需担心锁范围过大。
• 外键约束仅在修改主键或删除行时触发一致性检查，更新非主键列不会触发额外锁。

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