Hola Habr! Con este artículo, comienzo una serie de bucles (¿o un bucle de series? En general, una gran idea) sobre la estructura interna de PostgreSQL.

El material se basará en cursos de capacitación administrativa que realizamos con Pavel pluzanov . No a todos les gusta ver un video (definitivamente no me gusta), pero leer diapositivas, incluso con comentarios, es completamente "incorrecto".

Por supuesto, los artículos no repetirán el contenido de los cursos uno a uno. Solo hablaré sobre cómo funciona todo, omitiendo la administración en sí, pero intentaré hacerlo con más detalle y detalle. Y creo que ese conocimiento es útil para el desarrollador de la aplicación, no menos que el administrador.

Me centraré en aquellos que ya tienen experiencia en el uso de PostgreSQL y, al menos en términos generales, imaginen lo que está sucediendo. Para los principiantes, el texto será un poco pesado. Por ejemplo, no diré una palabra sobre cómo instalar PostgreSQL y ejecutar psql.

Las cosas que se discutirán no cambian mucho de una versión a otra, pero usaré el 11º PostgreSQL actual.

El primer ciclo está dedicado a cuestiones relacionadas con el aislamiento y la multiversion, y su plan es el siguiente:

1. Aislamiento, tal como lo entienden el estándar y PostgreSQL (este artículo);
2. Capas, archivos, páginas : lo que está sucediendo a nivel físico;
3. Versiones de fila, transacciones virtuales y anidadas ;
4. Instantáneas de datos y visibilidad de versiones de fila, horizonte de eventos ;
5. Limpieza en la página y actualizaciones CALIENTES ;
6. Limpieza normal (aspiradora);
7. Limpieza automática (autovacuum);
8. El contador de transacciones se desborda y se congela .

Pues vamos.

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

Probablemente, al menos, todos conocen la existencia de transacciones, conocieron el acrónimo ACID y escucharon acerca de los niveles de aislamiento. Pero todavía hay que conocer la opinión de que esta es una 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é encantado si la aplicación recibe 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 a nivel de base de datos, puede crear restricciones de integridad (como NOT NULL o UNIQUE). Si los datos siempre satisfacen las restricciones de integridad (y esto es así porque el DBMS lo garantiza), entonces son holísticos.

¿Son correctos e integrales , lo mismo? En realidad no No todas las restricciones se pueden formular a nivel de base de datos. Parte de las restricciones es demasiado complicada, por ejemplo, cubre varias tablas a la vez. E incluso si la restricción, en principio, pudiera definirse en la base de datos, pero por alguna razón no lo hizo, no significa que pueda ser violada.

Entonces, la corrección es más estricta que la integridad , pero no sabemos exactamente qué es. Queda por reconocer que el estándar de corrección es una aplicación que, como queremos creer, está escrita correctamente y nunca se equivoca. En cualquier caso, si la aplicación no viola la integridad, pero viola la corrección, el DBMS no lo sabrá y no le atrapará la mano.

De ahora en adelante, llamaremos a la corrección el término consistencia.

Supongamos, sin embargo, que la aplicación solo ejecuta la secuencia correcta de declaraciones. ¿Cuál es el papel del DBMS entonces, si la aplicación es correcta?

En primer lugar, resulta que una secuencia correcta de declaraciones puede alterar temporalmente la coherencia de los datos, y esto, curiosamente, es normal. Un ejemplo trillado pero comprensible es transferir 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 (dicha regla es bastante difícil de escribir 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, la segunda, aumenta en otra. La primera operación viola la consistencia de los datos, la segunda, restaura.

Un buen ejercicio es implementar la regla descrita anteriormente a nivel de restricciones de integridad. ¿Eres débil? © ©

¿Qué sucede si la primera operación se completa y la segunda no? Después de todo, es fácil: durante la segunda operación, se puede perder electricidad, el servidor puede caerse, la división por cero puede ocurrir, pero nunca se sabe. Está claro que se está violando la coherencia, y esto no debe permitirse. En principio, es posible resolver tales situaciones a nivel de aplicación a costa de esfuerzos increíbles, pero, afortunadamente, no es necesario: el DBMS se encarga de esto. Pero para esto, ella debe saber que dos operaciones constituyen un todo indivisible. Esa es una transacción .

Resulta interesante: sabiendo que las operaciones constituyen una transacción, el DBMS ayuda a mantener la coherencia al garantizar la atomicidad de las transacciones, 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 una por una, juntas pueden funcionar incorrectamente. Esto se debe al hecho de que el orden de las operaciones es mixto: no se puede suponer que todas las operaciones de una transacción se realizan primero, y solo luego todas las operaciones de otra.

Una nota sobre simultaneidad. De hecho, al mismo tiempo, las transacciones pueden ejecutarse en un sistema con un procesador multinúcleo, con una matriz de discos, etc. Pero todas las mismas consideraciones son ciertas para un servidor que ejecuta comandos secuencialmente, en modo de tiempo compartido: tantos ciclos, una transacción se ejecuta, tantos ciclos son diferentes . A veces, el término ejecución competitiva se utiliza para resumir.

Las situaciones en que las transacciones correctas no funcionan juntas correctamente se denominan anomalías de ejecución simultánea.

Un ejemplo simple: si una aplicación quiere obtener los datos correctos de la base de datos, al menos no debería ver cambios en 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 .

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

Por supuesto, es imposible rechazar la ejecución simultánea: de lo contrario, ¿qué tipo de rendimiento se puede discutir? Pero no puede trabajar con datos incorrectos.

Y nuevamente el DBMS viene al rescate. Puede hacer que las transacciones se ejecuten como si fueran secuencialmente, como si una tras otra. En otras palabras, aislados unos de otros. En realidad, el DBMS puede realizar operaciones confusas, pero al mismo tiempo garantizar que el resultado de la ejecución simultánea coincida con el resultado de cualquiera de las posibles ejecuciones secuenciales. Y esto elimina cualquier posible anomalía.

Entonces, llegamos a la definición:

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

Esta definición combina las tres primeras letras del acrónimo ACID. Están tan estrechamente relacionados entre sí que simplemente no tiene sentido considerar uno sin el otro. De hecho, es difícil arrancar la letra D (durabilidad). Después de todo, en caso de un bloqueo del sistema, los cambios en las transacciones no confirmadas permanecen en él, con lo que debe hacer algo para restaurar la consistencia de los datos.

Todo estaría bien, pero implementar un aislamiento completo es una tarea técnicamente difícil, junto con una reducción en el rendimiento del sistema. Por lo tanto, en la práctica, muy a menudo (no siempre, pero casi siempre) se aplica aislamiento debilitado, lo que evita algunas, pero no todas, las anomalías. Y esto significa que parte del trabajo para garantizar la exactitud de los datos recae en la aplicación. Es por eso que es muy importante comprender qué nivel de aislamiento se utiliza en el sistema, qué garantiza que ofrece y cuáles no, y cómo escribir el código correcto en tales condiciones.

Niveles de aislamiento SQL y anomalías

El estándar SQL ha descrito durante mucho tiempo cuatro niveles de aislamiento. Estos niveles se determinan enumerando las anomalías permitidas o no permitidas al realizar transacciones en ese nivel. Por lo tanto, para hablar sobre estos niveles, debe familiarizarse con las anomalías.

Enfatizo que en esta parte estamos hablando del estándar, es decir, de cierta teoría en la que la práctica se basa en gran medida, pero que al mismo tiempo está en desacuerdo. Por lo tanto, todos los ejemplos aquí son especulativos. Utilizarán las mismas operaciones en las cuentas de los clientes: esto es bastante obvio, aunque, es cierto, no tiene nada que ver con cómo se organizan las operaciones bancarias.

Actualización perdida

Comencemos con la actualización perdida . Esta anomalía ocurre cuando dos transacciones leen la misma fila en la tabla, luego una transacción actualiza esta fila y, después de eso, 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 aumentarán la cantidad en la misma cuenta en 100 ₽. La primera transacción lee el valor actual (1000 ₽), luego la segunda transacción lee el mismo valor. La primera transacción aumenta la cantidad (resulta 1100 ₽) y escribe este valor. La segunda transacción hace lo mismo: obtiene los mismos 1.100 ₽ y los escribe. Como resultado, el cliente perdió 100 ₽.

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

Lectura sucia y lectura sin compromiso

Con una lectura sucia ya nos hemos encontrado arriba. Esta anomalía ocurre cuando una transacción lee cambios pendientes realizados por otra transacción.

Por ejemplo, la primera transacción transfiere todo el dinero de la cuenta del cliente a otra cuenta, pero no registra el cambio. Otra transacción lee el estado de la cuenta, recibe 0 ₽ y se niega a emitir efectivo al cliente, a pesar de que la primera transacción se interrumpe y cancela sus cambios, por lo que el valor 0 nunca existió en la base de datos.

El estándar permite la lectura sucia en el nivel de lectura no comprometida.

Lectura no repetida y lectura comprometida

La anomalía de lectura no repetida ocurre cuando una transacción lee la misma línea dos veces, y en el intervalo entre lecturas, la segunda transacción cambia (o elimina) esta línea y confirma los cambios. Entonces, la primera transacción obtendrá resultados diferentes.

Por ejemplo, deje que la regla de coherencia prohíba los importes negativos en las cuentas de los clientes . La primera transacción reducirá la cantidad en la cuenta en 100 ₽. Comprueba el valor actual, obtiene 1000 ₽ y decide que es posible una reducción. En este momento, la segunda transacción reduce el monto en la cuenta a cero y registra los cambios. Si ahora la primera transacción volviera a verificar la cantidad, recibiría 0 ₽ (pero ya había decidido disminuir el valor y la cuenta "va a menos").

El estándar permite la lectura no repetida en los niveles de lectura no confirmada y lectura confirmada. Pero la lectura sucia Read Committed no lo permite.

Lectura fantasma y lectura repetible

La lectura fantasma ocurre cuando una transacción lee un conjunto de líneas dos veces bajo la misma condición, y en el intervalo entre lecturas, la segunda transacción agrega líneas que satisfacen esta condición (y confirma los cambios). Entonces la primera transacción recibirá diferentes conjuntos de filas.

Por ejemplo, suponga que una regla de coherencia prohíbe que un cliente tenga más de 3 cuentas . La primera transacción va a abrir una nueva cuenta, verifica su número actual (digamos, 2) y decide que es posible abrirlo. En este momento, la segunda transacción también abre una nueva cuenta para el cliente y registra los cambios. Si ahora la primera transacción verificara dos veces la cantidad, recibiría 3 (pero ya está abriendo otra cuenta y el cliente tiene 4 de ellas).

La lectura fantasma está permitida por el estándar en los niveles de Lectura no confirmada, Lectura comprometida y Lectura repetible. Pero en el nivel de lectura repetible, la lectura no repetida no está permitida.

Falta de anomalías y serializables

El estándar define otro nivel, Serializable, en el que no se permiten anomalías. Y esto no es lo mismo que prohibir una actualización perdida y una lectura sucia, no repetida y fantasma.

El hecho es que hay significativamente más anomalías conocidas que las enumeradas en el estándar, y aún se desconoce un número desconocido.

Serializable debería prevenir todas las anormalidades en general . Esto significa que a este nivel, el desarrollador de la aplicación no necesita pensar en ejecutarse simultáneamente. Si las transacciones realizan las secuencias correctas de declaraciones, trabajando solas, los datos serán consistentes con la operación simultánea de estas transacciones.

Placa resumen

Ahora puede traer una mesa conocida para todos. Pero aquí, para mayor claridad, se le agrega la última columna, que no está en el estándar.

¿Por qué exactamente estas anomalías?

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

Aparentemente, nadie parece saberlo con certeza. Pero la práctica aquí definitivamente superó la teoría, por lo que es posible que luego no pensemos en otras anomalías (discurso sobre el estándar SQL: 92).

Además, se supuso que el aislamiento debería construirse sobre enclavamientos. La idea del protocolo de bloqueo de dos fases (2PL) ampliamente utilizado es que durante la transacción, la transacción bloquea las líneas con las que está trabajando y, cuando se completa, libera los bloqueos. Simplificando enormemente, mientras más bloqueos captura una transacción, mejor se aísla de otras transacciones. Pero el rendimiento del sistema sufre aún más, porque en lugar de trabajar juntos, las transacciones comienzan a alinearse para las mismas líneas.

Me parece que la diferencia entre los niveles de aislamiento del estándar se explica precisamente por el número de bloqueos necesarios.

Si una transacción bloquea las filas modificadas para que no cambien, pero no para leer, obtenemos el nivel Leer no confirmado: no se permiten cambios perdidos, pero se pueden leer datos no confirmados.

Si la transacción bloquea las líneas mutables de lectura y cambio, obtenemos el nivel de lectura confirmada: no puede leer datos no confirmados, pero cuando accede a la línea nuevamente, puede obtener un valor diferente (lectura no repetida).

Si una transacción bloquea tanto líneas legibles como mutables tanto de lectura como de cambio, obtenemos el nivel de Lectura repetible: la lectura repetida de la línea producirá el mismo valor.

Pero hay un problema con Serializable: no es posible bloquear una fila que aún no existe. Debido a esto, la posibilidad de lectura fantasma permanece: otra transacción puede agregar (pero no eliminar) una fila que se encuentra en las condiciones de una consulta ejecutada previamente, y esta fila se volverá a buscar.

Por lo tanto, para implementar el nivel de Serializable, los bloqueos ordinarios no son suficientes: debe bloquear no las filas, sino las condiciones (predicados). Tales cerraduras se llamaban predicado . Se propusieron en 1976, pero su aplicabilidad práctica está limitada por condiciones bastante simples, por lo que está claro cómo combinar dos predicados diferentes. Hasta donde yo sé, no ha llegado a la implementación de tales bloqueos en ningún sistema.

Niveles de aislamiento PostgreSQL

Con el tiempo, el aislamiento de instantáneas reemplazó los protocolos de administración de transacciones de bloqueo. Su idea es que cada transacción funcione con una instantánea consistente de los datos en un cierto punto en el tiempo, en el que solo caen los cambios que se registraron antes de la creación de la instantánea.

Tal aislamiento no permite automáticamente la lectura sucia. Formalmente, en PostgreSQL, puede especificar el nivel de lectura no confirmada, pero funcionará como lectura confirmada. Por lo tanto, no hablaremos más sobre el nivel Leer no confirmado.

PostgreSQL implementa una versión múltiple de este protocolo. La idea de las versiones múltiples es que varias versiones de la misma cadena pueden coexistir en un DBMS. Esto le permite crear una instantánea de los datos utilizando las versiones disponibles y sobrevivir con un mínimo de bloqueos. De hecho, solo se bloquean los cambios repetidos en la misma línea. Todas las demás operaciones se realizan al mismo tiempo: las transacciones de escritura nunca bloquean las transacciones de lectura, y las transacciones de lectura nunca bloquean a nadie.

Usando instantáneas de datos, el aislamiento en PostgreSQL es más estricto de lo que requiere el estándar: el nivel de lectura repetible no solo permite lecturas no repetitivas, sino también fantasmas (aunque no proporciona un aislamiento completo). Y esto se logra sin pérdida de efectividad.

Cómo se implementa la versión múltiple "bajo el capó", hablaremos en los siguientes artículos, y ahora veremos en detalle cada uno de los tres niveles a través de los ojos del usuario (como saben, lo más interesante está oculto detrás de "otras anomalías"). Para hacer esto, cree una tabla de cuentas. Alice y Bob tienen $ 1,000 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

Falta de lectura sucia

Es fácil verificar que los datos sucios no se puedan leer. Comencemos la transacción. Por defecto, utilizará 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, se puede cambiar si es necesario:

 => SHOW default_transaction_isolation; 

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

Entonces, en una transacción abierta, retiramos fondos de la cuenta, pero no registramos 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, comenzamos otra transacción con el mismo nivel de lectura confirmada. Para distinguir entre diferentes transacciones, los comandos de la segunda transacción serán sangrados y tachados.

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

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

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

Como se esperaba, otra transacción no ve cambios no confirmados: no se permite la lectura sucia.

Lectura no repetida

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

 => COMMIT; 

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

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

 | => COMMIT; 

La solicitud ya está recibiendo nuevos datos: esta es la anomalía de la lectura no repetida , que está permitida en el nivel de lectura confirmada.

Conclusión práctica : en una transacción es imposible tomar decisiones basadas en los datos leídos por la declaración anterior, porque todo puede cambiar entre las veces que se ejecutan las declaraciones. Aquí hay un ejemplo cuyas variaciones son tan comunes en el código de la aplicación que es 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 según lo deseado, de modo que tal "cheque" no guarde nada. Es conveniente imaginar que entre los operadores de una transacción, cualquier otro operador de otras transacciones puede "encajar", por ejemplo, así:

  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, reorganizando los operadores, puede arruinar todo, entonces el código está escrito incorrectamente. Y no se engañe a sí mismo de que tal combinación de circunstancias no sucederá, sucederá.

¿Cómo escribir el código correctamente? Las oportunidades, como regla, se reducen a lo siguiente:

• No escribas código.
  Esto no es una broma. Por ejemplo, en este caso, la verificación se convierte fácilmente en una restricción de integridad:
  ALTER TABLE accounts ADD CHECK amount >= 0;
  Ahora, no se necesitan verificaciones: es suficiente simplemente realizar la acción y, si es necesario, manejar la excepción que surgirá en el caso de un intento de violar la integridad.
  
• Use una sola declaración SQL.
  Los problemas de consistencia surgen debido al hecho de que en el intervalo entre operadores puede finalizar otra transacción y los datos visibles cambiarán. Y si solo hay un operador, entonces no hay huecos.
  PostgreSQL tiene suficientes herramientas para resolver problemas complejos con una sola declaración SQL. Observamos las expresiones de tabla general (CTE), en las que, entre otras cosas, puede usar las instrucciones INSERT / UPDATE / DELETE, así como la instrucción INSERT ON CONFLICT, que implementa la lógica "insertar, y si ya hay una fila, actualizar" en una instrucción.
  
• Bloqueos de usuario.
  El último recurso es establecer manualmente un bloqueo exclusivo en todas las filas necesarias (SELECCIONAR PARA ACTUALIZAR) o en toda la tabla (TABLA DE BLOQUEO). Esto siempre funciona, pero niega los beneficios de las versiones múltiples: en lugar de ejecutar simultáneamente, parte de las operaciones se realizarán secuencialmente.
  

Lectura inconsistente

Antes de embarcarse en el siguiente nivel de aislamiento, uno tiene que admitir que no todo es tan simple. La implementación de PostgreSQL es tal que permite otras anomalías menos conocidas que no están reguladas por el estándar.

Digamos que la primera transacción comenzó a transferir fondos de una cuenta Bob a otra:

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

En este momento, otra transacción calcula el saldo de Bob, y el cálculo se realiza en un ciclo en todas las cuentas de Bob. De hecho, la transacción comienza desde la primera cuenta (y, obviamente, ve el estado anterior):

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

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

En este punto, 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 un nuevo valor):

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

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

 | => COMMIT; 

Por lo tanto, la segunda transacción recibió un total de 1100 ₽, es decir, datos incorrectos. Esta es una anomalía de lectura inconsistente .

¿Cómo evitar tal anomalía al permanecer en Read Committed? Por supuesto, use un operador. Por ejemplo, así:

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

Hasta ahora, he argumentado que la visibilidad de los datos solo puede cambiar entre operadores, pero ¿es tan obvio? Y si la solicitud lleva mucho tiempo, ¿puede ver parte de los datos en un estado y parte en el otro?

Compruébalo Una forma conveniente de hacer esto es insertar un retraso artificial en el operador llamando a la función pg_sleep. Su parámetro establece el tiempo de retraso en segundos.

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

Mientras esta construcción está en progreso, en otra transacción, transferimos los fondos de regreso:

 | => 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 en que estaban en el momento en que comenzaron. Esto es ciertamente correcto.

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

Pero aquí no es tan simple. PostgreSQL le permite definir funciones, mientras que las funciones tienen el concepto de una categoría de variabilidad . Si se llama a una función volátil (con la categoría VOLÁTIL) en una solicitud, y se ejecuta otra solicitud en esta función, entonces esta solicitud dentro de la función verá datos que no son consistentes con los datos de la solicitud 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) 

Insisto en que tal efecto solo es posible en el nivel de aislamiento de lectura comprometida, y solo con la categoría de variabilidad VOLÁTIL. El problema es que este nivel de aislamiento y esta categoría de variabilidad se usan por defecto, así que debo admitir que el rastrillo se encuentra muy bien. ¡No pises!

Lectura inconsistente a cambio de cambios perdidos

Se puede obtener una lectura inconsistente dentro del marco de un operador, de una manera algo inesperada, durante una actualización.

Veamos qué sucede cuando intenta cambiar la misma fila con dos transacciones. Bob ahora 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, otra transacción genera intereses en todas las cuentas de clientes con un saldo total igual o superior a 1000 ₽:

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

La ejecución de una instrucción UPDATE consta de dos partes. Primero, se ejecuta un SELECT, que selecciona las filas que coinciden con la condición para la actualización. Como el cambio de la primera transacción no es fijo, la segunda transacción no puede verlo y no afecta la elección de las líneas para calcular el interés. Por lo tanto, las cuentas de Bob caen bajo la condición y, una vez completada la actualización, su saldo debería aumentar en 10 ₽.

La segunda etapa de ejecución: las filas seleccionadas se actualizan una tras otra. Aquí la segunda transacción se ve obligada a "congelarse", porque la línea 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) 

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

Después de liberar el bloqueo, ACTUALIZAR vuelve a leer la línea que está tratando de actualizar (¡pero solo una!). El resultado es que Bob acumuló 9 ₽, basado en la cantidad de 900 ₽. Pero si Bob tuviera 900 ₽, sus cuentas no deberían haber sido incluidas en la muestra.

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

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

  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: recibe dos sentencias SQL y no sabe nada de que el valor de x + 100 esté relacionado de alguna manera con accounts.amount. No escriba código de esta manera.

Lectura repetible

Falta de lecturas no repetidas y fantasmas

El nombre del nivel de aislamiento en sí mismo indica que la lectura es repetible. Verificaremos esto, y al mismo tiempo nos convenceremos de la ausencia de lecturas fantasmas. Para hacer esto, en la primera transacción, devuelva las cuentas de Bob a su estado anterior y cree 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, indicándolo en el comando BEGIN (el nivel de la primera transacción no es importante).

 | => 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 solicitud.

 => 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 continúa viendo exactamente los mismos datos que al principio: ni los cambios en las líneas existentes ni las nuevas líneas son visibles.

En este nivel, no tiene que preocuparse de que algo cambie entre los dos operadores.

Error de serialización a cambio de cambios perdidos

Dijimos anteriormente que al actualizar la misma fila con dos transacciones en el nivel Confirmar lectura, puede producirse una anomalía de lectura inconsistente. Esto se debe al hecho de que la transacción pendiente vuelve a leer la fila bloqueada y, por lo tanto, no la ve en el mismo momento en el tiempo que el resto de las filas.

En el nivel de lectura repetible, dicha anomalía no está permitida, pero si ocurre, no se puede hacer nada; por lo tanto, la transacción finaliza con un error de serialización. Verificamos repitiendo el mismo escenario con porcentajes:

 => 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; | => UPDATE accounts SET amount = amount * 1.01 | WHERE client IN ( | SELECT client | FROM accounts | GROUP BY client | HAVING sum(amount) >= 1000 | ); 

 => COMMIT; 

 | ERROR: could not serialize access due to concurrent update 

 | => ROLLBACK; 

Los datos se mantuvieron consistentes:

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

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

Se producirá el mismo error en el caso de cualquier otro cambio de fila competitivo, incluso si las columnas de interés para nosotros no han cambiado realmente.

Conclusión práctica : si la aplicación utiliza el nivel de aislamiento de lectura repetible para escribir transacciones, debería estar lista para repetir transacciones que terminaron en un error de serialización. Para transacciones de solo lectura, tal resultado no es posible.

Entrada inconsistente

Entonces, en PostgreSQL, en el nivel de aislamiento de lectura repetible, se evitan todas las anomalías descritas en el estándar. Pero no todo en absoluto. Resulta que hay exactamente dos anomalías que siguen siendo posibles. (Esto es cierto no solo para PostgreSQL, sino también para otras implementaciones de aislamiento basadas en instantáneas).

La primera de estas anomalías es un registro inconsistente .

Deje que se aplique esta regla de coherencia: se permiten montos negativos en las cuentas del cliente si el monto total en todas las cuentas de este cliente no es negativo .

La primera transacción recibe 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 recibe 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 con razón 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 puntuación:

 | => 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 que el saldo de Bob sea menos, aunque cada una de las transacciones funciona correctamente individualmente.

Anomalía 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 modificarán los datos y la tercera, solo lectura.

Pero primero, restaure el estado de la cuenta 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) 

La primera transacción cobra intereses de Bob sobre la cantidad de fondos en todas las cuentas. 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 otra cuenta de Bob y captura sus cambios:

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

Si en este momento se confirma la primera transacción, no habrá anomalía: podríamos suponer que la primera transacción se completó primero, y luego la segunda (pero no al revés, porque la primera transacción vio el estado de la cuenta id = 3 antes de que esta cuenta fuera cambiado por la segunda transacción).

Pero suponga que en este momento 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 ahora la tercera transacción?

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

Una vez iniciada, la tercera transacción podría ver cambios en la segunda transacción (que ya se había confirmado), pero no en la primera (que aún no se ha confirmado). Por otro lado, ya hemos establecido anteriormente que la segunda transacción debe considerarse que comenzó después de la primera. Cualquiera que sea el estado que vea la tercera transacción, será inconsistente: esta es la anomalía de solo la transacción de 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

En el nivel serializable, se evitan todas las anomalías posibles. De hecho, Serializable se implementa como un complemento de aislamiento basado en instantáneas de datos. Esas anomalías que no ocurren durante la Lectura Repetible (como sucia, no repetible, lectura fantasma) tampoco ocurren en el nivel Serializable. Y esas anomalías que surgen (grabación inconsistente y anomalía de solo la transacción de lectura) se detectan y la transacción se cancela; el error de serialización ya familiar no pudo serializar el acceso.

Entrada inconsistente

Para ilustrar, repetimos el escenario con una anomalía de grabación 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. 

Además del nivel de lectura repetible, una aplicación que utiliza el nivel de aislamiento serializable debe repetir las transacciones que terminaron en un error de serialización, que también nos informa la sugerencia en el mensaje de error.

Obtenemos la simplicidad de la programación, pero el precio es la ruptura forzada de una cierta proporción de transacciones y la necesidad de repetirlas. Toda la pregunta, por supuesto, es qué tan grande es esta participación. Si solo se terminaran esas transacciones que realmente se cruzan de manera incompatible en los datos con otras transacciones, todo estaría bien. Pero tal implementación inevitablemente resultaría ser intensiva en recursos e ineficiente, ya que tendría que rastrear las operaciones con cada fila.

De hecho, la implementación de PostgreSQL es tal que permite disparadores negativos falsos: algunas transacciones completamente normales que simplemente "no tienen suerte" se romperán. Como veremos más adelante, esto depende de muchas razones, por ejemplo, la disponibilidad de índices adecuados o la cantidad de RAM disponible. Además, hay otras restricciones de implementación (bastante serias), por ejemplo, las solicitudes en el nivel Serializable no funcionarán en las réplicas, no se utilizarán planes de ejecución paralelos para ellas. Y aunque el trabajo para mejorar la implementación no se detiene, las restricciones existentes reducen el atractivo de este nivel de aislamiento.

Los planes paralelos aparecerán en PostgreSQL 12 ( parche ). Y las consultas sobre réplicas pueden ganar en PostgreSQL 13 ( otro parche ).

Anomalía de solo lectura

Para que solo una transacción de lectura no pueda conducir a una anomalía y no pueda sufrirla, PostgreSQL ofrece un mecanismo interesante: dicha transacción puede bloquearse hasta que su ejecución sea segura. Este es el único caso donde una declaración SELECT puede ser bloqueada 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 es declarada explícitamente solo por el lector (SOLO LEER) y diferida (DEFERRABLE):

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

Cuando intenta ejecutar una solicitud, la transacción se bloquea, porque de lo contrario su ejecución provocará una anomalía.

 => COMMIT; 

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

 | 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, entonces todas las transacciones en la aplicación deben usar este nivel. No puede mezclar transacciones confirmadas de lectura (o lectura repetible) con serializable. Es decir, puede mezclar algo, pero Serializable se comportará como una Lectura repetible sin previo aviso.Por qué sucede esto, lo consideraremos 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 prohíbe especificar el nivel incorrecto explícitamente):

  ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

Se 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 curso de conferencias de Boris Asenovich Novikov , "Fundamentos de las tecnologías de bases de datos".

¿Qué nivel de aislamiento debo usar?

El nivel de aislamiento de lectura comprometida se usa por defecto en PostgreSQL, y parece que este nivel se usa en la gran mayoría de las aplicaciones. Es conveniente porque es posible una interrupción de la transacción solo en caso de falla, pero no para evitar inconsistencias. En otras palabras, no se puede producir un error de serialización.

, . , . SQL-, . , , , .

Repeatable Read , , , . , , . , , . Read Committed , , , SQL-.

, Serializable , . , — . , , . , Serializable , , .

.