A 09-01 vas veure què fa una transacció. Ara toca què garanteix exactament, i la resposta cap en quatre lletres: ACID — atomicitat, consistència, aïllament i durabilitat. No són una etiqueta de màrqueting: són quatre promeses concretes, cadascuna amb un mecanisme al darrere, cadascuna amb una manera coneguda de trencar-se i cadascuna amb un límit que convé saber on és.

I hi ha una recompensa al final de la lliçó. La quarta lletra ens portarà al WAL, el registre que fa que les teves dades sobrevisquin a un tall de corrent i que tanca la promesa que 05-04 va deixar oberta sobre recuperar un esborrat. I la tercera ens portarà per fi a MVCC, el model amb què PostgreSQL aconsegueix que una sessió llegeixi sense bloquejar ningú mentre una altra escriu la mateixa fila — el mecanisme que explica, exactament, les files mortes, el bloat i la necessitat de VACUUM que 08-05 va deixar pendent.

1. Les quatre garanties d'un cop d'ull
2. Atomicitat: tot o res
3. Consistència: d'un estat vàlid a un altre estat vàlid
4. Isolation (aïllament): com si fossin seqüencials
5. Durabilitat: el WAL, fsync i synchronous_commit
6. MVCC: aïllament sense bloquejar les lectures
7. De MVCC al bloat: per què existeix VACUUM
8. Com implementa cada motor l'aïllament
9. El que ACID no et garanteix
10. Errors habituals i consells
11. Exercicis
12. Conclusió

Una manera útil de recordar-les: A i D protegeixen davant de les fallades (errors, caigudes, talls de llum); C i I protegeixen davant dels altres (les teves pròpies regles i les altres sessions).

És la que ja has vist: els quatre passos de confirmar una comanda són un de sol. Però la pregunta interessant és com ho fa el motor, perquè la resposta de PostgreSQL sorprèn qui ve d'altres sistemes.

En un motor amb registre de desfer (undo log) —InnoDB, Oracle— l'UPDATE sobreescriu la fila al seu lloc i desa a part una còpia del valor anterior. El ROLLBACK consisteix a rellegir aquest registre i restaurar físicament els valors vells: com més gran la transacció, més car desfer-la.

PostgreSQL no fa això. Un UPDATE no sobreescriu res: escriu una versió nova de la fila i deixa la vella on era, marcada amb l'identificador de la transacció que la va substituir. Desfer és llavors trivial:

A PostgreSQL, un ROLLBACK no restaura res: es limita a anotar a pg_xact que aquesta transacció va avortar. A partir d'aquest instant, totes les versions de fila que ella va escriure passen a ser invisibles per a tothom, i les velles tornen a ser les bones.

Les conseqüències pràctiques d'aquesta decisió de disseny són grans i les notaràs a la feina:

Per això el BEGIN ... ROLLBACK que 08-05 recomanava per analitzar un DELETE amb EXPLAIN ANALYZE és tan barat a PostgreSQL: desfer no costa res. I per això, a canvi, PostgreSQL necessita un VACUUM que els altres no necessiten — apartat 7.

La C és la lletra pitjor explicada de les quatre, perquè sona a "les dades són correctes" i no és això. El que promet és més modest i més útil:

Si la base complia totes les regles declarades abans de la transacció, les continuarà complint després. La transacció pot violar-les durant la seva execució; el que no pot és deixar-les violades en confirmar.

I aquí hi ha la lletra petita: "regles declarades". La base de dades garanteix exactament el que li has dit, ni una regla més. Tot el de 05-01 hi compta:

I això no ho impedeix res de l'esquema: que un client ressenyi un producte que no ha comprat mai; que una comanda lliurat no tingui cap línia; que la suma de les línies no quadri amb l'import cobrat; que es vengui un producte actiu = FALSE. Són regles de negoci no declarades, i la base de dades no sap que existeixen.

"L'estoc no pot quedar mai negatiu" es pot fer complir de dues maneres, i convé entendre que no són equivalents.

Forma 1 — declarativa. És la que BotigaVerda ja té:

Avantatge: és inviolable. Tant se val qui escrigui, des de quina aplicació, amb quin llenguatge o a les tres de la matinada. Inconvenient: l'única resposta possible és un error, que a més avorta la transacció sencera (09-01) i obliga el teu codi a interpretar un missatge.

Forma 2 — a la sentència. Un UPDATE condicional que no compra si no hi ha existències:

Zero files, sense error i sense avortar res. La teva aplicació llegeix el comptador de files afectades —l'UPDATE N de 05-03, el :ROW_COUNT de 09-01— i si és 0 respon "producte esgotat" al client. I hi ha una propietat enorme amagada aquí: aquest UPDATE també és correcte davant de la concurrència, perquè la comprovació (stock >= 1) i l'escriptura passen a la mateixa sentència atòmica. És la mateixa lliçó de 05-05 amb l'upsert: comprovar i després actuar en dues sentències no és mai segur.

La regla d'or de la C: fes servir totes dues. El CHECK és la xarxa que garanteix l'invariant passi el que passi; el WHERE condicional és el que converteix "excepció" en "flux normal de negoci". La primera protegeix les dades, la segona protegeix l'experiència de l'usuari.

El que la base no pot fer per tu és la part que no li has explicat. Regles com ara "una comanda pagada ha de tenir almenys una línia" o "no es pot ressenyar el que no s'ha comprat" necessiten una restricció declarada que avui no existeix, un trigger (10-05) o codi d'aplicació dins de la mateixa transacció. La consistència és una responsabilitat compartida, i la base de dades només signa la seva meitat.

La promesa, en una frase:

Diverses transaccions executant-se alhora han de produir el mateix resultat que si s'haguessin executat una darrere l'altra, en algun ordre.

Ja la vas veure funcionant a 09-01: mentre A tenia l'stock del matcha a 39 sense confirmar, B continuava llegint 40. Per a B, la transacció d'A encara no havia començat; després del COMMIT, havia passat sencera. Mai a mitges.

I aquí hi ha el matís que fa que aquesta sigui la lletra més interessant de les quatre: l'aïllament és negociable. Les altres tres són tot o res, però d'aquesta en pots demanar més o menys:

Aquesta negociació són els nivells d'aïllament, amb els seus quatre esglaons i les seves quatre anomalies, i és el contingut íntegre de 09-04. Aquí n'hi ha prou de saber que existeix la palanca i que PostgreSQL la porta posada per omissió a READ COMMITTED.

Promesa: si el servidor t'ha dit COMMIT, les dades hi són, encara que li arrenquis el cable un microsegon després.

El problema és que escriure al disc és lent i les pàgines de dades estan disperses pel fitxer. Si cada COMMIT hagués d'escriure al disc totes les pàgines modificades, a les seves posicions aleatòries, i esperar que hi arribessin, el rendiment seria inacceptable. La solució, universal a tots els motors seriosos, és el registre d'escriptura anticipada: WAL (Write-Ahead Log).

La idea clau és l'ordre: primer el registre, després les dades (d'aquí "escriptura anticipada"). El WAL és un fitxer seqüencial, i escriure seqüencialment és ordres de magnitud més ràpid que escriure en posicions aleatòries. En l'instant del COMMIT els fitxers de dades poden estar completament desactualitzats; tant se val, perquè el WAL ja conté la recepta per reconstruir-los.

I fsync és la paraula crítica: escriure no n'hi ha prou, perquè el sistema operatiu i el mateix disc tenen les seves memòries cau. fsync és la crida que obliga que la dada sigui físicament al mitjà persistent abans de continuar.

Aquest fsync és l'única cosa que hi ha entre el teu COMMIT i una resposta instantània. PostgreSQL et deixa negociar-ho:

És un ajust legítim —i molt rendible— en una càrrega de dades reexecutable o en una taula de mètriques. És inacceptable en una comanda o un cobrament.

⚠️ El que sí que és una barbaritat: fsync = off. No confonguis els dos paràmetres. Amb synchronous_commit = off pots perdre les últimes transaccions, però la base queda íntegra. Amb fsync = off pots perdre la base sencera: les escriptures arriben al disc en qualsevol ordre i la recuperació deixa un fitxer de dades corrupte i irreparable. Està pensat per a bancs de proves d'un sol ús i per a res més.

El WAL no serveix només per recuperar-se d'una caiguda. Com que és un registre complet i ordenat de tot el que ha canviat, serveix per a dues coses més de les quals aquí només direm el nom, perquè són matèria d'administració:

• Replicació. Si envies el WAL a un segon servidor i aquest el va aplicant, tens una còpia viva de la base. És el fonament de l'alta disponibilitat i de les rèpliques de només lectura per a informes.
• PITR (Point-In-Time Recovery). Amb una còpia base més tot el WAL posterior arxivat, pots restaurar la base en qualsevol instant concret, per exemple 2026-03-05 11:59:58, dos segons abans d'aquell DELETE FROM comandes; sense WHERE. Aquesta és la promesa que 05-04 va deixar oberta: no hi ha "desfer" per a una cosa ja confirmada, però sí que hi ha una màquina del temps, sempre que algú hagués configurat l'arxivat abans de l'accident.

Ja ho hem esmentat tres vegades; toca obrir-ho. MVCC significa Multi-Version Concurrency Control: control de concurrència per multiversió.

La idea, en una frase: cada fila pot existir en diverses versions alhora, i cada transacció veu la versió que li correspon segons quan va començar.

D'aquí surt la propietat més valuosa de PostgreSQL en concurrència:

Els lectors no bloquegen mai els escriptors, i els escriptors no bloquegen mai els lectors.

Per això a 09-01 la sessió B va poder llegir l'estoc del matcha sense esperar ni un mil·lisegon mentre A el modificava: A va escriure una versió nova, i B va continuar llegint la vella, que per a ella era la bona.

Cada versió de fila porta dues columnes de sistema que pots consultar encara que no apareguin a SELECT *:

Mira-ho en viu. Recarrega la base i executa:

(Els números de transacció depenen de la teva instal·lació; el que importa són les relacions entre ells.) La fila la va crear la transacció 748 —l'INSERT de càrrega— i ningú no l'ha tocat des de llavors (xmax = 0). Ara modifica-la:

Tres coses han canviat alhora, i són tota l'explicació d'MVCC:

1. L'xmin és un altre: aquesta és una fila nova, creada per la transacció 812.
2. El ctid és un altre: és en un altre lloc físic de la pàgina. La fila vella continua allà, a (0,15), amb el seu xmax ara posat a 812.
3. La taula ocupa una fila més al disc, encara que SELECT COUNT(*) continuï retornant 20.

I això és literalment el que significava aquella frase de 08-05: "un UPDATE no modifica la fila: escriu una versió nova i marca la vella com a morta". Ara ja saps amb què la marca: amb el seu xmax.

Quan una transacció necessita decidir quines versions veu, pren una instantània (snapshot): la llista de quines transaccions estaven confirmades en aquell moment. Amb ella, la regla de visibilitat de cada versió és senzilla:

Una versió és visible si el seu xmin està confirmat i és anterior a la meva instantània, i el seu xmax és buit, avortat, o és posterior a la meva instantània.

Aquí hi ha tot el que vas veure a 09-01 sense explicació:

• A veu el seu propi canvi perquè l'xmin de la versió nova és la seva pròpia transacció.
• B no el veu perquè aquest xmin correspon a una transacció encara no confirmada, i per a B això equival a inexistent.
• Després del COMMIT, la instantània següent de B ja inclou A, i la versió nova passa a ser visible.
• I un ROLLBACK no necessita esborrar res: n'hi ha prou que la transacció quedi marcada com a avortada a pg_xact perquè el seu xmin no validi cap de les seves versions.

Quan es pren la instantània és exactament la diferència entre els nivells d'aïllament: a READ COMMITTED se'n pren una de nova a cada sentència; a REPEATABLE READ i SERIALIZABLE, una de sola per a tota la transacció. Aquesta única frase explica el 90 % de 09-04.

I ara la factura. Si cada UPDATE deixa una versió morta i cada DELETE es limita a posar un xmax, la taula només creix. Les versions que ja no són visibles per a cap transacció són les files mortes (dead tuples), i l'espai que ocupen és el bloat de 08-05.

Vint files vives i cinc cadàvers, després de cinc actualitzacions d'una sola fila. Aquí hi ha el cercle complet, i aquesta és la cadena sencera que 08-05 va deixar a mitges:

I així queden explicades les tres coses que 08-05 va anunciar i no va poder justificar:

1. Per què hi ha files mortes. Perquè són les versions antigues que MVCC va necessitar per no bloquejar ningú.
2. Per què una transacció oberta impedeix netejar. Perquè VACUUM només pot eliminar una versió si cap transacció viva no la pot necessitar. Una sessió idle in transaction des de fa tres hores sosté una instantània de fa tres hores, i amb ella totes les versions mortes de tota la base. És el dany de l'apartat 11 de 09-01, ara amb el seu mecanisme.
3. Per què existeix CREATE INDEX CONCURRENTLY. Perquè construir un índex normal necessita bloquejar les escriptures de la taula, i MVCC permet fer-ho sense aquest bloqueig a canvi de recórrer la taula dues vegades. El detall és de 09-05.

Nota de dialecte: el vocabulari enganya. "REPEATABLE READ" significa coses diferents a PostgreSQL i a InnoDB, i "READ COMMITTED" no es comporta igual a PostgreSQL que a SQL Server sense snapshot. El nivell d'aïllament no és portable: és el primer que cal verificar en migrar una aplicació entre motors, i el detall és a 09-04.

Quatre límits honestos, perquè ACID se cita molt més del que s'entén:

1. No garanteix que la teva lògica sigui correcta. Una transacció perfectament atòmica, consistent, aïllada i duradora pot descomptar l'estoc del producte equivocat. ACID garanteix que es farà sencera i per sempre; que sigui el que calia fer és cosa teva.
2. No garanteix les regles que no vas declarar. La C només signa el que hi ha a l'esquema (apartat 3).
3. No s'estén més enllà de la base de dades. Aquest és l'important al món real: si la teva transacció descompta l'estoc i a més crida la passarel·la de pagament, la passarel·la no participa en el teu ROLLBACK. Pots desfer la comanda; no pots desfer el càrrec. Per això 09-03 insistirà que cap crida externa no ha de viure dins d'una transacció oberta, i per això existeixen patrons com l'outbox o les sagues.
4. No sobreviu intacte al repartiment entre diverses bases de dades. Coordinar un COMMIT entre dos servidors exigeix un protocol de dues fases, que és lent i fràgil.

Aquest últim punt és la raó que en sistemes distribuïts aparegui l'acrònim BASE (Basically Available, Soft state, Eventually consistent): en lloc de garantir coherència en tot moment, es garanteix que el sistema convergeix a un estat coherent. És un compromís deliberat, no una versió defectuosa d'ACID — però és un compromís que només té sentit quan el repartiment és inevitable. En una base de dades relacional d'un sol servidor, ACID és complet, gratis i no hi ha cap raó per renunciar-hi.

• Creure que la C significa "les dades són correctes". Significa "es respecten les regles declarades". El que no és a l'esquema, no està garantit.
• Confiar la validació només a l'aplicació. Demà hi haurà un script de migració, un becari amb psql o una segona aplicació. El CHECK hi és sempre; el teu if només és al teu codi.
• Confiar només en el CHECK. Avorta la transacció sencera i converteix un cas de negoci normal —"esgotat"— en una excepció. Afegeix-hi el WHERE stock >= 1 i llegeix les files afectades.
• Confondre synchronous_commit = off amb fsync = off. El primer arrisca les últimes transaccions; el segon arrisca la base sencera.
• Suposar que un ROLLBACK gran és car. A PostgreSQL és pràcticament gratis; a InnoDB i Oracle pot trigar més que la mateixa operació. És al revés del que gairebé tothom espera.
• Creure que DELETE allibera espai, o que UPDATE reescriu la fila. Cap de les dues coses: deixen versions mortes, i l'espai només es reutilitza després de VACUUM.
• Deixar una transacció oberta i després queixar-se del bloat. Són la mateixa cosa: la instantània vella impedeix netejar.
• Ficar una crida HTTP dins d'una transacció. ACID acaba a la vora de la base de dades; el càrrec a la targeta no es desfà amb un ROLLBACK.
• Consell: declara les regles dues vegades, a l'esquema i a la sentència. La primera protegeix les dades; la segona, l'experiència de l'usuari.
• Consell: mira xmin, xmax i ctid una vegada a la vida. Veure físicament com un UPDATE crea una fila nova val per deu explicacions d'MVCC.
• Consell: si t'importen les teves dades, comprova avui que tens còpies i arxivat de WAL. El PITR no s'improvisa després de l'accident.

Sobre la base acabada de recarregar, demostra experimentalment el model MVCC.

1. Consulta xmin, xmax, ctid i stock del producte 15 i anota'ls.
2. Executa UPDATE productes SET stock = stock - 1 WHERE id = 15; tres vegades seguides (en autocommit), consultant les mateixes columnes després de cadascuna.
3. Consulta n_live_tup i n_dead_tup a pg_stat_user_tables per a productes. Explica els dos números.
4. Executa VACUUM productes; i torna a mirar-los. Ha baixat la mida del fitxer de la taula (pg_relation_size('productes'))? Per què?
5. Ara repeteix el pas 2 dins de BEGIN … ROLLBACK. Quantes files mortes queden després del ROLLBACK? I per què el ROLLBACK va ser instantani?

BotigaVerda vol una regla nova: l'import d'una devolució no pot superar el total facturat de la seva comanda. Avui no ho impedeix res, i de fet ho pots comprovar:

1. S'insereix? Quina lletra d'ACID hi ha en joc i per què el motor no protesta?
2. Es pot expressar aquesta regla amb un CHECK? Raona-ho mirant quina informació necessita la comprovació.
3. Enumera tres maneres de fer-la complir, indicant a cadascuna qui la garanteix i quin forat deixa.
4. Escriu la consulta que detecta avui si alguna devolució de BotigaVerda viola la regla.

Un company proposa aquesta configuració per al servidor de producció de BotigaVerda, "perquè va més ràpid":

I afegeix: "total, tenim una rèplica i fem còpia de seguretat cada nit".

1. Explica, paràmetre a paràmetre, què es perd exactament en un tall de corrent.
2. Per què la rèplica no resol el problema de fsync = off?
3. En quin escenari concret seria raonable posar synchronous_commit = off, i en quin seria inacceptable? Posa un exemple de cadascun amb taules de BotigaVerda.
4. Quin mecanisme permetria recuperar la base a l'instant anterior a la fallada, i què cal haver configurat abans per poder-lo utilitzar?

Solució 1

1 i 2. L'xmin canvia a cada UPDATE (cadascun és una transacció diferent que crea una versió nova) i el ctid també, perquè cada versió ocupa una posició física nova. L'xmax de la versió visible és sempre 0; el que es posa al valor de la transacció nova és el de la versió anterior, que ja no veus.

3. n_live_tup = 20 i n_dead_tup = 3: vint files vives i tres versions mortes, una per UPDATE. La taula té ara 23 versions emmagatzemades per a 20 files lògiques.

4. Després de VACUUM productes;, n_dead_tup baixa a 0, però pg_relation_size no canvia. I és la part important de l'exercici: VACUUM marca l'espai com a reutilitzable per la mateixa taula, no el retorna al sistema operatiu. Per a això cal VACUUM FULL, que reescriu la taula sencera amb un bloqueig exclusiu (08-05). Per això el bloat es preveu i no es cura.

5. Queden igualment tres files mortes: les versions que la transacció va escriure abans de desfer-se continuen físicament allà, només que ara són invisibles perquè el seu xmin correspon a una transacció avortada. I el ROLLBACK va ser instantani precisament per això: no va restaurar res, només va anotar "avortada" a pg_xact (apartat 2). Desfer no esborra la feina feta: la deixa invisible i l'encarrega al VACUUM.

Solució 2

1. Sí que s'insereix. INSERT 0 1, sense cap queixa. La lletra en joc és la C: la base garanteix les regles declarades, i aquesta regla no està declarada enlloc. Les restriccions de devolucions només exigeixen que el comanda_id existeixi i que l'import sigui >= 0. Nou mil euros compleixen totes dues.

2. No, no es pot amb un CHECK. Un CHECK només pot mirar les columnes de la mateixa fila que s'està inserint. Per saber si 9999,00 € és massa cal sumar les línies de la comanda 1, és a dir, consultar una altra taula, i això un CHECK no ho permet (PostgreSQL rebutja subconsultes en un CHECK, precisament perquè el resultat podria canviar després i la restricció deixaria de complir-se sense que ningú toqués la fila).

3. Tres maneres:

4. La consulta que audita l'estat actual:

Sobre la base acabada de recarregar retorna 0 files: les tres devolucions (26,75 €, 34,02 € i 19,80 € sobre les comandes 6, 10 i 13) són totes per sota del total de la seva comanda. Amb la fila de 9999,00 € inserida, en retorna una.

Solució 3

1. Paràmetre a paràmetre:

2. La rèplica no salva res perquè replica el que el primari diu que va passar. Si el primari corromp les seves dades, propaga dades corruptes; i si la fallada es detecta dies després, la corrupció ja és a la rèplica i a les còpies de les últimes nits. Una rèplica protegeix de la fallada de maquinari d'un servidor, no de la corrupció lògica.

3. Raonable en una càrrega massiva i reexecutable: omplir una taula de mètriques o d'anàlisi amb SET LOCAL synchronous_commit = off, on perdre els últims segons només significa rellançar el procés. Inacceptable a comandes, linies_comanda i devolucions: perdre una comanda confirmada significa que el client va pagar i al sistema no hi ha res, i cap estalvi de latència no compensa això.

4. PITR. Amb una còpia base i l'arxivat continu del WAL es pot restaurar la base a qualsevol instant anterior a la fallada. El que cal tenir configurat abans —i aquesta és tota la moralitat— és wal_level = replica (o superior), archive_mode = on amb el seu archive_command, un destí d'arxiu fiable i còpies base periòdiques i provades. Una còpia de seguretat que no s'ha restaurat mai no és una còpia de seguretat: és una esperança.

Ja saps què prometen exactament les quatre lletres i què hi ha sota cadascuna:

• Atomicitat: tot o res. I a PostgreSQL el ROLLBACK no restaura res: marca la transacció com a avortada i les seves versions de fila deixen de ser visibles. Per això desfer és gratis aquí i car a InnoDB o Oracle.
• Consistència: d'un estat vàlid a un altre segons les regles declarades. CHECK, FK, UNIQUE i NOT NULL són la meitat que signa la base; l'altra meitat és teva. I el cas de l'estoc ensenya a fer servir les dues vies: el CHECK (stock >= 0) com a xarxa inviolable i l'UPDATE ... WHERE stock >= 1 com a flux de negoci, que a més és segur davant de la concurrència per ser una sola sentència.
• Aïllament: les transaccions concurrents es comporten com si fossin seqüencials. És l'única lletra negociable, i aquesta negociació són els nivells d'aïllament de 09-04.
• Durabilitat: el WAL s'escriu i se sincronitza abans que les dades, per això un COMMIT sobreviu a un tall de corrent. synchronous_commit és la palanca legítima; fsync = off no ho és. I del WAL surten també la replicació i el PITR, que és la resposta que 05-04 va deixar pendent sobre com recuperar un esborrat.
• MVCC: cada fila viu en diverses versions, marcades amb xmin i xmax, i cada transacció veu les que la seva instantània permet. D'aquí que els lectors no bloquegin els escriptors, i d'aquí que un UPDATE creï una fila nova amb un altre ctid i deixi la vella morta.
• I d'aquí el bloat: les versions mortes s'acumulen, VACUUM les marca com a reutilitzables, i una transacció oberta l'hi impedeix. La cadena que 08-05 va deixar a mitges queda tancada, inclosa la raó de ser de CREATE INDEX CONCURRENTLY.
• Els límits d'ACID: no valida la teva lògica, no endevina les teves regles, no s'estén a la passarel·la de pagament i no creua gratis la frontera d'un servidor — d'aquí BASE i els sistemes distribuïts.

Amb la teoria al seu lloc, toca el repertori complet de comandaments. A Instruccions de control de transaccions veuràs totes les opcions de BEGIN (ISOLATION LEVEL, READ ONLY, DEFERRABLE), els SAVEPOINT —que permeten desfer només una part d'una transacció i, de passada, rescatar-te de l'estat avortat de 09-01—, com fixar el nivell per omissió, com es comporta l'autocommit de psycopg, JDBC i SQLAlchemy, per què a PostgreSQL pots fer ROLLBACK d'un CREATE TABLE i a MySQL no, i el patró d'aplicació complet: try / except / rollback, reintents idempotents i la prohibició de cridar serveis externs amb una transacció oberta. Tot això construint, de principi a fi, la confirmació d'una comanda de BotigaVerda.