Mysql数据库数据更新失效的原因和解决方法
这篇文章将为大家详细讲解有关MySQL数据库数据更新失效的原因和解决方法,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。
站在用户的角度思考问题,与客户深入沟通,找到济南网站设计与济南网站推广的解决方案,凭借多年的经验,让设计与互联网技术结合,创造个性化、用户体验好的作品,建站类型包括:网站设计、成都做网站、企业官网、英文网站、手机端网站、网站推广、申请域名、虚拟主机、企业邮箱。业务覆盖济南地区。
1. 背景
最近的项目中遇到一项问题,并发更新某一单据的时候,出现了更新失效的情况。比如:
@Transactional(rollbackFor = Exception.class) public void update(Integer id){ //1.按id查询 //2.更新某一字段的值 }
生成的SQL大概是这样的:
UPDATE table 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覆盖了。
如果更新的是状态,那倒无所谓,如果库存量呢?
如果更新的是库存,是在这条记录的原始值上进行+或者-操作,是不是就出问题了?
2. 分析产生的原因
事后写了个小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-1 Creating a new SqlSession Registering 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: 1 Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9] Time:27 ms - ID:com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectById Execute SQL: SELECT cid, category_name, parent_id, image_url, icon_url, sort, status FROM t_admin_category WHERE cid=6 start:http-nio-8062-exec-2 Creating a new SqlSession Registering 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.selectById Execute SQL: SELECT cid, category_name, parent_id, image_url, icon_url, sort, status FROM t_admin_category WHERE cid=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: 1 Releasing 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.updateById Execute SQL: UPDATE t_admin_category SET category_name='1', parent_id=0, image_url='11111', icon_url='1', sort=1, status='1111' WHERE <== Updates: 1 cid=6 Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9] Time:0 ms - ID:com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectById Execute SQL: Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9] from current transaction SELECT cid, 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, status sort, status <== Row: 6, 1, 0, 11111, 1, 1, 1111 FROM <== Total: 1 t_admin_category WHERE Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6df7e3e9] cid=6 http-nio-8062-exec-1:Category{cid=6, categoryName='1', parentId=0, imageUrl='11111', iconUrl='1', sort=1, status='1111'} end:http-nio-8062-exec-1 Transaction 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.updateById Execute SQL: UPDATE t_admin_category SET category_name='1', parent_id=0, image_url='11111', icon_url='1', sort=1, status='1111' WHERE cid=6 ==> Parameters: 1(String), 0(Integer), 11111(String), 1(String), 1(Integer), 1111(String), 6(Integer) <== Updates: 1 Releasing 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.selectById Execute SQL: SELECT cid, category_name, <== Total: 1 parent_id, image_url, Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7af43046] icon_url, sort, status FROM http-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-2 WHERE cid=6
大致意思:
请求A与请求B,同时操作id=6的这条记录时,查询出来id=6的这条记录,然后请求A对其进行了修改 SET sort=1操作,完事之后,请求A的事务提交了。而此时请求B,通过id=6条件查询到的category的信息是请求A提交之前的,此时请求B执行了SET sort=1,覆盖了请求B的修改操作。
3. 解决思路
解决方法其它很简单,我们上面可以看到,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 t_admin_category SET sort =sort + 1 WHERE cid= #{cid,jdbcType=INTEGER}
我们执行试一下,看下日志:
start:http-nio-8062-exec-1 Creating a new SqlSession Registering 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: 1 Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f] Time:43 ms - ID:com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectById Execute SQL: SELECT cid, category_name, parent_id, image_url, icon_url, sort, status FROM t_admin_category WHERE cid=6 start:http-nio-8062-exec-2 Creating a new SqlSession Registering 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: 1 Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c88f022] Time:2 ms - ID:com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectById Execute SQL: SELECT cid, category_name, parent_id, image_url, icon_url, sort, status FROM t_admin_category WHERE cid=6 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@5e925f3f] from current transaction ==> Preparing: UPDATE t_admin_category SET sort =sort + 1 WHERE cid= ? ==> Parameters: 6(Integer) <== Updates: 1 Releasing 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.updateStatusById Execute SQL: UPDATE t_admin_category SET sort =sort + 1 WHERE cid= 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, status Execute SQL: <== Row: 6, 1, 0, 11111, 1, 1, 1111 SELECT <== Total: 1 cid, 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-1 icon_url, sort, Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f] status FROM Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f] t_admin_category WHERE Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e925f3f] cid=6 Time:0 ms - ID:com.sxl.simple.shop.admin.category.mapper.CategoryMapper.updateStatusById Execute SQL: UPDATE t_admin_category SET sort =sort + 1 WHERE cid= 6 Time:0 ms - ID:com.sxl.simple.shop.admin.category.mapper.CategoryMapper.selectById Execute SQL: SELECT cid, category_name, parent_id, image_url, icon_url, sort, status FROM t_admin_category WHERE cid=6 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@6c88f022] from current transaction ==> Preparing: UPDATE t_admin_category SET sort =sort + 1 WHERE cid= ? ==> Parameters: 6(Integer) <== Updates: 1 Releasing 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: 1 Releasing 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数据库数据更新失效的原因和解决方法
文章URL:http://pwwzsj.com/article/gpsscc.html