Projektując aplikacje używające baz danych, spotkamy się z sytuacjami współbieżnego dostępu do danych. Może to mieć różne implikacje — stan bazy danych może być niepoprawny lub niektóre dane mogą zostać utracone. Aby zapobiegać tego typu sytuacjom, możemy wykorzystać różne sposoby kontroli dostępu do zasobów, takie jak optymistyczne czy pesymistyczne blokowanie.
Załóżmy sytuację, gdy dwóch użytkowników próbuje zaktualizować ten sam rekord w bazie danych:
| SELECT * FROM account WHERE company_id =1 Returns id=1;company_id=1; balance=100 |
SELECT * FROM account WHERE company_id =1 Returns id=1;company_id=1; balance=100 |
| UPDATE account SET balance = balance - 70 WHERE company_id = 1 | |
| UPDATE account SET balance = balance - 70 WHERE company_id = 1 | |
| SELECT * FROM account WHERE company_id =1 Returns id=1;company_id=1; balance=-40 |
Jak widać w powyższej tabeli, pierwszy użytkownik pobiera rekord z tabeli account i na podstawie otrzymanych danych próbuje obciążyć konto kwotą $70.
W tym samym czasie drugi użytkownik wykonuje tę samą operację. Po wykonaniu tych dwóch aktualizacji stan konta będzie ujemny, czego na potrzeby tego wpisu, chcemy uniknąć. Jak można zapobiegać takim sytuacjom na poziomie bazy danych?
W MySQL 8 możemy zrobić to na dwa sposoby: używając pesymistycznego lub optymistycznego blokowania.
Pesymistyczne blokowanie
W tego typu blokowaniu uniemożliwiamy wykonywanie operacji na danych tabelach lub wierszach przez innych użytkowników do czasu zakończenia transakcji.
Jest to, w przeciwieństwie do optymistycznego blokowania, mechanizm wbudowany w bazę danych dający nam wyłączny dostęp do danego zasobu.
Wyróżniamy tu dwa typy blokad: współdzielone (shared) i na wyłączność (exclusive).
Jako, iż powyższy przykład dotyczy pojedynczych wierszy w bazie, skupię się tu tylko na blokowaniu wybranych wierszy, nie zaś całych tabel. Wspomnę tylko, iż zarówno blokowania tabel, jak i poszczególnych rekordów można używać równolegle.
Rodzaje blokad
Tak jak wspomniałem wyżej, do blokowania wierszy możemy wykorzystać dwa typy blokad — współdzieloną (shared) i na wyłączność (exclusive).
Shared lock
Blokowanie współdzielone umożliwia transakcjom, które zakładają ten rodzaj blokady tylko na odczyt tych samych rekordów. Oznacza to, iż inne transakcje, nie mogą na nich wówczas wykonywać żadnych operacji, tj. modyfikacji i usuwania.
Tego rodzaju blokadę zakładamy poprzez dodanie na końcu zapytania select FOR SHARE, np.:
START TRANSACTION; SELECT * from accounts WHERE owner_id = 1 FOR SHARE; # do something COMMIT;Zakończenie transakcji, czy to dzięki COMMIT lub ROLLBACK zwalnia blokadę z wierszy.
W przypadku, gdy inna transakcja będzie próbowała założyć na ten rekord blokadę na wyłączność, będzie musiała poczekać, aż blokada współdzielona zostanie zwolniona.
Exclusive lock
Ten rodzaj blokuje rekordy nie tylko do modyfikacji, ale także do odczytu — gdy inna transakcja będzie próbowała założyć shared lub exclusive lock na te wiersze, będzie musiała poczekać na zwolnienie tej blokady przez obecną transakcję.
Blokowanie na wyłączność zakładamy poprzez dodanie na końcu zapytania select FOR UPDATE, np.:
START TRANSACTION; SELECT * from accounts WHERE owner_id = 1 FOR UPDATE; # do something COMMIT;Jeśli pobieramy taki rekord w innej transakcji bez używania żadnego blokowania, zostanie on zwrócony, ale nie będzie można na nim wykonać operacji do czasu zwolnienia blokady.
Blokowanie zakresów wierszy
Powyżej pokazałem jak blokować pojedyncze rekordy. MySQL umożliwia także blokowanie całych zakresów danych.
Przykładowo SELECT * FROM accounts WHERE id > 1 FOR UPDATE zablokuje wszystkie rekordy z identyfikatorem > 1, a także uniemożliwi wstawianie nowych rekordów.
Jeśli zaś przykładowo założymy blokadę na lukę, która nie ma rekordów (max id = 10, a my zakładamy na id > 100), będzie można założyć inną blokadę na tę lukę w innej transakcji.
Taką blokadę możemy założyć również posługując się innymi kryteriami niż identyfikator.
SELECT * FROM user WHERE first_name = 'John' FOR UPDATEPowyższe zapytanie zablokuje wszystkie wiersze, gdzie first_name = John. Nie będzie można pobrać takiego wiersza, a także wstawić nowego, gdzie powyższa kolumna będzie miała tę właśnie wartość.
Inne opcje blokowania
Do powyższych zapytań, a w zasadzie do fragmentów for share lub for update możemy dodać jeszcze dwie opcje. Pierwszą z nich jest NOWAIT. Sprawi ona, iż zapytanie nie będzie czekało na odblokowanie wierszy i wyrzuci błąd, jeżeli nie uda jej się od razu pobrać danych.
Druga opcja to SKIP LOCKED. Powoduje ona, iż zapytanie pobierze tylko te wiersze, które nie są w tej chwili blokowane przez inną transakcję.
Przykłady użycia powyższych opcji są następujące:
SELECT * FROM user WHERE first_name = 'John' FOR UPDATE NOWAIT SELECT * FROM user WHERE first_name = 'John' FOR UPDATE SKIP LOCKEDDead locks
W przypadku blokowania pesymistycznego może wystąpić sytuacja, gdzie bardzo wiele wątków będzie próbowało pobrać i zablokować dany wiersz. W przypadku, gdy wiersz ten nie zostanie zwolniony we właściwym czasie (domyślnie 50 sekund w MySQL), oczekujące transakcje wyrzucą błąd:
[40001][1205] Lock wait timeout exceeded; try restarting transaction
Optymistyczne blokowanie
Optymistyczne blokowanie nie jest funkcjonalnością MySQL. Jest strategią, w której pod uwagę bierzemy numer wersji rekordu, i sprawdzamy, czy zmienił się podczas aktualizowania danych. Przykładowo, załóżmy, iż nasza tabela account ma dodatkową kolumnę - version.
| 1 | 1 | 100 | 1 |
Pobieramy rekord z bazy zwykłym zapytaniem select:
SELECT * FROM account WHERE id = 1;Następnie, gdy chcemy zaktualizować rekord, używamy do tego kolumny version, np.:
UPDATE account SET balance = balance - 70, version = version + 1 WHERE id = 1 AND version = 1W klauzuli UPDATE w sekcji WHERE użyliśmy numeru wersji, który został pobrany w zapytaniu select.
Powyższy sposób sprawi, iż gdy inny wątek wykona aktualizację rekordu po tym, gdy pobraliśmy ten rekord z bazy, nasza aktualizacja nie powiedzie się, ponieważ numer wersji tego wiersza będzie już inny. I na tym właśnie polega optymistyczne blokowanie — jeżeli ilość zmodyfikowanych wierszy to 1, możemy uznać, iż operacja się udała. jeżeli nie zaktualizowano żadnego rekordu, możemy, np. w naszej aplikacji rzucić wyjątek lub obsłużyć tę sytuację w inny sposób.
Wersja niekoniecznie musi być liczbą całkowitą. Można do tego celu użyć innych typów, np. datę, czy sumę kontrolną.
Które blokowanie wybrać?
Oczywiście to zależy od przypadku, w którym chcemy jej użyć.
Optymistyczne blokowanie sprawi się dobrze w momencie, gdy nie spodziewamy się zbyt wielu kolizji. Jest wydajniejsza, ze względu na to, iż nie blokuje wierszy, jednak w przypadku kolizji konieczne będzie obsłużenie takiej sytuacji, np. poprzez ponowne pobranie i wykonanie danej operacji.
Jedną z zalet tego typu blokowanie jest to, iż do działania nie potrzebuje ciągłego utrzymywania połączenia z bazą danych w sesji — możemy pobrać rekord, rozłączyć się, a następnie znowu połączyć, aby wykonać aktualizację wiersza.
W pesymistycznym blokowaniu blokujemy rekord lub zakres rekordów do wyłącznego użytku. Niewątpliwą przewagą nad blokowaniem optymistycznym jest to, iż baza czeka na pobranie zablokowanego rekordu, na którym następnie możemy wykonać dowolną ilość operacji, bez przejmowania się, iż inny wątek może zmodyfikować jego stan. Oczywiście jest to miecz obusieczny, ponieważ w przypadku próby dostępu przez zbyt wiele wątków, może powodować wydłużenie każdej operacji, a choćby dead locki. Dlatego trzeba być ostrożnym, projektując tego typu rozwiązania.
Podsumowanie
Obydwa rodzaje blokowania wymienione wyżej mają swoje plusy i minusy. Gdy zależy nam na zwiększeniu współbieżności, powinniśmy rozważyć wybór optymistycznego blokowania. W przypadku, gdy często aktualizujemy dany zasób i nie chcemy ponosić wysiłku w naprawianiu nieudanych operacji, powinniśmy wykorzystać blokowanie pesymistyczne.
