Kiedy używać SELECT… FOR UPDATE?


119

Proszę, pomóż mi zrozumieć przypadek użycia SELECT ... FOR UPDATE.

Pytanie 1 : Czy poniższy przykład jest dobrym przykładem, kiedy SELECT ... FOR UPDATEnależy stosować?

Dany:

  • pokoje [id]
  • tagi [identyfikator, nazwa]
  • room_tags [room_id, tag_id]
    • room_id i tag_id to klucze obce

Aplikacja chce wyświetlić wszystkie pokoje i ich tagi, ale musi rozróżnić pokoje bez tagów od tych, które zostały usunięte. Jeśli SELECT ... FOR UPDATE nie jest używany, może się zdarzyć:

  • Początkowo:
    • pokoje zawiera [id = 1]
    • tagi zawiera [id = 1, name = 'cats']
    • room_tags zawiera [room_id = 1, tag_id = 1]
  • Wątek 1: SELECT id FROM rooms;
    • returns [id = 1]
  • Wątek 2: DELETE FROM room_tags WHERE room_id = 1;
  • Wątek 2: DELETE FROM rooms WHERE id = 1;
  • Wątek 2: [zatwierdza transakcję]
  • Wątek 1: SELECT tags.name FROM room_tags, tags WHERE room_tags.tag_id = 1 AND tags.id = room_tags.tag_id;
    • zwraca pustą listę

Teraz wątek 1 uważa, że ​​pokój 1 nie ma tagów, ale w rzeczywistości pokój został usunięty. Aby rozwiązać ten problem, Wątek 1 powinien SELECT id FROM rooms FOR UPDATEzapobiegać usuwaniu wątku 2 od roomsmomentu zakończenia Wątku 1. Czy to jest poprawne?

Pytanie 2 : Kiedy należy używać SERIALIZABLEizolacji transakcji, a READ_COMMITTEDkiedy z SELECT ... FOR UPDATE?

Odpowiedzi powinny być przenośne (nie specyficzne dla bazy danych). Jeśli to niemożliwe, wyjaśnij dlaczego.


2
Którego systemu RDBMS używasz?
Quassnoi

2
@Quassnoi, jak wspomniano na dole pytania, szukam rozwiązania przenośnego (nie specyficznego dla bazy danych).
Gili

2
Czy są opcje, REPEATABLE_READa READ_COMMITTEDnawet opcje przenośne? Jedyne wyniki, które otrzymuję, dotyczą serwera MSSQL
Billy ONeal

3
@BillyONeal: zwróć uwagę, że tryby izolacji gwarantują, że nie zobaczysz dziwactw, na które nie pozwalają, ale nie mów nic o dziwactwach, na które pozwalają. Oznacza to, że ustawienie, powiedzmy, READ COMMITTEDtrybu nie definiuje, czy rzeczywiście zobaczysz rekordy zatwierdzone przez inną transakcję: zapewnia tylko, że nigdy nie zobaczysz niezatwierdzonych rekordów.
Quassnoi

3
select ... for updateNa roomswciąż pozwalają room_tagsbyć usunięte, ponieważ są osobne tabele. Czy chodziło Ci o pytanie, czy for updateklauzula zapobiegnie usunięciom rooms?
Chris Saxon

Odpowiedzi:


84

Jedynym przenośnym sposobem na osiągnięcie spójności między pokojami i tagami oraz zapewnienie, że pokoje nigdy nie zostaną zwrócone po ich usunięciu, jest ich zablokowanie SELECT FOR UPDATE.

Jednak w niektórych systemach blokowanie jest efektem ubocznym kontroli współbieżności i można osiągnąć te same wyniki bez FOR UPDATEjawnego określania .


Aby rozwiązać ten problem, Wątek 1 powinien SELECT id FROM rooms FOR UPDATEzapobiegać usuwaniu wątku 2 od roomsmomentu zakończenia Wątku 1. Czy to jest poprawne?

Zależy to od kontroli współbieżności używanej przez system bazy danych.

  • MyISAM w MySQL (i kilka innych starych systemów) blokuje całą tabelę na czas trwania zapytania.

  • W SQL Server, SELECTzapytań miejsce wspólne zamki na płytach / pages / Stoły mają badanych, natomiast DMLzamki zapytań miejsce aktualizacji (który później awansować do wyłącznej lub zdegradowany do wspólnych zamków). Wyłączne blokady są niekompatybilne z zamkami współdzielonymi, więc albo SELECTalboDELETE zapytanie zostanie zablokowana do momentu zatwierdzenia innej sesji.

  • W bazach danych, których używanie MVCC(jak Oracle, PostgreSQL, MySQLz InnoDB), A DMLkwerenda tworzy kopię rekordu (w jednym lub w inny sposób) i ogólnie czytelnicy nie blokować pisarzy i vice versa. W przypadku tych baz danych SELECT FOR UPDATEprzydałby się: blokowałby jedno SELECTlub DELETEzapytanie do momentu zatwierdzenia innej sesji, tak jak to się SQL Serverdzieje.

Kiedy należy używać REPEATABLE_READizolacji transakcji, a READ_COMMITTEDkiedy z SELECT ... FOR UPDATE?

Generalnie REPEATABLE READnie zabrania wierszy fantomowych (wierszy, które pojawiły się lub zniknęły w innej transakcji, zamiast być modyfikowane)

  • We Oraclewcześniejszych PostgreSQLwersjach REPEATABLE READjest w rzeczywistości synonimem SERIALIZABLE. Zasadniczo oznacza to, że transakcja nie widzi zmian wprowadzonych po jej rozpoczęciu. Tak więc w tej konfiguracji ostatnie Thread 1zapytanie zwróci pokój tak, jakby nigdy nie został usunięty (co może być tym, czego chciałeś, ale nie musi). Jeśli nie chcesz pokazywać pokoi po ich usunięciu, powinieneś zablokować wiersze za pomocąSELECT FOR UPDATE

  • W InnoDB, REPEATABLE READa SERIALIZABLEto dwie różne rzeczy: czytniki w SERIALIZABLEzestawie trybu next-key zamki ewidencji one ocenić, co skutecznie zapobiega równoczesna DMLna nich. Więc nie potrzebujesz SELECT FOR UPDATEw trybie serializowalnym, ale potrzebujesz ich w REPEATABLE READlub READ COMMITED.

Zauważ, że standard dotyczący trybów izolacji nakazuje, że nie widzisz pewnych dziwactw w zapytaniach, ale nie definiuje jak (z blokowaniem, z MVCClub w inny sposób).

Kiedy mówię „nie potrzebujesz SELECT FOR UPDATE”, naprawdę powinienem był dodać „z powodu skutków ubocznych implementacji niektórych silników bazodanowych”.


1
Ostatni punkt jest sednem sprawy, myślę: „nie potrzebujesz SELECT FOR UPDATE w trybie serializacji, ale potrzebujesz ich w REPEATABLE READ lub READ COMMITED”.
Colin 't Hart

Masz rację. Drugie pytanie powinno było zadać, kiedy SERIALIZABLEpowinno być używane, a kiedy READ_COMMITTEDz SELECT ... FOR UPDATE. Czy możesz zaktualizować swoją odpowiedź, aby odzwierciedlić to zaktualizowane pytanie?
Gili

1
@Gili: "nie potrzebujesz SELECT FOR UPDATEw trybie serializowalnym", z InnoDB. W przypadku innych MVCCsystemów te dwa są synonimami i potrzebujesz SELECT FOR UPDATE.
Quassnoi

1
Uważam, że post Colina lepiej odpowiada na moje konkretne pytania niż twoja, ale doceniam wszystkie podane przez ciebie referencje. Przyjmę odpowiedź, która najlepiej łączy te dwa elementy (szczegółowe odpowiedzi u góry, uzupełniające odniesienia poniżej).
Gili

This depends on the concurrency control your database system is using: Myślę, że rozdwajasz włosy. Wszystkie przypadki, które wymienisz poniżej, mówią, że pokój nie jest usuwany od SELECTkońca transakcji. Czy zatem odpowiedź nie powinna być po prostu za Yespomocą poniższych odnośników pomocniczych?
Gili

33

Krótkie odpowiedzi:

P1: Tak.

P2: Nie ma znaczenia, którego używasz.

Długa odpowiedź:

select ... for updateBędzie (jak to wskazuje) wybrać niektóre wiersze, ale także zablokować je tak, jakby zostały już zaktualizowane przez bieżącej transakcji (lub jeśli aktualizacja tożsamość została wykonana). Dzięki temu możesz ponownie zaktualizować je w bieżącej transakcji, a następnie zatwierdzić, bez możliwości zmiany tych wierszy przez inną transakcję.

Patrząc na to z innego punktu widzenia, wygląda na to, że następujące dwie instrukcje są wykonywane niepodzielnie:

select * from my_table where my_condition;

update my_table set my_column = my_column where my_condition;

Ponieważ wiersze, na które my_conditionma wpływ, są zablokowane, żadna inna transakcja nie może ich w żaden sposób modyfikować, dlatego poziom izolacji transakcji nie ma tutaj znaczenia.

Należy również pamiętać, że poziom izolacji transakcji jest niezależny od blokowania: ustawienie innego poziomu izolacji nie pozwala na obejście blokowania i aktualizowanie wierszy w innej transakcji, które są zablokowane przez transakcję.

To, które poziomy izolacji transakcji gwarantują (na różnych poziomach), to spójność danych podczas trwania transakcji.


1
Myślę, że What transaction isolation levels do guarantee [...] is the consistency of data once transactions are completed.błędnie oznacza, że ​​poziomy izolacji nie wpływają na to, co dzieje się podczas transakcji. Zalecam zrewidowanie tej sekcji i przedstawienie bardziej szczegółowych informacji o tym, jak wpływają one na to, co widzisz (lub czego nie widzisz) podczas transakcji.
Gili

1
Uważam, że Twój post lepiej odpowiada na moje konkretne pytania niż Quassnoi, ale doceniam wszystkie podane przez niego referencje. Przyjmę odpowiedź, która najlepiej łączy te dwa elementy (szczegółowe odpowiedzi u góry, uzupełniające odniesienia poniżej).
Gili

Blokowanie i izolacja są zamiennie skomplikowane. Czy są więc jakieś książki, w których można uzyskać wiedzę na ten temat?
Chao
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.