> In our experience, long running transactions are a PITA for all databases,
> even if they seam to work fine.
> It is always possible to design the work flow so that the transactions are
> short lived:
> - don't ever open a transaction and then wait for user input;
> - use cooperative locking instead of data base locking for long lived
> locks;
> - chunk transactions when processing huge amounts of data;
Trouble is, with JDBC as its currently implemented, the instant you Commit()
or Rollback(), JDBC starts a new transaction automatically, since JDBC has
no explicit Begin(). If that thread then goes quiescent for an arbitrary
period of time (perhaps waiting for more messaging traffic, our apps are
message driven factory floor middleware things), this transaction remains
open. Our applications are characterized by bursts of frenetic activity
followed by long idle periods, quite unpredictably.
> Regarding cooperative locking and the fact that after a crash the lock
> stays: I'm not sure how your application is designed, but in ours after
> a crash we definitely want it to stay there, so we can manually check
> the consistency of the data before releasing the lock for further
> processing. I have bad experience with automatic recovery after crashes.
in this particular case, the lock is to simply indicate there is an active
'subscriber' to a messaging 'subject', if the app has exited any which ways,
by definition that 'subscriber' is no longer active, so yes, we want the
lock to go away.