De les quatre lletres d'ACID, tres són innegociables i una té un comandament de volum: l'aïllament. Pots demanar la il·lusió perfecta que ets l'únic usuari —i pagar-la amb menys concurrència i transaccions que avorten— o relaxar-la a canvi de rendiment, acceptant que passin certes coses estranyes. Aquestes coses estranyes tenen nom des del 1992, són quatre, i aquesta lliçó les demostra una a una en dues sessions.

Després vindran els quatre nivells de l'estàndard SQL amb la taula clàssica de quina anomalia permet cadascun; i tot seguit el que gairebé cap curs no explica: aquesta taula no descriu PostgreSQL. PostgreSQL no implementa READ UNCOMMITTED, i el seu REPEATABLE READ ja impedeix els fantasmes, cosa que l'estàndard no exigeix. Acabarem amb l'error 40001, el més important del mòdul, i amb la seva conseqüència pràctica: si puges de nivell, la teva aplicació ha de saber reintentar.

Tots els exemples són reproduïbles amb dues terminals psql sobre la base acabada de recarregar, en el format de dues sessions de 09-01.

1. Lectura bruta
2. Lectura no repetible
3. Lectura fantasma
4. Actualització perduda i anomalia de serialització
5. Els quatre nivells de l'estàndard SQL
6. El que fa PostgreSQL de debò
7. El nivell per omissió de cada motor
8. Com es fixa i com es consulta el nivell
9. READ COMMITTED enfront de REPEATABLE READ en dues sessions
10. SERIALIZABLE i l'error 40001
11. El patró de reintent amb retrocés exponencial
12. Quin nivell triar
13. Errors habituals i consells
14. Exercicis
15. Conclusió

Lectura bruta (dirty read): una transacció llegeix dades que una altra ha escrit i encara no ha confirmat, i que poden desaparèixer amb un ROLLBACK.

En un motor que permetés la lectura bruta, a t3 la sessió B veuria 99.00 — un preu que no ha existit mai per a ningú, perquè a t4 es desfà. Si B fos el procés que genera el fitxer del comparador de preus, BotigaVerda hauria publicat un te matcha a 99 €.

A PostgreSQL, en canvi, B veu 22.00. Sempre. En qualsevol nivell d'aïllament, inclòs el que s'anomena READ UNCOMMITTED:

I no és que PostgreSQL "s'esforci" a evitar-ho: amb MVCC la lectura bruta és impossible per construcció. La versió nova de la fila porta un xmin d'una transacció no confirmada, i la regla de visibilitat de 09-02 la descarta sense més. No hi ha codi per treure ni nivell per baixar.

Nota de dialecte: la lectura bruta sí que existeix, i s'utilitza. SQL Server implementa READ UNCOMMITTED de debò, i la seva famosa pista WITH (NOLOCK) és exactament això: llegir sense respectar bloquejos ni confirmacions. Es posa en informes per no bloquejar ningú, i a canvi es poden veure files no confirmades, files duplicades i files absents en el mateix recorregut. MySQL/InnoDB també l'implementa. PostgreSQL, Oracle i SQLite, no.

Lectura no repetible (non-repeatable read): la mateixa consulta, sobre la mateixa fila, retorna valors diferents dins d'una mateixa transacció, perquè una altra la va modificar i confirmar pel mig.

El cas de BotigaVerda: l'analista està calculant l'informe de marges i, a mitges, el responsable de compres apuja el preu del matcha.

Dues lectures de la mateixa fila dins de la mateixa transacció, dos valors diferents. Si l'informe suma imports a t2 i calcula percentatges a t4, els percentatges no quadraran amb els imports i ningú no sabrà per què.

La causa és exactament la de 09-02: a READ COMMITTED, cada sentència pren una instantània nova. A REPEATABLE READ la instantània és una de sola per a tota la transacció, i t4 continuaria retornant 22.00 — ho veuràs a l'apartat 9.

Lectura fantasma (phantom read): la mateixa consulta retorna un conjunt de files diferent, perquè una altra transacció va inserir o esborrar files que compleixen la condició.

La diferència amb l'anterior és subtil però important: allà canviava el valor d'una fila; aquí canvia quines files hi ha.

L'informe dirà que hi ha 21 comandes i 47 línies, i algú passarà la tarda buscant la comanda sense línies. Aquesta comanda 21 és el fantasma: va aparèixer a mitja elaboració de l'informe.

El detall que gairebé ningú no explica: l'estàndard SQL permet fantasmes a REPEATABLE READ, però PostgreSQL no els permet. El seu REPEATABLE READ està implementat com a snapshot isolation: una única instantània per a tota la transacció, i una instantània no canvia de contingut. A PostgreSQL, t4 retornaria 20 — el mateix número que t2. És una garantia més forta que la de l'estàndard, i hi tornarem a l'apartat 6.

Aquesta és la important, la que li pot costar diners a BotigaVerda, i la que va deixar pendent 09-03.

Actualització perduda (lost update): dues transaccions llegeixen el mateix valor, cadascuna calcula un valor nou a partir d'ell i totes dues escriuen. La segona escriptura esborra la feina de la primera.

L'escenari: queden 40 unitats de te matcha i dos clients en compren una cadascun alhora. L'aplicació fa el que és natural —llegir l'estoc, restar, escriure—, que és precisament el que 05-05 ja va assenyalar com a incorrecte:

S'han venut dues unitats i només se n'ha descomptat una. Cap sessió no ha rebut un error, cap registre no ha quedat al log, i el descompensament no apareixerà fins a l'inventari físic. Amb dues sessions la pèrdua és d'una unitat; amb un pic de trànsit per Nadal, de desenes.

Canvia únicament la forma de l'UPDATE:

Trenta-vuit: correcte. I no és màgia. Quan B intenta actualitzar a t7 una fila que A té bloquejada, B es queda esperant (és el bloqueig implícit de 09-05). En confirmar A, PostgreSQL no aplica l'UPDATE de B a cegues: torna a llegir la fila actualitzada, reavalua el WHERE i recalcula l'expressió sobre la versió nova. Així que stock - 1 es calcula sobre 39, no sobre 40.

La regla que cal extreure, i val per a tot el mòdul: a READ COMMITTED, una sola sentència d'escriptura és segura; el perill és a llegir amb una sentència i escriure amb una altra. SET stock = stock - 1 és segur; SELECT i després SET stock = 39 no ho és. És la mateixa lliçó de 05-05 amb l'upsert i la de 09-02 amb WHERE stock >= 1.

Quan la lògica no cap en una sentència —perquè cal consultar tres taules, aplicar una regla i decidir— hi ha dues solucions correctes, i són les dues lliçons que queden: pujar el nivell d'aïllament (apartats següents) o bloquejar explícitament la fila amb SELECT ... FOR UPDATE (09-05).

L'estàndard SQL:1992 va definir els nivells per les anomalies que permeten, no per com s'implementen:

Aquesta és la taula que surt a totes les entrevistes de feina. I és una definició de mínims: diu el que un motor pot permetre, no el que fa. Un motor que no permeti cap anomalia en cap nivell compleix l'estàndard perfectament.

Tres diferències respecte de l'estàndard, i totes tres importen:

1. READ UNCOMMITTED no existeix. S'accepta la sintaxi per compatibilitat, però es comporta exactament com READ COMMITTED. La lectura bruta és impossible amb MVCC (apartat 1).
2. REPEATABLE READ ja impedeix els fantasmes. L'estàndard els permet; PostgreSQL, no. La seva implementació és snapshot isolation: una única instantània per a tota la transacció. Només hi ha tres nivells distingibles a la pràctica.
3. REPEATABLE READ i SERIALIZABLE no bloquegen per aconseguir-ho: avorten. En lloc de fer-te esperar, et deixen treballar i, si al final detecten un conflicte, maten la teva transacció amb l'error 40001. Aquest canvi de model és el que obliga a reintentar, i és l'apartat 11.

I una quarta que mereix la seva pròpia línia, perquè és la raó de ser del nivell més alt:

L'anomalia de serialització és més general que les tres clàssiques: és qualsevol resultat que cap execució seqüencial de les mateixes transaccions no hauria pogut produir, encara que cadascuna hagi llegit i escrit dades confirmades i diferents. SERIALIZABLE és l'únic nivell que la impedeix, i ho aconsegueix amb SSI (Serializable Snapshot Isolation): vigila les dependències de lectura i escriptura entre transaccions i avorta les que formin un cicle perillós.

Nota de dialecte — el parany en migrar. El nom del nivell no diu el mateix en dos motors. Una aplicació escrita contra MySQL corre en REPEATABLE READ sense saber-ho i, en portar-la a PostgreSQL, passa a READ COMMITTED: apareixen lectures no repetibles que allà no passaven. I a l'inrevés: codi que a PostgreSQL funciona en REPEATABLE READ perquè el motor avorta els conflictes, a MySQL no avorta i perd actualitzacions en silenci. Verifica sempre el nivell efectiu en migrar; és el primer que cal mirar.

I per a tota la sessió o per a tot el servidor, el de 09-03: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ..., ALTER ROLE ... SET default_transaction_isolation, o aquest mateix paràmetre a postgresql.conf.

Tota la diferència és en quan es pren la instantània:

Mateix guió, dos nivells, resultats diferents. La sessió A és l'informe; la B, l'operativa de la botiga.

A en READ COMMITTED (una instantània nova per sentència):

A en REPEATABLE READ (BEGIN ISOLATION LEVEL REPEATABLE READ;), amb el guió idèntic:

Els mateixos valors que a t1 i t2. Per a A, el món es va congelar en l'instant de la seva primera consulta, i l'UPDATE i l'INSERT de B no existeixen fins que A confirmi i comenci una altra transacció. Ni lectura no repetible ni fantasma: és exactament el que un informe necessita.

El preu d'aquesta foto fixa apareix així que A intenta escriure alguna cosa que B ja ha canviat:

L'actualització perduda de l'apartat 4 ja no passa: en lloc de trepitjar el canvi de B en silenci, PostgreSQL mata la transacció d'A. Aquest és el tracte dels nivells alts, i és un tracte honest: prefereixes un error que puguis reintentar abans que un descompensament invisible.

REPEATABLE READ protegeix cada fila, però no protegeix relacions entre files. El cas típic a BotigaVerda: la regla "l'estoc total de la categoria Begudes no pot baixar de 250 unitats", comprovada per dos operaris alhora.

Totes dues van llegir 370, totes dues van restar 60, i el total real hauria quedat en 250… que encara compleix la regla. Canvia els 60 per 70 i el total baixa a 230: les dues comprovacions eren correctes i el resultat combinat, no. Cap fila no es va trepitjar —A va tocar el producte 14 i B el 16—, així que REPEATABLE READ hauria deixat passar totes dues. Només SERIALIZABLE veu que A va llegir el que B va escriure i a l'inrevés i n'avorta una.

Fixa't en tres coses de l'error, perquè són la signatura de tot el nivell: el codi 40001, comú a tots els errors de serialització; el HINT explícit que reintentar pot funcionar —no és un error de dades, és un conflicte de programació temporal—; i que la fallada arriba al COMMIT, quan ja has fet tota la feina.

Si fas servir REPEATABLE READ o SERIALIZABLE, la teva aplicació ha d'estar preparada per reintentar. No és opcional ni és un cas rar: és el mode normal de funcionament d'aquests nivells.

Sis regles que fan que aquest bucle funcioni de debò:

1. Es reintenta la transacció sencera, des del BEGIN. No es pot continuar on es va quedar: la seva instantània ja no val.
2. Només es reintenten 40001 i 40P01 (interbloqueig, 09-05). Una violació de clau forana no s'arregla repetint-la: es repetirà igual cinc vegades i es perdran cinc segons.
3. El retrocés és exponencial i amb atzar. Sense la component aleatòria (jitter), dues transaccions que xoquen reintenten alhora i tornen a xocar, indefinidament.
4. La transacció ha de ser idempotent, que és la propietat de 05-03 i 09-03: si el primer intent va arribar a inserir la comanda i va fallar després, el segon no la pot duplicar. Una clau única de negoci ho resol.
5. Res fora de la base de dades dins del bloc: reintentar cinc vegades una transacció que cobra amb targeta significa cinc càrrecs (09-02). I compta els reintents: una taxa creixent de 40001 és el senyal que hi ha contenció real i que el problema és de disseny, no de configuració.

La regla de decisió, en una frase: comença sempre a READ COMMITTED i puja només quan puguis anomenar l'anomalia concreta que t'està fent mal. Pujar de nivell no és "més segur" sense més: és canviar un problema silenciós per un error explícit que cal gestionar amb codi.

• Estudiar-se la taula de l'estàndard i creure que descriu el teu motor. No en descriu cap amb exactitud: PostgreSQL no té READ UNCOMMITTED i el seu REPEATABLE READ no permet fantasmes. I READ COMMITTED no protegeix de l'actualització perduda: protegeix de la lectura bruta i de res més.
• Confondre SELECT + UPDATE amb un UPDATE relatiu. SET stock = stock - 1 és segur fins i tot a READ COMMITTED; llegir 40 i escriure 39 no ho és. És la distinció més rendible de la lliçó.
• Pujar a SERIALIZABLE sense escriure el reintent. Canvies corrupció silenciosa per caigudes visibles en producció. El nivell alt sense reintent és pitjor que el nivell baix.
• Reintentar qualsevol error, o reintentar sense jitter. Només 40001 i 40P01 mereixen reintent; repetir una violació de restricció és perdre el temps cinc vegades seguides. I sense la component aleatòria, dues transaccions sincronitzades tornen a xocar a cada volta.
• Suposar que el REPEATABLE READ de MySQL és el de PostgreSQL. El de MySQL permet actualitzacions perdudes que PostgreSQL avorta; el de PostgreSQL impedeix fantasmes que el de MySQL només evita amb gap locks. I WITH (NOLOCK) a SQL Server no és "anar més ràpid": és lectura bruta, amb files no confirmades, duplicades o absents en el mateix recorregut.
• Consell: posa SHOW transaction_isolation; a la teva llista de comprovacions en depurar concurrència; és la primera pregunta. I per a un informe, BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;: coherència total i ni un sol bloqueig a ningú.
• Consell: mesura els 40001. La seva taxa et diu si el teu disseny té contenció real, i aquesta informació no és a cap altre lloc.

Amb dues terminals psql i la base acabada de recarregar.

Demostra l'actualització perduda i les seves dues solucions.

1. Reprodueix l'apartat 4 amb el patró SELECT → UPDATE ... SET stock = 39 a READ COMMITTED. Comprova que l'estoc final és 39 i explica quina unitat s'ha perdut.
2. Repeteix-ho amb UPDATE ... SET stock = stock - 1. Comprova que el resultat és 38 i descriu què fa exactament la sessió B a t7.
3. Repeteix el cas 1 posant les dues sessions a REPEATABLE READ. Què passa i quan exactament? Copia l'error.
4. Quin nivell d'aïllament faries servir a l'aplicació real de BotigaVerda per confirmar una comanda, i per què no és la resposta pujar a SERIALIZABLE?

L'analista Daniel Vercher (empleat 8) llança l'informe mensual i es queixa que "els números no quadren entre les taules del PDF".

1. Reprodueix el problema: sessió A a READ COMMITTED compta comandes, una altra sessió insereix una comanda amb dues línies i confirma, i A torna a comptar comandes i línies. Mostra els números incoherents.
2. Identifica quina anomalia és i per què l'estàndard l'anomena així.
3. Arregla-ho canviant una sola línia del guió d'A, i demostra que ara quadra.
4. Si l'informe triga 40 minuts, quin efecte col·lateral té mantenir aquesta transacció oberta tot aquest temps? Relaciona-ho amb el que vas veure a 09-02 i proposa la variant de 09-03 que ho mitiga.

BotigaVerda imposa una regla nova: la categoria Begudes (4) no pot baixar mai de 250 unitats d'estoc total. Avui en té 370.

1. Escriu la transacció que descompta 70 unitats del producte 14 comprovant abans la regla, a SERIALIZABLE.
2. Executa-la alhora en dues sessions —una sobre el producte 14 (stock 180) i una altra sobre el 17 (stock 90)— i mostra què passa a cada COMMIT.
3. Què hauria passat a REPEATABLE READ? I a READ COMMITTED? Raona per què cap dels dos no detecta el problema.
4. Escriu el pseudocodi de reintent que caldria perquè aquesta operació funcionés en producció, i digues per què no es pot resoldre amb un CHECK.

Solució 1

1. Estoc final 39. S'ha perdut la unitat de la sessió A: el seu UPDATE de t5 es va arribar a escriure i a confirmar, però el de B el va sobreescriure amb un valor absolut calculat a partir d'una lectura ja obsoleta. A va vendre i no va descomptar.

2. Estoc final 38. A t7, la sessió B intenta actualitzar una fila que A té bloquejada, així que es queda esperant —l'UPDATE no retorna res, la terminal es queda penjada— fins que A fa COMMIT a t6. Llavors PostgreSQL rellegeix la fila ja actualitzada, reavalua el WHERE i recalcula stock - 1 sobre 39, no sobre 40. L'atomicitat d'una sola sentència fa la feina.

3. La sessió que arriba segona falla al seu UPDATE, no al COMMIT:

L'actualització perduda és ara impossible, però a canvi cal reintentar. I observa el matís: a REPEATABLE READ el conflicte es detecta en escriure, mentre que a SERIALIZABLE (exercici 3) se sol detectar en confirmar.

4. A l'aplicació real, READ COMMITTED més una d'aquestes dues: l'UPDATE ... WHERE id = 15 AND stock >= 1 de 09-02, o el SELECT ... FOR UPDATE de 09-05. Pujar a SERIALIZABLE no és la resposta per tres motius: funcionaria, però obligaria a implementar el bucle de reintent al camí més calent de la botiga; avorta sota càrrega justament quan hi ha més comandes, que és el pitjor possible; i és innecessari, perquè l'invariant afecta una sola fila i una sola sentència el pot expressar. El nivell alt es reserva per a invariants que cap sentència no pot expressar.

Solució 2

1. A obté 20 comandes a la seva primera consulta i, després de l'INSERT de l'altra sessió, 21 comandes i 49 línies. El PDF diria 21 comandes amb 49 línies si totes dues comptes es fessin després, o 20 comandes i 49 línies si es fessin a cavall del COMMIT aliè: en qualsevol cas, dues xifres preses de dos estats diferents del món.

2. És una lectura fantasma: la mateixa consulta retorna un conjunt de files diferent perquè una altra transacció va inserir files que compleixen la condició. S'anomena així perquè les files noves "apareixen" en una consulta que ja s'havia executat, com si es materialitzessin del no-res.

3. La línia que canvia és el BEGIN:

Amb la instantània fixa, A veu 20 comandes i 47 línies a totes les seves consultes, comenci la sessió B el que comenci. A PostgreSQL això n'hi ha prou perquè el seu REPEATABLE READ no permet fantasmes; en un motor que seguís l'estàndard al peu de la lletra caldria SERIALIZABLE.

4. Mantenir 40 minuts una transacció oberta amb instantània fixa impedeix que VACUUM netegi cap versió de fila posterior a aquell instant, a tota la base de dades: és el mecanisme del bloat que va explicar 09-02 i el dany que va anticipar 09-01. La mitigació de 09-03 és llançar l'informe com a BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;: continua retenint la seva instantània, però com que és de només lectura i diferible no bloqueja ningú ni pot avortar. I la solució de fons és la de sempre: executar els informes llargs sobre una rèplica (09-02).

Solució 3

1. La transacció, per al producte 14:

2. La primera a confirmar té èxit. La segona falla al COMMIT:

Cadascuna va comprovar 370 − 70 = 300 i totes dues tenien raó per separat; juntes deixarien el total en 230, per sota del límit. SERIALIZABLE detecta que A va llegir un conjunt de files que B va modificar i a l'inrevés —una dependència creuada de lectura i escriptura— i en cancel·la una de les dues.

3. Ni REPEATABLE READ ni READ COMMITTED no ho detecten, i pel mateix motiu: no hi ha cap fila en conflicte. A escriu el producte 14 i B el 17; són files diferents, així que no hi ha bloqueig, no hi ha conflicte d'actualització i res per avortar. REPEATABLE READ protegeix files; l'invariant aquí és sobre un conjunt. A READ COMMITTED seria encara pitjor, perquè a més cada sentència veuria dades diferents. Totes dues confirmarien feliçment i el total quedaria en 230.

4. El pseudocodi és el de l'apartat 11: bucle de fins a cinc intents, BEGIN ISOLATION LEVEL SERIALIZABLE, captura de 40001, rollback(), espera amb retrocés exponencial i jitter, i rellançar qualsevol altre error sense reintentar.

I no es pot resoldre amb un CHECK pel que va explicar 09-02: un CHECK només pot mirar les columnes de la pròpia fila, i aquesta regla necessita sumar l'estoc de quatre files de la taula. Les alternatives reals són tres: SERIALIZABLE amb reintent (la d'aquest exercici); bloquejar explícitament les quatre files amb SELECT ... FOR UPDATE abans de comprovar (09-05); o mantenir el total agregat en una fila pròpia i protegir-la amb un CHECK i un trigger que la mantingui (10-05). La primera és la més neta; la segona, la més previsible sota càrrega.

Ja saps què et pot passar i quant costa evitar-ho:

• Les quatre anomalies, demostrades en dues sessions: lectura bruta (llegir el que no s'ha confirmat), lectura no repetible (la mateixa fila canvia de valor), lectura fantasma (apareixen files noves) i actualització perduda / anomalia de serialització (dues transaccions correctes per separat produeixen un resultat que cap execució seqüencial no donaria).
• Que l'actualització perduda és el cas central: dos clients compren l'últim matcha, tots dos llegeixen 40, tots dos escriuen 39, i es ven una unitat que ningú no descompta, sense error ni traça.
• I la seva contrapartida més útil de tot el mòdul: a READ COMMITTED, una sola sentència d'escriptura és segura —SET stock = stock - 1 dóna 38 perquè el motor rellegeix i recalcula— i el perill és a llegir amb una sentència i escriure amb una altra.
• Els quatre nivells de l'estàndard amb la seva taula clàssica… i el que PostgreSQL fa de debò: no implementa READ UNCOMMITTED (la lectura bruta és impossible amb MVCC), el seu REPEATABLE READ ja impedeix els fantasmes i només hi ha tres nivells distingibles.
• El nivell per omissió de cada motor —READ COMMITTED a PostgreSQL, Oracle i SQL Server; REPEATABLE READ a InnoDB; SERIALIZABLE de facto a SQLite— i el parany en migrar: el mateix nom no significa el mateix.
• L'error 40001, en les seves dues formes (concurrent update a REPEATABLE READ, read/write dependencies a SERIALIZABLE), amb el seu HINT dient que reintentar pot funcionar. I la conseqüència: pujar de nivell obliga a escriure el bucle de reintent, amb retrocés exponencial, jitter, filtratge per SQLSTATE i una unitat de treball idempotent.
• I el criteri: READ COMMITTED per omissió; REPEATABLE READ READ ONLY per a informes; SERIALIZABLE només per a invariants que abasten diverses files i cap sentència no pot expressar. Puja de nivell únicament quan puguis anomenar l'anomalia que t'està fent mal.

Queda l'altra resposta a l'actualització perduda, la que no avorta res i la que fan servir de debò els sistemes de comerç electrònic: bloquejar la fila. A Gestió de la concurrència: bloquejos i interbloquejos veuràs per què la sessió B es quedava esperant a t7 i què la retenia; el patró llegir-modificar-escriure segur amb SELECT ... FOR UPDATE i tota la seva família; SKIP LOCKED per repartir una cua de comandes entre diversos processos sense trepitjar-se; el bloqueig optimista amb columna de versió enfront del pessimista; què bloqueja un ALTER TABLE i per què havia d'existir CREATE INDEX CONCURRENTLY; i els interbloquejos: com es produeixen, com els detecta i resol el motor, com es diagnostiquen amb pg_locks i pg_blocking_pids, i les regles per no provocar-los mai.