Русский
Русский
English
Статистика
Реклама

Upsert

PostgreSQL Antipatterns накручиваем себе проблемы

28.06.2020 20:20:26 | Автор: admin
Некоторые ситуации в работе PostgreSQL кажутся неочевидными, пока не попытаешься детально понять, почему это работает так. Из-за незнания таких особенностей иногда разработчик сам провоцирует проблемы для нормальной работы своего приложения в будущем.

Сегодня разберем пару примеров, как неудачная организация БД и кода могут превратить наше приложение в клубок проблем:

  • накрутка serial при ON CONFLICT
  • накрутка счетчика транзакций


Накрутка serial при ON CONFLICT


Давайте представим, что нам понадобилась небольшая таблица-словарь на пару десятков тысяч записей что-то вроде списка форматов ошибок PostgreSQL.

Наше приложение (или все-таки разработчики?) любит суррогатные ключи, поэтому сразу добавим в таблицу в качестве PRIMARY KEY автоинкремент-поле с типом serial. Точнее, smallserial ведь мы точно знаем, что строк будет не больше 215:

CREATE TABLE tbl(  pk    smallserial      PRIMARY KEY, val    integer      UNIQUE);

Пытаться вставлять данные в него мы иногда будем, но новых среди них будет немного. Поэтому для удобства вставки, чтобы не заниматься обработкой исключений уникальности в своем коде, воспользуемся появившимся с версии 9.5 функционалом INSERT ... ON CONFLICT ...:

INSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING;-- 1 строкаINSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING;-- 0 строк, и никаких ошибок!

Теперь оставим наше приложение спокойно работать, и Вот ровно с такой ошибкой к нам и прибегут через несколько дней или недель:

ERROR:  nextval: reached maximum value of sequence "tbl_pk_seq" (32767)

И потом начнут приходить все чаще и чаще. Но как появилось столько записей? Почему не сработал ON CONFLICT?

Дело в том, что они и не появились. Давайте еще раз с нуля посмотрим на происходящее в нашей таблице:

TRUNCATE TABLE tbl RESTART IDENTITY;INSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;-- 1 строка: pk = 1, val = 1INSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;-- 0 строкINSERT INTO tbl(val) VALUES(2) ON CONFLICT DO NOTHING RETURNING *;-- 1 строка: pk = 3, val = 2

Как так pk = 3, ведь вставилось всего 2 строки? Мы запутались


На самом-то деле, все объясняется просто. Посмотрим внимательно, что из себя представляет наша таблица:

_tmp=# \d tbl                             Table "public.tbl" Column |   Type   | Collation | Nullable |             Default--------+----------+-----------+----------+--------------------------------- pk     | smallint |           | not null | nextval('tbl_pk_seq'::regclass) val    | integer  |           |          |Indexes:    "tbl_pkey" PRIMARY KEY, btree (pk)    "tbl_val_key" UNIQUE CONSTRAINT, btree (val)

Типы данных smallserial, serial и bigserial не являются настоящими типами, а представляют собой просто удобное средство для создания столбцов с уникальными идентификаторами (подобное свойству AUTO_INCREMENT в некоторых СУБД). В текущей реализации запись:

CREATE TABLE имя_таблицы (    имя_столбца SERIAL);

равнозначна следующим командам:

CREATE SEQUENCE имя_таблицы_имя_столбца_seq AS integer;CREATE TABLE имя_таблицы (    имя_столбца integer NOT NULL DEFAULT nextval('имя_таблицы_имя_столбца_seq'));ALTER SEQUENCE имя_таблицы_имя_столбца_seq OWNED BY имя_таблицы.имя_столбца;

То есть при определении такого типа создаётся целочисленный столбец со значением по умолчанию, извлекаемым из генератора последовательности.
То есть наш smallserial превратился в тыкву в поле smallint с DEFAULT-значением из последовательности tbl_pk_seq.

А последовательность штука нетранзакционная:
Значение, выделенное из последовательности, считается задействованным, даже если строку с этим значением не удалось вставить в таблицу. Это может произойти, например, при откате транзакции, добавляющей данные.
То есть мы сначала сгенерировали DEFAULT-значение, использовали значение pk = 2, а потом его не вставили в таблицу из-за конфликта уникальности val, скрыв проблему с помощью ON CONFLICT DO NOTHING. И после очередной такой попытки у нас просто кончилась последовательность.

Что делать?


  • хорошо
    Стараться не использовать лишние суррогатные ключи в таблицах, где уникальный ключ и так уже есть.
  • просто
    Сконвертировать поле и вместо smallserial использовать serial или bigserial это позволит продлить агонию приложения на месяцы или даже годы.
  • разумно
    Не использовать serial и ON CONFLICT на таблицах с ожидаемо существенным количеством конфликтующих вставок.
  • странно
    Написать триггер INSTEAD OF для аналогичного по структуре VIEW (или можно хранимую процедуру, но мы ведь не ищем легких путей).

Давайте в научно-познавательных целях попробуем собрать последний вариант:

CREATE TABLE tbl(  pk    smallserial      PRIMARY KEY, val    integer      UNIQUE);-- отвязываем DEFAULTALTER TABLE tbl ALTER COLUMN pk DROP DEFAULT;-- создаем "промежуточное" VIEWCREATE VIEW _tbl AS TABLE tbl;CREATE OR REPLACE FUNCTION tbl_serial() RETURNS trigger AS $$BEGIN  IF NEW.pk IS NULL THEN    LOOP -- эмуляция UPSERT через цикл      PERFORM 1 FROM tbl WHERE val = NEW.val;      EXIT WHEN FOUND; -- выходим при наличии такого значения в словаре      BEGIN        NEW.pk = nextval(pg_get_serial_sequence('tbl', 'pk'));        INSERT INTO tbl VALUES(NEW.*);        RETURN NEW;      EXCEPTION        WHEN unique_violation THEN -- защита от конкурентной вставки      END;    END LOOP;  END IF;  RETURN NULL;END;$$ LANGUAGE plpgsql;-- триггер INSTEAD OF выполняется "вместо" заказанной операции над VIEWCREATE TRIGGER serial INSTEAD OF INSERT ON _tbl  FOR EACH ROW    EXECUTE PROCEDURE tbl_serial();

Обратите внимание, что дальнейшие вставки мы производим как бы во VIEW:

INSERT INTO _tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;-- 1 строка: pk = 1, val = 1INSERT INTO _tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;-- 0 строкINSERT INTO _tbl(val) VALUES(2) ON CONFLICT DO NOTHING RETURNING *;-- 1 строка: pk = 2, val = 2

Ура! Получили ровно то, что хотели, хоть и весьма нетривиально. Поэтому все получилось аккуратно, но котик несколько насторожен.



Он понимает, что со следующей ситуацией так просто уже не разобраться.

Накрутка счетчика транзакций


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

Давайте установим некоторое предельное значение времени ожидания, на которое мы готовы пойти:

SET statement_timeout = '1s';

Но в случае возникновения ошибки мы потеряем с откатом транзакции весь достигнутый прогресс 9 записей успешно вставили, на 10-й получили таймаут и все опять заново. Чтобы не терять сразу все, воспользуемся возможностью создания точек сохранения SAVEPOINT:

BEGIN TRANSACTION;  INSERT INTO _tbl(val) SELECT 1 FROM pg_sleep(0.1); -- эмулируем задержку  SAVEPOINT sp1;  INSERT INTO _tbl(val) SELECT 2 FROM pg_sleep(0.6);  SAVEPOINT sp2;  INSERT INTO _tbl(val) SELECT 3 FROM pg_sleep(1.1);  -- ERROR:  canceling statement due to statement timeout  ROLLBACK TO SAVEPOINT sp2;COMMIT TRANSACTION;

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

_tmp=# SELECT xmin, * FROM tbl;   xmin    | pk | val-----------+----+----- 926944639 |  1 |   1 926944641 |  2 |   2(2 rows)

Вот только у наших записей оказался разный идентификатор создавшей транзакции он увеличивается с каждым вызовом SAVEPOINT.
Для детального понимания внутренней механики работы транзакций, субтранзакций и 2PC в PostgreSQL рекомендую ознакомиться со статьей Transactions in PostgreSQL and their mechanism от Movead Li.
На практике такая ситуация приводит к тому, что autovacuum: VACUUM ... (to prevent wraparound) мы будем видеть очень и очень часто, а если ресурсы сервера не резиновые это может стать проблемой.

Что делать?


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

Единственный приемлемый вариант лавировать между Сциллой и Харибдой.

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

Ну как, стало немного полегче?..

Подробнее..

Перевод Пожалуйста, прекратите использовать антипаттерн UPSERT (SQL Server)

15.03.2021 16:12:10 | Автор: admin

Для будущих учащихся на курсе "MS SQL Server Developer" подготовили перевод статьи.

Также приглашаем посмотреть открытый вебинар на тему Графовые базы данных в SQL Server. На занятии участники вместе с экспертом рассмотрят, что такое графовые базы данных и какие есть варианты работы с графами и иерархиями в SQL Server.


Я думаю, что все уже знают мое мнение о MERGE и почему я держусь от него подальше. Но вот еще один антипаттерн, который я постоянно встречаю, когда требуется выполнить UPSERT (UPdate inSERT обновить строку, если она существует, и вставить, если ее нет):

IF EXISTS (SELECT 1 FROM dbo.t WHERE [key] = @key)BEGIN  UPDATE dbo.t SET val = @val WHERE [key] = @key;ENDELSEBEGIN  INSERT dbo.t([key], val) VALUES(@key, @val); END

Это выглядит довольно логично и соответствует тому, как мы об этом думаем:

  • Существует ли строка для данного ключа (key)?

    • ДА: Обновляем эту строку.

    • НЕТ: Тогда добавляем ее.

Но это расточительно

Искать строку только для того, чтобы проверить, что она существует, а потом искать ее повторно для обновления это двойная пустая работа. И даже в том случае, если ключ проиндексирован (что, я надеюсь, всегда так). Данную логику можно изобразить в виде блок-схемы, где для каждого шага показать операцию, которая происходит в базе данных:

Обратите внимание, что в каждой ветке будут выполняться две операции с индексами.Обратите внимание, что в каждой ветке будут выполняться две операции с индексами.

Более того, если строка не существует, не используются явные транзакции и не учитывается уровень изоляции, то многое может пойти не так (помимо производительности):

  • Если ключ существует и две сессии будут выполнять UPDATE одновременно, то они обе выполнятся успешно (одна "выиграет", а "проигравшая" получит "потерянное обновление"). Само по себе это не проблема, системы с параллелизмом так и работают. Здесь Пол Уайт (Paul White) рассказывает более подробно о внутренней механике, а здесь Мартин Смит (Martin Smith) о некоторых других нюансах.

  • Если ключ не существует и обе сессии пройдут этап проверки существования ключа одинаково, то при попытке выполнить INSERT может произойти все что угодно:

    • взаимная блокировка (deadlock) из-за несовместимых блокировок;

    • нарушение ключа (key violation), которого не должно быть;

    • вставка повторяющихся значений ключа, если для столбца нет корректных ограничений.

Последний вариант самый плохой, так как данные могут быть испорчены. С взаимоблокировками и исключениями можно легко работать с помощью обработки ошибок, XACT_ABORT или повторных попыток, в зависимости от того, как часто вы ожидаете коллизии. Но если вы думаете, что при проверке IF EXISTS вы в безопасности и защищены от дубликатов (или ошибок ключей), то здесь вас ждет сюрприз. Если вы ожидаете, что столбец будет ключевым, то сделайте его официально таким и добавьте ограничения.

Многие люди говорят...

Ден Гузман (Dan Guzman) говорил о состоянии гонки более десяти лет назад в Conditional INSERT/UPDATE Race Condition, а затем в "UPSERT" Race Condition With MERGE.

Майкл Сварт (Michael Swart) также затронул эту тему несколько лет назад в Mythbusting: Concurrent Update/Insert Solutions, включая тот факт, что сохраняя исходную логику и только повышая уровень изоляции, нарушения ограничения ключа меняются на взаимные блокировки. Позже он написал про MERGE в статье Be Careful with the Merge Statement. Обязательно прочитайте все комментарии к обоим постам.

Решение

За свою карьеру я исправил множество взаимных блокировок, используя следующий паттерн (убираем избыточную проверку, оборачиваем все в транзакцию и защищаем доступ к первой таблице соответствующей блокировкой):

BEGIN TRANSACTION; UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key; IF @@ROWCOUNT = 0BEGIN  INSERT dbo.t([key], val) VALUES(@key, @val);END COMMIT TRANSACTION;

Зачем нужны два хинта? Разве UPDLOCK не достаточно?

  • UPDLOCK используется для защиты от взаимоблокировок на уровне выражения (пусть другая сессия ждет вместо того, чтобы провоцировать жертву повторить попытку).

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

Здесь немного больше кода, но он на 1000% безопаснее. И даже в худшем случае (когда строка еще не существует) он будет работать не хуже рассматриваемого антипаттерна. А в лучшем случае, если вы обновляете уже существующую строку, эффективнее, так как поиск строки выполняется только один раз. Давайте опять изобразим операции, которые происходят в базе данных:

В этом случае есть ветка, в которой выполняется только одна операция поиска по индексу.В этом случае есть ветка, в которой выполняется только одна операция поиска по индексу.

Получается следующее:

  • Если ключ существует и две сессии пытаются одновременно его обновить, то они обе по очереди обновят строку успешно, как и раньше.

  • Если ключ не существует, то одна из сессий выиграет и вставит строку. Другая сессия будет вынуждена ждать (даже для проверки строки на существование), пока не будут сняты блокировки и выполнит UPDATE.

В обоих случаях сессия, выигравшая гонку, теряет свои данные из-за того, что проигравшая обновит их после.

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

Но что, если UPDATE менее вероятен?

Очевидно, что приведенное выше решение оптимизировано для UPDATE и предполагает присутствие ключа в таблице как минимум с той же вероятностью как и отсутствие. Если вам, наоборот, нужно оптимизировать INSERT, когда INSERT более вероятен, чем UPDATE, то вы можете перевернуть логику и все еще сохранить безопасность UPSERT:

BEGIN TRANSACTION; INSERT dbo.t([key], val)   SELECT @key, @val  WHERE NOT EXISTS  (    SELECT 1 FROM dbo.t WITH (UPDLOCK, SERIALIZABLE)      WHERE [key] = @key  ); IF @@ROWCOUNT = 0BEGIN  UPDATE dbo.t SET val = @val WHERE [key] = @key;END COMMIT TRANSACTION;

Здесь также есть подход просто сделай это, если вы вслепую выполните INSERT и позволите коллизиям вызвать исключения:

BEGIN TRANSACTION; BEGIN TRY  INSERT dbo.t([key], val) VALUES(@key, @val);END TRYBEGIN CATCH  UPDATE dbo.t SET val = @val WHERE [key] = @key;END CATCH COMMIT TRANSACTION;

Стоимость подобных исключений часто превышает стоимость проверки. Важно знать приблизительную частоту попаданий / промахов. Я писал об этом здесь и здесь.

А как насчет обработки нескольких строк?

Все вышесказанное относится к одиночным INSERT / UPDATE, но Джастин Пилинг (Justin Pealing) спросил, как быть с несколькими строками, когда неизвестно, какие из них уже существуют?

Если вы передаете список строк через что-то вроде табличного параметра (TVP, Table-Valued Parameters), то сделайте UPDATE с JOIN, а затем INSERT, используя NOT EXISTS. Подход в целом здесь остается таким же, как описано выше:

CREATE PROCEDURE dbo.UpsertTheThings    @tvp dbo.TableType READONLYASBEGIN  SET NOCOUNT ON;   BEGIN TRANSACTION;   UPDATE t WITH (UPDLOCK, SERIALIZABLE)     SET val = tvp.val  FROM dbo.t AS t  INNER JOIN @tvp AS tvp    ON t.[key] = tvp.[key];   INSERT dbo.t([key], val)    SELECT [key], val FROM @tvp AS tvp    WHERE NOT EXISTS (SELECT 1 FROM dbo.t WHERE [key] = tvp.[key]);   COMMIT TRANSACTION;END

Если вы получаете список строк каким-то другим способом, отличным от TVP (XML, список с разделителями-запятыми и т.п.), то сначала преобразуйте их в таблицу и потом сделайте JOIN к нужным данным. Будьте осторожны при оптимизации этого кода под первоначальный INSERT потенциально можно выполнить UPDATE для некоторых строк дважды.

Выводы

Рассмотренные UPSERT-паттерны лучше того, с чем мне часто приходится сталкиваться, и, я надеюсь, что вы начнете их использовать. Я буду давать ссылку на этот пост всякий раз, когда буду видеть паттерн IF EXIST. И еще хочу передать привет Полу Уайту (Paul White, sql.kiwi | @SQK_Kiwi) он так хорошо умеет объяснять сложные концепции простыми словами.

Для использования MERGE у вас должна быть либо веская причина (возможно, вам нужна какая-то маловразумительная MERGE-функциональность), либо вы не восприняли вышеприведенные ссылки всерьез.


Узнать подробнее о курсе "MS SQL Server Developer".

Смотреть открытый вебинар на тему Графовые базы данных в SQL Server.

Подробнее..

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru