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 cómo iniciar y confirmar (commit) transacciones, tratando una serie de operaciones como una única unidad de trabajo. También aprenderás a revertir (roll back) transacciones fallidas, establecer niveles de aislamiento (isolation levels) y simular bloqueos (locks) con actualizaciones concurrentes.

Skills Graph

Iniciar y Confirmar (Commit) una Transacción

En este paso, aprenderás cómo iniciar y confirmar (commit) una transacción en PostgreSQL. Las transacciones garantizan la integridad de los datos al tratar una serie de operaciones como una única unidad de trabajo. Si alguna operación dentro de la transacción falla, toda la transacción se revierte (roll back), evitando actualizaciones parciales y manteniendo la consistencia.

Primero, conéctate a la base de datos PostgreSQL como el usuario postgres. Abre una terminal y usa el siguiente comando:

sudo -u postgres psql

Ahora deberías ver el prompt 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, iniciemos 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 estos cambios permanentes, confirma (commit) la transacción usando el comando COMMIT.

COMMIT;

Verifica que la transacción fue exitosa consultando la tabla accounts.

SELECT * FROM accounts;

Deberías ver que el saldo de Alice ha disminuido en $20, y el saldo de Bob ha aumentado en $20.

Finalmente, sal de la shell psql.

\q

Revertir (Roll Back) una Transacción Fallida

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

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

sudo -u postgres psql

Comencemos una nueva transacción.

BEGIN;

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

SELECT MAX(id) FROM accounts;

Asumamos 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 resultará en un error ERROR: duplicate key value violates unique constraint "accounts_pkey".

Dado que ocurrió un error dentro de la transacción, revierte (roll back) la transacción para descartar cualquier cambio realizado. Usa el comando ROLLBACK.

ROLLBACK;

Verifica que el ROLLBACK fue exitoso consultando la tabla accounts.

SELECT * FROM accounts;

Deberías 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ó (rolled back) con éxito.

Finalmente, sal de la shell psql.

\q

Establecer y Probar los Niveles de Aislamiento (Isolation Levels)

En este paso, aprenderás sobre los niveles de aislamiento de transacciones (transaction isolation levels) 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 brindan mayor protección contra la corrupción de datos, pero pueden reducir la concurrencia.

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

sudo -u postgres psql

Abre dos ventanas de terminal separadas. En cada terminal, conéctate a la base de datos PostgreSQL como el usuario postgres. Deberías tener dos prompts postgres=#.

Terminal 1:

sudo -u postgres psql

Terminal 2:

sudo -u postgres psql

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

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Luego, comienza una transacción.

BEGIN;

En la Terminal 1, lee el saldo de Alice.

SELECT balance FROM accounts WHERE name = 'Alice';

Toma nota del saldo. Ahora, en la Terminal 2, comienza 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 la Terminal 1, lee el saldo de Alice nuevamente.

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 (committed) por la Terminal 2.

Ahora, probemos el nivel de aislamiento REPEATABLE READ. En la Terminal 1, revierte (roll back) la transacción actual y establece el nivel de aislamiento a REPEATABLE READ.

ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

En la Terminal 1, lee el saldo de Alice nuevamente.

SELECT balance FROM accounts WHERE name = 'Alice';

Toma nota del saldo. Ahora, en la Terminal 2, comienza una transacción y actualiza el saldo de Alice nuevamente.

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

En la Terminal 1, lee el saldo de Alice nuevamente.

SELECT balance FROM accounts WHERE name = 'Alice';

Debido a que el nivel de aislamiento es REPEATABLE READ, aún verás el saldo original de cuando comenzó la transacción, incluso si la Terminal 2 ha confirmado (committed) un nuevo valor.

Finalmente, en la Terminal 1, confirma (commit) la transacción.

COMMIT;

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

Sal de ambas shells psql.

\q

Simular Bloqueos (Locks) con Actualizaciones Concurrentes

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

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

sudo -u postgres psql

Abre dos ventanas de terminal separadas. En cada terminal, conéctate a la base de datos PostgreSQL como el usuario postgres. Deberías tener dos prompts postgres=#.

Terminal 1:

sudo -u postgres psql

Terminal 2:

sudo -u postgres psql

En la Terminal 1, comienza 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 (lock) en la fila, evitando que otras transacciones la modifiquen hasta que esta transacción se confirme (commit) o se revierta (roll back).

En la Terminal 2, comienza 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 la Terminal 2 parecerá que se cuelga. Esto se debe a que está esperando que se libere el bloqueo (lock) mantenido por la Terminal 1.

Ahora, en la Terminal 1, confirma (commit) la transacción.

COMMIT;

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

En la Terminal 2, confirma (commit) la transacción.

COMMIT;

Ahora, en cualquiera de las 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 la Terminal 2 después de que la Terminal 1 liberó el bloqueo (lock).

Finalmente, sal de ambas shells psql.

\q

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

Resumen

En este laboratorio, aprendiste cómo administrar transacciones en PostgreSQL. Comenzaste conectándote a la base de datos PostgreSQL usando psql y creando una tabla de ejemplo accounts con datos iniciales.

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