MySQL: transakcje a Blokowanie tabel

Question

MySQL: transakcje a Blokowanie tabel

Jestem trochę zmieszany z transakcjami a blokowaniem tabel, aby zapewnić integralność bazy danych i upewnić się, że SELECT I UPDATE pozostają zsynchronizowane i żadne inne połączenie nie zakłóca tego. Muszę:

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ą zakłócać i wykonywać tego samego SELECT (odczytanie 'starej wartości', zanim to połączenie zakończy aktualizację wiersza.

Wiem, że mogę domyślnie LOCK TABLES table, aby upewnić się, że tylko 1 połączenie robi to na raz, i odblokować go, gdy Skończyłem, ale to chyba przesada. Czy zawijanie tego w transakcji robi to samo (zapewnienie, że żadne inne połączenie nie próbuje tego samego procesu, podczas gdy inny jest nadal przetwarzany)? A może SELECT ... FOR UPDATE lub SELECT ... LOCK IN SHARE MODE byłoby lepsze?

89

mysql sql locking transactions

Author: robsch, 2010-11-19

Source

6 answers

Chcesz SELECT ... FOR UPDATE LUB SELECT ... LOCK IN SHARE MODE wewnątrz transakcji, jak powiedziałeś, ponieważ normalnie SELECTs, bez względu na to, czy są w transakcji, czy nie, nie zablokuje tabeli. To, który z nich wybierzesz, zależy od tego, czy inne transakcje mają być w stanie odczytać ten wiersz, gdy transakcja jest w toku.

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

START TRANSACTION WITH CONSISTENT SNAPSHOT nie zrobi tego za Ciebie, ponieważ inne transakcje mogą nadal się pojawiać i modyfikować ten rząd. Jest to wymienione na górze poniższego linku.

Jeśli inne sesje jednocześnie zaktualizuj tę samą tabelę [...] możesz zobacz tabelę w stanie, który nigdy / align = "left" /

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

13

Author: Alison R.,
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
2010-11-19 19:52:57

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

Znalazłem rozwiązanie problemu tutaj: Jak napisać INSERT jeśli nie ma zapytań w standardowym SQL

Zdaję sobie sprawę, że to nie odpowiada bezpośrednio na twoje pytanie, ale ta sama zasada wykonywania sprawdzania i wstawiania jako pojedynczej instrukcji jest bardzo przydatna; powinieneś być w stanie ją zmodyfikować, aby wykonać swoje aktualizacja.

6

Author: Tony,
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
2010-11-19 16:06:58

Pojęcia transakcji i blokady są różne. Jednak transakcja korzystała z zamków, aby pomóc jej przestrzegać zasad ACID. Jeśli chcesz, aby tabela uniemożliwiała innym odczyt/zapis w tym samym punkcie czasowym podczas odczytu/zapisu, potrzebujesz blokady, aby to zrobić. Jeśli chcesz mieć pewność, że dane są integralne i spójne, lepiej korzystaj z transakcji. Myślę, że mieszane pojęcia poziomów izolacji w transakcjach z zamkami. Proszę wyszukać poziomy izolacji transakcji, SERIALIZE powinien być poziom, który chcesz.

3

Author: tczhaodachuan,
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
2015-11-23 19:35:43

Mylisz się z lock & transaction. Są to dwie różne rzeczy w RMDB. Blokada zapobiega jednoczesnym operacjom, podczas gdy transakcja koncentruje się na izolacji danych. Sprawdź Ten świetny artykuł do wyjaśnienia i jakiegoś wdzięcznego rozwiązania.

2

Author: David,
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
2015-08-31 10:27:40

Użyłbym

START TRANSACTION WITH CONSISTENT SNAPSHOT;

Na początek i

COMMIT;

Na koniec.

Wszystko, co robisz pomiędzy, jest odizolowane od innych użytkowników twojej bazy danych , Jeśli twój silnik pamięci obsługuje transakcje (którym jest InnoDB).

1

Author: Martin Schapendonk,
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
2010-11-19 16:06:45

score 138 · Accepted Answer

Blokowanie tabel zapobiega wpływaniu innych użytkowników DB na zablokowane wiersze/tabele. Ale zamki same w sobie nie zapewnią, że twoja logika wyjdzie w spójnym stanie.

Pomyśl o systemie bankowym. Kiedy płacisz rachunek online, transakcja dotyczy co najmniej dwóch kont: Twojego konta, z którego pobierane są pieniądze. I konto odbiorcy, na które pieniądze są przekazywane. I konto bankowe, na które z radością wpłacą wszystkie opłaty serwisowe pobierane od 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 żadnych transakcji, ten system jest podatny na różne warunki rasowe, z których największym jest wielokrotne płatności wykonywane na twoim koncie lub na koncie odbiorcy równolegle. Podczas gdy twój kod ma odzyskane saldo i robi huge_overdraft_fees () i tak dalej, jest to całkowicie możliwe, że inna płatność będzie uruchamiać ten sam typ kodu równolegle. Będą pobierać Twoje saldo (powiedzmy, $100), wykonywać swoje transakcje( wyjmij $ 20, które płacisz, i $30, z którymi Cię wkręcają), a teraz obie ścieżki kodu mają dwa różne salda: $80 i $ 70. W zależności od tego, które z nich zakończą się, skończysz z jednym z tych dwóch sald na koncie, zamiast $50, które powinieneś mieć ($100 - $20 - $30). W tym przypadku " błąd banku w twoja przysługa".

Powiedzmy, że używasz zamków. Twoja płatność rachunku ($20) uderza rurę pierwszy, więc wygrywa i blokuje rekord konta. Teraz masz wyłączne użycie i możesz odliczyć 20 $z salda i zapisać nowe saldo w spokoju... a Twoje konto kończy się z $80, jak się spodziewano. Ale... uhoh... Próbujesz zaktualizować konto odbiorcy, które jest zablokowane i zablokowane dłużej niż pozwala na to kod, odmierzając transakcję... Mamy do czynienia z głupimi bankami, więc zamiast mając odpowiednią obsługę błędów, kod po prostu ciągnie exit(), a twoje $20 znika w pęk elektronów. Teraz masz 20 dolarów, i nadal wisisz 20 dolarów odbiorcy, a Twój telefon zostanie przejęty. Więc... wprowadź transakcje. Rozpoczynasz transakcję, obciążasz konto $20, próbujesz przypisać odbiorcy $ 20... i znowu coś wybuchnie. Ale tym razem, zamiast exit(), kod może po prostu zrobić rollback, i puf, Twoje $20 jest magicznie dodane z powrotem do twojego konto.

W końcu sprowadza się to do tego:

Blokady uniemożliwiają innym ingerowanie w jakiekolwiek rekordy bazy danych, z którymi masz do czynienia. Transakcje zapobiegają" późniejszym "błędom przed ingerencją w "wcześniejsze" rzeczy, które zrobiłeś. Ani sam nie może zagwarantować, że wszystko będzie dobrze w końcu. Ale razem tak.

W jutrzejszej lekcji: radość martwych punktów.