DELIMITER $$
CREATE PROCEDURE vpn_assign_nodes(IN node __NODENAME_T__, IN node_count INT)
BEGIN
    DROP TABLE IF EXISTS vpn_assign_tmp;
    DROP TABLE IF EXISTS vpn_row_tmp;

    CREATE TEMPORARY TABLE vpn_assign_tmp
        SELECT * FROM vpn_pool WHERE 0;
    CREATE TEMPORARY TABLE vpn_row_tmp
        SELECT
            vpn_pool.vpn_id, vpn_pool.vpn_type, vpn_pool.vpn_data,
            prov.semaphore_needed, prov.semaphore_count
            FROM vpn_pool JOIN vpn_provider_common AS prov
                ON vpn_pool.vpn_type = prov.vpn_type
            WHERE 0;

    SET @nodes_needed = node_count;
    SET @done = 0;
    WHILE (@nodes_needed > 0 AND @done != 1) DO

        START TRANSACTION;

        INSERT INTO vpn_row_tmp (
            SELECT
            vpn_pool.vpn_id, vpn_pool.vpn_type, vpn_pool.vpn_data,
            prov.semaphore_needed, prov.semaphore_count
        FROM vpn_pool JOIN vpn_provider_common AS prov
            ON vpn_pool.vpn_type = prov.vpn_type
        WHERE
            vpn_pool.vpn_node IS NULL
            AND (prov.semaphore_needed > 0 AND
                prov.semaphore_count < prov.semaphore_needed)
            OR prov.semaphore_needed = 0
        ORDER BY RAND()
        LIMIT 1
        FOR UPDATE);

        IF (ROW_COUNT() < 1) THEN
            SET @done = 1;
        ELSE
            SET @my_id := (SELECT vpn_id FROM vpn_row_tmp LIMIT 1);
            INSERT INTO vpn_assign_tmp (vpn_node, vpn_id, vpn_type, vpn_data)
                SELECT node, @my_id, vpn_type, vpn_data FROM vpn_row_tmp LIMIT 1;

            UPDATE vpn_pool
                JOIN vpn_provider_common prov
                    ON vpn_pool.vpn_type = prov.vpn_type
                SET vpn_pool.vpn_node = node,
                    vpn_pool.vpn_conn_id = CONNECTION_ID(),
                    prov.semaphore_count = prov.semaphore_count + 1
                WHERE vpn_id = @my_id;

            SET @nodes_needed = @nodes_needed - 1;
            DELETE FROM vpn_row_tmp;
        END IF;

        COMMIT;
    END WHILE;
    SELECT vpn_id, vpn_type, vpn_node, vpn_data FROM vpn_assign_tmp;
END $$