sql - sistemas - race condition vulnerability

¿SELECCIONAR o INSERTAR en una función es propenso a las condiciones de carrera?

Question

Escribí una función para crear publicaciones para un simple motor de blogs:

CREATE FUNCTION CreatePost(VARCHAR, TEXT, VARCHAR[])
RETURNS INTEGER AS $$
DECLARE
InsertedPostId INTEGER;
TagName VARCHAR;
BEGIN
INSERT INTO Posts (Title, Body)
VALUES ($1, $2)
RETURNING Id INTO InsertedPostId;
FOREACH TagName IN ARRAY $3 LOOP
DECLARE
InsertedTagId INTEGER;
BEGIN
-- I am concerned about this part.
BEGIN
INSERT INTO Tags (Name)
VALUES (TagName)
RETURNING Id INTO InsertedTagId;
EXCEPTION WHEN UNIQUE_VIOLATION THEN
SELECT INTO InsertedTagId Id
FROM Tags
WHERE Name = TagName
FETCH FIRST ROW ONLY;
END;
INSERT INTO Taggings (PostId, TagId)
VALUES (InsertedPostId, InsertedTagId);
END;
END LOOP;
RETURN InsertedPostId;
END;
$$ LANGUAGE ''plpgsql'';

¿Es esto propenso a condiciones de carrera cuando múltiples usuarios eliminan etiquetas y crean publicaciones al mismo tiempo?
Específicamente, ¿las transacciones (y, por lo tanto, las funciones) evitan que ocurran tales condiciones de carrera?
Estoy usando PostgreSQL 9.2.3.

Answer 1

Creo que hay una pequeña posibilidad de que cuando la etiqueta ya existía pueda ser eliminada por otra transacción después de que su transacción la haya encontrado. Usar un SELECCIONAR PARA ACTUALIZAR debería resolver eso.

Answer 2

Es el problema recurrente de SELECT o INSERT bajo una posible carga de escritura simultánea, relacionada con (pero diferente de) UPSERT (que es INSERT o UPDATE ).

Para Postgres 9.5 o posterior

Usando la nueva implementación de UPSERT INSERT ... ON CONFLICT .. DO UPDATE , podemos simplificar en gran medida. Función PL / pgSQL para INSERT o SELECT una única fila (etiqueta):

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
BEGIN
SELECT tag_id -- only if row existed before
FROM tag
WHERE tag = _tag
INTO _tag_id;
IF NOT FOUND THEN
INSERT INTO tag AS t (tag)
VALUES (_tag)
ON CONFLICT (tag) DO NOTHING
RETURNING t.tag_id
INTO _tag_id;
END IF;
END
$func$ LANGUAGE plpgsql;

Todavía hay una pequeña ventana para una condición de carrera. Para estar absolutamente seguro de obtener una ID:

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
BEGIN
LOOP
SELECT tag_id
FROM tag
WHERE tag = _tag
INTO _tag_id;
EXIT WHEN FOUND;
INSERT INTO tag AS t (tag)
VALUES (_tag)
ON CONFLICT (tag) DO NOTHING
RETURNING t.tag_id
INTO _tag_id;
EXIT WHEN FOUND;
END LOOP;
END
$func$ LANGUAGE plpgsql;

Esto sigue dando vueltas hasta que INSERT o SELECT éxito. Llamada:

SELECT f_tag_id(''possibly_new_tag'');

Si los comandos posteriores en la misma transacción dependen de la existencia de la fila y es posible que otras transacciones se actualicen o eliminen al mismo tiempo, puede bloquear una fila existente en la instrucción SELECT con FOR SHARE .
Si la fila se inserta en su lugar, se bloquea hasta el final de la transacción de todos modos.

Si se inserta una nueva fila la mayor parte del tiempo, comience con INSERT para hacerlo más rápido.

Relacionado:

• Obtener Id de un INSERT condicionado
• Cómo incluir filas excluidas en RETORNO desde INSERTAR ... EN CONFLICTO

Solución relacionada (SQL puro) para INSERT o SELECT múltiples filas (un conjunto) a la vez:

• ¿Cómo se usa RETURNING con ON CONFLICT en PostgreSQL?

¿Qué pasa con esta solución SQL pura?

Anteriormente, también sugerí esta función de SQL:

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
WITH ins AS (
INSERT INTO tag AS t (tag)
VALUES (_tag)
ON CONFLICT (tag) DO NOTHING
RETURNING t.tag_id
)
SELECT tag_id FROM ins
UNION ALL
SELECT tag_id FROM tag WHERE tag = _tag
LIMIT 1
$func$ LANGUAGE sql;

Lo cual no está del todo mal, pero no logra sellar una laguna, como @FunctorSalad resolvió en su respuesta agregada . La función puede generar un resultado vacío si una transacción simultánea intenta hacer lo mismo al mismo tiempo. Todas las declaraciones en una consulta con CTE se ejecutan virtualmente al mismo tiempo. El manual:

Todas las declaraciones se ejecutan con la misma instantánea

Si una transacción simultánea inserta la misma nueva etiqueta un momento antes, pero no se ha confirmado, aún:

• La parte UPSERT aparece vacía, después de esperar a que finalice la transacción simultánea. (Si la transacción simultánea se retrotrae, aún inserta la nueva etiqueta y devuelve una nueva ID).

• La parte SELECT también aparece vacía, porque está basada en la misma instantánea, donde la nueva etiqueta de la transacción concurrente (aún no confirmada) no está visible.

No obtenemos nada . No como se pretendía Eso es contrario a la intuición de la lógica ingenua (y me atraparon allí), pero así es como funciona el modelo MVCC de Postgres: tiene que funcionar.

Por lo tanto, no use esto si varias transacciones pueden intentar insertar la misma etiqueta al mismo tiempo. O bucle hasta que realmente obtenga una fila. El bucle casi nunca se activará en cargas de trabajo comunes.

Respuesta original (Postgres 9.4 o anterior)

Dada esta tabla (ligeramente simplificada):

CREATE table tag (
tag_id serial PRIMARY KEY
, tag text UNIQUE
);

... una función prácticamente 100% segura para insertar una nueva etiqueta / seleccionar una existente, podría verse así.
¿Por qué no 100%? Considere las notas en el manual para el ejemplo UPSERT relacionado :

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT tag_id int) AS
$func$
BEGIN
LOOP
BEGIN
WITH sel AS (SELECT t.tag_id FROM tag t WHERE t.tag = _tag FOR SHARE)
, ins AS (INSERT INTO tag(tag)
SELECT _tag
WHERE NOT EXISTS (SELECT 1 FROM sel) -- only if not found
RETURNING tag.tag_id) -- qualified so no conflict with param
SELECT sel.tag_id FROM sel
UNION ALL
SELECT ins.tag_id FROM ins
INTO tag_id;
EXCEPTION WHEN UNIQUE_VIOLATION THEN -- insert in concurrent session?
RAISE NOTICE ''It actually happened!''; -- hardly ever happens
END;
EXIT WHEN tag_id IS NOT NULL; -- else keep looping
END LOOP;
END
$func$ LANGUAGE plpgsql;

SQL Fiddle.

Explicación

• Pruebe el SELECT primero . De esta forma evitará el manejo de excepciones considerablemente más caro el 99.99% del tiempo.

• Use un CTE para minimizar el (ya diminuto) intervalo de tiempo para la condición de carrera.

• La ventana de tiempo entre SELECT e INSERT dentro de una consulta es muy pequeña. Si no tiene mucha carga concurrente, o si puede vivir con una excepción una vez al año, puede ignorar el caso y usar la declaración SQL, que es más rápida.

• No es necesario FETCH FIRST ROW ONLY (= LIMIT 1 ). El nombre de la etiqueta es obviamente UNIQUE .

• Quite FOR SHARE en mi ejemplo si no suele tener DELETE o UPDATE simultáneos en la tag la tabla. Cuesta un poco de rendimiento.

• Nunca cite el nombre del idioma: ''plpgsql'' . plpgsql es un identificador . Citar puede causar problemas y solo se tolera por compatibilidad con versiones anteriores.

• No use nombres de columna no descriptivos como id o name . Cuando se une a un par de tablas ( que es lo que hace en un DB relacional) termina con múltiples nombres idénticos y tiene que usar alias.

Integrado en tu función

Usando esta función, podría simplificar en gran medida su FOREACH LOOP a:

...
FOREACH TagName IN ARRAY $3
LOOP
INSERT INTO taggings (PostId, TagId)
VALUES (InsertedPostId, f_tag_id(TagName));
END LOOP;
...

Más rápido, sin embargo, como una sola instrucción SQL con unnest() :

INSERT INTO taggings (PostId, TagId)
SELECT InsertedPostId, f_tag_id(tag)
FROM unnest($3) tag;

Reemplaza todo el ciclo.

Solución alternativa

Esta variante se basa en el comportamiento de UNION ALL con una cláusula LIMIT : tan pronto como se encuentran suficientes filas, el resto nunca se ejecuta:

• ¿Cómo probar múltiples SELECT hasta que haya un resultado disponible?

Sobre la base de esto, podemos externalizar el INSERT en una función separada. Solo allí necesitamos manejo de excepciones. Tan seguro como la primera solución.

CREATE OR REPLACE FUNCTION f_insert_tag(_tag text, OUT tag_id int)
RETURNS int AS
$func$
BEGIN
INSERT INTO tag(tag) VALUES (_tag) RETURNING tag.tag_id INTO tag_id;
EXCEPTION WHEN UNIQUE_VIOLATION THEN -- catch exception, NULL is returned
END
$func$ LANGUAGE plpgsql;

Que se usa en la función principal:

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
BEGIN
LOOP
SELECT tag_id FROM tag WHERE tag = _tag
UNION ALL
SELECT f_insert_tag(_tag) -- only executed if tag not found
LIMIT 1 -- not strictly necessary, just to be clear
INTO _tag_id;
EXIT WHEN _tag_id IS NOT NULL; -- else keep looping
END LOOP;
END
$func$ LANGUAGE plpgsql;

• Esto es un poco más barato si la mayoría de las llamadas solo necesitan SELECT , porque el bloque más costoso con INSERT contiene la cláusula EXCEPTION rara vez se ingresa. La consulta también es más simple.

• FOR SHARE no es posible aquí (no está permitido en la consulta UNION ).

• LIMIT 1 no sería necesario (probado en la página 9.4). Postgres deriva LIMIT 1 de INTO _tag_id y solo se ejecuta hasta que se encuentre la primera fila.

Answer 3

Todavía hay algo de qué preocuparse, incluso cuando se utiliza la cláusula ON CONFLICT introducida en Postgres 9.5. Usando la misma función y tabla de ejemplo que en la respuesta de @Erwin Brandstetter, si lo hacemos:

Session 1: begin;
Session 2: begin;
Session 1: select f_tag_id(''a'');
f_tag_id
----------
11
(1 row)
Session 2: select f_tag_id(''a'');
[Session 2 blocks]
Session 1: commit;
[Session 2 returns:]
f_tag_id
----------
NULL
(1 row)

Así que f_tag_id devolvió NULL en la sesión 2, lo que sería imposible en un mundo de subprocesos únicos.

Si elevamos el nivel de aislamiento de la transacción a repeatable read (o el serializable más fuerte), la sesión 2 arroja ERROR: could not serialize access due to concurrent update lugar. Así que no hay resultados "imposibles" al menos, pero lamentablemente ahora tenemos que estar preparados para volver a intentar la transacción.

Editar: con repeatable read o serializable , si la sesión 1 inserta la etiqueta a , la sesión 2 inserta b , luego la sesión 1 intenta insertar b y la sesión 2 intenta insertar a , una sesión detecta un punto muerto:

ERROR: deadlock detected
DETAIL: Process 14377 waits for ShareLock on transaction 1795501; blocked by process 14363.
Process 14363 waits for ShareLock on transaction 1795503; blocked by process 14377.
HINT: See server log for query details.
CONTEXT: while inserting index tuple (0,3) in relation "tag"
SQL function "f_tag_id" statement 1

Después de que la sesión que recibió el error de interbloqueo retrocede, la otra sesión continúa. ¿Entonces supongo que deberíamos tratar un punto muerto como serialization_failure y volver a intentarlo en una situación como esta?

Alternativamente, inserte las etiquetas en un orden consistente, pero esto no es fácil si no todas se agregan en un solo lugar.