Problem

Próbuję dowiedzieć się, jak poprawnie skonfigurować transakcję w bazie danych i uwzględnić potencjalne opóźnienie.

Ustawić

W moim przykładzie mam tabelęusers, keys, gdzie każdy użytkownik może mieć wiele kluczy, a aconfig tabela określająca, ile kluczy każdy użytkownik może mieć.

Chcę uruchomić procedurę składowaną, która:

oblicza, czy dany użytkownik może zażądać klucza.uzyskać dostępny, nieodebrany klucz.próbuje odkupić klucz dla danego użytkownika.

pseudokodem procedury byłoby:

    START TRANSACTION
(1)     CALL check_permission(...,@result);
        IF (@result = 'has_permission') THEN
(2)         SET @unclaimed_key_id = (QUERY FOR RETURNING AVAILABLE KEY ID);
(3)         CALL claim_key(@unclaimed_key_id);
        END IF;
    COMMIT;

Problem, na który wpadam, polega na tym, że kiedy symuluję opóźnienie po kroku1, (używającSELECT SLEEP(<seconds>)), dany użytkownik może wykupić wiele kluczy, gdy ma tylko uprawnienia do odkupienia jednego, uruchamiając procedurę w wielu sesjach, zanim pierwsza procedura zakończy sen (co również ma na celu symulację opóźnienia)

Oto kod dlastoły iprocedury (uwaga: dla małego przykładu nie zawracałem sobie głowy indeksami i obcymi kluczami, ale oczywiście używam tych w rzeczywistym projekcie).

Aby zobaczyć mój problem, po prostu ustaw tabele i procedury w bazie danych, a następnie otwórz dwa terminale mysql, aw pierwszym uruchom to:

CALL `P_user_request_key`(10,1,@out);
SELECT @out;

A potem szybko (masz 10 sekund) w drugim uruchomieniu:

CALL `P_user_request_key`(0,1,@out);
SELECT @out;

Oba zapytania zostaną pomyślnie zwróconekey_claimed i UżytkownikBob skończy się przypisaniem mu 4 kluczy, chociaż maksymalna wartość w konfiguracji jest ustawiona na 3 na użytkownika.

PytaniaJaki jest najlepszy sposób uniknięcia takich problemów? Próbuję użyć transakcji, ale czuję, że to nie pomoże w tym problemie i może to być niewłaściwe.Zdaję sobie sprawę, że jednym z możliwych sposobów rozwiązania tego problemu jest po prostu enkapsulowanie wszystkiego w jednym dużym zapytaniu o aktualizację, ale wolałbym tego uniknąć, ponieważ lubię mieć możliwość ustawiania indywidualnych procedur, z których każda ma tylko zrobić pojedynczy zadanie.Baza danych za tym przykładem jest przeznaczona dla wielu (tysięcy) jednoczesnych użytkowników. W związku z tym najlepiej byłoby, gdyby jeden użytkownik próbujący zrealizować kod nie blokował wszystkich innych użytkowników. Czuję się dobrze, zmieniając swój kod, aby po prostu próbować ponownie go wymienić, jeśli inny użytkownik już zażądał klucza, ale absolutnie nie powinno się zdarzyć, że użytkownik może wykorzystać dwa kody, gdy mają tylko pozwolenie na ich zdobycie.