MySQL InnoDB并发更新插入时的锁选型及事务配合疑问

问题解答 #

1. 应使用何种锁？

推荐两种方案：

• 原子SQL替代锁：将查询最大queue和插入操作合并成一条原子SQL，利用InnoDB的事务特性保证操作原子性，避免竞态问题：

  INSERT INTO job (job_id, queue) VALUES (UUID(), (SELECT MAX(queue) + 1 FROM job));
  

• 悲观锁（行级锁）：在查询最大queue时加FOR UPDATE锁，锁住表中所有行（因为MAX(queue)需要扫描全表），确保后续插入操作前没有其他线程修改数据，SQL如下：

  SELECT MAX(queue) FROM job FOR UPDATE;
  

  这种方式要确保锁的范围覆盖查询到插入的全流程。

2. 使用锁时是否必须同时使用事务？

是的，必须结合事务。InnoDB的锁依赖事务生命周期，锁的释放时机是事务提交或回滚时。如果没有事务包裹，SELECT ... FOR UPDATE执行完成后锁会立即释放，无法保证后续插入操作的原子性——比如锁释放后，update_func可能修改了queue值，导致插入时用了旧的最大值。只有把查询和插入放在同一个事务里，锁才会保持到事务提交，确保中间不会有其他线程篡改数据。

另外补充：你的代码存在线程安全问题，全局的conn和cursor被多线程共享，会导致资源竞争，每个线程应该创建独立的数据库连接和游标。

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