Problem z blokowaniem współbieżnego DELETE / INSERT w PostgreSQL

Jest to dość proste, ale jestem zaskoczony tym, co robi PG (v9.0). Zaczynamy od prostego stołu:

CREATE TABLE test (id INT PRIMARY KEY);

i kilka rzędów:

INSERT INTO TEST VALUES (1);
INSERT INTO TEST VALUES (2);

Korzystając z mojego ulubionego narzędzia do wysyłania zapytań JDBC (ExecuteQuery), łączę dwa okna sesji z db, w którym znajduje się ta tabela. Oba są transakcyjne (tj. Automatyczne zatwierdzanie = fałsz). Nazwijmy je S1 i S2.

Ten sam bit kodu dla każdego:

1:DELETE FROM test WHERE id=1;
2:INSERT INTO test VALUES (1);
3:COMMIT;

Teraz uruchom to w zwolnionym tempie, wykonując pojedynczo w oknach.

S1-1 runs (1 row deleted)
S2-1 runs (but is blocked since S1 has a write lock)
S1-2 runs (1 row inserted)
S1-3 runs, releasing the write lock
S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???
S2-2 runs, reports a unique key constraint violation

Teraz działa to dobrze w SQLServer. Gdy S2 usuwa, zgłasza usunięcie 1 wiersza. A potem wkładka S2 działa dobrze.

Podejrzewam, że PostgreSQL blokuje indeks w tabeli, w której istnieje ten wiersz, podczas gdy SQLServer blokuje rzeczywistą wartość klucza.

Czy mam rację? Czy da się to zrobić?

Mat i Erwin mają rację, a ja dodam tylko kolejną odpowiedź, aby rozwinąć to, co powiedzieli, w sposób, który nie mieści się w komentarzu. Ponieważ ich odpowiedzi nie wydają się satysfakcjonujące dla wszystkich i pojawiła się sugestia, że ​​należy skonsultować się z programistami PostgreSQL, a ja jestem jednym z nich, omówię to szczegółowo.

Ważną kwestią jest to, że zgodnie ze standardem SQL w ramach transakcji działającej na READ COMMITTEDpoziomie izolacji transakcji ograniczenie polega na tym, że praca niezaangażowanych transakcji nie może być widoczna. Kiedy praca zatwierdzonych transakcji staje się widoczna, zależy od implementacji. Wskazujesz na różnicę w sposobie, w jaki dwa produkty zdecydowały się to wdrożyć. Żadna z implementacji nie narusza wymagań normy.

Oto, co dzieje się w PostgreSQL:

Uruchamia S1-1 (usunięto 1 wiersz)

Stary wiersz pozostaje na miejscu, ponieważ S1 nadal może się wycofać, ale S1 trzyma teraz blokadę w wierszu, dzięki czemu każda inna sesja próbująca zmodyfikować wiersz będzie czekać na sprawdzenie, czy S1 się wycofuje, czy wycofuje. Wszelkie odczyty tabeli nadal widzą stary wiersz, chyba że spróbują go zablokować za pomocą SELECT FOR UPDATElub SELECT FOR SHARE.

S2-1 działa (ale jest zablokowane, ponieważ S1 ma blokadę zapisu)

S2 musi teraz poczekać, aby zobaczyć wynik S1. Jeśli S1 miałby wycofać, a nie zatwierdzić, S2 usunąłby wiersz. Zauważ, że gdyby S1 wstawił nową wersję przed wycofaniem, nowa wersja nigdy nie byłaby tam z perspektywy innej transakcji, ani też stara wersja nie zostałaby usunięta z perspektywy jakiejkolwiek innej transakcji.

S1-2 działa (wstawiono 1 wiersz)

Ten wiersz jest niezależny od starego. Gdyby była aktualizacja wiersza o id = 1, stare i nowe wersje byłyby powiązane, a S2 mógłby usunąć zaktualizowaną wersję wiersza, gdy został odblokowany. To, że nowy wiersz ma takie same wartości, jak jakiś wiersz, który istniał w przeszłości, nie czyni go tym samym, co zaktualizowana wersja tego wiersza.

S1-3 działa, zwalniając blokadę zapisu

Tak więc zmiany S1 są utrwalane. Jeden rząd zniknął. Dodano jeden wiersz.

S2-1 działa, teraz, gdy może uzyskać blokadę. Ale raporty 0 wierszy usunięte. Co ???

To, co dzieje się wewnętrznie, polega na tym, że istnieje wskaźnik z jednej wersji wiersza do następnej wersji tego samego wiersza, jeśli jest aktualizowany. Jeśli wiersz zostanie usunięty, nie będzie następnej wersji. Gdy READ COMMITTEDtransakcja budzi się z bloku konfliktu zapisu, następuje ten łańcuch aktualizacji do końca; jeśli wiersz nie został usunięty i jeśli nadal spełnia kryteria wyboru zapytania, zostanie przetworzony. Ten wiersz został usunięty, więc zapytanie S2 przechodzi dalej.

S2 może, ale nie musi dostać się do nowego wiersza podczas skanowania tabeli. Jeśli tak, zobaczy, że nowy wiersz został utworzony po DELETEuruchomieniu instrukcji S2 , a zatem nie jest częścią zestawu wierszy widocznych dla niego.

Gdyby PostgreSQL zrestartował całą instrukcję DELETE S2 od początku z nową migawką, działałby tak samo jak SQL Server. Społeczność PostgreSQL nie wybrała tego ze względu na wydajność. W tym prostym przypadku nigdy nie zauważyłbyś różnicy w wydajności, ale gdybyś był dziesięć milionów wierszy w czasie, DELETEgdy zostałeś zablokowany, na pewno byś to zrobił. Jest tu kompromis, w którym PostgreSQL wybrał wydajność, ponieważ szybsza wersja nadal spełnia wymagania standardu.

S2-2 działa, zgłasza wyjątkowe naruszenie ograniczenia klucza

Oczywiście wiersz już istnieje. To najmniej zaskakująca część obrazu.

Chociaż zachodzi tutaj zaskakujące zachowanie, wszystko jest zgodne ze standardem SQL i mieści się w zakresie tego, co jest „specyficzne dla implementacji” zgodnie ze standardem. Z pewnością może być zaskakujące, jeśli zakładasz, że zachowanie niektórych innych implementacji będzie obecne we wszystkich implementacjach, ale PostgreSQL bardzo stara się uniknąć błędów serializacji na READ COMMITTEDpoziomie izolacji i pozwala na niektóre zachowania, które różnią się od innych produktów, aby to osiągnąć.

Teraz osobiście nie jestem wielkim fanem READ COMMITTEDpoziomu izolacji transakcji we wdrażaniu jakiegokolwiek produktu. Wszystkie pozwalają warunkom wyścigowym na tworzenie zaskakujących zachowań z transakcyjnego punktu widzenia. Gdy ktoś przyzwyczai się do dziwnych zachowań dozwolonych przez jeden produkt, zwykle uważa to za „normalne”, a kompromisy wybrane przez inny produkt są dziwne. Ale każdy produkt musi mieć jakąś kompromis dla dowolnego trybu, który nie został zaimplementowany jako SERIALIZABLE. Tam, gdzie programiści PostgreSQL postanowili wprowadzić tę linię, READ COMMITTEDminimalizują blokowanie (odczyty nie blokują zapisów i zapisy nie blokują odczytów) oraz minimalizują ryzyko niepowodzenia serializacji.

Standard wymaga, aby SERIALIZABLEtransakcje były domyślne, ale większość produktów tego nie robi, ponieważ powoduje to spadek wydajności w porównaniu z bardziej swobodnymi poziomami izolacji transakcji. Niektóre produkty nawet nie zapewniają prawdziwie serializowanych transakcji, gdy SERIALIZABLEzostaną wybrane - w szczególności Oracle i wersje PostgreSQL wcześniejszych niż 9.1. Ale korzystanie z prawdziwych SERIALIZABLEtransakcji jest jedynym sposobem na uniknięcie zaskakujących skutków warunków wyścigu, a SERIALIZABLEtransakcje zawsze muszą albo blokować, aby uniknąć warunków wyścigu, albo wycofać niektóre transakcje, aby uniknąć rozwijających się warunków wyścigu. Najczęstszą implementacją SERIALIZABLEtransakcji jest ścisłe blokowanie dwufazowe (S2PL), które ma zarówno błędy blokowania, jak i serializacji (w postaci zakleszczeń).

Pełne ujawnienie: Współpracowałem z Danem Ports z MIT, aby dodać naprawdę możliwe do serializacji transakcje do PostgreSQL w wersji 9.1 przy użyciu nowej techniki o nazwie Serializable Snapshot Isolation.

Uważam, że jest to zgodne z projektem, zgodnie z opisem zatwierdzonego do odczytu poziomu izolacji dla PostgreSQL 9.2:

Komendy UPDATE, DELETE, SELECT FOR UPDATE i SELECT FOR SHARE zachowują się tak samo jak SELECT pod względem wyszukiwania wierszy docelowych: znajdą tylko wiersze docelowe, które zostały zatwierdzone od czasu uruchomienia polecenia ¹ . Jednak taki wiersz docelowy mógł być już zaktualizowany (lub usunięty lub zablokowany) przez inną współbieżną transakcję do czasu jego znalezienia. W takim przypadku niedoszły aktualizator zaczeka na zatwierdzenie lub wycofanie pierwszej transakcji aktualizacji (jeśli nadal trwa). Jeśli pierwszy aktualizator cofa się, wówczas jego efekty są negowane, a drugi aktualizator może kontynuować aktualizację pierwotnie znalezionego wiersza. Jeśli pierwszy aktualizator się zatwierdzi, drugi aktualizator zignoruje wiersz, jeśli pierwszy aktualizator go usunie ², w przeciwnym razie spróbuje zastosować swoje działanie do zaktualizowanej wersji wiersza.

Wiersz włożeniu w S1nie istnieje jeszcze kiedy S2„s DELETEzaczęło. Nie będzie to widoczne podczas usuwania S2zgodnie z ( ¹ ) powyżej. Ten, który został S1usunięty, jest ignorowany przez S2s DELETEzgodnie z ( ² ).

W S2ten sposób usunięcie nic nie robi. Gdy wkładka przychodzi jednak, że jeden robi zobaczyć S1„s wkładki:

Ponieważ tryb Read Committed rozpoczyna każdą komendę od nowej migawki, która obejmuje wszystkie transakcje zatwierdzone do tej chwili, kolejne polecenia w tej samej transakcji będą w każdym przypadku widzieć skutki zatwierdzonej transakcji współbieżnej . Kwestią powyżej jest to, czy jedno polecenie widzi absolutnie spójny widok bazy danych.

Próba wstawienia przez użytkownika S2kończy się niepowodzeniem z naruszeniem ograniczenia.

Kontynuowanie czytania tego dokumentu, korzystanie z powtarzalnego odczytu lub nawet serializacji nie rozwiąże całkowicie problemu - druga sesja zakończy się niepowodzeniem z błędem serializacji podczas usuwania.

Pozwoliłoby to jednak ponowić transakcję.

Całkowicie zgadzam się z doskonałą odpowiedzią @ Mat . Piszę tylko inną odpowiedź, ponieważ nie pasuje do komentarza.

W odpowiedzi na Twój komentarz: DELETEw S2 jest już podpięty do określonej wersji wiersza. Ponieważ w międzyczasie zabija go S1, S2 uważa się za udany. Chociaż nie jest to oczywiste na pierwszy rzut oka, seria wydarzeń jest prawie taka:

   S1 DELETE powiodło się  
S2 DELETE (pomyślne przez proxy - USUŃ z S1)  W międzyczasie 
   S1 ponownie WSTAWIA usuniętą wartość  
S2 INSERT kończy się niepowodzeniem z unikalnym naruszeniem ograniczenia klucza

Wszystko zgodnie z projektem. Naprawdę musisz użyć SERIALIZABLEtransakcji dla swoich wymagań i upewnij się, że spróbujesz ponownie po awarii serializacji.

Użyj DEFERRABLE klucza podstawowego i spróbuj ponownie.

Napotkaliśmy także ten problem. Nasze rozwiązanie dodaje już select ... for updatewcześniej delete from ... where. Poziom izolacji musi być zatwierdzony do odczytu.