Gestión de Transacciones en PostgreSQL

Introducción

En este laboratorio, explorarás la gestión de transacciones de PostgreSQL, un aspecto crucial para garantizar la integridad de los datos. Aprenderás a iniciar y confirmar transacciones, tratando una serie de operaciones como una sola unidad de trabajo. También aprenderás a deshacer transacciones fallidas, establecer niveles de aislamiento y simular bloqueos con actualizaciones concurrentes.

Este es un Guided Lab, que proporciona instrucciones paso a paso para ayudarte a aprender y practicar. Sigue las instrucciones cuidadosamente para completar cada paso y obtener experiencia práctica. Los datos históricos muestran que este es un laboratorio de nivel principiante con una tasa de finalización del 93%. Ha recibido una tasa de reseñas positivas del 93% por parte de los estudiantes.

Iniciar y Confirmar una Transacción

En este paso, aprenderás a iniciar y confirmar una transacción en PostgreSQL. Las transacciones garantizan la integridad de los datos al tratar una serie de operaciones como una sola unidad de trabajo. Si alguna operación dentro de la transacción falla, la transacción completa se revierte, evitando actualizaciones parciales y manteniendo la consistencia.

Primero, conecta a la base de datos PostgreSQL como el usuario postgres. Abre un terminal y usa el siguiente comando:

sudo -u postgres psql

Ahora deberías ver el indicador postgres=#.

A continuación, crea una tabla llamada accounts para demostrar las transacciones.

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10, 2)
);

Inserta algunos datos iniciales en la tabla accounts.

INSERT INTO accounts (name, balance) VALUES ('Alice', 100.00);
INSERT INTO accounts (name, balance) VALUES ('Bob', 50.00);

Ahora, inicia una transacción usando el comando BEGIN.

BEGIN;

Dentro de la transacción, transfiere $20 de la cuenta de Alice a la cuenta de Bob.

UPDATE accounts SET balance = balance - 20.00 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 20.00 WHERE name = 'Bob';

Para hacer permanentes estos cambios, confirma la transacción usando el comando COMMIT.

COMMIT;

Verifica que la transacción se realizó correctamente consultando la tabla accounts.

SELECT * FROM accounts;

Debes ver que el saldo de Alice ha disminuido en $20 y el saldo de Bob ha aumentado en $20.

Salida de ejemplo de transacción PostgreSQL

Finalmente, sale de la shell psql.

\q

Revertir una Transacción Fallida

En este paso, aprenderás cómo revertir una transacción fallida en PostgreSQL. Revertir una transacción es crucial cuando se produce un error durante una serie de operaciones en la base de datos, asegurando que la base de datos permanezca en un estado consistente.

Debes estar conectado a la base de datos PostgreSQL desde el paso anterior. Si no es así, conéctate de nuevo usando:

sudo -u postgres psql

Iniciemos una nueva transacción.

BEGIN;

Dentro de esta transacción, intentaremos una operación que fallará intencionadamente. Intentemos insertar una clave primaria duplicada. Primero, encontremos el siguiente valor disponible para id.

SELECT MAX(id) FROM accounts;

Supongamos que el resultado es 2. Ahora, intenta insertar una nueva cuenta con id = 1, que ya existe.

INSERT INTO accounts (id, name, balance) VALUES (1, 'Eve', 25.00);

Este comando producirá un error ERROR: duplicate key value violates unique constraint "accounts_pkey".

Dado que se produjo un error dentro de la transacción, revierte la transacción para descartar cualquier cambio realizado. Usa el comando ROLLBACK.

ROLLBACK;

Verifica que la reversión (ROLLBACK) se realizó correctamente consultando la tabla accounts.

SELECT * FROM accounts;

Debes ver que la tabla todavía contiene solo las cuentas de Alice y Bob con sus saldos del final del Paso 1. La operación INSERT fallida se revirtió correctamente.

Ejemplo de reversión de transacción PostgreSQL

Finalmente, sale de la shell psql.

\q

Establecer y Probar Niveles de Aislamiento

En este paso, aprenderás sobre los niveles de aislamiento de transacciones en PostgreSQL y cómo establecerlos y probarlos. Los niveles de aislamiento controlan el grado en que las transacciones concurrentes están aisladas entre sí. Los niveles de aislamiento más altos proporcionan una mayor protección contra la corrupción de datos, pero pueden reducir la concurrencia.

Debes estar conectado a la base de datos PostgreSQL desde el paso anterior. Si no es así, conéctate de nuevo usando:

sudo -u postgres psql

Abre dos ventanas de terminal separadas. En cada terminal, conecta a la base de datos PostgreSQL como usuario postgres. Deberías tener dos indicadores postgres=#.

Terminal 1:

sudo -u postgres psql

Terminal 2:

sudo -u postgres psql

En el Terminal 1, establece el nivel de aislamiento en READ COMMITTED (aunque este es el valor predeterminado, lo estableceremos explícitamente para la demostración).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Luego, inicia una transacción.

BEGIN;

En el Terminal 1, lee el saldo de Alice.

SELECT balance FROM accounts WHERE name = 'Alice';

Observa el saldo. Ahora, en el Terminal 2, inicia una transacción y actualiza el saldo de Alice.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE accounts SET balance = 90.00 WHERE name = 'Alice';
COMMIT;

En el Terminal 1, lee de nuevo el saldo de Alice.

SELECT balance FROM accounts WHERE name = 'Alice';

Debido a que el nivel de aislamiento es READ COMMITTED, verás el saldo actualizado (90.00) confirmado por el Terminal 2.

Saldo de Alice actualizado en READ COMMITTED

Ahora, probemos el nivel de aislamiento REPEATABLE READ. En el Terminal 1, revierte la transacción actual y establece el nivel de aislamiento en REPEATABLE READ.

ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

En el Terminal 1, lee de nuevo el saldo de Alice.

SELECT balance FROM accounts WHERE name = 'Alice';

Observa el saldo. Ahora, en el Terminal 2, inicia una transacción y actualiza de nuevo el saldo de Alice.

BEGIN;
UPDATE accounts SET balance = 100.00 WHERE name = 'Alice';
COMMIT;

En el Terminal 1, lee de nuevo el saldo de Alice.

SELECT balance FROM accounts WHERE name = 'Alice';

Debido a que el nivel de aislamiento es REPEATABLE READ, aún verás el saldo original desde el inicio de la transacción, aunque el Terminal 2 haya confirmado un nuevo valor.

Finalmente, en el Terminal 1, confirma la transacción.

COMMIT;

Ahora, si lees el saldo de Alice en el Terminal 1 de nuevo, verás el último valor confirmado (100.00).

Captura de pantalla del nivel de aislamiento de lectura repetible

Cierra ambas sesiones psql.

\q

Simular Bloqueos con Actualizaciones Concurrente

En este paso, simularás bloqueos con actualizaciones concurrentes en PostgreSQL. Los bloqueos son mecanismos utilizados para evitar que transacciones concurrentes interfieran entre sí, asegurando la integridad de los datos.

Debes estar conectado a la base de datos PostgreSQL desde el paso anterior. Si no es así, conéctate de nuevo usando:

sudo -u postgres psql

Abre dos ventanas de terminal separadas. En cada terminal, conecta a la base de datos PostgreSQL como usuario postgres. Deberías tener dos indicadores postgres=#.

Terminal 1:

sudo -u postgres psql

Terminal 2:

sudo -u postgres psql

En el Terminal 1, inicia una transacción y actualiza el saldo de Alice. Crucialmente, usa SELECT ... FOR UPDATE para bloquear la fila.

BEGIN;
SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;

Este comando recupera el saldo de Alice y coloca un bloqueo en la fila, evitando que otras transacciones la modifiquen hasta que esta transacción se confirme o se revierta.

En el Terminal 2, inicia una transacción e intenta actualizar el saldo de Alice.

BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE name = 'Alice';

Observa que este comando en el Terminal 2 parecerá colgar. Esto se debe a que está esperando que el bloqueo mantenido por el Terminal 1 sea liberado.

Ahora, en el Terminal 1, confirma la transacción.

COMMIT;

Después de confirmar la transacción en el Terminal 1, el comando UPDATE en el Terminal 2 continuará.

En el Terminal 2, confirma la transacción.

COMMIT;

Ahora, en cualquiera de los terminales, consulta la tabla accounts para verificar los cambios.

SELECT * FROM accounts;

Deberías ver que el saldo de Alice ha sido actualizado por la transacción en el Terminal 2 después de que el Terminal 1 liberó el bloqueo.

Simulación de actualización concurrente en PostgreSQL

Finalmente, cierra ambas sesiones psql.

\q

Este ejemplo demuestra cómo SELECT ... FOR UPDATE se puede usar para simular bloqueos y evitar que las actualizaciones concurrentes interfieran entre sí. Sin el bloqueo, ambas transacciones podrían leer el mismo saldo inicial y aplicar sus actualizaciones basadas en ese valor, lo que llevaría a una actualización perdida.

Resumen

En este laboratorio, aprendiste a gestionar transacciones en PostgreSQL. Empezaste conectándote a la base de datos PostgreSQL usando psql y creando una tabla de muestra accounts con datos iniciales.

Luego te centraste en demostrar el uso de transacciones. Aprendiste a iniciar una transacción usando el comando BEGIN, a realizar múltiples operaciones en la base de datos (actualizando los saldos de Alice y Bob), y a confirmar la transacción usando el comando COMMIT para hacer permanentes los cambios. Esto ilustró el principio fundamental de las transacciones: tratar una serie de operaciones como una unidad atómica única. También aprendiste a revertir transacciones y a establecer niveles de aislamiento. Finalmente, simulaste bloqueos con actualizaciones concurrentes para comprender cómo prevenir la corrupción de datos.