Aislamiento de transacciones de Postgresql

13.2.1. Leer el nivel de aislamiento comprometido

13.2.2. Nivel de aislamiento de lectura repetible

13.2.3. Nivel de aislamiento serializable

El estándar SQL define cuatro niveles de aislamiento de transacciones. El más estricto es Serializable, que está definido por el estándar en un párrafo que dice que cualquier ejecución concurrente de un conjunto de transacciones serializables está garantizado para producir el mismo efecto que ejecutarlas una a la vez en algún orden. Los otros tres niveles se definen en términos de fenómenos, resultantes de la interacción entre transacciones concurrentes, que no deben ocurrir en cada nivel. El estándar señala que debido a la definición de serializable, ninguno de estos fenómenos es posible a ese nivel. (Esto no es sorprendente: si el efecto de las transacciones debe ser consistente con haber sido ejecutadas una a la vez, ¿cómo podría ver los fenómenos causados ​​por las interacciones?)

Los fenómenos que están prohibidos en varios niveles son:

lectura sucia

Una transacción lee datos escritos por una transacción concurrente no comprometida.

lectura irrepetible

Una transacción vuelve a leer los datos que ha leído anteriormente y encuentra que los datos han sido modificados por otra transacción (que se confirmó desde la lectura inicial).

lectura fantasma

Una transacción vuelve a ejecutar una consulta que devuelve un conjunto de filas que satisfacen una condición de búsqueda y encuentra que el conjunto de filas que satisfacen la condición ha cambiado debido a otra transacción confirmada recientemente.

anomalía de serialización

El resultado de realizar con éxito un grupo de transacciones es inconsistente con todos los posibles ordenamientos de ejecutar esas transacciones una a la vez.

Los niveles de aislamiento de transacciones implementados por el estándar SQL y PostgreSQL se describen en la Tabla 13.1.

Cuadro 13.1. Niveles de aislamiento de transacciones

En PostgreSQL, puede solicitar cualquiera de los cuatro niveles de aislamiento de transacciones estándar, pero internamente solo se implementan tres niveles de aislamiento distintos, es decir, el modo Lectura no comprometida de PostgreSQL se comporta como Lectura confirmada. Esto se debe a que es la única forma sensata de asignar los niveles de aislamiento estándar a la arquitectura de control de concurrencia de múltiples versiones de PostgreSQL.

La tabla también muestra que la implementación de lectura repetible de PostgreSQL no permite lecturas fantasma. El estándar SQL permite un comportamiento más estricto: los cuatro niveles de aislamiento solo definen qué fenómenos no deben ocurrir, no qué fenómenos debe ocurrir. El comportamiento de los niveles de aislamiento disponibles se detalla en las siguientes subsecciones.

Para establecer el nivel de aislamiento de una transacción, use el comando SET TRANSACTION.

Importante

Algunos tipos de datos y funciones de PostgreSQL tienen reglas especiales con respecto al comportamiento transaccional. En particular, los cambios realizados en una secuencia (y por lo tanto el contador de una columna declarada usando serial) son inmediatamente visibles para todas las demás transacciones y no se revierten si la transacción que realizó los cambios se cancela. Consulte la Sección 9.17 y la Sección 8.1.4.

13.2.1. Leer el nivel de aislamiento comprometido

Leer comprometido es el nivel de aislamiento predeterminado en PostgreSQL. Cuando una transacción usa este nivel de aislamiento, un SELECT consulta (sin una FOR UPDATE/SHARE cláusula) ve solo los datos confirmados antes de que comenzara la consulta; nunca ve datos no confirmados o cambios cometidos durante la ejecución de la consulta por transacciones concurrentes. En efecto, un SELECT query ve una instantánea de la base de datos en el momento en que la consulta comienza a ejecutarse. Sin embargo, SELECT ve los efectos de las actualizaciones anteriores ejecutadas dentro de su propia transacción, aunque aún no se hayan confirmado. También tenga en cuenta que dos sucesivos SELECT Los comandos pueden ver diferentes datos, incluso si están dentro de una sola transacción, si otras transacciones confirman cambios después de la primera SELECT comienza y antes del segundo SELECT empieza.

UPDATE, DELETE, SELECT FOR UPDATE, y SELECT FOR SHARE los comandos se comportan igual que SELECT en términos de búsqueda de filas de destino: solo encontrarán filas de destino que se confirmaron a la hora de inicio del comando. Sin embargo, es posible que dicha fila de destino ya haya sido actualizada (o eliminada o bloqueada) por otra transacción simultánea en el momento en que se encuentre. En este caso, el actual actualizador esperará a que la primera transacción de actualización se confirme o retroceda (si aún está en curso). Si el primer actualizador se revierte, sus efectos se anulan y el segundo actualizador puede continuar con la actualización de la fila encontrada originalmente. Si el primer actualizador se confirma, el segundo actualizador ignorará la fila si el primer actualizador la eliminó; de lo contrario, intentará aplicar su operación a la versión actualizada de la fila. La condición de búsqueda del comando (el WHERE cláusula) se vuelve a evaluar para ver si la versión actualizada de la fila todavía coincide con la condición de búsqueda. Si es así, el segundo actualizador continúa con su operación utilizando la versión actualizada de la fila. En el caso de SELECT FOR UPDATE y SELECT FOR SHARE, esto significa que es la versión actualizada de la fila la que se bloquea y se devuelve al cliente.

INSERT con un ON CONFLICT DO UPDATE La cláusula se comporta de manera similar. En el modo de lectura confirmada, cada fila propuesta para la inserción se insertará o actualizará. A menos que haya errores no relacionados, uno de esos dos resultados está garantizado. Si un conflicto se origina en otra transacción cuyos efectos aún no son visibles para el INSERT , los UPDATE La cláusula afectará a esa fila, aunque posiblemente no La versión de esa fila es convencionalmente visible para el comando.

INSERT con un ON CONFLICT DO NOTHING La cláusula puede hacer que la inserción no proceda para una fila debido al resultado de otra transacción cuyos efectos no son visibles para el INSERT instantánea. Nuevamente, este es solo el caso en el modo de lectura confirmada.

Debido a las reglas anteriores, es posible que un comando de actualización vea una instantánea inconsistente: puede ver los efectos de los comandos de actualización simultáneos en las mismas filas que está intentando actualizar, pero no ve los efectos de esos comandos en otras filas en la base de datos. Este comportamiento hace que el modo de lectura confirmada no sea adecuado para comandos que implican condiciones de búsqueda complejas; sin embargo, es ideal para casos más simples. Por ejemplo, considere actualizar los saldos bancarios con transacciones como:

BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;

Si dos transacciones de este tipo intentan simultáneamente cambiar el saldo de la cuenta 12345, es evidente que queremos que la segunda transacción comience con la versión actualizada de la fila de la cuenta. Debido a que cada comando afecta solo a una fila predeterminada, dejar que vea la versión actualizada de la fila no crea ninguna inconsistencia problemática.

Un uso más complejo puede producir resultados no deseados en el modo de lectura comprometida. Por ejemplo, considere un DELETE comando que opera en datos que se agregan y eliminan de sus criterios de restricción por otro comando, por ejemplo, asumir website es una mesa de dos filas con website.hits igualando 9 y 10:

BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session:  DELETE FROM website WHERE hits = 10;
COMMIT;

los DELETE no tendrá ningún efecto a pesar de que hay un website.hits = 10 fila antes y después de la UPDATE. Esto ocurre porque el valor de la fila de actualización previa 9 se omite, y cuando el UPDATE completa y DELETE obtiene un bloqueo, el nuevo valor de fila ya no es 10 pero 11, que ya no coincide con los criterios.

Debido a que el modo de lectura confirmada inicia cada comando con una nueva instantánea que incluye todas las transacciones comprometidas hasta ese instante, los comandos subsiguientes en la misma transacción verán los efectos de la transacción concurrente comprometida en cualquier caso. El punto en discusión arriba es si un soltero El comando ve una vista absolutamente consistente de la base de datos.

El aislamiento de transacciones parcial proporcionado por el modo de lectura confirmada es adecuado para muchas aplicaciones, y este modo es rápido y fácil de usar; sin embargo, no es suficiente para todos los casos. Las aplicaciones que realizan consultas y actualizaciones complejas pueden requerir una vista de la base de datos más rigurosamente coherente que la que proporciona el modo de lectura confirmada.

13.2.2. Nivel de aislamiento de lectura repetible

los Lectura repetible el nivel de aislamiento solo ve los datos comprometidos antes de que comenzara la transacción; nunca ve datos no confirmados o cambios cometidos durante la ejecución de transacciones por transacciones concurrentes. (Sin embargo, la consulta ve los efectos de las actualizaciones anteriores ejecutadas dentro de su propia transacción, aunque aún no se hayan confirmado). Esta es una garantía más sólida que la requerida por el estándar SQL para este nivel de aislamiento y evita todos los fenómenos. descrito en la Tabla 13.1 excepto por las anomalías de serialización. Como se mencionó anteriormente, esto está específicamente permitido por la norma, que solo describe mínimo protecciones que debe proporcionar cada nivel de aislamiento.

Este nivel es diferente de Lectura confirmada en que una consulta en una transacción de lectura repetible ve una instantánea desde el inicio de la primera declaración de control sin transacción en el transacción, no desde el inicio del estado de cuenta actual dentro de la transacción. Así, sucesivos SELECT comandos dentro de un soltero transacción ven los mismos datos, es decir, no ven los cambios realizados por otras transacciones que se comprometieron después de que comenzó su propia transacción.

Las aplicaciones que usan este nivel deben estar preparadas para reintentar transacciones debido a fallas de serialización.

UPDATE, DELETE, SELECT FOR UPDATE, y SELECT FOR SHARE los comandos se comportan igual que SELECT en términos de búsqueda de filas de destino: solo encontrarán filas de destino que se confirmaron a la hora de inicio de la transacción. Sin embargo, es posible que dicha fila de destino ya haya sido actualizada (o eliminada o bloqueada) por otra transacción simultánea en el momento en que se encuentre. En este caso, la transacción de lectura repetible esperará a que se confirme la primera transacción de actualización. o retroceder (si todavía está en curso). Si el primer actualizador se revierte, sus efectos se anulan y la transacción de lectura repetible puede continuar con la actualización de la fila encontrada originalmente. Pero si el primer actualizador se confirma (y en realidad actualizó o eliminó la fila, no solo la bloqueó), la transacción de lectura repetible se revertirá con el mensaje

ERROR:  could not serialize access due to concurrent update

porque una transacción de lectura repetible no puede modificar o bloquear filas cambiadas por otras transacciones después de que comenzó la transacción de lectura repetible.

Cuando una aplicación recibe este mensaje de error, debe abortar la transacción actual y reintentar la transacción completa desde el principio. La segunda vez, la transacción verá el cambio confirmado previamente como parte de su vista inicial de la base de datos, por lo que no hay un conflicto lógico al usar la nueva versión de la fila como punto de partida para la actualización de la nueva transacción.

Tenga en cuenta que es posible que solo sea necesario reintentar las transacciones de actualización; Las transacciones de solo lectura nunca tendrán conflictos de serialización.

El modo de lectura repetible proporciona una garantía rigurosa de que cada transacción ve una vista completamente estable de la base de datos. Sin embargo, esta vista no siempre será necesariamente consistente con alguna ejecución en serie (una a la vez) de transacciones concurrentes del mismo nivel. Por ejemplo, incluso una transacción de solo lectura en este nivel puede ver un registro de control actualizado para mostrar que se ha completado un lote, pero no ver uno de los registros de detalle que es lógicamente parte del lote porque leyó una revisión anterior del registro de control. Es poco probable que los intentos de hacer cumplir las reglas comerciales mediante transacciones que se ejecutan en este nivel de aislamiento funcionen correctamente sin un uso cuidadoso de bloqueos explícitos para bloquear transacciones en conflicto.

El nivel de aislamiento de lectura repetible se implementa utilizando una técnica conocida en la literatura académica de bases de datos y en algunos otros productos de bases de datos como Aislamiento de instantáneas. Se pueden observar diferencias en el comportamiento y el rendimiento en comparación con los sistemas que utilizan una técnica de bloqueo tradicional que reduce la concurrencia. Algunos otros sistemas pueden incluso ofrecer lectura repetible y aislamiento de instantáneas como niveles de aislamiento distintos con un comportamiento diferente. Los fenómenos permitidos que distinguen las dos técnicas no fueron formalizados por los investigadores de bases de datos hasta después de que se desarrolló el estándar SQL, y están fuera del alcance de este manual. Para un tratamiento completo, consulte [berenson95].

Nota

Antes de la versión 9.1 de PostgreSQL, una solicitud del nivel de aislamiento de transacciones serializables proporcionaba exactamente el mismo comportamiento que se describe aquí. Para conservar el comportamiento serializable heredado, ahora se debe solicitar la lectura repetible.

13.2.3. Nivel de aislamiento serializable

los Serializable El nivel de aislamiento proporciona el aislamiento de transacciones más estricto. Este nivel emula la ejecución de transacciones en serie para todas las transacciones comprometidas; como si las transacciones se hubieran ejecutado una tras otra, en serie, en lugar de al mismo tiempo. Sin embargo, al igual que el nivel de lectura repetible, las aplicaciones que utilizan este nivel deben estar preparadas para reintentar transacciones debido a fallas de serialización. De hecho, este nivel de aislamiento funciona exactamente igual que la Lectura repetible, excepto que monitorea las condiciones que podrían hacer que la ejecución de un conjunto concurrente de transacciones serializables se comporte de manera inconsistente con todas las posibles ejecuciones en serie (una a la vez) de esas transacciones. Esta monitorización no introduce ningún bloqueo más allá del presente en la lectura repetible, pero hay una sobrecarga para la monitorización y detección de las condiciones que podrían causar una anomalía de serialización desencadenará un falla de serialización.

Como ejemplo, considere una mesa mytab, que inicialmente contiene:

 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

Suponga que la transacción serializable A calcula:

SELECT SUM(value) FROM mytab WHERE class = 1;

y luego inserta el resultado (30) como el value en una nueva fila con class= 2. Al mismo tiempo, la transacción serializable B calcula:

SELECT SUM(value) FROM mytab WHERE class = 2;

y obtiene el resultado 300, que inserta en una nueva fila con class= 1. Entonces ambas transacciones intentan comprometerse. Si alguna de las transacciones se estuviera ejecutando en el nivel de aislamiento de lectura repetible, se permitiría que ambas se comprometieran; pero dado que no hay un orden de ejecución en serie consistente con el resultado, el uso de transacciones serializables permitirá que una transacción se confirme y revertirá la otra con este mensaje:

ERROR:  could not serialize access due to read/write dependencies among transactions

Esto se debe a que si A se hubiera ejecutado antes que B, B habría calculado la suma 330, no 300, y de manera similar, el otro orden habría resultado en una suma diferente calculada por A.

Al confiar en transacciones serializables para evitar anomalías, es importante que los datos leídos de una tabla de usuario permanente no se consideren válidos hasta que la transacción que los leyó se haya comprometido con éxito. Esto es cierto incluso para transacciones de solo lectura, excepto que los datos leídos dentro de un diferible Se sabe que la transacción de solo lectura es válida tan pronto como se lee, porque dicha transacción espera hasta que puede adquirir una instantánea garantizada que está libre de tales problemas antes de comenzar a leer cualquier dato. En todos los demás casos, las aplicaciones no deben depender de los resultados leídos durante una transacción que luego se canceló; en su lugar, deben volver a intentar la transacción hasta que se realice correctamente.

Para garantizar una verdadera serialización, PostgreSQL utiliza bloqueo de predicado, lo que significa que mantiene bloqueos que le permiten determinar cuándo una escritura habría tenido un impacto en el resultado de una lectura anterior de una transacción concurrente, si se hubiera ejecutado primero. En PostgreSQL, estos bloqueos no causan ningún bloqueo y, por lo tanto, pueden no jugar cualquier papel en causar un punto muerto. Se utilizan para identificar y marcar dependencias entre transacciones serializables concurrentes que en ciertas combinaciones pueden conducir a anomalías de serialización. Por el contrario, una transacción de lectura confirmada o de lectura repetible que desea garantizar la coherencia de los datos puede necesitar eliminar un bloqueo en una tabla completa, lo que podría bloquear a otros usuarios que intentan usar esa tabla, o puede usar SELECT FOR UPDATE o SELECT FOR SHARE que no solo puede bloquear otras transacciones, sino que también provoca el acceso al disco.

Los bloqueos de predicados en PostgreSQL, como en la mayoría de los otros sistemas de bases de datos, se basan en los datos a los que realmente se accede mediante una transacción. Estos aparecerán en el pg_locks vista del sistema con un mode de SIReadLock. Los bloqueos particulares adquiridos durante la ejecución de una consulta dependerán del plan utilizado por la consulta, y varios bloqueos de grano más fino (por ejemplo, bloqueos de tupla) se pueden combinar en menos bloqueos de grano más grueso (por ejemplo, bloqueos de página) durante el transcurso de la transacción para evitar el agotamiento de la memoria utilizada para rastrear los bloqueos. A READ ONLY La transacción puede liberar sus bloqueos SIRead antes de su finalización, si detecta que aún no pueden ocurrir conflictos que podrían conducir a una anomalía de serialización. De hecho, READ ONLY las transacciones a menudo podrán establecer ese hecho al inicio y evitar realizar bloqueos de predicado. Si solicita explícitamente una SERIALIZABLE READ ONLY DEFERRABLE transacción, se bloqueará hasta que pueda establecer este hecho. (Este es el solamente caso en el que las transacciones serializables se bloquean pero las transacciones de lectura repetible no.) Por otro lado, los bloqueos de SIRead a menudo deben mantenerse más allá de la confirmación de la transacción, hasta que se completen las transacciones de lectura y escritura superpuestas.

El uso constante de transacciones serializables puede simplificar el desarrollo. La garantía de que cualquier conjunto de transacciones serializables concurrentes exitosamente comprometidas tendrá el mismo efecto que si se ejecutaran de una en una significa que si puede demostrar que una sola transacción, tal como está escrita, hará lo correcto cuando se ejecute por sí misma, puede tener la confianza de que hará lo correcto en cualquier combinación de transacciones serializables, incluso sin ninguna información sobre lo que podrían hacer esas otras transacciones, o no se confirmará correctamente. Es importante que un entorno que utiliza esta técnica tenga una forma generalizada de manejar las fallas de serialización (que siempre regresan con un valor SQLSTATE de ‘40001’), porque será muy difícil predecir exactamente qué transacciones pueden contribuir a la lectura / escritura. dependencias y deben revertirse para evitar anomalías de serialización. El monitoreo de las dependencias de lectura / escritura tiene un costo, al igual que el reinicio de las transacciones que se terminan con una falla de serialización, pero equilibrado con el costo y el bloqueo involucrados en el uso de bloqueos explícitos y SELECT FOR UPDATE o SELECT FOR SHARELas transacciones serializables son la mejor opción de rendimiento para algunos entornos.

Si bien el nivel de aislamiento de transacciones serializables de PostgreSQL solo permite que las transacciones concurrentes se confirmen si puede demostrar que hay un orden de ejecución en serie que produciría el mismo efecto, no siempre evita que se generen errores que no ocurrirían en una ejecución en serie real. En particular, es posible ver violaciones de restricciones únicas causadas por conflictos con transacciones serializables superpuestas incluso después de verificar explícitamente que la clave no está presente antes de intentar insertarla. Esto puede evitarse asegurándose de que todos Las transacciones serializables que insertan claves potencialmente conflictivas verifican explícitamente si pueden hacerlo primero. Por ejemplo, imagine una aplicación que le pide al usuario una nueva clave y luego verifica que no existe ya al intentar seleccionarla primero, o genera una nueva clave seleccionando la clave máxima existente y agregando una. Si algunas transacciones serializables insertan nuevas claves directamente sin seguir este protocolo, se pueden informar violaciones de restricciones únicas incluso en los casos en que no podrían ocurrir en una ejecución en serie de las transacciones concurrentes.

Para un rendimiento óptimo al confiar en transacciones serializables para el control de la concurrencia, se deben considerar estos problemas:

• Declarar transacciones como READ ONLY cuando sea posible.

• Controle el número de conexiones activas, utilizando un grupo de conexiones si es necesario. Esta es siempre una consideración importante sobre el rendimiento, pero puede ser particularmente importante en un sistema ocupado que utiliza transacciones serializables.

• No invierta en una sola transacción más de lo necesario por motivos de integridad.

• No dejes las conexiones colgando “inactivo en la transacción“ más de lo necesario. El parámetro de configuración idle_in_transaction_session_timeout se puede utilizar para desconectar automáticamente las sesiones persistentes.

• Eliminar bloqueos explícitos, SELECT FOR UPDATE, y SELECT FOR SHARE donde ya no se necesita debido a las protecciones proporcionado automáticamente por transacciones serializables.

• Cuando el sistema se ve obligado a combinar varios bloqueos de predicado de nivel de página en un solo bloqueo de predicado de nivel de relación porque la tabla de bloqueo de predicado tiene poca memoria, puede producirse un aumento en la tasa de errores de serialización. Puede evitar esto aumentando max_pred_locks_per_transaction, max_pred_locks_per_relation y / o max_pred_locks_per_page.

• Una exploración secuencial siempre necesitará un bloqueo de predicado a nivel de relación. Esto puede resultar en una mayor tasa de errores de serialización. Puede resultar útil fomentar el uso de exploraciones de índices reduciendo random_page_cost y / o aumentando cpu_tuple_cost. Asegúrese de sopesar cualquier disminución en las reversiones y reinicios de transacciones con cualquier cambio general en el tiempo de ejecución de la consulta.

El nivel de aislamiento serializable se implementa mediante una técnica conocida en la literatura académica de bases de datos como Aislamiento de instantáneas serializables, que se basa en el aislamiento de instantáneas agregando comprobaciones de anomalías de serialización. Se pueden observar algunas diferencias en el comportamiento y el rendimiento en comparación con otros sistemas que utilizan una técnica de bloqueo tradicional. Por favor mira [ports12] para obtener información detallada.