my project is a spring boot 2.x, mybatis 3.x and mysql 8.0(default isolation level – repeatable read).

I have a BServiceImpl:

@Slf4j
@Service
public class BServiceImpl extends CommonService {

    @Transactional
    public Integer saveBill(JSONObject param) {

        this.saveIC(param);
        return 1;
    }
}

the CommonService is:

public abstract class CommonService implements BService {

    @Override
    @Transactional
    public void saveIC(JSONObject param) {

        this.sService.getNewNo("IV", 20473, "2407"));
    }
}

the SServiceImpl is:

@Slf4j
@Service
public class SServiceImpl extends BaseServiceImpl implements SService {

    public String getNewNo(String n, Integer b, String d) {
    
    String key = n + b + d;

    StringBuffer sb = new StringBuffer();

    Object obj = getNoType(key);

    synchronized (obj) {

        BeanA beanA = this.getBeanA(key);// SELECT ID, VALUE_INT FROM tableA WHERE CODE LIKE CONCAT(#{key}, '%')
        Integer numberSuffix = null;
        numberSuffix = 1 + beanA.getValue_int();
        beanA.setValue_int(numberSuffix);
        this.sDao.updateBeanA(beanA);// UPDATE tableA SET VALUE_INT = #{value_int} WHERE id = #{id}
        sb.append(beanA.getCode());

        sb.append(String.format(patten, numberSuffix));// 1 -> 0001, 1234 -> 1234
    }

        return sb.toString();
    }

}

but sometimes the getNewNoreturn same string for same key when in concurrency condition.

after debug, I make sure the synchronized works for different threads with same key.

the problem is BeanA beanA = this.getBeanA(key);, at the beginning, the value_int = 1, thread1 read it and increment it, then do the update(it should be 2 now).
however, after thread1 finish the synchronized block, thread2 enter the synchronized and call getBeanA(key), it still read the old value(value_int = 1, the correct should be 2),
thus, it results in getNewNo return same number.

I tried to add @Transactional(propagation = Propagation.REQUIRES_NEW) for getNewNo or set mybatis.configuration.cached-enable=false, mybatis.configuration.local-cache-scope=statement, they doesn’t work.

why it still read old value even start a new transaction for getNewNo? how to fix this problem besides using select for update?