mysql数据库失效_Mysql数据库数据更新失效的原因和解决方法

Mysql数据库数据更新失效的原因和解决方法

发布时间：2020-06-12 12:20:09

来源：亿速云

阅读：486

作者：Leah

这篇文章将为大家详细讲解有关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值数据库初始为：0start: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数据库数据更新失效的原因和解决方法大概了解了吗？如果想了解更多相关文章内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！