Re: BUG #12330: ACID is broken for unique constraints

> [combining replies -- nikita, better not to top-post (FYI)]

I'm sorry. I don't know what you mean. I just replied to an email.

> To prove your statement, you need to demonstrate how a transaction left
the database in a bad state given concurrent activity without counting
failures.

1. Transaction A looks up a row by ID 1 and gets an empty result.
2. Concurrent transaction B inserts a row with ID 1.
3. Transaction A goes on with the presumption that a row with ID 1 does not
exist, because a transaction is supposed to be isolated and because it has
made sure that the row does not exist. With this presumption it confidently
inserts a row with ID 1 only to get Postgres report a duplicate key. Wat?

2014-12-29 19:17 GMT+03:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

> On Mon, Dec 29, 2014 at 9:44 AM, Greg Stark <stark(at)mit(dot)edu> wrote:
> > On Mon, Dec 29, 2014 at 3:31 PM, Merlin Moncure <mmoncure(at)gmail(dot)com>
> wrote:
> >> In that case: we don't agree. How come duplicate key errors would be
> >> reported as serialization failures but not RI errors (for example,
> >> inserting a record pointing to another record which a concurrent
> >> transaction deleted)?
> >
> > The key question is not the type of error but whether the transaction
> > saw another state previously.
>
> [combining replies -- nikita, better not to top-post (FYI)]
>
> How is that relevant? Serialization errors only exist as a concession
> to concurrency and performance. Again, they should be returned as
> sparsely as possible because they provide absolutely (as Tom pointed
> out) zero detail to the application. The precise definition of the
> error is up to us, but I'd rather keep it to it's current rather
> specific semantics.
>
> On Mon, Dec 29, 2014 at 9:48 AM, Nikita Volkov <nikita(dot)y(dot)volkov(at)mail(dot)ru>
> wrote:
> > I believe, the objections expressed in this thread miss a very important
> > point of all this: the isolation property (the "I" in ACID) is violated.
> >
> > Here’s a quote from the Wikipedia article on ACID:
> >
> > The isolation property ensures that the concurrent execution of
> transactions
> > results in a system state that would be obtained if transactions were
> > executed serially, i.e., one after the other.
> >
> > The original example proves that it's violated. Such behaviour can
> neither
> > be expected by a user, nor is even mentioned anywhere. Instead in the
> first
> > paragraph of the “About” section of the Postgres site it states:
> >
> > It is fully ACID compliant
> >
> > Which is basically just a lie, until issues like this one get dealt with.
>
> That's simply untrue: inconvenience != acid violation
>
> Transaction levels provide certain guarantees regarding the state of
> the data in the presence of concurrent overlapping operations. They
> do not define the mechanism of failure or how/when those failures
> should occur. To prove your statement, you need to demonstrate how a
> transaction left the database in a bad state given concurrent activity
> without counting failures. Postgres can, and does, for example,
> return concurrency type errors more aggressively than it needs to for
> the 'repeatable read', level. Point being, this is completely ok as
> database implementation is free to understand that, just as it's free
> to define precisely how and when it fails given concurrency as long as
> those guarantees are provided.
>
> merlin
>