Obsługa współbieżności w języku SQL na przykładzie DBMS MySQL

Z jednego z postulatów Codd'a dot. systemów zarządzania bazami danych wynika, że musi on obsługiwać współbieżność. Sytuacje, gdy z bazy danych korzysta tylko jeden użytkownik w środowiskach produkcyjnych występują w praktyce bardzo rzadko. zwykle mamy do czynienia z dostępem wielu użytkowników do tej samej bazy danych.

Może się zdarzyć, że w tym samym czasie więcej niż jeden użytkownik będzie próbował modyfikować te same rekordy w tej samej tabeli. W takiej sytuacji serwer baz danych musi sensownie rozstrzygnąć ten konflikt. Niezależnie jednak od tego, jak fizycznie będzie to zrobione, poszczególni użytkownicy powinni mieć ciągłe wrażenie, że mają wyłączny dostęp do zasobów serwera.

Z pomocą przychodzi tutaj tzw. mechanizm blokowania, którego istotą jest możliwość chwilowego zablokowania modyfikowanych przez jednego użytkownika danych tak, aby do czasu zakończenia modyfikacji nikt inny nie mógł dokonać ich zmiany. blokowanie takie powinno trwać możliwie jak najkrócej oraz w miarę możliwości odbywać się automatycznie, tak aby użytkownicy nie musieli czekać zbyt długo na zwolnienie zablokowanych zasobów.

UWAGA: Jak wiadomo, serwer MySQL potrafi obsługiwać różne mechanizmy składowania danych (silniki). Obecnie najbardziej popularnymi silnikami do obsługi baz danych są MyISAM oraz InnoDB. Najnowszym silnikiem, który obsługuje transakcje, klucze obce i pozwala na definiowanie węzłów spóności (patrz: integralność referencyjna) jest mechanizm składowania InnoDB, który dodatkowo obsługuje automatyczne blokowanie rekordów przy konfliktach. Niestety, choć obecnie jest najlepszym znanym silnikiem (mechanizmem) obsługującym transakcje, odbywa się to kosztem wydajności. W przypadku dużych baz danych w celu poprawienia wydajności niektórzy stosują mechanizm MyISAM, który nie obsługuje współbieżności i wymaga ręcznego zakładania blokad i ich późniejszego zdejmowania. W mechaniźmie MyISAM można zakładać blokady na poziomie tabel.

Pomimo tego, że mechanizm InnoDB obsługuje automatyczne zakładanie blokad, to można jawnie zakładać blokady zarówno na poziomie tabel, jak i na poziomie rekordów.

LOCK TABLES emp READ; -- Sesja blokująca może odczytywać, inni mogą odczytywać, nikt nie może zapisywać

LOCK TABLES emp WRITE; -- Sesja blokująca może odczytywać i zapisywać, inni nie mają dostępu do odczytu ani zapisu

UNLOCK TABLES; -- Zdjęcie wszystkich założonych przez nas blokad

UNLOCK TABLES emp; -- Zdjęcie blokady z jednej tabeli

START TRANSACTION;
SELECT *
    FROM emp
    WHERE `id` BETWEEN 5 AND 15;
COMMIT;
-- Na czas transakcji po wydaniu komendy SELECT dostęp do wybranych przez tę komendę krotek zostaje zablokowany dla innych [często nie ma dostępu do całej tabeli - blokada zapisu w tabeli, zamiast w wierszu]

UWAGA: W przypadku mechanizmu InnoDB nie musimy stosować jawnego blokowania danych - silnik automatycznie obsługuje blokowanie - chyba, że zależy nam na tym, by jakaś transakcja była wykonana w pierwszej kolejności.

Zakleszczenia w języku SQL na przykładzie DBMS MySQL

Do tzw. zakleszczenia dochodzi przy pracy współbieżnej. Gdy dwóch lub więcej użytkowników usiłuje zmodyfikować te same dane, wszystkie sesje zostają zablokwoane, a użytkownicy nie mogą pracować. Serwer MySQL próbuje wówczas, po pewnym czasie (który standardowo wynosi ~5s), odblokowac sesje. Skutkuje to tym, że serwer w jednej sesji (użytkownik nie ma na to wpływu) automatycznie wycofuje transakcję, wyświetlając użytkownikowi stosowny komunikat.

Przebieg ćwiczeń

1. Zalogować się do serwera MySQL i utworzyć użytkownika o nazwie nazwisko_kolegi, który będzie się logować z komputera o adresie IP adres.IP.Twojego.kolegi przy pomocy hasła (wymyślić).

2. Utworzonemu użytkownikowi nadać pewłne uprawnienia do bazy danych firma.

3. Zalogować się na własnego użytkownika na swoim serwerze MySQL i na serwerze kolegi.

4. Przełączyć serwer w tryb jawnego zatwierdzania transakcji.

5. Korzystając z transakcji spróbować równocześnie: (1) osoba podnosi wszystkie ceny o 10%, (2) osoba obniża wszystkie ceny o 10%.

6. Zasymulować pracę blokad w ten sposób, że jedna osoba będzie podwyższać sugerowaną cenę produktów o 10%, a druga będzie starała się zmienić krótką nazwę jakiegoś produktu.

Nim zatwierdziłem transakcję, czas oczekiwania konsoli serwera kolegi (biały wiersz polecenia) został przekroczony, dlatego jego transakcja została przerwana.

7. Zasymulować jawne blokowanie danych na poziomie rekordów w tabeli emp. (1) osoba blokuje rekordy zawierające dane pracowników pracujących na stanowisku Warehouse Manager i próbuje zwiększyć pensje tym pracownikom o 10%, a (2) osoba próbuje zmniejszyć tym samym pracownikom pensje o 10%. Obie osoby korzystają z transakcji.

8. Zasymulować jawne blokowanie danych na poziomie tabel - jeden użytkownik blokuje tabelę emp, a drugi usiłuje coś w niej zmodyfikować.