DataSourceTransactionManager wont rolling back

[muthukumaran.m@gmail.com]

I am using DataSourceTransactionManage for writing into two tables. I made the connection object null before I insert into the second table to see if the operation rolls back , but the insert into the first table wont rollback.

   <bean id="TestDS" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close" scope="singleton">
		<property name="connectionCachingEnabled" value="true" />
		<property name="URL">
		         <value>jdbc:oracle:thin:@10.8.237.211:1521:orcl</value>
		    </property>
		<property name="user">
		         <value>Muthu_TWE82_Sep27</value>
		    </property>
		<property name="password">
		         <value>Muthu_TWE82_Sep27</value>
		    </property>
   	<property name="connectionProperties">
		<value>
				autoCommit:false
		      </value>
		   </property>
		<property name="connectionCacheProperties">
		      <value>
				MinLimit:20
				MaxLimit:150
				InitialLimit:5
				ConnectionWaitTimeout:10
				InactivityTimeout:180
				ValidateConnection:false
		      </value>
		   </property>
	</bean>

	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		  <property name="dataSource" ref="TestDS"/>
	</bean>

Here is the java code to

Connection con = SpringUtil.getInstance().getConnection("TestDS");
insertTable1(con);
con=null;
insertTable2(con);

I did a remote debug from Eclipse and found that object ID of the connection object in "doBegin()" & "doRollback" of DataSourceTransactionManager is not the same as the object ID of the connection object that I get in the java code using the datasource.

I think that this is the reason that the insert operation are not rolled back as the rollback is called on a difference connection object.

I also tried the scope="singleton" but it doesn't help.

Please share you suggestions.

[muthukumaran.m@gmail.com]

After posting on this thread, I realized that I missed one of the important point mentioned in the Spring Doc.

Application code is required to retrieve the JDBC Connection via DataSourceUtils.getConnection(DataSource) instead of a standard J2EE-style DataSource.getConnection() call. Spring classes such as JdbcTemplate use this strategy implicitly. If not used in combination with this transaction manager, the DataSourceUtils lookup strategy behaves exactly like the native DataSource lookup; it can thus be used in a portable fashion.

That's why they always say read the doc first..