mysql – haciendo un mecanismo similar a las secuencias de Oracle

MySQL proporciona un mecanismo automático para incrementar los ID de registro. Esto está bien para muchos propósitos, pero necesito poder usar las secuencias que ofrece ORACLE. Obviamente, no tiene sentido crear una mesa para ese propósito.

La solución DEBERÍA ser simple:

1) Crea una tabla para los hosts de todas las secuencias necesarias,

2) Crea una función que aumenta el valor de una secuencia específica y devuelve el nuevo valor,

3) Crea una función que devuelve el valor actual de una secuencia.

En teoría, parece simple … PERO …

Al boost el valor de una secuencia (muy similar a nextval en Oracle), debe evitar que otras sesiones realicen esta operación (o incluso recuperar el valor actual) hasta que se complete la actualización.

Dos opciones teóricas:

a – Use una instrucción UPDATE que devolvería el nuevo valor en una sola toma, o

b – Bloquee la tabla entre UPDATE y SELECT.

Desafortunadamente, parece que MySQL no permite bloquear tablas dentro de funciones / procedimientos, y al intentar hacer todo en una sola statement (como ACTUALIZAR … DEVOLVER …) debes usar variables de tipo @ que sobrevivan la finalización de la función / procedimiento.

¿Alguien tiene una idea / solución de trabajo para esto?

Gracias.

    El siguiente es un ejemplo simple con un locking de intención FOR UPDATE. Un locking a nivel de fila con el motor INNODB. La muestra muestra cuatro filas para las próximas secuencias disponibles que no sufrirán la anómala INNODB Gap conocida (el caso en el que se producen vacíos después del uso fallido de AUTO_INCREMENT).

    Esquema:

     -- drop table if exists sequences; create table sequences ( id int auto_increment primary key, sectionType varchar(200) not null, nextSequence int not null, unique key(sectionType) ) ENGINE=InnoDB; -- truncate table sequences; insert sequences (sectionType,nextSequence) values ('Chassis',1),('Engine Block',1),('Brakes',1),('Carburetor',1); 

    Código de muestra:

     START TRANSACTION; -- Line1 SELECT nextSequence into @mine_to_use from sequences where sectionType='Carburetor' FOR UPDATE; -- Line2 select @mine_to_use; -- Line3 UPDATE sequences set nextSequence=nextSequence+1 where sectionType='Carburetor'; -- Line4 COMMIT; -- Line5 

    Lo ideal es que no tenga un código Line3 o bloaty, lo que retrasaría a otros clientes en espera de locking. Es decir, obtenga su próxima secuencia para usar, realice la actualización (la parte que se incrementa), y COMMIT , CUANTO ANTES .

    Lo anterior en un procedimiento almacenado:

     DROP PROCEDURE if exists getNextSequence; DELIMITER $$ CREATE PROCEDURE getNextSequence(p_sectionType varchar(200),OUT p_YoursToUse int) BEGIN -- for flexibility, return the sequence number as both an OUT parameter and a single row resultset START TRANSACTION; SELECT nextSequence into @mine_to_use from sequences where sectionType=p_sectionType FOR UPDATE; UPDATE sequences set nextSequence=nextSequence+1 where sectionType=p_sectionType; COMMIT; -- get it and release INTENTION LOCK ASAP set p_YoursToUse=@mine_to_use; -- set the OUT parameter select @mine_to_use as yourSeqNum; -- also return as a 1 column, 1 row resultset END$$ DELIMITER ; 

    Prueba:

     set @myNum:= -1; call getNextSequence('Carburetor',@myNum); +------------+ | yourSeqNum | +------------+ | 4 | +------------+ select @myNum; -- 4 

    Modifique el procedimiento almacenado según sus necesidades, como tener solo 1 de los 2 mecanismos para recuperar el número de secuencia (ya sea el parámetro OUT o el conjunto de resultados). En otras palabras, es fácil deshacerse del concepto de parámetro OUT .

    Si no se adhiere a la versión ASAP del LOCK (que obviamente no es necesaria después de la actualización), y procede a realizar un código de tiempo, antes del lanzamiento, puede ocurrir lo siguiente después de un período de tiempo de espera para otros clientes que esperan una secuencia número:

    ERROR 1205 (HY000): se excedió el tiempo de espera de espera de locking; intente reiniciar la transacción

    Espero que esto nunca sea un problema.

     show variables where variable_name='innodb_lock_wait_timeout'; 

    Página de manual de MySQL para innodb_lock_wait_timeout .

    En mi sistema en este momento tiene un valor de 50 (segundos). Una espera de más de un segundo o dos es probablemente insoportable en la mayoría de las situaciones.

    También de interés durante TRANSACTIONS es esa sección de la salida del siguiente comando:

     SHOW ENGINE INNODB STATUS;