Thread: two phase commit
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?
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: >> 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 >
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 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ >
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