Prevenir el Write Skew mediante Aislamiento de Transacciones

Enunciado y Contexto Aplicable

La tabla doctor_shifts(team_id, shift_date, doctor_id, on_call) almacena el estado de guardia. La regla de negocio establece que cada equipo y turno debe conservar al menos 1 médico de guardia. Tanto Alice como Bob están activos, y dos solicitudes intentan simultáneamente retirar a diferentes médicos de la guardia. Cada transacción primero cuenta los médicos activos. Si el conteo es mayor que 1, actualiza la fila de su propio médico.

Esta pregunta está delimitada para PostgreSQL 18. El conteo inicial de activos es 2. Ambas transacciones terminan sus lecturas antes de que cualquiera de ellas haga commit, y luego actualizan filas diferentes. Los números y el esquema son suposiciones para la entrevista, no un modelo de datos universal para la atención médica. El problema central es el predicado multifila count(on_call) >= 1; los flujos de trabajo de aprobación, las zonas horarias y la autorización quedan fuera de alcance.

El mismo patrón aparece en reglas como que el inventario nunca sea negativo, que una cuenta conserve al menos un aprobador o que un clúster conserve al menos un nodo primario. Decir “ponlo en una transacción” es incompleto. La atomicidad hace que una transacción tenga éxito o falle como una unidad. El nivel de aislamiento determina qué pueden observar las transacciones concurrentes y si la base de datos rechaza un resultado que no tiene una explicación serial válida.

Qué Evalúa el Entrevistador

La primera señal es reconocer el write skew (desviación de escritura). Ambas transacciones leen el mismo predicado pero escriben en filas diferentes, por lo que no generan un conflicto de escritura estándar sobre la misma fila. Cada transacción pasa su verificación de manera aislada, pero su resultado combinado deja cero médicos activos. Llamar a esto una lectura sucia (dirty read) o una simple actualización perdida (lost update) conduce a una solución incorrecta.

La segunda señal es separar los nombres estándar de las implementaciones en las bases de datos. En PostgreSQL, Read Committed toma una nueva instantánea (snapshot) para cada sentencia. Repeatable Read utiliza una instantánea de transacción estable y está implementado como snapshot isolation, lo que aún permite anomalías de serialización. Serializable rastrea dependencias de lectura/escritura sobre lecturas similares basadas en instantáneas y aborta una transacción cuando el resultado no puede coincidir con ningún orden serial. Un nivel de aislamiento con el mismo nombre puede utilizar una semántica de bloqueos e instantáneas diferente en otra base de datos.

La tercera señal es elegir un límite de concurrencia a partir del invariante. Cuando el invariante abarca varias filas, bloquear “el médico que estoy a punto de actualizar” no hace que las dos transacciones colisionen. Un diseño seguro debe hacer que compitan por un único objeto bloqueable, reducir la regla a una condición atómica en una sola fila o permitir que Serializable detecte el conflicto.

Por último, el entrevistador evalúa el manejo de fallas. Tanto Serializable como el bloqueo explícito pueden abortar transacciones. El código de error de serialización común en PostgreSQL es 40001; un orden de bloqueo inconsistente también puede producir 40P01. Una respuesta sólida reintenta la transacción completa, limita la cantidad de intentos y traslada los correos electrónicos u otros efectos externos a una ruta idempotente posterior al commit exitoso.

Preguntas para Aclarar Antes de Responder

• ¿Qué base de datos y versión se están utilizando? Esta respuesta está orientada a PostgreSQL 18. MySQL, SQL Server y las bases de datos distribuidas requieren una revisión específica de sus niveles de aislamiento homónimos y errores de reintento.
• ¿La regla cubre exactamente un turno? Un invariante identificado por (team_id, shift_date) puede tener un punto de contención estable por turno. Una regla que abarque regiones o bases de datos no se puede proteger con un bloqueo de fila en una sola base de datos.
• ¿Qué rutas pueden cambiar el estado de guardia? Las solicitudes de permisos, intercambios, importaciones y reparaciones administrativas deben seguir el mismo protocolo. Corregir una sola API deja escrituras paralelas que pueden corromper un contador o eludir un bloqueo.
• ¿Los conflictos son ocasionales o continuamente frecuentes? Serializable con reintentos suele ser más claro con baja contención. Una actualización condicional en una sola fila puede evitar trabajo desperdiciado en un turno con alta demanda, pero también convierte a ese turno en un cuello de botella serializado.
• ¿Cuánto tiempo puede esperar una solicitud? Los bloqueos pesimistas esperan al titular del bloqueo. Un presupuesto estricto de latencia exige transacciones cortas, un límite de espera de bloqueo y un resultado explícito de “el estado cambió, reintente”.
• ¿La transacción realiza efectos secundarios externos? Un reintento vuelve a ejecutar la lógica de la transacción. El envío de correos electrónicos, las llamadas al servicio de guardias o los mensajes no transaccionales deben ocurrir después del commit o utilizar una bandeja de salida transaccional (transactional outbox) con consumo idempotente.

Estructura de Respuesta de 30 Segundos

“Esto es write skew: ambas transacciones toman decisiones a partir del mismo conjunto de guardia pero actualizan filas de médicos diferentes, por lo que incluso una instantánea estable de Repeatable Read puede permitir que ambas hagan commit. PostgreSQL Serializable rastrea esa dependencia de lectura/escritura y aborta al menos una transacción; la aplicación debe volver a ejecutar la decisión completa ante un 40001. Sin Serializable, mapearía cada turno a una fila del cronograma (roster), decrementaría atómicamente bajo Read Committed solo cuando active_count > 1, y actualizaría al médico en la misma transacción. Otra opción es bloquear la fila del cronograma antes de volver a contar. Probaría esto con dos conexiones y una barrera tras ambas lecturas, demostrando que un aislamiento débil llega a cero mientras que el diseño seguro retiene exactamente un médico.”

Respuesta Detallada Paso a Paso

Comencemos con la secuencia de ejecución concurrente. Inicialmente, Alice=true, Bob=true:

T1: read count(on_call) = 2
T2: read count(on_call) = 2
T1: update Alice to false
T2: update Bob to false
T1: commit
T2: commit

Si T1 se ejecutara primero en un orden serial, T2 leería 1 y rechazaría el cambio. Si T2 se ejecutara primero, T1 lo rechazaría. El resultado real es cero, lo cual no equivale a ningún orden serial y, por lo tanto, constituye una anomalía de serialización. La diferencia clave con una actualización perdida (lost update) es que T1 y T2 nunca sobrescriben la misma fila, por lo que los bloqueos de escritura ordinarios a nivel de fila no entran en conflicto de forma natural.

Bajo Read Committed, cada sentencia de conteo observa los datos confirmados al momento de iniciar dicha sentencia. Con la barrera descrita en el problema, ambas sentencias leen 2 antes de que ocurra cualquiera de las actualizaciones. Las sentencias UPDATE apuntan a filas distintas y ambas pueden hacer commit. Una sentencia posterior recibiría una instantánea más reciente, pero PostgreSQL no invalida retroactivamente la decisión de aplicación que ya fue tomada.

PostgreSQL Repeatable Read mantiene una sola instantánea para toda la transacción. Evita lecturas no repetibles y lecturas fantasma dentro de esa implementación, pero permite anomalías de serialización. Las dos transacciones actualizan diferentes cadenas de versiones, por lo que ninguna actualización concurrente sobre la misma fila desencadena un rollback y ambas pueden hacer commit. MVCC explica cómo los lectores evitan bloquear a los escritores. No es sinónimo de Serializable y no deduce la regla de negocio de que “al menos un médico debe permanecer de guardia”.

La primera opción segura es Serializable:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT count(*) AS active_count
FROM doctor_shifts
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND on_call;

-- Reject when active_count <= 1.
UPDATE doctor_shifts
SET on_call = false
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND doctor_id = 101
  AND on_call;

COMMIT;

Cuando las solicitudes se solapan, PostgreSQL detecta que sus lecturas de predicado y escrituras concurrentes forman dependencias que no pueden serializarse. No puede permitir que ambas transacciones hagan commit. La transacción fallida devuelve el SQLSTATE 40001. Un reintento debe iniciarse antes de BEGIN y volver a ejecutar el conteo y la decisión de actualizar. Reintentar únicamente el UPDATE final preserva una decisión obsoleta. Utilice retroceso con variación aleatoria (jittered backoff), un número máximo de intentos y un tiempo límite global, ya que un reintento puede volver a entrar en conflicto bajo alta concurrencia.

Serializable permite a la aplicación expresar la regla en torno a su predicado real en lugar de inventar manualmente un bloqueo para cada invariante. Su costo radica en la sobrecarga del rastreo de dependencias y los reintentos por conflicto. Un conjunto de lectura más grande, una transacción más prolongada y una contención concentrada generalmente crean más oportunidades de solapamiento. Un índice en (team_id, shift_date), transacciones cortas y evitar esperas de red antes del commit reducen esa ventana.

La segunda opción proyecta el invariante multifila sobre una sola fila, on_call_rosters(team_id, shift_date, active_count), y utiliza una actualización atómica bajo Read Committed:

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE on_call_rosters
SET active_count = active_count - 1
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND active_count > 1
RETURNING active_count;

-- Continue only when exactly one roster row was returned.
UPDATE doctor_shifts
SET on_call = false
WHERE team_id = 42
  AND shift_date = DATE '2026-07-20'
  AND doctor_id = 101
  AND on_call;

-- Commit only when both updates changed exactly one row; otherwise roll back.
COMMIT;

Ambas solicitudes ahora compiten por la misma fila del cronograma. Tras esperar una actualización concurrente bajo PostgreSQL Read Committed, la transacción que actualiza vuelve a comprobar active_count > 1 frente a la nueva versión de la fila. La primera solicitud cambia de 2 a 1; la segunda condición falla y no devuelve ninguna fila. Una restricción a nivel de fila CHECK (active_count >= 1) puede proporcionar una protección final. El costo es que cada ruta de activación, desactivación, importación y reparación debe mantener el contador dentro de la misma transacción. El sistema también debe conciliar el contador con las filas de detalle y alertar sobre desviaciones en lugar de sobrescribirlo silenciosamente.

Si mantener un contador no es deseable, conserve una fila de cronograma estable para cada turno. Como primer paso en una transacción Read Committed, ejecute SELECT ... FOR UPDATE sobre esa fila, luego cuente las filas de detalle y actualice al médico. Una vez que la segunda solicitud termina su espera, su siguiente SELECT obtiene una instantánea que incluye el primer commit. Cada ruta de escritura debe bloquear primero la misma fila de cronograma y la transacción debe mantenerse corta. Una variante sutil e insegura consiste en establecer una instantánea antigua de Repeatable Read y luego bloquear una fila de guardia que nadie modifica: adquirir FOR UPDATE no refresca esa instantánea de transacción.

Bloquear directamente todas las filas de médicos actualmente activos también requiere cuidado. El conjunto de bloqueo proviene de un predicado, y nuevas filas, órdenes de consulta diferentes o escrituras directas pueden alterar la validez de la seguridad. Un orden inconsistente en múltiples bloqueos de filas puede causar un interbloqueo (deadlock). Si se requiere este diseño, adquiera los bloqueos en un orden estable de clave primaria y vuelva a ejecutar la transacción completa ante un 40P01. Bloquear únicamente “el médico que estoy retirando de la guardia” es definitivamente insuficiente porque las solicitudes seguirán bloqueando filas diferentes.

No valide esto llamando a dos API de forma secuencial. Utilice dos conexiones de base de datos independientes y una barrera de prueba. Ambas sesiones inician Repeatable Read, cuentan las filas, verifican mediante aserciones que cada una observó 2, y solo entonces proceden a sus actualizaciones y commits. La línea base debería producir de manera confiable dos commits y un conteo final de cero. Bajo Serializable, compruebe que dos éxitos sean imposibles: una transacción recibe 40001 y su reintento completo observa 1 y rechaza el cambio. Bajo el diseño de cronograma, compruebe que exactamente una actualización condicional devuelve una fila y que tanto el detalle como active_count finalizan en 1.

Repita el caso concurrente alterando el orden de los commits. Agregue solicitudes duplicadas para el mismo médico, la incorporación de un tercer médico, un rollback entre las dos actualizaciones, tiempos de espera de bloqueo agotados y casos de interbloqueo. En producción, monitoree 40001, 40P01, intentos de reintento, esperas de bloqueo, duración de transacciones, tasa de fallas terminales y discrepancias entre los contadores del cronograma y las filas de detalle. Un aumento repentino en la tasa de conflictos suele indicar un punto caliente (hotspot), una transacción prolongada o una nueva ruta de escritura que ingresa al límite del sistema; los reintentos ilimitados solo lo ocultan y lo amplifican.

Ejemplo de Respuesta de Alto Nivel

“Primero identificaría esto como un caso de write skew. Ambas transacciones comprueban la misma regla que abarca múltiples filas, pero Alice y Bob actualizan sus propias filas, por lo que los bloqueos de escritura a nivel de fila no colisionan. Dado el cronograma donde ambas leen 2 primero, Read Committed puede permitir que ambas hagan commit. PostgreSQL Repeatable Read proporciona a cada transacción una instantánea estable, pero aun así puede producir el valor final cero, el cual ningún ordenamiento serial puede explicar.

Mi primera opción es expresar la regla dentro de una transacción Serializable. Cuento los médicos activos para el turno y actualizo solo cuando el conteo es mayor a 1. PostgreSQL rastrea la dependencia entre la lectura del predicado y las escrituras concurrentes. En este caso, ambas transacciones no pueden hacer commit; una recibe 40001. La aplicación captura ese error y vuelve a ejecutar el conteo y la actualización desde el inicio con una política de reintentos acotada. Mantengo las notificaciones fuera de la transacción reintentable y las proceso desde una bandeja de salida (outbox) idempotente después del commit.

Para un turno con contención constante, consideraría una fila en on_call_rosters. Bajo Read Committed, la transacción para salir de guardia utiliza UPDATE ... SET active_count = active_count - 1 WHERE active_count > 1 RETURNING ... para competir sobre esa única fila y luego actualiza el detalle del médico. Si cualquiera de las sentencias no logra modificar exactamente una fila, toda la transacción se revierte. La segunda solicitud vuelve a comprobar su condición sobre la versión más reciente de la fila y falla. La contrapartida es que cada ruta de escritura debe mantener el contador.

Verificaría el diseño con dos conexiones y una barrera que fije el entrelazado. Primero demostraría que Repeatable Read puede permitir que ambas sesiones lean 2 y lleguen a cero. Luego demostraría que Serializable aborta exactamente una transacción y que su reintento rechaza el cambio. El diseño con contador debería permitir únicamente una actualización condicional. Por último, monitorearía fallas de serialización, interbloqueos, esperas de bloqueo y desvíos en los contadores para que la garantía de seguridad no dependa de un orden de prueba afortunado.”

Errores Comunes

• “Está dentro de una transacción, así que es seguro” → La atomicidad no garantiza nada sobre la visibilidad ni el orden de commits entre transacciones concurrentes → Nombre el nivel de aislamiento y detalle el entrelazado que rompe el invariante.
• Tratar el write skew como una actualización perdida (lost update) → Las transacciones escriben en filas diferentes, por lo que una verificación de versión en una sola fila no cubre su predicado compartido → Identifique el invariante multifila y utilice un punto de contención común o Serializable.
• Afirmar que Repeatable Read es igual a Serializable → Una instantánea estable aún puede producir un resultado combinado sin explicación serial → Describa la anomalía de serialización permitida por PostgreSQL Repeatable Read.
• Bloquear la fila de médico de cada transacción → Los bloqueos de Alice y Bob no entran en conflicto → Bloquee una sola fila de cronograma, actualice una fila de contador o permita que SSI detecte la dependencia de predicado.
• Agregar FOR UPDATE después de una instantánea antigua de Repeatable Read → Bloquear una fila de resguardo no modificada no refresca la instantánea de la transacción → Bloquee y vuelva a leer bajo Read Committed, o use Serializable o una fila de contador mutable.
• Reintentar solo COMMIT o la última sentencia SQL tras un 40001 → La decisión de negocio aún se derivó de una instantánea obsoleta → Vuelva a ejecutar la transacción completa y toda la lógica de la aplicación que selecciona el SQL.
• Reintentar inmediatamente sin un límite → Las solicitudes concurrentes colisionan en sincronía y amplifican la carga de la base de datos → Utilice retroceso con variación aleatoria (jittered backoff), un conteo máximo de intentos y un tiempo límite global.
• Enviar una notificación antes del commit → Un reintento por serialización puede enviar la notificación más de una vez → Posponga los efectos externos y utilice una outbox transaccional con consumidores idempotentes.
• Mantener un contador permitiendo escrituras directas en las filas de detalle → active_count se desvía del conteo real de guardia y la condición pierde sentido → Asegúrese de que todas las rutas de escritura utilicen el mismo protocolo transaccional y concílielo continuamente.

Preguntas de Seguimiento y Respuestas

Pregunta de seguimiento 1: ¿Por qué una sentencia atómica UPDATE puede evitar el inventario negativo pero no resuelve automáticamente este problema?

Una sola fila de inventario puede usar UPDATE inventory SET stock = stock - 1 WHERE stock > 0. La condición y el objetivo de escritura son la misma fila, por lo que las actualizaciones concurrentes vuelven a comprobar la condición sobre la versión más reciente. Aquí, la condición es un conteo a través de múltiples filas mientras que la escritura afecta a un solo médico. Para obtener la misma propiedad, materialice el conteo en una sola fila de cronograma o utilice Serializable para detectar dependencias de predicados.

Pregunta de seguimiento 2: ¿Qué sucede si la tasa de fallas en Serializable es alta?

Primero acorte la duración de la transacción, indexe el predicado, elimine las llamadas de red dentro de la transacción y mida los conflictos por turno. Si unos pocos turnos de alta demanda representan la mayoría de los errores 40001, traslade solo esas escrituras a la actualización condicional del cronograma para que la contención haga cola en una sola fila. Si todos los turnos sufren una alta contención, revise las operaciones por lotes y los límites de las transacciones. Aumentar el límite de reintentos no reemplaza el análisis de capacidad.

Pregunta de seguimiento 3: ¿Puede una restricción CHECK por sí sola garantizar que un médico permanezca de guardia?

Una comprobación colocada en una fila de doctor_shifts puede validar campos en esa misma fila, pero esa fila no puede demostrar de forma independiente que otro médico siga activo en el mismo turno. Después de proyectar el invariante en una fila de cronograma con active_count, CHECK (active_count >= 1) se convierte en una protección a nivel de base de datos. El detalle y el contador aún deben modificarse en la misma transacción y ser conciliados.

Pregunta de seguimiento 4: ¿Qué sucede si el servicio se cae después de decrementar el contador del cronograma?

Si el contador y el estado del médico están dentro de la misma transacción de base de datos, una sesión desconectada revierte la transacción no confirmada, por lo que no es posible que persista solo la mitad del cambio. Si el commit tuvo éxito pero el cliente perdió la respuesta, el resultado es desconocido. Un reintento debe incluir un ID de operación de negocio e inspeccionar el estado actual del médico antes de volver a descontar la misma acción de salida de guardia.

Pregunta de seguimiento 5: ¿Qué sucede si la regla abarca médicos almacenados en dos bases de datos?

Serializable y los bloqueos de fila en una sola base de datos solo protegen los datos visibles para esa base de datos. Elija un único límite de escritura autoritativo, como un servicio de cronograma fuertemente consistente cuyo estado se proyecte asíncronamente a otras bases de datos, o introduzca una transacción distribuida aceptando sus costos de coordinación, disponibilidad y latencia. Si ambas bases de datos deciden localmente y se fusionan de forma asíncrona, el diseño debe permitir explícitamente violaciones temporales y definir compensaciones; la prueba de seguridad de base de datos única deja de ser aplicable.