resueltos - prepared statement php pdo

¿Implementando una cola simple con PHP y MySQL?

Question

Tengo un script PHP que recupera filas de una base de datos y luego realiza un trabajo basado en el contenido. El trabajo puede llevar mucho tiempo (pero no es necesariamente costoso desde el punto de vista informático), por lo que debo permitir que se ejecuten varios scripts en paralelo.

Las filas en la base de datos se parecen a esto:

+---------------------+---------------+------+-----+---------------------+----------------+
| Field               | Type          | Null | Key | Default             | Extra          |
+---------------------+---------------+------+-----+---------------------+----------------+
| id                  | bigint(11)    | NO   | PRI | NULL                | auto_increment |
.....
| date_update_started | datetime      | NO   |     | 0000-00-00 00:00:00 |                |
| date_last_updated   | datetime      | NO   |     | 0000-00-00 00:00:00 |                |
+---------------------+---------------+------+-----+---------------------+----------------+

Mi script actualmente selecciona las filas con las fechas más antiguas en date_last_updated (que se actualiza una vez que se realiza el trabajo) y no hace uso de date_update_started .

Si tuviera que ejecutar varias instancias de la secuencia de comandos en paralelo en este momento, seleccionarían las mismas filas (al menos parte del tiempo) y se realizaría el trabajo duplicado.

Lo que estoy pensando hacer es usar una transacción para seleccionar las filas, actualizar la columna date_update_started y luego agregar una condición WHERE a la instrucción SQL seleccionando las filas para seleccionar solo las filas con date_update_started mayor que algún valor (para asegurar que haya otra secuencia de comandos No estoy trabajando en ello). P.ej

$sth = $dbh->prepare(''
    START TRANSACTION;
    SELECT * FROM table WHERE date_update_started > 1 DAY ORDER BY date_last_updated LIMIT 1000;
    UPDATE table DAY SET date_update_started = UTC_TIMESTAMP() WHERE id IN (SELECT id FROM table WHERE date_update_started > 1 DAY ORDER BY date_last_updated LIMIT 1000;);
    COMMIT;
'');
$sth->execute(); // in real code some values will be bound
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);

Por lo que he leído, esto es esencialmente una implementación de cola y parece estar mal visto en MySQL. De todos modos, tengo que encontrar una manera de permitir que se ejecuten varios scripts en paralelo, y después de la investigación que he hecho esto es lo que he encontrado.

¿Funcionará este tipo de enfoque? ¿Hay alguna manera mejor?

Answer 1

Cada vez que se ejecuta el script, el script genera un uniqid.

$sctiptInstance = uniqid();

Yo agregaría una columna de instancia de script para mantener este valor como varchar y ponerle un índice. Cuando se ejecuta la secuencia de comandos, usaría Seleccionar para actualizar dentro de una transacción para seleccionar las filas según la lógica, excluyendo las filas con una instancia de secuencia de comandos, y luego actualizar esas filas con la instancia de secuencia de comandos. Algo como:

START TRANSACTION;
SELECT * FROM table WHERE script_instance = '''' AND date_update_started > 1 DAY ORDER BY date_last_updated LIMIT 1000 FOR UPDATE;
UPDATE table SET date_update_started = UTC_TIMESTAMP(), script_instance = ''{$scriptInstance}'' WHERE script_instance = '''' AND date_update_started > 1 DAY ORDER BY date_last_updated LIMIT 1000;
COMMIT;

Ahora esas filas serán excluidas de otras instancias de la secuencia de comandos. ¿Trabaja y luego actualiza las filas para volver a establecer la instancia del script en nulo o en blanco y también actualizar la fecha de la última columna actualizada?

También puede usar la instancia de script para escribir en otra tabla llamada "instancias actuales" o algo así, y hacer que el script verifique esa tabla para obtener un conteo de scripts en ejecución para controlar el número de scripts concurrentes. También agregaría el PID del script a la tabla. Luego, podría usar esa información para crear un script de mantenimiento que se ejecute desde cron periódicamente para verificar procesos de larga ejecución o deshonestos y eliminarlos, etc.

Answer 2

Creo que su enfoque podría funcionar, siempre que también agregue algún tipo de identificador a las filas que seleccionó en las que se ha trabajado actualmente, podría ser como sugirió @JuniusRendel e incluso podría pensar en usar otra clave de cadena (al azar o Id. de instancia) para los casos en que el script generó errores y no se completó correctamente, ya que tendrá que limpiar estos campos una vez que haya actualizado las filas nuevamente después de su trabajo.

El problema con este enfoque, tal como lo veo, es la opción de que habrá 2 scripts que se ejecutarán en el mismo punto y seleccionarán las mismas filas antes de que se firmen como bloqueadas. aquí, como puedo verlo, realmente depende del tipo de trabajo que haga en las filas, si el resultado final en estos dos scripts será el mismo, creo que el único problema que tiene es el tiempo perdido y la memoria del servidor (que No son temas pequeños pero los voy a dejar de lado por ahora ...). Si su trabajo resultará en actualizaciones diferentes en ambos scripts, su problema será que podría tener una actualización incorrecta al final de la TB.

@ Jean ha mencionado el segundo enfoque que puede tomar que involucra el uso de los bloqueos MySql. No soy un experto en el tema, pero parece ser un buen enfoque y el uso de la declaración " Seleccionar ... PARA ACTUALIZAR " podría ofrecerle lo que está buscando, ya que podría hacer en la misma convocatoria, seleccionar y actualizar. lo que será más rápido que 2 consultas separadas y podría reducir el riesgo de que otras instancias seleccionen estas filas ya que se bloquearán.

El ''SELECCIONAR .... PARA ACTUALIZAR'' le permite ejecutar una declaración de selección y bloquear esas filas específicas para actualizarlas, por lo que su declaración podría verse así:

START TRANSACTION;
   SELECT * FROM tb where field=''value'' LIMIT 1000 FOR UPDATE;
   UPDATE tb SET lock_field=''1'' WHERE field=''value'' LIMIT 1000;
COMMIT;

Los bloqueos son potentes, pero tenga cuidado de que no afecte su aplicación en diferentes secciones. Verifique si las filas seleccionadas que están actualmente bloqueadas para la actualización, se soliciten en algún otro lugar de su aplicación (tal vez para el usuario final) y qué ocurrirá en ese caso.

Además, las Tablas deben ser InnoDB y se recomienda que los campos con los que está marcando la cláusula where tengan un índice Mysql, ya que de lo contrario puede bloquear toda la tabla o encontrar el '' Bloqueo de espacio ''.

También existe la posibilidad de que el proceso de bloqueo, y especialmente cuando se ejecutan scripts paralelos, sea pesado en su CPU y memoria.

Aquí hay otra lectura sobre el tema: http://www.percona.com/blog/2006/08/06/select-lock-in-share-mode-and-for-update/

Espero que esto ayude, y me gustaría escuchar cómo progresaste.

Answer 3

He usado un procedimiento almacenado por razones muy similares en el pasado. Usamos el bloqueo de lectura FOR UPDATE para bloquear la tabla mientras se actualizaba una marca seleccionada para eliminar esa entrada de cualquier selección futura. Parecía algo como esto:

CREATE PROCEDURE `select_and_lock`()
 BEGIN
  START TRANSACTION;
  SELECT your_fields FROM a_table WHERE some_stuff=something 
   AND selected = 0 FOR UPDATE;
  UPDATE a_table SET selected = 1;
  COMMIT;
 END$$

No hay ninguna razón para hacerlo en un procedimiento almacenado, aunque ahora lo pienso.

Answer 4

Tenemos algo como esto implementado en producción.

Para evitar duplicados, hacemos una ACTUALIZACIÓN de MySQL como esta (modifiqué la consulta para parecerme a su tabla):

UPDATE queue SET id = LAST_INSERT_ID(id), date_update_started = ... 
WHERE date_update_started IS NULL AND ...
LIMIT 1;

Realizamos esta ACTUALIZACIÓN en una sola transacción y aprovechamos la función LAST_INSERT_ID . Cuando se usa así, con un parámetro, escribe en la sesión de transacción el parámetro que, en este caso, es el ID de la cola única ( LIMIT 1 ) que se ha actualizado (si existe).

Justo después de eso, hacemos:

SELECT LAST_INSERT_ID();

Cuando se usa sin parámetro, recupera el valor previamente almacenado, obteniendo la ID del elemento de la cola que se debe realizar.

Answer 5

Tengo un sistema que funciona exactamente así en producción. Ejecutamos una secuencia de comandos cada minuto para hacer un procesamiento, y algunas veces esa ejecución puede llevar más de un minuto.

Tenemos una columna de tabla para el estado, que es 0 para NO FUNCIONAR AÚN TODO, 1 para FINALIZADO, y otro valor para en curso.

Lo primero que hace el script es actualizar la tabla, estableciendo una línea o varias líneas con un valor que significa que estamos trabajando en esa línea. Usamos getmypid() para actualizar las líneas en las que queremos trabajar y que aún están sin procesar.

Cuando finalizamos el procesamiento, el script actualiza las líneas que tienen la misma ID de proceso, marcándolas como finalizadas (estado 1).

De esta manera, evitamos que cada uno de los scripts intente procesar y procesar una línea que ya se está procesando, y funciona como un encanto. Esto no significa que no haya una mejor manera, pero sí hace el trabajo.

Answer 6

Edit : Lo siento, entendí totalmente tu pregunta

Simplemente debe poner una columna "bloqueada" en su tabla y poner el valor en verdadero en las entradas con las que trabaja su script, y cuando haya terminado, ponerlo en falso.

En mi caso, he puesto otras 3 columnas de marca de tiempo (entero): target_ts, start_ts, done_ts. Tú

UPDATE table SET locked = TRUE WHERE target_ts<=UNIX_TIMESTAMP() AND ISNULL(done_ts) AND ISNULL(start_ts);

y entonces

SELECT * FROM table WHERE target_ts<=UNIX_TIMESTAMP() AND ISNULL(start_ts) AND locked=TRUE;

Realice sus trabajos y actualice cada entrada una por una (para evitar inconsistencias en los datos) configurando la propiedad done_ts con la marca de tiempo actual (también puede desbloquearlas ahora). Puede actualizar target_ts a la próxima actualización que desee o puede ignorar esta columna y simplemente usar done_ts para su selección