Re: [RESEND] Transaction auto-abort causes grief with Spring Framework

Gregory Stark wrote:

>"Tyson Lloyd Thwaites" <tyson.lloydthwaites@allianza.com.au> writes:
>
>
>
>>Gregory Stark wrote:
>>
>>
>>
>>>"Tyson Lloyd Thwaites" <tyson.lloydthwaites@allianza.com.au> writes:
>>>
>>>
>>>
>>>>Normally if we catch the exception, other dbs (Oracle, MSSQL) will let us
>>>>keep going.
>>>>
>>>>
>>>How do you catch exceptions in these other dbs?
>>>
>>>
>>plain java try/catch. In other dbs, if I am in a txn, and I run 3 statements,
>>and one of them fails, spring's jdbc wrapper will throw an exception up to the
>>wrapping template, which will roll the txn back. However if I wrap one of those
>>statements in a try/catch(RuntimeException), then the exception will not
>>propagate, but the db will not automatically fry the whole transaction either.
>>
>>
>>
>>>The way to do it in Postgres is with the SAVEPOINT command.
>>>
>>>
>>Yeah, but that is totally unrealistic in our case. I can't go sprinkling "if
>>current db is pgsql then savepoint here" code all through the app. It's a bit
>>annoying that pg insists on being so different to others in this respect,
>>especially since the spec does not mandate it, and appears even to say that the
>>transaction should be allowed to continue. (I read this in another pg thread, I
>>will have to find the argument there, it was a good one). I wish it could be
>>turned off - it seems a bit draconian and heavy-handed to me.
>>
>>
>
>Well you could suggest to your driver that it should wrap your queries in a
>savepoint and throw a java exception in case it gets an error.
>
>
>
I am not sure how one would go about suggesting such a thing to the
driver? That sounds good though.

>>>>For example, if something goes wrong, I can't even write an event row to our
>>>>auditing table!
>>>>
>>>>
>>>This is actually a somewhat more complex example than handling an expected
>>>error. For audit records you really want to be able to commit the audit record
>>>independently of the rest of the transaction. These are called "autonomous
>>>transactions" and there's no built-in support for them in Postgres but you can
>>>put together something equivalent using dblink or a second client connection.
>>>
>>>
>>>
>>>
>>it is more like this:
>>
>>try {
>> <do something>
>>} catch (Exception e) {
>> <insert into audit table: hey, it broke>
>>}
>>
>>
>
>Well that doesn't work if you have more statements to execute within the same
>transaction. Yo would still want the audit record to be committed even if you
>subsequently roll back the transaction.
>
>
Exactly - it won't work for pgsql but it WILL work for MSSQL (although
it probably shouldn't). This is my problem exactly.


--
Tyson Lloyd Thwaites
Lead Software Developer
Allianza Services Pty Ltd

M: 0404 456 858
P: (+618) 8232 5525
F: (+618) 8232 8252
W: www.allianza.com.au