Transaccions i concurrència
Un dels objectius més importants d'un SGBD és permetre l'accés concurrent de molts usuaris a la mateixa base de dades preservant-ne la integritat, i garantir que les dades sobreviuen a fallades (una apagada, una avaria de disc, un incendi). La unitat bàsica que ho fa possible és la transacció.
Què és una transacció?
Una transacció és un conjunt d'operacions de lectura (SELECT) i/o actualització (INSERT, UPDATE, DELETE) que actua com una unitat lògica de processament i que s'executa com una unitat indivisible: o es fan totes les operacions, o no se'n fa cap. Una transacció acaba confirmant (COMMIT) o cancel·lant (ROLLBACK) els canvis.
Exemples de transaccions
- Transferència bancària: treure X € d'un compte i posar-los en un altre.
- Venda a un client: guardar la factura i actualitzar l'estoc de cada producte venut.
- Reserva d'un vol: guardar les dades del client i decrementar les places disponibles.
- Ingrés hospitalari: reservar habitació, quiròfan i agenda del personal sanitari.
En tots aquests casos, què passaria si la transacció quedés a mig fer (per exemple, per un tall elèctric)? La base de dades quedaria en un estat inconsistent.
Per què són necessàries?
Imagineu una transferència: 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, es perden.
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 UPDATE, el compte origen perd 50 € que mai arriben al destí. Amb una transacció, el ROLLBACK automàtic restaura el compte origen als 100 € originals.
Propietats ACID
Tota transacció ha de complir quatre propietats, conegudes per l'acrònim ACID:
| Propietat | Garanteix que... |
|---|---|
| A — Atomicitat | El conjunt d'operacions és una unitat: o es fan tots els canvis (COMMIT) o cap (ROLLBACK). Si la transacció s'avorta, l'SGBD desfà tot el que s'havia fet, com si mai s'hagués començat. |
| C — Consistència | Una transacció que parteix d'un estat consistent de la BD l'ha de deixar de nou en un estat consistent, tant físicament com lògicament (es respecten les restriccions: claus, CHECK, etc.). |
| I — Aïllament (Isolation) | El comportament d'una transacció no s'ha de veure afectat per l'execució concurrent d'altres transaccions: ha de funcionar com si estigués sola a la BD. |
| D — Definitivitat (Durabilitat) | Un cop confirmats, els resultats d'una transacció són permanents, encara que després es produeixin fallades o desastres. |
Com ho aconsegueix l'SGBD
L'atomicitat i la durabilitat s'aconsegueixen amb un registre de transaccions (write-ahead log): abans d'aplicar un canvi, l'SGBD l'apunta al log, de manera que pot refer (redo) o desfer (undo) operacions després d'una fallada. L'aïllament s'aconsegueix amb el control de la concurrència (bloquejos o MVCC), que veurem més avall.
El cicle de vida d'una transacció
stateDiagram-v2
[*] --> Activa
state "Parcialment confirmada" as PC
state "Confirmada" as OK
state "Fallida" as KO
state "Avortada" as AB
Activa --> PC: darrera operació
Activa --> KO: error / condició no vàlida
PC --> OK: COMMIT (canvis permanents)
KO --> AB: ROLLBACK (desfà els canvis)
OK --> [*]
AB --> [*]- Activa: la transacció s'està executant (des del
BEGINo la primera DML). - Parcialment confirmada: s'ha executat l'última operació però encara no s'ha escrit definitivament.
- Confirmada: s'ha fet
COMMIT; els canvis són permanents. - Fallida → Avortada: hi ha hagut un error; el
ROLLBACKdesfà tot i deixa la BD com estava.
BEGIN, COMMIT i ROLLBACK
-- 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;
L'estàndard SQL: COMMIT WORK / ROLLBACK WORK
L'SQL estàndard defineix COMMIT [WORK] i ROLLBACK [WORK] (la paraula WORK és opcional i només aclareix què fa la sentència). Segons l'estàndard, en obrir una sessió la primera sentència SQL inicia implícitament una transacció, que resta activa fins que s'indica explícitament que acaba.
Autocommit
Per defecte, la majoria de SGBD estan en mode autocommit: cada instrucció DML es confirma automàticament quan acaba. Això vol dir que UPDATE clients SET email = 'error' (sense WHERE) confirmaria el canvi immediatament sense possibilitat de ROLLBACK.
- PostgreSQL: autocommit activat per defecte a
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 TRANSACTIONexplícit per iniciar una transacció. - Oracle: les instruccions DML no fan autocommit; cal un
COMMITexplícit. Les instruccions DDL (CREATE,ALTER,DROP,TRUNCATE) fan unCOMMITimplícit automàticament. En SQL*Plus es pot activar ambSET AUTOCOMMIT ON.
Bones pràctiques
Per a operacions crítiques (càrregues massives, migracions, actualitzacions globals), engloba sempre les operacions en una transacció explícita. Això et permet verificar els resultats amb un SELECT abans 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ó.
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
-- 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;
La concurrència: per què és necessària
En un entorn multiusuari, moltes transaccions s'executen alhora. Amb una sola CPU, les operacions es van intercalant; amb diverses CPU, s'executen simultàniament.
Per què no executar-les una després de l'altra (en sèrie)? Perquè les operacions d'entrada/sortida (llegir del disc) són molt lentes. Mentre una transacció espera una operació d'E/S, l'SGBD dona pas a altres transaccions. Així s'aprofita la CPU i el rendiment global (transaccions per segon) és molt més alt. Si s'executessin en sèrie, el temps d'espera seria enorme.
El preu de la concurrència
Intercalar operacions de transaccions diferents sobre les mateixes dades pot deixar la BD en un estat incorrecte. Per això l'SGBD necessita mecanismes de control de la concurrència que garanteixin l'aïllament (la I d'ACID).
Problemes de la concurrència
Quan dues transaccions accedeixen a les mateixes dades sense control, poden aparèixer quatre problemes clàssics:
| # | Problema | Què passa |
|---|---|---|
| 1 | Actualització perduda (lost update) | Dues transaccions llegeixen el mateix valor i l'actualitzen; el segon COMMIT sobreescriu el primer, que es perd. |
| 2 | Lectura no confirmada (dirty read) | Una transacció llegeix dades modificades per una altra que encara no ha confirmat (i que potser farà ROLLBACK). |
| 3 | Lectura no repetible (non-repeatable read) | Una transacció llegeix la mateixa fila dues vegades i obté valors diferents perquè una altra transacció l'ha modificada i confirmada entremig. |
| 4 | Anàlisi inconsistent / lectura fantasma (phantom) | Es llegeix un conjunt de files (o s'agrega amb SUM/COUNT) mentre una altra transacció insereix o esborra files del rang: el resultat no correspon ni a l'estat inicial ni al final. |
Visualització dels problemes de concurrència
READ UNCOMMITTED.
READ COMMITTED.
REPEATABLE READ en alguns motors.
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.
Plans d'execució i operacions en conflicte
Com que les operacions de les transaccions s'executen intercalades, l'ordre concret d'execució s'anomena pla d'execució (o schedule, història, planificació). Per raonar sobre la correcció, s'usa una notació amb quatre operacions:
- L = lectura · E = escriptura · C = confirmar (commit) · A = avortar (abort)
- El subíndex indica la transacció i el parèntesi l'element de la BD accedit.
Així, E1(X) vol dir «la transacció 1 escriu l'element X». Dins d'un pla, les operacions de cada transacció han de mantenir el seu ordre relatiu, però es poden intercalar amb les d'altres transaccions:
Transacció 1: L1(X) , E1(X) , L1(Y) , E1(Y)
Transacció 2: L2(X) , E2(X)
Pla Pa: L1(X) , L2(X) , E1(X) , L1(Y) , E2(X) , C2 , E1(Y) , C1
Pla Pb: L1(X) , E1(X) , L2(X) , E2(X) , C2 , L1(Y) , A1
Operacions en conflicte
Dues operacions estan en conflicte quan es compleixen totes aquestes condicions:
- Pertanyen a transaccions diferents.
- Accedeixen al mateix element.
- Almenys una de les dues és una escriptura.
Conflictes al pla Pa
Al pla Pa, les operacions L1(X) i E2(X) estan en conflicte (mateix element X, una és escriptura), i també ho estan E1(X) i E2(X). En canvi, L1(X) i L2(X) no (totes dues són lectures), ni E2(X) i E1(Y) (elements diferents).
Els conflictes són, precisament, els que provoquen els quatre problemes de concurrència. L'estratègia dels SGBD consisteix a imposar regles (protocols) que evitin els plans incorrectes. El mecanisme més utilitzat és el bloqueig d'elements.
Control de la concurrència amb bloquejos
Per evitar que dues transaccions manipulin alhora el mateix element, l'SGBD fa servir candaus (bloquejos, locks): una variable associada a cada element que en descriu l'estat. La gestió dels bloquejos la fa l'SGBD automàticament; com a programadors normalment no n'hem de fer res.
Bloqueig binari
El candau més senzill té dos estats: bloquejat (1) o desbloquejat (0). Abans d'accedir a un element X, la transacció el bloqueja; quan acaba, el desbloqueja. Si una altra transacció el necessita mentre està bloquejat, s'ha d'esperar. Només pot desbloquejar l'element la transacció que l'ha bloquejat. És senzill però molt restrictiu: ni tan sols dues lectures simultànies són possibles.
Bloqueig de modus múltiple (compartit / exclusiu)
Per permetre més concurrència, es distingeixen dos tipus de bloqueig:
- Compartit (S, shared) — per llegir. Diverses transaccions poden tenir el bloqueig compartit del mateix element alhora.
- Exclusiu (X, exclusive) — per escriure. Només una transacció el pot tenir, i cap altra pot llegir ni escriure l'element mentre duri.
La matriu de compatibilitat resumeix quan es pot concedir un bloqueig segons el que ja hi ha:
| Ja concedit ↓ / Sol·licitat → | Compartit (S) | Exclusiu (X) |
|---|---|---|
| Cap bloqueig | ✔ concedit | ✔ concedit |
| Compartit (S) | ✔ concedit | ✖ espera |
| Exclusiu (X) | ✖ espera | ✖ espera |
En SQL: bloqueig explícit
Encara que l'SGBD bloqueja sol, de vegades convé forçar un bloqueig per evitar lost updates:
- PostgreSQL / MySQL / Oracle:
SELECT ... FOR UPDATE(bloqueig exclusiu de les files llegides) iSELECT ... FOR SHARE(compartit). - SQL Server:
SELECT ... WITH (UPDLOCK)oWITH (HOLDLOCK).
Així, la transacció que fa SELECT saldo ... FOR UPDATE obliga les altres a esperar abans de modificar aquesta fila.
El bloqueig en dues fases (2PL)
Bloquejar no n'hi ha prou: si els candaus s'alliberen massa aviat, encara es poden produir plans incorrectes. El protocol de bloqueig en dues fases (2PL, two-phase locking) hi posa remei amb una regla: totes les operacions de bloqueig han de precedir la primera operació de desbloqueig. Cada transacció té, doncs, dues fases:
- Fase d'expansió: només pot adquirir bloquejos (no en pot alliberar cap).
- Fase de contracció: només pot alliberar bloquejos (no en pot adquirir cap de nou).
Es demostra que si totes les transaccions segueixen el 2PL, tots els plans resultants són correctes (serialitzables). Hi ha variants:
| Variant de 2PL | Descripció |
|---|---|
| Bàsic | La regla general: tots els bloquejos abans del primer desbloqueig. |
| Conservador | La transacció bloqueja tots els elements que necessitarà abans de començar. Evita el bloqueig mortal, però cal saber per endavant els elements (poc pràctic). |
| Estricte | El més utilitzat: la transacció no allibera cap bloqueig exclusiu fins després de fer COMMIT o ROLLBACK. |
Bloqueig mortal i espera indefinida
El 2PL garanteix plans correctes, però introdueix dos problemes nous.
Bloqueig mortal (deadlock)
Passa quan T1 espera un element bloquejat per T2 i, alhora, T2 espera un element bloquejat per T1: cap de les dues pot avançar. Perquè hi hagi bloqueig mortal, totes dues han de voler un bloqueig d'escriptura sobre elements que l'altra reté.
graph LR
T1((T1)) -->|espera X<br/>bloquejat per T2| T2((T2))
T2 -->|espera Y<br/>bloquejat per T1| T1Un cicle en aquest graf d'espera = bloqueig mortal. Les solucions es divideixen en dues famílies:
| Estratègia | Com funciona |
|---|---|
| Prevenció — marques de temps | A cada transacció se li assigna una marca de temps (MT) única i creixent. Quan una transacció no pot bloquejar un element, es decideix segons l'ordre d'arribada fer-la esperar o avortar-la (i reiniciar-la després). |
| Prevenció — no esperar (no-wait) | Si una transacció no aconsegueix un bloqueig, s'avorta i es reinicia al cap d'un temps. |
| Prevenció — espera cautelosa | Ti pot esperar Tj només si Tj no està esperant cap element; altrament, Ti s'avorta. |
| Detecció — graf d'espera | L'SGBD manté el graf i, quan detecta un cicle, tria una víctima (algorisme de selecció de víctimes) i l'avorta. |
| Detecció — temps predefinits (timeout) | Si una transacció espera més d'un temps màxim, se suposa que hi ha bloqueig mortal i s'avorta. |
Als SGBD reals
PostgreSQL i MySQL/InnoDB detecten els bloquejos mortals automàticament i avorten una de les transaccions amb un error (deadlock detected / Deadlock found when trying to get lock). L'aplicació ha d'estar preparada per reintentar la transacció avortada. Paràmetres útils: deadlock_timeout (PostgreSQL) i innodb_lock_wait_timeout (MySQL). Per inspeccionar-ho: SHOW ENGINE INNODB STATUS a MySQL.
Espera indefinida (starvation)
Una transacció queda parada molt de temps mentre les altres avancen, normalment perquè el sistema afavoreix transaccions més prioritàries. Solucions:
- Cua FIFO: primer a arribar, primer a ser servit.
- Envelliment de prioritats (aging): les transaccions que esperen van augmentant la seva prioritat fins que s'executen.
Granularitat dels bloquejos
Un element de bloqueig pot tenir mides diferents: un camp, un registre (fila), una pàgina (bloc de disc), una taula o tota la BD. Aquesta mida es diu granularitat.
BD sencera ← granularitat gruixuda (bloqueja molt, poca concurrència)
└ taula
└ pàgina
└ registre (fila)
└ camp ← granularitat fina (bloqueja poc, molta concurrència però molts candaus)
Hi ha un compromís:
- Granularitat gruixuda (p. ex. bloquejar tota la taula): pocs candaus a gestionar, però poca concurrència (totes les altres transaccions esperen).
- Granularitat fina (p. ex. bloquejar un registre): molta concurrència, però l'SGBD ha de gestionar molts candaus, cosa que consumeix recursos.
La mida òptima depèn de les transaccions: si accedeixen a poques files, convé bloquejar a nivell de registre; si recorren gran part d'una taula (per exemple, un abonament d'interessos a tots els comptes), pot compensar bloquejar a nivell de taula.
Nivells d'aïllament
En la pràctica, l'aïllament total (SERIALIZABLE) té un cost en concurrència. Per això l'SQL estàndard permet relaxar el nivell d'aïllament quan sabem que certes interferències no passaran o no ens importen. Els nivells d'aïllament controlen fins a quin punt una transacció veu els canvis d'altres transaccions concurrents: més aïllament → més consistència → menys concurrència.
Cada nivell evita uns problemes concrets (de menys a més estricte):
| Nivell | Dirty read | Non-repeatable read | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | Impossible | Possible | Possible |
| REPEATABLE READ | Impossible | Impossible | Possible |
| SERIALIZABLE | Impossible | Impossible | Impossible |
-- 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)
-- 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)
Bloquejos vs. MVCC
Molts SGBD moderns (PostgreSQL, MySQL/InnoDB, Oracle) no bloquegen les lectures: usen MVCC (Multi-Version Concurrency Control), que manté diverses versions de cada fila. Així, les lectures veuen una versió consistent sense esperar les escriptures, i «les lectures no bloquegen les escriptures ni a l'inrevés». Els bloquejos segueixen sent necessaris per a les escriptures i per al SELECT ... FOR UPDATE.
Visió externa: l'estàndard SQL
L'SQL estàndard formalitza tot això amb dues sentències que inicien una transacció i en fixen les característiques:
-- SQL-92: fixa les característiques (no ha d'haver-hi cap transacció activa)
SET TRANSACTION READ ONLY; -- la transacció només consultarà
SET TRANSACTION READ WRITE; -- la transacció podrà modificar
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- relaxa/fixa l'aïllament
-- SQL-99: inicia explícitament una nova transacció
START TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED;
-- Fi de la transacció
COMMIT WORK; -- confirma els canvis (WORK és opcional)
ROLLBACK WORK; -- els desfà
READ ONLYpermet a l'SGBD optimitzar (sap que no hi haurà escriptures);READ WRITEés el mode per defecte.- Si no s'especifica cap nivell, l'estàndard considera
SERIALIZABLEcom a nivell per defecte (tot i que cada motor en tria un de propi, com hem vist).
AC0372/05/03 — Miniactivitat: transaccions i SAVEPOINT
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.
AC0372/05/04 — Miniactivitat: concurrència, bloquejos i deadlock
RA5 · CA5.5, CA5.6
Amb dues sessions obertes contra la mateixa base de dades (dos terminals psql/mysql), reproduïu els fenòmens de concurrència:
- Lost update: sense
FOR UPDATE, feu que les dues sessions llegeixin el mateix saldo i l'actualitzin; comproveu que un canvi es perd. Repetiu-ho ambSELECT ... FOR UPDATEi observeu com la segona sessió espera. - Dirty read / non-repeatable read: canvieu el nivell d'aïllament (
SET TRANSACTION ISOLATION LEVEL ...) i comproveu quins problemes apareixen o desapareixen. - Deadlock: feu que la sessió A bloquegi la fila 1 i després demani la 2, i la sessió B bloquegi la 2 i després demani la 1. Observeu com l'SGBD detecta el bloqueig mortal i avorta una de les transaccions; captureu el missatge d'error i expliqueu com reintentaríeu la transacció.