Just about to leave work, I was stopped by a development colleague. Let's see a strange problem: the query method of the same Mapper interface of Mybatis, the first return result is different from the second return result. I can't think about it!
problem
Talk is heap. Show me the code
- mapper interface definition
public interface GoodsTrackMapper extends BaseMapper<GoodsTrack> {
List<GoodsTrackDTO> listGoodsTrack(@Param("criteria") GoodsTrackQueryCriteria criteria);
}- xml definition
<select id="listGoodsTrack" resultType="xxx.GoodsTrackDTO">
SELECT ...
</select>- service definition
@Service
@Transactional(propagation = Propagation.SUPPORTS, readOnly = true, rollbackFor = Exception.class)
public class GoodsTrackService extends BaseService<GoodsTrack, GoodsTrackDTO> {
@Autowired
private GoodsTrackMapper goodsTrackMapper;
public List<GoodsTrackDTO> listGoodsTrack(GoodsTrackQueryCriteria criteria){
return goodsTrackMapper.listGoodsTrack(criteria);
}
public List<GoodsTrackDTO> goodsTrackList(GoodsTrackQueryCriteria criteria){
List<GoodsTrackDTO> listGoodsTrack = goodsTrackMapper.listGoodsTrack(criteria);
Map<String, GoodsTrackDTO> goodsTrackDTOMap = new HashMap<String, GoodsTrackDTO>();
for (GoodsTrackDTO goodsTrackDTO : listGoodsTrack){
String goodsId = String.valueOf(goodsTrackDTO.getGoodsId());
if (!goodsTrackDTOMap.containsKey(goodsId)){
goodsTrackDTOMap.put(goodsId, goodsTrackDTO);
}else {
GoodsTrackDTO goodsTrack = goodsTrackDTOMap.get(goodsId);
int num = goodsTrack.getGoodsNum() + goodsTrackDTO.getGoodsNum();
goodsTrack.setGoodsNum(num);
}
}
List<GoodsTrackDTO> list = new ArrayList(goodsTrackDTOMap.values());
return list;
}
}
@Service
@Transactional(propagation = Propagation.SUPPORTS, readOnly = true, rollbackFor = Exception.class)
public class GoodsOrderService extends BaseService<GoodsOrder, GoodsOrderDTO> {
@Autowired
private GoodsTrackService goodsTrackService;
@Override
public GoodsOrderDTO create(GoodsOrderDTO goodsOrderDTO) {
//...
List<GoodsTrackDTO> rs1 = goodsTrackList(criteria);
//...
List<GoodsTrackDTO> rs2 = listGoodsTrack(criteria);
//...
}
}The general logic is to define two query methods in goodstrakservice, one is to get data directly from the database, the other is to get data from the database and carry out some processing (merge and accumulate through a certain field, similar to sum group by), and then use the same method in goodsordservice (this method is a transaction method In these two queries, it is found that there is a problem in the data of rs2. The expectation is that all data should be consistent with the data of the database table, but some of the data is consistent with the rs1 in the revised database.
Location
Initially, the mapper method goodstrakmapper.listGoodsTrack (criteria), which is directly called by listGoodsTrack method, does not do any application layer processing. The first reaction is the reason for caching. I asked whether the previous query had changed the results returned by the query (I didn't look at the specific implementation in detail at the beginning). I replied No. After a while of tossing, I went back to look at the implementation of goodstraklist. As expected, seeing is believing, listening is believing. In this method, the returned list is grouped by goodsId, and goodsNum is accumulated. Finally, several objects after accumulation are returned. But in the accumulation, it directly affects the returned result object, which obviously changes the query result (say no?!). This is the problem. In the same transaction, mybatis caches the return results of the same query (the same sql, the same parameters) (called the first level cache). The next time it does the same query, if there is no update operation in the middle, it directly returns the cached data. In this case, because of the artificial modification of the cached data, it finally imports The data found is inconsistent with the database.
mybatis caching mechanism
A brief introduction to the two-level caching mechanism of mybatis
Level 1 cache: Level 1 cache includes SqlSession and STATEMENT, which are implemented in SqlSession by default. In one session, if the two queries have the same sql and parameters, and there is no update operation in the middle, the second query will directly return the cached results of the first query, and no longer request the database. If there is an update operation in the middle, the update operation will clear the cache and the subsequent query will access the database. At the STATEMENT level, each query will clear the first level cache, and each query will access the database.
Second level Cache: second level Cache is the Cache shared among multiple sqlsessions of the same namesapce, which is not enabled by default. When level 2 Cache is enabled, the data query process is level 2 Cache - > Level 1 Cache - > database. The update operation under the same namespace will affect the same Cache.
How to open L2 cache
- You need to set in mybatis-config.xml:
<settings>
<setting name="cacheEnabled" value="true"/>
</settings>- Then set the cache related configuration under < mapper > in the mapper's xml file:
<cache
eviction="LRU"
flushInterval="60000"
size="512"
readOnly="true"/> Supported properties:
- Type: the type used by cache. The default is perpetual cache
- eviction: the strategy of recycling. The common ones are LRU and FIFO
- flushInterval: configure to automatically refresh the cache for a certain time, in milliseconds
- size: maximum number of cached objects
- readOnly: whether it is read-only. If it is configured as read-write, the corresponding entity class is required to implement the Serializable interface
- blocking: if the corresponding key cannot be found in the cache, will it be blocked until the corresponding data enters the cache
You can also use < cache ref namespace = "mapper. Usermapper" / > to share the secondary cache with another mapper
Solve
It has been determined that it is due to the first level cache of mybatis. How to solve the problem mentioned in this article? There are basically three solutions.
- Scheme using cache
Since we want to use cache, we cannot change the cached data. At this time, we can make a copy of the data where we need to change the data, so that it does not change the cached data itself, such as
for (GoodsTrackDTO goodsTrackDTO : listGoodsTrack){
String goodsId = String.valueOf(goodsTrackDTO.getGoodsId());
if (!goodsTrackDTOMap.containsKey(goodsId)){
goodsTrackDTOMap.put(goodsId, ObjectUtil.clone(goodsTrackDTO));
}else {
GoodsTrackDTO goodsTrack = goodsTrackDTOMap.get(goodsId);
int num = goodsTrack.getGoodsNum() + goodsTrackDTO.getGoodsNum();
goodsTrack.setGoodsNum(num);
}
}Use the ObjectUtil.clone() method (provided in the hutool Toolkit) to make a copy of the data that needs to be changed.
- Scheme to disable caching
Add the configuration of flushCache="true" to the sql definition of xml, so that the query does not use cache, as follows
<select id="listGoodsTrack" resultType="xxx.GoodsTrackDTO" flushCache="true">
SELECT ...
</select>Another way to disable caching is to set the first level cache to state directly to disable globally. Configure in mybatis-config.xml:
<settings>
<setting name="localCacheScope" value="STATEMENT"/>
</settings>- Scheme to avoid cache
It's not elegant to define a mapper method that implements the same query with different id to avoid using the same cache.
<select id="listGoodsTrack2" resultType="xxx.GoodsTrackDTO" flushCache="true">
SELECT ...
</select>Another way to avoid caching is not to use transactions, so that two queries are not in a SqlSession, but sometimes transactions are necessary, so score scenarios.
In addition, because the cache of mybatis is based on the local environment, the data read may not be consistent with the database in the distributed environment. For example, if one service instance reads the data twice, and another service instance updates the data, then the later reading may cause problems due to the old data cached or read, rather than the updated data. At this time, you can disable mybatis cache by setting the cache to the state level, and provide distributed global cache by Redis, MemCached, etc.
Live seriously and share happily
Welcome to WeChat public: technical space for new mountain rain.
Get more information about Spring Boot, Spring Cloud, Docker and other enterprise practical technologies