Amb BEGIN, COMMIT i ROLLBACK ja pots treballar, però el TCL té més peces, i algunes resolen problemes que fins ara no sabies anomenar. Què fas si estàs carregant cent comandes de cop i la número quaranta-set falla: llences les quaranta-sis bones? Com surts de l'estat avortat de 09-01 sense perdre la feina feta? I per què a PostgreSQL pots fer ROLLBACK d'un CREATE TABLE i a MySQL no? Aquesta lliçó és el repertori complet: totes les opcions de BEGIN, els SAVEPOINT i les seves tres operacions, com fixar el nivell per omissió de la sessió, què fa realment l'autocommit de psycopg, JDBC o SQLAlchemy —perquè a la teva aplicació el BEGIN probablement el posa el framework sense que l'hagis escrit—, el DDL transaccional, les transaccions preparades i els patrons d'ús des de codi. I tot desemboca en l'exemple integrador del mòdul: confirmar una comanda de BotigaVerda de principi a fi, amb el seu savepoint i el seu COMMIT.

1. El repertori del TCL
2. BEGIN i les seves opcions
3. COMMIT i ROLLBACK
4. SAVEPOINT: desfer només una part
5. Un savepoint per sortir de l'estat avortat
6. El que costen els savepoints
7. SET TRANSACTION i el nivell per omissió de la sessió
8. Implícites enfront d'explícites: l'autocommit dels controladors
9. DDL dins de transaccions
10. Transaccions preparades i two-phase commit
11. Patrons d'ús des d'una aplicació
12. L'exemple integrador: confirmar una comanda
13. Errors habituals i consells
14. Exercicis
15. Conclusió

Les faràs servir totes menys l'última, que existeix per a un cas molt concret (apartat 10).

Les opcions es poden combinar en qualsevol ordre i separades per comes o per espais:

El cas típic de l'últim és l'informe mensual de BotigaVerda executat sobre la base en producció:

Els 118,25 € de ports canònics, repartits per estat, llegits d'una foto perfectament coherent del sistema i sense cap possibilitat que l'informe avorti al cap de deu minuts ni de bloquejar ningú mentre corre. Tres paraules extra al BEGIN.

Poca cosa a afegir a 09-01, tret dels sinònims —COMMIT = END = COMMIT WORK; ROLLBACK = ABORT = ROLLBACK WORK— i un detall de comportament: COMMIT o ROLLBACK fora d'una transacció no són un error, només un WARNING: there is no transaction in progress.

Sembla inofensiu, i és just el contrari: és el símptoma clàssic d'un script el BEGIN del qual no es va executar —perquè era dins d'un if que no va entrar, o perquè una eina ja havia fet COMMIT pel seu compte—. Tot el que creies transaccional es va executar en autocommit. Si veus aquest WARNING en un registre de producció, investiga'l.

Un SAVEPOINT és una marca dins de la transacció a la qual pots tornar sense tancar-la. SAVEPOINT s1; crea la marca (si ja n'existia una amb aquest nom, la nova l'oculta); ROLLBACK TO SAVEPOINT s1; desfà tot el fet després d's1 i deixa la transacció oberta, amb s1 encara disponible; i RELEASE SAVEPOINT s1; elimina la marca sense desfer res, fonent el fet després d's1 amb la transacció exterior.

BotigaVerda importa cada nit les comandes del marketplace. Avui en vénen tres, i la segona porta un article que no existeix al catàleg. Objectiu: carregar les dues bones i descartar només la dolenta, tot en una única transacció.

El RELEASE de la primera confirma una decisió: "aquesta comanda està bé, ja no necessito poder descartar-la per separat". No la fa permanent —això només ho fa el COMMIT final—, però la fon amb la transacció exterior. La segona, en canvi, peta:

La transacció està avortada (botigaverda=!>). Sense savepoints, aquí s'hauria acabat la nit i les 46 comandes següents s'haurien perdut. Amb savepoint, es rescata — i l'indicador torna a botigaverda=*>: la transacció torna a ser viva, la comanda 22 ha desaparegut i la 21 continua allà.

L'estat després de cada pas, que és el que cal mirar:

Fixa't en el forat: no hi ha comanda 22, ni línia 49. És exactament el que va anticipar l'exercici 2 de 09-01: les seqüències no es desfan, ni amb un ROLLBACK ni amb un ROLLBACK TO SAVEPOINT. El valor 22 el va consumir la capçalera descartada, i el 49 el va consumir l'INSERT de la línia que va violar la clau forana — perquè PostgreSQL construeix la fila completa, avaluant el nextval de l'id, abans de comprovar la restricció. Els forats als identificadors són normals i no signifiquen que faltin dades.

Acabes de veure-ho de passada, però val la pena dir-ho en veu alta, perquè resol el missatge més frustrant de 09-01:

ROLLBACK TO SAVEPOINT és l'única manera de recuperar-se de l'estat avortat sense perdre la transacció. Desfà fins a la marca, neteja l'estat d'error i retorna la transacció a activa. Sobre una transacció avortada, ROLLBACK i COMMIT la desfan sencera i la tanquen; ROLLBACK TO SAVEPOINT s la reanima.

I d'aquí surt un patró que veuràs molt en codi generat: embolicar cada operació arriscada al seu propi savepoint, perquè una fallada previsible —una clau duplicada, una FK que no hi és— no llenci la feina sencera. És literalment el que fan els try/except imbricats d'un ORM: cada bloc atomic() de Django o begin_nested() de SQLAlchemy dins d'un altre no és una transacció nova, és un SAVEPOINT.

No són gratis. Cadascun obre una subtransacció, i PostgreSQL només en cau 64 per transacció: en passar d'aquí, resoldre la visibilitat de cada fila obliga a anar a disc (pg_subtrans) i el rendiment de tot el servidor pot caure bruscament. A més, cada intent fallit deixa les seves files mortes per al VACUUM (09-02) i crema valors de seqüència, com acabes de veure amb el 22 i el 49.

La regla pràctica: un savepoint per unitat de negoci que vulguis poder descartar —una comanda dins d'un lot— i no un per sentència. Si en necessites milers, el que necessites en realitat és dividir la càrrega en diverses transaccions i portar un registre del que s'ha processat (la idempotència de 05-03).

Dues sentències semblants amb abasts molt diferents:

La forma (a) equival a posar les opcions al BEGIN, amb la mateixa limitació: un cop la transacció ha llegit o escrit alguna cosa, ja no es pot canviar el nivell. La (b) afecta la sessió sencera i sobreviu als COMMIT, així que és la manera de configurar una connexió dedicada a informes. Per saber on ets, SHOW transaction_isolation; (el de la transacció actual) i SHOW default_transaction_isolation; (el de les següents), que en una instal·lació acabada de fer retornen read committed — el valor per omissió de PostgreSQL i el punt de partida de 09-04. Els mateixos ajustos es fixen per usuari o per base de dades amb ALTER ROLE ... SET i ALTER DATABASE ... SET, o per a tot el servidor a postgresql.conf.

Això és el que més problemes causa a la feina real, i el motiu és simple: a la teva aplicació, el BEGIN gairebé mai no l'escrius tu.

Tres conseqüències que cal gravar-se. Amb psycopg o SQLAlchemy, el teu procés pot portar hores amb una transacció oberta sense que ningú ho hagi demanat: un script que obre connexió, llegeix una taula i es posa a processar en Python vint minuts està idle in transaction vint minuts, amb tot el que això implica (09-01, 09-02); la solució és autocommit = True per a les lectures, o tancar la transacció abans de calcular. Amb JDBC passa el contrari: cada executeUpdate() es confirma sol, i una operació de quatre passos no és atòmica tret que algú cridi setAutoCommit(false). I els blocs imbricats no són transaccions imbricades: són savepoints — SQL no té transaccions imbricades de debò, i per això un atomic() intern pot fallar i desfer-se sense llençar l'extern.

Consell operatiu: abans de depurar un bloqueig o una dada que "no es desa", esbrina on posa el teu framework el BEGIN i on el COMMIT. Es comprova en un minut activant log_statement = 'all' i llegint el registre.

Aquí PostgreSQL guanya per golejada, i és la promesa que va deixar oberta 05-06.

A PostgreSQL, el DDL és transaccional. CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, ADD CONSTRAINT… tot es pot desfer:

La taula no va existir mai. Aquesta és la propietat que fa segures les migracions d'esquema a PostgreSQL: una migració de quinze passos que falla al dotzè es desfà sencera i deixa la base exactament com era. És el fonament de les eines de migració versionada de 05-06.

Les excepcions —el que no es pot executar dins d'una transacció— són poques i totes pel mateix motiu, que necessiten actuar fora del control transaccional: CREATE DATABASE · DROP DATABASE · CREATE TABLESPACE · ALTER SYSTEM · VACUUM · CREATE INDEX CONCURRENTLY · REINDEX CONCURRENTLY · CLUSTER sobre diverses taules.

Nota de dialecte — la divergència més cara del mòdul. MySQL no té DDL transaccional. Qualsevol sentència d'esquema provoca un commit implícit: confirma silenciosament tot el que hi ha pendent i no es pot desfer. La llista convé conèixer-la: CREATE/ALTER/DROP TABLE · TRUNCATE TABLE · RENAME TABLE · CREATE/DROP INDEX · CREATE/ALTER/DROP DATABASE · CREATE/ALTER/DROP EVENT, FUNCTION, PROCEDURE, TRIGGER, VIEW · CREATE/ALTER/DROP USER · GRANT / REVOKE · LOCK TABLES / UNLOCK TABLES · START TRANSACTION · SET autocommit = 1.

Conseqüències pràctiques: una migració que falla a mitges deixa l'esquema a mitges i cal escriure el desfer a mà; i un ALTER TABLE colat dins d'un bloc transaccional confirma sense avisar tot el que aquell bloc portés escrit. MySQL 8 va introduir el DDL atòmic, que garanteix que una sentència d'esquema no queda a mitges després d'una caiguda — però això no és el mateix que poder desfer-la amb un ROLLBACK. Oracle es comporta igual; SQL Server i SQLite sí que tenen DDL transaccional, com PostgreSQL.

Quan una operació ha de ser atòmica entre dues bases de dades diferents, un COMMIT normal no serveix: cada servidor confirmaria pel seu compte i un podria fallar. La resposta clàssica és el commit en dues fases:

La transacció preparada sobreviu a la desconnexió i al reinici del servidor: queda al disc esperant la seva resolució, i es consulta a pg_prepared_xacts.

⚠️ Gairebé mai no ho hauries de fer servir a mà, i a PostgreSQL ve desactivat (max_prepared_transactions = 0). El motiu és que una transacció preparada i mai resolta és el pitjor que li pot passar a una base de dades: manté els seus bloquejos per sempre, impedeix que VACUUM netegi res i ignora tots els temps d'espera, perquè no està associada a cap sessió que es pugui matar. Només té sentit amb un gestor de transaccions distribuïdes que garanteixi la resolució (JTA, XA, postgres_fdw). Si creus que el necessites, gairebé sempre la resposta correcta és una altra arquitectura: un patró outbox, una cua de missatges o una saga amb compensacions.

El bloc canònic, en pseudocodi vàlid per a qualsevol llenguatge:

Quatre regles que hi van:

1. El rollback() no és opcional. Sense ell, la connexió torna al pool en estat avortat i la petició següent que la rebi fallarà amb 25P02 sense haver fet res dolent. És un dels errors més desconcertants de depurar.
2. Res d'esperes ni de serveis externs a dins. Ni la passarel·la de pagament, ni el correu de confirmació, ni la pujada d'un fitxer. Ja ho va justificar 09-02: ACID acaba a la vora de la base de dades. El patró correcte és cobrar fora i registrar el resultat dins, o escriure la intenció en una taula outbox que consumeix un altre procés.
3. Un reintent ha de ser idempotent. Si la transacció avorta per conflicte de serialització (09-04) o per interbloqueig (09-05), es reintenta sencera — i això només és segur amb la propietat de 05-03: valors absoluts i WHERE acotats, o una clau única de negoci (l'identificador del carretó) que faci impossible inserir dues vegades la mateixa comanda.
4. Prepara les dades fora i obre la transacció com més tard millor. Validar el carretó i calcular imports no necessita transacció oberta. El que va dins és només l'escriptura.

Nota d'abast: en un sistema real aquesta lògica sol viure en un procediment emmagatzemat invocat amb una sola crida, perquè la transacció no depengui de la latència de xarxa. Els procediments són 10-04 i els triggers que els acompanyen, 10-05. Aquí continuem amb SQL pla.

Tot junt. Pau Llorens (client 6) compra per web dos olis d'oliva i un te matcha, paga amb targeta i li corresponen 4,95 € de ports.

L'AND stock >= N és el patró de 09-02: si no hi hagués existències, l'UPDATE retornaria UPDATE 0 sense avortar la transacció, i l'aplicació podria decidir què fer —ROLLBACK TO SAVEPOINT abans_de_stock i deixar la comanda en pendent com a reserva, o ROLLBACK sencer i avisar el client— en lloc d'estavellar-se contra el CHECK.

I la comprovació final, que és la factura del client:

Quatre operacions sobre tres taules, una sola unitat de treball. El desastre de l'apartat 3 de 09-01 —comanda sense cobrar, línies òrfenes, inventari evaporat— és ara impossible: o hi ha comanda pagada amb el seu estoc descomptat, o no hi ha absolutament res.

L'única cosa que aquesta comanda encara no resol és què passa si un altre client compra l'últim matcha en el mateix instant. Els dos UPDATE del pas 3 podrien llegir el mateix estoc i descomptar cadascun pel seu compte. Això és l'actualització perduda, i és l'assumpte de les dues lliçons següents.

• Canviar el nivell d'aïllament a mitja transacció. SET TRANSACTION ISOLATION LEVEL ha d'anar abans de la primera consulta; després, PostgreSQL el rebutja.
• Confondre RELEASE SAVEPOINT amb ROLLBACK TO SAVEPOINT. RELEASE no desfà res: només descarta la marca. Qui vulgui anul·lar la feina necessita ROLLBACK TO.
• Posar un savepoint per sentència. En passar de 64 subtransaccions, la resolució de visibilitat se'n va a disc i el rendiment del servidor sencer es pot esfondrar.
• Ignorar el WARNING: there is no transaction in progress. Significa que el teu BEGIN no es va executar i que tot va córrer en autocommit.
• Oblidar el rollback() a l'except. La connexió torna al pool avortada i peta la petició d'un altre usuari amb un 25P02 incomprensible. I no suposis que l'ORM no obre transaccions: psycopg i SQLAlchemy n'obren una a la primera consulta; JDBC no n'obre cap.
• Escriure una migració per a MySQL com si el DDL es pogués desfer. No es pot: cada ALTER TABLE confirma implícitament. Escriu sempre el down a mà.
• Ficar el cobrament amb targeta dins de la transacció. El ROLLBACK desfarà la teva comanda, no el càrrec. I PREPARE TRANSACTION sense un gestor que garanteixi resoldre-la deixa bloquejos i impedeix el VACUUM indefinidament.
• Consell: un savepoint per unitat de negoci descartable, no per sentència; i BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE per als informes llargs sobre producció.
• Consell: activa log_statement = 'all' una estona en desenvolupament i llegeix on posa el teu framework els BEGIN i els COMMIT. És revelador.

Treballa sobre la base acabada de recarregar.

Reprodueix la càrrega per lots de l'apartat 4 amb quatre comandes en lloc de tres, en una sola transacció i amb un savepoint per comanda: (1) client 6, 2 × producte 1 a 12,50 € — correcta; (2) client 11, 1 × producte 99 a 15,00 € — falla per FK inexistent; (3) client 12, 3 × producte 14 a 3,25 € — correcta; (4) client 13, 1 × producte 13 a 13,75 € i descomptar-ne l'estoc — falla per CHECK (stock >= 0).

1. Escriu el bloc complet, descartant només les comandes 2 i 4 del lot.
2. Indica quin id rep cada capçalera i per què hi ha forats.
3. Quantes files tenen comandes i linies_comanda després del COMMIT?
4. Reescriu la comanda 4 perquè no falli, fent servir el patró de 09-02, i explica què canvia en el flux de l'aplicació.

Un company t'ensenya aquest codi Python amb psycopg i et diu que "de vegades desa dues vegades la mateixa comanda i de vegades la connexió es queda tonta":

1. Localitza quatre problemes diferents.
2. On és el BEGIN d'aquesta transacció, sabent que ningú no l'ha escrit?
3. Què veu una altra sessió que consulti comandes mentre la passarel·la està responent?
4. Reescriu-lo en pseudocodi corregint-ho tot, i indica què caldria perquè el reintent fos idempotent.

Sobre DDL transaccional: (1) dins de BEGIN … ROLLBACK, executa ALTER TABLE productes ADD COLUMN pes_kg NUMERIC(6,3); i un UPDATE que l'ompli; comprova després que la columna no existeix. (2) intenta CREATE INDEX CONCURRENTLY idx_prova ON comandes (estat); dins d'un BEGIN i explica per què és una excepció. (3) digues què canvia en l'estratègia de desplegament si la mateixa migració és per a MySQL, té cinc passos i falla al tercer.

Solució 1

1. L'estructura és exactament la de l'apartat 4, amb quatre blocs SAVEPOINT lot_N … (RELEASE si tot va anar bé, ROLLBACK TO si va fallar) i un COMMIT al final. Els blocs 2 i 4 acaben en ROLLBACK TO SAVEPOINT: el 2 després de l'error de clau forana del producte 99, i el 4 després del CHECK que impedeix deixar l'estoc del producte 13 a −1.

2. Les capçaleres reben 21, 22, 23 i 24 en ordre, però només sobreviuen la 21 i la 23. Els forats (22 i 24) són valors de seqüència consumits per files que després es van descartar, i les seqüències no es desfan. El mateix passa amb linies_comanda: sobreviuen la 48 i la 50, i es perden la 49 i la 51.

3. comandes té 22 files (20 + 2) i linies_comanda en té 49 (47 + 2).

4. La versió que no falla fa servir l'UPDATE condicional:

Canvia tot el flux: en lloc d'un error que avorta la transacció i obliga al ROLLBACK TO, s'obté un UPDATE 0 que l'aplicació interpreta com a "esgotat". Pot llavors decidir en calent: descartar només aquella línia, deixar la comanda en pendent com a reserva, o proposar un producte alternatiu. Un cas de negoci previsible no hauria d'arribar mai al motor com a excepció.

Solució 2

1. Els quatre problemes:

2. El BEGIN el posa psycopg, automàticament, en executar la primera sentència sobre la connexió — és a dir, a l'INSERT. Per això la transacció ja és oberta quan es crida la passarel·la, encara que al codi no hi aparegui la paraula. 3. Una altra sessió veu 20 comandes: la capçalera nova no està confirmada i per a ella no existeix (09-01). Durant els vuit segons de la passarel·la, aquesta comanda és en un llimb visible només per a la seva pròpia sessió.

4. La versió corregida:

Perquè el reintent sigui idempotent calen dues coses: una clau d'idempotència a la passarel·la (l'identificador del carretó), perquè un segon cobrament amb la mateixa clau no carregui dues vegades; i una restricció UNIQUE sobre aquesta mateixa clau a comandes, perquè el segon INSERT xoqui en lloc de duplicar — o directament ON CONFLICT DO NOTHING (05-05). Sense identitat de negoci no hi ha idempotència possible.

Solució 3

1. Després del ROLLBACK, la columna no existeix: ERROR: column "pes_kg" does not exist. ALTER TABLE és transaccional a PostgreSQL, així que l'ADD COLUMN i l'UPDATE posterior es desfan junts.

2. ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Aquesta sentència necessita diverses transaccions internes: recorre la taula en dues passades i espera entre elles que acabin les transaccions en curs, per no bloquejar les escriptures. Res d'això no cap dins d'una única transacció de l'usuari. El perquè d'aquesta variant es tanca a 09-05.

3. A MySQL, ALTER TABLE productes ADD COLUMN pes_kg DECIMAL(6,3); provoca un commit implícit i no es pot desfer, així que l'estratègia canvia completament. A PostgreSQL tot el fitxer va en una transacció: falla el tercer pas, es desfan els tres i l'esquema queda intacte; es corregeix i es rellança. A MySQL, els dos primers passos ja estan aplicats i confirmats i el tercer pot haver quedat a mitges: cal escriure a mà un guió de reversió (down) per a cada pas, aplicar-los d'un en un verificant entre ells, i dissenyar cadascun idempotent i compatible cap enrere — el patró expand/contract de 05-06 deixa de ser una bona pràctica i passa a ser obligatori.

Ja tens el panell de comandaments complet:

• El repertori del TCL complet, i les opcions de BEGIN —ISOLATION LEVEL, READ ONLY i DEFERRABLE— amb la combinació d'or per a un informe sobre producció: SERIALIZABLE READ ONLY DEFERRABLE.
• Els SAVEPOINT: descartar una comanda d'un lot sense perdre les anteriors, i —el més útil de tot— l'única manera de sortir de l'estat avortat de 09-01 sense tancar la transacció. Amb el seu preu: subtransaccions, files mortes i valors de seqüència perduts. Un per unitat de negoci, no un per sentència.
• Que les seqüències no es desfan ni amb ROLLBACK ni amb ROLLBACK TO SAVEPOINT, i que per això els id tenen forats: al lot van sobreviure el 21 i el 23, i es van perdre el 22 i el 49.
• L'autocommit dels controladors, i les tres sorpreses: psycopg i SQLAlchemy obren transacció sols a la primera consulta, JDBC no n'obre cap, i els blocs imbricats dels ORM són savepoints, no transaccions imbricades.
• El DDL transaccional: a PostgreSQL es pot fer ROLLBACK d'un CREATE TABLE —i per això les seves migracions són segures—, amb set excepcions encapçalades per CREATE INDEX CONCURRENTLY. A MySQL no: cada sentència d'esquema confirma implícitament el que hi hagués pendent. És el que va anunciar 05-06. I les transaccions preparades, que gairebé mai no hauries de tocar a mà.
• Els patrons d'aplicació: try / except / rollback() obligatori, reintents idempotents amb clau de negoci, i cap crida externa dins de la transacció.
• I l'exemple integrador: la comanda 21 confirmada de principi a fi, 47,00 € + 4,95 € = 51,95 €, amb els estocs a 118 i 39, i un savepoint abans del descompte.

Però aquesta comanda encara té un punt cec, i l'hem deixat a la vista expressament: què passa si un altre client compra l'últim te matcha en el mateix instant? Les dues sessions llegeixen 40, totes dues resten 1, i totes dues escriuen 39 — s'han venut dues unitats i només se n'ha descomptat una. A Nivells d'aïllament i anomalies de concurrència veuràs aquesta actualització perduda demostrada pas a pas en dues sessions, juntament amb la lectura bruta, la lectura no repetible i la lectura fantasma; els quatre nivells de l'estàndard i quina anomalia permet cadascun; el que gairebé cap curs no explica —que PostgreSQL no implementa READ UNCOMMITTED i que el seu REPEATABLE READ ja impedeix els fantasmes, a diferència de l'estàndard—; l'error 40001 i per què, si fas servir els nivells alts, la teva aplicació ha de saber reintentar.