Upserts concurrentes en SQL Server sin carreras de existencia

Un upsert parece sencillo: actualizar si existe la clave del negocio e insertar en caso contrario. Sin embargo, dos sesiones pueden observar a la vez la misma ausencia y decidir insertar. Con una restricción única, una recibe un error. Sin ella, ambas pueden tener éxito y romper la relación esperada.

Primero define qué significa actualizar un valor existente. Reemplazar un idioma preferido es distinto de incrementar un saldo o rechazar una edición basada en información antigua. Un upsert es una política de escritura, no solo una comodidad de sintaxis. Esa política debe guiar el patrón de bloqueo.

Proteger la clave del negocio

Esta tabla de práctica permite una preferencia por cliente y nombre. Créala únicamente en una base desechable.

-- Create only in a disposable practice database.
CREATE TABLE dbo.PreferenceDemo (
    CustomerId int NOT NULL,
    PreferenceKey nvarchar(50) NOT NULL,
    PreferenceValue nvarchar(200) NOT NULL,
    CONSTRAINT PK_PreferenceDemo PRIMARY KEY (CustomerId, PreferenceKey)
);

La clave única es la última barrera de integridad aunque todas las aplicaciones deban usar el procedimiento correcto. Incluye todas las dimensiones, como TenantId cuando los identificadores solo son únicos dentro de un inquilino. Para claves textuales, acuerda normalización y sensibilidad a mayúsculas; la intercalación determina qué cadenas se consideran iguales.

Un IF NOT EXISTS seguido de INSERT puede sufrir la carrera bajo read committed normal. Envolverlos en una transacción no protege necesariamente la clave ausente. La protección importante cubre el rango donde se insertaría.

El siguiente lote actualiza primero y conserva la protección de su búsqueda hasta finalizar.

DECLARE @CustomerId int = 42;
DECLARE @Key nvarchar(50) = N'language';
DECLARE @Value nvarchar(200) = N'en';
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50001, 'This batch owns its transaction.', 1;

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.PreferenceDemo WITH (UPDLOCK, HOLDLOCK)
    SET PreferenceValue = @Value
    WHERE CustomerId = @CustomerId AND PreferenceKey = @Key;

    IF @@ROWCOUNT = 0
        INSERT dbo.PreferenceDemo(CustomerId, PreferenceKey, PreferenceValue)
        VALUES (@CustomerId, @Key, @Value);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

HOLDLOCK aplica comportamiento serializable a esa referencia de tabla. UPDLOCK solicita bloqueos orientados a actualización. El índice único permite proteger la clave o su rango. No promete bloquear únicamente una fila física: el plan de acceso y las demás operaciones influyen en el alcance.

Comprueba @@ROWCOUNT inmediatamente después de UPDATE, sin instrucciones de registro entre ambos. Una fila coincidente cuenta aunque su valor ya sea igual al proporcionado; en ese caso no se intentará insertar otra.

Definir los conflictos aceptables

Dos solicitudes que asignan idiomas diferentes pueden ejecutarse una después de otra. La última asignación serializada completada determina el valor. Eso no garantiza que gane la última solicitud recibida por el servidor web ni detecta que alguien sobrescribió una edición ajena.

Si deben rechazarse ediciones antiguas, utiliza una rowversion esperada en el predicado de UPDATE e investiga la ausencia de coincidencia como conflicto. rowversion es un token de cambio, no una fecha. Crear y reemplazar condicionalmente pueden necesitar operaciones de API diferentes.

Repetir una asignación del mismo valor suele ser idempotente respecto al dato. Repetir "sumar diez" no lo es. Desencadenadores, auditoría y mensajes externos también pueden introducir efectos adicionales. Usa un identificador duradero de solicitud cuando la operación de negocio deba aplicarse una vez por petición.

MERGE no elimina la necesidad de estudiar unicidad, aislamiento y concurrencia. Comprueba el comportamiento para la carga y versión concretas, sin asumir que una sola instrucción hace segura toda la operación.

Probar con sesiones competidoras

Ejecuta el lote desde dos conexiones contra la misma tabla, incluyendo una clave inexistente. En una prueba controlada, pausa temporalmente una sesión después de UPDATE con la transacción abierta y observa la espera de la otra. Elimina esa pausa del código real.

Prueba además claves distintas, valores repetidos, errores de restricciones y pérdida de conexión cerca de COMMIT. El cliente puede desconocer si se confirmó. Un reintento indiscriminado puede duplicar efectos; resuélvelo mediante el identificador de solicitud o consultando el estado autoritativo.

Los bloqueos de rango pueden aumentar la contención y no eliminan interbloqueos. Accede a varias claves en un orden coherente y mantén transacciones breves. Reintenta la transacción completa de una víctima con espera limitada, no solo el INSERT final dentro de una transacción dañada. La corrección debe preservar tanto la unicidad como la política acordada de conflictos.

Referencias técnicas: Microsoft Learn: Table hints · Microsoft Learn: Transaction locking guide.