multithreading - ACTUALIZACIÓN ATÓMICA.. SELECCIONAR en Postgres

postgresql concurrency

Question

Estoy construyendo un mecanismo de hacer cola. Hay filas de datos que necesitan procesamiento y un indicador de estado. Estoy usando una update .. returning cláusula de update .. returning para administrarlo:

UPDATE stuff
SET computed = ''working''
WHERE id = (SELECT id from STUFF WHERE computed IS NULL LIMIT 1)
RETURNING * 

¿La parte de selección anidada es el mismo bloqueo que la actualización, o tengo una condición de carrera aquí? Si es así, ¿la selección interna necesita ser una select for update ?

Answer 1

Si bien la sugerencia de Erwin es posiblemente la forma más sencilla de obtener un comportamiento correcto (siempre que vuelva a intentar su transacción si obtiene una excepción con SQLSTATE de 40001), las aplicaciones de cola tienden a funcionar mejor con solicitudes que bloquean la oportunidad de tomar su turno en la cola que con la implementación de PostgreSQL de transacciones SERIALIZABLE , lo que permite una mayor concurrencia y es algo más "optimista" sobre las posibilidades de colisión.

La consulta de ejemplo en la pregunta, tal como está, en el nivel de aislamiento de transacción READ COMMITTED predeterminado permitiría dos (o más) conexiones simultáneas para "reclamar" la misma fila de la cola. Lo que sucederá es esto:

• T1 se inicia y llega a bloquear la fila en la fase de UPDATE .
• T2 se superpone a T1 en tiempo de ejecución e intenta actualizar esa fila. Bloquea en espera de COMMIT o ROLLBACK de T1.
• T1 se compromete, después de haber "reclamado" con éxito la fila.
• T2 intenta actualizar la fila, encuentra que T1 ya lo tiene, busca la nueva versión de la fila, descubre que todavía cumple los criterios de selección (que es exactamente lo que coincide con el id ) y también "reclama" la fila.

Se puede modificar para que funcione correctamente (si está utilizando una versión de PostgreSQL que permite la cláusula FOR UPDATE en una subconsulta). Simplemente agregue FOR UPDATE al final de la subconsulta que selecciona la identificación, y esto sucederá:

• T1 se inicia y ahora bloquea la fila antes de seleccionar el id.
• T2 se superpone a T1 en el tiempo de ejecución y bloques al intentar seleccionar un id., Pendiente de COMMIT o ROLLBACK de T1.
• T1 se compromete, después de haber "reclamado" con éxito la fila.
• En el momento en que T2 puede leer la fila para ver la identificación, ve que se ha reclamado, por lo que encuentra la siguiente identificación disponible.

En el nivel de aislamiento de transacciones REPEATABLE READ o SERIALIZABLE , el conflicto de escritura arrojaría un error, que podría detectar y determinar que era una falla de serialización basada en SQLSTATE, y volver a intentarlo.

Si generalmente desea transacciones SERIALIZABLES pero desea evitar reintentos en el área de espera, es posible que pueda lograr eso mediante el uso de un bloqueo de aviso .

Answer 2

Si usted es el único usuario , la consulta debería estar bien. En particular, no hay una condición de carrera o punto muerto dentro de la consulta en sí (entre la consulta externa y la subconsulta). Cito el manual here :

Sin embargo, una transacción nunca entra en conflicto consigo misma.

Para uso concurrente , el asunto puede ser más complicado. Usted estaría en el lado seguro con el modo de transacción SERIALIZABLE :

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE stuff
SET    computed = ''working''
WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
RETURNING * 
COMMIT;

Debe prepararse para las fallas de serialización y volver a intentar su consulta en tal caso.

Pero no estoy del todo seguro de si esto no es excesivo. Le pediré a @kgrittn que pase por aquí ... él es el experto con concurrencia y transacciones serializables ...

Y él hizo. :)

Lo mejor de ambos mundos

Ejecute la consulta en el modo de transacción predeterminado READ COMMITTED .

Para Postgres 9.5 o posterior use FOR UPDATE SKIP LOCKED . Ver:

• Postgres ACTUALIZACIÓN ... LÍMITE 1

Para versiones anteriores, vuelva a verificar la condición computed IS NULL explícitamente en la UPDATE externa:

UPDATE stuff
SET    computed = ''working''
WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
AND   computed IS NULL;

Como @kgrittn advirtió en el comentario de su respuesta, esta consulta podría aparecer vacía, sin haber hecho nada, en el (improbable) caso se entrelazó con una transacción simultánea.

Por lo tanto, funcionaría mucho como la primera variante en el modo de transacción SERIALIZABLE , tendría que volver a intentarlo, simplemente sin la penalización de rendimiento.

El único problema: mientras que el conflicto es muy poco probable porque la ventana de oportunidad es muy pequeña, puede suceder bajo una gran carga. No se puede decir con certeza si finalmente no quedan más filas.

Si eso no importa (como en su caso), ha terminado aquí.
Si lo hace, para estar absolutamente seguro , inicie una consulta más con bloqueo explícito después de obtener un resultado vacío. Si esto aparece vacío, has terminado. Si no, continúa.
En plpgsql podría verse así:

LOOP
   UPDATE stuff
   SET    computed = ''working''
   WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL
                LIMIT 1 FOR UPDATE SKIP LOCKED);  -- pg 9.5+
   -- WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
   -- AND    computed IS NULL; -- pg 9.4-

   CONTINUE WHEN FOUND;  -- continue outside loop, may be a nested loop

   UPDATE stuff
   SET    computed = ''working''
   WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL
                LIMIT 1 FOR UPDATE);

   EXIT WHEN NOT FOUND;  -- exit function (end)
END LOOP;

Eso debería darte lo mejor de ambos mundos: rendimiento y confiabilidad.