mysql记录不存在插入存在修改

引发

因为项目中使用select for update引发的死锁问题。

select for update为什么会出现死锁

select for update语句是当前读。会有加锁操作。

mysql加锁规则：

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。只有记录存在时才会优化，不存在时会按普通索引处理。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

next-key lock 是 间隙锁（Gap lock） + 行锁(record lock)

行锁与行锁之间有冲突关系，比如加了id=1的行写锁，别一个想加行写锁时就需要阻塞。

间隙锁之间没有冲突关系，比如加了[x,-supernum] ，别一个也可以加。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。

按照以上描述, 程序实现了以下逻辑：

begin;
select * from t where id=N for update;

/*如果行不存在*/
insert into t values(N,N,N);
/*如果行存在*/
update t set d=N set id=N;

commit;

当where 条件里是 id = n 或者 普通索引的d=n 记录不存在时，按照优化1 和 优化2都会退化为间隙锁。因为间隙锁特性所以select for update都可以成功，但是insert时就会出现死锁。

如何解决记录不存在插入存在修改这个逻辑

两个方向：

​ 1.mysql

​ 2.程序代码

Mysql:

方法1：

1.试着插入记录

insert into sys_a(name, age, code) 

-- 默认值
select 'test1', 12, 'm6'from dual 
-- 原来的值
where not exists (select name, age, code from sys_a where code = 'm6');

2.一直执行修改

update sys_a set age = age + 1 where code = 'm6'

缺点：

​ 要写两条语句。

​ 每次都执行两条语句

方法2：replace into

replace into sys_a(name, age, code) values('test1', 20, 'm8')

replace into相当于，先检测该记录是否存在(根据表上的唯一键)，如果存在，先delete，然后再insert。 这个方法有一个很大的问题，如果记录存在，每次执行完，主键自增id就变了（相当于重新insert了一条），对于有复杂关联的业务场景，如果主表的id变了，其它子表没做好同步，会死得很难看。– 不建议使用该方法！

方法3：on duplicate key

insert into sys_a(name, age, code)  values('test1', 12, 'm6') 
on duplicate key 
update age = age + 1

insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

注意，如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。

需要注意的是，执行这条语句的 affected rows 返回的是 2，很容易造成误解。实际上，真正更新的只有一行，只是在代码实现上，insert 和 update 都认为自己成功了，update 计数加了 1， insert 计数也加了 1。

其中方法2与方法3都需要惟一索引。

程序代码上：

使用java的同步锁。

使用三方法的分布式锁。