PostgreSQL Антипатерни: борба с ордите на „мъртвите“.

Характеристиките на вътрешните механизми на PostgreSQL му позволяват да бъде много бърз в определени ситуации и 'не толкова' в други. Днес ще се спрем на класическия пример за конфликт между начина, по който работи СУБД и това, което прави с него разработчикът — UPDATE срещу принципи на MVCC.

Накратко извод от отлична статия:

Когато редът се променя с командата UPDATE, всъщност се извършват две операции: DELETE и INSERT. В текущата версия на реда се задава xmax, равно на номера на транзакцията, извършила UPDATE. След това се създава нова версия на същия ред; стойността на xmin при него съвпада с стойността на xmax на предишната версия.

След известно време след завършването на тази транзакция старата или новата версия, в зависимост от COMMIT/ROLLBACK, ще бъдат признати за 'мъртви' (dead tuples) при обхождане VACUUM по таблицата и ще бъдат почистени.

Но това ще се случи не веднага, а проблемите с 'мъртвците' могат бързо да възникнат — при многократни или масови обновления на записи в голяма таблица, а по-късно да се срещнете с ситуация, в която и VACUUM не може да помогне.

#1: I Like To Move It

Да предположим, че вашият метод по бизнес логика работи, и изведнъж осъзнава, че е необходимо да обновите поле X в някаква запис:

UPDATE tbl SET X = <newX> WHERE pk = $1;

После, по време на изпълнението, става ясно, че и поле Y е необходимо да се обнови:

UPDATE tbl SET Y = <newY> WHERE pk = $1;

… а след това и Z — защо да се спираме?

UPDATE tbl SET Z = <newZ> WHERE pk = $1;

Колко версии на този запис сега имаме в базата? А, 4!!! От тях една е актуална, а 3 трябва да почистите чрез [auto]VACUUM.

Не правете така! Използвайте обновяване на всички полета с една заявка — почти винаги логиката на метода може да бъде променена така:

UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;

#2: Use IS DISTINCT FROM, Luke!

И така, в крайна сметка решихте да обновите много-много записи в таблицата (в хода на изпълнение на сценария или конвертора, например). И в сценария лети нещо такова:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;

Приблизително в такъв вид заявката е достатъчно често срещана и почти винаги не е за попълване на празно ново поле, а за коригиране на някакви грешки в данните. При това самата коректност на вече съществуващите данни изобщо не се взима под внимание — и заради това е жалко! Тоест записът се презаписва, дори ако там е имало точно това, което искате — а защо? Нека поправим:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;

Много хора не знаят за съществуването на този страхотен оператор, затова ето шпаргалка по IS DISTINCT FROM и други логически оператори в помощ:

… и малко за операции с комплексни ROW()-изрази:

#3: А я милого узнаю по… блокировке

Стартират се два идентични паралелни процеса, всеки от които се опитва да обозначи запис, че е "в процес на работа":

UPDATE tbl SET processing = TRUE WHERE pk = $1;

Дори ако тези процеси извършват независими действия помежду си, в рамките на един и същи ID, при този запитване вторият клиент "ще се блокира", докато не приключи първата транзакция.

Решение №1: задачата е сведена до предишното

Просто отново ще добавим IS DISTINCT FROM:

UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;

В такъв вид второто запитване просто няма да променя нищо в базата, там и така вече "всичко е как трябва" — следователно блокировка няма да възникне. След това фактът, че "записът не се намира", вече ще бъде обработен в приложния алгоритъм.

Решение №2: advisory locks

Голяма тема за отделна статия, в която можете да прочетете за начини за прилагане и "грабли" на препоръчителните блокировки.

Решение №3: без[д]умни повиквания

А е точно-точно да се случва едновременна работа с един и същи запис? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?..

Източник: habr.com