事情是这样的,我有个需求,简单来说是每次insert三条记录,每次都给本次insert的记录version+1,理想情况下,假设没有并发,最后的数据应该是这样
id name version
1 name 1
2 name 1
3 name 1
4 name 2
5 name 2
6 name 2
7 name 3
8 name 3
9 name 3
然后由于并发,结果变成这样,
id name version
1 name 1
2 name 1
3 name 1
4 name 1
5 name 1
6 name 1
7 name 2
8 name 2
9 name 2
sql语句如下:
开始事务
insert into test set name=‘name’;
执行三次,存下这三次lastInsertId
select max(version) from test where name=’name';
newVersion = max version + 1
update test set version = newVersion where id in (lastInsertId1, lastInsertId2, lastInsertId3)
select max(version) from test;
提交事务
在阐述如何解决之前,
先在mysql的innodb下做了个实验,是关于insert是否锁表,形式是两个并发的进程同时开始事务,结果既有意料之中又有意料之外。
意料之中的是,mysql auto-increatement机制是简单insert语句不会锁表,没错,验证通过。
意料之外的是,说好的事务互相不可见呢,
竟然在没有commit之前互相影响了,好吧,是个坑,mark一下。
进入正题,分析下上面的sql语句,
假设insert不锁表,那也就是说,
两个事务在insert之后,
先将其中一个事务(取名事务A) select max version,最开始得到的值是0,然后+1,再update new version,最后select max version,得到值为1。
然后另一个事务(取名事务B) 也select max version,此时事务之间不可见的原理总算真理了,得到的值依然是0,不受事务A影响。也就是说,此时事务B如何和事务A一样,在NULL的基础上version+1,最后事务B插入的三条数据version也是B,脏数据就应运而生了。
本来打算使用redis计数器来解决这个问题,不过后来想想,内部矛盾内部解决,借助第三者终非正途。灵光一闪,想到innodb的多版本并发控制,主要手段是借助附加的隐藏version字段。
同样的思路,我也加个版本控制字段,比如control_version(值为当前最大自增id),另外name必须加索引,下面会用到行锁,不加索引导致表锁,会影响到其它不相关的insert操作。
然后sql语句如下,
开始事务
insert into test set name=‘name’;
执行三次,存下这三次的lastInsertId
select max(version),max(control_version) from test where name=’name’;
newVersion = max version + 1
update test set version = newVersion where id in (lastInsertId1, lastInsertId2, lastInsertId3) and control_version = max_control_version and name=’name';
update test set control_version= lastInsertId3 where name=’name’;
select max(version) from test;
提交事务
以上事务并发会导致在第三句update test set version = newVersion … where … name=’name’ 阻塞,此时有2种情况
1)因为其他事务insert导致本事务抛出锁超时异常,或其他事务也执行到第三局update而抛出死锁异常,一旦抓到异常就rollback
2)阻塞在有效时间内得到release锁(之前拥有锁的事务commit或rollback),但由于可重复读,即使其他事务已经commit更新了最大version值,本事务拿到的max version依然是旧version。此时control_version就起作用了,本事务commit后,第三句update会找不到满足条件的记录,因此 id in (lastInsertId1, lastInsertId2, lastInsertId3) 的version为空,此处可根据该线索写删除逻辑或者重复红色部分sql事务直至成功为止。