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

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?

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.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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 (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

Tom Lane-2 wrote

mscott@

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?

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.

regards, tom lane

My reading is that if run serially the second function call results in a
select returning zero records (exists returns true, then not-ed to a
constant false in the where clause) and thus no insert is attempted and thus
no duplicate key error - the record from the first transaction remains as-is
and the second is effectively a no-op.

So the fact the serializable versions fails is correct but it fails
"incorrectly" since a serial execution could never fail with a duplicate key
error. Thus neither a possible unspecified serial order result (dup being
an impossible one) nor a serialization error occurred which is contrary to
the guarantee that you state.

Now, the fact is the serialization case correctly fails but from an API
perspective immediate retry makes no sense for duplicate key failure while
it does for serialization failure.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9301-INSERT-WHERE-NOT-EXISTS-on-table-with-UNIQUE-constraint-in-concurrent-SERIALIZABLE-transacts-tp5792985p5793000.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

David Johnston <polobo@yahoo.com> wrote:

Tom Lane-2 wrote

mscott@ 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?

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.

My reading is that if run serially the second function call
results in a select returning zero records (exists returns true,
then not-ed to a constant false in the where clause) and thus no
insert is attempted and thus no duplicate key error - the record
from the first transaction remains as-is and the second is
effectively a no-op.

So the fact the serializable versions fails is correct but it
fails "incorrectly" since a serial execution could never fail
with a duplicate key error.  Thus neither a possible unspecified
serial order result (dup being an impossible one) nor a
serialization error occurred which is contrary to the guarantee
that you state.

Now, the fact is the serialization case correctly fails but from
an API perspective immediate retry makes no sense for duplicate
key failure while it does for serialization failure.

This issue has been noted in the literature since at least 2007[1]http://www.vldb.org/conf/2007/papers/industrial/p1263-jorwekar.pdf Automating the Detection of Snapshot Isolation Anomalies by Sudhir Jorwekar, Krithi Ramamritham, Alan Fekete, S. Sudarshan VLDB ‘07, September 23-28, 2007, Vienna, Austria..
In the static code analysis of academic and financial systems at
Indian Institute of Technology Bombay which had been relying on
snapshot isolation for transactional integrity they initially
flagged many possible sources of data corruption from serialization
failure which turned out not to actually be possible due to primary
keys, unique indexes, or foreign keys.  After considering such
issues they were left with only two real risks in each of the
systems, and were only able to find actual data corruption of one
of those in each system.

If there were a cheap and reliable way to turn violations of these
constraints into serialization failures where appropriate, I agree
that it would be better to do so.  I have not been able to see any
way to do that, but am all ears if anyone else has an idea.  (Note
the constraints of cheap and reliable; we could allow the SSI
mechanism to generate SIReadLocks during constraint enforcement,
but it's not clear that it would always find a serialization error
before the constraint caused an error, and the overhead might be
significant.)

The actual guarantee provided is more like: "The behavior of any
set of successfully committed concurrent serializable transactions
will be consistent with some unspecified serial order of execution
of those transactions."  Constraints may terminate a transaction
before the serializable snaphsot isolation mechanism is able to
recognize a problem and cancel a transaction with a different
SQLSTATE, but the guarantee should prevent any serialization
anomalies from appearing in the database.  It does present a
challenge in terms of knowing whether a constraint validation like
"duplicate key" indicates a situation which can be automatically
re-tried.  If that is of paramount importance one could implement
the logic for preventing duplicate keys or enforcing foreign keys
in triggers using serializable transactions, but that would come
with a heavy price in both maintenance effort and performance.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[1]: http://www.vldb.org/conf/2007/papers/industrial/p1263-jorwekar.pdf Automating the Detection of Snapshot Isolation Anomalies by Sudhir Jorwekar, Krithi Ramamritham, Alan Fekete, S. Sudarshan VLDB ‘07, September 23-28, 2007, Vienna, Austria.
Automating the Detection of Snapshot Isolation Anomalies
by Sudhir Jorwekar, Krithi Ramamritham, Alan Fekete, S. Sudarshan
VLDB ‘07, September 23-28, 2007, Vienna, Austria.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs