DuplicateKeyException raised when using Spring JdbcMetadataStore (Oracle)

[dinizssa]

As discussed via StackOverFlow website. It looks like JdbcMetadataStore.putIfAbsent() should be improved to catch SQLIntegrityConstraintViolationException as a fact of data presence and return the current value.

Issue:

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 

I've tried different flavors of isolation and no luck.

<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>

and

@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);
    }

}

I'm using Spring Boot 2.2.4.RELEASE, Java 8 and Oracle as database.

[dinizssa]

Thanks you guys.