Hola Habr! Con este artículo comienzo un conjunto de series (¿o una serie de conjuntos? - En una palabra, la idea es grandiosa) sobre la estructura interna de PostgreSQL.

El material se basará en cursos de capacitación (en ruso) sobre administración que Pavel pluzanov y yo estamos creando. No a todos les gusta ver videos (definitivamente no), y leer diapositivas, incluso con comentarios, no es nada bueno.

Desafortunadamente, el único curso disponible en inglés en este momento es Introducción de 2 días a PostgreSQL 11 .

Por supuesto, los artículos no serán exactamente iguales al contenido de los cursos. Solo hablaré sobre cómo está organizado todo, omitiendo la administración en sí, pero intentaré hacerlo con más detalle y más a fondo. Y creo que este conocimiento es tan útil para un desarrollador de aplicaciones como para un administrador.

Apuntaré a aquellos que ya tienen experiencia en el uso de PostgreSQL y al menos en general entienden qué es qué. El texto será demasiado difícil para los principiantes. Por ejemplo, no diré una palabra sobre cómo instalar PostgreSQL y ejecutar psql.

El material en cuestión no varía mucho de una versión a otra, pero utilizaré el 11º PostgreSQL de vainilla actual.

La primera serie trata temas relacionados con el aislamiento y la concurrencia de múltiples versiones, y el plan de la serie es el siguiente:

1. Aislamiento tal como lo entiende el estándar y PostgreSQL (este artículo).
2. Bifurcaciones, archivos, páginas : lo que está sucediendo a nivel físico.
3. Versiones de fila , transacciones virtuales y subtransacciones.
4. Instantáneas de datos y la visibilidad de las versiones de fila; El horizonte de eventos.
5. Vacío en la página y actualizaciones CALIENTES .
6. Vacío normal
7. Autovacuum .
8. Identificación de la transacción envolvente y congelación .

¡Vamos!

Y antes de comenzar, me gustaría agradecer a Elena Indrupskaya por traducir los artículos al inglés.

¿Qué es el aislamiento y por qué es importante?

Probablemente, todos al menos son conscientes de la existencia de transacciones, se han encontrado con la abreviatura ACID y han oído hablar de los niveles de aislamiento. Pero todavía nos enfrentamos a la opinión de que esto pertenece a la teoría, que no es necesaria en la práctica. Por lo tanto, pasaré un tiempo tratando de explicar por qué esto es realmente importante.

Es poco probable que esté satisfecho si una aplicación obtiene datos incorrectos de la base de datos o si la aplicación escribe datos incorrectos en la base de datos.

Pero, ¿qué son los datos "correctos"? Se sabe que las restricciones de integridad , como NOT NULL o UNIQUE, se pueden crear en el nivel de la base de datos. Si los datos siempre cumplen con las restricciones de integridad (y esto es así porque el DBMS lo garantiza), entonces son integrales.

¿Son correctos e integrales las mismas cosas? No exactamente No todas las restricciones se pueden especificar a nivel de base de datos. Algunas de las restricciones son demasiado complicadas, por ejemplo, que cubren varias tablas a la vez. E incluso si una restricción en general pudiera haberse definido en la base de datos, pero por alguna razón no lo fue, no significa que la restricción pueda ser violada.

Entonces, la corrección es más fuerte que la integridad , pero no sabemos exactamente lo que esto significa. No tenemos más que admitir que el "estándar de oro" de la corrección es una aplicación que, como nos gustaría creer, está escrita correctamente y nunca funciona mal. En cualquier caso, si una aplicación no viola la integridad, pero viola la corrección, el DBMS no lo sabrá y no atrapará la aplicación "con las manos en la masa".

Además, usaremos el término consistencia para referirnos a la corrección.

Sin embargo, supongamos que una aplicación ejecuta solo secuencias correctas de operadores. ¿Cuál es el papel de DBMS si la aplicación es correcta como es?

Primero, resulta que una secuencia correcta de operadores puede romper temporalmente la consistencia de los datos y, curiosamente, esto es normal. Un ejemplo trillado pero claro es una transferencia de fondos de una cuenta a otra. La regla de coherencia puede sonar así: una transferencia nunca cambia la cantidad total de dinero en las cuentas (esta regla es bastante difícil de especificar en SQL como una restricción de integridad, por lo que existe a nivel de aplicación y es invisible para el DBMS). Una transferencia consta de dos operaciones: la primera reduce los fondos en una cuenta y la segunda, los aumenta en la otra. La primera operación rompe la consistencia de los datos, mientras que la segunda la restaura.

Un buen ejercicio es implementar la regla anterior a nivel de restricciones de integridad.

¿Qué pasa si se realiza la primera operación y la segunda no? De hecho, sin mucho inconveniente: durante la segunda operación puede ocurrir una falla eléctrica, un bloqueo del servidor, división por cero, lo que sea. Está claro que la consistencia se romperá, y esto no se puede permitir. En general, es posible resolver tales problemas a nivel de aplicación, pero a costa de enormes esfuerzos; sin embargo, afortunadamente, no es necesario: esto lo hace el DBMS. Pero para hacer esto, el DBMS debe saber que las dos operaciones son un todo indivisible. Es decir, una transacción .

Resulta interesante: como el DBMS sabe que las operaciones constituyen una transacción, ayuda a mantener la coherencia al garantizar que las transacciones sean atómicas, y lo hace sin saber nada sobre reglas de coherencia específicas.

Pero hay un segundo punto, más sutil. Tan pronto como aparecen varias transacciones simultáneas en el sistema, que son absolutamente correctas por separado, es posible que no funcionen correctamente juntas. Esto se debe a que el orden de las operaciones está mezclado: no se puede suponer que todas las operaciones de una transacción se realizan primero y luego todas las operaciones de la otra.

Una nota sobre simultaneidad. De hecho, las transacciones pueden ejecutarse simultáneamente en un sistema con un procesador multinúcleo, matriz de discos, etc. Pero el mismo razonamiento es válido para un servidor que ejecuta comandos secuencialmente, en un modo de tiempo compartido: durante ciertos ciclos de reloj se ejecuta una transacción, y durante los siguientes ciclos determinados se ejecuta la otra. A veces, el término ejecución concurrente se usa para una generalización.

Las situaciones en que las transacciones correctas funcionan juntas incorrectamente se denominan anomalías de ejecución concurrente.

Para un ejemplo simple: si una aplicación quiere obtener datos correctos de la base de datos, no debe, al menos, ver los cambios de otras transacciones no confirmadas. De lo contrario, no solo puede obtener datos inconsistentes, sino también ver algo que nunca ha estado en la base de datos (si la transacción se cancela). Esta anomalía se llama lectura sucia .

Hay otras anomalías más complejas, que trataremos un poco más adelante.

Ciertamente es imposible evitar la ejecución concurrente: de lo contrario, ¿de qué tipo de rendimiento podemos hablar? Pero tampoco puedes trabajar con datos incorrectos.

Y nuevamente el DBMS viene al rescate. Puede realizar transacciones ejecutadas de forma secuencial, como una tras otra. En otras palabras, aislados unos de otros. En realidad, el DBMS puede realizar operaciones confusas, pero garantizar que el resultado de una ejecución concurrente sea el mismo que el resultado de algunas de las posibles ejecuciones secuenciales. Y esto elimina cualquier posible anomalía.

Entonces llegamos a la definición:

Una transacción es un conjunto de operaciones realizadas por una aplicación que transfiere una base de datos de un estado correcto a otro estado correcto (consistencia), siempre que la transacción se complete (atomicidad) y sin interferencia de otras transacciones (aislamiento).

Esta definición une las tres primeras letras del acrónimo ACID. Están tan estrechamente relacionados entre sí que no tiene sentido considerar uno sin los demás. De hecho, también es difícil separar la letra D (durabilidad). De hecho, cuando un sistema falla, todavía tiene cambios de transacciones no confirmadas, con lo que debe hacer algo para restaurar la consistencia de los datos.

Todo hubiera estado bien, pero la implementación del aislamiento completo es una tarea técnicamente difícil que implica una reducción en el rendimiento del sistema. Por lo tanto, en la práctica muy a menudo (no siempre, pero casi siempre) se utiliza el aislamiento debilitado, lo que evita algunas, pero no todas las anomalías. Esto significa que una parte del trabajo para garantizar la corrección de datos recae en la aplicación. Por esta misma razón, es muy importante comprender qué nivel de aislamiento se utiliza en el sistema, qué garantiza que ofrece y qué no, y cómo escribir el código correcto en tales condiciones.

Niveles de aislamiento y anomalías en el estándar SQL

El estándar SQL ha descrito durante mucho tiempo cuatro niveles de aislamiento. Estos niveles se definen enumerando las anomalías permitidas o no permitidas cuando las transacciones se ejecutan simultáneamente en este nivel. Por lo tanto, para hablar sobre estos niveles, es necesario conocer las anomalías.

Enfatizo que en esta parte estamos hablando del estándar, es decir, de una teoría, en la cual la práctica se basa significativamente, pero de la que al mismo tiempo diverge significativamente. Por lo tanto, todos los ejemplos aquí son especulativos. Utilizarán las mismas operaciones en las cuentas de los clientes: esto es bastante demostrativo, aunque, es cierto, no tiene nada que ver con la organización de las operaciones bancarias en realidad.

Actualización de pérdida

Comencemos con la actualización perdida . Esta anomalía ocurre cuando dos transacciones leen la misma fila de la tabla, luego una transacción actualiza esa fila, y luego la segunda transacción también actualiza la misma fila sin tener en cuenta los cambios realizados por la primera transacción.

Por ejemplo, dos transacciones van a aumentar la cantidad en la misma cuenta en ₽100 (₽ es el signo de moneda para el rublo ruso). La primera transacción lee el valor actual (₽1000) y luego la segunda transacción lee el mismo valor. La primera transacción aumenta la cantidad (esto da ₽1100) y escribe este valor. La segunda transacción actúa de la misma manera: obtiene el mismo ₽1100 y escribe este valor. Como resultado, el cliente perdió ₽100.

El estándar no permite actualizaciones perdidas en ningún nivel de aislamiento.

Lectura sucia y lectura no comprometida

Una lectura sucia es lo que ya conocemos. Esta anomalía ocurre cuando una transacción lee cambios que aún no se han confirmado por otra transacción.

Por ejemplo, la primera transacción transfiere todo el dinero de la cuenta del cliente a otra cuenta, pero no confirma el cambio. Otra transacción lee el saldo de la cuenta, para obtener ₽0, y se niega a retirar efectivo al cliente, aunque la primera transacción cancela y revierte sus cambios, por lo que el valor de 0 nunca ha existido en la base de datos.

El estándar permite lecturas sucias en el nivel de lectura no comprometida.

Lectura no repetible y lectura comprometida

Una anomalía de lectura no repetible ocurre cuando una transacción lee la misma fila dos veces, y entre las lecturas, la segunda transacción modifica (o elimina) esa fila y confirma los cambios. Entonces, la primera transacción obtendrá resultados diferentes.

Por ejemplo, permita que una regla de coherencia prohíba los importes negativos en las cuentas de los clientes . La primera transacción reducirá el monto de la cuenta en ₽100. Comprueba el valor actual, obtiene ₽1000 y decide que la disminución es posible. Al mismo tiempo, la segunda transacción reduce el monto de la cuenta a cero y confirma los cambios. Si la primera transacción ahora vuelve a verificar la cantidad, obtendría ₽0 (pero ya ha decidido reducir el valor y la cuenta "entra en rojo").

El estándar permite lecturas no repetibles en los niveles Lectura no confirmada y Lectura confirmada. Pero Read Committed no permite lecturas sucias.

Lectura fantasma y lectura repetible

Una lectura fantasma ocurre cuando una transacción lee un conjunto de filas con la misma condición dos veces, y entre las lecturas, la segunda transacción agrega filas que cumplen esa condición (y confirma los cambios). Luego, la primera transacción obtendrá un conjunto diferente de filas.

Por ejemplo, permita que una regla de coherencia evite que un cliente tenga más de 3 cuentas . La primera transacción va a abrir una nueva cuenta, verifica el número actual de cuentas (digamos, 2) y decide que es posible abrir. Al mismo tiempo, la segunda transacción también abre una nueva cuenta para el cliente y confirma los cambios. Ahora, si la primera transacción volvió a verificar el número, obtendría 3 (pero ya está abriendo otra cuenta y el cliente parece tener 4 de ellas).

El estándar permite lecturas fantasmas en los niveles de Lectura no confirmada, Lectura comprometida y Lectura repetible. Sin embargo, la lectura no repetible no está permitida en el nivel de Lectura repetible.

La ausencia de anomalías y serializables

El estándar define un nivel más, Serializable, que no permite ninguna anomalía. Y esto no es lo mismo que prohibir actualizaciones perdidas y lecturas sucias, no repetibles o fantasmas.

La cuestión es que hay muchas más anomalías conocidas que las enumeradas en el estándar y también un número desconocido de otras aún desconocidas.

El nivel serializable debe evitar absolutamente todas las anomalías. Significa que a este nivel, un desarrollador de aplicaciones no necesita pensar en la ejecución concurrente. Si las transacciones realizan una secuencia correcta de operadores que trabajan por separado, los datos serán coherentes también cuando estas transacciones se ejecuten simultáneamente.

Tabla resumen

Ahora podemos proporcionar una tabla conocida. Pero aquí se agrega la última columna, que falta en el estándar, para mayor claridad.

¿Por qué exactamente estas anomalías?

¿Por qué el estándar enumera solo algunas de las muchas anomalías posibles, y por qué son exactamente estas?

Nadie parece saberlo con certeza. Pero aquí la práctica está evidentemente por delante de la teoría, por lo que es posible que en ese momento (del estándar SQL: 92) no se pensara en otras anomalías.

Además, se supuso que el aislamiento debe construirse sobre cerraduras. La idea detrás del ampliamente utilizado Protocolo de bloqueo de dos fases (2PL) es que durante la ejecución, una transacción bloquea las filas con las que está trabajando y libera los bloqueos al finalizar. Simplificando considerablemente, cuanto más bloqueos adquiere una transacción, mejor se aísla de otras transacciones. Pero el rendimiento del sistema también sufre más, porque en lugar de trabajar juntos, las transacciones comienzan a ponerse en cola para las mismas filas.

Mi sensación es que es solo la cantidad de bloqueos necesarios, lo que explica la diferencia entre los niveles de aislamiento del estándar.

Si una transacción bloquea las filas que se modificarán desde la actualización, pero no desde la lectura, obtenemos el nivel Leer no confirmado: no se permiten cambios perdidos, pero se pueden leer los datos no confirmados.

Si una transacción bloquea las filas para modificarlas tanto de lectura como de actualización, obtenemos el nivel de lectura confirmada: no puede leer datos no confirmados, pero puede obtener un valor diferente (lectura no repetible) cuando accede a la fila nuevamente.

Si una transacción bloquea las filas para que se lean y se modifiquen, tanto de lectura como de actualización, obtenemos el nivel de lectura repetible: volver a leer la fila devolverá el mismo valor.

Pero hay un problema con Serializable: no puede bloquear una fila que aún no existe. Por lo tanto, todavía es posible una lectura fantasma: otra transacción puede agregar (pero no eliminar) una fila que cumpla las condiciones de una consulta ejecutada previamente, y esa fila se incluirá en la re-selección.

Por lo tanto, para implementar el nivel Serializable, los bloqueos normales no son suficientes: debe bloquear las condiciones (predicados) en lugar de las filas. Por lo tanto, tales bloqueos se llamaron predicado . Se propusieron en 1976, pero su aplicabilidad práctica está limitada por condiciones bastante simples para las cuales está claro cómo unir dos predicados diferentes. Hasta donde yo sé, tales bloqueos nunca se han implementado en ningún sistema hasta ahora.

Niveles de aislamiento en PostgreSQL

Con el tiempo, los protocolos de gestión de transacciones basados ​​en bloqueos fueron reemplazados por el protocolo de aislamiento de instantáneas (SI). Su idea es que cada transacción funcione con una instantánea coherente de los datos en un momento determinado, y solo esos cambios se introducen en la instantánea que se confirmaron antes de crearse.

Este aislamiento evita automáticamente las lecturas sucias. Formalmente, puede especificar el nivel de lectura no confirmada en PostgreSQL, pero funcionará exactamente de la misma manera que lectura confirmada. Por lo tanto, además no hablaremos sobre el nivel Leer no comprometido en absoluto.

PostgreSQL implementa una variante multiversion de este protocolo. La idea de concurrencia multiversion es que varias versiones de la misma fila pueden coexistir en un DBMS. Esto le permite crear una instantánea de los datos usando versiones existentes y usar un mínimo de bloqueos. En realidad, solo los cambios posteriores en la misma fila están bloqueados. Todas las demás operaciones se realizan simultáneamente: las transacciones de escritura nunca bloquean las transacciones de solo lectura, y las transacciones de solo lectura nunca bloquean nada.

Al usar instantáneas de datos, el aislamiento en PostgreSQL es más estricto de lo requerido por el estándar: el nivel de lectura repetible no solo permite lecturas no repetibles, sino también lecturas fantasmas (aunque no proporciona aislamiento completo). Y esto se logra sin pérdida de eficiencia.

Hablaremos en los próximos artículos de cómo se implementa la concurrencia multiversion "bajo el capó", y ahora veremos en detalle en cada uno de los tres niveles con el ojo del usuario (como saben, lo más interesante está oculto detrás de "otras anomalías"). "). Para hacer esto, creemos una tabla de cuentas. Alice y Bob tienen ₽1000 cada uno, pero Bob tiene dos cuentas abiertas:

=> CREATE TABLE accounts( id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, number text UNIQUE, client text, amount numeric ); => INSERT INTO accounts VALUES (1, '1001', 'alice', 1000.00), (2, '2001', 'bob', 100.00), (3, '2002', 'bob', 900.00); 

Leer comprometido

La ausencia de lectura sucia

Es fácil asegurarse de que los datos sucios no se puedan leer. Comenzamos la transacción. Por defecto, usará el nivel de aislamiento de lectura confirmada:

 => BEGIN; => SHOW transaction_isolation; 

  transaction_isolation ----------------------- read committed (1 row) 

Más precisamente, el nivel predeterminado lo establece el parámetro, que se puede cambiar si es necesario:

 => SHOW default_transaction_isolation; 

  default_transaction_isolation ------------------------------- read committed (1 row) 

Por lo tanto, en una transacción abierta, retiramos fondos de la cuenta, pero no comprometemos los cambios. La transacción ve sus propios cambios:

 => UPDATE accounts SET amount = amount - 200 WHERE id = 1; => SELECT * FROM accounts WHERE client = 'alice'; 

  id | number | client | amount ----+--------+--------+-------- 1 | 1001 | alice | 800.00 (1 row) 

En la segunda sesión, comenzaremos otra transacción con el mismo nivel de lectura confirmada. Para distinguir entre las transacciones, los comandos de la segunda transacción se sangrarán y marcarán con una barra.

Para repetir los comandos anteriores (lo cual es útil), debe abrir dos terminales y ejecutar psql en cada uno. En el primer terminal, puede ingresar los comandos de una transacción, y en el segundo, los de la otra.

 | => BEGIN; | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+--------- | 1 | 1001 | alice | 1000.00 | (1 row) 

Como se esperaba, la otra transacción no ve cambios no confirmados ya que no se permiten lecturas sucias.

Lectura no repetible

Ahora deje que la primera transacción confirme los cambios y la segunda vuelva a ejecutar la misma consulta.

 => COMMIT; 

 | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

 | => COMMIT; 

La consulta ya obtiene datos nuevos, y esta es la anomalía de lectura no repetible , que se permite en el nivel de lectura confirmada .

Conclusión práctica : en una transacción, no puede tomar decisiones basadas en datos leídos por un operador anterior porque las cosas pueden cambiar entre la ejecución de los operadores. Aquí hay un ejemplo cuyas variaciones ocurren con tanta frecuencia en el código de la aplicación que se considera un antipatrón clásico:

  IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN UPDATE accounts SET amount = amount - 1000 WHERE id = 1; END IF; 

Durante el tiempo que transcurre entre la verificación y la actualización, otras transacciones pueden cambiar el estado de la cuenta de cualquier manera, por lo que dicho "cheque" no se protege de la nada. Es conveniente imaginar que entre los operadores de una transacción, cualquier otro operador de otras transacciones puede "encajar", por ejemplo, de la siguiente manera:

  IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN ----- | UPDATE accounts SET amount = amount - 200 WHERE id = 1; | COMMIT; ----- UPDATE accounts SET amount = amount - 1000 WHERE id = 1; END IF; 

Si todo se puede estropear reorganizando los operadores, entonces el código se escribe incorrectamente. Y no se engañe a sí mismo de que tal coincidencia no sucederá, seguramente lo hará.

Pero, ¿cómo escribir el código correctamente? Las opciones tienden a ser las siguientes:

• No para escribir código.
  Esto no es una broma. Por ejemplo, en este caso, la comprobación se convierte fácilmente en una restricción de integridad:
  ALTER TABLE accounts ADD CHECK amount >= 0;
  No se necesitan verificaciones ahora: simplemente realice la operación y, si es necesario, maneje la excepción que ocurrirá si se intenta una violación de integridad.
  
• Para usar una sola instrucción SQL.
  Surgen problemas de consistencia ya que en el intervalo de tiempo entre operadores se puede completar otra transacción, lo que cambiará los datos visibles. Y si hay un operador, entonces no hay intervalos de tiempo.
  PostgreSQL tiene suficientes técnicas para resolver problemas complejos con una declaración SQL. Observemos las expresiones de tabla comunes (CTE), en las que, entre el resto, puede usar las instrucciones INSERT / UPDATE / DELETE, así como la instrucción INSERT ON CONFLICT, que implementa la lógica de "insertar, pero si la fila ya existe, actualizar "en una declaración.
  
• Cerraduras personalizadas.
  El último recurso es establecer manualmente un bloqueo exclusivo en todas las filas necesarias (SELECCIONAR PARA ACTUALIZAR) o incluso en toda la tabla (TABLA DE BLOQUEO). Esto siempre funciona, pero anula los beneficios de la concurrencia multiversion: algunas operaciones se ejecutarán secuencialmente en lugar de la ejecución concurrente.
  

Lectura inconsistente

Antes de pasar al siguiente nivel de aislamiento, debes admitir que no todo es tan simple como parece. La implementación de PostgreSQL es tal que permite otras anomalías menos conocidas que no están reguladas por el estándar.

Supongamos que la primera transacción inició la transferencia de fondos de una cuenta de Bob a la otra:

 => BEGIN; => UPDATE accounts SET amount = amount - 100 WHERE id = 2; 

Al mismo tiempo, otra transacción cuenta el saldo de Bob, y el cálculo se realiza en un ciclo sobre todas las cuentas de Bob. De hecho, la transacción comienza con la primera cuenta (y, obviamente, ve el estado anterior):

 | => BEGIN; | => SELECT amount FROM accounts WHERE id = 2; 

 | amount | -------- | 100.00 | (1 row) 

En este momento, la primera transacción se completa con éxito:

 => UPDATE accounts SET amount = amount + 100 WHERE id = 3; => COMMIT; 

Y el otro lee el estado de la segunda cuenta (y ya ve el nuevo valor):

 | => SELECT amount FROM accounts WHERE id = 3; 

 | amount | --------- | 1000.00 | (1 row) 

 | => COMMIT; 

Por lo tanto, la segunda transacción obtuvo ₽1100 en total, es decir, datos incorrectos. Y esta es una anomalía de lectura inconsistente .

¿Cómo evitar tal anomalía mientras se mantiene en el nivel de lectura comprometida? Por supuesto, use un operador. Por ejemplo:

  SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

Hasta aquí, afirmé que la visibilidad de los datos solo podía cambiar entre operadores, pero ¿es eso tan obvio? Y si la consulta lleva mucho tiempo, ¿puede ver una parte de los datos en un estado y una parte en otro?

Vamos a ver Una forma conveniente de hacer esto es insertar un retraso forzado en el operador llamando a la función pg_sleep. Su parámetro especifica el tiempo de retraso en segundos.

 => SELECT amount, pg_sleep(2) FROM accounts WHERE client = 'bob'; 

Mientras se ejecuta este operador, transferimos los fondos nuevamente en otra transacción:

 | => BEGIN; | => UPDATE accounts SET amount = amount + 100 WHERE id = 2; | => UPDATE accounts SET amount = amount - 100 WHERE id = 3; | => COMMIT; 

El resultado muestra que el operador ve los datos en el estado que tenía en el momento en que comenzó la ejecución del operador. Esto es indudablemente correcto.

  amount | pg_sleep ---------+---------- 0.00 | 1000.00 | (2 rows) 

Pero tampoco es tan simple aquí. PostgreSQL le permite definir funciones, y las funciones tienen el concepto de una categoría de volatilidad . Si se llama a una función VOLÁTIL en una consulta y se ejecuta otra consulta en esa función, la consulta dentro de la función verá datos que son inconsistentes con los datos en la consulta principal.

 => CREATE FUNCTION get_amount(id integer) RETURNS numeric AS $$ SELECT amount FROM accounts a WHERE a.id = get_amount.id; $$ VOLATILE LANGUAGE sql; 

 => SELECT get_amount(id), pg_sleep(2) FROM accounts WHERE client = 'bob'; 

 | => BEGIN; | => UPDATE accounts SET amount = amount + 100 WHERE id = 2; | => UPDATE accounts SET amount = amount - 100 WHERE id = 3; | => COMMIT; 

En este caso, obtenemos datos incorrectos: se pierden ₽100:

  get_amount | pg_sleep ------------+---------- 100.00 | 800.00 | (2 rows) 

Destaco que este efecto solo es posible en el nivel de aislamiento de lectura comprometida y solo con las funciones VOLÁTILES. El problema es que, por defecto, se utilizan exactamente este nivel de aislamiento y esta categoría de volatilidad. ¡No caigas en la trampa!

Lectura inconsistente a cambio de cambios perdidos

También podemos obtener una lectura inconsistente dentro de un solo operador durante una actualización, aunque de una manera algo inesperada.

Veamos qué sucede cuando dos transacciones intentan modificar la misma fila. Ahora Bob tiene ₽1000 en dos cuentas:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 (2 rows) 

Comenzamos una transacción que reduce el saldo de Bob:

 => BEGIN; => UPDATE accounts SET amount = amount - 100 WHERE id = 3; 

Al mismo tiempo, en otra transacción, el interés se acumula en todas las cuentas de clientes con un saldo total igual o superior a ₽1,000:

 | => UPDATE accounts SET amount = amount * 1.01 | WHERE client IN ( | SELECT client | FROM accounts | GROUP BY client | HAVING sum(amount) >= 1000 | ); 

La ejecución del operador ACTUALIZACIÓN consta de dos partes. Primero, se ejecuta SELECT, que selecciona las filas para actualizar que cumplan con la condición adecuada. Debido a que el cambio en la primera transacción no se confirma, la segunda transacción no puede verlo y el cambio no afecta la selección de filas para la acumulación de intereses. Bueno, entonces, las cuentas de Bob cumplen la condición y una vez que se ejecuta la actualización, su saldo debería aumentar en ₽10.

La segunda etapa de la ejecución es actualizar las filas seleccionadas una por una. Aquí la segunda transacción se ve obligada a "bloquearse" porque la fila con id = 3 ya está bloqueada por la primera transacción.

Mientras tanto, la primera transacción confirma los cambios:

 => COMMIT; 

¿Cuál será el resultado?

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+---------- 2 | 2001 | bob | 202.0000 3 | 2002 | bob | 707.0000 (2 rows) 

Bueno, por un lado, el comando ACTUALIZAR no debería ver los cambios de la segunda transacción. Pero, por otro lado, no debe perder los cambios comprometidos en la segunda transacción.

Una vez que se libera el bloqueo, ACTUALIZAR vuelve a leer la fila que está tratando de actualizar (pero solo esta). Como resultado, Bob acumuló ₽9, basado en la cantidad de ₽900. Pero si Bob tuviera ₽900, sus cuentas no deberían haber estado en la selección en absoluto.

Entonces, la transacción obtiene datos incorrectos: algunas de las filas son visibles en un punto en el tiempo y otras en otro. En lugar de una actualización perdida, nuevamente tenemos la anomalía de la lectura inconsistente .

Los lectores atentos notan que con un poco de ayuda de la aplicación puede obtener una actualización perdida incluso en el nivel de lectura comprometida. Por ejemplo:

  x := (SELECT amount FROM accounts WHERE id = 1); UPDATE accounts SET amount = x + 100 WHERE id = 1; 

La base de datos no tiene la culpa: obtiene dos declaraciones SQL y no sabe nada sobre el hecho de que el valor de x + 100 está de alguna manera relacionado con el monto de las cuentas. Evite escribir código de esa manera.

Lectura repetible

La ausencia de lecturas no repetibles y fantasmas

El mismo nombre del nivel de aislamiento supone que la lectura es repetible. Vamos a comprobarlo y, al mismo tiempo, asegúrese de que no haya lecturas fantasmas. Para hacer esto, en la primera transacción, revertimos las cuentas de Bob a su estado anterior y creamos una nueva cuenta para Charlie:

 => BEGIN; => UPDATE accounts SET amount = 200.00 WHERE id = 2; => UPDATE accounts SET amount = 800.00 WHERE id = 3; => INSERT INTO accounts VALUES (4, '3001', 'charlie', 100.00); => SELECT * FROM accounts ORDER BY id; 

  id | number | client | amount ----+--------+---------+-------- 1 | 1001 | alice | 800.00 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 4 | 3001 | charlie | 100.00 (4 rows) 

En la segunda sesión, comenzamos la transacción con el nivel de Lectura repetible al especificarlo en el comando BEGIN (el nivel de la primera transacción no es esencial).

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT * FROM accounts ORDER BY id; 

 | id | number | client | amount | ----+--------+--------+---------- | 1 | 1001 | alice | 800.00 | 2 | 2001 | bob | 202.0000 | 3 | 2002 | bob | 707.0000 | (3 rows) 

Ahora la primera transacción confirma los cambios y la segunda vuelve a ejecutar la misma consulta.

 => COMMIT; 

 | => SELECT * FROM accounts ORDER BY id; 

 | id | number | client | amount | ----+--------+--------+---------- | 1 | 1001 | alice | 800.00 | 2 | 2001 | bob | 202.0000 | 3 | 2002 | bob | 707.0000 | (3 rows) 

 | => COMMIT; 

La segunda transacción todavía ve exactamente los mismos datos que al principio: no hay cambios en las filas existentes o en las nuevas filas visibles.

En este nivel, puede evitar preocuparse por algo que pueda cambiar entre dos operadores.

Error de serialización a cambio de cambios perdidos

Hemos discutido anteriormente que cuando dos transacciones actualizan la misma fila en el nivel de lectura confirmada, puede ocurrir una anomalía de lectura inconsistente. Esto se debe a que la transacción en espera vuelve a leer la fila bloqueada y, por lo tanto, no la ve en el mismo momento en el tiempo que las otras filas.

En el nivel de lectura repetible, esta anomalía no está permitida, pero si ocurre, no se puede hacer nada, por lo que la transacción termina con un error de serialización. Vamos a comprobarlo repitiendo el mismo escenario con acumulación de intereses:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 (2 rows) 

 => BEGIN; => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; 

 | => BEGIN ISOLATION LEVEL REPEATABLE READ;<span/> | => UPDATE accounts SET amount = amount * 1.01<span/> | WHERE client IN (<span/> | SELECT client<span/> | FROM accounts<span/> | GROUP BY client<span/> | HAVING sum(amount) >= 1000<span/> | );<span/> 

 => COMMIT; 

 | ERROR: could not serialize access due to concurrent update 

 | => ROLLBACK; 

Los datos permanecieron consistentes:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 700.00 (2 rows) 

El mismo error ocurrirá en el caso de cualquier otro cambio competitivo de una fila, incluso si las columnas de nuestra preocupación no se cambiaron realmente.

Conclusión práctica : si su aplicación utiliza el nivel de aislamiento de lectura repetible para las transacciones de escritura, debe estar lista para repetir transacciones que terminaron con un error de serialización. Para transacciones de solo lectura, este resultado no es posible.

Escritura inconsistente

Por lo tanto, en PostgreSQL, en el nivel de aislamiento de lectura repetible, se evitan todas las anomalías descritas en el estándar. Pero no todas las anomalías en general. Resulta que hay exactamente dos anomalías que todavía son posibles. (Esto es cierto no solo para PostgreSQL, sino también para otras implementaciones de Snapshot Isolation).

La primera de estas anomalías es una escritura inconsistente .

Deje que se cumpla la siguiente regla de coherencia: se permiten cantidades negativas en las cuentas de los clientes si la cantidad total en todas las cuentas de ese cliente sigue siendo no negativa .

La primera transacción obtiene el monto en las cuentas de Bob: ₽900.

 => BEGIN ISOLATION LEVEL REPEATABLE READ; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

  sum -------- 900.00 (1 row) 

La segunda transacción obtiene la misma cantidad.

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

 | sum | -------- | 900.00 | (1 row) 

La primera transacción cree legítimamente que el monto de una de las cuentas se puede reducir en ₽600.

 => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; 

Y la segunda transacción llega a la misma conclusión. Pero reduce otra cuenta:

 | => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; | => COMMIT; 

 => COMMIT; => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+--------- 2 | 2001 | bob | -400.00 3 | 2002 | bob | 100.00 (2 rows) 

Logramos hacer que el saldo de Bob pasara a rojo, aunque cada transacción funciona correctamente solo.

Anomalía de transacción de solo lectura

Esta es la segunda y última de las anomalías posibles en el nivel de lectura repetible. Para demostrarlo, necesitará tres transacciones, dos de las cuales cambiarán los datos y la tercera solo lo leerá.

Pero primero restauremos el estado de las cuentas de Bob:

 => UPDATE accounts SET amount = 900.00 WHERE id = 2; => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 3 | 2002 | bob | 100.00 2 | 2001 | bob | 900.00 (2 rows) 

En la primera transacción, se acumulan intereses sobre el monto disponible en todas las cuentas de Bob. El interés se acredita a una de sus cuentas:

 => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 1 => UPDATE accounts SET amount = amount + ( SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01 WHERE id = 2; 

Luego, otra transacción retira dinero de la cuenta de otro Bob y confirma sus cambios:

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 2 | => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; | => COMMIT; 

Si la primera transacción se confirma en este punto, no se producirá ninguna anomalía: podríamos suponer que la primera transacción se ejecutó primero y luego la segunda (pero no viceversa porque la primera transacción vio el estado de la cuenta con id = 3 antes de eso la cuenta fue cambiada por la segunda transacción).

Pero imagine que en este punto comienza la tercera transacción (solo lectura), que lee el estado de alguna cuenta que no se ve afectada por las dos primeras transacciones:

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 3 | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

Y solo después de eso se completa la primera transacción:

 => COMMIT; 

¿Qué estado debería ver la tercera transacción ahora?

 | SELECT * FROM accounts WHERE client = 'bob'; 

Una vez iniciada, la tercera transacción podría ver los cambios de la segunda transacción (que ya se había confirmado), pero no de la primera (que aún no se había confirmado). Por otro lado, ya hemos determinado anteriormente que la segunda transacción debe considerarse iniciada después de la primera. Cualquiera que sea el estado que vea la tercera transacción será inconsistente, esto es solo la anomalía de una transacción de solo lectura. Pero en el nivel de lectura repetible está permitido:

 | id | number | client | amount | ----+--------+--------+-------- | 2 | 2001 | bob | 900.00 | 3 | 2002 | bob | 0.00 | (2 rows) 

 | => COMMIT; 

Serializable

El nivel serializable evita todas las anomalías posibles. De hecho, Serializable está construido sobre el aislamiento de instantáneas. Esas anomalías que no ocurren con la lectura repetible (como una lectura sucia, no repetible o fantasma) tampoco ocurren en el nivel serializable. Y esas anomalías que ocurren (una escritura inconsistente y una anomalía de transacción de solo lectura) se detectan y la transacción se anula; se produce un error de serialización familiar: no se pudo serializar el acceso .

Escritura inconsistente

Para ilustrar esto, repitamos el escenario con una anomalía de escritura inconsistente:

 => BEGIN ISOLATION LEVEL SERIALIZABLE; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

  sum ---------- 910.0000 (1 row) 

 | => BEGIN ISOLATION LEVEL SERIALIZABLE; | => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

 | sum | ---------- | 910.0000 | (1 row) 

 => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; 

 | => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; | => COMMIT; 

 => COMMIT; 

 ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt. HINT: The transaction might succeed if retried. 

Al igual que en el nivel de lectura repetible, una aplicación que utiliza el nivel de aislamiento serializable debe repetir las transacciones que terminaron con un error de serialización, como nos indica el mensaje de error.

Ganamos simplicidad de programación, pero el precio es una terminación forzada de una fracción de las transacciones y la necesidad de repetirlas. La pregunta, por supuesto, es qué tan grande es esta fracción. Si solo se terminaran esas transacciones que se superponen incompatiblemente con otras transacciones, hubiera sido bueno. Pero tal implementación inevitablemente requeriría muchos recursos e ineficiencia porque tendría que rastrear las operaciones en cada fila.

En realidad, la implementación de PostgreSQL es tal que permite falsos negativos: algunas transacciones absolutamente normales que son "desafortunadas" también abortarán. Como veremos más adelante, esto depende de muchos factores, como la disponibilidad de índices apropiados o la cantidad de RAM disponible. Además, existen otras restricciones de implementación (bastante severas), por ejemplo, las consultas en el nivel Serializable no funcionarán en las réplicas y no utilizarán planes de ejecución paralelos. Aunque el trabajo para mejorar la implementación continúa, las limitaciones existentes hacen que este nivel de aislamiento sea menos atractivo.

Los planes paralelos aparecerán tan pronto como en PostgreSQL 12 ( parche ). Y las consultas sobre réplicas pueden comenzar a funcionar en PostgreSQL 13 ( otro parche ).

Anomalía de transacción de solo lectura

Para que una transacción de solo lectura no resulte en una anomalía y no la sufra, PostgreSQL ofrece una técnica interesante: dicha transacción puede bloquearse hasta que su ejecución sea segura. Este es el único caso cuando un operador SELECCIONAR puede ser bloqueado por actualizaciones de fila. Así es como se ve:

 => UPDATE accounts SET amount = 900.00 WHERE id = 2; => UPDATE accounts SET amount = 100.00 WHERE id = 3; => SELECT * FROM accounts WHERE client = 'bob' ORDER BY id; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 900.00 3 | 2002 | bob | 100.00 (2 rows) 

 => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 1 => UPDATE accounts SET amount = amount + ( SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01 WHERE id = 2; 

 | => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 2 | => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; | => COMMIT; 

La tercera transacción se declara explícitamente LEER SOLAMENTE y DEFERRABLE:

 | => BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -- 3 | => SELECT * FROM accounts WHERE client = 'alice'; 

Al intentar ejecutar la consulta, la transacción se bloquea porque de lo contrario causaría una anomalía.

 => COMMIT; 

Y solo después de que se confirma la primera transacción, la tercera continúa la ejecución:

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

 | => SELECT * FROM accounts WHERE client = 'bob'; 

 | id | number | client | amount | ----+--------+--------+---------- | 2 | 2001 | bob | 910.0000 | 3 | 2002 | bob | 0.00 | (2 rows) 

 | => COMMIT; 

Otra nota importante: si se usa el aislamiento serializable, todas las transacciones en la aplicación deben usar este nivel. No puede mezclar transacciones de lectura confirmada (o lectura repetible) con serializable. Es decir, puede mezclar, pero Serializable se comportará como Lectura repetible sin ninguna advertencia. Discutiremos por qué sucede esto más adelante, cuando hablemos sobre la implementación.

Entonces, si decide usar Serializble, es mejor establecer globalmente el nivel predeterminado (aunque esto, por supuesto, no le impedirá especificar un nivel incorrecto explícitamente):

 ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

Puede encontrar una presentación más rigurosa de los problemas relacionados con las transacciones, la coherencia y las anomalías en el libro y el curso de conferencias de Boris Novikov "Fundamentos de las tecnologías de bases de datos" (disponible solo en Russion).

¿Qué nivel de aislamiento usar?

El nivel de aislamiento de lectura confirmada se usa de manera predeterminada en PostgreSQL, y es probable que este nivel se use en la gran mayoría de las aplicaciones. Este valor predeterminado es conveniente porque a este nivel es posible cancelar la transacción solo en caso de falla, pero no como un medio para evitar inconsistencias. En otras palabras, no se puede producir un error de serialización.

El otro lado de la moneda es un gran número de posibles anomalías, que se han discutido en detalle anteriormente. El ingeniero de software siempre debe tenerlos en cuenta y escribir código para no permitir que aparezcan. Si no puede codificar las acciones necesarias en una sola instrucción SQL, debe recurrir al bloqueo explícito. Lo más problemático es que el código es difícil de probar en busca de errores asociados con la obtención de datos inconsistentes, y los errores en sí mismos pueden ocurrir de maneras impredecibles y no reproducibles y, por lo tanto, son difíciles de corregir.

El nivel de aislamiento de lectura repetible elimina algunos de los problemas de inconsistencia, pero lamentablemente, no todos. Por lo tanto, no solo debe recordar las anomalías restantes, sino también modificar la aplicación para que maneje correctamente los errores de serialización. Ciertamente es inconveniente. Pero para las transacciones de solo lectura, este nivel complementa perfectamente la lectura confirmada y es muy conveniente, por ejemplo, para crear informes que utilizan múltiples consultas SQL.

Finalmente, el nivel serializable le permite no preocuparse por la inconsistencia, lo que facilita enormemente la codificación. Lo único que se requiere de la aplicación es poder repetir cualquier transacción al obtener un error de serialización. Pero la fracción de transacciones anuladas, la sobrecarga adicional y la incapacidad de paralelizar consultas pueden reducir significativamente el rendimiento del sistema. También tenga en cuenta que el nivel serializable no es aplicable en las réplicas, y que no se puede mezclar con otros niveles de aislamiento.

Sigue leyendo .