Announcement Announcement Module
Collapse
No announcement yet.
Early commit and no rollback for nested transactions - Any Ideas? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • #3
      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:
      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

      Comment


      • #4
        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.

        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;
        		}
        	}

        Comment

        Working...
        X