Re: Default transaction timeout on screen widget

[Scott Gray <sc...@hotwaxsystems.com>]

Hi Paul,

I've never seen a transaction timeout actually cause locks to be released
ahead of time before the transaction finally attempts to commit, are you
saying that happens? In my experience if you have a 60 second timeout but
your transaction doesn't try to commit for 15 minutes then the locks will
be held for the full 15 minutes (before finally rolling back when you
attempt to commit).

But no sooner did I ask this question about the relevance of timeouts than
I came across a good use case for them.  If your transaction is wrapping an
API call and the intermediate gateways (like a proxy server or whatever)
have a timeout, then it can be better to have the transaction timeout
shorter than the gateway timeout.  In this manner the client won't
encounter situations where they receive a gateway timeout error response
but the API call they made is actually successful on the server.

Regards
Scott

On Thu, 26 Sep 2019 at 00:46, Paul Foxworthy <pa...@cohsoft.com.au.invalid>
wrote:

> On Tue, 17 Sep 2019 at 18:45, Scott Gray <sc...@hotwaxsystems.com>
> wrote:
>
> > Has anyone ever had a good experience with transaction timeouts?  I feel
> > like they achieve nothing useful in that:
> > 1. They don't interrupt any work that is taking too long (they just
> prevent
> > it from being committed)
> > 2. By the time the exception is thrown, all the work is already done and
> > the result is that it just gets thrown away in exchange for an error
> >
> > Surely there's a positive side to them, and I've just never seen it?
> >
>
> Hi Scott,
>
> Timeouts are not good for the transaction, they're good for competing
> transactions.
>
> In many DBMSes, a transaction keeps things locked until commit, to
> achieve isolation, the I in ACID. If an unusual error situation arises, or
> somebody has designed a transaction (badly) to include a user's think time,
> it's possible the transaction does not commit at all, blocking access to
> data touched by the transaction for everyone else, indefinitely.
>
> A transaction timeout is an estimate of a reasonable amount of time within
> which the transaction should complete. If it doesn't complete, the timeout
> ensures it will roll back, so other transactions won't have to wait too
> long. It's something like an operating system segfaulting a rogue process
> in order to protect all the others.
>
> Cheers
>
> Paul Foxworthy
>
> --
> Coherent Software Australia Pty Ltd
> PO Box 2773
> Cheltenham Vic 3192
> Australia
>
> Phone: +61 3 9585 6788
> Web: http://www.coherentsoftware.com.au/
> Email: info@coherentsoftware.com.au
>

Re: Default transaction timeout on screen widget

[Paul Foxworthy <pa...@cohsoft.com.au.INVALID>]

On Tue, 1 Oct 2019 at 19:06, Scott Gray <sc...@hotwaxsystems.com>
wrote:

> Hi Paul,
>
> I've never seen a transaction timeout actually cause locks to be released
> ahead of time before the transaction finally attempts to commit, are you
> saying that happens?


OK, now we need to talk about context.

The OFBiz transaction timeout uses
javax.transaction.TransactionManager.setTransactionTimeout, so it depends
on your JDBC driver, but I reckon that's what I'd want - the transaction
should fail if it can't do the job within the time limit. Often the default
timeout is infinite, so never happens, because what's a reasonable timeout
depends on the work the application is doing. An end of financial year
transaction might quite legitimately take a weekend to complete.

InnoDB has an option innodb_rollback_on_timeout (
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_rollback_on_timeout).
By default it rolls back only the last statement. That seems very weird to
me - a rollback should roll back the transaction, not any one statement.

Postgres has statement_timeout and idle_in_transaction_session_timeout (
https://www.postgresql.org/docs/9.6/runtime-config-client.html)

In my experience if you have a 60 second timeout but
> your transaction doesn't try to commit for 15 minutes then the locks will
> be held for the full 15 minutes (before finally rolling back when you
> attempt to commit).
>

Then I'd agree with you - the timeout is only informing you your query was
slow to execute, and there is no other benefit to the timeout. Might as
well have some sort of monitoring for query execution times, and turn off
timeouts. If the locks are going to stay on anyway, might as well commit
the first transaction if it completes without error.


> But no sooner did I ask this question about the relevance of timeouts than
> I came across a good use case for them.  If your transaction is wrapping an
> API call and the intermediate gateways (like a proxy server or whatever)
> have a timeout, then it can be better to have the transaction timeout
> shorter than the gateway timeout.  In this manner the client won't
> encounter situations where they receive a gateway timeout error response
> but the API call they made is actually successful on the server.
>

We are used to transactions spanning several changes within a database -
the classic example is a transfer between two bank accounts. And
transactions should give us the famous all-or-nothing ACID behaviour. Mix
in anything else, like the API call, and it gets harder. Queuing services
like JMS, MSMQ, ServiceBus, RabbitMQ, AMQP give the same transactional
behaviour to sending and receiving messages. Posting a message to a queue
is a pending operation that becomes real when you commit a transaction, so
any other processing before or after must complete successfully. You can
rollback if any error occurs, which undoes the posting of the message.
Similarly, at the other end we pull a message off the queue and try to
process it. In the event of any failure, a transaction is rolled back and
the message is still safely there on the queue.

In the absence of queuing, the best you can probably do is build in
compensation - call the API, and in the event of a subsequent failure issue
a second API call to undo the first. The analogy is like clearing a cheque
instead of transferring between your own bank accounts - in the event of a
failure, the second bank sends a decline message that undoes the effect of
the deposit. Conceptually, there's a transaction but it's built by
composing a sequence of smaller and simpler transactions rather than having
an overarching one. When you are managing compensation, you have to
implement an undo stack for yourself rather than relying on a transaction
to do it for you.

Cheers

Paul Foxworthy

-- 
Coherent Software Australia Pty Ltd
PO Box 2773
Cheltenham Vic 3192
Australia

Phone: +61 3 9585 6788
Web: http://www.coherentsoftware.com.au/
Email: info@coherentsoftware.com.au