Insertar solo una fila si aún no está allí
Siempre había usado algo similar a lo siguiente para lograrlo:
INSERT INTO TheTable
SELECT
@primaryKey,
@value1,
@value2
WHERE
NOT EXISTS
(SELECT
NULL
FROM
TheTable
WHERE
PrimaryKey = @primaryKey)
...pero una vez bajo carga, se produjo una violación de la clave principal. Esta es la única declaración que se inserta en esta tabla. Entonces, ¿significa esto que la afirmación anterior no es atómica?
El problema es que esto es casi imposible de recrear a voluntad.
Quizás podría cambiarlo a algo como lo siguiente:
INSERT INTO TheTable
WITH
(HOLDLOCK,
UPDLOCK,
ROWLOCK)
SELECT
@primaryKey,
@value1,
@value2
WHERE
NOT EXISTS
(SELECT
NULL
FROM
TheTable
WITH
(HOLDLOCK,
UPDLOCK,
ROWLOCK)
WHERE
PrimaryKey = @primaryKey)
Aunque tal vez esté usando las cerraduras incorrectas o usando demasiadas cerraduras o algo así.
He visto otras preguntas en stackoverflow.com donde las respuestas sugieren un "SI (SELECT COUNT(*)... INSERT", etc., pero siempre tuve la suposición (quizás incorrecta) de que una sola declaración SQL sería atómica.
¿Alguien tiene alguna idea?
¿Qué pasa con el patrón "IEDJ" ?
BEGIN TRY
INSERT etc
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2627
RAISERROR etc
END CATCH
En serio, esto es más rápido y más concurrente sin bloqueos, especialmente en volúmenes altos. ¿Qué pasa si se escala UPDLOCK y toda la tabla está bloqueada?
Lea la lección 4 :
Lección 4: Al desarrollar el proceso upsert antes de ajustar los índices, primero confié en que la
If Exists(Select…)línea se activaría para cualquier elemento y prohibiría duplicados. Nada. En poco tiempo hubo miles de duplicados porque el mismo elemento llegaba al upsert en el mismo milisegundo y ambas transacciones veían que no existía y realizaban la inserción. Después de muchas pruebas, la solución fue usar el índice único, detectar el error y volver a intentar permitir que la transacción vea la fila y realice una actualización en lugar de una inserción.
Agregué HOLDLOCK que no estaba presente originalmente. Ignore la versión sin esta pista.
En lo que a mí respecta, esto debería ser suficiente:
INSERT INTO TheTable
SELECT
@primaryKey,
@value1,
@value2
WHERE
NOT EXISTS
(SELECT 0
FROM TheTable WITH (UPDLOCK, HOLDLOCK)
WHERE PrimaryKey = @primaryKey)
Además, si realmente desea actualizar una fila si existe e insertarla si no existe, esta pregunta puede resultarle útil.
Para agregar un poco a la respuesta de @ gbn, tal vez "mejorarla". Para aquellos, como yo, que no saben qué hacer en el <> 2627escenario (y no, un vacío CATCHno es una opción). Encontré esta pequeña pepita en technet .
BEGIN TRY
INSERT etc
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2627
BEGIN
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (
@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
END
END CATCH
No sé si esta es la forma "oficial", pero puedes probar INSERTy recurrir a UPDATEella si falla.
