Connection permanently broken if transaction begin times out or fails

[Stuart Bishop]

If starting the transaction fails, such as the statement timeout kicking in, no abort is issued. At least with PostgreSQL, this leaves the connection in a broken state, as all future attempts to begin the transaction fail when trying to issue a BEGIN statement on a broken transaction.

[Stuart Bishop (stub)]

We could attempt to catch the failure as it happens and issue an abort. However, the abort might also fail and we can't loop for ever.

The best approach is to abort the broken transaction before attempting to start a new one. Modern versions of psycopg2 may let us interrogate the connection state and abort it if necessary. We could also unconditionally issue an ABORT before the BEGIN, but this will create log spam in the PostgreSQL logs.

[Stuart Bishop (stub)]

From the PostgreSQL logs, this is what put Launchpad into this state:

<lpnet132@launchpad_prod_3:18158> 2012-02-23 06:18:23 UTC LOG: duration: 14486.797 ms statement: BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
<lpnet132@launchpad_prod_3:18158> 2012-02-23 06:18:23 UTC ERROR: canceling statement due to user request

[Stuart Bishop (stub)]

Those statements are issued by psycopg2 when a transaction is implicitly started. Because the start of a transaction is not explicitly started, there is nowhere to hook in to repair the transaction if it is broken.

I'm not sure how we managed to trigger this state in Launchpad.

[Stuart Bishop (stub)]

Not a Storm bug. An exception is raised if the implicit transaction cannot be started. Somehow, Launchpad managed to not catch this and rollback the transaction like it should have.