How do I configure transactions as RollbackOnly?

[tbroyer]

We're writing an ASP.NET 2.0 application where logic is in stored procedures in an Oracle DB (to allow reuse between our ASP.NET app, a client-server PowerBuilder app and sometimes SQL*C programs exec'd by a cron).

We have a case where the SP works on "trees of objects" so, for our ASP.NET app in which the DB connection doesn't span multiple HTTP requests, we store an "incomplete tree of objects" (using NHibernate), then call the SP and finally get back the "completed tree of objects". This "tree" is then presented to the user (stored in ViewState) and saved back to the DB if he clicks "OK".
The "save/exec/retrieve" steps must be run in a transaction which is rollback'd in every case (the client-server app, which maintains an open connection can do a "begin transaction, save, exec SP" followed by either a commit or rollback depending on the user's choice; we can't do it, so we always rollback, then eventually save the result back to DB if the user wants to).

I've seen that transactions can be marked "RollbackOnly" but didn't find how to configure them as such. I know I could manage transactions in my NHibernate code but I'd like to use Spring's AOP framework (TransactionInterceptor, etc.) Can somebody enlighten me?
...or propose another way to deal with our problem...

[Mark Pollack]

Hi,

Are you using Spring's NHibernate support? I'm not that clear on the use-case, specifically "we can't do it, so we always rollback, then eventually save the result back to DB if the user wants to)."

Nevertheless, the generic explanation of how you can trigger a rollback of a transaction is either programmatically or by throwing an exception. The latter approach is generally preferred as it does not tie your code to Spring's transaction infrastructure. See the docs, section 14.5 for a high level description.

The default configuration of the transaction infrastructure is to rollback when any exception is thrown inside a transaction. If in your logic you can throw an exception for this case that would trigger the rollback. It sounds like you are using the same code from a client-server app (winform) as well as from an ASP.NET app. In this case maybe the implementation would be to check for some object in thread local storage, see Spring's LogicalThreadContext, that is set only in the web tier in a high level custom module or other code location. The programmatic way would be to call TransactionAspectSupport.CurrentTransactionStatus. RollbackOnly = true.


The transactional attribute has the properties, RollbackFor and NoRollbackFor, which take an array of types that must cause rollback or must not cause rollback respectfully. The Transaction QuickStart in the distribution shows usage of the NoRollbackFor, see AccountManagerTests.cs and the corresponding AccountManager class.

If you are not using the transactional attribute, but XML, the configuration is like this,


<property name="TransactionAttributes">
<name-values>
<add key="Delete*" value="PROPAGATION_REQUIRED, +ExceptionTypeToCommitOn"/>
<add key="Create*" value="PROPAGATION_REQUIRED, -ExceptionTypeToRollbackOn"/>
</name-values>
</property>


The exception name after the plus and minus is handled by looking for a name match in the thrown exception rather than check the specific type. It is easy to specify the full type when using the attribute but can be tedious to list the fully qualified name the string/xml approach. Generally speaking you shouldn't run into problems using jus the name instead of the fully qualified name.

A full blown example of the string used in the XML config that gets parsed to determine the various tx settings is


PROPAGATION_MANDATORY,ISOLATION_REPEATABLEREAD,tim eout_10,-DataException,+RemotingException


taken from the test case....

Hope this helps.

Cheers,
Mark

[tbroyer]

Are you using Spring's NHibernate support?

Sorry if I wasn't clear. Yes, we're using Spring.Data.NHibernate12.

I'm not that clear on the use-case, specifically "we can't do it, so we always rollback, then eventually save the result back to DB if the user wants to)."

The scenario is, basically: we insert a new record into table A, then call the SP with the record's primary key, the SP fills table B.
We have to retrieve the computed data (select from B where A_ID = "X") and ask the user if he's OK. The user either confirms (data should be recorded in tables A and B) or cancels (tables A and B must be as they were before we did anything).

In the Windows app, it's as simple as "if user confirms then commit, otherwise rollback".

In the ASP.NET app, we don't maintain transactions and connections across postbacks, so we have to select the computed data from B and rollback the transaction (always). The data is shown to the client and the request terminates. If the user confirms (postback, new HTTP request), we insert the data back into tables A and B, otherwise we do nothing (data has already been removed from tables A and B by the previous rollback).

Nevertheless, the generic explanation of how you can trigger a rollback of a transaction is either programmatically or by throwing an exception. The latter approach is generally preferred as it does not tie your code to Spring's transaction infrastructure. [...] If in your logic you can throw an exception for this case that would trigger the rollback.

We have to return some data, so I can't throw an exception.

We could, but we'd then have to create a class derived from Exception with a property to carry our data.
That really doesn't seem a good design, since the rollback in our case is the normal behavior, not an "exception".

It sounds like you are using the same code from a client-server app (winform) as well as from an ASP.NET app.

Actually not, the shared code is the SP.

The programmatic way would be to call TransactionAspectSupport.CurrentTransactionStatus. RollbackOnly = true.

Do you mean injecting the TransactionInterceptor into the service class being "intercepted", so it can set RollbackOnly to true from within the "compute data" method?

It's not really clear to me how Spring's transaction infrastructure is architectured; from within an HibernateDelegate (HibernateTemplate.Execute(...)), can ISession.Transaction.Rollback() be used?

[Mark Pollack]

Hi,

Thanks for the explination, I see what you are doing now. I don't know if you can change the design, but I'd try to go in the direction of having a different stored proc, or modify the current one with an additional parameters, such that in this case you could put the results of the computation in a temporary table and return the information from that table from within the stored proc. Basically any strategy that would make 'rollback is normal behavior' go away is preferred, but I'm sure you know this and are trying to work with what you got - there must be alot of inertia to get mods to these stored procs.

In anycase,
Do you mean injecting the TransactionInterceptor into the service class being "intercepted", so it can set RollbackOnly to true from within the "compute data" method?

No, you don't have to modify the code that much. The methods on TransactionAspectSupport are static so the one-liner I listed before can be cut-n-pasted verbatim into your "compute data" method. Getting ahold of the TransactionStatus object in another manner when using declarative transactions is too much of a pain to make that approach worthwhile.

It's not really clear to me how Spring's transaction infrastructure is architectured; from within an HibernateDelegate (HibernateTemplate.Execute(...)), can ISession.Transaction.Rollback() be used?

You should not call transaction rollback on the session as Spring's NHibernate transaction manager is managing this responsibility. Doing so for one thing would short-circuit things like the rollback rules and it is also easy to forget to call things like ISession.Clear() in these cases, among other items. That being said I will be adding support so that you can use the native NHibernate API but participate in spring managed transactions in the future.

Cheers,
Mark