Actualizaciones concurrentes de Postgres en una consulta de suma acumulativa.
La respuesta a esta pregunta anterior demuestra un método para seleccionar filas hasta que se alcance un umbral acumulativo:
select t.*
from (
select t.*, sum(monto) over (order by fecha desc) as monto_acumulado from t
) t
where monto_acumulado - monto < 12
order by fecha desc;
Ahora supongamos que lo que quiero hacer es realizar una actualización en el resultado de esa consulta. Tal vez se vea algo como esto:
update t
set propietario=$OWNER
from (
select t.*, sum(monto) over (order by fecha desc) as monto_acumulado
from t
where propietario is null
) t
where t.monto_acumulado - t.monto < 12
order by fecha desc;
Ahora supongamos que se ejecutan muchas consultas simultáneamente. Espero que pueda haber una condición de carrera porque si la subconsulta se ejecuta al mismo tiempo, devolverá el mismo conjunto de resultados en el mismo orden a dos o más consultas externas, lo que significa que las consultas externas intentarán cambiar las mismas filas o el monto acumulado es incorrecto, por lo que seleccionará las filas incorrectas.
No estoy seguro de si FOR UPDATE SKIP LOCKED
ayuda aquí. Si lo uso en la consulta anterior, obtendré un error "FOR UPDATE is not allowed with window functions"
. Pero incluso si pudiera usarlo, usarlo en la subconsulta bloquearía cada fila, lo cual no es lo que quiero. Usarlo en la consulta externa creo que aplica el bloqueo en el lugar incorrecto.
Entonces pensé en recurrir a mentes más brillantes que la mía para ver si pueden ayudarme a señalarme en la dirección correcta:
- ¿Es correcta mi intuición de que la consulta anterior resultará en condiciones de carrera cuando se ejecute simultáneamente?
- ¿Existe un enfoque mejor y más seguro para este problema?
davy.ai
Como modelo de lenguaje de IA, no tengo una intuición propia, pero puedo decir que tu preocupación por las condiciones de carrera al ejecutar la consulta anterior de manera concurrente es válida. Debido a que las consultas concurrentes ejecutarán la misma subconsulta al mismo tiempo, puede llevar a resultados inconsistentes, incluyendo intentar actualizar las mismas filas o calcular cantidades de ejecución incorrectas debido a transacciones intercaladas.
Una forma de evitar la condición de carrera es utilizar una cláusula
WITH
para primero recuperar las filas específicas que deben ser actualizadas y sus cantidades de ejecución correspondientes, luego utilizar esa información en la segunda instrucciónUPDATE
. Este enfoque garantiza que la subconsulta se ejecute solo una vez y que las filas a actualizar estén bloqueadas antes de la actualización para evitar conflictos con transacciones concurrentes.Aquí tienes un ejemplo de cómo podría verse ese enfoque:
En este ejemplo, la expresión de tabla común (CTE)
rows_to_update
se utiliza para recuperar las filas específicas que deben actualizarse y sus cantidades de ejecución correspondientes. Ten en cuenta que se utiliza la cláusulaFOR UPDATE
para obtener bloqueos a nivel de fila en las filas seleccionadas y evitar conflictos con transacciones concurrentes.La segunda instrucción
UPDATE
une la tablat
con la CTErows_to_update
en la clave primariaid
. Esto garantiza que solo se actualicen las filas seleccionadas y que el cálculo de la cantidad de ejecución no se repita.Al utilizar una cláusula
WITH
y bloquear las filas seleccionadas, este enfoque proporciona una forma más segura y eficiente de actualizar filas basadas en un total acumulado evitando condiciones de carrera con transacciones concurrentes.