The causes and Solutions of the failure of data Update in Mysql Database
This article will explain in detail the reasons and solutions for the invalidation of data update in Mysql database. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.
1. Background
When a recent project encountered a problem and updated a document at the same time, the update failed. For example:
@ Transactional (rollbackFor = Exception.class) public void update (Integer id) {/ / 1. Press id to query / / 2. Update the value of a field}
The generated SQL looks something like this:
UPDATE table SET field = # {field,jdbcType=INTEGER} WHERE id= 1
Then the problem with the above code is:
For the same id=1, both request An and request B enter the update method, and the information obtained by id query is the same. Then, after request A updates the record of id=1, request B updates the record of id=1. At this time, note that when request B updates the record of id=1, the data queried by request B by id is already old data. The update of request An is overwritten by request B.
It doesn't matter if the status is updated. What about inventory?
If the inventory is updated, is it a + or-operation on the original value of this record, is there a problem?
two。 Analyze the causes
After that, I wrote a small demo to reproduce the problem, and the code is roughly as follows:
Requirements: every time a category is accessed, the sort is + 1
Open two clients to access simultaneously
@ 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 ());}
The mybatis used by the DAO layer generates logs roughly as follows:
The sort database that needs to be updated is initially: 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)
