Cechy działania wewnętrznych mechanizmów PostgreSQL sprawiają, że jest on bardzo szybki w jednych sytuacjach, a "nie bardzo" w innych. Dziś skupimy się na klasycznym przykładzie konfliktu między tym, jak działa DBMS a tym, co robi z nią programista — UPDATE vs zasady MVCC.
Krótka fabuła z :
Kiedy wiersz jest zmieniany przez polecenie UPDATE, w rzeczywistości wykonywane są dwie operacje: DELETE i INSERT. W bieżącej wersji wiersza ustawiane jest xmax, równe numerowi transakcji, która wykonała UPDATE. Następnie tworzona jest nowa wersja ta sama wersja wiersza; wartość xmin w niej zgadza się z wartością xmax poprzedniej wersji.
Po pewnym czasie po zakończeniu tej transakcji stara lub nowa wersja, w zależności od COMMIT/ROLLBACK, zostanie uznana za "martwe" (dead tuples) podczas przeszukiwania VACUUM tabeli i będą usuwane.
Jednak nie nastąpi to od razu, a kłopoty z "martwymi" rekordami można napotkać bardzo szybko — przy wielokrotnym lub w dużej tabeli, a chwilę później natrafić na sytuację, że nawet .
#1: I Like To Move It
Załóżmy, że twój sposób w logice biznesowej działa sobie, a nagle zdajesz sobie sprawę, że warto zaktualizować pole X w jakimś rekordzie:
UPDATE tbl SET X = <newX> WHERE pk = $1;Potem, w trakcie realizacji, okazuje się, że pole Y również trzeba zaktualizować:
UPDATE tbl SET Y = <newY> WHERE pk = $1;… a potem jeszcze Z — czemu nie?
UPDATE tbl SET Z = <newZ> WHERE pk = $1;Ile wersji tego rekordu teraz mamy w bazie? Aha, 4 sztuki! Z nich jedna aktualna, a 3 trzeba będzie sprzątnąć za pomocą [auto]VACUUM.
Nie rób tego! Użyj aktualizacji wszystkich pól w jednym zapytaniu. - niemal zawsze logikę działania metody można tak zmienić:
UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;#2: Use IS DISTINCT FROM, Luke!
Zatem, mimo wszystko, zapragnąłeś (w trakcie wykonywania skryptu lub konwertera, na przykład). I do skryptu leci coś takiego:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;W takim mniej więcej formacie zapytanie pojawia się dość często i prawie zawsze nie w celu wypełnienia pustego nowego pola, a z powodu korekty jakichś błędów w danych. Przy tym sama poprawność już istniejących danych w ogóle nie jest brana pod uwagę - a szkoda! Oznacza to, że zapis jest nadpisywany, nawet jeśli zawierał dokładnie to, co chciałeś — po co? Poprawmy to:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>; Wiele osób nie zdaje sobie sprawy z istnienia tak wspaniałego operatora, dlatego oto ściągawka na temat IS DISTINCT FROM i innych operatorów logicznych:
… i trochę o operacjach na skomplikowanych ROW()-wyrażeniach:
#3: А я милого узнаю по… блокировке
Uruchamiane są dwa identyczne równoległe procesy, z których każdy próbuje oznaczyć rekord, że jest "w trakcie przetwarzania":
UPDATE tbl SET processing = TRUE WHERE pk = $1;Nawet jeśli te procesy rzeczywiście robią niezależne od siebie rzeczy, to w ramach jednego ID, w tym zapytaniu drugi klient „zablokuje się”, dopóki nie zakończy się pierwsza transakcja.
Rozwiązanie nr 1: zadanie zostało sprowadzone do poprzedniego
Po prostu ponownie dodamy IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;W tej formie drugie zapytanie po prostu nic nie zmieni w bazie, bo już „wszystko jak trzeba” — dlatego blokada nie wystąpi. Dalej fakt „nienałożenia” rekordu już obsługujemy w aplikacyjnym algorytmie.
Rozwiązanie nr 2: blokady doradcze
Duży temat na osobny artykuł, w którym można przeczytać o .
Rozwiązanie nr 3: bez[ d]urnych wywołań
A tu na pewno musi odbywać się równoległa praca z tym samym rekordem? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?..
Źródło: habr.com
