Opracował:

Internetowe bazy danychwykład 3: transakcyjne i nietransakcyjne

mechanizmy MySQL

dr inż. Jacek Mazurkiewicz

e-mail: Jacek.Mazurkiewicz@pwr.wroc.pl

p. 225 C-3, tel: 71 3202681

Typy tabel MySQL

Czy transakcje potrzebne?

w aplikacjach WWW tabele transakcyjne nie są potrzebne

bezstanowe środowisko HTTP nie wymaga zatwierdzania i

odwoływania – operacje nie są skomplikowane i nie jest konieczna

niezależność, transakcja jako logika PHP

domyślny – MyISAM

inne możliwe: Merge, Heap, InnoDB, BDB

jedna baza – różne typy tabel możliwe

Kryteria wyboru

tabela transakcyjna – transaction-safe table – TST: InnoDB, BDB

tabela nietransakcyna – non-transaction-safe table – NTST:

MyISAM, Merge, Heap

Tabele transakcyjne – zalety:

dane bardziej bezpieczne, można je odtworzyć gdy serwer

MySQL lub system się załamie

odtworzenie danych automatyczne lub ręczne na podstawie

kopii zapasowej i dziennika tabeli

łączenie kilku instrukcji SQL w jeden zbiór – transakcję – zbiór

taki to jedna, atomowa operacja

albo zrealizują się wszystkie operacje ze zbioru albo żadna z

nich

można odwołać dotychczas wykonane operacje gdy akcja

zostanie przez użytkownika anulowana lub któryś z kroków się

nie powiedzie

można także odwołać operacje gdy współpraca interakcja

użytkownika z aplikacją bazodanową WWW będzie podejrzana

Tabele nietransakcyjne – zalety:

Przykład tworzenia tabeli

InnoDB

MyISAM, Merge, Heap, InnoDB – dostępne od MySQL 4

BDB – trzeba wkompilować w serwer

jak zakładamy tabelę, która nie jest realizowana – MyISAM

CREATE TABLE winery (

winery_id int(4) NOT NULL,

winery_name varchar(10) NOT NULL,

region_id int(4) NOT NULL,

PRIMARY KEY (winery_id),

KEY name (winery_id),

KEY region (region_id)

) type = InnoDB;

są znacznie szybsze – zapewnienie bezpieczeństwa danych i

zarządzanie nimi jest czasochłonne

zużywają mniej zasobów – tak dysku, jak i pamięci

prostsza idea konstrukcyjna

MyISAM:

bardzo szybkie wykonywanie zapytań, czasochłonność modyfikacji danych

niewielka

trzy wbudowane mechanizmy przechowywania danych – łatwość tworzenia

tabel o różnych konstrukcjach

najlepiej dopasowana do aplikacji WWW

blokowanie tabel – jedna lub wiele tabel jest niedostępnych wtedy dla innych

użytkowników

blokady potrzebne na krótko – DELETE i UPDATE na grupach wierszy –

szybko, dostęp – index, klucz główny

blokady rzadko potrzebne – odczytów dużo więcej niż zapisów,

współbieżność nie jest nagminna

jedynie blokowanie – grupowanie GROUP BY – generalnie dostęp do

większości wierszy w tabeli

zarządzanie współbieżnymi uaktualnieniami – automatyczna kopia nowych

danych, reszta (SELECT) czyta niezmienione – wersjonowanie danych

atrybuty stałej długości – na dysku wiersze stałej długości, szybki dostęp,

łatwość rekonstrukcji, zmienna długość - dynamiczna

InnoDB (1):

ogólnego przeznaczenia, alternatywa do MyISAM

tabele transakcyjne – obecny FOREIGN KEY, zatwierdzanie, odwoływanie

operacji, odzyskiwanie danych, blokowanie wierszy

zalety rzadko przewyższają wady w świecie WWW – lepiej MyISAM

COMMIT I ROLLBACK – zbiór instrukcji SQL to jeden blok, albo zrobi się

wszystko, albo nic

elastyczne, szybkie blokowanie na poziomie wierszy – blokowane są wiersze,

gdzie realizuje się zapytania i uaktualnienia, a nie całe tabele jak w MyISAM

– sprawniejsze niż w MyISAM przy dużej liczbie użytkowników

ograniczenia kluczy obcy – dbałość o integralność danych – nie można

dodać żadnych wierszy, które nie mają odpowiedników w innej tabeli

ograniczenia kluczy obcych nie są konieczne – wymagają dużo zasobów, z

powodzeniem może je zastąpić logika PHP

punkty kontrolne odzyskiwania danych – wpis do pliku dziennika – w

przypadku załamania bazy lub systemu można odzyskać dane z tabeli

InnoDB

InnoDB (2):

elastyczna izolacja transakcji – model można rozluźnić by

przyspieszyć wykonywanie zapytań część właściwości transakcji

znika

elastyczne indeksowanie – automatyczna decyzja czy w tabeli

potrzebny jest szybki indeks mieszający

zajmują dużo więcej przestrzeni niż MyISAM

jeśli są klucze obce to trzeba na zarządzanie tabelami dużo więcej

zasobów

wersjonowanie danych i użycie transakcji – zarządzanie tabelami –

dużo więcej zasobów

większa ilość pamięci by panować nad blokadami wierszy

blokowanie może obniżyć wydajność – skoro jest na poziomie

wierszy –więcej trzeba ustanawiać i zdejmować blokad

indeksy zakładać gdy ładujemy dane do tabel, potem jest to bardzo

czasochłonne

Heap:

do specjalnych celów, mają ograniczenia

przechowywane w pamięci, nie na dysku

tabele tymczasowe, tabele z częstymi odwołaniami

ograniczenia – jak serwer zatrzyma się, ponowne uruchomienie, nie

ładują się

indeksowanie mieszające – najszybsze wyszukiwanie – dokładne

dopasowanie jako = i <=> inne porównania nie działają, nie działa

sortowanie ORDER BY – do znalezienia wiersza związanego z

wartością klucza

nie obsługują wszystkich typów – brak TEXT, BLOB, brak

mechanizmu AUTO_INCREMENT

trzymanie w pamięci – zasobochłonne – używać z umiarem, małe

„gabaryty”

blokady na poziomie całych tabel

jak padnie serwer to tabela przepada – jest tylko w pamięci!

Transakcje – COMMIT i ROLLBACK (1)

seria instrukcji SQL jako jedna niepodzielna grupa, działa tylko w

tabelach transakcyjnych (InnoDB)

transakcje = powtarzalne odczyty

domyślnie MySQL na automatyczne zatwierdzanie – aktualizacja danych

zaraz po wykonaniu modyfikacji

wyłączenie – poprzez ustawienie parametru bądź włączenie transakcji:

SET AUTOCOMMIT = 1;

START TRANSACTION;

SELECT * FROM tabela1 WHERE typ LIKE ”tutu”;

UPDATE tabela2 SET suma = 1 WHERE typ LIKE ”tutu”;

ROLLBACK;

SET AUTOCOMMIT = 0;

START TRANSACTION;

SELECT * FROM tabela1 WHERE typ LIKE ”tutu”;

UPDATE tabela2 SET suma = 1 WHERE typ LIKE ”tutu”;

COMMIT;

Transakcje – COMMIT i ROLLBACK (2)

BEGIN;

SELECT * FROM tabela1 WHERE typ LIKE ”tutu”;

UPDATE tabela2 SET suma = 1 WHERE typ LIKE ”tutu”;

ROLLBACK;

ALTER TABLE BEGIN CREATE INDEX

DROP DATABASE DROP INDEX DROP TABLE

LOAD MASTER TABLE LOCK TABLES RENAME TABLE

SET AUTOCOMMIT=1 START TRANSACTION TRUNCATE TABLE

UNLOCK TABLES CREATE TABLE

nie można cofnąć instrukcji, które tworzą i usuwają bazy danych, tworzą,

usuwają tabele – projektować tak transakcje by ich nie było w nich, inaczej

nie można cofnąć całej transakcji

niejawne zatwierdzenie robią:

BEGIN WORK;

SELECT * FROM tabela1 WHERE typ LIKE ”tutu”;

UPDATE tabela2 SET suma = 1 WHERE typ LIKE ”tutu”;

COMMIT;

Transakcje – COMMIT i ROLLBACK (3)

SAVEPOINT indetyfikator

ROLLBACK TO SAVEPOINT indetyfikator

blokowanie tabel - LOCK TABLE – blokowanie tabeli dla potrzeb bieżącego

wątku, UNLOCK TABLES – zwalnia wszystkie blokady nałożone w danym

wątku, niejawne odblokowanie przy wykonaniu innej LOCK TABLES lub przy

zamknięciu połączenia z serwerem

LOCK TABLES niebezpieczna dla transakcji – zatwierdza aktywną transakcję

przed nałożeniem blokad

Użycie LOCK TABLES wymaga uprawnień do LOCK TABLES i SELECT,

wcześniejsze wersje MySQL: SELECT, INSERT, DELETE, UPDATE

blokada READ – tylko odczyt z tabeli, WRITE – wątek, który założył może

pisać, czytać, reszta wątków zablokowana

READ LOCAL – możliwe są niekonfliktowe INSERT-y

jeśli blokada z synonimem trzeba odwołać się z synonimem:

wycofanie transakcji do punku kontrolnego:

Transakcje – COMMIT i ROLLBACK (3)

LOCK TABLE t READ;

SELECT * FROM t AS synonim;

LOCK TABLE t AS synonim READ;

SELECT * FROM t;

SELECT * FROM t AS synonim;

WRITE ma wyższy priorytet od READ – jeśli nałożono READ i czeka WRITE

do nałożenia, to stawiane kolejne READ muszą czekać aż WRITE będzie

nałożona i zwolniona

LOW_PRIORITY WRITE – pozwala nałożyć READ chociaż czekamy na

rozstrzygnięcie z WRITE

LOCK TABLES: blokada do odczytu i zapisu, najpierw zapis, KILL pozwala

znieść oczekiwanie na założenie blokady

w MyISAM blokowanie przyspiesza wykonywanie wstawiania, aktualizacji,

usuwania, klucze są pamiętane w pamięci podręcznej, normalnie jest ona

czyszczona po każdym zapytaniu SQL-owym

zasadniczo nie ma potrzeby robienia blokad!

Transakcje – COMMIT i ROLLBACK (4)

UPDATE klienci SET wartosc = wartosc + nowa_wartosc

bez LOCK TABLES między SELECT a UPDATE inny wątek może

spowodować wstawienie nowego wiersza do tabeli trans

alternatywą do blokowania są operacje SET postaci:

LOCK TABLES trans READ, klienci WRITE;

SELECT SUM(value) FROM trans WHERE id_klienta == pewien_id;

UPDATE klienci

SET razem = suma_z_poprzednich_instrukcji

WHERE id_klienta == pewnie_id;

UNLOCK TABLES;