php - try - Transacción MySQL: SELECCIONE+INSERTAR

transacciones en mysql workbench

Question

Estoy construyendo una aplicación web - sistema de reserva usando php y mysql. El sistema permitirá a los usuarios hacer reservas de intervalos de tiempo en algunos dispositivos (el tiempo del usuario trabajando en ese dispositivo).

Yo llamo a estos intervalos de tiempo reservados ranuras. Las ranuras se almacenan en la tabla de la base de datos mysql de esta manera:

CREATE TABLE IF NOT EXISTS `slot` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`start` int(11) unsigned DEFAULT NULL,
`end` int(11) unsigned DEFAULT NULL,
`uid` int(11) unsigned DEFAULT NULL,
`group` int(11) unsigned DEFAULT NULL,
`message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`devices_id` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `start_2` (`start`),
UNIQUE KEY `end_2` (`end`),
KEY `index_foreignkey_slot_devices` (`devices_id`),
KEY `start` (`start`),
KEY `end` (`end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=6997 ; 

(Esta tabla es creada automáticamente por redbean orm y aún no la optimicé)

Entonces, cuando el usuario crea una reserva, se inserta una nueva fila en esta tabla. En las columnas inicio, fin, conservo las marcas de tiempo de unix del inicio y el final de cada reserva.

Otra cosa a tener en cuenta es que la aplicación permite a diferentes usuarios ver diferentes horarios del mismo dispositivo. Por ejemplo: el usuario A tiene intervalos de 6 minutos para que pueda ver la ranura libre (12:00 - 12:06) y también la ranura libre (12:06 - 12:12), pero el usuario B tiene intervalos de 4 minutos, por lo que también entre otros ve ranura (12:04 - 12:08). Cada usuario o grupo de usuarios puede tener diferentes duraciones de intervalo. Así que debo estar seguro de que cuando los usuarios A y B envían solicitudes con esas ranuras, solo una de ellas tiene éxito. Lo que me lleva a las transacciones y también a mi pregunta.

Lo hago así: - iniciar transacción - seleccionar todas las ranuras de ese día - ejecutar el algoritmo que comprueba las colisiones de tiempo entre las ranuras reservadas seleccionadas y las ranuras solicitadas - si no hay colisión, inserte nuevas filas en la tabla de ranuras, de lo contrario, error de señal para el usuario - cometer

Ahora sabes lo que puede suceder cuando se ejecuta al mismo tiempo. Soy nuevo en las transacciones y en mysql, pero he intentado probarlo y tengo motivos para creer que simplemente estar en una transacción no es suficiente en este caso, pero no estoy seguro.

Entonces mi pregunta es: ¿cómo puedo seleccionar, verificar colisiones y almacenar reservas en una transacción correctamente?

Gracias

Answer 1

Lo que necesitas es bloqueo . Las transacciones "no son estrictamente necesarias" de hecho.

Puede elegir entre "bloqueo pesimista" y "bloqueo optimista". La decisión sobre cuál de estas dos posibilidades depende de usted y debe evaluarse básicamente teniendo en cuenta:

• el nivel de concurrencia que tienes
• la duración de las operaciones de atomic en la base de datos
• la complejidad de toda la operación

Recomendaré leer estos dos para construir una idea de las cosas involucradas:

• Bloqueo optimista vs. pesimista
• Bloqueo optimista en MySQL (aquí algunos ejemplos que muestran cómo las transacciones no son estrictamente necesarias)

Un ejemplo para explicar mejor

Esto tal vez no es tan elegante, pero es solo un ejemplo que muestra cómo es posible hacer todo sin transacción (e incluso sin las restricciones ÚNICAS). Lo que se necesita hacer es usar el siguiente statemet combinado INSERT + SELECT y después de su ejecución para verificar el número de filas afectadas. Si el número de filas afectadas es 1, entonces ha tenido éxito de otra manera (si es 0) ha habido una colisión y la otra parte ha ganado.

INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
SELECT @startTime, @endTime, @uid, @group, @message, @deviceId
FROM `slot`
WHERE NOT EXISTS (
SELECT `id` FROM `slot`
WHERE `start` <= @endTime AND `end` >= @startTime
AND `devices_id` = @deviceId)
GROUP BY (1);

Este es un ejemplo de bloqueo optimista obtenido sin transacciones y con una sola operación SQL.

Como está escrito, tiene el problema de que debe haber al menos una fila en la tabla de slot para que funcione (de todos modos la cláusula SELECT siempre devolverá un conjunto de registros vacío y en ese caso no se insertará nada si no hay colisiones. Hay dos posibilidades para que funcione realmente:

• inserte una fila ficticia en la tabla tal vez con la fecha en el pasado

• vuelva a escribir para que la cláusula FROM principal se refiera a cualquier tabla que tenga al menos una fila o mejor, cree una pequeña tabla (quizás llamada dummy ) con solo una columna y solo un registro y reescriba de la siguiente manera (tenga en cuenta que ya no es necesario la cláusula GROUP BY)

  INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
  SELECT @startTime, @endTime, @uid, @group, @message, @deviceId
  FROM `dummy`
  WHERE NOT EXISTS (
  SELECT `id` FROM `slot`
  WHERE `start` <= @endTime AND `end` >= @startTime
  AND `devices_id` = @deviceId);

Aquí siguiendo una serie de instrucciones que si simplemente copie / pegue muestra la idea en acción. He supuesto que codifica la fecha / hora en campos int como un número con los dígitos de fecha y hora concatenados.

INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
VALUES (1008141200, 1008141210, 11, 2, ''Dummy Record'', 14)
INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
SELECT 1408141206, 1408141210, 11, 2, ''Hello'', 14
FROM `slot`
WHERE NOT EXISTS (
SELECT `id` FROM `slot`
WHERE `start` <= 1408141210 AND `end` >= 1408141206
AND `devices_id` = 14)
GROUP BY (1);
INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
SELECT 1408141208, 1408141214, 11, 2, ''Hello'', 14
FROM `slot`
WHERE NOT EXISTS (
SELECT `id` FROM `slot`
WHERE `start` <= 1408141214 AND `end` >= 1408141208
AND `devices_id` = 14)
GROUP BY (1);
INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
SELECT 1408141216, 1408141220, 11, 2, ''Hello'', 14
FROM `slot`
WHERE NOT EXISTS (
SELECT `id` FROM `slot`
WHERE `start` <= 1408141220 AND `end` >= 1408141216
AND `devices_id` = 14)
GROUP BY (1);
SELECT * FROM `slot`;

Este es claramente un ejemplo extremo de bloqueo optimista pero al final es muy eficiente porque todo se hace con solo una instrucción SQL y con baja interacción (intercambio de datos) entre el servidor de la base de datos y el código php. Además, prácticamente no hay bloqueo "real".

... o con bloqueo pesimista

El mismo código puede convertirse en una buena implementación de Pessimistc Locking que rodea las instrucciones explícitas de bloqueo / desbloqueo de tablas:

LOCK TABLE slot WRITE, dummy READ;
INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
SELECT @startTime, @endTime, @uid, @group, @message, @deviceId
FROM `dummy`
WHERE NOT EXISTS (
SELECT `id` FROM `slot`
WHERE `start` <= @endTime AND `end` >= @startTime
AND `devices_id` = @deviceId);
UNLOCK TABLES;

Por supuesto, en este caso (Bloqueo pesimista) el SELECT y el INSERT podrían separarse y algunos códigos php ejecutados en el medio. Sin embargo, este código sigue siendo muy rápido de ejecutar (sin intercambio de datos con php, sin código php intermedio) por lo que la duración del bloqueo pesimista es la más corta posible. Mantener el bloqueo pesimista lo más corto posible es un punto clave para evitar la ralentización de la aplicación.

De todos modos, debe verificar el número de valores devueltos de los registros afectados para saber si tuvieron éxito, ya que el código es prácticamente el mismo y para obtener la información de éxito / fracaso de la misma manera.

Aquí http://dev.mysql.com/doc/refman/5.0/en/insert-select.html dicen que "MySQL no permite inserciones concurrentes para las sentencias INSERT ... SELECT", por lo que no debería ser necesario el pesimista Bloquear, pero de todos modos, esta puede ser una buena opción si crees que esto cambiará en futuras versiones de MySQL.

Soy "Optimista" de que esto no cambie ;-)