BUG #9301: INSERT WHERE NOT EXISTS on table with UNIQUE constraint in concurrent SERIALIZABLE transactions
От | mscott@apple.com |
---|---|
Тема | BUG #9301: INSERT WHERE NOT EXISTS on table with UNIQUE constraint in concurrent SERIALIZABLE transactions |
Дата | |
Msg-id | 20140221002001.29130.27157@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #9301: INSERT WHERE NOT EXISTS on table with UNIQUE constraint in concurrent SERIALIZABLE transactions
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: "Steven Canova"Дата:
Сообщение: Is there a Support Platforms document more current that this?
Следующее
От: Haribabu KommiДата:
Сообщение: Re: Is there a Support Platforms document more current that this?