数据库并发下的脏数据问题

事情是这样的，我有个需求，简单来说是每次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事务直至成功为止。