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, 因此能获得更好的并发性能。