Author: Wen Anshi
Source: https://my.oschina.net/floor/blog/4784609

problem

One busy afternoon, Xiaohang suddenly scolded, "TM, shit, the version hasn't changed, the update is unsuccessful".

I saw him sweating, clenched his hands, and looked ferocious. He seemed to have another attack. He hurriedly said, "it doesn't matter if he doesn't succeed. Just try again. Optimism lock usually needs to try again."

He said with slight disdain: the code has retry logic, and I added logs. It was found that the version has not changed, but the update is unsuccessful.

As a person who has little pursuit of technology, what he said immediately aroused my curiosity, and then sincerely said, can I see the code?

Xiao hang didn't say a word, but he drew a please gesture with his hands.

After careful study, the general logic of the code is as follows:

@Transactional(timeout = 36000, rollbackFor = Throwable.class)
public void updateGoodNum(String id,Integer num)
      throws Exception {
    //1. select num as  dbnum,version as dbversion  from t where id=#{id}
    //2. update t set num=dbnum-num,version =dbversion +1  
    // where id=#{id} and version =dbversion  ;
    // If the update fails, retry 1 or 2, 3 times in total
}

I sighed slightly. After the mysql connection tool executed the following statement and sent the screenshot to Xiaohang,

Pretend to be a master and say: the isolation level of our test environment is RR (REPEATABLE-READ). It is impossible to retry in a transaction!

Xiaohang said in embarrassment: brother, what is the isolation level? Why not? How to change it?

Isolation level

Isolation level	explain
READ UNCOMMITTED	Uncommitted reading will cause dirty reading and violate persistence D
READ COMMITTED	Reading submitted data will cause unreal reading to violate consistency C
REPEATABLE READ(RR)	It can be read repeatedly. The default isolation level is. The select statement in the transaction will read the snapshot before the start of the transaction. Of course, it can also read the updated content of the transaction
SERIALIZABLE	Instead of using mysql's mvcc mechanism, it obtains the read lock under each select request and attempts to obtain the write lock under each update operation

The update operation is to read the current value.

At the RR isolation level, why can't retry in a transaction?

Table simulation, why not?

Before starting a transaction	Table t corresponds to id=1, version=1
	Transaction Abegin	Transaction Bbegin
1	select version from t where id=1;-- Get version=1	update t set version=2 where id=1;commit;
2	update t set XX where id=1 and version=1；// Update failed. Update reads the current, version=2
3	select version from t where id=1;// Get version=1
4	commit

Note: select in the transaction is to read the snapshot, and update is to read the current.

Simply put, for other transactions, the version has been modified by 2. What transaction A sees is the value before the transaction starts

That is, the version is 1

Solution

Under RR isolation level, move the retry out of the transaction That is, restart a transaction each time you retry

The summary logic is as follows

///If the update fails, retry updateGoodNum 3 times in total
public AFacadeImpl{
    @Autowired
    AService aservice;
    public void updateGoodsNum(){
        int i = 0; 
        while(!aservice.updateGoodNum(id,num) && i++ < 3);
    }
}

public AServiceimpl implement AService{
    @Transactional(timeout = 36000, rollbackFor = Throwable.class)
    public boolean updateGoodNum(String id,Integer num)
          throws Exception {
        //1. select num as  dbnum,version as dbversion  from t where id=#{id}
        //2. update t set num=dbnum-num,version =dbversion +1  
        // where id=#{id} and version =dbversion  ;
        /
    }
}

Recent hot article recommendations:

1.1000 + Java interview questions and answers (2021 latest version)

2.Finally got the IntelliJ IDEA activation code through the open source project. It's really fragrant!

3.Ali Mock tools are officially open source and kill all Mock tools on the market!

4.Spring Cloud 2020.0.0 is officially released, a new and subversive version!

5.Java development manual (Songshan version) is the latest release. Download it quickly!

Feel good, don't forget to like + forward!