Снова о DEADLOCK

Снова по deadlock

Я постарась быть максимально кратким, насколько это возможно.

Deadlock - это состояние базы данных, когда, условно говоря, она зависает. Почему так происходило? Я говорю, происходило, потому что вероятность данной проблемы я максимально свел к нулю. Если у кого-то не так, это обычно частные случаи неправильной настройки, которые я бывает объясняю в личке и это не всегда видится как ответ в форуме.

Итак, почему происходил deadlock? Если сеть большая, то часто к NoDeny идут параллельно несколько запросов на получение свободных динамических ip. И когда база данных "захватывает" (назначает юзеру) один и тот же ip разным пользователям, то возникает конфликт, который не разруливается.

Может возникнуть вопрос: может что-то не так делаю с процедурами раз это происходит? Я отвечу: все правильно ибо это проблема mysql

Почему я перекидываю ответственность на mysql? Потому что в 8й версии они эту проблему исправили. Но по-порядку...

Функция get_ip занимается выдачей ip по id абонента. Если ip статический, то это просто прекрасно, у вас никогда не будет дедлоков (рекомендую

). Если динамический, то из таблицы ip_pool выбирается любой ip, который не назначен никому (uid=0). Чтобы в один момент не было попытки выдать один и тот же свободный ip разным юзерам, get_ip выбрает ip по случайному id. Тут есть свои нюансы, не хочу забивать вам голову, но скажу, что order by rand() не катит и сделано это немного иначе. Но в любом случае нет 100%-й гарантии выборки одного и того же ip разным абонентам, поэтому если вы посмотрите процедуру get_ip, то увидите, что она довольно мудреная. Как раз из-за разруливания этой ситуации. Честно скажу, она работает, но у меня "болит сердце" от ее корявого вида. Хотя, повторюсь, она работает нормально.

По документации я рекомендовал (я ее изменил буквально несколько дней назад) ставить 5.7 версию mysql. У меня есть данные, что довольно большая сеть пользуется этой версией несколько месяцев, так что противопоказаний к вакцинации 8й версией нет. Но, главное, что есть в 8й версии, это штука, которая позволяет обходить deadlock-и.

Что нам это дает?

1) mysql-процедуры nodeny становятся более понятными и простыми
2) все

Поэтому, если у вас все работает и вас не парит, что вы не понимаете детально как работает get_ip, то можете ничего не менять. Просто имейте ввиду, что я планирую поменять в будущем и в документации и вообще рекомендовать.

Уловили главное слово "планирую"? Да, пока я планирую ибо не тестил досконально - к сожалению, у меня нет сети уже несколько лет, поэтому такие важные вещи протестировать в бою сам лично не могу, за меня это делают другие админы, с которыми я взаимодейтсвую, спасибо им.

Но, если кто хочет попробовать (на самом деле я бы хотел чтоб кто-то попробовал и стал первопроходцем), то суть такая:
1) функция get_ip становится процедурой get_ip. Не получилось оставить функцию т.к. mysql-функции не умеют в транзакции
2) везде, где вызывается get_ip как

Код:

SELECT get_ip(usr_id) INTO usr_ip;

нужно заменить на:

Код:

CALL get_ip(usr_id, usr_ip);

Сама процедура get_ip:

Код:

DROP PROCEDURE IF EXISTS `get_ip`;
DELIMITER $$
CREATE PROCEDURE `get_ip` (IN user_id INTEGER UNSIGNED, OUT res_ip VARCHAR(15))
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip INTEGER;
    DECLARE row_cnt INTEGER;
    DECLARE ip_id INTEGER;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type='static' LIMIT 1;

    IF( user_ip IS NULL ) THEN
        SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';
        SELECT IF(ROW_COUNT()>0, 1, 0) INTO real_ip;

        SELECT id, INET_NTOA(ip) INTO ip_id, user_ip FROM ip_pool
            WHERE uid = user_id AND type = 'dynamic' AND realip = real_ip
            LIMIT 1;

        IF( ip_id IS NOT NULL) THEN
            UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
                WHERE id = ip_id AND uid = user_id AND `release` < UNIX_TIMESTAMP() + 3601;
        ELSE
            START TRANSACTION;
            SELECT id, INET_NTOA(ip) INTO ip_id, user_ip
                FROM ip_pool
                WHERE uid = 0
                    AND type = 'dynamic'
                    AND realip = real_ip
                    AND `release` < UNIX_TIMESTAMP()
                    ORDER BY id
                LIMIT 1 FOR UPDATE SKIP LOCKED;

            IF( user_ip IS NOT NULL) THEN
                UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
                    WHERE id = ip_id
                    AND uid = 0
                    AND type = 'dynamic'
                    AND realip = real_ip
                    AND `release` < UNIX_TIMESTAMP();
            END IF;
        COMMIT;
        END IF;
    END IF;
    SELECT user_ip INTO res_ip;
END$$
DELIMITER ;