Thread: Failure to COMMIT -- Timeout?

Looking through the documentation, I can't find anything that tells me what
happens if an explicitly defined transaction fails to commit.  I am
assuming that there must be some timeout, otherwise this obviously causes
the potential for serious problems with locks.  For example:

Transaction 1:
BEGIN WORK
UPDATE table
(network outage prevents a COMMIT)

If I am reading correctly, all other transactions will hang waiting to see
if Transaction 1 commits.  In this situation it should clearly rollback.
Will postgres eventually time out the transaction and catch this problem?
Can anyone supply or point me to more information about how Postgres
handles this situation?

Testing in psql shows that the transaction is considered stopped (and
rolled back) when the connection from psql is broken.  Is a broken network
link (in this case established in PHP) an equivalent situation?

Thanks,
Ben

Ben Lanson <lanson@bellatlantic.net> writes:
> BEGIN WORK
> UPDATE table
> (network outage prevents a COMMIT)

> If I am reading correctly, all other transactions will hang waiting to see
> if Transaction 1 commits.

Not "all others", just those that actually need to care whether that
xact commits --- in practice, xacts that want to update the same tuples
it did.

> Will postgres eventually time out the transaction and catch this problem?

In the current implementation, loss of connection to the client will be
detected eventually because we run TCP connections with SO_KEEPALIVE
set.  However KEEPALIVE is quite unaggressive; IIRC it takes about 2
hours before the kernel will lose all hope and declare the connection
lost.

> Testing in psql shows that the transaction is considered stopped (and
> rolled back) when the connection from psql is broken.  Is a broken network
> link (in this case established in PHP) an equivalent situation?

Once the kernel tells the backend that the connection is gone, the
backend will rollback its transaction and exit.  Your question therefore
reduces to a transport issue: when will the TCP stack report connection
loss/closure?

            regards, tom lane