Re: BUG #9301: INSERT WHERE NOT EXISTS on table with UNIQUE constraint in concurrent SERIALIZABLE transactions

Hi Tom,

On Feb 20, 2014, at 8:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> mscott(at)apple(dot)com writes:
>> -- The following seems to violate the fundamental guarantee of ISOLATION
>> LEVEL SERIALIZABLE in that the two
>> -- transactions below do not behave the same as if they were run serially.
>
> I must be missing something ... what about the duplicate-key error is not
> what you would expect if the two transactions had been run serially?

If the two transactions had run serially, one of them would have inserted into the table and the other one would not (because the subquery would have found that a row existed).

Let me see if I can simplify this even further. Here’s a PDF transcript showing the commands in the order issued. No functions involved, no subqueries, but the logic is exactly the same. When TXN #2 performs the first SELECT (before TXN #1 commits) it does not find any existing row. OK, good, that’s expected. Then TXN #1 commits, no problem. Back on TXN #2, we perform the exact same SELECT to make sure we *still* don’t find anything. Perfect, as expected again.

Now the bug: We perform an INSERT into the table that we KNOW (because we just checked) doesn’t have any conflicting rows and we blow up with a duplicate key violation. Whoa Nellie! My database is leaking a part of TXN #1 into TXN #2 via the UNIQUE INDEX on the table. Now, I certainly won’t claim to be a SQL expert, but this sure seems to me to be a phantom read in action. Granted, the SELECT isn’t giving us a phantom read, but the index certainly is. The index has made it clear that something which wasn’t present at the start of my transaction is now, quite unexpectedly, present in the middle of it. It would almost be better, in this case, if we had a real phantom read, because at least we could then avoid attempting the INSERT.

What I would expect from the database at this point is a serialization error, not a unique constraint error. My application code catches and knows how to deal with serialization errors. But it doesn’t have the faintest clue that a unique constraint error is really a serialization error in disguise. Any why should it? The thousands of unique constraint errors that have come before have all been, without exception, application program errors.

I’m not suggesting that TXN #2 should complete successfully, clearly it shouldn’t. What I’m asking for is to have the correct error reported so I can recover from the failure appropriately (i.e., retry the transaction). For now, our only workaround is to remove the UNIQUE constraint and trust that nobody ever performs an INSERT into this table without using our insert function, or an INSERT WHERE NOT EXISTS expression (or equivalent). I contend that we probably shouldn’t have to do that.

> BTW, the fundamental guarantee is not what you said above. It is that
> you will *either* get the same results as if the transactions had been
> run in some unspecified serial order, *or* you will get a serialization
> error. There is certainly no guarantee about which of these cases
> ensues.

Right, I get that. Clearly one of these two transactions, run in this sequence, should fail with a serialization error, but that’s not what’s happening. It’s failing with a unique constraint violation error.

Thanks.

-Scott