El mòdul 8 va acabar confessant una mentida: durant vuitanta i escaig lliçons hem suposat que som l'únic usuari de la base de dades. Aquest mòdul desmunta aquesta suposició, i comença per la peça que la fa manejable: la transacció, una unitat de treball que el motor tracta com a indivisible. O passa sencera, o no passa res. Aquí veuràs per què confirmar una comanda a BotigaVerda —quatre operacions sobre tres taules— és un desastre esperant a passar si no és atòmic; entendràs l'autocommit, la font número u de malentesos amb les transaccions; aprendràs BEGIN, COMMIT i ROLLBACK amb el seu cicle de vida complet; i coneixeràs el format de dues sessions amb què es demostra tota la resta del mòdul. Al final tindràs dues terminals psql obertes alhora i hauràs vist, per primera vegada al curs, que dues sessions no veuen el mateix al mateix temps.

1. Què és una transacció
2. El cas de BotigaVerda: confirmar una comanda són quatre operacions
3. Què queda si falla el tercer pas
4. Autocommit: cada sentència ja és una transacció
5. BEGIN, COMMIT i ROLLBACK: el cicle de vida
6. Com saber si ets dins d'una transacció
7. El format de dues sessions
8. Tancar la sessió sense confirmar, i què passa si el servidor cau
9. L'estat avortat
10. Transaccions de només lectura
11. Durada: transaccions curtes i el problema d'idle in transaction
12. Errors habituals i consells
13. Exercicis
14. Conclusió

Una transacció és un conjunt de sentències SQL que el motor executa com una sola operació indivisible: o s'apliquen totes o no se n'aplica cap.

L'exemple de manual és la transferència bancària. Moure 100 € d'un compte a un altre són dues operacions:

Si el sistema cau entre les dues, els 100 € han deixat d'existir. I no hi ha cap consulta que ho pugui detectar després: les dues files són individualment vàlides; només la relació entre totes dues està trencada, i aquesta relació no viu en cap columna.

La transacció resol exactament això: converteix les dues sentències en una. El subllenguatge que la controla és el TCL (Transaction Control Language), el quart dels que vam anomenar a 01-01 i l'únic que encara no havies fet servir a fons.

Oblidem els bancs: a BotigaVerda el cas és més ric i el tens a la base de dades. Un client confirma el seu carretó i el sistema ha de fer quatre coses sobre tres taules:

Pau Llorens Vidal (client 6) compra per web dos olis d'oliva, un te matcha i —sense saber que està esgotat— un pack d'espelmes de cera de soja. Els quatre passos, tal com els executa l'aplicació:

Seixanta euros amb setanta-cinc de producte més 4,95 € de ports: 65,70 €. Ara el pas que falla:

El producte 13 té stock = 0 i el CHECK (stock >= 0) de 05-01 impedeix baixar-lo a −1. El pas 3 ha fallat a mitges. I el pas 4 —marcar la comanda com a pagada— no arriba a executar-se mai.

Aquesta és la pregunta que cal mirar de cara. Sense transacció, cada sentència es va confirmar sola i això és el que hi ha ara a la base de dades:

Tres unitats d'inventari han desaparegut del sistema sense que ningú les hagi comprat. Multiplica-ho per cent comandes al dia i en un mes l'inventari de l'ERP no s'assembla al del magatzem. I no hi ha cap error registrat enlloc: l'aplicació va tornar un missatge al client, el client va tancar la pestanya, i les files s'hi van quedar. El mateix, dins d'una transacció:

Com si no hagués passat mai. 20 comandes, 47 línies, els estocs intactes. Aquesta és tota la idea.

Aquí hi ha el malentès número u, i convé dir-ho sense embuts:

En SQL no existeix "estar fora d'una transacció". Tota sentència s'executa dins d'una. Si no n'obres una explícitament, el motor n'obre una d'implícita que dura exactament el que dura aquesta sentència i es confirma sola en acabar. Això és l'autocommit.

Dues conseqüències que cal interioritzar:

1. Una sola sentència sempre és atòmica. L'UPDATE productes SET preu = preu * 1.05; de 05-03 afecta 20 files: o canvien les 20, o no en canvia cap. Per això INSERT ... ON CONFLICT (05-05) és segur i SELECT + INSERT no ho és: una sentència és atòmica i dues no.
2. El ROLLBACK no existeix per al que ja s'ha confirmat. Quan veus UPDATE 20 en autocommit, ja és al disc. No hi ha marxa enrere.

I el problema és que l'autocommit no es comporta igual a tot arreu:

Nota de dialecte: Oracle és el cas que més sorprèn: allà un UPDATE sense COMMIT posterior no ha passat per a ningú més que per a tu, i si tanques la sessió es perd. I a l'extrem oposat, psycopg i SQLAlchemy fan el contrari del que la majoria espera: la teva aplicació Python ja és dins d'una transacció oberta des de la primera consulta, encara que tu no hagis escrit BEGIN enlloc. Hi tornarem a 09-03, perquè explica la meitat dels bloquejos misteriosos en producció.

Tres paraules i ja ho saps tot:

START TRANSACTION és la forma de l'estàndard SQL i funciona a PostgreSQL, MySQL i SQL Server; BEGIN és la més curta i la que fa servir tothom a PostgreSQL. Compte: a MySQL, BEGIN és a més l'arrencada d'un bloc de codi en procediments, així que allà convé escriure START TRANSACTION perquè no hi hagi ambigüitat.

El cicle de vida complet, amb l'estat avortat que veurem a l'apartat 9:

Fixa't en la transició més perillosa del diagrama: un COMMIT sobre una transacció avortada no confirma res, fa un ROLLBACK. PostgreSQL respon ROLLBACK en lloc de COMMIT, i si el teu script no mira aquesta resposta, creurà que va desar les dades.

És la pregunta pràctica més freqüent, i psql te la respon al mateix indicador:

El caràcter final és > per a un usuari normal i # per a un superusuari, així que un administrador dins d'una transacció veu botigaverda=*#. El que importa sempre és l'asterisc: apareix just després del BEGIN i desapareix amb el COMMIT o el ROLLBACK. Des de SQL:

Retorna NULL mentre la transacció no hagi escrit res, perquè PostgreSQL no gasta un identificador de transacció en qui només llegeix; així que facis un INSERT o un UPDATE, apareix un número. La seva germana pg_current_xact_id() (abans txid_current(), que continua funcionant) força l'assignació, així que retorna sempre un número — i per això no serveix per diagnosticar: en preguntar, canvia la resposta.

I dos comodins de psql: \echo :ROW_COUNT imprimeix les files afectades per l'última sentència —l'UPDATE N de 05-03, però utilitzable dins d'un script— i \set ON_ERROR_STOP on fa que psql avorti el fitxer al primer error en lloc de continuar llançant sentències contra una transacció ja morta. És obligatori en qualsevol script de migració.

Tot el que queda del mòdul tracta del que passa quan dues persones treballen alhora, i això no es veu en una sortida de psql normal. A partir d'aquí ho mostrarem sempre així:

Com reproduir-ho tu. Obre dues terminals i a cadascuna llança psql -h localhost -U curs_sql -d botigaverda. Anomenarem Sessió A la primera i Sessió B la segona. Executa les sentències en l'ordre dels instants t1, t2, t3… alternant de terminal. Tots els exemples del mòdul estan pensats per fer-se així, i no n'entendràs cap de debò fins que els teclegis.

La primera demostració: què veu cada sessió abans i després del COMMIT.

Llegeix-ho a poc a poc, perquè en aquestes set línies hi ha el mòdul sencer:

• A t4, A veu 39. És el seu propi canvi: tota transacció veu sempre el que ella mateixa ha fet.
• A t5, B veu 40. El canvi d'A existeix, està escrit, però no està confirmat, i per a B és com si no existís. B no es bloqueja, no espera, no rep cap avís: simplement llegeix el valor bo anterior.
• A t7, després del COMMIT, B veu 39. El canvi s'ha fet públic de cop, i per a B va passar sencer en l'instant del COMMIT, no repartit entre t3 i t6.

Això que acabes de veure és l'aïllament, la tercera lletra d'ACID, i l'estudiarem a fons a 09-02 i 09-04. I el mecanisme que ho fa possible sense que B hagi d'esperar —dos valors de la mateixa fila coexistint— és MVCC, la resposta a la pregunta que va deixar oberta 08-05 sobre el bloat.

Quan l'exemple requereixi veure el SQL complet en lloc de resumit, farem servir dos blocs etiquetats amb l'instant:

I per als interbloquejos de 09-05, diagrames mermaid de seqüència. Sigui quin sigui el format, la regla no canvia: sempre s'indica què veu cada sessió a cada instant i quina es queda esperant.

Els dos finals imprevistos tenen la mateixa resposta, i és tranquil·litzadora:

La regla mental: el que s'ha confirmat sobreviu a tot; el que no s'ha confirmat no sobreviu a res. No hi ha estat intermedi, ni manera que una transacció quedi "mig aplicada" després d'una caiguda.

Aquest missatge el veuràs, segur, i probablement avui mateix:

Passa així:

Fixa't en l'indicador: va passar de =*> a =!> tan bon punt hi va haver un error. A partir d'aquí PostgreSQL rebutja qualsevol sentència, fins i tot un SELECT COUNT(*) inofensiu, amb el codi 25P02.

Per què és així, i per què és el correcte. La transacció va prometre atomicitat: o tot o res. Tan bon punt una sentència falla, "tot" ja és impossible, així que l'única promesa que el motor pot continuar complint és "res". Deixar-te continuar seria permetre que confirmessis un resultat parcial creient que està complet — exactament el desastre de l'apartat 3, però amb el segell de qualitat d'una transacció al damunt.

Com sortir-ne: hi ha dues portes i totes dues acaben la transacció. ROLLBACK; ho desfà tot, i és la sortida honesta. COMMIT; respon ROLLBACK i ho desfà tot igualment. N'existeix una tercera que no tanca la transacció i salva la feina ja feta: tornar a un SAVEPOINT anterior a l'error, una de les raons de ser dels savepoints (09-03).

Nota de dialecte — i és una divergència enorme. MySQL/InnoDB no avorta la transacció sencera. Si una sentència falla, es desfà només aquesta sentència i la transacció continua viva i acceptant ordres; pots fer COMMIT i confirmaràs tot el que hi havia abans de l'error. SQL Server queda al mig: depèn de la gravetat de l'error i de SET XACT_ABORT ON (que el fa comportar-se com PostgreSQL, i és el recomanat). Oracle també desfà només la sentència fallida. Conseqüència pràctica: un script provat a MySQL que "funciona" pot estar confirmant resultats parcials; el mateix script a PostgreSQL fallarà sorollosament. La versió sorollosa és la bona.

Es declaren així:

I si intentes escriure-hi dins: ERROR: cannot execute UPDATE in a read-only transaction. Quatre raons per declarar-les:

1. És una xarxa de seguretat contra tu mateix. Un informe mensual no hauria de poder modificar res; amb READ ONLY, un UPDATE enganxat per error és un error i no un incident.
2. No consumeix un identificador de transacció, cosa que redueix la pressió sobre MVCC i sobre la congelació de transaccions (09-02).
3. És obligatòria en una rèplica de només lectura. Si el teu informe apunta a un secundari, val més que l'escriptura falli al teu portàtil.
4. Habilita DEFERRABLE, que permet executar un informe llarg en mode SERIALIZABLE sense risc que avorti per conflicte (09-03).

Aquesta és l'única regla operativa que cal memoritzar d'aquesta lliçó:

Una transacció s'ha d'obrir com més tard millor i tancar-se com més aviat millor. Mai, mai de la vida, no s'espera dins d'una transacció oberta: ni entrada de l'usuari, ni una crida HTTP, ni una lectura de fitxer, ni un sleep.

Una transacció oberta i ociosa (l'estat idle in transaction) fa tres danys simultanis:

El cas real és sempre el mateix: un formulari que obre transacció, mostra una pantalla de confirmació i espera. L'usuari se'n va a dinar. La botiga s'atura. Com es detecta:

Una hora i quaranta-dos minuts amb la fila del matcha bloquejada. La defensa automàtica és un paràmetre que tot servidor de producció hauria de tenir posat —SET idle_in_transaction_session_timeout = '5min';— fixable per sessió, per usuari, per base de dades o a postgresql.conf. Al costat de statement_timeout i lock_timeout forma el kit de supervivència que detalla 09-05.

• Creure que "no estic fent servir transaccions". Sí que en fas servir: cada sentència solta n'és una. La pregunta no és si hi ha transacció, sinó on comença i on acaba.
• Suposar que l'autocommit es comporta igual a tot arreu. A Oracle està desactivat; a psycopg i SQLAlchemy la teva aplicació ja és dins d'una transacció oberta sense que hagis escrit BEGIN.
• Fer BEGIN i oblidar de tancar. L'error operatiu més car del mòdul: bloqueges files, impedeixes el VACUUM i ocupes una connexió. Mira l'asterisc de l'indicador abans d'aixecar-te.
• Ignorar l'estat avortat, o no comprovar la resposta del COMMIT. Després d'un error tot falla amb 25P02 fins al ROLLBACK; i sobre una transacció avortada, COMMIT retorna ROLLBACK i no desa res. Si el teu script no ho mira, creurà que va desar.
• Executar un fitxer .sql sense \set ON_ERROR_STOP on. psql continuarà llançant cent sentències contra una transacció morta i l'error real quedarà sepultat entre cent missatges de 25P02.
• Descomptar estoc línia a línia sense transacció. És l'apartat 3: inventari trencat en silenci i sense traça.
• Esperar dins d'una transacció oberta. Entrada d'usuari, crida a la passarel·la de pagament, lectura d'un fitxer gran. Prepara les dades a fora, obre, escriu, tanca.
• Consell: adopta BEGIN … verificar … COMMIT/ROLLBACK com a reflex en qualsevol UPDATE o DELETE manual, i BEGIN TRANSACTION READ ONLY per als teus informes: costa dues paraules i fa impossible l'accident.
• Consell: tingues sempre dues terminals psql obertes mentre estudies aquest mòdul. És l'única manera de veure la concurrència.

Treballa sobre la base acabada de recarregar (botigaverda.sql) i amb dues terminals psql obertes.

Reprodueix el desastre de l'apartat 3 i després la seva versió correcta.

1. Sense transacció, executa els quatre passos de confirmació de la comanda d'en Pau amb les tres línies (productes 1, 15 i 13). Anota què falla.
2. Escriu una consulta que demostri la incoherència: comandes, línies i estocs dels tres productes implicats.
3. Recarrega l'script i repeteix-ho tot dins de BEGIN … ROLLBACK. Comprova amb la mateixa consulta que no en queda ni rastre.
4. Què hauria passat si el pas 3 s'hagués escrit com un sol UPDATE ... FROM linies_comanda (05-03) en lloc de tres sentències? Continuaria fent falta la transacció?

Anomenant INSERIR_COMANDA la sentència INSERT INTO comandes (client_id, empleat_id, data_comanda, estat, metode_pagament, despeses_enviament) VALUES (6, NULL, DATE '2026-03-05', 'pendent', 'targeta', 4.95) RETURNING id;, prediu què veurà cada sessió abans d'executar res, i després comprova-ho amb dues terminals:

La pregunta de t6 és la interessant: quin identificador rep la comanda de B, sabent que la d'A es va desfer?

En una sessió, provoca deliberadament l'estat avortat i surt-ne de les dues maneres possibles.

1. BEGIN;, un UPDATE vàlid sobre productes i després un INSERT que violi una clau forana (per exemple, una comanda amb client_id = 999).
2. Intenta executar SELECT 1;. Copia el missatge exacte.
3. Surt-ne amb COMMIT; i anota què respon el servidor. Comprova després si l'UPDATE vàlid es va arribar a aplicar.
4. Repeteix-ho tot sortint-ne amb ROLLBACK; i compara.
5. Explica en dues frases per què MySQL es comportaria de manera diferent i quin dels dos comportaments prefereixes per a un script de facturació.

Solució 1

1 i 2. El tercer UPDATE falla amb productes_stock_check, perquè el producte 13 està a 0. La consulta que revela l'estropici:

Una comanda que ningú no cobrarà, tres línies òrfenes de propòsit i tres unitats d'inventari evaporades.

3. Amb BEGIN al principi i ROLLBACK al final, la mateixa consulta retorna 20, 47, (null), 120, 40, 0. Estat inicial exacte.

4. Amb un sol UPDATE ... FROM, el pas 3 sí que seria atòmic per si mateix: en violar el CHECK en una de les tres files, no se n'aplicaria cap, i els estocs quedarien a 120 i 40. Però la transacció continua fent falta, i per dos motius: la comanda 21 i les seves tres línies ja estan inserides i confirmades pels passos 1 i 2, així que la incoherència persisteix; i el pas 4 tampoc no s'executa. L'atomicitat d'una sentència no dóna atomicitat al procés: la unitat de treball és la comanda, no l'UPDATE.

Solució 2

A t3, B veu 20: l'INSERT d'A no està confirmat i per a B no existeix. A t5, B continua veient 20, perquè A va fer ROLLBACK i la comanda no va existir mai per a ningú. I a t6, l'id que rep B és 22, no 21. Aquest 22 és la part important. Les seqüències no es desfan amb un ROLLBACK, com ja van avisar 05-02 i 05-05: A va consumir el valor 21 en inserir i aquest valor es va perdre en desfer. És deliberat — si nextval respectés les transaccions, dues sessions haurien d'esperar-se l'una a l'altra per obtenir un identificador, i això destruiria el rendiment de qualsevol sistema amb insercions concurrents. El preu és que els id tenen forats, i la conseqüència pràctica (per què una PK no s'ha de fer servir com a número de factura) es desenvolupa a 09-05.

Solució 3

2. El missatge, literal: ERROR: current transaction is aborted, commands ignored until end of transaction block.

3. El servidor respon a COMMIT; amb ROLLBACK, i l'UPDATE vàlid no s'ha aplicat: la transacció sencera es va desfer. Aquesta resposta discordant —demanes COMMIT i et contesten ROLLBACK— és el senyal que estàs confirmant una transacció morta.

4. Amb ROLLBACK; el resultat és idèntic, però honest: vas demanar desfer i va desfer. La diferència no és a les dades, sinó que en el primer cas un script que no llegeixi la resposta creurà que va desar.

5. A MySQL/InnoDB, l'INSERT fallit s'hauria desfet només a si mateix i la transacció hauria continuat viva; el COMMIT hauria confirmat l'UPDATE de productes. Per a un script de facturació és preferible el comportament de PostgreSQL: una factura a mitges és pitjor que cap factura, i l'error sorollós obliga a mirar. El de MySQL és més còmode en càrregues massives on es toleren files rebutjades, però exigeix comprovar el resultat de cada sentència a mà.

Ja tens la unitat de treball que faltava:

• Una transacció és un conjunt de sentències que s'apliquen totes o cap. Es controla amb el TCL: BEGIN, COMMIT i ROLLBACK.
• El cas de BotigaVerda: confirmar una comanda són quatre operacions sobre tres taules, i si la tercera falla a mitges queden una comanda sense cobrar, tres línies òrfenes i tres unitats d'inventari evaporades — sense cap error registrat.
• L'autocommit no és l'absència de transaccions: és una transacció per sentència. Per això una sentència sempre és atòmica i dues mai no ho són. I per això importa saber que Oracle no l'activa, i que psycopg i SQLAlchemy fan just el contrari del que gairebé tothom suposa.
• El cicle de vida: activa → confirmada / desfeta, amb el desviament a l'estat avortat tan bon punt una sentència falla. Allà PostgreSQL rebutja tot amb 25P02 i un COMMIT respon ROLLBACK; MySQL, en canvi, només desfà la sentència fallida. I per saber on ets: l'asterisc de l'indicador (botigaverda=*>), l'admiració si està avortada (=!>) i pg_current_xact_id_if_assigned().
• El format de dues sessions que farem servir tot el mòdul, i la seva primera lliçó: abans del COMMIT, B veu el valor vell sense esperar ni assabentar-se'n; després, el veu sencer i de cop.
• Tancar la sessió o caure el servidor equivalen a un ROLLBACK; el que s'ha confirmat sobreviu sempre. Transaccions de només lectura per als informes, i la regla d'or: curtes, i mai esperant ningú. Una transacció idle in transaction bloqueja files, impedeix el VACUUM —el bloat de 08-05— i ocupa una connexió.

Has vist què fa una transacció. Falta què garanteix exactament, i aquesta resposta té quatre lletres. A Propietats ACID desmuntarem una a una l'atomicitat (i el mecanisme que permet desfer), la consistència (on acaba la responsabilitat de la base de dades i comença la teva, amb l'estoc que no pot quedar negatiu com a cas d'estudi), l'aïllament (per què B veia 40 mentre A veia 39) i la durabilitat (el WAL, fsync i per què les teves dades sobreviuen a un tall de corrent). I per fi arribarà MVCC: com PostgreSQL desa diverses versions de cada fila, com es veuen amb SELECT xmin, xmax, *, i per què d'aquí surten les files mortes, el bloat i la necessitat de VACUUM que 08-05 va deixar pendent.