mysql-haciendo un mecanismo similar a las secuencias de Oracle

sequences

Question

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

La solución DEBE ser simple:

1) Crear una tabla para alojar todas las secuencias necesarias,

2) Cree una función que aumente el valor de una secuencia específica y devuelva el nuevo valor,

3) Cree una función que devuelva el valor actual de una secuencia.

En teoría, parece simple ... PERO ...

Al aumentar el valor de una secuencia (muy similar a nextval en Oracle), debe evitar que otras sesiones realicen esta operación (o incluso obtener 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 mesa entre UPDATE y SELECT.

Desafortunadamente, parece que MySQL no permite bloquear tablas dentro de funciones / procedimientos, y al intentar hacer todo en una sola declaración (como ACTUALIZAR ... VOLVER ...) debe usar variables de tipo @ que sobreviven La finalización de la función / procedimiento.

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

Gracias.

Answer 1

El siguiente es un ejemplo simple con un bloqueo de intención FOR UPDATE. Un bloqueo de nivel de fila con el motor INNODB. El ejemplo muestra cuatro filas para las siguientes secuencias disponibles que no sufrirán la conocida anomalía de brecha INNODB (el caso en que se producen brechas después del uso fallido de un AUTO_INCREMENTO).

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

Idealmente, no tiene un código Line3 o bloaty en absoluto, lo que retrasaría a otros clientes en una espera de bloqueo. 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 del parámetro OUT .

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

ERROR 1205 (HY000): Tiempo de espera de bloqueo excedido; intente reiniciar la transacción

Esperemos que esto nunca sea un problema.

show variables where variable_name=''innodb_lock_wait_timeout'';

Página del 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;