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

Our app uses system state. We scan filesystems and record file
information in a database.

Here is one example:

<txn started by Spring in web container>
- insert 250 files
- update some stats (MUST work even if insert fails)
- update agent last-contact time (also must work so we know it's not awol)
<txn commited by Spring in web container>

When last insert has finished, server will kick into summary mode:

<txn started by Spring in web container>
- wait for asynchronous inserts to finish
- summarise data
- do some misc operations
- switch back to 'Idle' state
<txn committed by Spring in web container>

That last step is where the trick is: if anything goes wrong with the
previous operations, we MUST be able to put the system back into an
'Idle' state. Currently we do this in a catch block, ie:

try {
<do the summary stuff>
} catch {
<switch back to idle>
}

Now of course that won't work in pg. We use the same logic all through
the system, so you can see my problem. For example, if the user deletes
a drive record that still has referential links to it, we do this:

try {
<delete drive>
} catch (ReferentialIntegrityException e) {
<mark drive as deleted so it becomes invisible, is deleted later>
}

We rely on the fact that we can still do things and commit a transaction
even if a single statement has failed.

The above drive delete case is where I first noticed the problem when
switching from MSSQL: instead of the server handling the exception and
doing something else instead, the 'something else' also threw an
exception (txn is aborted), which propagated to the client end.

UPDATE: Webb has proposed that this behaviour may be MSSQL misbehaving.

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