Rollback not occurring with Spring + Ibatis

[Marc Novakouski <no...@sei.cmu.edu>]

Hi all,
I am relatively new to the spring world, and have been thrust into maintaining a java application which appears to use Spring 2.0.6 with iBATIS 2.3.  My problem is that while debugging what turned out to be a SQL syntax error, I found what appears to be a transaction failure.  I fixed the SQL issue, but the transaction failure worries me, and any insight on what I'm doing wrong would be much appreciated.

Here is the relevant code:
[CODE]		transactionTemplate.execute(new TransactionCallback() 
		{
            // the code in this method executes in a transactional context
            public Object doInTransaction(TransactionStatus status)     {
				int rowsChangedTotal = 0;
				int rowsChanged = 0;
				Iterator<Question> qIterator = qn.iterator();				
				String userID = null;
				
				// For each question, get the user instance either from answer, tag, or comment 
				// and delete answer,comments, and tags for that user
				if(qn != null && !qn.isEmpty()) {
					for (Question q:qn) { 

						Map<String, String> defaultMap = new HashMap<String, String>();
						defaultMap.put(ENG_ID_CONSTANT, engagementID);
						
						Question qntemp = q;
						userID = doStoreLogicForUserID(qntemp);																
						defaultMap.put(USER_ID_CONSTANT, userID);

						rowsChanged = getSqlMapClientTemplate().delete("deleteTagResponsesFromEngagement", defaultMap);
						rowsChanged = getSqlMapClientTemplate().delete("deleteComments", defaultMap);

						rowsChanged = getSqlMapClientTemplate().delete("deleteCommentResponsesFromEngagement", defaultMap);

						rowsChanged = getSqlMapClientTemplate().delete("deleteAnswerResponsesFromEngagement", defaultMap);
						rowsChanged = rowsChangedTotal;
					}
				}
				
				//now store the new data
				while (qIterator.hasNext())		{
					Question question = qIterator.next();
										
					// Setting the collections of answers, comments, and tags for this question
					List<Tag> tagList = question.getTags();
					Comment comment = question.getComment();
					List<Answer> answerList = question.getAnswers();
					
					processTagsForStoreQuestions(tagList, engagementID, question);
					processCommentsAndAnswersForStoreQuestion(comment, engagementID, answerList, question);					
				}

				// update engagement state				
				setState(engagementID, STORED);
				return null;
            }
		});[/CODE]

A simple explanation of the code's function is that data is first deleted from the DB (MySQL 5.0), and then re-written with the most current data.  The problem I observed is that the 3rd delete statement failed (did not delete any lines when it should have, due to the SQL error), however did not result in a data access exception because the SQL simply returned 0 lines modified.  However, one of the data updates called later on by the processCommentsAndAnswersForStoreQuestion() method Did fail, throwing the following error:

[CODE]
--- The error occurred in edu/cmu/sei/smart/zen/common/core/dao/internal/Server.xml.
--- The error occurred while applying a parameter map.
--- Check the insertCommentInServerDB-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry 'e030f0b2-2a4b-4bf0-bc3b-9b6a89a20d50' for key 1).
[/CODE]

The problem I have is that the transaction is not rolled back.  All data access calls up until the point of the exception are committed to the DB, and that is clearly not what I want.

Now again, I am no expert at this, but the research I've done indicates that when you put business logic within the doInTransaction() method of a TransactionCallback object, it's supposed to provide transaction support (i.e. either all data access calls are made or none are).  It's my understanding that if a RuntimeException is called within the doInTransaction() method, the transaction state is set to rollback only, resulting in the rollback that I want.

So, I did a bit more research and confirmed that the exception I'm getting is in fact Not a RuntimeException, which would explain why the TransactionCallback object is not rolling back the results up to the point of the exception.  So what I did was to encapsulate the entirety of the code within the doInTransaction() method in a try/catch block that caught Any exception, and if that occurs, set the status to rollback only.  I even created a savepoint at the start of the transaction and, if an exception is caught, rolled back to the savepoint.  However, it still doesn't work!  The resulting code is here:

[CODE]
		transactionTemplate.execute(new TransactionCallback() 
		{
            // the code in this method executes in a transactional context
            public Object doInTransaction(TransactionStatus status)     {
            	Object savepoint = status.createSavepoint();
    			try{
    				
					int rowsChangedTotal = 0;
					int rowsChanged = 0;
					Iterator<Question> qIterator = qn.iterator();				
					String userID = null;
					
					if(qn != null && !qn.isEmpty()) {
						for (Question q:qn) { 
	
							Map<String, String> defaultMap = new HashMap<String, String>();
							defaultMap.put(ENG_ID_CONSTANT, engagementID);
							
							Question qntemp = q;
							userID = doStoreLogicForUserID(qntemp);																
							defaultMap.put(USER_ID_CONSTANT, userID);
	
							rowsChanged = getSqlMapClientTemplate().delete("deleteTagResponsesFromEngagement", defaultMap);
	
							rowsChanged = getSqlMapClientTemplate().delete("deleteComments", defaultMap);
	
							rowsChanged = getSqlMapClientTemplate().delete("deleteCommentResponsesFromEngagement", defaultMap);
	
							rowsChanged = getSqlMapClientTemplate().delete("deleteAnswerResponsesFromEngagement", defaultMap);
						}
					}
	
					//now store the new data
					while (qIterator.hasNext())		{
						Question question = qIterator.next();
											
						// Setting the collections of answers, comments, and tags for this question
						List<Tag> tagList = question.getTags();
						Comment comment = question.getComment();
						List<Answer> answerList = question.getAnswers();
						
						processTagsForStoreQuestions(tagList, engagementID, question);
						processCommentsAndAnswersForStoreQuestion(comment, engagementID, answerList, question);					
					}
	
					// update engagement state				
					setState(engagementID, STORED);
    			}
				catch(Exception e){
					System.out.println("Caught exception within storeQuestionsForEngagement(): " + e.getClass().toString());
					e.printStackTrace();
					
					// Set transaction status to rollback
					status.rollbackToSavepoint(savepoint);
					status.setRollbackOnly();
				}
				
				status.releaseSavepoint(savepoint);
				
				return null;
            }
		});
[/CODE]

I have verified when running my test that the exception is in fact caught, thus the savepoint rollback should happen and the status should be set to rollback only; however, I still find that all data access calls up to the point of the exception are committed, resulting in corrupted data.  Any help in terms of why the rollbacks are not happening would be greatly appreciated.  Thanks!


---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org