Transaccions

Per què són necessàries les transaccions?

Imagineu el procés d'una transferència bancària: s'ha de treure diners d'un compte i afegir-los a un altre. Sense transaccions, si el sistema falla després de treure els diners però abans d'afegir-los al compte destí, els diners es perden. Una transacció garanteix que les dues operacions són atòmiques: o es fan les dues, o no és fa cap.

sequenceDiagram
    participant A as Compte origen (100€)
    participant DB as Base de dades
    participant B as Compte destí (0€)

    Note over DB: BEGIN TRANSACTION
    A->>DB: UPDATE: saldo = saldo - 50
    Note over DB: A ara te 50€
    Note over A,B: *** Aquí falla el sistema sense transaccions ***
    DB->>B: UPDATE: saldo = saldo + 50
    Note over DB: COMMIT
    Note over A,B: A te 50€, B te 50€ (consistent)

Sense transaccions, si el sistema falla entre els dos UPDATEs, el compte origen perd 50€ que mai arriben al destí. Amb una transacció, el ROLLBACK automàtic restaura el compte origen als 100€ originals.

BEGIN, COMMIT i ROLLBACK

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Iniciar una transacció
BEGIN;
-- o bé:
START TRANSACTION;

-- Operacions DML
UPDATE comptes SET saldo = saldo - 50 WHERE id_compte = 1;
UPDATE comptes SET saldo = saldo + 50 WHERE id_compte = 2;

-- Si tot ha anat bé: confirmar
COMMIT;

-- Si hi ha hagut algun error: desfer
ROLLBACK;

-- Desactivar autocommit (per sessions interactives)
SET autocommit = 0;

START TRANSACTION;

UPDATE comptes SET saldo = saldo - 50 WHERE id_compte = 1;
UPDATE comptes SET saldo = saldo + 50 WHERE id_compte = 2;

COMMIT;
-- o:
ROLLBACK;

BEGIN TRANSACTION;

UPDATE comptes SET saldo = saldo - 50 WHERE id_compte = 1;
UPDATE comptes SET saldo = saldo + 50 WHERE id_compte = 2;

-- Gestió d'errors amb TRY/CATCH
BEGIN TRY
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH;

-- Oracle NO necessita BEGIN per iniciar una transacció:
-- la primera instrucció DML la inicia automàticament

UPDATE comptes SET saldo = saldo - 50 WHERE id_compte = 1;
UPDATE comptes SET saldo = saldo + 50 WHERE id_compte = 2;

-- Confirmar
COMMIT;

-- Desfer (torna al darrer COMMIT o al començament de la sessió)
ROLLBACK;

Autocommit

Per defecte, la majoria de SGBD estan en mode autocommit: cada instruccio DML es confirma automàticament quan acaba. Aixo vol dir que UPDATE clients SET email = 'error' (sense WHERE) confirmaria el canvi immediatament sense possibilitat de ROLLBACK.

PostgreSQL: Autocommit activat per defecte en psql. Per desactivar-lo: \set AUTOCOMMIT off.
MySQL/MariaDB: Autocommit activat per defecte. SET autocommit = 0; per desactivar-lo.
SQL Server: Autocommit activat per defecte. Cal BEGIN TRANSACTION explícit per iniciar una transacció.
Oracle: Les instruccions DML no fan autocommit; cal un COMMIT explícit. Les instruccions DDL (CREATE, ALTER, DROP, TRUNCATE) fan un COMMIT implícit automàticament. En SQL*Plus es pot activar amb SET AUTOCOMMIT ON.

Bones pràctiques

Per a operacions crítiques (càrregues massives, migracions, actualitzacions globals), sempre englobeu les operaciones en una transacció explícita. Aixo us permet verificar els resultats amb SELECT antes de confirmar amb COMMIT.

SAVEPOINT

Un SAVEPOINT és un punt de control dins d'una transacció. Permet fer ROLLBACK fins a aquell punt sense desfer tota la transacció.

PostgreSQLMySQL / MariaDBSQL ServerOracle

BEGIN;

INSERT INTO comandes (id_client, estat) VALUES (1, 'pendent');
SAVEPOINT sp_comanda;

INSERT INTO linies_comanda (id_comanda, id_producte, quantitat, preu_unitari)
VALUES (LASTVAL(), 5, 2, 29.99);
-- Suposem que aquí hi ha un error (producte inexistent, etc.)

ROLLBACK TO SAVEPOINT sp_comanda;
-- Desfem la linia de comanda però mantenim la capçalera

-- Podem intentar una inserció corregida
INSERT INTO linies_comanda (id_comanda, id_producte, quantitat, preu_unitari)
VALUES (LASTVAL(), 3, 2, 19.99);  -- Producte correcte

COMMIT;
-- Hem confirmat la comanda amb la linia corregida

START TRANSACTION;

INSERT INTO comandes (id_client, estat) VALUES (1, 'pendent');
SAVEPOINT sp_comanda;

-- ... operació que pot fallar ...
ROLLBACK TO SAVEPOINT sp_comanda;

COMMIT;

BEGIN TRANSACTION;

INSERT INTO comandes (id_client, estat) VALUES (1, 'pendent');
SAVE TRANSACTION sp_comanda;

-- ... operació que pot fallar ...
ROLLBACK TRANSACTION sp_comanda;

COMMIT TRANSACTION;

-- La transacció s'inicia implícitament amb la primera DML
INSERT INTO comandes (id_client, estat) VALUES (1, 'pendent');
SAVEPOINT sp_comanda;

INSERT INTO linies_comanda (id_comanda, id_producte, quantitat, preu_unitari)
VALUES (comandes_seq.CURRVAL, 5, 2, 29.99);
-- Suposem que aquí hi ha un error

ROLLBACK TO SAVEPOINT sp_comanda;
-- Desfem la línia però mantenim la capçalera de la comanda

INSERT INTO linies_comanda (id_comanda, id_producte, quantitat, preu_unitari)
VALUES (comandes_seq.CURRVAL, 3, 2, 19.99);  -- Producte correcte

COMMIT;

Nivells d'aïllament

Els nivells d'aïllament controlen fins a quin punt una transacció veu els canvis que estan fent altres transaccions concurrents. Hi ha un compromís entre consistència i rendiment: més aïllament → més consistència → menys concurrencia.

Anomalies de concurrencia

Anomalia	Descripció
Lectura bruta (Dirty read)	Llegim dades d'una transacció no confirmada que potser farà ROLLBACK
Lectura no repetible	Rellegim una fila i el valor ha canviat (una altra transacció la va modificar i confirmar)
Lectura fantasma	Rellegim un rang de files i n'han aparegut o desaparegut noves

Nivells estàndard SQL

nivell	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Impossible	Possible	Possible
REPEATABLE READ	Impossible	Impossible	Possible
SERIALIZABLE	Impossible	Impossible	Impossible

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Establir el nivell d'aïllament per a la sessió
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- O per a una transacció específica
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... operacions ...
COMMIT;

-- nivell per defecte a PostgreSQL: READ COMMITTED
-- (MVCC garanteix que READ COMMITTED no dona dirty reads)

-- Establir el nivell per a la sessió
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- nivell per defecte a MySQL/InnoDB: REPEATABLE READ
-- (prevé dirty reads i non-repeatable reads)

-- Establir el nivell per a la sessió
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- nivell per defecte: READ COMMITTED
-- SQL Server 2005+ amb READ_COMMITTED_SNAPSHOT activat
-- usa MVCC per a READ COMMITTED (millor concurrencia)

-- Oracle suporta només READ COMMITTED (per defecte) i SERIALIZABLE
-- No existeixen READ UNCOMMITTED ni REPEATABLE READ a Oracle

-- Per a una transacció específica:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Per a la sessió:
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;

-- Oracle usa MVCC nativament: els dirty reads són impossibles
-- fins i tot a READ COMMITTED (a diferencia d'altres motors)

Visualització de les anomalies de concurrència

Anomalies de concurrència — pas a pas

Dirty Read (Lectura bruta) T2 actualitza el saldo però no fa COMMIT. T1 llegeix el valor no confirmat (700 €). T2 fa ROLLBACK: el valor real mai va ser 700 €, però T1 ja l'ha usat. Possible amb READ UNCOMMITTED.

#

T1 (consulta)

T2 (transferència)

saldo real a BD

1

BEGIN

—

1 000 €

2

SELECT saldo → 1 000 €

—

1 000 €

3

—

BEGIN + UPDATE saldo = 700

700 € (sense COMMIT)

4

SELECT saldo → 700 € (DIRTY!)

—

700 € (no confirmat)

5

—

ROLLBACK

1 000 € (restaurat)

6

COMMIT

—

1 000 €

T1 ha llegit 700 € que mai va existir: LECTURA BRUTA Possible amb: READ UNCOMMITTED Evitat per: READ COMMITTED, REPEATABLE READ, SERIALIZABLE

Non-Repeatable Read (Lectura no repetible) T1 llegeix el saldo dues vegades. Entre les dues lectures, T2 confirma un UPDATE. T1 obté valors diferents per a la mateixa consulta dins la mateixa transacció. Possible amb READ COMMITTED.

#

T1 (auditoria)

T2 (actualitza saldo)

saldo real a BD

1

BEGIN

—

1 000 €

2

SELECT saldo → 1 000 €

—

1 000 €

3

— (processant...)

BEGIN → UPDATE saldo = 700 → COMMIT

700 € (confirmat!)

4

SELECT saldo → 700 € (diferent!)

—

700 €

5

COMMIT (ha vist 2 valors diferents)

—

700 €

La mateixa SELECT dins T1 retorna resultats diferents: LECTURA NO REPETIBLE Possible amb: READ UNCOMMITTED, READ COMMITTED Evitat per: REPEATABLE READ, SERIALIZABLE

Phantom Read (Lectura fantasma) T1 consulta quants comptes tenen saldo > 500 €. T2 insereix un compte nou amb saldo 800 € i fa COMMIT. T1 torna a consultar i ara apareix una fila nova (un "fantasma"). Possible amb REPEATABLE READ en alguns motors.

#

T1 (recompte)

T2 (nou compte)

Files amb saldo > 500

1

BEGIN

—

3 files

2

SELECT COUNT(*) WHERE saldo > 500 → 3

—

3 files

3

— (processant...)

INSERT compte (saldo=800) → COMMIT

4 files (nova fila!)

4

SELECT COUNT(*) WHERE saldo > 500 → 4 (fantasma!)

—

4 files

Apareix una fila nova que no existia al principi de la transacció: LECTURA FANTASMA Possible amb: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (alguns motors) Evitat per: SERIALIZABLE

REPEATABLE READ / SERIALIZABLE — protecció completa Amb REPEATABLE READ o SERIALIZABLE, T1 veu sempre el mateix "snapshot" de les dades que existia quan va iniciar la transacció. Cap modificació externa afecta el que T1 llegeix.

#

T1 (protegida)

T2 (actualitza)

Que veu T1

1

BEGIN (snapshot pres)

—

1 000 €

2

SELECT saldo → 1 000 €

—

1 000 €

3

—

UPDATE saldo = 700 → COMMIT

1 000 € (snapshot intacte)

4

SELECT saldo → 1 000 € (igual!)

—

1 000 € (snapshot)

5

COMMIT

—

700 € (valor real ara)

T1 sempre veu el valor consistent del moment en que va iniciar PostgreSQL i MySQL/InnoDB usen MVCC: les lectures no bloquegen les escriptures

AC0372/05/03 — Miniactivitat

RA5 · CA5.5, CA5.6

Demostreu el funcionament de les transaccions amb una simulació de transferència bancària:

Creeu una taula comptes(id_compte, titular, saldo) i inseriu 3 comptes amb saldo inicial de 1000€ cadascun.
Escriviu un script SQL que transfereixi 200€ del compte 1 al compte 2, usant una transacció explícita.
Simuleu un error (per exemple, intenteu transferir a un compte inexistent) i verificeu que el ROLLBACK restaura el saldo original.
Demostreu el comportament del SAVEPOINT transferint primer una part i usant ROLLBACK TO SAVEPOINT si la segona part falla.