无唯一索引时如何实现线程安全的存在则更新/不存在则插入？

问题描述 #

需要实现逻辑：检查某行记录是否存在（user_id、comment_type匹配且last_update在5分钟内），存在则更新last_update，不存在则创建新记录。但当前代码非线程安全，并发场景下两个线程可能同时查到无记录，导致插入重复数据。现有代码：

$row = DB::table('some_table')
    ->where('last_update', '>=', now()->subMinutes(5))
    ->where('user_id', '=', $user_id)
    ->where('comment_type', '=', $comment_type)
    ->first();

if ($row === null) {
     // record not found, create new
     DB::table('some_table')->insert([
        'user_id' => $user_id,        
        'comment_type' => $comment_type,     
        'created_at' => $created_at,     
        'last_update' => $last_update   
    ]);
} else {
     // record found, update existing
     DB::table('some_table')
          ->where('id', '=', $row->id)
          ->update(['last_update' => now()]);     
}

解决方案 #

方法一：事务+悲观锁（最贴合原有业务逻辑） #

把查询和写入操作包裹在事务中，查询时加排他锁，确保其他线程无法同时操作相关记录：

DB::transaction(function () use ($user_id, $comment_type, $created_at, $last_update) {
    // 加排他锁锁定符合条件的行，避免其他线程并发修改
    $row = DB::table('some_table')
        ->where('last_update', '>=', now()->subMinutes(5))
        ->where('user_id', '=', $user_id)
        ->where('comment_type', '=', $comment_type)
        ->lockForUpdate()
        ->first();

    if ($row === null) {
        DB::table('some_table')->insert([
            'user_id' => $user_id,
            'comment_type' => $comment_type,
            'created_at' => $created_at,
            'last_update' => $last_update
        ]);
    } else {
        DB::table('some_table')
            ->where('id', '=', $row->id)
            ->update(['last_update' => now()]);
    }
});

原理：lockForUpdate()会对查询到的行加排他锁，其他线程需等待当前事务提交才能操作这些行；事务的原子性保证了查询与写入是不可分割的整体，不会被并发打断。

方法二：唯一约束+原子Upsert（适合调整业务逻辑场景） #

如果业务允许同一个user_id+comment_type只保留一条最新记录，可通过唯一约束+Laravel的upsert实现完全原子化操作：

1. 添加复合唯一索引

ALTER TABLE some_table ADD UNIQUE INDEX idx_user_comment (user_id, comment_type);

2. 使用Upsert方法

DB::table('some_table')->upsert(
    [
        'user_id' => $user_id,
        'comment_type' => $comment_type,
        'created_at' => $created_at,
        'last_update' => now()
    ],
    ['user_id', 'comment_type'], // 触发唯一约束的字段
    ['last_update'] // 冲突时需要更新的字段
);

原理：upsert是数据库级别的原子操作，先尝试插入，若触发唯一约束则自动执行更新，彻底避免并发重复插入问题。

方法三：乐观锁（适合高并发低冲突场景） #

若不想用悲观锁影响性能，可添加版本字段实现乐观锁，通过重试机制处理冲突：

1. 添加版本字段

ALTER TABLE some_table ADD COLUMN version INT DEFAULT 1;

2. 修改业务逻辑

do {
    $row = DB::table('some_table')
        ->where('last_update', '>=', now()->subMinutes(5))
        ->where('user_id', '=', $user_id)
        ->where('comment_type', '=', $comment_type)
        ->first();

    if ($row === null) {
        try {
            DB::table('some_table')->insert([
                'user_id' => $user_id,
                'comment_type' => $comment_type,
                'created_at' => $created_at,
                'last_update' => $last_update,
                'version' => 1
            ]);
            break;
        } catch (\Illuminate\Database\QueryException $e) {
            // 捕获插入冲突，重新查询
            continue;
        }
    } else {
        // 更新时校验版本号，确保未被其他线程修改
        $updated = DB::table('some_table')
            ->where('id', '=', $row->id)
            ->where('version', '=', $row->version)
            ->update([
                'last_update' => now(),
                'version' => $row->version + 1
            ]);

        if ($updated) break;
    }
} while (true);

原理：通过版本号判断记录是否被并发修改，若冲突则重试逻辑，避免锁竞争，适合高并发场景但会有少量重试开销。

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