usar - UPSERT atómico en SQL Server 2005

sql server using merge

Question

¿Cuál es el patrón correcto para realizar un "UPSERT" atómico (ACTUALIZAR si existe, INSERTAR de otro modo) en SQL Server 2005?

Veo una gran cantidad de código en SO (por ejemplo, consulte Comprobar si existe una fila, de lo contrario inserte ) con el siguiente patrón de dos partes:

UPDATE ...
FROM ...
WHERE <condition>
-- race condition risk here
IF @@ROWCOUNT = 0
INSERT ...

o

IF (SELECT COUNT(*) FROM ... WHERE <condition>) = 0
-- race condition risk here
INSERT ...
ELSE
UPDATE ...

donde <condición> será una evaluación de las claves naturales. Ninguno de los enfoques anteriores parece tratar bien con la concurrencia. Si no puedo tener dos filas con la misma clave natural, parece que todo el riesgo anterior inserta filas con las mismas claves naturales en los escenarios de condición de carrera.

He estado utilizando el siguiente enfoque, pero me sorprende no verlo en ninguna parte de las respuestas de las personas, así que me pregunto qué tiene de malo:

INSERT INTO <table>
SELECT <natural keys>, <other stuff...>
FROM <table>
WHERE NOT EXISTS
-- race condition risk here?
( SELECT 1 FROM <table> WHERE <natural keys> )
UPDATE ...
WHERE <natural keys>

Tenga en cuenta que la condición de carrera que se menciona aquí es diferente a las del código anterior. En el código anterior, el problema eran las lecturas fantasmas (filas que se insertan entre UPDATE / IF o entre SELECT / INSERT por otra sesión). En el código anterior, la condición de carrera tiene que ver con DELETE. ¿Es posible que una sesión coincidente sea eliminada por otra sesión DESPUÉS de que se ejecute (DONDE NO EXISTE) pero antes de que se ejecute INSERT? No está claro dónde DONDE NO EXISTE pone un candado en algo junto con la ACTUALIZACIÓN.

¿Es esto atómico? No puedo encontrar dónde se documentará esto en la documentación de SQL Server.

EDITAR: me doy cuenta de que esto podría hacerse con las transacciones, pero creo que tendría que establecer el nivel de transacción en SERIALIZABLE para evitar el problema de lectura fantasma. Seguramente eso es exagerado para un problema tan común?

Answer 1

Pase updlock, rowlock, holdlock al probar la existencia de la fila. Holdlock asegura que todas las inserciones estén serializadas; rowlock permite actualizaciones concurrentes a filas existentes.

Las actualizaciones aún pueden bloquearse si su PK es un bigint, ya que el hash interno está degenerado para los valores de 64 bits.

begin tran -- default read committed isolation level is fine
if not exists (select * from <table> with (updlock, rowlock, holdlock) where <PK = ...>
-- insert
else
-- update
commit

Answer 2

Puede usar bloqueos de aplicación: (sp_getapplock) http://msdn.microsoft.com/en-us/library/ms189823.aspx

Answer 3

Un truco que he visto es probar INSERTAR y, si falla, realizar la ACTUALIZACIÓN.

Answer 4

EDITAR : Remus es correcto, la cláusula condicional de inserción w / donde no garantiza un estado consistente entre la subconsulta correlacionada y la inserción de la tabla.

Quizás las sugerencias correctas de la tabla podrían forzar un estado consistente. INSERT <table> WITH (TABLOCKX, HOLDLOCK) parece funcionar, pero no tengo idea si ese es el nivel óptimo de bloqueo para una inserción condicional.

En una prueba trivial como la que describió Remus, TABLOCKX, HOLDLOCK mostró ~ 5 veces el volumen de inserción sin sugerencias de tabla, y sin los errores PK o el rumbo.

RESPUESTA ORIGINAL, INCORRECTO:

¿Es esto atómico?

Sí, la cláusula condicional de inserción w / where es atómica, y su formulario INSERT ... WHERE NOT EXISTS() ... UPDATE es la forma correcta de realizar un UPSERT.

IF @@ROWCOUNT = 0 entre INSERT y UPDATE:

INSERT INTO <table>
SELECT <natural keys>, <other stuff...>
WHERE NOT EXISTS
-- no race condition here
( SELECT 1 FROM <table> WHERE <natural keys> )
IF @@ROWCOUNT = 0 BEGIN
UPDATE ...
WHERE <natural keys>
END

Las sentencias únicas siempre se ejecutan dentro de una transacción, ya sea propia ( autocommit e implicit ) o junto con otras declaraciones ( explicit ).

Answer 5

INSERT INTO <table>
SELECT <natural keys>, <other stuff...>
FROM <table>
WHERE NOT EXISTS
-- race condition risk here?
( SELECT 1 FROM <table> WHERE <natural keys> )
UPDATE ...
WHERE <natural keys>

• hay una condición de carrera en el primer INSERTAR. Es posible que la clave no exista durante la consulta interna SELECT, pero existe al momento de INSERT, lo que da como resultado una violación de clave.
• hay una condición de carrera entre INSERT y UPDATE. La clave puede existir cuando está marcada en la consulta interna de INSERT, pero desaparece cuando se ejecuta ACTUALIZACIÓN.

Para la segunda condición de carrera se podría argumentar que la clave se habría eliminado de todos modos por el hilo concurrente, por lo que no es realmente una actualización perdida.

La solución óptima suele ser probar el caso más probable y manejar el error si falla (dentro de una transacción, por supuesto):

• si la clave es probable que falte, inserte siempre primero. Maneje la violación única de restricción, repliegue para actualizar.
• si la clave probablemente está presente, siempre actualice primero. Insertar si no se encontró ninguna fila. Manejar posible violación de restricción única, alternativa para actualizar.

Además de la corrección, este patrón también es óptimo para la velocidad: es más eficiente tratar de insertar y manejar la excepción que realizar bloqueos espurios. Los bloqueos significan que las lecturas lógicas de las páginas (que pueden significar lecturas físicas de las páginas) e IO (incluso lógicas) son más caras que las SEH.

Actualizar @ Peter

¿Por qué no hay una sola declaración ''atómica''? Digamos que tenemos una tabla trivial:

create table Test (id int primary key);

Ahora bien, si hubiera ejecutado este enunciado único a partir de dos hilos, en un bucle, sería ''atómico'', como dices, no puede existir una condición de carrera:

 insert into Test (id)
select top (1) id
from Numbers n
where not exists (select id from Test where id = n.id);

Sin embargo, en solo un par de segundos, ocurre una violación a la clave principal:

Msg 2627, nivel 14, estado 1, línea 4
Violación de la restricción PRIMARY KEY ''PK__Test__24927208''. No se puede insertar una clave duplicada en el objeto ''dbo.Test''.

¿Porqué es eso? Tiene razón en que el plan de consulta SQL hará lo ''correcto'' en DELETE ... FROM ... JOIN , on WITH cte AS (SELECT...FROM ) DELETE FROM cte y en muchos otros casos. Pero hay una diferencia crucial en estos casos: la ''subconsulta'' se refiere al objetivo de una operación de actualización o eliminación . Para tales casos, el plan de consulta utilizará un bloqueo apropiado, de hecho, este comportamiento es crítico en ciertos casos, como cuando se implementan colas. Usar tablas como Colas .

Pero en la pregunta original, así como en mi ejemplo, el optimizador de consultas ve la subconsulta solo como una subconsulta en una consulta, no como una consulta de tipo especial ''escanear para actualizar'' que necesita protección de bloqueo especial. El resultado es que la ejecución de la búsqueda de subconsulta puede ser observada como una operación distinta por un observador concurente , rompiendo así el comportamiento "atómico" de la declaración. A menos que se tome una precaución especial, varios hilos pueden intentar insertar el mismo valor, ambos convencidos de que se han verificado y el valor no existe. Solo uno puede tener éxito, el otro golpeará la violación PK. QED.