Using DataSourceTransactionManager

[EnricoDP]

Hi all,

I'm currently working on ensuring transaction safety for database operations in an larger business software.
For reasonably resons I decided to implement the programmatic approach using the DataSourceTransactionManager from Spring.
Everything seems to work fine except for that (e.g.) my insert operations aren't using the same connection that the transaction holds. Thus I got the same behavior as if using no transaction management at all. Btw connections are managed by a connection pool.
I'll provide some pseudo code to clarify my problem:

public class DataRecordDAO extends JdbcTemplate{

  ...
  private DataSourceTransactionManager txManager;

  //sets datasource for DataRecordDAO
  public void setDataSource(DataSource paramDataSource){
    ...
  }

  public void setTransactionManager(){
    txManager = new DataSourceTransactionManager(this.dataSource);
  }

  ...

  public boolean insert(some parameters){

    this.setTransactionManager();
    DefaultTransactionDefinition txDef = new DefaultTransactionDefinition();
    Transactionstatus txStatus = txManager.getTransaction(txDef);

    try{
    - some business logic preparing an update string - 

    update(updateString);

      if(something is true){

        - some business logic preparing an update string - 

        update(updateString);
      }

    txManager.commit();
    } catch(some Exception occured){
         txManager.rollback(txStatus);
    }
  }
}

While debugging my code step by step i can verify that the transaction is fetching a connection from the pool as well as any update operation is fetching an connection from the pool too. Thus the transaction object(respectively the connection that transaction holds) is used at no time.
Am I missing something or is there some error in resoning in my code?

Thank you for your help in advance
Enrico

[Marten Deinum]

Your code is flawed... Simply inject the transactionamanger and don't reconstruct it on each and every method invocation.

Also if you do this in multiple methods you get multiple transactions and not a single transaction spanning all the methods (so no ACID or single unit of work but multiple).

[EnricoDP]

Thank your for your advice you are right.

But injection of the transactionmanager is not the favoured way (though it's common spring practice).
Could this (not injecting through a bean) be the reason for having a different connection object for the transaction and each single db operation or is it just because of the flawed code?

[Marten Deinum]

Why isn't it favoured? Creating a new instance will also make it not detect ongoing transactions etc. I also wonder why you aren't simply using the declarative approach (although you say you have reasons to do so). In general doing programmatic tx management makes code only harder to maintain, produces more errors. etc.