Обсуждение: BUG #9301: INSERT WHERE NOT EXISTS on table with UNIQUE constraint in concurrent SERIALIZABLE transactions
The following bug has been logged on the website: Bug reference: 9301 Logged by: Scott Marcy Email address: mscott@apple.com PostgreSQL version: 9.3.2 Operating system: Mac OS X 10.9, CentOS 6.5 Description: ------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------ -- 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. Code that checks for -- serialization failures obviously doesn't catch this problem and there is no good workaround (other than -- removing the UNIQUE constraint) as you get the same behavior if you use a plpgsql function and run the -- SELECT query separately. ------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------ ----------- -- Setup -- ----------- CREATE TABLE test ( key integer UNIQUE, val text ); CREATE OR REPLACE FUNCTION insert_unique(k integer, v text) RETURNS void LANGUAGE SQL AS $$ INSERT INTO test (key, val) SELECT k, v WHERE NOT EXISTS (SELECT key FROM test WHERE key = k); $$; ---------- -- Test -- ---------- --------------------------- -- On psql Connection #1 -- --------------------------- \set VERBOSITY verbose BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT insert_unique(1, '1'); --------------------------- -- On psql Connection #2 -- --------------------------- \set VERBOSITY verbose BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT insert_unique(1, '2'); -- (Connection #2 waits here for #1) --------------------------- -- On psql Connection #1 -- --------------------------- COMMMIT; -- Connection #2 blows up: -- ERROR: 23505: duplicate key value violates unique constraint "test_key_key" -- DETAIL: Key (key)=(1) already exists. -- CONTEXT: SQL function "insert_unique" statement 1 -- LOCATION: _bt_check_unique, nbtinsert.c:398 -- Adding a "LOCK TABLE test IN ACCESS EXCLUSIVE MODE;" at the top of the function doesn't help if you've performed -- any queries prior to using this function. -- Adding a "FOR UPDATE" to the WNE subquery does not help -- Removing the UNIQUE constraint avoids the duplicate key error and properly causes a serialization failure on -- Connection #2's transaction. -- It appears that the UNIQUE INDEX is not snapshotted at the start of a transaction the same way the data table -- is. The row inserted by Connection #1 is obviosuly visible in the index in the transaction on Connection #2. --------------------------- -- On psql Connection #2 -- --------------------------- ROLLBACK; ALTER TABLE test DROP CONSTRAINT test_key_key; DELETE FROM test; -- Now repeat the test. -- Output on Connection #2 (might need to 'COMMIT' on Connection #2) is as expected: -- ERROR: 40001: could not serialize access due to read/write dependencies among transactions -- DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt. -- HINT: The transaction might succeed if retried. -- LOCATION: PreCommit_CheckForSerializationFailure, predicate.c:4651
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
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.
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
Вложения
David Johnston <polobo@yahoo.com> wrote:=0A> Tom Lane-2 wrote=0A>> mscott@ = writes:=0A=0A>>> -- The following seems to violate the fundamental guarante= e of=0A>>> ISOLATION LEVEL SERIALIZABLE in that the two transactions below= =0A>>> do not behave the same as if they were run serially.=0A>>=0A>> I mus= t be missing something ... what about the duplicate-key=0A>> error is not w= hat you would expect if the two transactions had=0A>> been run serially?=0A= >>=0A>> BTW, the fundamental guarantee is not what you said above.=C2=A0 It= =0A>> is that you will *either* get the same results as if the=0A>> transac= tions had been run in some unspecified serial order, *or*=0A>> you will get= a serialization error.=C2=A0 There is certainly no=0A>> guarantee about wh= ich of these cases ensues.=0A=0A> My reading is that if run serially the se= cond function call=0A> results in a select returning zero records (exists r= eturns true,=0A> then not-ed to a constant false in the where clause) and t= hus no=0A> insert is attempted and thus no duplicate key error - the record= =0A> from the first transaction remains as-is and the second is=0A> effecti= vely a no-op.=0A>=0A> So the fact the serializable versions fails is correc= t but it=0A> fails "incorrectly" since a serial execution could never fail= =0A> with a duplicate key error.=C2=A0 Thus neither a possible unspecified= =0A> serial order result (dup being an impossible one) nor a=0A> serializat= ion error occurred which is contrary to the guarantee=0A> that you state.= =0A>=0A> Now, the fact is the serialization case correctly fails but from= =0A> an API perspective immediate retry makes no sense for duplicate=0A> ke= y failure while it does for serialization failure.=0A=0AThis issue has been= noted in the literature since at least 2007[1].=0AIn the static code analy= sis of academic and financial systems at=0AIndian Institute of Technology B= ombay which had been relying on=0Asnapshot isolation for transactional inte= grity they initially=0Aflagged many possible sources of data corruption fro= m serialization=0Afailure which turned out not to actually be possible due = to primary=0Akeys, unique indexes, or foreign keys.=C2=A0 After considering= such=0Aissues they were left with only two real risks in each of the=0Asys= tems, and were only able to find actual data corruption of one=0Aof those i= n each system.=0A=0AIf there were a cheap and reliable way to turn violatio= ns of these=0Aconstraints into serialization failures where appropriate, I = agree=0Athat it would be better to do so.=C2=A0 I have not been able to see= any=0Away to do that, but am all ears if anyone else has an idea.=C2=A0 (N= ote=0Athe constraints of cheap and reliable; we could allow the SSI=0Amecha= nism to generate SIReadLocks during constraint enforcement,=0Abut it's not = clear that it would always find a serialization error=0Abefore the constrai= nt caused an error, and the overhead might be=0Asignificant.)=0A=0AThe actu= al guarantee provided is more like: "The behavior of any=0Aset of successfu= lly committed concurrent serializable transactions=0Awill be consistent wit= h some unspecified serial order of execution=0Aof those transactions."=C2= =A0 Constraints may terminate a transaction=0Abefore the serializable snaph= sot isolation mechanism is able to=0Arecognize a problem and cancel a trans= action with a different=0ASQLSTATE, but the guarantee should prevent any se= rialization=0Aanomalies from appearing in the database.=C2=A0 It does prese= nt a=0Achallenge in terms of knowing whether a constraint validation like= =0A"duplicate key" indicates a situation which can be automatically=0Are-tr= ied.=C2=A0 If that is of paramount importance one could implement=0Athe log= ic for preventing duplicate keys or enforcing foreign keys=0Ain triggers us= ing serializable transactions, but that would come=0Awith a heavy price in = both maintenance effort and performance.=0A=0A--=0AKevin Grittner=0AEDB: ht= tp://www.enterprisedb.com=0AThe Enterprise PostgreSQL Company=0A=0A[1] http= ://www.vldb.org/conf/2007/papers/industrial/p1263-jorwekar.pdf=0AAutomating= the Detection of Snapshot Isolation Anomalies=0Aby Sudhir Jorwekar, Krithi= Ramamritham, Alan Fekete, S. Sudarshan=0AVLDB =E2=80=9807, September 23-28= , 2007, Vienna, Austria.