two phase commit

Ben <bench@silentmedia.com> writes:

I'm reading the description of PREPARE TRANSACTION, and I see this:
"...its state is fully stored on disk, and there is a very high
probability that it can be committed successfully..."

What corner case reduces 2pc from "guaranteed" to "very high probability"?

Well, for example, someone drops a nuke on your data center ...

Barring hardware failure, OS failure, or irrecoverable database crash,
the only condition I can think of that would prevent COMMIT PREPARED
from succeeding is out-of-disk-space on the WAL drive. Which is a PANIC
condition anyway, and thus might be classed with database crashes,
although it's not irrecoverable so long as the admin can free up some
disk space.

regards, tom lane

On Thu, 2007-07-19 at 15:13 -0700, Ben wrote:

I'm reading the description of PREPARE TRANSACTION, and I see this:

"...its state is fully stored on disk, and there is a very high
probability that it can be committed successfully..."

What corner case reduces 2pc from "guaranteed" to "very high probability"?
Is the worry if somebody leaves transactions in a prepared state for
weeks, only to find that deadlock issues has arrisen at final commit time?

You won't get a deadlock on COMMIT, because COMMIT doesn't acquire more
locks.

The failure cases for a 2PC transaction are (as I understand it)
catastrophic. That means either the transaction can be committed, or you
probably need to restore onto new hardware anyway.

2PC is useful when multiple databases are involved and your application
crashes. When the application comes back you can look at the prepared
transactions and decide whether to COMMIT PREPARED or ROLLBACK PREPARED
based on the information in the other databases involved.

Regards,
Jeff Davis

Er, right.... I guess I should have asked if it's more likely to commit a
running transaction than a prepared one.... and it sounds like the answer
is "no". :)

On Thu, 19 Jul 2007, Tom Lane wrote:

Ben <bench@silentmedia.com> writes:

Er, right.... I guess I should have asked if it's more likely to commit a
running transaction than a prepared one.... and it sounds like the answer
is "no". :)

Less likely, because PREPARE TRANSACTION executes all the COMMIT-time
actions that can cause "expected" failures --- checking deferred
constraints for example. If you get past that, it's not supposed to fail.

regards, tom lane

On Thu, Jul 19, 2007 at 03:13:27PM -0700, Ben wrote:

What corner case reduces 2pc from "guaranteed" to "very high probability"?
Is the worry if somebody leaves transactions in a prepared state for
weeks, only to find that deadlock issues has arrisen at final commit time?

That's not the worry, no. But something _else_ could happen. For
instance, recently it turned out that there was a way, using 2PC, to
lock everybody out of the database. The only remedy to that at the
moment is to blow away all the PREPAREd transactions, which could
mean you lose something that was already committed to.

A
--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

On Fri, 2007-07-20 at 15:26 -0400, Andrew Sullivan wrote:

On Thu, Jul 19, 2007 at 03:13:27PM -0700, Ben wrote:

What corner case reduces 2pc from "guaranteed" to "very high probability"?
Is the worry if somebody leaves transactions in a prepared state for
weeks, only to find that deadlock issues has arrisen at final commit time?

That's not the worry, no. But something _else_ could happen. For
instance, recently it turned out that there was a way, using 2PC, to
lock everybody out of the database. The only remedy to that at the
moment is to blow away all the PREPAREd transactions, which could
mean you lose something that was already committed to.

To clarify, I think you're referring to this:

http://archives.postgresql.org/pgsql-hackers/2007-07/msg00245.php

which can only be done as superuser locking a system table.

I would classify that as a "catastrophic" problem, since it involves
manually modifying $PGDATA.

Regards,
Jeff Davis

On Fri, Jul 20, 2007 at 05:17:00PM -0700, Jeff Davis wrote:

On Fri, 2007-07-20 at 15:26 -0400, Andrew Sullivan wrote:

instance, recently it turned out that there was a way, using 2PC, to
lock everybody out of the database. The only remedy to that at the
moment is to blow away all the PREPAREd transactions, which could
mean you lose something that was already committed to.

http://archives.postgresql.org/pgsql-hackers/2007-07/msg00245.php

which can only be done as superuser locking a system table.

I would classify that as a "catastrophic" problem, since it involves
manually modifying $PGDATA.

Right. But there's a big difference between this case and many
catastrophic problems, because it's entirely possible that the whole
reason you were using 2PC was to increase reliability in the face of
various disasters, including operator error. So you had _better_
know which operator errors of this very feature are likely to cause
catastrophes.

A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

Good point, but just to be clear, I was asking about 2PC because our app
writes to two different databases, and the authors never considered that
the second commit might fail.

On Mon, 23 Jul 2007, Andrew Sullivan wrote:

On Mon, Jul 23, 2007 at 11:59:39AM -0700, Ben wrote:

Good point, but just to be clear, I was asking about 2PC because our app
writes to two different databases, and the authors never considered that
the second commit might fail.

Ok, so as long as you're willing to accept that "second commit might
fail in certain, world-blows-up scenarios", then maybe that's good
enough. (You really just need to know what you're not protected
against, I'd assume.)

A

--
Andrew Sullivan | ajs@crankycanuck.ca
The very definition of "news" is "something that hardly ever happens."
--Bruce Schneier

On Mon, 2007-07-23 at 14:48 -0400, Andrew Sullivan wrote:

Right. But there's a big difference between this case and many
catastrophic problems, because it's entirely possible that the whole
reason you were using 2PC was to increase reliability in the face of
various disasters, including operator error. So you had _better_
know which operator errors of this very feature are likely to cause
catastrophes.

Fair enough. I'm not very opinionated about the referenced "feature/bug"
discussion, I just wanted to add some context to the problem you
mentioned (for the archives, if nothing else).

The way you worded your reply would scare anyone away from using 2PC at
all, and 2PC might be useful in Ben's case.

Regards,
Jeff Davis

On Mon, Jul 23, 2007 at 12:29:06PM -0700, Jeff Davis wrote:

The way you worded your reply would scare anyone away from using 2PC at
all, and 2PC might be useful in Ben's case.

Well, I didn't intend to scare anyone away from it! Apologies.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell