Database deletes failed during inserts

Question

Hi, I have two java apps: one of them inserts records to Table1. Second application reads first N items and removes them. When 1st application inserts data intensive, 2nd failed when I try to delete any rows with CannotSerializeTransactionException. I don't see any problems: inserted items are visible in select/delete only when insert transaction is finished. How can I fix it? Thanks.

 TransactionTemplate tt = new  TransactionTemplate(platformTransactionManager);  
    tt.setIsolationLevel(Connection.TRANSACTION_SERIALIZABLE);
    tt.execute(new TransactionCallbackWithoutResult() {                
    @Override
    protected void doInTransactionWithoutResult(TransactionStatus status) {
        List<Record> records = getRecords(); // jdbc select
        if (!records.isEmpty()) {
           try {
              processRecords(records); // no database
              removeRecords(records); // jdbc delete - exception here
           } catch (CannotSerializeTransactionException e) {
                 log.info("Transaction rollback");
            }
         } else {
           pauseProcessing();
             }
        }
   });

pauseProcessing() - sleep

public void removeRecords(int changeId) { String sql = "delete from RECORDS where ID <= ?";
        getJdbcTemplate().update(sql, new Object[]{changeId});}

Answer 1

Are you using Connection.TRANSACTION_SERIALIZABLE also in first application? Looks like first application locks table, so second one cannot access it (cannot start transaction). Maybe Connection.TRANSACTION_REPEATABLE_READ could be enough?

Probably you can also configure second application not to throw exception when it cannot access resources, but to wait for it.

Answer 2

This sounds as if you're reading uncommitted data. Are you sure you're properly settings the isolation level?

It seems to me that you're mixing up constants from two different classes: Shouldn't you be passing TransactionDefinition.ISOLATION_SERIALIZABLE instead of Connection.TRANSACTION_SERIALIZABLE to the setIsolationLevel method?

Why do you set the isolation level anyway? Oracle's default isolation level (read committed) is usually the best compromise between consistency and speed and should nicely work in you case.