unique key constraint violation causes unexpectedrollbackexception

[joost de vries]

Hi,

I'm quite stuck with the following problem:

When the database throws an SqlException, f.i. because of a unique key constraint violation, this causes an UnexpectedRollbackException in Spring TransactionInterceptor.
This is problematic because the original causal exception gets lost. This means that it is very hard to track bugs and to handle exceptions correctly.

It may be that this problem has something to do with our appserver but I'd appreciate feedback that points out the way this kind of exception should be handled by the Spring transactioninterceptor. If that is clear to me I can point out the problem in the correct way to my appserver vendor.

Where should a unique constraint violation SqlException be handled in org.springframework.transaction.interceptor.Transa ctionInterceptor?
In our case it bubbles up at the statement marked *4*

//from Spring TransactionInterceptor
	public Object invoke(MethodInvocation invocation) throws Throwable {
//snip comment
		Class targetClass = (invocation.getThis() != null) ? invocation.getThis().getClass() : null;
		
		// Create transaction if necessary.
		TransactionInfo txInfo = createTransactionIfNecessary(invocation.getMethod(), targetClass);

		Object retVal = null;
		try {
//snip comment
			retVal = invocation.proceed(); //*1*
		}
		catch (Throwable ex) {
			// target invocation exception
			doCloseTransactionAfterThrowing(txInfo, ex); // *2*
			throw ex;
		}
		finally {
			doFinally(txInfo); //*3*
		}
		doCommitTransactionAfterReturning(txInfo);  //*4*
		return retVal;
	}

We're using Spring 1.2.6 declarative transactionhandling. (org.springframework.transaction.interceptor.Trans actionInterceptor)
The appserver Oracle AS 10.1.2 provides the TransactionManager.
Toplink 10.1.2 provides ORM and taps into the appserver transactionmanager.

[joost de vries]

We're using JTATransactionManager by the way.

[Loren Rosen]

DBMS's vary as to whether a constraint violation comes back on the insert (or update) or on the subsequent commit. In fact IIRC with Oracle (the database, not the app server) this can be configured. There are several reasons why having the violation arise on the commit might be preferable.

However it does sound like there's a small Spring bug since it should in fact be expected for the constraint error and rollback to occur on commit. You might try the latest build of Spring 1.3 -- at least in some places it does a better job of reporting nested exceptions.

[joost de vries]

Hi Loren,

Our RDBMS is Oracle too. So I'd be curious as to how you'd configure at what time constraint exceptions are thrown.

I agree that it is strange that the TransactionInterceptor does not expect an exception at this point. But looking at the Spring code I find it hard to tell what the right dataflow would be.

But I think it is the appservers TransactionManagers fault for throwing away the information about the causal exception. I'll take that question to the vendors forum.

We're using BMT sessionbeans by the way.

cheers,
Joost

[joost de vries]

We're using emulated datasources by the way. But using other datasources did not change the exception throwing behaviour.

[Loren Rosen]

Do a web search on
oracle deferred constraint
and you'll find a lot of info on this. Or lookup "deferred constraint" on OTN. As I recall the default is for the constraint to not be deferred. So it seems that someone has already explicited deferred them. Or something else is going on.

[joost de vries]

Loren,

Deferred constraints are not going to make a difference to my problem. If anything they will only make the exception be triggered even later. When the problem is that the exception gets triggered at a moment that does not expect anymore exceptions to occur.

groetjes,
Joost

[Loren Rosen]

My speculation was that you already have deferred constraints, perhaps unknowingly. Perhaps set by your DBA or the DBA who left last year or something of that sort. I can't say whether this is plausible in your situation or not.

[joost de vries]

Ok, I see what you mean. That's an interesting suggestion.
I'll look into it wether the unexpectedrollbackexception correlates with this.

But then still it should not result in an unexpectedrollbackexception since deferred constraints can be expected.

I'll try this out and post the results here.

[joost de vries]

It seems that indeed only deferred constraints lead to this behaviour.
So thanks for the suggestion.
Still, we need the deferred constraints for our ORM. So I guess this is a bug in Spring then, that this causes an UnexpectedRollbackException?