MySQL：批量插入多行时生成唯一团队编号并规避并发冲突

这是典型的并发竞态条件问题——当多个请求同时执行「统计现有记录数→加1→插入」这个流程时，它们会在几乎同一时间拿到相同的统计结果，最终插入重复的team_number。比如20个请求同时查询到当前有10条记录，都会用11作为团队编号插入，就会出现大量重复。

下面是几种靠谱的解决方法，按推荐程度排序：

1. 让数据库自动生成唯一的团队编号（最推荐） #

直接把team_number交给数据库维护，利用它的原子性特性彻底规避竞态问题：

• MySQL：将team_number设为自增列并添加唯一约束（双重保障）

  ALTER TABLE team_members MODIFY COLUMN team_number INT AUTO_INCREMENT UNIQUE;
  -- 插入时无需指定team_number，数据库自动分配唯一值
  INSERT INTO team_members (first_name, last_name) VALUES ('Alice', 'Smith'), ('Bob', 'Jones');
  

• PostgreSQL：使用SERIAL类型或显式序列

  -- 方式1：快速用SERIAL类型
  ALTER TABLE team_members ALTER COLUMN team_number TYPE SERIAL;
  -- 方式2：自定义序列（更灵活）
  CREATE SEQUENCE team_number_seq OWNED BY team_members.team_number;
  ALTER TABLE team_members ALTER COLUMN team_number SET DEFAULT nextval('team_number_seq');
  -- 插入逻辑同样无需指定team_number
  INSERT INTO team_members (first_name, last_name) VALUES ('Alice', 'Smith'), ('Bob', 'Jones');
  

这种方法完全由数据库处理编号唯一性，并发场景下不会出任何问题，是最省心的方案。

2. 使用事务+行级锁（适合自定义编号规则的场景） #

如果team_number不能单纯用自增（比如需要按业务规则生成），可以用事务包裹操作，并通过锁确保同一时间只有一个请求能计算编号：

BEGIN TRANSACTION;
-- 锁定最大team_number的记录，防止其他请求同时读取计算
SELECT MAX(team_number) INTO @max_team FROM team_members FOR UPDATE;
SET @new_team = IFNULL(@max_team, 0) + 1;
-- 插入第一条记录
INSERT INTO team_members (first_name, last_name, team_number) VALUES ('Alice', 'Smith', @new_team);
-- 第二条记录编号递增
INSERT INTO team_members (first_name, last_name, team_number) VALUES ('Bob', 'Jones', @new_team + 1);
COMMIT;

注意：这种方法会降低并发性能，因为锁会阻塞其他请求，适合并发量不是极高的场景。

3. 唯一约束+重试机制（乐观锁思路） #

给team_number添加唯一约束，插入时如果遇到重复键错误，就重新计算编号再尝试：

-- 先添加唯一约束
ALTER TABLE team_members ADD CONSTRAINT unique_team_number UNIQUE (team_number);

然后在应用代码里实现重试逻辑（伪代码示例）：

def insert_two_members(first_names, last_names):
    max_retries = 3
    for _ in range(max_retries):
        try:
            # 获取当前最大团队编号
            current_max = db.query("SELECT MAX(team_number) FROM team_members")[0][0] or 0
            new_team = current_max + 1
            # 批量插入两条，编号连续
            db.execute("""
                INSERT INTO team_members (first_name, last_name, team_number)
                VALUES (%s, %s, %s), (%s, %s, %s)
            """, (first_names[0], last_names[0], new_team, first_names[1], last_names[1], new_team+1))
            return
        except DuplicateKeyError:
            # 遇到重复，重试一次
            continue
    raise Exception("插入失败，重试次数耗尽")

这种方法不需要加锁，性能更好，但需要处理重试逻辑，适合并发量中等的场景。

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