Durant cinc mòduls hem mirat l'esquema de BiblioRed com es mira un plànol: sobre la taula, quiet, amb temps per discutir si aquella columna sobra o si aquella clau forana falta. El plànol ja està bé. Les taules estan normalitzades, les restriccions escrites i les quatre desnormalitzacions justificades per escrit.

Aquest matí el plànol s'ha convertit en un edifici. El sistema ha entrat en producció a les quatre sucursals de Vallmar i, amb ell, ha entrat el món real: hi ha dues persones al taulell de la sucursal Centre registrant préstecs alhora, hi ha un soci pagant una multa amb targeta mentre el servidor decideix apagar-se, i hi ha una consulta que al portàtil de desenvolupament trigava 30 mil·lisegons i aquí triga catorze segons.

Aquest mòdul tracta de tot això. I comença per la peça sobre la qual descansa la resta: la transacció.

Una transacció és la resposta de les bases de dades a una pregunta incòmoda: què passa si una operació s'interromp per la meitat? No és una pregunta teòrica. El servidor s'apaga, el procés mor, la xarxa es talla, l'aplicació llança una excepció, l'operari tanca la finestra. L'única pregunta rellevant no és si passarà, sinó què queda a la base de dades quan passa. I la resposta que dóna una base de dades transaccional és tan simple com radical: queda tot, o no queda res.

En aquesta lliçó veurem què és una transacció i per què existeix, com es controla des de SQL, com es comporta quan alguna cosa falla, i què signifiquen de debò les quatre lletres d'ACID —amb especial deteniment en la D de durabilitat i en el mecanisme que la fa possible, el registre d'escriptura anticipada o WAL, que és el mateix mecanisme que reapareixerà a la lliçó 06-04 quan parlem de còpies de seguretat.

1. El problema: registrar un préstec són tres operacions
2. Què és una transacció
3. Control de transaccions en SQL: BEGIN, COMMIT, ROLLBACK
4. El mode autoconfirmació: cada instrucció solta ja és una transacció
5. Punts de desament: SAVEPOINT, ROLLBACK TO i RELEASE
6. El cicle de vida d'una transacció
7. Atomicitat: tot o res
8. Consistència: d'un estat vàlid a un altre de vàlid
9. Aïllament: enunciat aquí, desenvolupat a 06-02
10. Durabilitat i el registre d'escriptura anticipada (WAL)
11. Punts de control i recuperació després d'una caiguda
12. El cost de la durabilitat i els paràmetres que la relaxen
13. Errors dins d'una transacció: el comportament de PostgreSQL
14. Transaccions i DDL
15. Bones pràctiques en escriure transaccions
16. Fora de PostgreSQL: SQLite i MongoDB

Comencem pel cas més comú de BiblioRed. Marta Alsina (sòcia 14) s'acosta al taulell de la sucursal Centre amb l'exemplar EJ-3081 d'"El mapa del temps". Tenia una reserva pendent sobre aquest material. La persona del taulell prem "Prestar".

El que l'aplicació ha de fer a la base de dades són tres operacions diferents:

Tres instruccions. Cadascuna, per separat, és correcta. I tanmateix el conjunt és una bomba, perquè hi ha dos moments en què el món es pot aturar:

Cap d'aquests estats intermedis no és un estat vàlid de la biblioteca. No existeix cap biblioteca en què un llibre estigui prestat i disponible alhora. Existeix a la base de dades perquè hem escrit tres instruccions on el negoci té un sol fet: "la Marta s'ha endut l'exemplar EJ-3081".

Fixa't que cap restricció del mòdul 4 no ens salva d'això. Un CHECK comprova una fila; una clau forana comprova una referència. Cap de les dues no pot expressar "aquestes tres instruccions van juntes". Necessitem una altra eina, d'una altra naturalesa: una que no parli de dades, sinó de temps.

Definició. Una transacció és una seqüència d'operacions sobre la base de dades que el gestor tracta com una sola unitat indivisible de treball: o s'apliquen totes les seves operacions, o no se n'aplica cap.

Tres conseqüències que convé tenir clares des del principi:

• La transacció és una unitat lògica, no tècnica. La seva mida la decideix el negoci, no el motor. "Registrar un préstec" és una transacció perquè a la biblioteca és un acte únic. Que siguin tres UPDATE o set és irrellevant.
• La frontera la marca qui escriu el codi. El gestor no pot endevinar que aquells tres UPDATE van junts. Algú l'hi ha de dir, i aquest algú ets tu.
• Una transacció no és només "un grup d'instruccions". És un grup d'instruccions amb quatre garanties associades —les propietats ACID— que el gestor es compromet a complir encara que es talli la llum.

L'acrònim ACID el van encunyar Theo Härder i Andreas Reuter el 1983, formalitzant idees que Jim Gray venia desenvolupant des dels anys setanta a IBM. Recordaràs del recorregut històric de 01-03 que aquest és exactament el període en què les bases de dades relacionals van passar de prototip de laboratori a sistema de producció bancari: sense transaccions fiables, aquell salt no hauria estat possible.

La resposta a les quatre, en un gestor transaccional, és no. Les veurem una a una a partir de l'apartat 7. Abans cal saber escriure-les.

El vocabulari és curt i no ha canviat en quaranta anys:

START TRANSACTION és la forma de l'estàndard SQL; BEGIN és la forma curta que PostgreSQL admet i que s'usa a la pràctica. Són equivalents.

El préstec de la Marta, escrit correctament:

Resultat esperat:

I si alguna cosa va malament pel mig —l'aplicació detecta que l'exemplar ja estava prestat, o el soci està donat de baixa— n'hi ha prou de canviar l'última línia:

Després del ROLLBACK la base de dades està exactament com estava abans del BEGIN. No hi ha préstec, l'exemplar continua disponible i la reserva continua activa. No cal "desfer a mà" res: desfer és responsabilitat del gestor, i ho fa bé.

Aquesta és la primera de diverses demostracions que has de reproduir obrint dos terminals amb psql connectats a la mateixa base de dades. N'anomenarem Sessió A i Sessió B a cadascuna. Executa les instruccions en l'ordre de la columna "moment": l'ordre importa, i aquí hi ha tot l'ensenyament.

Dues coses que cal aprendre aquí:

1. Dins de la transacció, A veu els seus propis canvis (t3). És coherent: A està treballant.
2. Fora de la transacció, B no veu res (t4) fins que A confirmi. És la propietat d'aïllament traient el cap. Què veu exactament cada sessió, en quin moment i sota quines regles, és el contingut íntegre de la lliçó 06-02; aquí només ens interessa constatar que la feina no confirmada és invisible per als altres.

Una pregunta raonable: si les transaccions s'obren amb BEGIN, què passa amb els centenars d'INSERT solts que vam escriure als mòduls 2 i 3, sense cap BEGIN a la vista? Estaven desprotegits?

No. Estaven dins d'una transacció, només que implícita.

Autoconfirmació (autocommit). Quan el client no ha obert una transacció explícita, el gestor embolcalla cada instrucció individual en la seva pròpia transacció, que es confirma automàticament si la instrucció té èxit i es desfà si falla.

És a dir, això:

es comporta internament com això:

I té una conseqüència molt útil que sol passar desapercebuda: una única instrucció SQL ja és atòmica. Si aquell UPDATE afecta 9.400 exemplars i falla al 9.399 perquè un viola un CHECK, no queden 9.398 files modificades: no en queda cap. L'estàndard exigeix exactament això, i tots els gestors seriosos ho compleixen.

Aquest últim punt té una mesura concreta. Carregar 200.000 files a prestecs fila a fila en autoconfirmació pot trigar diversos minuts; les mateixes 200.000 files dins d'un sol BEGIN ... COMMIT triguen uns segons. La diferència no és a l'INSERT, és al COMMIT: cada confirmació obliga a sincronitzar el registre amb el disc, i això ho veurem a l'apartat 10.

No tots els clients es comporten igual, i aquesta és una font inesgotable de sorpreses:

L'error clàssic amb psycopg és escriure un INSERT, no cridar commit(), tancar el programa i no trobar la fila. No s'ha perdut: s'ha desfet, que és justament el que ha de passar amb una transacció que mai no es va confirmar.

Un ROLLBACK és un instrument contundent: desfà la transacció sencera. De vegades es necessita alguna cosa més fina, i per a això hi ha els punts de desament.

Punt de desament (savepoint). Marca amb nom dins d'una transacció oberta que permet desfer la feina posterior a aquesta marca sense avortar la transacció completa.

Les tres instruccions:

La documentació sol presentar-los amb exemples artificials. Els dos usos reals són aquests:

Ús 1: operacions opcionals dins d'una operació obligatòria.

En registrar la devolució d'un préstec vençut, BiblioRed intenta emetre la multa corresponent. Si el càlcul de la multa falla —perquè el tipus de multa no està configurat per a aquell material, per exemple—, la devolució s'ha de registrar igualment: és intolerable que un soci no pugui retornar un llibre perquè el sistema de multes està mal configurat.

Resultat esperat en el camí feliç:

I en el camí amb fallada, després del ROLLBACK TO SAVEPOINT abans_multa, el COMMIT final confirma la devolució sense la multa. Que és exactament el que la biblioteca vol.

Ús 2: recuperar-se d'un error sense perdre la feina.

Aquest és l'ús decisiu a PostgreSQL, i s'entén millor a l'apartat 13: quan una instrucció falla dins d'una transacció, PostgreSQL avorta la transacció sencera i rebutja tot el que vingui després. Un punt de desament és l'única manera de sobreviure a un error i continuar. De fet, quan un controlador ofereix "reintentar aquesta instrucció", gairebé sempre està posant un SAVEPOINT implícit abans de cada instrucció.

Els punts de desament no són gratis: cadascun consumeix recursos interns del gestor. Posar-ne un abans de cada instrucció en un bucle de 100.000 iteracions degrada el rendiment de manera perceptible. Fes-los servir on hi ha una decisió real per prendre, no per sistema.

El comportament que hem vist respon a un autòmat molt senzill, present en qualsevol llibre de text i en la implementació de qualsevol gestor:

Els cinc estats, amb el seu significat pràctic:

Hi ha dos detalls que solen passar-se per alt i que aquí importen molt.

El primer: "parcialment confirmada" no és un tecnicisme. És l'instant crític. L'aplicació ha demanat COMMIT, el gestor ha aplicat els canvis en memòria, però encara no ha rebut la confirmació del disc que el registre està fora de perill. Si la màquina cau en aquell microsegon, la transacció no s'ha confirmat i es desfarà en arrencar. Per això el gestor no respon "COMMIT" al client fins a estar segur: la resposta al client és la promesa de durabilitat.

El segon: de "confirmada" no se surt. No existeix el "des-confirmar". Un ROLLBACK després d'un COMMIT no desfà res —obre una transacció buida i la desfà—. Si necessites revertir alguna cosa ja confirmada, has d'escriure l'operació inversa, o restaurar d'una còpia (lliçó 06-04). Aquesta irreversibilitat és una característica, no un defecte: és el que permet construir a sobre.

Atomicitat. Una transacció és indivisible: o s'apliquen totes les seves operacions o no se n'aplica cap. No existeix cap estat intermedi observable ni persistent.

Què garanteix. Que les tres instruccions del préstec de la Marta es comportin com una de sola. Que no existeixi mai a prestecs una fila sense el seu corresponent exemplars.estat = 'prestat'.

Què falla si no hi és. Exactament la taula d'estats intermedis de l'apartat 1: préstecs fantasma, exemplars prestats que figuren disponibles, reserves òrfenes. I el pitjor és que aquestes fallades són silencioses. No hi ha error, no hi ha traça, no hi ha excepció. Només una biblioteca que un dia descobreix que el seu inventari no quadra i no sap des de quan.

Com la implementa el gestor. Amb la informació necessària per desfer. Abans de modificar una dada, el gestor registra en algun lloc prou informació per tornar enrere:

• PostgreSQL no sobreescriu les files: cada UPDATE crea una versió nova de la fila i marca la vella com a obsoleta a partir d'aquella transacció. Desfer és tan simple com marcar la transacció com a avortada: les versions noves deixen de ser visibles per a tothom i les velles continuen allà. Aquest mecanisme és el MVCC, i el seu tractament complet és de 06-02. La conseqüència interessant és que a PostgreSQL un ROLLBACK és més barat que un COMMIT, al contrari que en altres gestors.
• Oracle, MySQL/InnoDB usen un segment de desfer (undo): guarden la imatge anterior de cada fila modificada i, en desfer, la reposen.

Tots dos camins arriben al mateix lloc. La diferència pràctica és que PostgreSQL paga després, netejant les versions mortes amb VACUUM (06-02), i els altres paguen durant, mantenint el segment de desfer.

Provoquem una fallada expressament enmig d'una transacció. Intentarem prestar un exemplar a un soci que no existeix (el 9999), amb la clau forana de 02-06 fent la seva feina:

Resultat esperat:

L'UPDATE havia funcionat. L'atomicitat l'ha esborrat del mapa. L'exemplar continua disponible, com ha de ser.

Consistència. Una transacció porta la base de dades d'un estat vàlid a un altre estat vàlid. Si la base complia totes les seves regles abans de la transacció, les compleix després.

Què garanteix. Que en confirmar, cap clau forana no apunti al no-res, cap CHECK no estigui violat, cap UNIQUE no estigui duplicat i cap NOT NULL no estigui buit. Tot el catàleg de restriccions de 04-04 i la integritat referencial de 02-06 continuen drets a l'altre costat del COMMIT.

Què falla si no hi és. Dades que contradiuen les regles del negoci: multes assignades a préstecs inexistents, inscripcions a esdeveniments esborrats, exemplars a sucursals que van tancar.

Com la implementa el gestor. Comprovant les restriccions. La majoria es comproven en executar cada instrucció; algunes es poden diferir al COMMIT si es van declarar DEFERRABLE, cosa que és imprescindible quan dues taules es referencien mútuament:

Si en arribar al COMMIT alguna restricció diferida no es compleix, el COMMIT falla i la transacció es desfà sencera. És la consistència fent la seva feina a l'últim segon.

Convé dir-ho, perquè l'estudiant que llegeixi sobre el tema s'ho trobarà: hi ha un consens ampli que la "C" d'ACID no està a la mateixa alçada que les altres tres.

Les raons són aquestes:

Si multes.import pot ser negatiu perquè ningú no va escriure el CHECK, la base de dades acceptarà -50,00 € sense protestar i haurà estat perfectament "consistent": no ha violat cap regla, perquè aquella regla no existia. La consistència del gestor és consistència respecte a les restriccions declarades, no respecte al sentit comú.

A més, l'atomicitat i l'aïllament ja impliquen bona part del que la C promet. Per això hi ha qui diu, amb raó, que ACID són "tres propietats i una lletra que quedava bé a l'acrònim". La postura útil per a un professional és intermèdia: la C és un recordatori que les regles del negoci han d'estar declarades a l'esquema perquè la transacció les pugui protegir. Aquest és exactament l'argument de l'apartat 21 de la lliçó 04-04 sobre quines regles van a la base i quines a l'aplicació.

Aïllament. Cada transacció s'executa com si fos l'única del sistema. Els resultats d'una transacció concurrent no interfereixen amb els d'una altra.

Què garanteix. Que puguis raonar sobre la teva transacció sense pensar en les altres onze que s'estan executant alhora.

Què falla si no hi és. Els dos taulells de Centre presten el mateix exemplar EJ-3081 alhora. Dos socis ocupen la mateixa última plaça del club de lectura. Un informe suma xifres d'un instant i xifres d'un altre, i no quadra amb res.

Com la implementa el gestor. Amb bloqueigs, amb control multiversió (MVCC), o amb una combinació de tots dos.

I aquí ens aturem deliberadament. L'aïllament és, amb diferència, la més complexa de les quatre propietats: és l'única que admet graus —l'estàndard SQL defineix quatre nivells i cadascun permet uns fenòmens i en prohibeix uns altres—, és l'única en què el gestor et deixa triar quanta garantia vols a canvi de quant rendiment, i és la font dels errors més difícils de reproduir de tota aquesta professió.

Tot això és el contingut íntegre de la lliçó 06-02: els fenòmens de concurrència un a un amb dues sessions reproduïbles, els quatre nivells d'aïllament i la seva taula canònica, els bloqueigs compartits i exclusius, el MVCC de PostgreSQL, els interbloqueigs i el bloqueig optimista enfront del pessimista. Aquí ens n'hi ha prou amb la definició i amb saber que l'aïllament existeix, que té nivells, i que el nivell per omissió de PostgreSQL —READ COMMITTED— no és el més estricte.

Durabilitat. Un cop el gestor ha respost COMMIT, els canvis sobreviuen a qualsevol fallada posterior: tall de llum, apagada del procés, caiguda del sistema operatiu.

Què garanteix. Que quan el taulell veu "Préstec registrat", el préstec existeix. Encara que l'edifici es quedi sense llum mig segon després.

Què falla si no hi és. Que la biblioteca cregui que ha cobrat una multa que no consta. I, sobretot, que ningú no sàpiga quines de les operacions de l'última hora van sobreviure i quines no.

Com s'implementa és la part interessant, i val la pena entendre-la perquè explica moltes coses del comportament de PostgreSQL, inclòs el rendiment.

Recorda el gestor de buffers de la lliçó 01-04. La base de dades no llegeix ni escriu directament a disc: manté en memòria una memòria cau de pàgines (a PostgreSQL, shared_buffers). Quan un UPDATE modifica una fila, el que es modifica és la pàgina en memòria. Aquella pàgina queda marcada com a bruta (modificada i no escrita a disc) i s'escriurà "més tard".

Això és imprescindible per al rendiment: la memòria és diversos ordres de magnitud més ràpida que el disc, i agrupar escriptures evita milers d'operacions d'entrada/sortida.

Però crea un problema evident: si el COMMIT només modifica memòria, un tall de llum s'endú per davant tot el que s'ha confirmat.

La solució ingènua seria escriure a disc totes les pàgines modificades a cada COMMIT. És correcta, i és inacceptablement lenta: les pàgines modificades estan disperses pel fitxer de dades, i escriure-les obliga a salts de capçal (en disc mecànic) o a reescriure blocs sencers (en SSD). Una transacció que toca tres taules escriuria en tres llocs llunyans del disc.

Registre d'escriptura anticipada (Write-Ahead Log, WAL). Abans de modificar una pàgina de dades, el gestor escriu en un fitxer de registre seqüencial una anotació que descriu el canvi. El registre es sincronitza a disc abans de confirmar la transacció; les pàgines de dades poden esperar.

La regla, enunciada de forma canònica, és d'una simplicitat total:

Mai no s'escriu un canvi als fitxers de dades abans d'haver escrit a disc el registre que el descriu.

Per què és més ràpid? Perquè el registre és seqüencial. Totes les anotacions de totes les transaccions s'afegeixen al final del mateix fitxer, una darrere l'altra. Escriure 4 KB al final d'un fitxer seqüencial és l'operació més barata que existeix en qualsevol sistema d'emmagatzematge. Escriure 4 KB en vuit llocs diferents del disc, no.

Així queda la seqüència real d'un COMMIT:

Fixa't en l'ordre: l'aplicació rep el "COMMIT" tan bon punt el registre està fora de perill, no quan les dades estan escrites. Les dades poden trigar minuts a arribar al seu lloc definitiu. No importa: la informació per reconstruir-les ja és en un lloc segur.

A PostgreSQL el WAL viu al directori pg_wal/, en fitxers de 16 MB per omissió. Pots veure'l:

I consultar la posició actual del registre (el LSN, Log Sequence Number, que és l'adreça d'un byte dins del registre):

Aquest número avança amb cada escriptura. És el rellotge intern de la durabilitat, i tornarà a aparèixer a 06-04 quan parlem de recuperació a un instant concret.

Si el registre creixés indefinidament i calgués rellegir-lo sencer per recuperar-se, arrencar una base de dades de dos anys duraria dies. Per això existeixen els punts de control.

Punt de control (checkpoint). Operació periòdica en què el gestor escriu a disc totes les pàgines brutes que hi ha en memòria i anota al registre que, fins a aquell punt, els fitxers de dades estan al dia.

Conseqüència: per recuperar-se d'una caiguda només cal llegir el registre des de l'últim punt de control. Tot l'anterior ja és als fitxers de dades.

Els paràmetres que ho governen a PostgreSQL:

Hi ha un compromís clar: punts de control freqüents fan la recuperació ràpida però carreguen el disc durant el funcionament normal; punts de control espaiats són més suaus en marxa però allarguen l'arrencada després d'una caiguda.

Suposem que a les 12:41 marxa la llum al centre de processament de dades de Vallmar. L'últim punt de control va ser a les 12:37. Entre les 12:37 i les 12:41 hi va haver 214 transaccions: 209 confirmades i 5 obertes en el moment del tall.

En arrencar, PostgreSQL detecta que el tancament no va ser net i executa la recuperació, en dues fases:

Fase 1 — Refer (redo). Llegeix el registre des de l'últim punt de control i torna a aplicar tots els canvis anotats, tant els de transaccions confirmades com els de les que no ho estaven. Sona estrany, i és deliberat: és més ràpid tornar-ho a aplicar tot i netejar després que anar decidint cas per cas.

Fase 2 — Desfer (undo). Es descarten els efectes de les transaccions que no van arribar a confirmar-se. A PostgreSQL aquesta fase és gairebé gratuïta gràcies al MVCC: les 5 transaccions obertes simplement mai no consten com a confirmades al mapa d'estats de transacció, així que les seves versions de fila són invisibles per a tothom i es netejaran amb VACUUM. En un gestor amb segment de desfer, aquesta fase sí que implica feina real de reposició.

El resultat després de la recuperació és exacte: les 209 confirmades hi són; les 5 obertes no van deixar rastre. Ni una a mitges.

Al registre del servidor es veu així:

Aquest "invalid record length" no és un error: és el gestor trobant el final del registre vàlid, és a dir, l'instant exacte del tall. Llegeix-lo com "fins aquí va arribar la llum".

Aquest mecanisme —el registre, els punts de control, refer i desfer— és el que a 01-04 vam anomenar gestor de transaccions i recuperació, treballant colze a colze amb el gestor de buffers. Ara ja saps què fan exactament aquelles dues caixes del diagrama.

L'fsync() de l'apartat 10 —la crida al sistema que obliga el disc a confirmar que ha escrit de debò— és l'operació més cara de tot el cicle. En un SSD de servidor decent ronda els 0,1-1 ms; en un disc mecànic, entre 5 i 15 ms. Aquell temps és un sostre dur: una base de dades no pot confirmar més transaccions per segon de les que el seu disc pot sincronitzar.

PostgreSQL ofereix un paràmetre per relaxar-ho, i cal entendre exactament què es compra i què es paga:

El guany és real i de vegades espectacular: en càrregues de moltes transaccions petites, off pot multiplicar el nombre de transaccions per segon. Però la lletra petita cal dir-la sencera:

Amb synchronous_commit = off pots perdre transaccions que el gestor ja t'havia confirmat. La base de dades no queda corrupta —l'atomicitat i la consistència es mantenen: les transaccions perdudes es perden senceres—, però desapareixen operacions que l'usuari va veure com a completades.

És acceptable? Depèn de la taula:

Regla pràctica: si perdre els últims segons obliga a trucar algú per telèfon, no ho desactivis.

Nota addicional: existeix també el paràmetre fsync = off. Aquest sí que desactiva la protecció d'arrel i pot deixar la base corrupta i irrecuperable davant d'un tall. El seu únic ús legítim és una base de dades d'usar i llençar de proves que es pot regenerar amb un script. Mai en producció, sota cap circumstància i per cap motiu.

Aquest apartat explica un dels missatges d'error més freqüents —i pitjor entesos— de PostgreSQL.

Quan una instrucció falla dins d'una transacció explícita, PostgreSQL avorta la transacció sencera. No la instrucció: la transacció. A partir d'aquell moment qualsevol instrucció es rebutja fins que s'executi ROLLBACK (o ROLLBACK TO SAVEPOINT).

Dues coses notables:

1. El SELECT —que és inofensiu— també es rebutja. La transacció està enverinada.
2. El COMMIT final ha respost ROLLBACK. PostgreSQL no confirma una transacció avortada: la desfà. És un comportament segur i alhora traïdor, perquè una aplicació que només comprova "m'han respost al COMMIT?" creurà que tot va anar bé.

La postura de PostgreSQL és la més estricta, i és defensable: si una instrucció de la teva unitat de treball ha fallat, el més probable és que la teva unitat de treball ja no tingui sentit. Però obliga a escriure el codi d'una altra manera.

Si un error concret és esperable i vols sobreviure-hi, embolcalla'l en un punt de desament:

L'alta de la Núria s'ha conservat. La de l'Ivan no. La transacció ha arribat viva al COMMIT.

Una característica de PostgreSQL que sorprèn qui ve d'altres gestors: el DDL és transaccional. CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX poden anar dins d'una transacció i desfer-se amb ROLLBACK.

Ni la columna ni la taula existeixen. Això té una conseqüència operativa enorme: una migració d'esquema a PostgreSQL pot ser atòmica. Si el pas 7 de 9 falla, l'esquema torna intacte a l'estat inicial en lloc de quedar-se a mig migrar, que és la pitjor situació possible en una matinada de desplegament.

Les excepcions a PostgreSQL, que convé conèixer:

• CREATE DATABASE, DROP DATABASE, CREATE TABLESPACE no poden anar en una transacció.
• CREATE INDEX CONCURRENTLY —el que no bloqueja la taula— tampoc: és precisament la seva manera de no bloquejar. Tornarà a 06-03.
• VACUUM tampoc.

I un advertiment important: que el DDL sigui transaccional no vol dir que sigui gratis. Un ALTER TABLE pren un bloqueig fort sobre la taula, i mentre la transacció estigui oberta ningú més no la podrà fer servir. Els bloqueigs són el tema de 06-02.

Quatre regles que eviten la majoria dels problemes de producció. Les tres primeres es resumeixen en una idea: una transacció oberta és un recurs car que algú altre està esperant.

Una transacció oberta reté bloqueigs, impedeix que VACUUM netegi versions mortes i consumeix una ranura de connexió. Com més duri, més molesta.

Un DELETE de nou milions de files per lots:

Cada execució és la seva pròpia transacció (autoconfirmació), curta, interrompible i que no infla el registre amb nou milions d'anotacions de cop.

És l'error clàssic del sistema de taulell:

Aquell FOR UPDATE manté bloquejada la fila de l'exemplar durant quaranta minuts, i qualsevol altre taulell que intenti tocar-la es queda esperant. El patró correcte és llegir sense transacció, mostrar el diàleg, i obrir la transacció després que l'humà decideixi —comprovant llavors que res no hagi canviat—. És exactament el bloqueig optimista, que es veu a 06-02.

Com a xarxa de seguretat, PostgreSQL permet tallar els despistats:

Qualsevol sessió que es quedi més de 30 segons amb una transacció oberta sense fer res serà desconnectada. En producció és molt recomanable posar-hi un valor raonable.

Dos problemes de naturalesa diferent:

• De rendiment: la transacció dura el que duri la xarxa.
• De correcció, i aquest és el greu: la crida externa no es desfà amb ROLLBACK. Si el COMMIT falla després d'haver cobrat, has cobrat i no consta. La transacció de base de dades no pot desfer el món exterior.

El patró correcte separa les dues coses: una transacció registra la intenció (pagaments amb estat iniciat), es fa la crida externa fora de tota transacció, i una segona transacció registra el resultat. Amb una referència idempotent per poder reintentar sense cobrar dues vegades —per a això hi ha la columna pagaments.referencia.

Una transacció pot fallar per causes transitòries: un interbloqueig, una fallada de serialització, una desconnexió momentània. Aquests errors no signifiquen que el codi estigui malament; signifiquen que cal tornar-ho a intentar. Una aplicació seriosa embolcalla les seves transaccions en un reintent amb espera creixent i un nombre màxim d'intents. La mecànica concreta es veu a 06-02, on apareixen els errors que s'han de reintentar i els que no.

SQLite és plenament ACID, cosa que sorprèn qui el pren per "una base de dades de joguina". No ho és: és la base de dades més desplegada del món, i és transaccional de debò.

Les seves diferències vénen de la seva naturalesa incrustada (01-04):

Activar el mode WAL, que és el primer que es fa en qualsevol ús seriós de SQLite:

El concepte és el mateix que a PostgreSQL —escriure primer el registre—, aplicat a un fitxer local. La diferència decisiva continua sent la granularitat del bloqueig: PostgreSQL bloqueja files; SQLite bloqueja el fitxer sencer per escriure. Per a BiblioRed, amb quatre taulells escrivint alhora, SQLite seria una mala elecció; per a l'aplicació d'inventari que un bibliotecari porta en una tauleta i sincronitza al final del dia, seria perfecta.

Reprenent el que vam veure a 03-04:

L'atomicitat a nivell de document explica per què el modelatge documental de 03-03 empeny a agrupar en un document allò que ha de canviar junt. Si el préstec i l'estat de l'exemplar viuen al mateix document, l'operació és atòmica sense cap transacció.

Amb documents separats sí que cal una transacció explícita:

Funciona, i és correcte. Però a MongoDB una transacció multidocument té un cost notablement més gran que a PostgreSQL, i la comunitat la tracta com l'excepció, no com l'eina habitual. El criteri de 03-04 continua sent vàlid: si el teu domini necessita transaccions multidocument a tota hora, és un senyal fort que el model relacional encaixa millor amb el teu problema.

Creure que el ROLLBACK desfà el que s'ha confirmat. No existeix. Un cop respost el COMMIT, l'únic camí de tornada és una operació compensatòria o una restauració des de còpia (06-04). Escriu el codi sabent que COMMIT és un punt de no retorn.

Confiar en el COMMIT sense comprovar la resposta. Com vam veure a l'apartat 13, un COMMIT sobre una transacció avortada respon ROLLBACK sense llançar excepció en alguns clients. Comprova sempre el resultat; no ho suposis.

Deixar la transacció oberta esperant un humà o una xarxa. És l'origen del 80 % dels problemes de bloqueig en producció. Configura idle_in_transaction_session_timeout i no ho deixis al criteri de ningú.

Ficar tot el procés nocturn en una sola transacció. Nou hores de procés en una transacció oberta impedeixen netejar versions mortes, inflen la base i, si falla a la vuitena hora, es perd tot. Divideix per lots amb confirmació intermèdia.

Oblidar commit() al controlador. Amb psycopg i amb molts ORM, si no confirmes, la feina es descarta en tancar la connexió. Sense error, sense avís, sense fila.

Usar synchronous_commit = off en taules que representen diners o inventari. El guany de rendiment és real i la pèrdua potencial també. Decideix-ho taula per taula, no globalment, i deixa-ho escrit.

Tocar fsync = off alguna vegada en producció. No hi ha cap cas. Cap.

No posar un SAVEPOINT on hi ha un error esperable. Si la teva lògica té un "això pot fallar i no passa res", a PostgreSQL necessita un punt de desament. Sense ell, la fallada s'endú per davant tota la transacció.

Consell final: anomena les transaccions al codi. Un mètode registrarPrestec() que obre i tanca la transacció sencera, amb el BEGIN i el COMMIT visibles al mateix bloc de codi, es llegeix i s'audita. Un BEGIN en un lloc i un COMMIT tres capes més avall és una font inesgotable de transaccions oblidades.

A BiblioRed, retornar un exemplar implica: (a) posar data_devolucio a prestecs; (b) posar exemplars.estat = 'disponible'; (c) si hi ha retard, emetre una multa de 0,20 € per dia a multes. L'emissió de la multa és opcional: si falla, la devolució s'ha de registrar igualment.

Escriu la transacció completa per al préstec 88214 del soci 14 sobre l'exemplar 3081, la data_devolucio_prevista del qual era 2026-07-15 i que es retorna el 2026-08-02. Fes servir un punt de desament on correspongui i calcula l'import amb SQL, no a mà.

Donada la seqüència següent, indica quines files de socis existeixen al final i per què. Suposa que la sucursal 77 no existeix i que les altres sí.

La regidoria de Vallmar vol reduir el temps de resposta del taulell. Un tècnic proposa posar synchronous_commit = off al fitxer de configuració del servidor, per a tota la base de dades. Argumenta amb tres punts concrets per què aquella decisió, tal com està formulada, és inacceptable, i proposa una alternativa que conservi part del benefici.

Solució 1

Punts clau de la solució:

• L'INSERT ... SELECT amb la condició AND p.data_devolucio > p.data_devolucio_prevista fa que la multa s'emeti només si hi ha retard, sense necessitat de lògica a l'aplicació. Si no hi ha retard, la resposta és INSERT 0 0 i no passa res.
• L'import es calcula amb la resta de dates (18 dies × 0,20 € = 3,60 €), llegint del mateix préstec ja actualitzat dins de la mateixa transacció. És correcte perquè la transacció veu els seus propis canvis.
• El punt de desament permet que, si l'INSERT falla —per exemple, perquè un CHECK de multes rebutja un import superior al màxim de l'ordenança—, l'aplicació executi ROLLBACK TO SAVEPOINT abans_multa i confirmi igualment la devolució.
• L'ordre importa: primer prestecs, després exemplars, després multes. Mantenir sempre el mateix ordre d'accés a les taules prevé interbloqueigs (06-02).

Solució 2

Al final no existeix cap de les quatre files. Recorregut pas a pas:

El parany de l'exercici és doble. Primer: ROLLBACK TO s2 no avorta la transacció, la rescata —sense ell, tot el que ve després hauria fallat amb current transaction is aborted—. Segon: ROLLBACK TO s1 descarta B i D, que molts donen per confirmades perquè "ja havien passat". Un punt de desament desfà tot el posterior a la marca, incloses les operacions que van tenir èxit.

Solució 3

Punt 1 — L'abast és global quan el problema no ho és. Posar-ho al fitxer de configuració ho aplica a totes les transaccions, incloses les de pagaments i multes. La biblioteca acceptaria perdre cobraments confirmats a canvi que el taulell vagi més ràpid. No és un intercanvi que un servei públic pugui fer, i certament no el pot decidir un tècnic en solitari.

Punt 2 — No s'ha mesurat on és el problema. No hi ha cap dada que digui que el temps de resposta del taulell se'n va en l'fsync(). És igual de probable —més, de fet— que se'n vagi en una consulta sense índex (06-03), en el temps de xarxa o en la mateixa interfície. Canviar un paràmetre de durabilitat abans d'haver mesurat és actuar sobre una hipòtesi no verificada, i a sobre amb la garantia de seguretat com a moneda.

Punt 3 — El risc no està acotat ni comunicat. "Es poden perdre les últimes transaccions" és una afirmació que la regidoria ha de conèixer i acceptar per escrit, perquè afecta dades de ciutadans i cobraments. Una decisió d'aquest tipus no és tècnica: és de risc operatiu, i es documenta.

Alternativa raonable. Deixar synchronous_commit = on com a configuració global i desactivar-lo per transacció, només a les operacions on perdre uns segons és tolerable:

Amb SET LOCAL l'efecte mor en acabar la transacció, així que no pot escapar-se a una altra operació per accident. I abans d'això: mesurar amb EXPLAIN ANALYZE (06-03) on se'n va realment el temps del taulell, que gairebé mai no és on es creu.

Aquesta lliçó ha canviat l'objecte d'estudi. Fins al mòdul 5 miràvem l'estructura: quines taules, quines columnes, quines restriccions. A partir d'aquí mirem el comportament: què passa quan el sistema està en marxa i les coses surten malament.

La transacció és la unitat amb què es raona sobre aquell comportament. Hem vist que registrar un préstec a BiblioRed són tres instruccions però un sol fet, i que qui decideix on comença i acaba una unitat de treball no és el gestor, sinó qui escriu el codi. Hem vist el vocabulari complet —BEGIN, COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO, RELEASE—, el mode d'autoconfirmació que embolcalla cada instrucció solta en la seva pròpia transacció, i el cicle de cinc estats pel qual passa tota transacció, amb aquell instant crític de "parcialment confirmada" en què el gestor encara no ha promès res.

De les quatre propietats ACID n'hem desenvolupat tres. L'atomicitat, que esborra els estats intermedis i fa que un ROLLBACK a PostgreSQL sigui més barat que un COMMIT. La consistència, que és la lletra a mitges —el gestor protegeix les regles que li has declarat, i només aquestes, cosa que converteix cada CHECK i cada clau forana del mòdul 4 en part de la garantia transaccional—. I la durabilitat, que hem obert en canal: el registre d'escriptura anticipada que se sincronitza abans que les dades perquè escriure seqüencialment és barat i escriure dispers no ho és; el punt de control que acota quant registre cal rellegir; la recuperació en dues fases —refer tot des de l'últim punt de control, descartar després el que no s'ha confirmat— que retorna la base exactament a l'últim COMMIT respost; i el preu de tot plegat, aquell fsync() que posa un sostre dur al nombre de transaccions per segon i que synchronous_commit permet relaxar a canvi d'acceptar, per escrit, què s'està disposat a perdre.

També hem après a conviure amb el caràcter estricte de PostgreSQL: una instrucció fallida avorta la transacció sencera, i el punt de desament és l'única manera de sobreviure a un error esperable. A canvi, PostgreSQL regala una cosa que altres gestors no tenen: DDL transaccional, i amb ell migracions d'esquema que o s'apliquen senceres o no deixen rastre.

Queda una lletra sense desenvolupar, i és la més difícil de les quatre. L'aïllament l'hem enunciat —cada transacció es comporta com si estigués sola— i n'hem vist treure el cap l'efecte a la demostració de dos terminals de l'apartat 3, on la sessió B no veia res del que la sessió A estava fent. Però no hem dit què passa quan les dues sessions toquen la mateixa fila, ni què veu exactament cadascuna, ni què passa si totes dues decideixen alhora que l'exemplar EJ-3081 està disponible i totes dues el presten.

Això és la lliçó 06-02, Concurrència i Nivells d'Aïllament: els quatre fenòmens clàssics —actualització perduda, lectura bruta, lectura no repetible, lectura fantasma— provocats un a un amb dos terminals psql sobre les dades de BiblioRed; el biaix d'escriptura, que sorprèn fins i tot en nivells alts d'aïllament; els quatre nivells de l'estàndard SQL amb la taula de què permet cadascun i què fa realment PostgreSQL; els bloqueigs compartits i exclusius amb SELECT ... FOR UPDATE; el control multiversió que fa que a PostgreSQL els lectors mai no bloquegin els escriptors, i el VACUUM que paga aquella factura; els interbloqueigs, amb dues sessions que s'esperen mútuament per sempre fins que el gestor en mata una; i la solució final i completa al problema que ja ens està esperant a l'agenda del club de lectura de la sucursal Nord: dos socis inscrivint-se al mateix segon a l'última plaça lliure.