Anomalia odczytu Phantom w Oracle i PostgreSQL nie wycofuje transakcji

Zauważyłem następujące wystąpienie zarówno w Oracle, jak i PostgreSQL.

Biorąc pod uwagę, że mamy następujący schemat bazy danych: 

create table post (
    id int8 not null, 
    title varchar(255), 
    version int4 not null, 
    primary key (id));    

create table post_comment (
    id int8 not null, 
    review varchar(255), 
    version int4 not null, 
    post_id int8, 
    primary key (id));

alter table post_comment 
    add constraint FKna4y825fdc5hw8aow65ijexm0 
    foreign key (post_id) references post;

Z następującymi danymi: 

insert into post (title, version, id) values ('Transactions', 0, 1);
insert into post_comment (post_id, review, version, id) 
    values (1, 'Post comment 1', 459, 0);
insert into post_comment (post_id, review, version, id) 
    values (1, 'Post comment 2', 537, 1);
insert into post_comment (post_id, review, version, id) 
    values (1, 'Post comment 3', 689, 2);

Jeśli otworzę dwie oddzielne Konsole SQL i wykonam następujące polecenia: 

TX1: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

TX2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

TX1: SELECT COUNT(*) FROM post_comment where post_id = 1;

TX1: > 3

TX1: UPDATE post_comment SET version = 100 WHERE post_id = 1;

TX2: INSERT INTO post_comment (post_id, review, version, id) VALUES (1, 'Phantom', 0, 1000);

TX2: COMMIT;

TX1: SELECT COUNT(*) FROM post_comment where post_id = 1;

TX1: > 3

TX1: COMMIT;

TX3: SELECT * from post_comment;

     > 0;"Post comment 0";100;1
       1;"Post comment 1";100;1
       2;"Post comment 2";100;1
       1000;"Phantom";0;1

Zgodnie z oczekiwaniami, poziom izolacji SERIALIZABLE zachował dane migawki od początku transakcji TX1 i TX1 widzi tylko 3 rekordy post_comment.

Ze względu na model MVCC w Oracle i PostgreSQL, TX2 może wstawić nowy rekord i zatwierdzić.

Dlaczego TX1 moĹźe siÄ ™ zatwierdzać? Ponieważ jest to anomalia odczytu phantom, spodziewałem się, że TX1 zostanie wycofany z "wyjątkiem awarii serializacji" lub czymś podobnym.

Czy Model Serializowalny MVCC w PostgreSQL i Oracle oferuje tylko gwarancję izolacji migawek, ale nie wykrywa anomalii odczytu phantom?

UPDATE

Zmieniłem nawet Tx1, aby wydać oświadczenie UPDATE, że zmienia kolumnę version dla wszystkich rekordów post_comment należących do tego samego post.

W ten sposĂłb, Tx2 tworzy nowy rekord i TX1 bÄ ™ dzie zatwierdzaÄ ‡ nie wiedzÄ ... c, Ĺźe zostaĹ 'dodany nowy rekord, ktĂłry speĹ' niĹ ' kryteria filtrowania aktualizacji.

W zasadzie, jedynym sposobem na to, aby PostgreSQL nie powiódł się jest wykonanie następującego zapytania COUNT w Tx2, przed wstawieniem rekordu phantom: 

Tx2: SELECT COUNT(*) FROM post_comment where post_id = 1 and version = 0

TX2: INSERT INTO post_comment (post_id, review, version, id) VALUES (1, 'Phantom', 0, 1000);

TX2: COMMIT;

Wtedy Tx1 zostanie wycofany z: 

org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
  Detail: Reason code: Canceled on identification as a pivot, during conflict out checking.
  Hint: The transaction might succeed if retried.

Najprawdopodobniej że mechanizm zapobiegania anomaliom zapisu skośnego wykrył tę zmianę i cofnął transakcję.

Ciekawe, że Oracle zdaje się nie przejmować tą anomalią, więc TX1 po prostu spełnia się pomyślnie. PoniewaĹź Oracle nie zapobiega wystÄ ™ powaniu write-skew, Tx1 wĹ ' aĹ " nie dostosowuje jÄ ™ zyka.

Przy okazji, możesz uruchomić wszystkie te przykłady samodzielnie, ponieważ są one naGitHub .

Author: Jakub Korab, 2016-09-19
Source

4 answers

To, co obserwujesz, to a nie widmo odczytu. Byłoby tak, gdyby nowy wiersz pojawił się po wydaniu zapytania po raz drugi(pojawiają się nieoczekiwanie).

Jesteś chroniony przed odczytem phantom zarówno w Oracle, jak i PostgreSQL za pomocą izolacji SERIALIZABLE.

Różnica między Oracle a PostgreSQL polega na tym, że SERIALIZABLE poziom izolacji w Oracle oferuje tylko izolację migawek (co jest wystarczająco dobre, aby nie pojawiać się Phantomów), podczas gdy w PostgreSQL gwarantuje to true serializability (tzn. zawsze istnieje serializacja poleceń SQL, która prowadzi do tych samych wyników). Jeśli chcesz uzyskać to samo w Oracle i PostgreSQL, użyj izolacji REPEATABLE READ w PostgreSQL.

 3
Author: Laurenz Albe,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-09-19 07:30:04

Uwielbiam to pytanie, ponieważ pokazuje, że definicja Phantom Read w standardzie SQL tylko obrazuje efekt bez podania przyczyny tej anomalii danych:

P3 ("Phantom"): SQL-transakcja T1 odczytuje zbiór wierszy N to zadowala niektórych . SQL-transaction T2 then wykonuje polecenia SQL, które generują jeden lub więcej wierszy, które used by SQL-transaction T1. Jeśli SQL-transaction T1 następnie powtarza początkowy odczyt z tym samym , otrzymuje inny zbiór wierszy.

W 1995 roku, Jim Gray i co, krytyka poziomów izolacji ANSI SQL, opisali Phantom Read jako:

P3: r1 [P]...w2 [y W P]...(C1 lub A1) (Phantom)

Jedną z ważnych uwag jest to, że ANSI SQL P3 zakazuje tylko wstawiania (i aktualizacje, według niektórych interpretacji) do orzeczenia, natomiast definicja P3 powyższy cytat pochodzi ze strony: po odczytaniu predykatu-zapis może być wkładką, zaktualizuj lub usuń.

Dlatego odczyt Phantom nie oznacza, że możesz po prostu zwrócić migawkę od początku bieżącej transakcji i udawać, że dostarczenie tego samego wyniku dla zapytania ochroni Cię przed rzeczywistą anomalią odczytu Phantom.

W oryginalnej implementacji SQL Server 2PL (dwufazowe blokowanie), zwracając ten sam wynik dla zapytania impliced Predicate Locks.

Izolacja migawek MVCC (Multi-Version Concurrency Control) (błędnie nazwana Serializowalna w Oracle) w rzeczywistości nie uniemożliwia innym transakcjom wstawiania/usuwania wierszy, które spełniają te same kryteria filtrowania, z zapytaniem, które zostało już wykonane i zwróciło wynik ustawiony w bieżącej transakcji.

Z tego powodu możemy sobie wyobrazić następujący scenariusz, w którym chcemy zastosować podwyżkę do wszystkich pracownicy:

  1. Tx1: SELECT SUM(salary) FROM employee where company_id = 1;
  2. Tx2: INSERT INTO employee (id, name, company_id, salary) VALUES (100, 'John Doe', 1, 100000);
  3. Tx1: UPDATE employee SET salary = salary * 1.1;
  4. Tx2: COMMIT;
  5. Tx1: COMMIT:

W tym scenariuszu CEO prowadzi pierwszą transakcję (Tx1), więc:

    Najpierw sprawdza sumę wszystkich pensji w swojej firmie.
  1. tymczasem dział HR prowadzi drugą transakcję (Tx2), ponieważ właśnie udało im się zatrudnić Johna Doe i dać mu 100 tysięcy dolarów wynagrodzenia.
  2. prezes decyduje, że podwyżka o 10% jest 100 tys.), a w 2009 r. - 100 tys. (w 2009 r. - 100 tys.).]}
  3. tymczasem transakcja HR Tx2 jest zobowiązana.
  4. transakcja CEO TX1 jest zobowiązana.
Boom! Prezes podjął decyzję w sprawie starej migawki, dając podwyżkę, która może nie być utrzymana przez aktualny zaktualizowany budżet wynagrodzeń.

Szczegółowe wyjaśnienie tego przypadku użycia (z dużą ilością diagramów) można zobaczyć w poniżej post .

Czy to widmo odczytu czy zapisu pochylenia ?

Według Jima Graya i co , jest to odczyt fantomowy, ponieważ zapis skośny jest zdefiniowany jako:

A5b zapis skośny przypuśćmy, że T1 czyta x i y, które są zgodne z C(), a następnie T2 czyta x i y, zapisuje x i commity. Następnie T1 zapisuje y. gdyby było ograniczenie między x I y, to może być naruszone. Pod względem historii:

A5B: r1 [x]...r2 [y]...w1 [y]...w2 [x]...(c1 i C2)

W Oracle Menedżer transakcji może wykryć powyższą anomalię, ponieważ nie używa blokad predykatów ani blokad zakresu indeksów (blokad następnego klucza) , takich jak MySQL.

PostgreSQL udaje się złapać tę anomalię tylko wtedy, gdy Bob wystawia odczyt z tabeli pracowników, w przeciwnym razie zjawisko nie jest zapobiegane.

UPDATE

Początkowo zakładałem, że Serializowalność oznaczałaby również uporządkowanie czasu. Jednak jako bardzo dobrze wyjaśnione przez Petera Bailisa , kolejność zegarów ściennych lub Linearyzowalność zakłada się tylko dla ścisłej Serializowalności.

Dlatego moje założenia zostały przyjęte dla ścisłego systemu Serializowalnego. Ale to nie jest to, co Serializable ma oferować. Model izolacji Serializowalnej nie gwarantuje czasu, a operacje mogą być ponownie uporządkowane, o ile są równoważne z pewnym wykonaniem szeregowym.

Dlatego zgodnie z Serializowanym definicja, taki odczyt fantomowy może wystąpić, jeśli druga transakcja nie wyda żadnego odczytu. Ale w modelu strict Serializowalnym, oferowanym przez 2PL, odczyt Phantom zostałby zablokowany, nawet jeśli druga transakcja nie wystawi odczytu na te same wpisy, które staramy się chronić przed odczytem phantom.

 3
Author: Vlad Mihalcea,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-04 18:59:24

Dokumentacja Postgres definiuje odczyt phantom jako:

Transakcja ponownie wykonuje zapytanie zwracające zestaw wierszy, które spełniają warunek wyszukiwania i stwierdza, że zbiór wierszy spełniających stan uległ zmianie z powodu kolejnej niedawno dokonanej transakcji.

Ponieważ select zwraca tę samą wartość zarówno przed, jak i po zatwierdzeniu drugiej transakcji, nie spełnia ona kryteriów odczytu phantom.

 0
Author: Andomar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-09-19 07:30:39

Chciałem tylko zaznaczyć, że odpowiedź Vlada Mihalcei jest po prostu błędna.

Czy to widmo odczytu czy zapisu?

Żaden z tych -- nie ma tu anomalii, transakcje można serializować jako Tx1 - > Tx2.

Stany standardowe SQL: "Serializowalne wykonanie jest zdefiniowane jako wykonanie operacji jednocześnie wykonujących transakcje SQL, które daje taki sam efekt jak niektóre seryjne wykonanie tych samych SQL-transakcje."

PostgreSQL udaje się złapać tę anomalię tylko wtedy, gdy Bob wystawia odczyt z tabeli pracowników, w przeciwnym razie zjawisko nie jest zapobiegane.

Zachowanie PostgreSQL jest tutaj w 100% poprawne, po prostu "odwraca" pozorną kolejność transakcji.

 0
Author: Yaroslav Schekin,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-11 19:15:15