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

On Mon, Dec 29, 2014 at 4:17 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> Serialization errors only exist as a concession
> to concurrency and performance.  Again, they should be returned as
> sparsely as possible


I think this is fuzzy thinking. Serialization *errors* themselves are
a concession to concurrency and performance but the actual signaling
of the errors is just a consequence that the serialization failure
occurred. If you can find a way to avoid the serialization failure
then yay but you can't just not report it and call that a win.

The point of returning a serialization failure is to report when a
transaction sees something that can't be possible in a serialized
execution. It's not that "retrying might make the error go away" it's
that the error shouldn't have been possible in a serialized execution
so the code shouldn't have to be prepared for it.

So if you get a unique violation or an RI violation and the
transaction didn't previously verify that the constraint wouldn't be
violated then it's perfectly ok to return a constraint violation
error. It doesn't matter whether retrying might avoid the error since
there was some serialized order in which the constraint was violated.

The case at issue is things like upsert where the code already
verified that no violation should occur. Upsert is a typical case but
it would be equally valid if it's an RI constraint and the transaction
verified that the RI constraint is satisified before inserting or
updating. If a concurrent transaction breaks the constraint for this
insert and the insert gets a constraint violation then it ought to get
a serialization failure since the constraint failure can't occur in a
serialized execution order.

But shouldn't the Read-Write dependency graph already be picking this
up? IF you've previously read a row and someone updates it and then
you try to lock it for updates it should be considering this a
serialization failure.

-- 
greg