Упростить транзакцию

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

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

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

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

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

Ответы

Ответ 1

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

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

Ответ 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 с большим количеством объяснений и ссылок: