mysql并发场景下如何避免插入重复行

文章目录

        场景
        问题
        解决方案
            LOCK TABLE
            INSERT IGNORE

场景

    先查询，不存在则插入，存在则更新
    更新需依赖于之前的记录并做复杂的计算，无法使用 “INSERT … ON DUPLICATE KEY UPDATE” 解决

问题

两个请求 A 和 B 如果同时到来，那么可能出现以下序列：

    A select 记录不存在
    B select 记录不存在
    A 插入
    B 插入

从而出现重复插入的情况。
解决方案
LOCK TABLE

这种方式会锁全表，导致并发性能下降，此外，如果在 LOCK TABLE 和 UNLOCK TABLE 之间有异常，会导致锁无法释放，因此，暂时不考虑这种方案。
INSERT IGNORE

设置查询字段为 UNIQUE KEY, INSERT 时增加 IGNORE, 当重复插入时，不返回错误，但 affected_rows 为 0.

具体实现为：
 

local ok, task = CheckTask(params)
if not ok then
    ngx.log(ngx.ERR, task)
    utils.response(-1, task)
end

local task_id, err = CreateOrUpdateTask(params, task)
if not task_id then
    ngx.log(ngx.ERR, "create or update task failed: " .. err)
    utils.response(-1, err)
end

function CheckTask(params)
    local sql = "select * from t_task where CenterTaskID=" ..
        ngx.quote_sql_str(params.CenterTaskID)
    local res, err = db.select(sql)
    if not res then
        ngx.log(ngx.ERR, err)
        return false, "database error"
    end
    
    local task
    if #res > 0 then
        -- some check stuff
    end
    
    return true, task
end

function CreateOrUpdateTask(params, task)
    local sql
    if task then
        -- update sql
        sql = "update t_task ..."
    else
        -- insert sql
        sql = "insert ignore into t_task ..."
    end

    local res, err = db.query_once(sql)
    if not res then
        ngx.log(ngx.ERR, err)
        return nil, "database error"
    end
    
    if not task and res.affected_rows == 0 then
        ngx.log(ngx.NOTICE, "duplicate insert: " .. sql)
        local ok, task = CheckTask(params)
        if not ok then
            ngx.log(ngx.ERR, task)
            return nil, task
        end
        
        return CreateOrUpdateTask(params, task)
    end

    if task then
        return task.TaskID
    else
        return res["insert_id"]
    end
end

即当 INSERT 的 affected_rows 为 0 时重新执行一次 SELECT + INSERT/UPDATE 的操作。

整个过程都没有显式加锁，并且每条语句都是 autocommit, 因此能获得更好的并发性能。