How can I get a spring JdbcTemplate to read_uncommitted?

Question

Firstly, I can't use the declarative @Transactional approach as the application has multiple JDBC data-sources, I don't want to bore with the details, but suffice it to say the DAO method is passed the correct data-source to perform the logic. All JDBC data sources have the same schema, they're separated as I'm exposing rest services for an ERP system.

Due to this legacy system there are a lot of long lived locked records which I do not have control over, so I want dirty reads.

Using JDBC I would perform the following:

private Customer getCustomer(DataSource ds, String id) {
    Customer c = null;
    PreparedStatement stmt = null;
    Connection con = null;
    try {
        con = ds.getConnection();
        con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        stmt = con.prepareStatement(SELECT_CUSTOMER);
        stmt.setString(1, id);
        ResultSet res = stmt.executeQuery();
        c = buildCustomer(res);
    } catch (SQLException ex) {
        // log errors
    } finally {
        // Close resources
    }
    return c;
}

Okay, lots' of boiler-plate, I know. So I've tried out JdbcTemplate since I'm using spring.

Use JdbcTemplate

private Customer getCustomer(JdbcTemplate t, String id) {
    return t.queryForObject(SELECT_CUSTOMER, new CustomerRowMapper(), id);
}

Much nicer, but it's still using default transaction isolation. I need to somehow change this. So I thought about using a TransactionTemplate.

private Customer getCustomer(final TransactionTemplate tt,
                             final JdbcTemplate t,
                             final String id) {
    return tt.execute(new TransactionCallback<Customer>() {
        @Override
        public Customer doInTransaction(TransactionStatus ts) {
            return t.queryForObject(SELECT_CUSTOMER, new CustomerRowMapper(), id);
        }
    });
}

But how do I set the transaction isolation here? I can't find it anywhere on the callback or the TransactionTemplate to do this.

I'm reading Spring in Action, Third Edition which explains as far as I've done, though the chapter on transactions continues on to using declarative transactions with annotations, but as mentioned I can't use this as my DAO needs to determine at runtime which data-source to used based on provided arguments, in my case a country code.

Any help would be greatly appreciated.

Salesgirl

Answer 1

I've currently solved this by using the DataSourceTransactionManager directly, though it seems like I'm not saving as much boiler-plate as I first hoped. Don't get me wrong, it's cleaner, though I still can't help but feel there must be a simpler way. I don't need a transaction for the read, I just want to set the isolation.

private Customer getCustomer(final DataSourceTransactionManager txMan,
                             final JdbcTemplate t,
                             final String id) {
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);

    TransactionStatus status = txMan.getTransaction(def);
    Customer c = null;
    try {
        c = t.queryForObject(SELECT_CUSTOMER, new CustomerRowMapper(), id);
    } catch (Exception ex) {
        txMan.rollback(status);
        throw ex;
    }
    txMan.commit(status);
    return c;
}

I'm still going to keep this one unanswered for a while as I truly believe there must be a better way.

Refer to Spring 3.1.x Documentation - Chapter 11 - Transaction Management

Salesgirl

This is an old question and answer so I just thought if there is a new way to solve problem that you described. I think, new transaction manager attribute in new version of transactional annotation solves the problem Spring 3.1 Transactional – Copyreader

so would commit and rollback be used for this Read only query? Its not doing an insert or update, just wanted to clarify, since I don't know – Masonmasonic

why would a rollback be needed for Read Uncommitted query? – Masonmasonic

Answer 2

Using the TransactionTemplate helps you here, you need to configure it appropriately. The transaction template also contains the transaction configuration. Actually the TransactionTemplate extends DefaultTransactionDefinition.

So somewhere in your configuration you should have something like this.

<bean id="txTemplate" class=" org.springframework.transaction.support.TransactionTemplate">
  <property name="isolationLevelName" value="ISOLATION_READ_UNCOMMITTED"/>
  <property name="readOnly" value="true" />
  <property name="transactionManager" ref="transactionManager" />
</bean>

If you then inject this bean into your class you should be able to use the TransactionTemplate based code you posted/tried earlier.

However there might be a nicer solution which can clean up your code. For one of the projects I worked on, we had a similar setup as yours (single app multiple databases). For this we wrote some spring code which basically switches the datasource when needed. More information can be found here.

If that is to far fetched or overkill for your application you can also try and use Spring's AbstractRoutingDataSource, which based on a lookup key (country code in your case) selects the proper datasource to use.

By using either of those 2 solutions you can start using springs declarative transactionmanagement approach (which should clean up your code considerably).

Gehrke

Would this mean that I would need to configure multiple txTemplate's for each database and each isolation? So if I wanted writable isolation and read-only dirty isolation with 6 databases I would need 12 txTemplates? – Salesgirl

Either that or construct them yourself when needed this would require you to pass in the transactionmanager and set the configuration accordingly. I guess the best solution is by using the AbstractRoutingDataSource that way you can utilize springs declarative transactionmanagement (and in the run cleanup your code). – Gehrke

Answer 3

Define a proxy data source, class being org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy and set the transaction isolation level. Inject actual data source either through setter or constructor.

<bean id="yourDataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
    <constructor-arg index="0" ref="targetDataSource"/>
    <property name="defaultTransactionIsolationName" value="TRANSACTION_READ_UNCOMMITTED"/>
</bean>

Upend

Answer 4

I'm not sure you can do it without working with at the 'Transactional' abstraction-level provided by Spring.

A more 'xml-free' to build your transactionTemplate could be something like this.

private TransactionTemplate getTransactionTemplate(String executionTenantCode, boolean readOnlyTransaction) {
    TransactionTemplate tt = new TransactionTemplate(transactionManager);
    tt.setReadOnly(readOnlyTransaction);
    tt.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);
    tt.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    return tt;
}

In any case I would "leverage" the @Transactional annotation specifying the appropriate transaction manager, binded with a separate data-source. I've done this for a multi-tenant application.

The usage:

@Transactional(transactionManager = CATALOG_TRANSACTION_MANAGER, 
    isolation = Isolation.READ_UNCOMMITTED, 
    readOnly = true)
public void myMethod() {
  //....
}

The bean(s) declaration:

public class CatalogDataSourceConfiguration {
    
    @Bean(name = "catalogDataSource")
    @ConfigurationProperties("catalog.datasource")
    public DataSource catalogDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = ENTITY_MANAGER_FACTORY)
    public EntityManagerFactory entityManagerFactory(
            @Qualifier("catalogEntityManagerFactoryBean") LocalContainerEntityManagerFactoryBean emFactoryBean) {
        return emFactoryBean.getObject();
    }

    @Bean(name= CATALOG_TRANSACTION_MANAGER)
    public PlatformTransactionManager catalogTM(@Qualifier(ENTITY_MANAGER_FACTORY) EntityManagerFactory emf) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(emf);
        return transactionManager;
    }

    @Bean
    public NamedParameterJdbcTemplate catalogJdbcTemplate() {
        return new NamedParameterJdbcTemplate(catalogDataSource());
    }

}

Mannie