System transakcji w MySQL
Domyślnie MySQL pracuje w trybie automatycznym. Oznacza to, że wynik każdej wykonanej instrukcji (utworzenie tabeli, dodanie lub
aktualizacja danych, itd.) jest od razu zapisywany na dysk.
Tryb automatyczny możesz wyłączyć instrukcją:
SET AUTOCOMMIT=0;
Po wyłączeniu automatycznego zapisu musisz użyś instrukcji COMMIT, aby zapisać wszystkie wprowadzone zmiany
na dysk lub ROLLBACK, jeżeli chcesz pominąć wszystkie zmiany wykonane od rozpoczęcia transakcji.
Jeżeli chcesz wyłączyć tryb automatyczny dla pojedynczych instrukcji, to użyj polecenia START TRANSACTION, jak
widać w tym przykładzie:
Instrukcja START TRANSACTION wyłącza tryb automatyczny dla wszystkich następujących po niej poleceń aż do podania
instrukcji COMMIT lub ROLLBACK. Potem tryb automatyczny zostaje przywrócony.
Instrukcja BEGIN jest synonimem START TRANSACTION i może być użyta zamiast niej.
Instrukcje, któe nie mogą być anulowane
Niektóre instrukcje nie mogą być anulowane. Dotyczy to w szczególności instrukcji języka definiowania danych (DDL):
założenie lub usunięcie bazy danych, założenie, usunięcie lub modyfikacja struktury tabeli, itp.
Powinieneś używać takich instrukcji poza transakcjami. Użycie ich wewnątrz transakcji spowoduje, że żadna instrukcja
transakcji nie będzie mogła być anulowana.
Instrukcje generujace wykonanie zapisu transakcji
Każda z poniższych instrukcji (i ich synonimy) automatycznie kończy otwartą transakcję tak, jakbyś wydał polecenie
COMMIT przed ich użyciem:
ALTER TABLE BEGIN CREATE INDEX
DROP DATABASE DROP INDEX DROP TABLE
LOAD DATA LOCK TABLES RENAME TABLE
SET AUTOCOMMIT=1 START TRANSACTION TRUNCATE TABLE
Również instrukcja UNLOCK TABLES kończy transakcje, jeżeli aktualnie jest blokowana jakakolwiek tabela.
Transakcje nie mogą być zagnieżdżane, ponieważ instrukcja COMMIT kończy wszystkie aktualnie otwarte transakcje.
Instrukcje LOCK TABLES i UNLOCK TABLES
LOCK TABLES
nazwa_tabeli [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, nazwa_tabeli [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}]
...
UNLOCK TABLESLOCK TABLES blokuje tabelę dla aktualnego procesu. UNLOCK TABLES zwalania wszystkie blokady nałożone
na dany proces. Wszystkie tabele, które są zablokowane dla danego procesu zostają automatycznie odblokowywane jeżeli proces ten
wywoła kolejne polecenie LOCK TABLES lub połączenie klienta z serwerem zostanie zamknięte (dlatego też,
aby zablokować kilka tabel, należy tego dokonać w pojedynczej instrukcji - przyp. tłum.).
Jeżeli na tabelę w danym procesie zostanie założona blokada READ, zarówno ten, jak i wszystkie inne procesy będą mogły
jedynie odczytywać dane z tabeli. Jeżeli natomiast na tabelę zostanie założona blokada WRITE, jedynie proces
władający aktualnie tabelą będzie mógł z niej odczytywać oraz zapisywać do niej dane. Pozostałe procesy są zablokowane.
Różnica między READ LOCAL a READ jest taka, że READ LOCAL zezwala na bezkonfliktowe
wykonywanie poleceń INSERT w czasie trwania blokady. Nie można tego jednak stosować do manipulowania plikami
baz danych spoza MySQL w czasie trwania blokady.
Każdy proces czeka (bez narzuconego limitu czasowego) do momentu nałożenia wszystkich blokad jakie zostały dla niego zlecone.
W celu zapewnienia wykonywania aktualizacji bazy tak szybko, jak to możliwe, blokady WRITE mają zazwyczaj wyższy
priorytet niż blokady READ. Oznacza to, że jeżeli jeden proces dostanie polecenie blokady READ,
a następnie drugi proces dostanie polecenie blokady WRITE, kolejne żądania blokad READ będą czekały,
aż proces WRITE założy blokadę i ją zwolni. Możesz użyć parametru LOW_PRIORITY WRITE, który zezwala
procesom na odczyt z tabeli w czasie, gdy inny proces czeka na założenie blokady WRITE. Blokad
LOW_PRIORITY WRITE powinieneś używać tylko wtedy, gdy jesteś pewny, że będzie czas, kiedy żaden proces nie będzie
stosował blokady READ (w przeciwnym razie blokada WRITE nigdy nie mogłaby zostać założona).
Podczas używania LOCK TABLES musisz zablokować wszystkie tabele których będziesz używał oraz stosować
te same aliasy, co w zapytaniach. Jeżeli jakiejś tabeli używasz w zapytaniu wielokrotnie (z różnymi aliasami), to musisz
założyć tabelę dla każdego aliasu.
Pamiętaj, żeby nie blokować tabel, których używasz w zapytaniach INSERT DELAYED. W takim przypadku instrukcja
INSERT wykonywana jest przez osobny proces (w którym prawdopodobnie nie są stosowane założone blokady).
Zazwyczaj nie ma potrzeby stosowania blokad, jako że pojedyncze rozkazy UPDATE są niepodzielne, co oznacza,
że żaden inny proces nie może wpływać na żadną aktualnie wykonywaną instrukcję SQL. Mimo to jest kilka przypadków,
kiedy blokady mogą być przydatne lub wręcz konieczne:
- jeżeli zamierzasz uruchomić wiele operacji na wielu tabelach, będzie znacznie szybciej jeżeli zablokujesz te tabele;
wadą jest to, że żaden inny proces nie będzie mógł dokonać aktualizacji tabeli z blokadą
READ ani żaden
proces nie będzie mógł nic odczytać z tabeli z blokadą WRITE;
- MySQL nie obsługuje środowiska transakcyjnego, musisz więc stosować blokady do zapewnienia, że żaden inny proces
nie zdoła "wejść" pomiędzy rozkazy
SELECT i UPDATE; przykład do poprawnego wykonania wymaga
właśnie polecenia LOCK TABLES:
W wielu wypadkach można uniknąć stosowania LOCK TABLES poprzez użycie inkrementacji
(UPDATE klient SET wartość = wartość + inna_wartość) lub funkcji LAST_INSERT_ID().
Niektóre przypadki można też rozwiązać stosując funkcje blokad na poziomie użytkownika GET_LOCK() i
RELEASE_LOCK(). Blokady te są zapisywane w tablicach haszujących serwera. Są one zaimplementowane przy
użyciu funkcji pthread_mutex_lock() i pthread_mutex_unlock(), co pozwala na dużą szybkość
wykonywania tych operacji.
Możesz też zablokować do odczytu wszystkie tabele we wszystkich bazach danych przy użyciu polecenia
FLUSH TABLES WITH READ LOCK. Jest to bardzo wygodny sposób na wykonywanie kopii zapasowej zawartości serwera
w przypadku systemów plików pozwalających na wykonanie obrazu stanu systemu w danym czasie, takich jak Veritas.
Przykład operacji na zablokowanych tabelach:
LOCK TABLES transakcje READ, klient WRITE;
SELECT SUM(wartosc) FROM transakcje WHERE klient_id = jakis_id;
UPDATE klient
SET wartosc_ogolem = suma_poprzedniej instrukcji
WHERE klient_id = jakis_id;
UNLOCK TABLES;