Early commit and no rollback for nested transactions - Any Ideas?

[ksevern]

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:

	<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:

	@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:

	@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

[rishishehrawat]

This will not work as you are manging the connections. You will have to use Spring's JdbcTemplate. Using JdbcTemplate will remove all the error prone boiler plate code & it will also manage transactions & resources for you.

[ksevern]

rishishehrawat,

Thank you for your response! I really appreciate it!

Your comment regarding connections got me thinking. I wasn't initially worried about handling connections because of what I read in http://static.springsource.org/sprin...ronization-low. That indicates that I don't absolutely have to use Spring's JDBCTemplate (I'm not disputing it's use, I just have have requirements that don't allow me to do that yet.)

Anyway, thinking that there was some way to do what I was trying without Spring's JDBCTemplate, I altered my methods so that the parent method was handling one connection and passing that same connection to all the "child" methods. I also removed @Transactional from the child methods. That does work. I had to move all the code into the DAO because I didn't want to be dealing with connections outside it.

Bean Code:

	@Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
	public void testTransactional()
	throws Exception
	{
		Connection conn = null;
		try
		{
			conn = DataSourceUtils.doGetConnection(dataSource);
			String appName = "iv";
			//	Get Record and calculate next ID
			String baseId = "DeleteMeFolder";
			DBHelpContents hc = testTransGetHelpContents(appName, conn);
			int ordering = hc.getOrdering();
			ordering++;
			String newHelpId = baseId + ordering;		
			
			//	Insert in Help Contents
			DBHelpContents newHc = testTransInsertHelpContents(appName, newHelpId, hc.getTopicId(), appName, ordering, conn);
			
			//	Throw exception
			if (true) throw new Exception("testing transactional");
			
			//	Insert into related topic
			DBHelpRelatedTopic rt = testTransInsertRelatedTopic(appName, newHelpId, baseId, conn);
		}
		catch (SQLException ex)
		{
			Log.error("HelpDBDAO.testTransactional", ex);
			throw ex;
		}
		finally
		{
			Database.closeConnection(conn);
		}
	}
	
	public DBHelpContents testTransGetHelpContents(
			String		appName,
			Connection	conn)
	throws Exception
	{
		String sql = "select * from help_contents where app_name = '" + appName + "' and parent_id = '" + appName + "' and help_id like 'DeleteMeFolder%' order by ordering";
		ResultSet rs = null;
		PreparedStatement stmt = null;
		DBHelpContents hc = null;
		try
		{
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next())
			{
				hc = decodeDBHelpContents(rs);
			}
		}
		catch (SQLException ex)
		{
			Log.error("HelpDBDOA.testTransGetHelpContents", ex);
			throw ex;
		}
		finally
		{
			if (null != stmt) { try { stmt.close(); } catch(Exception e) {} }
			if (null != rs) { try { rs.close(); } catch(Exception e) {} }
		}
		return hc;
	}
	
	public DBHelpContents testTransInsertHelpContents(
			String	appName,
			String	helpId,
			long	topicId,
			String	parentId,
			int		ordering,
			Connection	conn)
	throws Exception
	{
		DBHelpContents hc = null;
		String sql = "insert into help_contents (app_name, help_id, topic_id, parent_id, ordering) values (?, ?, ?, ?, ?)";
		PreparedStatement stmt = null;
		try
		{
			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
		{
			if (null != stmt) { try { stmt.close(); } catch(Exception e) {} }
		}
		return hc;
	}

	public DBHelpRelatedTopic testTransInsertRelatedTopic(
			String	appName,
			String	helpId,
			String	relatedHelpId,
			Connection	conn)
	throws Exception
	{
		DBHelpRelatedTopic rt = null;
		String sql = "insert into help_related_topic (app_name, help_id, related_help_id) values (?, ?, ?)";
		PreparedStatement stmt = null;
		try
		{
			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
		{
			if (null != stmt) { try { stmt.close(); } catch(Exception e) {} }
		}
		return rt;
	}

Thanks for your help!
Ken

[ksevern]

Looks like I need one more adjustment. I removed the connection closing call and the exception throwing. Now the transaction commits, if completed successfully, but not until.

	@Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
	public void testTransactional()
	throws Exception
	{
		Connection conn = null;
		try
		{
			conn = DataSourceUtils.doGetConnection(dataSource);
			String appName = "iv";
			//	Get Record and calculate next ID
			String baseId = "DeleteMeFolder";
			DBHelpContents hc = testTransGetHelpContents(appName, conn);
			int ordering = hc.getOrdering();
			ordering++;
			String newHelpId = baseId + ordering;		
			
			//	Insert in Help Contents
			DBHelpContents newHc = testTransInsertHelpContents(appName, newHelpId, hc.getTopicId(), appName, ordering, conn);
			
			//	Throw exception
//			if (true) throw new Exception("testing transactional");
			
			//	Insert into related topic
			DBHelpRelatedTopic rt = testTransInsertRelatedTopic(appName, newHelpId, baseId, conn);
		}
		catch (SQLException ex)
		{
			Log.error("HelpDBDAO.testTransactional", ex);
			throw ex;
		}
	}