MySQL transakcje

Odpowiedz Nowy wątek
2019-01-06 21:34
webkam
0

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)?

  1. 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?

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

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

  4. 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?

  5. 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?

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

Pozostało 580 znaków

2019-01-06 23:23
1

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
}
  1. zapoznaj się z klauzulą FOR UPDATE, np. SELECT id FROM tabela WHERE id= 123 FOR UPDATE - zablokuje jedynie rekord z id = 123
  2. tak, patrz 3
  3. to zależy - zapoznaj się z NOWAIT oraz SKIP LOCKED
  4. 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/ref[...]saction-isolation-levels.html
  5. nie, ale zawsze możesz sprawdzić ile rekordów było zmienionych (mysql_affected_rows)

Chcesz pomocy - pokaż kod - abrakadabra źle działa z techniką.
edytowany 1x, ostatnio: abrakadaber, 2019-01-06 23:24

Pozostało 580 znaków

2019-01-07 00:08
webkam
0
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
}
  1. zapoznaj się z klauzulą FOR UPDATE, np. SELECT id FROM tabela WHERE id= 123 FOR UPDATE - zablokuje jedynie rekord z id = 123
  2. tak, patrz 3
  3. to zależy - zapoznaj się z NOWAIT oraz SKIP LOCKED
  4. 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/ref[...]saction-isolation-levels.html
  5. 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?

  1. 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?

Pozostało 580 znaków

2019-01-07 00:28
webkam
0

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?

Pozostało 580 znaków

2019-01-07 00:29
webkam
0

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

Pozostało 580 znaków

2019-01-07 13:04
0
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.

  1. 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/que[...]ing-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


Chcesz pomocy - pokaż kod - abrakadabra źle działa z techniką.

Pozostało 580 znaków

2019-01-07 13:31
0

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.

Pozostało 580 znaków

2019-01-07 13:34
0

Zwyczajny klient linii poleceń nie wystarczy?

Pozostało 580 znaków

2019-01-07 13:36
0

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

Pozostało 580 znaków

2019-01-07 13:42
0
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/unde[...]ation-levels-repeatable-read/

edytowany 1x, ostatnio: yarel, 2019-01-07 13:44

Pozostało 580 znaków

2019-01-07 13:49
0

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

Pozostało 580 znaków

Odpowiedz
Liczba odpowiedzi na stronę

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0