Atomic UPDATE .. SELECT in Postgres

Sto costruendo un meccanismo di messa in coda di sorta. Vi sono righe di dati che necessitano di elaborazione e un flag di stato. Sto usando un update .. returning clausola di update .. returning per gestirlo:

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

La parte di selezione nidificata ha lo stesso blocco dell’aggiornamento o ho una condizione di competizione qui? In tal caso, la selezione interna deve essere una select for update ?

Mentre il suggerimento di Erwin è probabilmente il modo più semplice per ottenere un comportamento corretto (a condizione che riprovi la transazione se ottieni un’eccezione con SQLSTATE di 40001), le applicazioni di accodamento per loro natura tendono a funzionare meglio con il blocco delle richieste per avere la possibilità di prendere il loro turno in coda piuttosto che con l’implementazione PostgreSQL delle transazioni SERIALIZABLE , che consente una maggiore concorrenza ed è un po ‘più “ottimista” circa le probabilità di collisione.

La query di esempio nella domanda, così com’è, nel livello di isolamento della transazione READ COMMITTED predefinito consentirebbe due (o più) connessioni simultanee per “rivendicare” la stessa riga dalla coda. Quello che succederà è questo:

  • T1 inizia e arriva fino al blocco della riga nella fase UPDATE .
  • T2 si sovrappone a T1 nel tempo di esecuzione e tenta di aggiornare quella riga. Blocca in sospeso il COMMIT o il ROLLBACK di T1.
  • T1 si impegna, avendo “rivendicato” con successo la riga.
  • T2 prova ad aggiornare la riga, trova che T1 abbia già, cerca la nuova versione della riga, trova che soddisfa ancora i criteri di selezione (che è proprio quella corrispondenza id ), e anche “reclama” la riga.

Può essere modificato per funzionare correttamente (se si utilizza una versione di PostgreSQL che consente la clausola FOR UPDATE in una sottoquery). Basta aggiungere FOR UPDATE alla fine della subquery che seleziona l’id, e questo accadrà:

  • T1 inizia e ora blocca la riga prima di selezionare l’id.
  • T2 si sovrappone a T1 in tempo di esecuzione e blocchi durante il tentativo di selezionare un ID, in attesa di COMMIT o ROLLBACK di T1.
  • T1 si impegna, avendo “rivendicato” con successo la riga.
  • Quando T2 è in grado di leggere la riga per vedere l’id, vede che è stata rivendicata, quindi trova il prossimo ID disponibile.

Al livello di isolamento della transazione REPEATABLE READ o SERIALIZABLE , il conflitto di scrittura genera un errore, che è ansible rilevare e determinare un errore di serializzazione basato su SQLSTATE e riprovare.

Se in genere si desiderano transazioni SERIALIZZABILI ma si desidera evitare tentativi nell’area di accodamento, è ansible ottenerlo utilizzando un blocco di avviso .

Se sei l’ unico utente , la query dovrebbe andare bene. In particolare, non vi è alcuna condizione di competizione o deadlock all’interno della query stessa (tra la query esterna e la sottoquery). Cito il manuale qui :

Tuttavia, una transazione non è mai in conflitto con se stessa.

Per uso concorrente , la questione potrebbe essere più complicata. SERIALIZABLE al sicuro con la modalità di transazione SERIALIZABLE :

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

È necessario prepararsi per errori di serializzazione e riprovare la query in questo caso.

Ma non sono del tutto sicuro se questo non sia eccessivo. Chiederò a @kgrittn di fermarsi .. lui è l’ esperto con transazioni simultanee e serializzabili ..

E lo ha fatto. 🙂


Il meglio di entrambi i mondi

Esegui la query nella modalità di transazione predefinita READ COMMITTED .

Per Postgres 9.5 o versioni successive, utilizzare FOR UPDATE SKIP LOCKED . Vedere:

  • Postgres UPDATE … LIMIT 1

Per le versioni precedenti ricontrollare esplicitamente la condizione computed IS NULL nell’outDate esterno:

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

Come suggerito da @ kgrittn nel commento alla sua risposta, questa query potrebbe apparire vuota, senza aver fatto nulla, nel caso (improbabile) che si fosse intrecciata con una transazione concorrente.

Pertanto, funzionerebbe molto come la prima variante in modalità di transazione SERIALIZABLE , dovresti riprovare – solo senza la penalizzazione delle prestazioni.

L’unico problema: mentre il conflitto è molto improbabile perché la finestra di opportunità è così piccola, può accadere sotto carico pesante. Non si poteva dire con certezza se alla fine non ci fossero più file.

Se questo non ha importanza (come nel tuo caso), hai finito qui.
In tal caso, per essere assolutamente sicuro , avviare un’altra query con il blocco esplicito dopo aver ottenuto un risultato vuoto. Se questo risulta vuoto, hai finito. In caso contrario, continua.
In plpgsql potrebbe assomigliare a questo:

 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; 

Questo dovrebbe darti il ​​meglio di entrambi i mondi: prestazioni e affidabilità.