MySQL transakcje

Witajcie,

Prośba o pomoc w zrozumieniu tematu transakcji, do osób, które PERFEKCYJNIE znają temat. Mam napisane kawałki kodu, które działają, ale artykuły, które czytałem nie rozwiały wszystkich moich wątpliwości.

1. Wykonuję zapytania w ramach transakcji (z wyłączonym AUTOCOMMIT, ponieważ cała transakcja to kilka odseparowanych zapytań wykonywanych przez PHP). Transakcja blokuje tabelę dopiero po wykonaniu zapytania, co jeśli dobrze ten mechanizm zrozumiałem nie zabezpiecza mnie w 100%. Wykonuję zapytanie na tabeli x (blokada tabeli x), potem dopiero na tabeli y (dopiero wówczas blokada tabeli y) i czysto teoretycznie między zapytaniami może dojść do katastrofy.

Rozumiem, że w tej sytuacji powinienem stosować LOCK TABLES zanim rozpocznę transakcję. Pytanie brzmi, czy po zakończeniu transakcji powinienem ręcznie odblokować tabele, czy też zakończenie transakcji samo je odblokuje (zakładając, że zapytania w ramach transakcji dotyczą tego samego zestawu tabel, które zablokowałem poprzez LOCK TABLES)?

2. Co się stanie jeśli wyślę zapytanie LOCK TABLES lub rozpocznę transakcję, które zablokuje daną tabelę (pozwalając odczytywać innym dane), a następnie kod wywali błąd i nie dokończy działania (nie zdejmie blokady na tabeli)? Jak to działa na innych użytkowników?

3. W jaki sposób założyć blokadę na konkretny wiersz tabeli i sprawić, by transakcja dotyczyłą tylko jego?

4. Czy da się założyć blokadę np. na grupę wierszy (lock dla wierszy z danym user_id)?

5. Jęśli dwóch userów wykonuje zapytania w transakcjach dotyczące tych samych zestawów danych, rozumiem, że ten drugi zaobserwuje po prostu dłuższy czas oczekiwania na odpowiedź od serwera MySQL (bo jego sesja czeka na zakończenie transakcji innego usera), czy tak?

6. Powiedzmy, że w panelu admina aplikacji mamy moduł, który aktualizuje fakturę za pomocą transakcji. Faktury to dwie tabele (invoices oraz invoicesproducts). Tu wszystko działa jak należy, bo jeśli coś się podzieje, zmiany zostaną wycofane.

Powiedzmy, że w części klienckiej jest moduł z listą faktur użytkownika. Ten moduł wykonuje zapytanie SELECT nie korzystając z transakcji. Czy to zapytanie będzie widziało dane SPRZED zakończenia ewentualnej transakcji po stronie panelu admina, czy też może widzieć dane dodawane do tej tabeli?

W podanym przykładzie aktualizujemy najpierw tabelę invoices potem invoicesproducts. Stąd moje pytanie. Czy user BEZ TRANSAKCJI będzie widział niejako starą wersję faktury do momentu zakończenia transakcji w panelu admina?

7. Pytanie dodatkowe niejako z poza powyższego tematu. Czy jest możliwość by MySQL wywalił błąd jeśli dla zapytania UPDATE nie istnieje żaden rekord?

PS: Wątek ten przez nieuwagę dodałem w dziale PHP. Przperaszam za powtórzenie.

najważniejsze - w mysqlu transakcje obsługują jedynie innodb

1. jak to zapytanie wygląda
2. takie rzeczy robi się tak (pseudokod):

try
{
transaction.begin
//kod SQL
transaction.commit
}
catch
{
transaction.rollback
}

3. zapoznaj się z klauzulą FOR UPDATE, np. SELECT id FROM tabela WHERE id= 123 FOR UPDATE - zablokuje jedynie rekord z id = 123
4. tak, patrz 3
5. to zależy - zapoznaj się z NOWAIT oraz SKIP LOCKED
6. zapytanie ZAWSZE wykonywane jest w transakcji - to, że jawnie jej nie zaczynasz nie znaczy, że jej nie ma. Poczytaj o poziomach izolacji transakcji https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
7. nie, ale zawsze możesz sprawdzić ile rekordów było zmienionych (mysql_affected_rows)

abrakadaber napisał(a):

najważniejsze - w mysqlu transakcje obsługują jedynie innodb

1. jak to zapytanie wygląda
2. takie rzeczy robi się tak (pseudokod):

try
{
transaction.begin
//kod SQL
transaction.commit
}
catch
{
transaction.rollback
}

3. zapoznaj się z klauzulą FOR UPDATE, np. SELECT id FROM tabela WHERE id= 123 FOR UPDATE - zablokuje jedynie rekord z id = 123
4. tak, patrz 3
5. to zależy - zapoznaj się z NOWAIT oraz SKIP LOCKED
6. zapytanie ZAWSZE wykonywane jest w transakcji - to, że jawnie jej nie zaczynasz nie znaczy, że jej nie ma. Poczytaj o poziomach izolacji transakcji https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
7. nie, ale zawsze możesz sprawdzić ile rekordów było zmienionych (mysql_affected_rows)

Ogromnie Ci dziękuję za odpowiedź.

Wiem, że tylko INNODB i z takiej właśnie korzystam na localu oraz serwerze docelowym.

1, Wygląda tak jak to podałeś w odpowiedzi na punkt 2. czyli z wykorzystaniem klauzuli TRY.

Natomiast punkt 2 dotyczył nieco innej sytuacji. O ile SELECT ... FOR UPDATE zakłada blokadę na określone rekordy, czasem istnieje potrzeba aktualizacji BARDZO wielu rekordów z jednoczesnym wykorzystaniem transakcji.

Przykładowo masz w tabeli kolumnę z numerem porządkowym. User wyedytował rekord zmienił numer porządkowy w związku z czym trzeba zaktualizować numerację większości pozostałych rekordów. Pytanie, czy wykonanie SELECT id FROM tabela WHERE id > 0 FOR UPDATE by zablokować wszystkie wiersze będzie ok czy będzie za mało wydajne?

4. DO tego mam jeszcze jedno pytanie. INNODB domyślnie jest w trybie REPEATED READS. On ma tę specyficzną właściwość, że jeśli rozpocznę transakcję 1 i wewnątrz będą selecty z tabeli X, to nawet jeśli inna transakcja (z innej sekcji) uzyska status zatwierdzonej czyli wprowadzi aktualizację do rekordów, wewnątrz transakcji 1 select nie będzie tych zmian widział.

Pytanie czy jeśli w transakcji 2 wykonam zapytanie SELECT ... FOR UPDATE dla określonego rekordu, w tym czasie ina transakcja się rozpocznie, po czym w tej pierwszej będzie COMMIT czyli zakończenie to czy wówczas 1 transakcja już będzie widziała w swoim selecie rekord po zmianach? Tzn. czy SELECT * FOR UPDATE zawsze wykonuje się dla realnych danych i w najgorszym wypadku oczekuje na zakończenie pozostałych transakcji?

Ostatni akapit zamotałem. Miało być tak:

Pytanie czy jeśli w transakcji 1 wykonam zapytanie SELECT ... FOR UPDATE dla określonego rekordu, w tym czasie inna transakcja się rozpocznie, po czym w tej pierwszej będzie zakończenie to czy wówczas ta druga transakcja już będzie widziała w swoim selecie rekord po zmianach? Tzn. czy SELECT * FOR UPDATE zawsze wykonuje się dla realnych danych i w najgorszym wypadku oczekuje na zakończenie pozostałych transakcji?

Czyli czy wygląda to tak:

1. Rozpoczyna się transakcja 1
2. Transakcja 1 wykonuje select for update
3. rozpoczyna się transakcja 2
4. Transakcja 2 wykonuje select for update (który jest zawieszony bo transakcja 1 trwa)
5. Transakcja 1 wykonuje update
6. Transakcja 1 zostaje pozytywnie zakończona
7. Transakcja 2 wznawia działanie wykonując polecenie z punktu 4 i pobiera aktualną wersję rekordu już po aktualizacji przez transakcję 1.
   ... itd.

Dobrze rozumiem?

Powyższe dotyczy trybu REPEATED READS! Bo to ważne.

webkam napisał(a):

Natomiast punkt 2 dotyczył nieco innej sytuacji. O ile SELECT ... FOR UPDATE zakłada blokadę na określone rekordy, czasem istnieje potrzeba aktualizacji BARDZO wielu rekordów z jednoczesnym wykorzystaniem transakcji.

Przykładowo masz w tabeli kolumnę z numerem porządkowym. User wyedytował rekord zmienił numer porządkowy w związku z czym trzeba zaktualizować numerację większości pozostałych rekordów. Pytanie, czy wykonanie SELECT id FROM tabela WHERE id > 0 FOR UPDATE by zablokować wszystkie wiersze będzie ok czy będzie za mało wydajne?

Nie ma znaczenia czy blokujesz jedne rekord czy jeden milion rekordów (w tym sensie, że update niezablokowanych a update zablokowanych będzie znacząco różnił się czasem wykonania). Tutaj problem jest inny - przy zmianie dużej ilości rekordów w jednej transakcji mocno rośnie obciążenie bazy bo dopóki nie zrobisz commit lub rollback zmiany muszą być trzymane "obok". Dlatego zaleca się robić zmiany małymi paczkami - np. po 100 - 1000 rekordów.

4. DO tego mam jeszcze jedno pytanie. INNODB domyślnie jest w trybie REPEATED READS. On ma tę specyficzną właściwość, że jeśli rozpocznę transakcję 1 i wewnątrz będą selecty z tabeli X, to nawet jeśli inna transakcja (z innej sekcji) uzyska status zatwierdzonej czyli wprowadzi aktualizację do rekordów, wewnątrz transakcji 1 select nie będzie tych zmian widział.

Pytanie czy jeśli w transakcji 2 wykonam zapytanie SELECT ... FOR UPDATE dla określonego rekordu, w tym czasie ina transakcja się rozpocznie, po czym w tej pierwszej będzie COMMIT czyli zakończenie to czy wówczas 1 transakcja już będzie widziała w swoim selecie rekord po zmianach? Tzn. czy SELECT * FOR UPDATE zawsze wykonuje się dla realnych danych i w najgorszym wypadku oczekuje na zakończenie pozostałych transakcji?

https://stackoverflow.com/questions/46230555/why-i-shouldnt-use-repeatable-read-with-locking-reading-select-for-update

jeśli blokujesz dużą liczbę rekordów i dopuszczasz sytuację, że ktoś inny może próbować zrobić to samo to wg mnie powinieneś wykrywać takie sytuacje i zamiast czekać informować usera, że inna operacja trwa i żeby spróbował za chwilę. Wyobraź sobie, że masz trwającą transakcję, potem przychodzi druga i czeka, trzecia, czwarta - każda musi czekać dłużej a userzy zaczynają się niecierpliwić i zamykać karty/ubijać program. To prosta droga do katastrofy. Zauważ, że zamknięcie karty/ubicie aplikacji nie kończy sesji po stronie bazy dopóki jej timeout nie minie

W jakim narzędziu można testować multisesje do bazy? Próbowałem to robić przez PHPMyAdmina, ale odnoszę wrażenie, PHPMyAdmin obligatoryjnie kończy transakcje po wykonaniu zapytania, bo nie udało mi się wytrigerować błędu.

Zwyczajny klient linii poleceń nie wystarczy?

A w jaki sposób w linii poleceń symulować dwa równoległe połączenia?

webkam napisał(a):

A w jaki sposób w linii poleceń symulować dwa równoległe połączenia?

Otwierasz sobie dwa shelle/okienka linii poleceń i w każdym uruchamiasz klienta linii poleceń do bazy.

1. Start->Uruchom->cmd.exe
2. mysql -u user -phaslo -h host baza_danych

-- edited:
Tu masz nawet opisane scenariusze pozwalające zrozumieć poziom REPEATABLE READS w mysqlu.
https://blog.pythian.com/understanding-mysql-isolation-levels-repeatable-read/

Uruchomiłem dwie instancje obok siebie linii komend co teoretycznie sugeruje dwie sesje i kompletnie tego nie rozumiem

SET autocommit = 0;
START TRANSACTION;
SELECT id FROM testowa WHERE id = 1 FOR UPDATE;
UPDATE testowa SET nazwa = 'test2' WHERE id = 1;
SELECT * FROM testowa;

To jest w pierwszym oknie. Ta transakcja nie została zakończona w związku z czym ona w wyniku ostatniego zapytania zobaczy już zrobioną przez siebie zmianę, ale inna nie.

Mimo to zmiana od raz jest widoczna np. w PHPMyAdminie. Coś robię nie tak?

Chcę dotrzeć do momentu w którym będę widział EFEKT działania tego mechanizmu i z resztą sobie już poradzę.

A co pokazuje phpmyadmin dla show variables like '%isolation%'; ?

tx_isolation REPEATABLE-READ

Co ciekawe wykonanie w PHP MY ADMIN

SET autocommit = 0;
BEGIN;
SELECT id FROM testowa WHERE id = 1 LIMIT 1 FOR UPDATE;
UPDATE testowa SET nazwa = 'a' WHERE id = 1;
ROLLBACK;
SELECT * FROM testowa;

NIE POWODUJE cofniecia zmian. Ostatnie zapytanie już za rollback pokazuje efekt taki jak przy comit

U mnie działa, możliwe, ze phpadmin jest ułomny :-)

1. select for udpate w sesji 1 blokuje wiersz dla zapisu.
2. update w sesji 2 wisi i czeka na zwolnienie locka (leci timeout)

Pogrzebię u siebie w konfiugracji serwera.

To możesz mi tylko odpowiedzieć wprost na pytanie zadane wyżej w poście?

Czyli czy wygląda to tak:

Rozpoczyna się transakcja 1
Transakcja 1 wykonuje select for update
rozpoczyna się transakcja 2
Transakcja 2 wykonuje select for update (który jest zawieszony bo transakcja 1 trwa)
Transakcja 1 wykonuje update
Transakcja 1 zostaje pozytywnie zakończona
Transakcja 2 wznawia działanie wykonując polecenie z punktu 4 i pobiera aktualną wersję rekordu już po aktualizacji przez transakcję 1.
... itd.
Dobrze rozumiem?

W powyższym przykładzie transakcja 2 startuje PO wystartowaniu transakcji 1.

Jeśli transakcja 1 zotanie zakończona przez commit, transakcja 2 pobierze rekord i również zablokuje go do odczytu, ale bezie widziała go w AKTUALNEJ wersji PO zaktualizowaniu przez transakcję 1 tak?

I drugie pytanie. Jeśli aktualizuję bardzo dużą ilość rekordów (czasem są takie konieczności) ale mam pewność, że zapytanie wykona się błyskawicznie, powinienem skorzystać z transakcji i zrobić select dla wszystkich rekorów, czy też powinienem skorzystać z LOCK TABLE?

Wiem co jest :) głupi nie zwróciłem uwagi że podczas tworzenia bazy domyślnie wybrany był silnik MyISAM :)

I sam już sobie odpowiedziałem na postawione pytanie :) Jest dokładnie tak jak to zrozumiałem. Tzn. transakcja 2 po wznowieniu działania ma dostęp do najnowszej wersji rekordu nawet jeśli transakcja rozpoczęła się po rozpoczęciu transakcji 1.

W końcu to ogarniam :) de facto FOR UPDATE staje się swego rodzaju punktem dostępowym kolejkującym dostęp do danego rekordu.

OGROMNIE DZIĘKUJĘ WAM ZA POMOC i poświęcony czas :)