MySQL - Transakcje

Efekty przeprowadzanej transakcji można obserwować na bieżąco poprzez instrukcje SELECT. W przypadku stwierdzenia jakiejś nieprawidłowości możliwe jest cofnięcie się do miejsca, w którym transakcja została zapisana, lub całkowite przerwanie transakcji. Baza MySQL obsługuje transakcje stosunkowo od niedawna. Domyślnie stosowany typ tabeli MyISAM nie miał możliwości przeprowadzania transakcji. Funkcjonalność ta pojawiła się dopiero po wprowadzeniu tabel InnoDB.

Przygotowanie tabeli

Przed rozpoczęciem transakcji trzeba odpowiednio przygotować tabelę w bazie danych. Tabela, na której będziemy przeprowadzać operacje powinna być typu InnoDB. Aktualnie jest to jedyny (obok nie w pełni zintegrowanego BerkeleyDB) typ tabeli w MySQL, który obsługuje transakcje. Poniżej znajduje się przykładowa struktura tabeli, na której będziemy przeprowadzać operacje.

CREATE TABLE `trans` (
`id` INT NOT NULL AUTO_INCREMENT ,
`re` INT NOT NULL ,
`txt` VARCHAR( 100 ) NOT NULL ,
PRIMARY KEY ( `id` ) 
) TYPE = innodb;

Rozpoczynanie transakcji

Aby rozpocząć transakcję w bazie MySQL należy posłużyć się poleceniem START TRANSACTION; Przed rozpoczęciem transakcji można również określić poziom izolacji za pomocą polecenia SET TRANSACTION ISOLATION LEVEL. Dla tego polecenia dostępne są cztery parametry: { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }. Jeżeli nie użyjemy polecenia SET TRANSACTION, zostanie jako domyślnie użyta opcja REPEATABLE READ. Poniżej przykład rozpoczęcia transakcji:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

Zapisywanie stanu transakcji

Zapisywanie stanu transakcji nie jest krokiem koniecznym. Wykorzystuje się je w przypadku, gdy podczas przeprowadzania transakcji mogą wystąpić jakieś problemy. Wtedy jest możliwość cofnięcia się do wcześniej zapisanego stanu. Zapisania stanu transakcji dokonuje się poleceniem SAVEPOINT punkt; gdzie punkt jest nazwą zapisywanej pozycji.

Kończenie transakcji

Do zakończenia aktualnej transakcji używa się polecenia COMMIT;. Po zakończeniu transakcji wszystkie przeprowadzone operacje są zapisywane do bazy danych, bez możliwości jej odwołania.

Przerwanie transakcji

Są jednak przypadki, kiedy jesteśmy zmueszni z jakiegoś powodu przerwać transakcję. Do tego celu służy polecenie ROLLBACK; W przypadku jego użycia wszystkie wydane polecenia podczas trwania transakcji zostaną odwołane i zawartość bazy nie ulegnie zmianie. Za pomocą tego polecenia możliwe jest też cofnięcie się do wcześniej zapisanego stanu transakcji. W tym wypadku polecenie będzie wyglądało następująco: ROLLBACK TO punkt; gdzie punkt jest nazwą ostatnio zapisanej pozycji.

Przykłady przebiegu kilku transakcji:

Transakcja 1:

START TRANSACTION;
INSERT INTO trans (re,txt) VALUES (0,'rek 1');
INSERT INTO trans (re,txt) VALUES (0,'rek 2');

Podgląd stanu tabeli w przypadku pomyślnego zakończenia transakcji:

SELECT * FROM trans;
+----+----+-------+
| id | re | txt   |
+----+----+-------+
|  1 |  0 | rek 1 |
|  2 |  0 | rek 2 |
+----+----+-------+

COMMIT;

Tu nastąpiło zakończenie transakcji – dodane rekordy znalazły się w bazie.

Transakcja 2:

START TRANSACTION;
INSERT INTO trans (re,txt) VALUES (5,'trans 2');

Podgląd stanu transakcji:

SELECT * FROM trans;
+----+----+---------+
| id | re | txt     |
+----+----+---------+
|  1 |  0 | rek 1   |
|  2 |  0 | rek 2   |
|  3 |  5 | trans 2 |
+----+----+---------+

ROLLBACK;

Tutaj nastąpiło przerwanie transakcji. Aby pokazać, że wpis nie został dodany do bazy wywołujemy ponownie komendę:

SELECT * FROM trans;
+----+----+-------+
| id | re | txt   |
+----+----+-------+
|  1 |  0 | rek 1 |
|  2 |  0 | rek 2 |
+----+----+-------+

Transakcja 3:

START TRANSACTION;
UPDATE trans SET re=5 WHERE id=2;
SAVEPOINT krok1;
INSERT INTO trans (re,txt) VALUES (5,'trans 3');

Sprawdzenie stanu transakcji:

SELECT * FROM trans;
+----+----+---------+
| id | re | txt     |
+----+----+---------+
|  1 |  0 | rek 1   |
|  2 |  5 | rek 2   |
|  4 |  5 | trans 3 |
+----+----+---------+

ROLLBACK TO krok1;
COMMIT;

Transakcja została pomyślenie zakończono, jednak przed jej zakończeniem cofnęliśmy się do wcześniej zapisanego stanu transakcji. W wyniku tego polecenie dodania nowego rekordu zostało odrzucone:

SELECT * FROM trans;
+----+----+-------+
| id | re | txt   |
+----+----+-------+
|  1 |  0 | rek 1 |
|  2 |  5 | rek 2 |
+----+----+-------+