El problema

Estoy tratando de averiguar cómo configurar correctamente una transacción en una base de datos y tener en cuenta la latencia potencial.

La puesta en marcha

En mi ejemplo tengo una tabla deusers, keys, donde cada usuario puede tener múltiples claves, y unaconfig tabla que determina cuántas claves puede tener cada usuario.

Quiero ejecutar un procedimiento almacenado que:

Averigua si el usuario dado puede solicitar una clave.Obtener una clave disponible, no reclamada.intenta canjear la clave para el usuario dado.

El pseudocódigo para el procedimiento sería:

    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;

El problema que estoy encontrando es que cuando simulo un retraso después de un paso1, (mediante el usoSELECT SLEEP(<seconds>)), es posible que un usuario determinado canjee varias claves cuando solo tienen permisos para canjear una, ejecutando el procedimiento en varias sesiones antes de que el primer procedimiento finalice su suspensión (lo que, de nuevo, es simular un retraso)

Aquí está el código paralas mesas ylos procedimientos (nota: para el pequeño ejemplo no me molesté con los índices y las claves externas, pero obviamente las uso en el proyecto real).

Para ver mi problema, simplemente configure las tablas y los procedimientos en una base de datos, luego abra dos terminales mysql, y en la primera ejecución esto:

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

Y luego rápidamente (tienes 10 segundos) en la segunda ejecución de esto:

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

Ambas consultas volverán con éxitokey_claimed y UsuarioBob terminará con 4 teclas asignadas a él, aunque el valor máximo en la configuración se establece en 3 por usuario.

Las preguntas¿Cuál es la mejor manera de evitar problemas como este? Estoy tratando de usar una transacción pero siento que no va a ayudar específicamente con este problema, y ​​es posible que esté implementando este error.Me doy cuenta de que una forma posible de solucionar el problema sería simplemente encapsular todo en una consulta de actualización grande, pero preferiría evitar eso, ya que me gusta poder configurar procedimientos individuales, donde cada uno está destinado a hacer una sola tarea. tarea.La base de datos detrás de este ejemplo está diseñada para ser utilizada por muchos (miles) de usuarios concurrentes. Como tal, sería mejor si un usuario que intenta canjear un código no bloquee a todos los demás usuarios para que no lo canjeen. No puedo cambiar mi código para intentar canjear de nuevo si otro usuario ya reclamó una clave, pero no debería suceder que un usuario pueda canjear dos códigos cuando solo tenga permiso para obtener una.