Kiedy używać SELECT… FOR UPDATE?

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.

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”.

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.