使用 Spring JdbcMetadataStore (Oracle) 时引发 DuplicateKeyException [英] DuplicateKeyException raised when using Spring JdbcMetadataStore (Oracle)

问题描述

我当前的 file:inbound-channel-adapter 配置工作正常.我在多台服务器上运行该应用程序，实际上只有一台服务器在处理该文件.数据库表 INT_METADATA_STORE 也更新成功.我面临的问题是其中一台服务器仍在尝试插入记录并引发以下异常:

My current configuration of file:inbound-channel-adapter is working fine. I'm running the application in multiple servers and only one is actually processing the file. Database table INT_METADATA_STORE also updated successfully. Issue I'm facing is that one of the servers are still trying to insert the record and below exception is raised:

org.springframework.messaging.MessagingException: nested exception is
org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; 
SQL [INSERT INTO INT_METADATA_STORE(METADATA_KEY, METADATA_VALUE, REGION) SELECT ?, ?, ? FROM INT_METADATA_STORE WHERE METADATA_KEY=? AND REGION=? HAVING COUNT(*)=0];
ORA-00001: unique constraint (SMS_OWNER.INT_METADATA_STORE_PK) violated; 
nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SMS_OWNER.INT_METADATA_STORE_PK) violated

我尝试了不同口味的隔离，但都没有成功.这是否与我使用 XML 和 Spring Integration + Java 后使用的事务管理器有关?请参阅下面的一些配置:

I've tried different flavors of isolation and no luck. Is this something related to the transaction manager used since I'm using XML with Spring Integration + Java? See below some configurations:

<int-file:inbound-channel-adapter directory="file:/tmp/input/" prevent-duplicates="true" filter="compositeFileListFilter">
  <int:poller max-messages-per-poll="1" cron="*/10 * * * * *">
    <int:transactional transaction-manager="transactionManager" isolation="READ_COMMITTED" timeout="5" />
  </int:poller>
</int-file:inbound-channel-adapter>

和

@Configuration
@EnableTransactionManagement
public class MetadataStoreConfiguration {

    @Value("${input.file.pattern:(DUMMY)(_).*\\.(xml)}")
    private String pattern;

    @Bean
    @Qualifier("fileSystemPersistentAcceptOnceFileListFilter")
    public FileSystemPersistentAcceptOnceFileListFilter fileSystemPersistentAcceptOnceFileListFilter(final DataSource dataSource) {
        return new FileSystemPersistentAcceptOnceFileListFilter(metadataStore(dataSource),"");
    }

    @Bean
    @Qualifier("metadataStore")
    public JdbcMetadataStore metadataStore(final DataSource dataSource) {
        JdbcMetadataStore metadataStore = new JdbcMetadataStore(dataSource);
        return metadataStore;
    }

    @Bean 
    public CompositeFileListFilter<File> compositeFileListFilter(FileSystemPersistentAcceptOnceFileListFilter fileSystemPersistentAcceptOnceFileListFilter) {       
        CompositeFileListFilter<File> filter = new CompositeFileListFilter<>(Arrays.asList(fileSystemPersistentAcceptOnceFileListFilter, new RegexPatternFileListFilter(pattern)));
        return filter;
    }

    @Bean
    @Primary
    public PlatformTransactionManager transactionManager(final DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

我使用 Spring Boot 2.2.4.RELEASE、Java 8 和 Oracle 作为数据库.任何帮助将不胜感激.

I'm using Spring Boot 2.2.4.RELEASE, Java 8 and Oracle as database. Any help will be much appreciated.

推荐答案

不，您的配置完全没问题.这真的无关紧要，因为它在启动时只读取一次.在运行时，您只有 bean，并且它们连接正确.因此，可以在 Java 配置中使用 PlatformTransactionManager 和 compositeFileListFilter 并将它们用作 XML 的引用.

No, your configuration is fully OK. It really doesn't matter because it is read only once at start up. At runtime you have only beans and they are wired properly. So, it's OK to have that PlatformTransactionManager and compositeFileListFilter in Java config and use them as references from XML one.

我认为问题不在于交易.更重要的是，它们甚至看起来有效！

I think the problem is not about transactions. More over it even looks like they work!

您的两个事务都尝试插入但没有看到值.当然，其中一个在提交时没问题，但另一个失败，因为值已经插入.使用 IINSERT INTO .. SELECT ... HAVING 我们实现了当前事务的原子性.但它确实没有给我们机会避免提交时发生冲突......

Both of your transactions try to insert and don't see a value. Of course, one of them is OK on commit, but another fails because the value is already inserted. With IINSERT INTO .. SELECT ... HAVING we achieve an atomicity for the current transaction. But it really doesn't give us a chance to avoid conflict on commit...

看起来我们的 JdbcMetadataStore.putIfAbsent() 应该改进以捕获这个 SQLIntegrityConstraintViolationException 作为数据存在的事实并返回当前值.

It looks like our JdbcMetadataStore.putIfAbsent() should be improved to catch this SQLIntegrityConstraintViolationException as a fact of data presence and return the current value.

请提出 GH 问题，我们会考虑如何进行！

Please, raise a GH issue and we'll think how to proceed!

这篇关于使用 Spring JdbcMetadataStore (Oracle) 时引发 DuplicateKeyException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！