带唯一性索引约束的upsert_all执行冲突问题求助

问题原因 #

批量更新wave_order时，upsert_all按数组顺序逐行处理变更。当尝试将id1的wave_order从0改为1、id2从1改为2时，先处理id1会导致其新值1与id2当前的1冲突——数据库的唯一约束是实时检查每一行的变更，而非等所有更新完成后再验证。

除了你提到的三种方案，还有以下可行方案：

方案4：调整更新顺序（从大到小更新wave_order） #

将更新数据按新wave_order的值降序排列，先更新wave_order更大的记录，释放出小的数值后再更新其他记录，避免中间冲突：

update_data = [
  {id: 2, name: "Wave2", wave_order: 2, wavable_id: my_object.id, wavable_class: my_object.class.name},
  {id: 1, name: "Wave1", wave_order: 1, wavable_id: my_object.id, wavable_class: my_object.class.name}
]
InvitationWave.upsert_all(update_data, unique_by: :id, update_only: :wave_order, record_timestamps: true)

优势：无需修改数据库或写原生SQL，实现成本低，适合简单的批量更新场景。

方案5：使用原生SQL的UPDATE ... FROM语句 #

直接通过PostgreSQL的原生批量更新语句，利用数据库的语句级快照隔离特性，一次性完成所有更新，不会触发中间约束冲突：

sql = <<~SQL
  UPDATE invitation_waves
  SET wave_order = updates.new_wave_order, updated_at = NOW()
  FROM (
    VALUES
      (1, 1),
      (2, 2)
  ) AS updates(id, new_wave_order)
  WHERE invitation_waves.id = updates.id;
SQL
ActiveRecord::Base.connection.execute(sql)

优势：性能最优，完全绕过Rails ORM的限制，适合复杂或大规模的批量更新。

方案6：事务内临时偏移wave_order #

用事务包裹两次操作：先给目标记录的wave_order加一个足够大的临时偏移量（确保不与现有值冲突），再设置正确的目标值：

ActiveRecord::Base.transaction do
  # 临时偏移，避免冲突
  InvitationWave.where(id: [1, 2]).update_all("wave_order = wave_order + 1000")
  # 执行目标更新
  update_data = [
    {id: 1, name: "Wave1", wave_order: 1, wavable_id: my_object.id, wavable_class: my_object.class.name},
    {id: 2, name: "Wave2", wave_order: 2, wavable_id: my_object.id, wavable_class: my_object.class.name}
  ]
  InvitationWave.upsert_all(update_data, unique_by: :id, update_only: :wave_order, record_timestamps: true)
end

优势：无需调整更新顺序，适合无法预测更新值顺序的场景，事务保证原子性，失败则回滚。

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