On Tue, 1 Oct 2019 at 19:06, Scott Gray <[email protected]> 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: [email protected]
