Подтвердить с транзакцией

Question

Я использую Spring с PostgreSQL и пытаюсь сделать что-то вроде UPSERT, используя такой код:

jt.update("delete from A where id = 1")
jt.update("insert into A (id, value) values (1, 100)")

обернутый внутри транзакции (используя @Transactional).

Проблема заключается в том, что при наличии множества одновременных запросов этот код завершается ошибкой с ошибками "дубликата ключа", то есть транзакция не изолирована или...

Я что-то упускаю из-за того, как работают транзакции? Должен ли я использовать другой механизм здесь (например, синхронизация потоков)?

Answer 1

Я написал довольно большой пост об этом в блоге, так что, хотя я могу получить отрицательные отзывы о ссылках, прочитайте это.

Суть в том, что транзакции здесь не помогают (по крайней мере, по умолчанию), и хотя можно написать правильное upsert, на самом деле это довольно сложно.

Answer 2

Предполагая эту простую таблицу:

CREATE TABLE tbl(id int primary key, value int);

Эта функция почти на 100% безопасна (см. Комментарии) для одновременных транзакций.:

CREATE OR REPLACE FUNCTION f_upsert(_id int, _value int)
  RETURNS void AS
$func$
BEGIN
LOOP
   UPDATE tbl SET value = _value WHERE  id = _id;

   EXIT WHEN FOUND;

   BEGIN
      INSERT INTO tbl (id, value)
      VALUES (_id, _value);

      RETURN;

   EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- tbl.id has UNIQUE constraint.
      RAISE NOTICE 'It actually happened!'; -- hardly ever happens
   END;

END LOOP;
END
$func$ LANGUAGE plpgsql;

Вызов:

SELECT f_upsert(2, 2);

Это очень похоже на это INSERT / SELECT дело с дополнительными пояснениями и ссылками:

• SELECT или INSERT в функции склонны к условиям гонки?