Hi Tom,
On Feb 20, 2014, at 8:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> mscott@apple.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
(becausethe 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
functionsinvolved, no subqueries, but the logic is exactly the same. When TXN #2 performs the first SELECT (before TXN
#1commits) 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
rowsand we blow up with a duplicate key violation. Whoa Nellie! My database is leaking a part of TXN #1 into TXN #2 via
theUNIQUE INDEX on the table. Now, I certainly won’t claim to be a SQL expert, but this sure seems to me to be a
phantomread in action. Granted, the SELECT isn’t giving us a phantom read, but the index certainly is. The index has
madeit clear that something which wasn’t present at the start of my transaction is now, quite unexpectedly, present in
themiddle of it. It would almost be better, in this case, if we had a real phantom read, because at least we could then
avoidattempting the INSERT.
What I would expect from the database at this point is a serialization error, not a unique constraint error. My
applicationcode catches and knows how to deal with serialization errors. But it doesn’t have the faintest clue that a
uniqueconstraint error is really a serialization error in disguise. Any why should it? The thousands of unique
constrainterrors 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
correcterror reported so I can recover from the failure appropriately (i.e., retry the transaction). For now, our only
workaroundis to remove the UNIQUE constraint and trust that nobody ever performs an INSERT into this table without
usingour insert function, or an INSERT WHERE NOT EXISTS expression (or equivalent). I contend that we probably
shouldn’thave 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,
butthat’s not what’s happening. It’s failing with a unique constraint violation error.
Thanks.
-Scott