Help! Transactions not working -- newbie question

[jjasmin]

I'm new to the spring framework, and am liking it so far. However, I cannot get transactions to work for the life of me.... I am trying to use programmatic transactions at the moment, I'll play with the declarative ones later. Here is the code I am using:

	private void sendHeartbeat() {
		PlatformTransactionManager transactionManager = Spring.getTransactionManager();

		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);

		TransactionStatus status = transactionManager.getTransaction(def);

		DataSource ds = Spring.getDataSource();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
		


		jdbcTemplate.update(HEARTBEAT_SQL, new Object[]{NODE_NAME});
		status.setRollbackOnly();
		transactionManager.rollback(status);
		log.info("Rolled back!");
	}

This has been modified several times based on forum messages I've read and what I've gathered from the docs, so it's probably a lot more verbose than it needs to be.

The basi c problem is that even though I am forcing a rollback (remeber, this code is just for testing and learning the framework) the rollback never happens. I always see the results committed to the database.

My relevent configuration is:

	<!-- Hibernate SessionFactory -->
	<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
		<property name="dataSource">
			<ref local="dataSource"/>
		</property>
		<property name="mappingResources">
			<value>/test.hbm.xml</value>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">net.sf.hibernate.dialect.Oracle9Dialect</prop>
			</props>
		</property>
	</bean>

	<!-- Transaction manager for a single Hibernate SessionFactory &#40;alternative to JTA&#41; -->
	<bean id="transactionManager" class="org.springframework.orm.hibernate.HibernateTransactionManager">
		<property name="sessionFactory">
			<ref local="sessionFactory"/>
		</property>
	</bean>

What am I doing wrong?


-- EDIT --

Turned on full debugging, got this as output:

     [java] 10:48:13,516 INFO  [SQLErrorCodesFactory] Looking up default SQLErrorCodes for DataSource
     [java] 10:48:13,516 INFO  [SQLErrorCodesFactory] Database product name found in cache for DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@1911540]. Name is 'Oracle'.
     [java] 10:48:13,522 DEBUG [JdbcTemplate] Executing SQL query [select    node_name from    node_info where    active = 'T'    and rownum = 1 order by   node_info_id ]
     [java] 10:48:13,523 DEBUG [DriverManagerDataSource] Creating new JDBC connection to [jdbc:oracle:thin:@192.168.0.233:1521:meddev]
     [java] 10:48:13,715 INFO  [ClusterNode] Assuming role of master node
     [java] 10:48:18,723 DEBUG [DefaultListableBeanFactory] Returning cached instance of singleton bean 'transactionManager'
     [java] 10:48:18,725 DEBUG [HibernateTransactionManager] Using transaction object [org.springframework.orm.hibernate.HibernateTransactionObject@1be2893]
     [java] 10:48:18,726 DEBUG [HibernateTransactionManager] Creating new transaction
     [java] 10:48:18,726 DEBUG [SessionFactoryUtils] Opening Hibernate session
     [java] 10:48:18,726 DEBUG [HibernateTransactionManager] Opened new session [net.sf.hibernate.impl.SessionImpl@159e154] for Hibernate transaction
     [java] 10:48:18,726 DEBUG [HibernateTransactionManager] Exposing Hibernate transaction as JDBC transaction [C3P0ProxyConnection [Invocation Handler: com.mchange.v2.c3p0.impl.C3P0PooledConnection$ProxyConnectionInvocationHandler@1d87b85]]
     [java] 10:48:18,727 DEBUG [TransactionSynchronizationManager] Bound value [org.springframework.jdbc.datasource.ConnectionHolder@1958bf9] for key [com.mchange.v2.c3p0.ComboPooledDataSource@a522a6] to thread [Thread-5]
     [java] 10:48:18,727 DEBUG [TransactionSynchronizationManager] Bound value [org.springframework.orm.hibernate.SessionHolder@118958e] for key [net.sf.hibernate.impl.SessionFactoryImpl@19fe451] to thread [Thread-5]
     [java] 10:48:18,727 DEBUG [TransactionSynchronizationManager] Initializing transaction synchronization
     [java] 10:48:18,727 DEBUG [DefaultListableBeanFactory] Returning cached instance of singleton bean 'dataSource'
     [java] 10:48:18,727 INFO  [SQLErrorCodesFactory] Looking up default SQLErrorCodes for DataSource
     [java] 10:48:18,727 INFO  [SQLErrorCodesFactory] Database product name found in cache for DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@1911540]. Name is 'Oracle'.
     [java] 10:48:18,728 DEBUG [JdbcTemplate] Executing SQL update [update    node_info set    last_heartbeat = sysdate where    node_info_id in (       select          max(node_info_id)       from          node_info       where          active='T'          and node_name = ?    )    and rownum = 1 ]
     [java] 10:48:18,728 DEBUG [DriverManagerDataSource] Creating new JDBC connection to [jdbc:oracle:thin:@192.168.0.233:1521:meddev]
     [java] 10:48:18,881 DEBUG [DataSourceUtils] Registering transaction synchronization for JDBC connection
     [java] 10:48:18,882 DEBUG [TransactionSynchronizationManager] Bound value [org.springframework.jdbc.datasource.ConnectionHolder@102b2b6] for key [org.springframework.jdbc.datasource.DriverManagerDataSource@1911540] to thread [Thread-5]
     [java] 10:48:18,882 DEBUG [TransactionSynchronizationManager] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@102b2b6] for key [org.springframework.jdbc.datasource.DriverManagerDataSource@1911540] bound to thread [Thread-5]
     [java] 10:48:18,882 DEBUG [StatementCreatorUtils] Setting SQL statement parameter value; columnIndex=1, parameter value='Test Node', valueClass=java.lang.String, sqlType=unknown
     [java] 10:48:18,885 DEBUG [JdbcTemplate] SQL update affected 1 rows
     [java] 10:48:18,887 INFO  [ClusterNode] Rolled back!
     [java] 10:48:18,888 DEBUG [HibernateTransactionManager] Transactional code has requested rollback
     [java] 10:48:18,888 DEBUG [HibernateTransactionManager] Triggering beforeCompletion synchronization
     [java] 10:48:18,888 DEBUG [TransactionSynchronizationManager] Removed value [org.springframework.jdbc.datasource.ConnectionHolder@102b2b6] for key [org.springframework.jdbc.datasource.DriverManagerDataSource@1911540] from thread [Thread-5]
     [java] 10:48:18,921 DEBUG [HibernateTransactionManager] Initiating transaction rollback
     [java] 10:48:18,921 DEBUG [HibernateTransactionManager] Rolling back Hibernate transaction on session [net.sf.hibernate.impl.SessionImpl@159e154]
     [java] 10:48:18,921 DEBUG [HibernateTransactionManager] Triggering afterCompletion synchronization
     [java] 10:48:18,922 DEBUG [TransactionSynchronizationManager] Clearing transaction synchronization
     [java] 10:48:18,922 DEBUG [TransactionSynchronizationManager] Removed value [org.springframework.orm.hibernate.SessionHolder@118958e] for key [net.sf.hibernate.impl.SessionFactoryImpl@19fe451] from thread [Thread-5]
     [java] 10:48:18,922 DEBUG [TransactionSynchronizationManager] Removed value [org.springframework.jdbc.datasource.ConnectionHolder@1958bf9] for key [com.mchange.v2.c3p0.ComboPooledDataSource@a522a6] from thread [Thread-5]
     [java] 10:48:18,922 DEBUG [HibernateTransactionManager] Closing Hibernate session [net.sf.hibernate.impl.SessionImpl@159e154] after transaction
     [java] 10:48:18,922 DEBUG [SessionFactoryUtils] Closing Hibernate session
     [java] 10:48:18,923 DEBUG [DefaultListableBeanFactory] Returning cached instance of singleton bean 'dataSource'

It appears that the transaction is trying to get rolled back, but I don't see it happening....???

[jjasmin]

This is driving me crazy.

Spent some time debugging the spring data access code, and I see that in the middle of JdbcTemplate.execute(PreparedStatementCreator psc, PreparedStatementCallback action) that my datasource has autocommit = true! But, it was my understanding that the transaction layer would automatically set autocommit to false.

I turned more debug output on, and I can see that the HibernateTransactionManager says that it turns autocommit off

2004-10-15 13:43:14,243 DEBUG [net.sf.hibernate.transaction.JDBCTransaction] begin
2004-10-15 13:43:14,243 DEBUG [net.sf.hibernate.transaction.JDBCTransaction] current autocommit status:true
2004-10-15 13:43:14,243 DEBUG [net.sf.hibernate.transaction.JDBCTransaction] disabling autocommit
2004-10-15 13:43:14,246 DEBUG [org.springframework.orm.hibernate.HibernateTransactionManager] Exposing Hibernate transaction as JDBC transaction [C3P0ProxyConnection [Invocation Handler: com.mchange.v2.c3p0.impl.C3P0PooledConnection$ProxyConnectionInvocationHandler@b32ed4]]
2004-10-15 13:43:14,247 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Bound value [org.springframework.jdbc.datasource.ConnectionHolder@1c7980c] for key [com.mchange.v2.c3p0.ComboPooledDataSource@a522a6] to thread [Thread-5]
2004-10-15 13:43:14,247 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Bound value [org.springframework.orm.hibernate.SessionHolder@1077fc9] for key [net.sf.hibernate.impl.SessionFactoryImpl@cf710e] to thread [Thread-5]
2004-10-15 13:43:14,247 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Initializing transaction synchronization
2004-10-15 13:43:14,277 DEBUG [org.springframework.beans.factory.support.DefaultListableBeanFactory] Returning cached instance of singleton bean 'dataSource'
20
...
...
...

2004-10-15 13:43:15,975 DEBUG [net.sf.hibernate.transaction.JDBCTransaction] rollback
2004-10-15 13:43:15,976 DEBUG [net.sf.hibernate.impl.SessionImpl] transaction completion
2004-10-15 13:43:15,976 DEBUG [net.sf.hibernate.transaction.JDBCTransaction] re-enabling autocommit

So my thought would be I am still doing something wrong and getting an incorrect data source somehow -- But I am just not seeing it.... I hope someone can tell me what I'm doing wrong here.

Thanks

[jjasmin]

Could this be the problem?

2004-10-15 14:04:51,457 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Bound value [org.springframework.jdbc.datasource.ConnectionHolder@7244ca] for key [com.mchange.v2.c3p0.ComboPooledDataSource@1e0512a] to thread [Thread-5]
2004-10-15 14:04:51,457 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Bound value [org.springframework.orm.hibernate.SessionHolder@17f11fb] for key [net.sf.hibernate.impl.SessionFactoryImpl@23756] to thread [Thread-5]
2004-10-15 14:04:51,457 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Initializing transaction synchronization

....
....
....

2004-10-15 14:04:53,005 DEBUG [org.springframework.jdbc.core.JdbcTemplate] Executing SQL update [INSERT into NODE_INFO (NODE_NAME, LAST_HEARTBEAT) values (?, sysdate) ]
2004-10-15 14:04:53,008 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@457d21] for key [org.springframework.jdbc.datasource.DriverManagerDataSource@111bfbc] bound to thread [Thread-5]
2004-10-15 14:04:53,231 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@457d21] for key [org.springframework.jdbc.datasource.DriverManagerDataSource@111bfbc] bound to thread [Thread-5]
2004-10-15 14:04:53,234 DEBUG [org.springframework.jdbc.core.StatementCreatorUtils] Setting SQL statement parameter value; columnIndex=1, parameter value='Test Node', valueClass=java.lang.String, sqlType=unknown
2004-10-15 14:04:53,451 DEBUG [org.springframework.jdbc.core.JdbcTemplate] SQL update affected 1 rows

Note that the values retrieved from the cache are NOT the same as the ones entered....

[jjasmin]

OK -- getting somewhere.

First off, I was looking in the wrong config file... :oops: I saw that I had connection pooling via c3p0. I changed the config to NOT use connection pooling, and rollbacks work properly. So that leads to the (hopefully) final question -- Given the fact that this code works

	private void sendHeartbeat() {
		PlatformTransactionManager transactionManager = Spring.getTransactionManager();
		TransactionTemplate tt = new TransactionTemplate(transactionManager);
		tt.execute(new TransactionCallbackWithoutResult() {
			protected void doInTransactionWithoutResult(TransactionStatus status) {
				DataSource ds = Spring.getDataSource();
				JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
				jdbcTemplate.update(HEARTBEAT_SQL, new Object[]{NODE_NAME});
				status.setRollbackOnly();
				log.info("Rolled back!");
			}
		});
	}

when NOT using c3p0 connection pooling, but DOES NOT work when pooling is active -- what am I doing wrong?

[Colin Sampaleanu]

Hibernate _does_ explicitly turn off auto-commit when a transaction is started. In the non-Hibernate case, with Spring's DataSourceTransactionManager, Spring itself will turn-off auto-commit.

To be honest I've always used dbcp over c3po when not using JTA, but I would guess you should look at yur c3po config and see if you can set it up to turn off auto-commit. It may be overriding things somehow...

[jjasmin]

I switched to use DBCP instead of C3P0, but rollbacks still don't work for me.

My config is:

	<bean id="dbcpDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName">
			<value>$&#123;jdbc.driverClassName&#125;</value>
		</property>


		<property name="maxActive">
			<value>100</value>
		</property>
		<property name="maxIdle">
			<value>30</value>
		</property>
		<property name="maxWait">
			<value>100</value>
		</property>
		<property name="url">
			<value>$&#123;jdbc.url&#125;</value>
		</property>
		<property name="username">
			<value>$&#123;jdbc.username&#125;</value>
		</property>
		<property name="password">
			<value>$&#123;jdbc.password&#125;</value>
		</property>
	</bean>

<!-- Hibernate SessionFactory -->
	<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
		<property name="dataSource">
			<ref local="dbcpDataSource"/>
		</property>
		<property name="mappingResources">
			<value>/medware.hbm.xml</value>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">net.sf.hibernate.dialect.Oracle9Dialect</prop>
			</props>
		</property>
	</bean>

	<!-- Transaction manager for a single Hibernate SessionFactory &#40;alternative to JTA&#41; -->
	<bean id="transactionManager" class="org.springframework.orm.hibernate.HibernateTransactionManager">
		<property name="sessionFactory">
			<ref local="sessionFactory"/>
		</property>
	</bean>

and the code in question is:

    private void sendHeartbeat() {
        log.debug("sendHeartbeat()");
        PlatformTransactionManager transactionManager = Spring.getTransactionManager();
        TransactionTemplate tt = new TransactionTemplate(transactionManager);
        tt.execute(new TransactionCallbackWithoutResult() {
            protected void doInTransactionWithoutResult(TransactionStatus status) {
                DataSource ds = Spring.getDataSource();
                JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
                jdbcTemplate.update(HEARTBEAT_SQL, new Object[]{NODE_NAME});
				status.setRollbackOnly();
				log.info("Rolled back!");
            }
        });
    }

I think the problem may be with the

DataSource ds = Spring.getDataSource();

line inside of my function -- I ask for the datasource bean so that I can use it in the template -- Is this what I should be doing? I suspect I am getting a new connection from the pool instead of using the one in the transaction, but how do I get the "proper" one to pass to the JDBCTemplate?

[Colin Sampaleanu]

What is the 'Spring' class that you are using? Is it a singleton of some sort?

As long as you are creating the jdbcTemplate specifying the same datasource that is being fed to the Hibernate session factory, then you will in fact share the same connection. The Hibernate classes do expose the connection on the current thread, syncrhonized to the transaction, and the jdbc classes will key off the datasource object to get the existing connection...

[jjasmin]

AHA! That was it! My 'Spring' class is a simple wrapper around common operations, and the getDataSource() function was returning the original, non-pooled datasource, so it didn't know about the transaction. I feel stupid now Thanks so much for the help, I was losing my mind