MySQL: transakcje a tabele blokujące

Jestem trochę zdezorientowany z transakcjami i tabelami blokującymi, aby zapewnić integralność bazy danych i upewnić się, że SELECT i UPDATE pozostają zsynchronizowane i żadne inne połączenie nie koliduje z tym. Potrzebuję:

SELECT * FROM table WHERE (...) LIMIT 1

if (condition passes) {
   // Update row I got from the select 
   UPDATE table SET column = "value" WHERE (...)

   ... other logic (including INSERT some data) ...
}

Muszę się upewnić, że żadne inne zapytania nie będą kolidować i wykonywać tego samego SELECT(odczytywanie „starej wartości” przed zakończeniem aktualizacji wiersza przez to połączenie.

Wiem, że mogę domyślnie po LOCK TABLES tableprostu upewnić się, że robi to tylko 1 połączenie naraz, i odblokować je, gdy skończę, ale wydaje się to przesadą. Czy zawinięcie tego w transakcję zrobiłoby to samo (zapewniając, że żadne inne połączenie nie próbuje tego samego procesu, podczas gdy inne nadal przetwarza)? Albo SELECT ... FOR UPDATElub SELECT ... LOCK IN SHARE MODEbyć lepiej?

Blokowanie tabel zapobiega wpływowi innych użytkowników bazy danych na zablokowane wiersze / tabele. Ale blokady same w sobie NIE zapewnią, że logika pojawi się w spójnym stanie.

Pomyśl o systemie bankowym. Kiedy płacisz rachunek online, transakcja ma wpływ na co najmniej dwa konta: Twoje konto, z którego pobierane są pieniądze. Oraz konto odbiorcy, na które wpłacane są pieniądze. I konto w banku, na które z radością wpłacą wszystkie opłaty serwisowe naliczone przy transakcji. Biorąc pod uwagę (jak wszyscy wiedzą w dzisiejszych czasach), że banki są wyjątkowo głupie, powiedzmy, że ich system działa tak:

$balance = "GET BALANCE FROM your ACCOUNT";
if ($balance < $amount_being_paid) {
    charge_huge_overdraft_fees();
}
$balance = $balance - $amount_being paid;
UPDATE your ACCOUNT SET BALANCE = $balance;

$balance = "GET BALANCE FROM receiver ACCOUNT"
charge_insane_transaction_fee();
$balance = $balance + $amount_being_paid
UPDATE receiver ACCOUNT SET BALANCE = $balance

Teraz, bez blokad i transakcji, system ten jest podatny na różne warunki wyścigu, z których największym jest wielokrotne dokonywanie płatności na Twoim koncie lub równolegle na konto odbiorcy. Podczas gdy twój kod ma odzyskane saldo i wykonuje ogromne_przedaż_opłat () i tak dalej, jest całkowicie możliwe, że jakaś inna płatność będzie uruchamiać równolegle ten sam typ kodu. Odzyskają Twoje saldo (powiedzmy 100 USD), przeprowadzą transakcje (wyjmą 20 USD, które płacisz, i 30 USD, z którymi cię oszukują), a teraz obie ścieżki kodu mają dwa różne salda: 80 USD i 70 $. W zależności od tego, który z nich zakończy się jako ostatni, otrzymasz na swoim koncie jedno z dwóch sald, zamiast 50 USD, które powinieneś otrzymać (100 USD - 20 USD - 30 USD). W tym przypadku „błąd banku na Twoją korzyść”

Powiedzmy, że używasz zamków. Twoja płatność za rachunek (20 USD) trafia jako pierwsza, więc wygrywa i blokuje rekord Twojego konta. Teraz masz wyłączne zastosowanie i możesz odliczyć 20 $ z salda i w spokoju zapisać nowe saldo ... a Twoje konto kończy się 80 $ zgodnie z oczekiwaniami. Ale ... uhoh ... Próbujesz zaktualizować konto odbiorcy, a jest ono zablokowane i zablokowane dłużej niż pozwala na to kod, przekroczenie limitu czasu transakcji ... Mamy do czynienia z głupimi bankami, więc zamiast mieć właściwy błąd obsługa, kod po prostu ciągnie an exit(), a twoje 20 $ znika w pęczek elektronów. Teraz straciłeś 20 $, a nadal jesteś winien 20 $ odbiorcy, a Twój telefon zostaje przejęty.

Więc ... wprowadź transakcje. Rozpoczynasz transakcję, obciążasz konto 20 $, próbujesz zasilić odbiorcę 20 $ ... i znowu coś wybucha. Ale tym razem, zamiast exit(), kod może po prostu zrobić rollback, i poof, twoje 20 $ zostanie magicznie dodane z powrotem do twojego konta.

W końcu sprowadza się to do tego:

Blokady uniemożliwiają innym ingerowanie w jakiekolwiek rekordy bazy danych, z którymi masz do czynienia. Dzięki transakcjom wszelkie „późniejsze” błędy nie kolidują z „wcześniejszymi” czynnościami, które wykonałeś. Żaden z nich sam nie gwarantuje, że wszystko pójdzie dobrze. Ale razem to robią.

w jutrzejszej lekcji: Radość z impasu.

Chcesz SELECT ... FOR UPDATElub SELECT ... LOCK IN SHARE MODEwewnątrz transakcji, jak pan powiedział, ponieważ typuje, bez względu na to, czy są one w transakcji, czy nie, nie będzie zablokować tabeli. To, który wybierzesz, będzie zależało od tego, czy chcesz, aby inne transakcje mogły odczytać ten wiersz w trakcie trwania transakcji.

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

START TRANSACTION WITH CONSISTENT SNAPSHOTnie załatwi sprawy, ponieważ nadal mogą pojawić się inne transakcje i zmodyfikować ten wiersz. Jest to wspomniane u góry linku poniżej.

Jeśli inne sesje jednocześnie aktualizują tę samą tabelę, [...] możesz zobaczyć tabelę w stanie, który nigdy nie istniał w bazie danych.

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

Koncepcje transakcji i blokady są różne. Jednak transakcja wykorzystywała blokady, aby pomóc jej przestrzegać zasad ACID. Jeśli chcesz, aby tabela uniemożliwiła innym czytanie / pisanie w tym samym momencie, gdy ty czytasz / zapisujesz, potrzebujesz do tego blokady. Jeśli chcesz zapewnić integralność i spójność danych, lepiej wykorzystaj transakcje. Myślę, że mieszane koncepcje poziomów izolacji w transakcjach z zamkami. Wyszukaj poziomy izolacji transakcji, SERIALIZE powinien być poziomem, który chcesz.

Miałem podobny problem podczas próby, IF NOT EXISTS ...a następnie wykonywania testu, INSERTktóry spowodował sytuację wyścigu, gdy wiele wątków aktualizowało tę samą tabelę.

Rozwiązanie problemu znalazłem tutaj: Jak pisać zapytania WSTAW, JEŚLI NIE ISTNIEJE w standardowym SQL

Zdaję sobie sprawę, że nie jest to bezpośrednią odpowiedzią na twoje pytanie, ale ta sama zasada sprawdzania i wstawiania jako pojedynczego stwierdzenia jest bardzo przydatna; powinieneś móc go zmodyfikować, aby przeprowadzić aktualizację.

Mylisz się z blokadą i transakcją. W RMDB to dwie różne rzeczy. Blokada zapobiega współbieżnym operacjom, podczas gdy transakcja koncentruje się na izolacji danych. Przeczytaj ten wspaniały artykuł, aby uzyskać wyjaśnienie i wdzięczne rozwiązanie.

Użyłbym

START TRANSACTION WITH CONSISTENT SNAPSHOT;

na początek, a

COMMIT;

na koniec.

Wszystko, co robisz w międzyczasie, jest odizolowane od innych użytkowników twojej bazy danych, jeśli twój silnik pamięci masowej obsługuje transakcje (czyli InnoDB).