tipos - Seleccionar fila desbloqueada en Postgresql

tipos de bloqueos en postgresql

Question

¿Hay alguna forma de seleccionar filas en Postgresql que no están bloqueadas? Tengo una aplicación multiproceso que hará:

Select... order by id desc limit 1 for update

en una mesa.

Si varios subprocesos ejecutan esta consulta, ambos intentan retrotraer la misma fila.

Uno obtiene el bloqueo de fila, los otros bloques y luego falla después de que el primero actualiza la fila. Lo que realmente me gustaría es que el segundo subproceso obtenga la primera fila que coincida con la cláusula WHERE y que no esté bloqueado.

Para aclarar, quiero que cada hilo actualice inmediatamente la primera fila disponible después de hacer la selección.

Entonces, si hay filas con ID: 1,2,3,4 , el primer subproceso vendrá, seleccione la fila con ID=4 y actualícela inmediatamente.

Si durante esa transacción llega un segundo hilo, me gustaría que fuera una fila con ID=3 e inmediatamente actualice esa fila.

Para Share no se logrará esto ni con nowait ya que la cláusula WHERE coincidirá con la fila bloqueada (ID=4 in my example) . Básicamente lo que me gustaría es algo como "Y NO BLOQUEADO" en la cláusula WHERE .

Users

-----------------------------------------
ID        | Name       |      flags
-----------------------------------------
1         |  bob       |        0
2         |  fred      |        1
3         |  tom       |        0
4         |  ed        |        0

Si la consulta es " Select ID from users where flags = 0 order by ID desc limit 1 " y cuando se devuelve una fila, lo siguiente es " Update Users set flags = 1 where ID = 0 " y luego me gustaría el primer hilo para agarrar la fila con ID 4 y la siguiente para agarrar la fila con ID 3 .

Si agrego " For Update " a la selección, el primer hilo obtiene la fila, el segundo bloquea y luego no devuelve nada porque una vez que la primera transacción confirma que la cláusula WHERE ya no está satisfecha.

Si no uso " For Update ", entonces necesito agregar una cláusula WHERE en la actualización posterior (WHERE flags = 0) para que solo un hilo pueda actualizar la fila.

El segundo hilo seleccionará la misma fila que el primero, pero la actualización del segundo hilo fallará.

De cualquier forma, el segundo hilo falla al obtener una fila y actualizarse porque no puedo hacer que la base de datos dé la fila 4 al primer hilo y la fila 3 al segundo hilo, las transacciones se superponen.

Answer 1

^^ eso funciona. Considere tener un estado "inmediato" de "bloqueado".

Digamos que su mesa es así:

id | nombre | apellido | estado

Y los posibles estados, por ejemplo, son: 1 = pendiente, 2 = bloqueado, 3 = procesado, 4 = error, 5 = rechazado

Cada nuevo registro se inserta con el estado pendiente (1)

Tu programa lo hace: "actualiza el estado de conjunto de mitades = 2 donde id = (selecciona id de mytable donde el nombre es ''% John%'' y estado = 1 límite 1) devuelve id, nombre, apellido"

Luego, su programa hace lo suyo y, si llega a la conclusión de que este subproceso no debería haber procesado esa fila en absoluto, lo hace: "update mytable set status = 1 where id =?"

Además, se actualiza a los otros estados.

Answer 2

¿Qué está tratando de lograr? ¿Puede explicar mejor por qué ni las actualizaciones de fila desbloqueadas ni las transacciones completas harán lo que usted quiere?

Mejor aún, ¿puede evitar la contención y simplemente hacer que cada hilo use un desplazamiento diferente? Esto no funcionará bien si la parte relevante de la tabla se actualiza con frecuencia; aún tendrá colisiones, pero solo durante la carga de inserción pesada.

Select... order by id desc offset THREAD_NUMBER limit 1 for update

Answer 3

Como todavía no he encontrado una mejor respuesta, he decidido usar el bloqueo dentro de mi aplicación para sincronizar el acceso al código que realiza esta consulta.

Answer 4

¿Qué tal lo siguiente? Puede tratarse de forma más atómica que los otros ejemplos, pero aún debe probarse para asegurarse de que mis suposiciones no sean erróneas.

UPDATE users SET flags = 1 WHERE id = ( SELECT id FROM users WHERE flags = 0 ORDER BY id DESC LIMIT 1 ) RETURNING ...;

Probablemente aún estés atascado con cualquier esquema de bloqueo que postgres use internamente para suministrar resultados consistentes de SELECT frente a ACTUALIZACIONES simultáneas.

Answer 5

Parece que estás intentando hacer algo como tomar el elemento de mayor prioridad en una cola que ya no está siendo resuelta por otro proceso.

Una solución probable es agregar una cláusula where que lo limite a solicitudes no controladas:

select * from queue where flag=0 order by id desc for update;
update queue set flag=1 where id=:id;
--if you really want the lock:
select * from queue where id=:id for update;
...

Afortunadamente, la segunda transacción se bloqueará mientras se realice la actualización de la bandera, luego podrá continuar, pero la bandera la limitará a la siguiente línea.

También es probable que al usar el nivel de aislamiento serializable, pueda obtener el resultado que desea sin toda esta locura.

Dependiendo de la naturaleza de su aplicación, puede haber mejores formas de implementar esto que en la base de datos, como una tubería FIFO o LIFO. Además, es posible invertir el orden en que los necesita y utilizar una secuencia para garantizar que se procesen de forma secuencial.

Answer 6

Enfrenté el mismo problema en nuestra aplicación y se me ocurrió una solución que es muy similar al enfoque de Grant Johnson. Una tubería FIFO o LIFO no era una opción porque tenemos un clúster de servidores de aplicaciones que acceden a una base de datos. Lo que hacemos es un

SELECT ... WHERE FLAG=0 ... FOR UPDATE seguido inmediatamente por un

UPDATE ... SET FLAG=1 WHERE ID=:id tan pronto como sea posible para mantener el tiempo de bloqueo lo más bajo posible. Según el recuento y los tamaños de las columnas de la tabla, puede ser útil buscar el ID en la primera selección y una vez que haya marcado la fila para recuperar los datos restantes. Un procedimiento almacenado puede reducir aún más la cantidad de viajes de ida y vuelta.

Answer 7

Si está intentando implementar una cola, eche un vistazo a PGQ, que ya ha resuelto este y otros problemas. http://wiki.postgresql.org/wiki/PGQ_Tutorial

Answer 8

Yo uso algo como esto:

select  *
into l_sms
from sms
where prefix_id = l_prefix_id
    and invoice_id is null
    and pg_try_advisory_lock(sms_id)
order by suffix
limit 1;

y no te olvides de llamar a pg_advisory_unlock

Answer 9

Mi solución es usar la instrucción UPDATE con la cláusula RETURNING.

Users

-----------------------------------
ID        | Name       |      flags
-----------------------------------
1         |  bob       |        0  
2         |  fred      |        1  
3         |  tom       |        0   
4         |  ed        |        0   

En lugar de SELECT .. FOR UPDATE uso

BEGIN; 

UPDATE "Users"
SET ...
WHERE ...;
RETURNING ( column list );

COMMIT;

Como la instrucción UPDATE obtiene un bloqueo ROW EXCLUSIVE en la tabla, su actualización le permite obtener actualizaciones serializadas. Las lecturas todavía están permitidas, pero solo ven datos antes del inicio de la transacción ACTUALIZACIÓN.

Referencia: capítulo de Control de Concurrencia de documentos Pg.

Answer 10

No No NOOO :-)

Sé lo que el autor quiere decir. Tengo una situación similar y se me ocurrió una buena solución. Primero comenzaré describiendo mi situación. Tengo una tabla donde guardo los mensajes que deben enviarse en un momento específico. PG no admite la ejecución de tiempo de las funciones, así que tenemos que usar daemons (o cron). Uso un script escrito personalizado que abre varios procesos paralelos. Cada proceso selecciona un conjunto de mensajes que deben enviarse con la precisión de +1 seg / -1 seg. La tabla en sí se actualiza dinámicamente con nuevos mensajes.

Entonces, cada proceso necesita descargar un conjunto de filas. Este conjunto de filas no puede ser descargado por el otro proceso porque causará mucho desorden (algunas personas recibirían un par de mensajes cuando deberían recibir solo uno). Es por eso que tenemos que bloquear las filas. La consulta para descargar un conjunto de mensajes con el candado:

FOR messages in select * from public.messages where sendTime >= CURRENT_TIMESTAMP - ''1 SECOND''::INTERVAL AND sendTime <= CURRENT_TIMESTAMP + ''1 SECOND''::INTERVAL AND sent is FALSE FOR UPDATE LOOP
-- DO SMTH
END LOOP;

un proceso con esta consulta se inicia cada 0.5 segundos. Por lo tanto, esto dará lugar a que la próxima consulta esté esperando el primer bloqueo para desbloquear las filas. Este enfoque crea enormes retrasos. Incluso cuando usamos NOWAIT, la consulta dará como resultado una excepción que no queremos porque puede haber nuevos mensajes en la tabla que se deben enviar. Si lo usa simplemente para COMPARTIR, la consulta se ejecutará correctamente, pero aún llevará mucho tiempo crear enormes retrasos.

Para que funcione, hacemos un poco de magia:

1. cambiando la consulta:

   FOR messages in select * from public.messages where sendTime >= CURRENT_TIMESTAMP - ''1 SECOND''::INTERVAL AND sendTime <= CURRENT_TIMESTAMP + ''1 SECOND''::INTERVAL AND sent is FALSE AND is_locked(msg_id) IS FALSE FOR SHARE LOOP
   -- DO SMTH
   END LOOP;

2. la misteriosa función ''is_locked (msg_id)'' tiene el siguiente aspecto:

   CREATE OR REPLACE FUNCTION is_locked(integer) RETURNS BOOLEAN AS $$
   DECLARE
       id integer;
       checkout_id integer;
       is_it boolean;
   BEGIN
       checkout_id := $1;
       is_it := FALSE;
   
       BEGIN
           -- we use FOR UPDATE to attempt a lock and NOWAIT to get the error immediately 
           id := msg_id FROM public.messages WHERE msg_id = checkout_id FOR UPDATE NOWAIT;
           EXCEPTION
               WHEN lock_not_available THEN
                   is_it := TRUE;
       END;
   
       RETURN is_it;
   
   END;
   $$ LANGUAGE ''plpgsql'' VOLATILE COST 100;

Por supuesto, podemos personalizar esta función para que funcione en cualquier tabla que tenga en su base de datos. En mi opinión, es mejor crear una función de verificación para una tabla. Agregar más cosas a esta función puede hacer que sea más lento. De todos modos, me toma más tiempo verificar esta cláusula, así que no hay necesidad de hacerlo aún más lento. Para mí esta es la solución completa y funciona perfectamente.

Ahora, cuando tengo mis 50 procesos ejecutándose en paralelo, cada proceso tiene un conjunto único de mensajes nuevos para enviar. Una vez que se envían, simplemente actualizo la fila con enviado = VERDADERO y nunca vuelvo a él.

Espero que esta solución también funcione para usted (autor). Si tienes alguna pregunta solo házmelo saber :-)

Ah, y dime si esto funcionó para ti como ... bueno.

Answer 11

¿Se usa en multihebra y clúster?
¿Qué tal esto?

START TRANSACTION;

// All thread retrive same task list
// If result count is very big, using cursor 
//    or callback interface provied by ORM frameworks.
var ids = SELECT id FROM tableName WHERE k1=v1;

// Each thread get an unlocked recored to process.
for ( id in ids ) {
   var rec = SELECT ... FROM tableName WHERE id =#id# FOR UPDATE NOWAIT;
   if ( rec != null ) {
    ... // do something
   }
}

COMMIT;

Answer 12

Esta función, SELECT ... SKIP LOCKED se está implementando en Postgres 9.5. http://www.depesz.com/2014/10/10/waiting-for-9-5-implement-skip-locked-for-row-level-locks/

Answer 13

Esto se puede lograr con SELECT ... NOWAIT; un ejemplo está aquí .

Answer 14

Parece que estás buscando un SELECCIONAR PARA COMPARTIR.

http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE

FOR SHARE se comporta de manera similar, excepto que adquiere un bloqueo compartido en lugar de exclusivo en cada fila recuperada. Un bloqueo compartido bloquea otras transacciones de ACTUALIZAR, ELIMINAR o SELECCIONAR PARA ACTUALIZAR en estas filas, pero no les impide realizar SELECCIONAR PARA COMPARTIR.

Si se nombran tablas específicas en FOR UPDATE o FOR SHARE, solo se bloquean las filas que provienen de esas tablas; cualquier otra tabla utilizada en SELECT se lee simplemente como de costumbre. Una cláusula FOR UPDATE o FOR SHARE sin una lista de tablas afecta a todas las tablas utilizadas en el comando. Si FOR UPDATE o FOR SHARE se aplica a una vista o subconsulta, afecta a todas las tablas utilizadas en la vista o sub consulta.

Se pueden escribir múltiples cláusulas FOR UPDATE y FOR SHARE si es necesario especificar un comportamiento de bloqueo diferente para tablas diferentes. Si la misma tabla se menciona (o se ve implícitamente afectada) por las cláusulas FOR UPDATE y FOR SHARE, entonces se procesa como FOR UPDATE. De forma similar, una tabla se procesa como NOWAIT si se especifica en alguna de las cláusulas que la afectan.

FOR UPDATE y FOR SHARE no pueden utilizarse en contextos en los que las filas devueltas no se pueden identificar claramente con filas de tablas individuales; por ejemplo, no se pueden usar con la agregación.