Jak zrealizować transakcje na przykładzie pomniejszenia jednego rekordu i zwiększenia drugiego aby zasymulować np. przelew pieniędzy?

Wykorzystując transakcje chcesz "przelać" pieniądze z jednego konta na drugi, czyli w jednym rekordzie zmniejszyć pole o podaną wartość, a w drugim zwiększyć.

Prowadząc sklep Internetowy czy przeprowadzając różne operacje na tabelach, często będziesz korzystać z mechanizmu transakcji. To bardzo ważny element wykonywania poleceń SQL, ponieważ umożliwia zrealizowanie wszystkich instrukcji z podanego bloku poleceń albo żadnej z nich.

Dobrym przykładem jest przelew pieniędzy z jednego konta na drugie. Nie można tego dokonać w tradycyjny sposób zmniejszając kwotę jednemu użytkownikowi i zwiększając drugiemu, gdy polecenia SQL wykonywane są natychmiast.

Gdyby się okazało, że drugi użytkownik nie istnieje w systemie (jego ID podaliśmy błędnie), wtedy pierwsza osoba nie miałaby na koncie jakiejś kwoty, a druga nie otrzymałaby jej. Musimy być pewni, że pierwszej osobie udało się poprawnie odjąć pieniądze, a drugiej poprawnie dodać. Dopiero wtedy transakcja ma dojść do skutku. Jeżeli gdzieś wystąpił błąd, operacje trzeba cofnąć do stanu z przed pierwszej operacji.

Prawda, że bardzo użyteczne? Nie musisz się martwić, że podczas wykonywania wielu poleceń SQL stanie się coś złego, bo zawsze można wrócić do stanu początkowego. To coś w rodzaju polecenia "undo" w programach komputerowych. Zabezpiecza również przed fizycznym przerwaniem operacji, np. gdy nagle zabraknie prądu lub wystąpi awaria dysku.

Zobacz jak posługiwać się transakcjami, które są podstawą bezpiecznych operacji w wielu bazach danych, nie tylko MySQL.

Kiedy można używać transakcji?

Standardowo, gdy założysz tabelę z danymi, przyjmuje ona domyślny typ MyISAM. Tabele tego typu nie obsługują transakcji, więc musisz albo zmienić ich typ albo zakładać od razu tabele obsługujące transakcje ustawiając inny typ tabeli podczas jej tworzenia.

Transakcje obsługiwane są przez dwa typy tabel: BDB i InnoDB. Aby można było zakładać tabele typu BDB i InnoDB trzeba mieć zainstalowaną bazę danych w wersji MAX.

Na stronie www.mysql.com znajdziesz MySQLa zawsze w dwóch wersjach - Standard i Max. Standardowa nie obsługuje transakcji, ale max radzi sobie z nimi wyśmienicie. Pobierz i zainstaluj wersję max.

W wersji dla Windows baza MySQL dostępna jest w wersji Standard i Max w jednym pliku do pobrania. Gdy masz już bazę zainstalowaną uruchom plik mysqld-max.exe aby MySQL działał w wersji max.

Jak korzystać z transakcji?

Stwórzmy prostą tabelę rachunki typu BDB. Tabelę tworzy się standardowo, jedynie na końcu polecenia SQL ustala się jej inny typ, w naszym wypadku type=BDB. W takiej tabeli będzie można realizować transakcje.

CREATE TABLE rachunki (

id INT NOT NULL PRIMARY KEY,

imie CHAR(100) NOT NULL,

nazwisko CHAR(100) NOT NULL,

kwota FLOAT(7,2) NOT NULL DEFAULT "0"

) type=BDB;

Zapełnijmy jeszcze tabelę kilkoma osobnikami, którzy będą dysponować gotówką:

INSERT INTO rachunki VALUES (102,"Piotr", "Nowak", 298.24);

INSERT INTO rachunki VALUES (147,"Andrzej", "Borowski", 792);

INSERT INTO rachunki VALUES (101,"Jan", "Kowalski", 350.45);

Nasz baza prezentuje się następująco:

id | imie | nazwisko | kwota

----+---------+----------+--------

102 | Piotr | Nowak | 298.24

147 | Andrzej | Borowski | 792

101 | Jan | Kowalski | 350.45

Możemy zabierać się za przelewanie pieniędzy. Standardowo pewnie wykonalibyśmy coś takiego:

<?

// ten przykład nie jest poprawny

$ile=10; // przelew na 10 zł.

$id_od=101; // użytkownik, który płaci

$id_dla=102; // użytkownik, który otrzymuje

$baza = mysql_connect("localhost", "user", "password");

if ($baza) {

$wynik = mysql_select_db("baza1",$baza);

if ($wynik) {

$wynik = mysql_query(

"UPDATE rachunki SET kwota=kwota-$ile WHERE id=$id_od", $baza);

if ($wynik) echo "<p>Pieniądze zostały pobrane osobie z ID=$id_od";

$wynik = mysql_query(

"UPDATE rachunki SET kwota=kwota+$ile WHERE id=$id_dla", $baza);

if ($wynik) echo "<p>Pieniądze zostały dodane osobie z ID=$id_dla...";

}

mysql_close($baza);

}

?>

Nie jest to mądre posunięcie, bo gdyby użytkownik o podanym ID, który ma otrzymać pieniądze, nie istniał, byłby poważny problem. Ktoś straciłby pieniądze, a druga osoba nie otrzymałaby ich. W naszej kasie byłoby brak 10 zł.

Gdyby po pierwszym odjęciu kwoty ktoś wyłączył serwer, również mamy problem, bo druga osoba nie otrzymałaby pieniędzy, choć pierwszej by ich ubyło. Takich sytuacji może być wiele, szczególnie gdy jednocześnie wielu uzytkowników ma dostęp do tych samych danych.

Jedynym wyjściem jest zastosowanie transakcji, gdzie mamy możliwość wycofania jej, jeżeli w całości nie przebiegnie zgodnie z planem.

Transakcję rozpoczyna polecenie BEGIN, następnie możemy wykonywać wiele różnych poleceń SQL, po czym mamy do wyboru dwa polecenia: COMMIT akceptujące nasze dotychczasowe zmiany i ROLLBACK anulujące wszystko co nastąpiło od chwili wydania polecenia BEGIN.

Oto prosty przykład, który uniemożliwia zwiększenie kwoty, bo cofamy zmiany za pomocą polecenia ROLLBACK:

BEGIN;

UPDATE rachunki SET kwota=kwota+10 WHERE id=101;

ROLLBACK;

SELECT * FROM rachunki;

Zmiana jest tylko jedna, uaktualniamy rekord - ale tych poleceń może być znacznie więcej. Aby zatwierdzić zmiany wystarczy użyć polecenia COMMIT

BEGIN;

UPDATE rachunki SET kwota=kwota+10 WHERE id=101;

COMMIT;

SELECT * FROM rachunki;

Teraz udało się poprawnie zwiększyć kwotę użytkownikowi 101.

Poprawny przykład

Wiesz już czym są transakcje i jak je stosować. Napiszmy więc poprawny skrypt, który przeleje pieniądze z konta na konto, czyli zabierze jednemu i da drugiemu, tak aby cała transakcja po drodze nie została zniekształcona.

<?

$ile=10; // przelew na 10 zł.

$id_od=101; // użytkownik, który płaci

$id_dla=102; // użytkownik, który otrzymuje

$baza = mysql_connect("localhost", "user", "password");

if ($baza) {

$wynik = mysql_select_db("baza1",$baza);

if ($wynik) {

// zaczynamy transakcję

mysql_query("BEGIN",$baza);

$blad=0;

// obsługa pierwszej operacji (zabieramy)

$wynik = mysql_query(

"UPDATE rachunki SET kwota=kwota-$ile WHERE id=$id_od", $baza);

if (mysql_affected_rows()<>1) $blad++;

// obsługa drugiej operacji (dajemy)

$wynik = mysql_query(

"UPDATE rachunki SET kwota=kwota+$ile WHERE id=$id_dla", $baza);

if (mysql_affected_rows()<>1) $blad++;

// zatwierdzamy lub odrzucamy zmiany

if ($blad>0) mysql_query("ROLLBACK",$baza);

else mysql_query("COMMIT",$baza);

echo "Błędy podczas wykonywania zapytań: $blad";

}

mysql_close($baza);

}

?>

Transakcje zaczynamy od polecenia BEGIN rozpoczynającego zestaw poleceń SQL biorących udział w transakcji. Jednocześnie wprowadzam zmienną $blad, która będzie liczyć ile razy nie powiodły się polecenia SQL.

Wykonuję pierwsze polecenie w transakcji, gdzie odejmuje z konta użytkownika podaną kwotę. Jeżeli uda się wykonać polecenie UPDATE funkcja mysql_affected_rows() zwróci mi ilość rekordów jakie uległy modyfikacji w ostatnim poleceniu SQL.

Ponieważ odejmuję pieniądze tylko jednemu użytkownikowi, funkcja mysql_affected_rows() powinna dać w wyniku dokładnie 1. Jeżeli rekordów będzie więcej, to znaczy, że mamy kilka takich samych ID i wtedy baza nie jest spójna. Jeżeli nie uaktualnimy żadnych rekordów to znaczy, że nie ma użytkownika o podanym ID.

Reasumując: jeżeli poprawnie wykonamy operację, liczba błędów nie zostanie zwiększona, jeżeli wystąpi błąd zmienna $blad będzie zwiększona o jeden.

Identyncza sytuacja ma miejsce w przypadku drugiego polecenia w transakcji, gdzie tym razem dodajemy pieniądze innej osobie.

Na końcu pozostaje sprawdzić czy wystąpiły błędy. Jeżeli tak, możemy cofnąć obie operacje wydając polecenie ROLLBACK. Jeżeli błędów nie było, wtedy zatwierdzamy obie operacje poleceniem COMMIT.

Tym sposobem wykonamy albo wszystkie polecenia albo żadnego. Dokładnie tak, jak sobie tego życzyliśmy. Oczywiście możnaby jeszcze po drodze testować czy użytkownik ma w ogóle pieniądze na koncie aby można je przelać lub wykonywać inne czynności związane z użytkownikami i kwotami. Transakcja pozwoliłaby je wszystkie cofnąć w razie błędów występujących w trakcie wykonywania grupy poleceń.

---