Проблема I '

Я пытаюсь понять, как правильно настроить транзакцию в базе данных и учесть потенциальную задержку.

Настройка

В моем примере у меня есть таблица,userskeysгде каждый пользователь может иметь несколько ключей иconfig таблица, которая определяет, сколько ключей может иметь каждый пользователь.

Я хочу запустить хранимую процедуру, которая:

выясняет, разрешено ли данному пользователю запрашивать ключ.получить доступный, невостребованный ключ.пытается выкупить ключ для данного пользователя.

псевдокод для процедуры будет:

    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;

Проблема, с которой я сталкиваюсь, состоит в том, что, когда я симулирую отставание после шага1, (используяSELECT SLEEP()), Это'Для данного пользователя возможно использовать несколько ключей, когда у них есть только разрешения на использование одного, запустив процедуру в нескольких сеансах до того, как первая процедура завершит свой сон (что опять-таки должно симулировать задержку)

Вот код длястолы а такжеПроцедуры (примечание: для небольшого примера я неЯ не использую индексы и внешние ключи, но, очевидно, я использую их в реальном проекте).

Чтобы увидеть мою проблему, просто настройте таблицы и процедуры в базе данных, затем откройте два терминала mysql и при первом запуске выполните следующее:

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

И затем быстро (у вас есть 10 секунд) во втором запустить это:

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

Оба запроса будут успешно возвращеныkey_claimed и пользовательBob в конечном итоге ему будут назначены 4 клавиши, хотя максимальное значение в конфигурации установлено на 3 для пользователя.

ВопросыКаков наилучший способ избежать подобных проблем? Я'я пытаюсь использовать транзакцию, но мне хочетсяне собирается помогать конкретно с этой проблемой, и может быть неправильно это реализовывать.Я понимаю, что одним из возможных способов решения проблемы было бы просто инкапсулировать все в один большой запрос на обновление, но я бы предпочел этого избегать, поскольку мне нравится иметь возможность настраивать отдельные процедуры, где каждая из них предназначена для выполнения только одной процедуры. задача.База данных за этим примером предназначена для использования многими (тысячами) одновременно работающих пользователей. Поэтому было бы лучше, если бы один пользователь, пытающийся получить код, неблокировать всех других пользователей от выкупа. Я'Я согласен с изменением моего кода, чтобы просто попытаться снова использовать его, если другой пользователь уже запросил ключ, но совершенно не должно быть так, чтобы пользователь мог использовать два кода, когда у него есть только разрешение на его получение.