java MyBatis有时在数据库中找不到记录

Question

我正在使用Spring Boot（2.3.10.RELEASE）+MyBatis+HikariCP（3.4.5）连接到PostgreSQL 13，今天我发现一个查询无法从数据库中获取数据。我绝对确定要查找数据的查询存在于数据库中。当我跟踪日志时，它显示如下：

DEBUG [dolphin-post-service-1] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5f3949a1]
2021-05-16 11:35:12.806 ERROR 12273 --- [-post-service-1] c.d.s.p.s.impl.article.ArticleService    : query failed:cruise:article:detail:2416846
2021-05-16 11:35:13.070  INFO 12273 --- [-post-service-1] c.d.s.p.common.mq.StreamMessageListener  : stream message。messageId=1621136113069-0, stream=pydolphin:stream:article, body={id=2416847, sub_source_id=1168}
DEBUG [dolphin-post-service-1] - Creating a new SqlSession
DEBUG [dolphin-post-service-1] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7f8a0ee1] was not registered for synchronization because synchronization is not active
DEBUG [dolphin-post-service-1] - JDBC Connection [HikariProxyConnection@803742617 wrapping org.postgresql.jdbc.PgConnection@35a5c30d] will not be managed by Spring
DEBUG [dolphin-post-service-1] - ==>  Preparing: SELECT 'true' AS QUERYID, id, user_id, sub_source_id, created_time, updated_time, sub_status FROM sub_relation WHERE (sub_status = ? AND sub_source_id = ?)
DEBUG [dolphin-post-service-1] - ==> Parameters: 1(Integer), 1168(Long)
DEBUG [dolphin-post-service-1] - <==      Total: 1
DEBUG [dolphin-post-service-1] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7f8a0ee1]
DEBUG [dolphin-post-service-1] - Creating a new SqlSession
DEBUG [dolphin-post-service-1] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@94502f9] was not registered for synchronization because synchronization is not active
DEBUG [dolphin-post-service-1] - JDBC Connection [HikariProxyConnection@858597637 wrapping org.postgresql.jdbc.PgConnection@35a5c30d] will not be managed by Spring
DEBUG [dolphin-post-service-1] - ==>  Preparing: SELECT id, user_id, title, author, guid, created_time, updated_time, link, pub_time, sub_source_id, cover_image, channel_reputation, editor_pick FROM article WHERE id = ?
DEBUG [dolphin-post-service-1] - ==> Parameters: 2416847(Long)
DEBUG [dolphin-post-service-1] - <==      Total: 0
DEBUG [dolphin-post-service-1] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@94502f9]
2021-05-16 11:35:13.074 ERROR 12273 --- [-post-service-1] c.d.s.p.s.impl.article.ArticleService    : query failed:cruise:article:detail:2416847

这是我的Java代码：

private void loadArticleFromDatabase(List<ArticleDTO> articles, Long articleId, String cachedKey) {
    Article dbArticle = articleMapper.selectByPrimaryKey(articleId);
    if (dbArticle == null) {
        log.error("query failed:" + cachedKey);
        return;
    }
    ArticleDTO articleDTO = appendArticleAttachInfo(dbArticle);
    articles.add(articleDTO);
}

这是我的MyBatis。xml配置：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "./mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="cacheEnabled" value="false"/>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
        <setting name="localCacheScope" value="STATEMENT"/>
        <setting name="multipleResultSetsEnabled" value="true"/>
        <setting name="useColumnLabel" value="true"/>
        <setting name="defaultStatementTimeout" value="5"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="useGeneratedKeys" value="true"/>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer" />
        <typeAlias alias="Long" type="java.lang.Long" />
        <typeAlias alias="HashMap" type="java.util.HashMap" />
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
        <typeAlias alias="ArrayList" type="java.util.ArrayList" />
        <typeAlias alias="LinkedList" type="java.util.LinkedList" />
    </typeAliases>
    <plugins>
        <plugin interceptor="misc.interceptor.db.DefaultTimeInterceptor"/>
        <plugin interceptor="misc.interceptor.db.DynamicDataSourceInterceptor"/>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="param1" value="value1"/>
        </plugin>
    </plugins>
</configuration>

奇怪的是，应用程序在一段时间内运行良好，突然无法从数据库中查询数据。问题在哪里？我应该如何解决？这是我关于transactionManager的DataSource.config：

@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis/mybatis-config.xml"));
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/*/*.xml"));
    Interceptor[] plugins = {paginationInterceptor,defaultTimeInterceptor};
    bean.setPlugins(plugins);
    return bean.getObject();
}

@Bean(name = "transactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
}

正如我所说，我确信文章id存在于数据库中：

顺便说一下，这是我的DynamicDataSourceInterceptor类，该类试图确定数据库应该使用主节点还是从节点：

package misc.interceptor.db;

import lombok.extern.slf4j.Slf4j;
import misc.config.db.DataSourceContextHolder;
import misc.constant.db.ConstantPool;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.keygen.SelectKeyGenerator;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.transaction.support.TransactionSynchronizationManager;

import java.util.Locale;
import java.util.Properties;

/**
 * @author dolphin
 * @version 1.0
 *
 * @date: 2019-09-17 20:28
 */
@Slf4j
@Intercepts({
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
})
public class DynamicDataSourceInterceptor implements Interceptor {

    /**
     * 
     */
    private static final String regex = ".*insert\\u0020.*|.*delete\\u0020.*|.update\\u0020.*";

    /**
     * 
     * @param invocation
     * @return
     * @throws Throwable
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
        Object[] objects = invocation.getArgs();
        MappedStatement mappedStatement = (MappedStatement) objects[0];
        String lookupKey = ConstantPool.MASTER_KEY;
        if (synchronizationActive != true){
            if (mappedStatement.getSqlCommandType().equals(SqlCommandType.SELECT)){
                if (mappedStatement.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)){
                    lookupKey = ConstantPool.MASTER_KEY;
                } else {
                    BoundSql boundSql = mappedStatement.getSqlSource().getBoundSql(objects[1]);
                    String sqlstr = boundSql.getSql();
                    String sql = sqlstr.toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " ");
                    if (sql.matches(regex)){
                        lookupKey = ConstantPool.MASTER_KEY;
                    } else {
                        lookupKey = ConstantPool.SLAVE_KEY;
                    }
                }
            }
        } else {
            lookupKey = ConstantPool.MASTER_KEY;
        }
        DataSourceContextHolder.setDataSourceKey(lookupKey);
        return invocation.proceed();
    }

    /**
     * 
     * @param target
     * @return
     */
    @Override
    public Object plugin(Object target) {
        if (target instanceof Executor){
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    /**
     * 
     * @param properties
     */
    @Override
    public void setProperties(Properties properties) {

    }
}

因为现在主节点和从节点具有相同的数据库配置（我的数据库现在只有一个节点）。因此，我认为这似乎不是问题所在。这是捕获的断点：

Answer 1

最后，我发现写文章的客户端没有立即提交事务。事务提交是behand和Mybatis搜索，调整编写Python客户端代码如下：

article_count = count(guid, title, source.id)
        if article_count == 0:
            with session_scope() as local_count_session:
                local_count_session.add(article)
                local_count_session.flush()
                # add this line to commit transaction
                local_count_session.commit() 
                push_message_to_stream(article)
        else:
            logger.warn("this article already exists in database,title:" + title + ",link:" + link)
        return article