使用InnoDB引擎与REPEATABLE READ事务，需加锁保障任务执行顺序吗？

结论 #

这种场景必须加锁，否则无法保证「先插入新记录、再更新所有行queue值」的业务顺序，会导致逻辑错误。

为什么默认逻辑不生效 #

在InnoDB的REPEATABLE READ隔离级别下：

• 第一个任务的SELECT MAX(queue)是快照读，不会对任何行加锁，仅读取事务启动时的快照数据；
• 第二个任务的UPDATE job SET queue = queue - 1是当前读，会对表中所有行加排他锁，但两个事务的执行顺序没有约束——完全可能出现第二个任务先完成全表更新，第一个任务才插入新记录的情况，此时新插入的记录queue值是旧的max+1，不会被第二个任务的UPDATE影响，不符合业务要求。

正确的锁方案 #

给第一个任务的查询语句加上排他锁，使用SELECT ... FOR UPDATE语法，强制第二个任务的UPDATE等待第一个任务完成。

修改后的第一个任务代码 #

import uuid
import MySQLdb

db = MySQLdb.connect("conn_info")
cursor = db.cursor()
# 加排他锁，确保后续UPDATE必须等待当前事务提交
cursor.execute("SELECT max(queue) from job FOR UPDATE")
max_queue = cursor.fetchone()[0]
# 处理表为空时max_queue为NULL的情况
max_queue = max_queue if max_queue is not None else 0
job_id, queue = str(uuid.uuid1()), max_queue + 1
cursor.execute("INSERT INTO job VALUES(%s,%s)", (job_id, queue))
db.commit()
db.close()

原理说明 #

SELECT ... FOR UPDATE会触发当前读，InnoDB会对查询涉及的所有行（此处为全表，因为MAX需要扫描所有行）加排他锁。此时第二个任务的UPDATE操作会因无法获取排他锁进入等待队列，直到第一个事务提交（插入操作完成），第二个事务才能继续执行全表更新，完美保证了业务要求的执行顺序。

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