UPSERT atómico en SQL Server 2005

¿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  -- race condition risk here IF @@ROWCOUNT = 0 INSERT ... 

o

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

donde 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  SELECT ,  FROM 
WHERE NOT EXISTS -- race condition risk here? ( SELECT 1 FROM
WHERE ) UPDATE ... WHERE

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?

 INSERT INTO 
SELECT , FROM
WHERE NOT EXISTS -- race condition risk here? ( SELECT 1 FROM
WHERE ) UPDATE ... WHERE
  • 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 lockings espurios. Los lockings 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 statement ‘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 objective de una operación de actualización o eliminación . Para tales casos, el plan de consulta utilizará un locking 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 locking 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 statement. 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.

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  with (updlock, rowlock, holdlock) where  -- insert else -- update commit

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

WITH (TABLOCKX, HOLDLOCK)

parece funcionar, pero no tengo idea si ese es el nivel óptimo de locking 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  SELECT ,  WHERE NOT EXISTS -- no race condition here ( SELECT 1 FROM 
WHERE ) IF @@ROWCOUNT = 0 BEGIN UPDATE ... WHERE END

Las sentencias únicas siempre se ejecutan dentro de una transacción, ya sea propia ( confirmación automática e implícita ) o junto con otras declaraciones ( explícita ).

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

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

Intereting Posts