Мы используем Postgres для нашей производственной базы данных, технически это база данных Amazon AWS Aurora с версией движка 10.11. Кажется, он не находится под какой-либо необоснованной нагрузкой (100-150 одновременных подключений, ЦП всегда ниже 10%, около 50% используемой памяти, всплески до 300 операций ввода-вывода в секунду при записи / 1500 операций ввода-вывода в секунду при чтении в секунду).
Нам нравится обеспечивать действительно хорошую согласованность данных, поэтому мы широко используем внешние ключи, триггеры для проверки данных при их вставке / обновлении, а также множество уникальных ограничений.
Большинство операций записи исходят из простых запросов REST API, которые приводят к очень стандартным запросам на вставку и обновление. Однако в некоторых случаях мы также используем триггеры и функции для обработки более сложной логики. Например, обновление одной таблицы приведет к довольно сложным каскадным обновлениям других таблиц.
Все запросы всегда заключаются в транзакции, и по большей части мы не используем явную блокировку.
Так что не так?
У нас есть много (десятки строк в десятках таблиц) случаев, когда данные существуют в базе данных, которые не соответствуют нашим уникальным ограничениям.
Иногда метки времени created_at и updated_at для строк с нарушением идентичны, а иногда они очень похожи (в пределах полсекунды). Это заставляет меня думать, что это вызвано состоянием гонки.
Мы не уверены, но вполне уверены в том, что общее с этими записями состоит в том, что записи либо запускали функцию (запись была записана путем простой вставки или обновления и вызывала обновление нескольких других таблиц), либо запись исходила из функции (другая запись была записана в результате простой вставки или обновления, что вызвало функцию, записывающую ошибочные данные).
Из того, что мне удалось исследовать, уникальные ограничения / индексы невероятно надежны и просто работают. Это правда? Если да, то почему это могло происходить?
Вот пример некоторых оскорбительных данных, некоторые из которых мне пришлось затемнить, но я обещаю вам, что значения в поле user_id идентичны. Как вы увидите ниже, существует уникальный индекс для user_id, position и undeleted. Так что наличие этих данных должно быть невозможным.
Вот экспорт структуры таблицы:
-- Table Definition ----------------------------------------------
CREATE TABLE guides.preferences (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
user_id uuid NOT NULL REFERENCES users.users(id),
guide_id uuid NOT NULL REFERENCES users.users(id),
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
undeleted boolean DEFAULT true,
deleted_at timestamp without time zone,
position integer NOT NULL CHECK ("position" >= 0),
completed_meetings_count integer NOT NULL DEFAULT 0,
CONSTRAINT must_concurrently_set_deleted_at_and_undeleted CHECK (undeleted IS TRUE AND deleted_at IS NULL OR undeleted IS NULL AND deleted_at IS NOT NULL),
CONSTRAINT preferences_guide_id_user_id_undeleted_unique UNIQUE (guide_id, user_id, undeleted),
CONSTRAINT preferences_user_id_position_undeleted_unique UNIQUE (user_id, position, undeleted) DEFERRABLE INITIALLY DEFERRED
);
COMMENT ON COLUMN guides.preferences.undeleted IS 'Set simultaneously with deleted_at to flag this as deleted or undeleted';
COMMENT ON COLUMN guides.preferences.deleted_at IS 'Set simultaneously with deleted_at to flag this as deleted or undeleted';
-- Indices -------------------------------------------------------
CREATE UNIQUE INDEX preferences_pkey ON guides.preferences(id uuid_ops);
CREATE UNIQUE INDEX preferences_user_id_position_undeleted_unique ON guides.preferences(user_id uuid_ops,position int4_ops,undeleted bool_ops);
CREATE INDEX index_preferences_on_user_id_and_guide_id ON guides.preferences(user_id uuid_ops,guide_id uuid_ops);
CREATE UNIQUE INDEX preferences_guide_id_user_id_undeleted_unique ON guides.preferences(guide_id uuid_ops,user_id uuid_ops,undeleted bool_ops);
Мы действительно озадачены этим и надеемся, что кто-нибудь сможет нам помочь. Спасибо!
