最近的项目中遇到一项问题，并发更新某一单据的时候，出现了更新失效的情况。比如：

@Transactional(rollbackFor = Exception.class)public void update(Integer id){//1.按id查询//2.更新某一字段的值}

生成的SQL大概是这样的：

UPDATEtable SET field = #{field,jdbcType=INTEGER}WHERE id= 1

那么以上代码产生的问题就是：
对于同一个id=1来说，请求A与请求B都进到了update方法中，此时按id查询得到的信息是相同的，那么请求A更新了id=1的这条记录之后，此时，请求B又对id=1的记录进行更新，此时注意请求B更新id=1的记录的时候，请求B按id查询到的数据已经是旧数据了，请求B执行完之后，请求A的更新被请求B覆盖了。

如果更新的是状态，那倒无所谓，如果库存量呢？
如果更新的是库存，是在这条记录的原始值上进行+或者-操作，是不是就出问题了？

事后写了个小demo来复现问题，代码大致如下：

需求： 某个分类每被访问一次，排序就+1；
开启两个客户端同时访问

@Transactional(rollbackFor = Exception.class)public void updateStatusTest(Integer id) {System.out.println("start:"+Thread.currentThread().getName());Category category = categoryMapper.selectById(id);try {Thread.sleep(8000);} catch (InterruptedException e) {e.printStackTrace();}System.out.println(Thread.currentThread().getName()+":"+category);category.setSort(category.getSort()+1);categoryMapper.updateById(category);category = categoryMapper.selectById(id);System.out.println(Thread.currentThread().getName()+":"+category);System.out.println("end:"+Thread.currentThread().getName());}

DAO层使用的mybatis，生成日志大致如下：
需要更新的sort值数据库初始为：0

start:http-nio-8062-exec-1Creating a new SqlSessionRegistering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9]JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@2d5c951c] will be managed by Spring==>Preparing: SELECT cid,category_name,parent_id,image_url,icon_url,sort,status FROM t_admin_category WHERE cid=? ==> Parameters: 6(Integer)<==Columns: cid, category_name, parent_id, image_url, icon_url, sort, status<==Row: 6, 1, 0, 11111, 1, 0, 1111<==Total: 1Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9] Time：27 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectByIdExecute SQL：SELECTcid,category_name,parent_id,image_url,icon_url,sort,status FROMt_admin_category WHEREcid=6start:http-nio-8062-exec-2Creating a new SqlSessionRegistering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7af43046]JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@700c5b36] will be managed by Spring==>Preparing: SELECT cid,category_name,parent_id,image_url,icon_url,sort,status FROM t_admin_category WHERE cid=?Time：2 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectByIdExecute SQL：SELECTcid,category_name,parent_id,image_url,icon_url,sort,status FROMt_admin_category WHEREcid=6==> Parameters: 6(Integer)<==Columns: cid, category_name, parent_id, image_url, icon_url, sort, status<==Row: 6, 1, 0, 11111, 1, 0, 1111<==Total: 1Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7af43046]http-nio-8062-exec-1:Category{cid=6, categoryName='1', parentId=0, imageUrl='11111', iconUrl='1', sort=0, status='1111'}Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9] from current transaction==>Preparing: UPDATE t_admin_category SET category_name=?, parent_id=?, image_url=?, icon_url=?, sort=?, status=? WHERE cid=? ==> Parameters: 1(String), 0(Integer), 11111(String), 1(String), 1(Integer), 1111(String), 6(Integer) Time：16 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.updateByIdExecute SQL：UPDATEt_admin_category SETcategory_name='1',parent_id=0,image_url='11111',icon_url='1',sort=1,status='1111' WHERE<==Updates: 1cid=6Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9] Time：0 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectByIdExecute SQL：Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9] from current transactionSELECTcid,category_name,==>Preparing: SELECT cid,category_name,parent_id,image_url,icon_url,sort,status FROM t_admin_category WHERE cid=? parent_id,==> Parameters: 6(Integer)image_url,icon_url,<==Columns: cid, category_name, parent_id, image_url, icon_url, sort, statussort,status <==Row: 6, 1, 0, 11111, 1, 1, 1111FROM<==Total: 1t_admin_category WHEREReleasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9]cid=6http-nio-8062-exec-1:Category{cid=6, categoryName='1', parentId=0, imageUrl='11111', iconUrl='1', sort=1, status='1111'}end:http-nio-8062-exec-1Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9]Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9]Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9]http-nio-8062-exec-2:Category{cid=6, categoryName='1', parentId=0, imageUrl='11111', iconUrl='1', sort=0, status='1111'}Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7af43046] from current transaction==>Preparing: UPDATE t_admin_category SET category_name=?, parent_id=?, image_url=?, icon_url=?, sort=?, status=? WHERE cid=?Time：0 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.updateByIdExecute SQL：UPDATEt_admin_category SETcategory_name='1',parent_id=0,image_url='11111',icon_url='1',sort=1,status='1111' WHEREcid=6==> Parameters: 1(String), 0(Integer), 11111(String), 1(String), 1(Integer), 1111(String), 6(Integer)<==Updates: 1Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7af43046]Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7af43046] from current transaction==>Preparing: SELECT cid,category_name,parent_id,image_url,icon_url,sort,status FROM t_admin_category WHERE cid=? ==> Parameters: 6(Integer)<==Columns: cid, category_name, parent_id, image_url, icon_url, sort, status<==Row: 6, 1, 0, 11111, 1, 0, 1111 Time：16 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectByIdExecute SQL：SELECTcid,category_name,<==Total: 1parent_id,image_url,Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7af43046]icon_url,sort,status FROMhttp-nio-8062-exec-2:Category{cid=6, categoryName='1', parentId=0, imageUrl='11111', iconUrl='1', sort=0, status='1111'}t_admin_category end:http-nio-8062-exec-2WHEREcid=6

大致意思：
请求A与请求B，同时操作id=6的这条记录时，查询出来id=6的这条记录，然后请求A对其进行了修改 SET　sort=1操作，完事之后，请求A的事务提交了。而此时请求B，通过id=6条件查询到的category的信息是请求A提交之前的，此时请求B执行了SET　sort=1，覆盖了请求B的修改操作。

解决方法其它很简单，我们上面可以看到，update语句是直接set sort=1的，我们只需要改为set sort = sort +1 就可以了。你不信？那我改一下试试：

@Transactional(rollbackFor = Exception.class)public void updateStatusTest(Integer id) {System.out.println("start:"+Thread.currentThread().getName());Category category = categoryMapper.selectById(id);try {Thread.sleep(8000);} catch (InterruptedException e) {e.printStackTrace();}System.out.println(Thread.currentThread().getName()+":"+category);//category.setSort(category.getSort()+1);categoryMapper.updateStatusById(category);category = categoryMapper.selectById(id);System.out.println(Thread.currentThread().getName()+":"+category);System.out.println("end:"+Thread.currentThread().getName());}

Mapper文件：

<update id="updateStatusById" parameterType="com.sxl.simple.shop.admin.category.entity.Category">UPDATEt_admin_categorySETsort =sort + 1WHEREcid= #{cid,jdbcType=INTEGER}</update>

我们执行试一下，看下日志：

start:http-nio-8062-exec-1Creating a new SqlSessionRegistering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f]JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@6c17839b] will be managed by Spring==>Preparing: SELECT cid,category_name,parent_id,image_url,icon_url,sort,status FROM t_admin_category WHERE cid=? ==> Parameters: 6(Integer)<==Columns: cid, category_name, parent_id, image_url, icon_url, sort, status<==Row: 6, 1, 0, 11111, 1, 0, 1111<==Total: 1Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f] Time：43 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectByIdExecute SQL：SELECTcid,category_name,parent_id,image_url,icon_url,sort,status FROMt_admin_category WHEREcid=6start:http-nio-8062-exec-2Creating a new SqlSessionRegistering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c88f022]JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@195f8b5a] will be managed by Spring==>Preparing: SELECT cid,category_name,parent_id,image_url,icon_url,sort,status FROM t_admin_category WHERE cid=? ==> Parameters: 6(Integer)<==Columns: cid, category_name, parent_id, image_url, icon_url, sort, status<==Row: 6, 1, 0, 11111, 1, 0, 1111<==Total: 1Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c88f022] Time：2 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectByIdExecute SQL：SELECTcid,category_name,parent_id,image_url,icon_url,sort,status FROMt_admin_category WHEREcid=6http-nio-8062-exec-1:Category{cid=6, categoryName='1', parentId=0, imageUrl='11111', iconUrl='1', sort=0, status='1111'}Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f] from current transaction==>Preparing: UPDATE t_admin_category SET sort =sort + 1 WHERE cid= ? ==> Parameters: 6(Integer)<==Updates: 1Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f]Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f] from current transaction==>Preparing: SELECT cid,category_name,parent_id,image_url,icon_url,sort,status FROM t_admin_category WHERE cid=?Time：0 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.updateStatusByIdExecute SQL：UPDATEt_admin_category SETsort =sort + 1 WHEREcid= 6==> Parameters: 6(Integer) Time：0 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectById<==Columns: cid, category_name, parent_id, image_url, icon_url, sort, statusExecute SQL：<==Row: 6, 1, 0, 11111, 1, 1, 1111SELECT<==Total: 1cid,Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f]category_name,parent_id,http-nio-8062-exec-1:Category{cid=6, categoryName='1', parentId=0, imageUrl='11111', iconUrl='1', sort=1, status='1111'}image_url,end:http-nio-8062-exec-1icon_url,sort,Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f]status FROMTransaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f]t_admin_category WHERETransaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f]cid=6 Time：0 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.updateStatusByIdExecute SQL：UPDATEt_admin_category SETsort =sort + 1 WHEREcid= 6 Time：0 ms - ID：com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectByIdExecute SQL：SELECTcid,category_name,parent_id,image_url,icon_url,sort,status FROMt_admin_category WHEREcid=6http-nio-8062-exec-2:Category{cid=6, categoryName='1', parentId=0, imageUrl='11111', iconUrl='1', sort=0, status='1111'}Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c88f022] from current transaction==>Preparing: UPDATE t_admin_category SET sort =sort + 1 WHERE cid= ? ==> Parameters: 6(Integer)<==Updates: 1Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c88f022]Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c88f022] from current transaction==>Preparing: SELECT cid,category_name,parent_id,image_url,icon_url,sort,status FROM t_admin_category WHERE cid=? ==> Parameters: 6(Integer)<==Columns: cid, category_name, parent_id, image_url, icon_url, sort, status<==Row: 6, 1, 0, 11111, 1, 2, 1111<==Total: 1Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c88f022]http-nio-8062-exec-2:Category{cid=6, categoryName='1', parentId=0, imageUrl='11111', iconUrl='1', sort=2, status='1111'}end:http-nio-8062-exec-2

看到了吧，保证数据一致了吧

我之前的文章有对Mysql事务这块做过详解，想知道真正原理的小伙伴请阅读： 深入理解mysql事务
注意，我这里使用的数据库是mysql8以上，应用服务器是单机版的。