En la lección anterior quedó una letra sin desarrollar. La A de atomicidad, la C de consistencia y la D de durabilidad las abrimos en canal; la I de aislamiento la enunciamos en cuatro líneas y la aplazamos. Este es el aplazamiento.

El motivo de aplazarla es que el aislamiento no se parece a las otras tres. La atomicidad no admite grados: una transacción es atómica o no lo es. La durabilidad tampoco: lo confirmado sobrevive o no sobrevive. El aislamiento, en cambio, es un dial. El estándar SQL define cuatro posiciones, cada gestor implementa las que quiere y como quiere, y elegir la posición equivocada produce errores que no aparecen en el portátil de desarrollo, no aparecen en los tests, aparecen un martes a las once de la mañana en el mostrador de la sucursal Centro y no hay forma de reproducirlos.

Porque eso es exactamente lo que ha pasado esta semana en BiblioRed. El ejemplar EJ-3081 de "El mapa del tiempo" figura prestado a dos socios distintos al mismo tiempo. El club de lectura de otoño de la sucursal Norte tiene 25 plazas y 26 inscritos, aunque el sistema comprobaba el aforo antes de inscribir. Y un evento publicado se ha quedado sin ningún ponente, pese a que la aplicación impide cancelar al último.

Ninguno de los tres es un error de programación en el sentido habitual. El código que los produce se lee bien, pasa la revisión y funciona perfectamente cuando lo ejecuta una sola persona. Los tres son fallos de concurrencia, y esta lección trata de reconocerlos, provocarlos a voluntad y arreglarlos.

Todo lo que sigue está pensado para que lo reproduzcas. Abre dos terminales con psql conectados a la misma base de datos. A lo largo de la lección los llamaremos Sesión A y Sesión B, y cada demostración indica en qué orden hay que ejecutar cada instrucción. Ejecutarlas en otro orden da otro resultado, y esa es precisamente la enseñanza.

1. Por qué la concurrencia es imprescindible y por qué es peligrosa
2. El laboratorio: dos terminales y el estado inicial
3. Fenómeno 1: actualización perdida
4. Fenómeno 2: lectura sucia
5. Fenómeno 3: lectura no repetible
6. Fenómeno 4: lectura fantasma y la última plaza
7. Fenómeno 5: sesgo de escritura, el que sorprende
8. Los cuatro niveles de aislamiento del estándar SQL
9. Cómo se establece el nivel y qué hace realmente PostgreSQL
10. Control de concurrencia por bloqueo
11. Bloqueos explícitos: FOR UPDATE, FOR SHARE, LOCK TABLE, SKIP LOCKED
12. Control multiversión (MVCC) y por qué existe VACUUM
13. Interbloqueos: cómo se producen, cómo se detectan, cómo se evitan
14. Bloqueo optimista frente a pesimista
15. La solución completa: la última plaza del club de lectura
16. Fuera de PostgreSQL: SQLite y el retorno del problema en NoSQL

Empecemos por lo obvio, porque explica por qué no basta con "hacerlo de uno en uno".

Por qué es imprescindible. BiblioRed tiene cuatro mostradores, un catálogo web abierto a 12.000 socios, una aplicación móvil y varios procesos automáticos (avisos de vencimiento, generación de informes). Si las operaciones se ejecutaran estrictamente una detrás de otra, cada mostrador esperaría a que terminaran todos los demás. Y no solo eso: mientras una transacción espera a que el disco confirme su fsync() —esos milisegundos del apartado 12 de la lección anterior—, el procesador estaría ocioso. La concurrencia es lo que permite que el tiempo de espera de una operación sea el tiempo de trabajo de otra.

Por qué es peligrosa. Esa última fila es toda la lección. Cuando dos transacciones tocan los mismos datos a la vez, el resultado puede depender del orden exacto en que se entrelacen sus instrucciones. Y ese orden no lo controlas: lo decide el planificador del sistema operativo, la latencia de la red y qué página estaba en caché en ese microsegundo.

La formulación clásica del problema es esta:

El objetivo del control de concurrencia es que la ejecución entrelazada de varias transacciones produzca el mismo resultado que alguna ejecución en la que se hubieran ejecutado una detrás de otra. A esa propiedad se la llama serializabilidad.

Fíjate en el "alguna": no se exige un orden concreto. Si A e I se ejecutan a la vez, vale que el resultado sea el de "A y luego I" o el de "I y luego A". Lo que no vale es que sea un resultado que ningún orden secuencial habría producido. Cuando eso ocurre, tenemos una anomalía.

Antes de provocar nada, hay que preparar el escenario. Abre dos terminales:

Un truco muy práctico: haz que cada sesión se identifique en el prompt y muestre siempre el número de proceso, que hará falta al hablar de bloqueos.

Y el estado de partida de los datos que vamos a maltratar:

Una plaza libre. Un ejemplar disponible. Todo lo que hace falta para romper el sistema.

Actualización perdida (lost update). Dos transacciones leen el mismo dato, ambas calculan un valor nuevo a partir de lo leído, y ambas escriben. La segunda escritura pisa la primera, que se pierde sin dejar rastro ni error.

Este es el fallo del ejemplar EJ-3081. La aplicación del mostrador hace lo natural: lee el estado, comprueba que está disponible, y lo presta.

Los dos mostradores de la sucursal Centro atienden a la vez. Sesión A es el mostrador 1, atendiendo a Marta Alsina (socia 14). Sesión B es el mostrador 2, atendiendo a Iván Pereda (socio 15). Ejecuta en el orden de la columna "momento":

Comprueba el estropicio:

Dos préstamos abiertos del mismo ejemplar físico. Marta se lo ha llevado a casa e Iván está en el mostrador preguntando dónde está su libro. No ha habido ningún error, ninguna excepción, ninguna traza. Las dos transacciones han sido atómicas, consistentes y durables. Y el resultado es imposible.

La comprobación de A (t3) y la escritura de A (t8) están separadas en el tiempo, y en ese hueco B ha leído. B ha tomado su decisión con información que dejó de ser cierta antes de que B actuara. Es lo que se llama una secuencia leer-modificar-escribir sin protección.

Fíjate en un detalle importante: ningún orden secuencial produce este resultado. Si A se hubiera ejecutado entera y luego B, B habría leído prestado y habría rechazado el préstamo. Al revés, igual. El resultado obtenido no corresponde a ninguna ejecución en serie: es una anomalía en el sentido estricto del apartado 1.

Es importante entender por qué esto ocurre a pesar de que un UPDATE suelto sí es seguro. Compara:

La segunda forma es atómica de verdad: el gestor bloquea la fila para actualizarla y evalúa la condición sobre la versión más reciente. Si otra transacción ya la puso en prestado, la respuesta es:

Y ese UPDATE 0 es la señal que la aplicación debe leer como "alguien se me ha adelantado, cancela la operación". Comprobar el número de filas afectadas es la defensa más barata que existe contra la actualización perdida, y sorprende cuánto código la ignora.

Lectura sucia (dirty read). Una transacción lee datos que otra ha modificado pero todavía no ha confirmado. Si la otra hace ROLLBACK, la primera ha trabajado con datos que nunca existieron.

En un gestor que permitiera la lectura sucia, en t4 la Sesión B leería pagada y el informe contaría un cobro que nunca ocurrió.

En PostgreSQL, en t4 la Sesión B lee pendiente. Siempre. No hay forma de provocar una lectura sucia, ni siquiera pidiéndolo explícitamente:

PostgreSQL acepta la sintaxis READ UNCOMMITTED por compatibilidad con el estándar, pero internamente la trata como READ COMMITTED. La razón es su arquitectura: como veremos en el apartado 12, el control multiversión hace que una transacción lea siempre una versión confirmada de cada fila. Leer datos sucios no es que esté prohibido: es que no hay ningún mecanismo con el que hacerlo.

Esto no significa que el fenómeno sea una curiosidad histórica. Otros gestores sí lo permiten —SQL Server con READ UNCOMMITTED o el infame WITH (NOLOCK), MySQL con READ UNCOMMITTED— y hay quien lo activa "para que los informes no bloqueen". Es una mala idea: además de leer datos que pueden desaparecer, en algunos motores puede leer filas duplicadas o saltarse filas si el índice se reorganiza durante el recorrido.

Lectura no repetible (non-repeatable read). Una transacción lee una fila, y al leerla de nuevo dentro de la misma transacción obtiene valores distintos, porque otra transacción la modificó y confirmó en medio.

La dirección de BiblioRed pide un informe que primero cuenta las multas pendientes y después suma su importe:

La misma consulta, dentro de la misma transacción, ha devuelto dos valores distintos. El informe que A está construyendo mezcla cifras de dos instantes: si la primera lectura alimentó un total y la segunda un desglose, el total y el desglose no cuadran. Y quien lo reciba pensará que hay un error de cálculo.

En REPEATABLE READ, PostgreSQL toma una foto (snapshot) de la base de datos en la primera instrucción de la transacción, y todas las lecturas posteriores ven esa foto, ignorando lo que otros confirmen después. Es exactamente lo que un informe necesita: una fotografía coherente de un instante.

Regla práctica. Todo informe que ejecute más de una consulta y presente los resultados juntos debería ir dentro de una transacción REPEATABLE READ. Es gratis, es una línea, y elimina de raíz la familia entera de "los números no cuadran".

Lectura fantasma (phantom read). Una transacción ejecuta una consulta con una condición, y al repetirla aparecen filas nuevas que cumplen esa condición y que otra transacción insertó y confirmó en medio. La diferencia con la lectura no repetible es que allí cambiaban los valores de una fila; aquí cambia el conjunto de filas.

Este es el fallo del club de lectura, y es más sutil que los anteriores porque el código que lo produce parece impecable.

La aplicación de inscripciones hace lo siguiente: cuenta las plazas ocupadas, comprueba que quedan libres, e inserta.

Recuerda el estado: evento 51, 25 plazas, 24 ocupadas, una libre. Marta Alsina (14) se inscribe desde el móvil mientras Iván Pereda (15) se inscribe en el mostrador Norte.

26 personas para 25 sillas. El día del club de lectura, alguien se queda de pie.

Este es el punto que hay que interiorizar, porque es contraintuitivo:

Contar antes de insertar no protege de nada. El recuento es cierto en el instante en que se hace y deja de serlo inmediatamente después. Entre el SELECT y el INSERT hay un hueco, y por ese hueco cabe una transacción entera.

Y hay algo peor. En la lectura no repetible, subir a REPEATABLE READ bastaba. Aquí no basta con ninguna foto, porque el problema no es lo que A ve: es que A y B están decidiendo sobre la misma plaza sin saberlo. Ni siquiera un bloqueo sobre las filas leídas serviría, porque las filas conflictivas —las inscripciones de la otra— todavía no existían cuando se leyó. No se puede bloquear una fila que no existe. De ahí el nombre "fantasma".

Las soluciones reales se ven en el apartado 15, y son tres, con consecuencias distintas.

El estándar SQL dice que REPEATABLE READ permite lecturas fantasma. PostgreSQL no las permite: su REPEATABLE READ es en realidad snapshot isolation, y la foto es de la base entera, así que las filas insertadas después son invisibles. Es más estricto que el estándar.

Pero cuidado con la conclusión: eso resuelve el fantasma de lectura, no el problema de la última plaza. Bajo REPEATABLE READ, A no vería la inscripción de B... y aun así insertaría la suya, y el COMMIT de las dos tendría éxito porque no hay conflicto de escritura sobre una misma fila. El resultado seguiría siendo 26. Este es el puente natural hacia el fenómeno siguiente.

Sesgo de escritura (write skew). Dos transacciones leen un mismo conjunto de datos, cada una decide algo basándose en él, y cada una escribe en filas distintas. Ninguna pisa a la otra, así que no hay conflicto detectable, pero juntas violan una regla que individualmente respetaban.

Es el fenómeno que sorprende incluso a quien lleva años trabajando con bases de datos, porque ocurre en REPEATABLE READ / snapshot isolation, que mucha gente da por "el nivel seguro".

Regla de la casa: todo evento publicado debe tener al menos un ponente confirmado. El evento 51 tiene dos: Nuria Bastos y un ponente externo. La aplicación, al cancelar un ponente, comprueba que quede alguno.

Cero ponentes en un evento publicado. Y las dos transacciones tenían razón cuando decidieron.

Fíjate en por qué el gestor no ha protestado: A escribió en la fila del ponente 7, B en la del ponente 9. No hay ninguna fila en conflicto. Los mecanismos que detectan actualizaciones perdidas trabajan a nivel de fila, y aquí no hay dos escrituras sobre la misma fila. El conflicto está en la premisa: las dos leyeron un conjunto que la otra iba a modificar.

Otros ejemplos del mismo patrón, para que lo reconozcas cuando lo veas:

Las únicas defensas contra el sesgo de escritura son:

1. Aislamiento SERIALIZABLE (con reintentos, porque abortará transacciones).
2. Materializar el conflicto: forzar que las dos transacciones escriban en la misma fila, aunque sea artificialmente —por ejemplo, bloqueando la fila de eventos con SELECT ... FOR UPDATE antes de tocar sus ponentes—.
3. Una restricción de la base de datos que exprese la regla, cuando sea posible. Aquí no lo es directamente (un CHECK no puede contar filas de otra tabla), lo que ilustra el límite de las restricciones declarativas frente a las reglas que abarcan varias filas.

En el apartado 15 aplicaremos exactamente estas tres ideas al problema de la última plaza.

El estándar SQL:1992 definió cuatro niveles, precisamente en función de qué fenómenos permiten. Esta es la tabla canónica, la que hay que saberse:

Las dos últimas columnas merecen una nota: el sesgo de escritura no aparece en el estándar de 1992. Se describió después, cuando el snapshot isolation se popularizó y se vio que cumplía la tabla del estándar hasta REPEATABLE READ y aun así permitía anomalías. Lo incluimos porque en la práctica es el que más problemas causa hoy.

Y ahora la tabla que de verdad importa cuando trabajas con PostgreSQL:

Tres lecturas de esta tabla:

• PostgreSQL es más estricto que el estándar en REPEATABLE READ: prohíbe los fantasmas de lectura, que el estándar permite.
• El nivel por omisión es READ COMMITTED, que permite tres de los cuatro fenómenos. No es un descuido: es un equilibrio deliberado entre corrección y rendimiento, y significa que el nivel de aislamiento con el que trabaja tu aplicación hoy, si nadie lo ha tocado, es el segundo más débil.
• El salto de garantía está entre REPEATABLE READ y SERIALIZABLE, y es el más caro: SERIALIZABLE es el único que elimina el sesgo de escritura, y lo hace abortando transacciones.

Está en cuándo se toma la foto:

Esa fila de "conflicto de escritura" es la que sorprende en producción. Bajo REPEATABLE READ:

La transacción de A queda abortada y hay que reintentarla entera. No es un fallo: es el gestor negándose a producir una anomalía. Pero si tu aplicación no sabe reintentar, el usuario ve un error.

Tres formas, de más local a más global:

Consultar el nivel actual:

También se puede cambiar el valor por omisión del servidor en postgresql.conf con default_transaction_isolation, pero no es recomendable: hace que el comportamiento de la aplicación dependa de un fichero que probablemente no está en el mismo repositorio que el código. El nivel es una decisión del código, y debe verse en el código.

Dos modificadores útiles para informes:

• READ ONLY impide escrituras y permite al gestor optimizaciones.
• DEFERRABLE, combinado con SERIALIZABLE READ ONLY, hace que la transacción espere hasta poder tomar una foto que garantice que nunca abortará por serialización. Es ideal para un informe nocturno largo: puede tardar un poco en arrancar, pero no fallará a mitad tras veinte minutos de trabajo.

Históricamente, la primera respuesta al problema de la concurrencia fue el bloqueo: si una transacción va a usar un dato, lo reserva y los demás esperan.

La idea es intuitiva: muchos pueden leer a la vez, pero escribir requiere exclusividad. La tabla de compatibilidad se lee así: si A tiene un bloqueo S sobre la fila 3081 y B pide otro S, B pasa. Si B pide X, B espera.

Un bloqueo puede tomarse sobre unidades de distinto tamaño, y hay un compromiso claro:

Algunos gestores practican la escalada de bloqueos: si una transacción acumula demasiados bloqueos de fila, los sustituyen por uno de tabla para ahorrar memoria, con el efecto colateral de bloquear a todo el mundo. PostgreSQL no escala bloqueos: guarda la marca de bloqueo en la propia fila, así que puede tener millones sin gastar memoria del servidor. Es una diferencia práctica notable.

El protocolo que garantiza la serializabilidad mediante bloqueos se llama bloqueo en dos fases (two-phase locking, 2PL), y su regla es de una simplicidad engañosa:

Una transacción tiene una fase de crecimiento, en la que solo puede adquirir bloqueos, y una fase de decrecimiento, en la que solo puede liberarlos. Una vez que ha liberado el primer bloqueo, no puede adquirir ninguno más.

En la práctica, casi todos los gestores usan 2PL estricto: los bloqueos exclusivos se mantienen hasta el COMMIT o el ROLLBACK. Eso garantiza que nadie lea cambios no confirmados y simplifica la recuperación.

Y explica la mayor consecuencia operativa de todo esto: cuanto más dura una transacción, más tiempo retiene sus bloqueos y más gente espera. Es la justificación técnica de la regla "transacciones cortas" de la lección 06-01.

El precio del 2PL es que las transacciones se esperan unas a otras, y de ahí nacen los interbloqueos del apartado 13.

Para completar el cuadro, PostgreSQL tiene ocho modos de bloqueo a nivel de tabla. No hay que memorizarlos, pero sí saber que existen y que los toma solo:

Esa última fila es la causa de la mitad de las caídas de servicio durante los despliegues: un ALTER TABLE que espera detrás de una consulta larga, y toda la cola de peticiones esperando detrás del ALTER TABLE, incluidos los SELECT que antes funcionaban. Ver los bloqueos en curso:

El wait_event_type = Lock de la segunda fila es la firma inconfundible de "estoy esperando a otro".

Además de los bloqueos automáticos, SQL permite pedirlos a mano. Es la herramienta del bloqueo pesimista (apartado 14).

Bloquea las filas leídas como si fueran a modificarse. Cualquier otra transacción que intente modificarlas —o bloquearlas— espera.

Ahora la fila 3081 está reservada. Reproducción con dos sesiones:

Y ahí está la clave: cuando B por fin obtiene la fila, la ve con el valor nuevo. Su comprobación de "¿está disponible?" ahora es correcta, y rechazará el préstamo. Esto resuelve la actualización perdida del apartado 3 de forma limpia.

Bloqueo compartido: impide que otros modifiquen las filas, pero permite que otros las lean con FOR SHARE. Se usa cuando necesitas garantizar que una fila no cambie mientras trabajas con datos relacionados, sin pretender modificarla tú.

Existen además dos variantes más suaves, FOR NO KEY UPDATE y FOR KEY SHARE, que PostgreSQL usa internamente para las claves ajenas y que permiten más concurrencia. Conocer que existen basta.

Bloquea la tabla entera. Es contundente y casi siempre desproporcionado.

Su uso legítimo es el proceso de mantenimiento nocturno que necesita una tabla quieta, o el paso de una migración que reorganiza datos. En el camino de una operación de usuario, nunca.

Dos modificadores que cambian qué ocurre cuando la fila está ocupada:

NOWAIT sirve para dar una respuesta rápida al usuario en lugar de dejarlo esperando:

La aplicación traduce ese error a "otro mostrador está atendiendo este ejemplar en este momento, inténtelo de nuevo", que es infinitamente mejor que una pantalla congelada.

SKIP LOCKED es la base de las colas de trabajo. BiblioRed tiene un proceso que envía los avisos de vencimiento; con varios trabajadores en paralelo, cada uno debe tomar avisos distintos:

Cada trabajador recibe diez avisos que ningún otro está procesando, sin esperas y sin duplicados. Es un patrón que sustituye a una cola de mensajes en muchos sistemas de tamaño medio, y funciona sorprendentemente bien.

El bloqueo tiene un defecto grave: si escribir requiere exclusividad, los lectores estorban a los escritores y viceversa. El informe mensual de dirección, que recorre tres millones de préstamos, bloquearía el mostrador durante todo su recorrido.

La solución que adoptan PostgreSQL, Oracle e InnoDB es el control de concurrencia multiversión.

MVCC. El gestor no sobrescribe los datos: cada modificación crea una nueva versión de la fila. Cada transacción ve la versión que era visible en el momento de su foto. Así, los lectores nunca bloquean a los escritores ni los escritores a los lectores.

Cada fila física lleva dos columnas ocultas:

Puedes verlas:

Ahora un UPDATE, y volvemos a mirar:

El ctid —la dirección física de la fila— ha cambiado de (12,7) a (12,41). La fila no se ha modificado: se ha escrito una nueva en otro sitio, y la antigua ha quedado marcada con xmax = 90118. Un UPDATE en PostgreSQL es, físicamente, un INSERT más un marcado de la versión anterior.

Cuando una transacción lee, aplica una regla sencilla: una versión es visible si su xmin corresponde a una transacción confirmada antes de mi foto y su xmax es cero o corresponde a una transacción no confirmada en mi foto.

Este diseño tiene una consecuencia inevitable. Después de un tiempo de funcionamiento, la tabla ejemplares contiene la versión vigente de cada fila y todas las versiones antiguas que ya no ve nadie. Se las llama versiones muertas (dead tuples).

Las versiones muertas cuestan de tres formas:

1. Espacio en disco, que crece sin parar.
2. Tiempo de lectura: un recorrido de la tabla lee también las versiones muertas y las descarta una a una.
3. Consumo de identificadores de transacción, que son finitos.

Para verlo:

VACUUM recorre las tablas y marca como reutilizable el espacio de las versiones muertas que ya no puede ver ninguna transacción.

Variantes:

En condiciones normales no hay que ejecutarlo a mano: el proceso autovacuum lo hace solo. Pero hay que saber qué pasa si no se ejecuta:

• Hinchazón (bloat): la tabla ocupa varias veces lo que debería y las consultas se ralentizan progresivamente. Una tabla prestamos de 2 GB de datos útiles puede llegar a ocupar 9 GB.
• Estadísticas viejas, y con ellas planes de ejecución malos (06-03).
• Agotamiento de identificadores de transacción: PostgreSQL usa un contador de 32 bits. Si VACUUM no "congela" a tiempo las filas antiguas, el servidor se detiene por completo para evitar la pérdida de datos, con un mensaje inolvidable: database is not accepting commands to avoid wraparound data loss. Es una de las pocas formas de dejar una base de datos PostgreSQL fuera de servicio por descuido operativo.

El enemigo número uno de VACUUM son las transacciones largas. Una transacción abierta desde hace tres horas obliga a conservar todas las versiones muertas creadas desde entonces, porque teóricamente esa transacción podría necesitarlas. Es otra razón —la tercera ya— para que las transacciones sean cortas. Detectar culpables:

Ese idle in transaction de tres horas es exactamente el patrón que la lección 06-01 pedía evitar con idle_in_transaction_session_timeout.

Interbloqueo (deadlock). Dos o más transacciones se esperan mutuamente en un ciclo: A espera un recurso que tiene B, y B espera un recurso que tiene A. Sin intervención externa, esperarían para siempre.

El caso clásico: dos transacciones que tocan las mismas dos filas en orden inverso. En BiblioRed, un traslado de ejemplares entre las sucursales Centro y Norte, ejecutado a la vez en los dos sentidos.

En la Sesión A:

El grafo de espera que el gestor ha construido:

Un ciclo. Cuando el detector encuentra un ciclo, elige una transacción víctima —normalmente la que menos ha trabajado— y la aborta. La otra continúa y termina bien.

No comprueba el ciclo en cada espera: sería carísimo. Cuando una transacción lleva esperando más de deadlock_timeout (1 segundo por omisión), y solo entonces, construye el grafo y busca ciclos.

Consecuencia: un interbloqueo cuesta como mínimo un segundo antes de resolverse. En un sistema con muchos interbloqueos, eso solo ya es un problema de rendimiento.

Para investigarlos, conviene activar el registro de los bloqueos que tardan:

La primera es la fundamental, y es fácil de aplicar. El traslado de ejemplares reescrito:

Con las dos sesiones tomando los bloqueos en el mismo orden, el ciclo es imposible: la segunda espera a la primera y termina después. Hay espera, pero no hay interbloqueo.

Y sobre los reintentos: un interbloqueo se identifica por el SQLSTATE 40P01, y un fallo de serialización por el 40001. Los dos son transitorios y reintentables. Una aplicación seria los captura y reintenta con una espera creciente y algo de aleatoriedad, hasta un máximo de tres o cinco intentos.

Las dos estrategias generales para proteger una secuencia leer-modificar-escribir. La diferencia está en la suposición de partida.

Es el patrón estándar. Se añade a la tabla una columna que se incrementa en cada modificación, y la actualización solo se aplica si la versión sigue siendo la que se leyó.

El flujo, aplicado a la edición de un evento de BiblioRed desde el panel de gestión:

Si nadie ha modificado el evento:

Si otro gestor lo modificó mientras nuestro usuario pensaba:

Y ese UPDATE 0 es toda la detección. La aplicación muestra "otro usuario ha modificado este evento; revise los cambios y vuelva a guardar" en lugar de pisar silenciosamente el trabajo ajeno.

Este patrón resuelve el problema del apartado 15.2 de la lección 06-01: no hay ninguna transacción abierta mientras el humano decide. La transacción dura lo que dura un UPDATE.

Para automatizar el incremento y que nadie se olvide, un disparador —de los que vimos brevemente en 05-04—:

Ojo: con el disparador, la aplicación ya no debe escribir version = version + 1 en su UPDATE, pero sí debe seguir poniendo AND version = ? en el WHERE. La condición es la protección; el incremento es solo contabilidad.

• Optimista si entre la lectura y la escritura hay un ser humano, o si los conflictos son raros (edición de fichas de socios, catalogación de materiales, gestión de eventos). Es el que se usa por omisión en las aplicaciones web.
• Pesimista si la contención es alta y el conflicto es la norma (el préstamo del último ejemplar en hora punta), o si rehacer el trabajo es caro.

Volvamos al problema del apartado 6 y resolvámoslo de verdad. Estado: evento 51, 25 plazas, 24 ocupadas, dos socios inscribiéndose a la vez.

Hay tres enfoques correctos. Los tres funcionan; no son equivalentes.

La idea: convertir el aforo en un dato de una sola fila, con una restricción declarada. Así el conflicto deja de ser un fantasma y pasa a ser un choque sobre la misma fila, que el gestor sabe resolver.

Y la inscripción:

Con dos sesiones simultáneas:

En la Sesión B:

Es imposible pasar de 25. No importa el nivel de aislamiento, no importa el cliente, no importa si mañana alguien escribe un script que inserta a mano: la restricción está en la base de datos y se cumple siempre.

Dos detalles técnicos que conviene entender:

• El UPDATE ... SET x = x + 1 no es leer-modificar-escribir de la aplicación: el gestor bloquea la fila, lee el valor vigente y escribe. Bajo READ COMMITTED, cuando B se desbloquea reevalúa su UPDATE sobre la versión más reciente, así que suma sobre 25 y no sobre 24.
• Bajo REPEATABLE READ, en lugar del error de CHECK, B obtendría could not serialize access due to concurrent update. También correcto, pero exige reintento.

Coste: la fila del evento se convierte en un punto de serialización. Todas las inscripciones a ese evento se ponen en cola sobre ella. Para un club de lectura de 25 plazas es irrelevante; para vender 60.000 entradas en dos minutos sería un cuello de botella.

La idea: bloquear la fila del evento antes de contar, de modo que solo una transacción a la vez pueda estar decidiendo sobre ese evento.

La Sesión B espera en su FOR UPDATE hasta el COMMIT de A, luego cuenta 25, ve que no hay sitio y rechaza limpiamente.

Ventajas: no requiere columnas nuevas ni desnormalización, y la lógica de aforo (que puede ser compleja: acompañantes, plazas reservadas para escolares, lista de espera) queda en un solo sitio.

Inconvenientes: la protección vive en el código de la aplicación. Si otro programa, otro equipo o un script de mantenimiento inserta en inscripciones sin tomar el bloqueo, la garantía desaparece sin que nadie se entere. Y hay que recordar bloquear siempre la misma fila y en el mismo orden respecto a otros bloqueos, o vuelven los interbloqueos del apartado 13.

La idea: pedirle al gestor la garantía completa y dejar que él detecte el conflicto.

Con las dos sesiones entrelazadas como en el apartado 6, la primera en confirmar tiene éxito y la segunda recibe, en el COMMIT:

El motor SSI de PostgreSQL ha detectado que B leyó un conjunto de filas que A modificó, y que el resultado combinado no corresponde a ninguna ejecución en serie. Aborta a B.

Ventajas: es la única solución que resuelve todos los fenómenos a la vez, incluido el sesgo de escritura del apartado 7. El código de la aplicación se escribe como si no hubiera concurrencia, que es enormemente más fácil de razonar.

Inconvenientes:

• Obliga a implementar reintentos. Sin ellos, el usuario ve un error críptico.
• Tiene coste: el gestor rastrea las dependencias de lectura/escritura de cada transacción.
• El error llega en el COMMIT, cuando ya se ha hecho todo el trabajo.
• Y, como el enfoque 2, no protege de un script que use otro nivel de aislamiento.

Recomendación razonada para BiblioRed: el enfoque 1, complementado con el 3 donde haga falta.

El argumento decisivo es el de la primera fila. Una regla de negocio tan dura como "no caben más de 25 personas" no puede depender de que todos los programas que toquen la base de datos, hoy y dentro de cinco años, recuerden tomar un bloqueo o pedir un nivel de aislamiento. La restricción chk_aforo está en el esquema, se cumple siempre, y además se documenta sola: quien lea la definición de eventos verá la regla escrita. Es el mismo argumento del apartado 21 de la lección 04-04 sobre qué reglas van en la base de datos.

El contador plazas_ocupadas_total es una desnormalización —hay que mantenerlo coherente con inscripciones, con las cautelas de 05-04, incluida la comprobación periódica de que cuadra—, y ese es su precio. Se paga con gusto.

Y para las reglas que no se pueden expresar como restricción de una fila —el "al menos un ponente" del apartado 7, que abarca varias filas de otra tabla— se usa el enfoque 3 con reintento, porque es el único que las cubre.

La concurrencia es la mayor diferencia entre SQLite y un servidor de bases de datos, y conviene tenerla clara para no elegir mal.

El BEGIN IMMEDIATE merece una nota: en SQLite, un BEGIN normal es diferido y no toma el bloqueo de escritura hasta la primera escritura, lo que produce el clásico "leo, decido, escribo y me dicen que la base está ocupada" con el trabajo ya hecho. Si la transacción va a escribir, empieza con BEGIN IMMEDIATE y tomarás el bloqueo desde el principio.

La conclusión práctica: SQLite es excelente con un escritor y muchos lectores. Para los cuatro mostradores de BiblioRed escribiendo a la vez, no. Para la aplicación de inventario en una tableta, perfecta.

Recordarás de 03-04 que la consistencia eventual es el precio de la disponibilidad y la tolerancia a particiones. Lo que aquí conviene añadir es que la consistencia eventual no elimina el problema de la última plaza: lo agrava.

Las herramientas cambian de nombre pero responden a las mismas ideas:

• El quórum de lectura y escritura (w + r > n) es una forma de comprar consistencia en un sistema distribuido, análoga a subir el nivel de aislamiento.
• El writeConcern: {w: "majority"} de MongoDB es la petición explícita de esa garantía.
• Las operaciones atómicas de documento ($inc, findAndModify con condición) son el equivalente del UPDATE ... SET x = x + 1 WHERE ... del enfoque 1: la comprobación y la escritura en una sola operación indivisible.

La lección de fondo, que conviene llevarse: la concurrencia no es un problema de las bases de datos relacionales, es un problema de la realidad. Cambiar de tecnología no lo elimina; cambia las herramientas con las que se afronta y, casi siempre, traslada más responsabilidad al código de la aplicación.

Contar antes de insertar y creer que eso protege. Es el error de esta lección. Entre el SELECT count(*) y el INSERT cabe una transacción entera. Si la regla es dura, exprésala como restricción.

No comprobar el número de filas afectadas. UPDATE ... WHERE estado='disponible' que devuelve UPDATE 0 está diciendo "alguien se me ha adelantado". Ignorarlo convierte una protección perfecta en una decoración.

Usar REPEATABLE READ sin lógica de reintento. En READ COMMITTED un conflicto de escritura espera y sigue; en REPEATABLE READ aborta. Subir el nivel sin reintentos cambia un error silencioso por un error ruidoso, que es mejor, pero sigue siendo un error visible para el usuario.

Creer que SERIALIZABLE es "el nivel seguro y ya está". Es seguro y obliga a reintentar. Sin reintentos no es más seguro: solo falla más.

Dar por hecho que el nivel por omisión es el más estricto. Es READ COMMITTED, el segundo más débil. Compruébalo con SHOW transaction_isolation; antes de razonar sobre nada.

Mantener abierta una transacción mientras un humano decide. Bloquea filas, impide el VACUUM y no arregla nada que el bloqueo optimista no arregle mejor.

Acceder a las mismas filas en distinto orden en distintos sitios del código. Es la receta del interbloqueo. Adopta un orden canónico —por clave primaria ascendente— y respétalo en todas partes.

Diagnosticar un interbloqueo como un fallo de la base de datos. No lo es: la base de datos ha hecho exactamente lo que debía al detectarlo y romper el ciclo. El fallo está en el orden de acceso del código.

Ignorar el VACUUM hasta que duele. Vigila n_dead_tup y last_autovacuum en pg_stat_user_tables, y persigue las transacciones idle in transaction largas, que son la causa habitual de que autovacuum no pueda hacer su trabajo.

Usar VACUUM FULL en horario de servicio. Toma un bloqueo ACCESS EXCLUSIVE: bloquea hasta los SELECT. Es una operación de ventana de mantenimiento.

Consejo final: reproduce siempre el fallo antes de arreglarlo. Todos los fenómenos de esta lección se provocan con dos terminales psql en menos de un minuto. Un error de concurrencia que no sabes reproducir es un error que no sabes si has arreglado.

Para cada una de estas tres situaciones reales de BiblioRed, indica qué fenómeno de los cinco estudiados se está produciendo, por qué el nivel READ COMMITTED no lo evita, y cuál es la defensa más adecuada.

(a) El informe mensual de dirección muestra "312 préstamos vencidos" en el titular y, tres páginas más abajo, un desglose por sucursal que suma 315.

(b) El proceso nocturno que traslada ejemplares poco usados de Centro a Sur, y otro que los trae de Sur a Centro, se quedan colgados y uno de los dos muere con un error al cabo de un segundo.

(c) La sucursal Este debe conservar siempre al menos dos ejemplares del material 907 (bibliografía escolar). Tiene tres. Dos bibliotecarios tramitan a la vez un traslado de un ejemplar cada uno a otras sucursales; ambos comprueban que quedarían dos y ambos confirman. Al final quedan uno.

Usando dos terminales psql, reproduce el préstamo doble del ejemplar EJ-3081 del apartado 3. Después, reescribe la operación de préstamo de forma que sea imposible que dos mostradores presten el mismo ejemplar, sin cambiar el nivel de aislamiento y sin añadir columnas nuevas. Escribe la transacción completa e indica qué debe comprobar la aplicación y qué mensaje debe mostrar al operario.

La biblioteca quiere admitir acompañantes en los eventos: un socio puede inscribirse con hasta 3 acompañantes, y la columna inscripciones.plazas_ocupadas recoge el total de plazas que consume esa inscripción (1 + acompañantes). Además, cada evento reserva 5 de sus plazas para grupos escolares, que no pueden ocupar los socios.

Con estas dos reglas nuevas, decide cuál de los tres enfoques del apartado 15 emplearías, justifícalo, y escribe el SQL de la solución.

Solución 1

(a) Lectura no repetible.

El informe ejecuta dos consultas distintas dentro de la misma sesión. Bajo READ COMMITTED, cada instrucción toma una foto nueva, así que la segunda consulta ve tres préstamos que vencieron —o que se registraron— entre una y otra. Cada cifra es correcta en su instante; juntas son incoherentes.

Defensa: envolver el informe en una transacción con foto estable.

Para un informe largo, SERIALIZABLE READ ONLY DEFERRABLE es aún mejor: garantiza que no abortará a mitad.

(b) Interbloqueo.

Dos transacciones que actualizan el mismo conjunto de ejemplares en orden inverso. READ COMMITTED no tiene nada que ver: los interbloqueos son independientes del nivel de aislamiento, porque surgen del orden de adquisición de bloqueos, no de la visibilidad. El error al cabo de un segundo es exactamente deadlock_timeout haciendo su trabajo.

Defensa: orden canónico de acceso. Que los dos procesos bloqueen los ejemplares afectados con SELECT ... FOR UPDATE ... ORDER BY ejemplar_id al principio de la transacción, sea cual sea el sentido del traslado. Y reintento ante 40P01 como red de seguridad.

(c) Sesgo de escritura.

Los dos bibliotecarios leen el mismo conjunto (los tres ejemplares del material 907 en Este), cada uno decide que puede mover uno distinto, y cada uno escribe en una fila distinta. No hay conflicto de filas, así que ningún mecanismo basado en filas lo detecta. Ni READ COMMITTED ni REPEATABLE READ lo evitan: es el fenómeno del apartado 7.

Defensa, por orden de preferencia:

1. Materializar el conflicto: bloquear una fila común antes de decidir, por ejemplo la del material o la de la sucursal.

   BEGIN;
   SELECT 1 FROM materiales WHERE material_id = 907 FOR UPDATE;
   SELECT count(*) FROM ejemplares
   WHERE material_id = 907 AND sucursal_id = 4 AND estado <> 'baja';
   -- si count > 2, trasladar
   COMMIT;
   

2. SERIALIZABLE con reintento, que lo detecta en general sin tener que anticipar el caso.

Un CHECK no sirve aquí: la regla cuenta filas de otra tabla, y eso queda fuera de lo que una restricción declarativa de fila puede expresar.

Solución 2

La reproducción es la tabla del apartado 3. La solución sin cambiar nivel ni esquema, con bloqueo pesimista sobre el ejemplar:

La segunda sesión espera en el FOR UPDATE hasta el COMMIT de la primera, y entonces lee prestado, así que aborta con ROLLBACK.

Una variante aún mejor, que no necesita ni siquiera que la aplicación compruebe nada antes:

En la sesión perdedora:

Qué debe comprobar la aplicación: el número de filas afectadas por el UPDATE. Si es 0, ejecutar ROLLBACK.

Qué debe mostrar al operario: un mensaje que refleje la realidad y le diga qué hacer. Por ejemplo: "El ejemplar EJ-3081 ya no está disponible: otro mostrador lo ha prestado hace unos segundos. Consulte otros ejemplares del mismo título o cree una reserva." Nada de "Error de base de datos" ni de códigos numéricos: quien está en el mostrador tiene a un socio delante.

Nota adicional: la segunda variante es preferible a la primera porque hace la comprobación y la escritura en una sola instrucción, sostiene el bloqueo menos tiempo y no depende de que la aplicación recuerde comparar el estado leído.

Solución 3

Enfoque elegido: el 1, la restricción en la base de datos, adaptado a las dos reglas nuevas.

Justificación: las dos reglas nuevas —plazas por acompañantes y reserva escolar— hacen la lógica de aforo más compleja, y por tanto más fácil de implementar mal en algún punto del código. Cuanto más complicada es una regla, más argumentos hay para que viva en un solo sitio y no en cada programa que inserte inscripciones. Además, ambas reglas siguen expresándose sobre valores de una sola fila de eventos, que es justo lo que un CHECK puede comprobar.

La inscripción de Marta con dos acompañantes:

Si el evento tiene 25 plazas, 5 reservadas a escolares y ya hay 18 ocupadas por socios, la operación falla porque 18 + 3 > 20:

Observaciones sobre la solución:

• chk_plazas_ocupadas garantiza que la columna desnormalizada plazas_ocupadas no pueda desviarse de acompanantes. Es la disciplina de 05-04 aplicada: una columna calculada debe llevar su comprobación al lado. De hecho, aquí sería aún mejor declararla como columna generada (GENERATED ALWAYS AS (acompanantes + 1) STORED), como vimos en 04-04.
• El nombre de la restricción importa: chk_aforo_socios aparece literalmente en el mensaje de error, y la aplicación puede traducirlo a "No quedan plazas suficientes para usted y sus acompañantes".
• Sigue siendo necesaria una comprobación periódica de que plazas_ocupadas_total cuadra con la suma real de inscripciones confirmadas, exactamente como se explicó en 05-04 para las tablas de resumen.
• Si en el futuro apareciera una regla que abarcase varias filas de otras tablas —por ejemplo, "un socio no puede estar inscrito en dos eventos que se solapen"—, esa ya no cabría en un CHECK, y habría que ir al enfoque 3 con SERIALIZABLE y reintento. Conviene saber dónde está esa frontera.

Esta lección ha demostrado algo incómodo: el código correcto para un usuario puede ser código roto para dos. Las tres averías con las que empezamos —el ejemplar prestado dos veces, las 26 personas en un club de 25 sillas, el evento publicado sin ponentes— no venían de errores de programación en el sentido habitual. Venían de una suposición implícita que el mundo real no respeta: que entre leer y escribir no pasa nada.

Hemos provocado los cinco fenómenos con dos terminales y los hemos entendido de dentro afuera. La actualización perdida, cuya defensa más barata es poner la condición dentro del propio UPDATE y mirar cuántas filas se han visto afectadas. La lectura sucia, que en PostgreSQL simplemente no puede ocurrir. La lectura no repetible, que arruina todos los informes de más de una consulta y se cura con una línea. La lectura fantasma, que enseña la lección más importante de todas: contar antes de insertar no protege de nada, porque no se puede bloquear una fila que todavía no existe. Y el sesgo de escritura, que ocurre en niveles altos de aislamiento, no lo detecta ningún mecanismo basado en filas, y solo se resuelve con SERIALIZABLE o materializando el conflicto a mano.

Hemos visto la tabla canónica de los cuatro niveles y —más útil todavía— la tabla de lo que PostgreSQL hace en realidad: que no tiene READ UNCOMMITTED de verdad, que su REPEATABLE READ es snapshot isolation y prohíbe fantasmas que el estándar permite, que su nivel por omisión es el segundo más débil, y que el salto de garantía real está en SERIALIZABLE, que se paga con transacciones abortadas y, por tanto, con lógica de reintento obligatoria.

Después hemos bajado a la maquinaria: los bloqueos compartidos y exclusivos, la granularidad y el hecho de que PostgreSQL bloquea filas y nunca escala; el bloqueo en dos fases, que explica por qué una transacción larga es un problema colectivo; los bloqueos explícitos, con FOR UPDATE para proteger una lectura, NOWAIT para responder rápido en lugar de congelar la pantalla y SKIP LOCKED para repartir una cola de trabajo entre varios procesos. Hemos abierto el MVCC hasta ver el xmin, el xmax y el ctid cambiando ante nuestros ojos, y hemos entendido su factura: las versiones muertas, el VACUUM que las limpia y lo que ocurre cuando no llega a tiempo. Hemos provocado un interbloqueo, leído su mensaje real, dibujado su grafo de espera y aprendido que la defensa fundamental cabe en una frase: accede siempre a las filas en el mismo orden.

Y hemos resuelto el problema de la última plaza tres veces, para descubrir que las tres soluciones funcionan y solo una protege de verdad. El bloqueo pesimista y el SERIALIZABLE viven en el código de la aplicación, y por tanto se pierden en cuanto otro programa toque la base de datos. La restricción chk_aforo vive en el esquema, se cumple siempre y se documenta sola. Es la misma conclusión a la que llegamos en 04-04 y en 05-04, y ya podemos enunciarla como principio: una regla que no puede romperse debe estar donde no se pueda saltar.

Con esto, el sistema de BiblioRed ya es correcto bajo concurrencia. Falta que sea rápido. Porque hay un problema pendiente desde la primera línea del módulo: el listado de préstamos vencidos por sucursal, ese mismo que acabamos de usar en los ejercicios, tarda catorce segundos desde que la tabla prestamos superó los dos millones de filas. Y catorce segundos en un mostrador con un socio delante es una eternidad.

La lección 06-03, Índices y Optimización de Consultas, es la que arregla eso —y es la lección a la que 05-04 nos remitió explícitamente cuando dijimos que los índices son lo primero que hay que probar antes de desnormalizar. Veremos qué es un índice y cómo un B-tree convierte millones de comparaciones en cuatro lecturas; qué cuesta un índice, porque no son gratis y por eso no se indexa todo; los índices únicos, compuestos, parciales y de expresión, con la regla del prefijo más a la izquierda que explica por qué el orden de las columnas lo cambia todo; qué se indexa y qué no, incluida la advertencia de que PostgreSQL no indexa solo las claves ajenas; cómo se lee un plan de ejecución de EXPLAIN ANALYZE línea a línea y qué significa que las filas estimadas no se parezcan a las reales; y el caso completo de esos catorce segundos, con su plan antes, su diagnóstico, el índice que lo arregla y su plan después.