PostgreSQL Antipatterns: ваюем з ордамі "мерцвякоў"

Асаблівасці працы ўнутраных механізмаў PostgreSQL дазваляюць яму быць вельмі хуткім у адных сітуацыя і "не вельмі" у іншых. Сёння спынімся на класічным прыкладзе канфлікту паміж тым, як працуе СКБД і тым, што робіць з ёй распрацоўшчык. UPDATE vs прынцыпы MVCC.

Коратка сюжэт з выдатнага артыкула:

Калі радок змяняецца камандай UPDATE, фактычна выконваюцца дзве аперацыі: DELETE і INSERT. У бягучай версіі радка усталёўваецца xmax, роўны нумару транзакцыі, якая выканала UPDATE. Затым ствараецца новая версія таго ж радка; значэнне xmin у яе супадае са значэннем xmax папярэдняй версіі.

Праз нейкі час пасля завяршэння гэтай транзакцыі старая ці новая версіі, у залежнасці ад COMMIT/ROOLBACK, будуць прызнаныя мёртвымі (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