Early commit and no rollback for nested transactions - Any Ideas?
I'm using jdbc and oracle with Spring 3.0.5. I created some fairly simplified test methods to debug this. I know the methods are getting wrapped because I see the Spring wrapper classes when I debug into the transactional methods. I believe I'm getting the same connection from DataSourceUtils.doGetConnection(dataSource) because I'm checking the id in the debugger. Here's my test code:
Config Snippet:
Code:
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean" scope="singleton">
<property name="jndiName" value="java:comp/env/jdbc/Oracle"/>
<property name="resourceRef" value="true" />
</bean>
<bean id="helpDAO" class="com.xactsites.help.repository.HelpDBDAO" scope="singleton">
<property name="dataSource" ref="dataSource" />
</bean>
<tx:annotation-driven />
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
Services Class Snippet:
Code:
@Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
public void testTransactional()
throws Exception
{
String appName = "iv";
// Get Record and calculate next ID
String baseId = "DeleteMeFolder";
DBHelpContents hc = helpDAO.testTransGetHelpContents(appName);
int ordering = hc.getOrdering();
ordering++;
String newHelpId = baseId + ordering;
// Insert in Help Contents
DBHelpContents newHc = helpDAO.testTransInsertHelpContents(appName, newHelpId, hc.getTopicId(), appName, ordering);
// Throw exception
if (true) throw new Exception("testing transactional");
// Insert into related topic
DBHelpRelatedTopic rt = helpDAO.testTransInsertRelatedTopic(appName, newHelpId, baseId);
}
DAO Snippet:
Code:
@Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
public DBHelpContents testTransGetHelpContents(
String appName)
throws Exception
{
String sql = "select * from help_contents where app_name = '" + appName + "' and parent_id = '" + appName + "' and help_id like 'DeleteMeFolder%' order by ordering";
Connection conn = null;
ResultSet rs = null;
PreparedStatement stmt = null;
DBHelpContents hc = null;
try
{
conn = DataSourceUtils.doGetConnection(dataSource);
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
while (rs.next())
{
hc = decodeDBHelpContents(rs);
}
}
catch (SQLException ex)
{
Log.error("HelpDBDOA.testTransGetHelpContents", ex);
throw ex;
}
finally
{
// Connection closing handled by Transaction management code
if (null != stmt) { try { stmt.close(); } catch(Exception e) {} }
if (null != rs) { try { rs.close(); } catch(Exception e) {} }
}
return hc;
}
@Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
public DBHelpContents testTransInsertHelpContents(
String appName,
String helpId,
long topicId,
String parentId,
int ordering)
throws Exception
{
DBHelpContents hc = null;
String sql = "insert into help_contents (app_name, help_id, topic_id, parent_id, ordering) values (?, ?, ?, ?, ?)";
Connection conn = null;
PreparedStatement stmt = null;
try
{
conn = DataSourceUtils.doGetConnection(dataSource);
stmt = conn.prepareStatement(sql);
int i = 1;
stmt.setString(i++, appName);
stmt.setString(i++, helpId);
stmt.setLong(i++, topicId);
stmt.setString(i++, parentId);
stmt.setInt(i++, ordering);
int rowsUpdated = stmt.executeUpdate();
if (rowsUpdated != 1)
{
throw new Exception("Insert sql=["+sql+"] inserted " + rowsUpdated + " rows.");
}
hc = new DBHelpContents();
hc.setAppName(appName);
hc.setHelpId(helpId);
hc.setTopicId(topicId);
hc.setParentId(parentId);
hc.setOrdering(ordering);
}
catch (Exception ex)
{
Log.error("HelpDBDOA.testTransInsertHelpContents", ex);
throw ex;
}
finally
{
// Connection closing handled by Transaction management code
if (null != stmt) { try { stmt.close(); } catch(Exception e) {} }
}
return hc;
}
@Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
public DBHelpRelatedTopic testTransInsertRelatedTopic(
String appName,
String helpId,
String relatedHelpId)
throws Exception
{
DBHelpRelatedTopic rt = null;
String sql = "insert into help_related_topic (app_name, help_id, related_help_id) values (?, ?, ?)";
Connection conn = null;
PreparedStatement stmt = null;
try
{
conn = DataSourceUtils.doGetConnection(dataSource);
stmt = conn.prepareStatement(sql);
int i = 1;
stmt.setString(i++, appName);
stmt.setString(i++, helpId);
stmt.setString(i++, relatedHelpId);
int rowsUpdated = stmt.executeUpdate();
if (rowsUpdated != 1)
{
throw new Exception("Insert sql=["+sql+"] inserted " + rowsUpdated + " rows.");
}
rt = new DBHelpRelatedTopic();
rt.setAppName(appName);
rt.setHelpId(helpId);
rt.setRelatedHelpId(relatedHelpId);
}
catch (Exception ex)
{
Log.error("HelpDBDOA.testTransInsertHelpContents", ex);
throw ex;
}
finally
{
// Connection closing handled by Transaction management code
if (null != stmt) { try { stmt.close(); } catch(Exception e) {} }
}
return rt;
}
What's happening is that the individual inserts are committed immediately and not waiting until the transaction completes. Also, when I throw the exception the inserts are not rolled back (I don't expect them to be, because they were already committed). Any ideas how I can get this to behave as a transaction? I'm sure I'm messing something up, but I can't see it yet.
Much thanks in advance!
Ken
