Transakcja w bazach danych

Krystian Brożek

wrz 23, 2019

23 września 2019

Jednym z najważniejszych mechanizmów używanych w relacyjnych bazach danych są transakcje. Właściwie wszystko, co robimy w bazie danych działa w oparciu o transakcje. Służą one do utrzymania integralności i pozwalają na wykonanie serii poleceń w całości.

Jak działają transakcje?

,,,Transakcje w bazach danych zawsze porównuję do pudełka. Pakujesz do niego kilka przedmiotów i do kogoś wysyłasz. Gdyby paczka przyszła uszkodzona, wtedy odbiorca jej nie odbiera, a całość wraca do Ciebie z powrotem. Dokładnie w ten sam sposób działają transakcje w bazach danych. Pakujesz do pudełka kilka zapytań i jeśli choć jedno wykona się niepoprawnie, wtedy silnik bazy danych wycofuje dokonane zmiany i zwraca Ci informację z błędem.

Skoro jesteśmy przy wysyłaniu, to zobaczmy, jak mogłoby to w nim działać. Załóżmy, że ktoś w naszym sklepie internetowym dodał do koszyka produkt, wypełnił dane do wysyłki i klika przycisk „Kup”. Nasz system wysyła zatem do bazy kilka zapytań:

  1. Sprawdzamy, czy osoba o podanych danych istnieje. Jeśli nie, wtedy dodajemy wpis do tabeli klientów.
  2. Pobieramy identyfikator klienta.
  3. Dodajemy nowy wiersz z zamówieniem.
  4. Pobieramy numer zamówienia.
  5. Następnie dla każdego produktu sprawdzamy dostępną ilość w magazynie i gdy mamy odpowiedni zapas, przypisujemy go do zamówienia.
  6. Przypisujemy zamówienie do pracownika, który ma je spakować i wysłać.

Wyobraź sobie teraz, że wystąpił błąd w trakcie tego procesu. Oto kilka przykładów, co mogłoby pójść nie tak:

  1. Problem z danymi – w punkcie 5. brakuje produktu (klient zamówił 3 takie same produkty, a mamy w magazynie tylko 1).
  2. Problem z dodaniem wiersza do tabeli – klient podał dłuższy opis do zamówienia, niż możemy dodać do tabeli (pamiętaj, że wiele baz np. MSSQL i Oracle mają z góry określoną maksymalną ilość znaków w komórce).
  3. Problem techniczny – tabela została zablokowana (np. proces archiwizujący odczytuje dane) lub zabrakło miejsca na dysku (wielokrotnie spotkałem się z tym problemem, tworząc różne systemy, czasem na tym samym dysku znajdziesz bazę danych, aplikację i backupy, a te ostatnie potrafią szybko przybierać na wadze).
  4. Problem z uprawnieniami – brak uprawnień do tabeli (wydaje się mało prawdopodobne, ale przy instalacji systemu na dodatkowym serwerze mogliśmy po prostu tego nie zrobić, przy jakiejś zmianie mogły zostać przypadkowo usunięte lub, tworząc coś nowego odczytującego dane np. API dla zewnętrznych aplikacji, nie nadaliśmy ich).

Przypatrzmy się opcji, gdy nie możemy sprawdzić stanu magazynowego produktu, bo usunęliśmy go zanim klient zdążył złożyć zamówienie (dodał do koszyka produkty i po kilku tygodniach kliknął kup). Co powinno się stać w takiej sytuacji? Pakując wszystkie 6 punktów w jedną transakcję, gdy wywali się na punkcie 5., to całość zostanie wycofana. To oznacza, że zamówienie nie pojawi się w bazie danych, a strona naszego sklepu zwróci błąd – oczywiście ładnie zaprezentowany użytkownikowi.

Najprościej określić transakcję jako zestaw zapytań, gdzie wykonuje się wszystko albo nic.

Zarządzanie transakcją

Możemy oczywiście transakcją zarządzać. Niesie to ze sobą wiele możliwości. Pozwala to nam samym stwierdzić, czy po wystąpieniu określonego błędu to, co się wykonało ma zostać zapisane, czy wycofane.

Da się również samemu stwierdzić, kiedy transakcja ma zostać wycofana. Zdarzają się sytuacje, kiedy błąd sam z siebie nie poleci, więc musimy sami napisać kod, który w określonych warunkach wycofa transakcję. Często korzystam z tej możliwości w pracy. Wiele tworzonych przeze mnie skryptów robi to, co użytkownik musiałby wykliwać przez długi czas, więc pracuję na danych znajdujących się już w bazie. To powoduje wiele sytuacji, w których coś może pójść nie tak. Znając dokładny wynik skryptu, mogę zabezpieczyć się przed niepożądanym jego działaniem, wycofując transakcję, gdy jakiekolwiek dane się nie zgadzają.

Z transakcji w bazach danych korzystam również, testując, czy coś usunie się poprawnie. Tworzę zapytanie usuwające wiersze, odpalam je, ale kończę całą operację, wycofując transakcję. Dzięki temu chcąc usunąć 2 wiersze, sprawdzam, czy się usuną (czy nie wystąpi błąd) i czy na pewno usuną się tylko dwa (może być więcej wierszy spełniających warunek).

W bazie MSSQL wygląda to następująco:

BEGIN TRANSACTION
  DELETE FROM Pracownicy 
  WHERE Imie = ‘Anna’ AND Nazwisko = ‘Kowalska’
ROLLBACK TRANSACTION

To spowoduje usunięcie Anny Kowalskiej, a następnie wycofanie całej operacji – czyli pani Ania wciąż będzie w bazie danych.

Zatwierdzenie transakcji odbywa się za pomocą COMMIT TRANSACTION.

BEGIN TRANSACTION
  DECLARE @IloscWierszy int = 0 --deklaracja zmiennej
  INSERT INTO PracownicyArchiwum (Imie, Nazwisko, PESEL) --dodanie wierszy na podstawie wyniku SELECTa
  SELECT Imie, Nazwisko, PESEL FROM Pracownicy WHERE Imie = 'Anna' AND Nazwisko = 'Kowalska'
  SELECT @IloscWierszy = @IloscWierszy + @@ROWCOUNT --zwiększenie wartości zmiennej
  IF @IloscWierszy = 1
  BEGIN
    PRINT 'Dodano poprawną ilość wierszy'
    COMMIT TRANSACTION --zatwierdzenie transakcji
  END
  ELSE
  BEGIN
    PRINT 'Wystąpił błąd. Próbowano dodać: ' + CAST(@IloscWierszy as varchar(10)) + ' wierszy'
    PRINT 'Zmiany zostały wycofane'
  ROLLBACK TRANSACTION --wycofanie transakcji
END

Przykład dotyczy MSSQLa. W przypadku bazy Oracle mamy SET TRANSACTION jako rozpoczęcie transakcji, a w MySQL START TRANSACTION. Mechanizm pozostaje jednak ten sam.

Zmienna @@ROWCOUNT przechowuje informację o ilości wierszy, na których operowała ostatnia instrukcja. Skoro robiliśmy INSERT jednego wiersza, wtedy w tej zmiennej otrzymamy wartość 1. Gdybyśmy zrobili SELECT zwracający 5 wierszy, wtedy w zmiennej @@ROWCOUNT znaleźlibyśmy wartość 5.

Przeczytaj więcej

Czy w IT jest miejsce dla kobiet?

Czy w IT jest miejsce dla kobiet?

Wyobraź sobie typowego pracownika IT. Kogo widzisz przed oczami? Tak naprawdę ten typowy obrazek nie ucieszy przedstawicielek płci żeńskiej, ani przedstawicieli płci męskiej. Dlaczego kobiety nie mogą od razu się uśmiechnąć? Dlatego że mało komu z branżą IT kojarzą...

Jakie są oferty pracy i trendy na rynku?

Jakie są oferty pracy i trendy na rynku?

Chcesz znaleźć zatrudnienie w branży IT? Poszukujesz swojego miejsca na rynku? A może zastanawiasz się nad obecnymi trendami? Nie wiem czy wiesz o tym, że obecnie w branży IT jest naprawdę wiele ofert pracy, jednak są one skierowane przede wszystkim dla specjalistów...

Najważniejsze zalety i wady pracy w IT

Najważniejsze zalety i wady pracy w IT

Praca w branży IT jest uznawana za niezwykle perspektywiczną, przyszłościową i coraz częściej wybieraną przez ludzi w różnym wieku. Zastanawiasz się nad rozpoczęciem kursu programowania? A może właśnie zaczynasz pracę w branży IT i chcesz poznać jej wady oraz zalety?...

2 komentarzy

2 komentarze

  1. Andrzej

    „Najprościej określić transakcję jako zestaw zapytań, gdzie wykonuje się wszystko albo nic.”
    Pierwszy raz o tym słyszę, dlatego co teraz napiszę pewnie Cię rozbawi, ale zapytam : czy ten zestaw pytań może wykluczyć wszystko albo nic, a poprzez pytania, warunki i jeszcze coś tam, może doprowadzić do odpowiedzi jaki jest rzeczywisty stan magazynu i zaproponować klientowi mniejszą ilość sztuk.

    Odpowiedz
    • Krystian Brożek

      Tak, można zrobić w taki sposób, aby po sprawdzeniu stanu, użytkownik był informowany o mniejszej ilości. Jednak nie spotkałem się z realizacją takiej funkcjonalności po stronie bazy, tylko po stronie aplikacji/strony

      Odpowiedz

Wyślij komentarz

Twój adres e-mail nie zostanie opublikowany.

Dołącz do newslettera już dziś!
Zero spamu - tylko wartościowe treści!
Musisz już lecieć?
Zostaw swój adres e-mail i dołącz do BEZPŁATNYCH WEBINARÓW dotyczących SQLa!
  • „Jak uczyć się SQLa?” – 4 października
  • „SQL dla testerów” – 12 października