Обработка условий гонки в PostgreSQL

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

Рабочие обрабатывают параллельные запросы извне системы. Одной из таблиц, к которым осуществляется доступ, является таблица пользователей. Когда приходит какая-то информация, я сначала должен убедиться, что в таблице есть запись для пользователя. Если нет записи, я хочу сначала создать ее.

Я использую следующую идиому:

if [user does not exist] then [create user]

Код [user does not exist]:

SELECT id FROM myschema.users WHERE userId='xyz'

и я проверяю, возвращается ли какая-либо строка.

(упрощенным) кодом [create user] является:

INSERT INTO myschema.users VALUES ('xyz')

Когда моя система обрабатывает параллельные потоки различной информации о одном и том же пользователе, я часто получаю ошибку PostgreSQL:

Key (id)=(xyz) already exists

Это происходит потому, что команда SELECT не возвращает никаких строк, затем другой рабочий создает пользователя, любой мой рабочий пытается сделать то же самое, что приводит к примерной ошибке concurrency.

Согласно документации PostgreSQL по умолчанию, когда я неявно запускаю транзакцию, таблица становится заблокированной до тех пор, пока я ее не фиксирую. Я не использую autocommit, и я совершаю транзакцию только в блоках, например. после всего блока if-else.

В самом деле, я мог бы поместить материал if-else в SQL напрямую, но он не решает мою проблему блокировки вообще. Я предполагал, что парадигма "победит все это" будет работать, и что первый рабочий, которому удается выполнить команду SELECT, будет иметь блокировки, пока не назовет COMMIT.

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

Ответ 1

Вы должны заботиться об уровне изоляции транзакций. Он должен быть установлен на "SERIALIZABLE".

Причина: Phantom Reads - транзакция не блокирует всю таблицу, а только строки, которые уже были прочитаны транзакцией.

Итак, если другая транзакция вставляет новые данные, они еще не заблокированы, и появляется ошибка.

Serializable избегает этого, блокируя все другие транзакции, пока это не закончится.

Вы можете сделать это через

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Документация: http://www.postgresql.org/docs/9.1/static/transaction-iso.html

Если вы хотите узнать больше об этой теме, я могу порекомендовать вам это видео: http://www.youtube.com/watch?v=zz-Xbqp0g0A

Ответ 2

Собственно, после некоторого вовлечения в ISOLATION LEVEL SERIALIZABLE, предложенного @maja, я обнаружил гораздо более простой механизм:

PERFORM pg_advisory_lock(id);
...
# do something that others must wait for
...
PERFORM pg_advisory_unlock(id);

где id - это значение BIGINT, которое я могу выбрать произвольно в соответствии с моей логикой приложения.

Это дало мне силу и гибкость, которые я искал.