Transakcje w MySQL

Lekcja: Transakcje w MySQL

Cel lekcji:

  • Zrozumienie, czym są transakcje w MySQL i jakie mają znaczenie w zarządzaniu danymi.
  • Poznanie właściwości transakcji (ACID).
  • Nauka używania poleceń do zarządzania transakcjami w MySQL.

Teoria:

1. Czym są transakcje?

Transakcja w MySQL to sekwencja jednego lub więcej poleceń SQL, które są traktowane jako pojedyncza jednostka pracy. Kluczowym celem transakcji jest zapewnienie integralności danych, co oznacza, że jeśli transakcja nie powiedzie się, żadne zmiany nie są wprowadzane do bazy danych. To podejście jest szczególnie istotne w aplikacjach, które wymagają wysokiej niezawodności, takich jak systemy bankowe, e-commerce czy systemy zarządzania zasobami.

2. Właściwości ACID

Transakcje w MySQL muszą spełniać cztery podstawowe właściwości, znane jako ACID:

  • Atomicity (atomowość): Transakcja jest traktowana jako jedna całość. Oznacza to, że wszystkie operacje wchodzące w skład transakcji muszą być wykonane, aby zmiany zostały zaakceptowane. W przypadku błędu w którejkolwiek z operacji, cała transakcja jest cofana.

  • Consistency (spójność): Po zakończeniu transakcji baza danych powinna znajdować się w spójnym stanie. To oznacza, że wszystkie reguły, takie jak klucze główne, klucze obce i inne ograniczenia, muszą być spełnione.

  • Isolation (izolacja): Transakcje są od siebie niezależne. Efekty transakcji nie są widoczne dla innych transakcji, dopóki nie zostaną zatwierdzone. To chroni dane przed nieoczekiwanymi zmianami, gdy kilka transakcji działa jednocześnie.

  • Durability (trwałość): Po zakończeniu transakcji (commit), wszystkie zmiany są trwałe, nawet w przypadku awarii systemu. Oznacza to, że raz zapisane dane nie znikną, niezależnie od sytuacji.

3. Jak działają transakcje w MySQL?

Aby korzystać z transakcji w MySQL, tabela musi używać silnika InnoDB lub innego, który obsługuje transakcje. Poniżej przedstawione są podstawowe polecenia do zarządzania transakcjami:

  • START TRANSACTION: Rozpoczyna nową transakcję.
  • COMMIT: Zatwierdza transakcję, zapisując wszystkie zmiany w bazie danych.
  • ROLLBACK: Co fa zmiany wprowadzone w ramach transakcji, cofa wszystkie operacje od momentu rozpoczęcia transakcji.

Przykłady użycia transakcji

Przykład 1: Przeniesienie pieniędzy między kontami

START TRANSACTION;

UPDATE konto SET saldo = saldo - 100 WHERE id = 1;  -- Zmniejszenie salda konta 1
UPDATE konto SET saldo = saldo + 100 WHERE id = 2;  -- Zwiększenie salda konta 2

COMMIT;  -- Zatwierdzenie transakcji

W powyższym przykładzie, jeśli operacja na koncie 1 nie powiedzie się (np. saldo jest za małe), użyjemy ROLLBACK, aby cofnąć wszystkie zmiany.

Ćwiczenia praktyczne w phpMyAdmin

Ćwiczenie 1: Tworzenie tabeli dla transakcji

Cel: Utworzyć przykładową tabelę, aby zobaczyć, jak działają transakcje.

Kroki:

  1. Zaloguj się do phpMyAdmin.
  2. Utwórz bazę danych bank.
  3. Utwórz tabelę konto z następującą strukturą: 
    CREATE TABLE konto (
    id INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50),
    nazwisko VARCHAR(50),
    saldo DECIMAL(10, 2)
);
  4. Dodaj kilka przykładowych rekordów do tabeli: 
    INSERT INTO konto (imie, nazwisko, saldo) VALUES ('Jan', 'Kowalski', 1000.00);
INSERT INTO konto (imie, nazwisko, saldo) VALUES ('Anna', 'Nowak', 500.00);

Ćwiczenie 2: Rozpoczęcie transakcji i ich zarządzanie

Cel: Rozpocząć transakcję, dokonać aktualizacji i nauczyć się używać rollback i commit.

Kroki:

  1. W phpMyAdmin, przejdź do zakładki SQL i użyj poniższego kodu:

    START TRANSACTION;

UPDATE konto SET saldo = saldo - 200 WHERE id = 1;  -- Zmniejszenie salda Jan Kowalski
UPDATE konto SET saldo = saldo + 200 WHERE id = 2;  -- Zwiększenie salda Anna Nowak

-- Sprawdź wyniki zapytań, a następnie zdecyduj, czy zatwierdzić zmiany
COMMIT;  -- Zatwierdzenie transakcji

  2. Zobacz wyniki w tabeli konto. Wprowadź ROLLBACK zamiast COMMIT, aby zobaczyć, jak działa cofnęcie zmian.

Ćwiczenie 3: Użycie ROLLBACK

Cel: Zobaczyć, jak działa rollback w transakcji.

Kroki:

  1. Rozpocznij transakcję i wprowadź zmiany w saldo:

    START TRANSACTION;

UPDATE konto SET saldo = saldo - 300 WHERE id = 1;  -- Jan Kowalski
-- Wprowadź błąd celowo:
UPDATE konto SET saldo = saldo + 300 WHERE id = 999;  -- Błędne ID konta

ROLLBACK;  -- Cofnięcie zmian

  2. Sprawdź, czy saldo na koncie Jana Kowalskiego pozostało takie samo, co przed transakcją.

Ćwiczenie 4: Symulacja transferu pieniędzy

Cel: Zrozumieć praktyczne zastosowanie transakcji.

Kroki:

  1. Rozpocznij transakcję i zrealizuj transfer pieniędzy między dwoma kontami:

    START TRANSACTION;

-- Przykładowy transfer 150 z konta 1 do konta 2
UPDATE konto SET saldo = saldo - 150 WHERE id = 1;  -- Konto 1
UPDATE konto SET saldo = saldo + 150 WHERE id = 2;  -- Konto 2

-- Sprawdź saldo przed zatwierdzeniem
SELECT * FROM konto;

-- Jeśli wszystko wygląda dobrze, zatwierdź zmiany
COMMIT;  -- Zatwierdzenie transakcji

  2. Wprowadź celowo błąd (np. odejmowanie większej kwoty niż dostępne saldo) i użyj ROLLBACK, aby zobaczyć, jak działają transakcje w praktyce.

4. Poziomy izolacji transakcji w MySQL

W MySQL istnieją różne poziomy izolacji transakcji, które określają, jak transakcje mogą współdziałać ze sobą oraz jakie efekty mogą być widoczne dla innych transakcji. Poziomy izolacji wpływają na integralność danych oraz na wydajność systemu. W MySQL można ustawić cztery główne poziomy izolacji:

a. Read Uncommitted (Odczyt niezatwierdzony)

  • Opis: Najniższy poziom izolacji, który pozwala transakcjom odczytywać dane, które zostały zmienione, ale jeszcze nie zatwierdzone przez inne transakcje.
  • Zalety: Najwyższa wydajność, ponieważ nie ma konieczności czekania na zatwierdzenie innych transakcji.
  • Wady: Może prowadzić do tzw. "brudnych odczytów" (dirty reads), gdzie transakcja odczytuje dane, które mogą zostać zmienione lub cofnięte w innej transakcji.

b. Read Committed (Odczyt zatwierdzony)

  • Opis: Transakcje mogą odczytywać tylko dane, które zostały zatwierdzone. To eliminuje problem "brudnych odczytów".
  • Zalety: Umożliwia współbieżny dostęp do danych i zapewnia większą spójność w porównaniu do poziomu "Read Uncommitted".
  • Wady: Nadal może wystąpić problem "niespójnych odczytów" (non-repeatable reads), gdzie wartości mogą się zmieniać, gdy ta sama transakcja jest wykonywana wielokrotnie.

c. Repeatable Read (Odczyt powtarzalny)

  • Opis: Zapewnia, że jeśli transakcja odczyta daną wartość, to przy kolejnych odczytach w tej samej transakcji wartość ta pozostanie niezmieniona, nawet jeśli inne transakcje ją zmienią.
  • Zalety: Eliminacja "niespójnych odczytów" i zapewnienie większej stabilności danych.
  • Wady: Może prowadzić do tzw. "problemów z fantomami" (phantom reads), gdzie nowo dodane wiersze przez inną transakcję mogą być widoczne w kolejnych odczytach.

d. Serializable (Serializowalny)

  • Opis: Najwyższy poziom izolacji, który sprawia, że transakcje są wykonywane sekwencyjnie, co oznacza, że nie mogą się nakładać. Gdy jedna transakcja jest wykonywana, inne muszą czekać na jej zakończenie.
  • Zalety: Największa ochrona przed problemami z odczytem i zapewnienie pełnej spójności danych.
  • Wady: Najniższa wydajność, ponieważ wiele transakcji może być zablokowanych, co prowadzi do zwiększonego czasu oczekiwania i obniżonej wydajności.

Wybór poziomu izolacji

Wybór odpowiedniego poziomu izolacji zależy od wymagań aplikacji dotyczących integralności danych oraz wydajności. W kontekście aplikacji krytycznych dla danych, takich jak systemy bankowe, zwykle preferuje się wyższe poziomy izolacji (np. Serializable lub Repeatable Read), aby zapewnić maksymalną spójność danych. W mniej krytycznych systemach, gdzie wydajność jest kluczowa, można zastosować niższe poziomy (np. Read Committed lub Read Uncommitted).

Przy odpowiedniej konfiguracji i zrozumieniu poziomów izolacji, można skutecznie zarządzać transakcjami w MySQL, zapewniając równocześnie wydajność i integralność danych.