A la lliçó anterior va quedar una lletra sense desenvolupar. La A d'atomicitat, la C de consistència i la D de durabilitat les vam obrir en canal; la I d'aïllament la vam enunciar en quatre línies i la vam ajornar. Aquest és l'ajornament.

El motiu d'ajornar-la és que l'aïllament no s'assembla a les altres tres. L'atomicitat no admet graus: una transacció és atòmica o no ho és. La durabilitat tampoc: el que s'ha confirmat sobreviu o no sobreviu. L'aïllament, en canvi, és un dial. L'estàndard SQL defineix quatre posicions, cada gestor implementa les que vol i com vol, i triar la posició equivocada produeix errors que no apareixen al portàtil de desenvolupament, no apareixen als tests, apareixen un dimarts a les onze del matí al taulell de la sucursal Centre i no hi ha manera de reproduir-los.

Perquè això és exactament el que ha passat aquesta setmana a BiblioRed. L'exemplar EJ-3081 d'"El mapa del temps" figura prestat a dos socis diferents alhora. El club de lectura de tardor de la sucursal Nord té 25 places i 26 inscrits, tot i que el sistema comprovava l'aforament abans d'inscriure. I un esdeveniment publicat s'ha quedat sense cap ponent, malgrat que l'aplicació impedeix cancel·lar l'últim.

Cap dels tres no és un error de programació en el sentit habitual. El codi que els produeix es llegeix bé, passa la revisió i funciona perfectament quan l'executa una sola persona. Els tres són fallades de concurrència, i aquesta lliçó tracta de reconèixer-les, provocar-les a voluntat i arreglar-les.

Tot el que segueix està pensat perquè ho reprodueixis. Obre dos terminals amb psql connectats a la mateixa base de dades. Al llarg de la lliçó els anomenarem Sessió A i Sessió B, i cada demostració indica en quin ordre cal executar cada instrucció. Executar-les en un altre ordre dóna un altre resultat, i aquesta és precisament l'ensenyança.

1. Per què la concurrència és imprescindible i per què és perillosa
2. El laboratori: dos terminals i l'estat inicial
3. Fenomen 1: actualització perduda
4. Fenomen 2: lectura bruta
5. Fenomen 3: lectura no repetible
6. Fenomen 4: lectura fantasma i l'última plaça
7. Fenomen 5: biaix d'escriptura, el que sorprèn
8. Els quatre nivells d'aïllament de l'estàndard SQL
9. Com s'estableix el nivell i què fa realment PostgreSQL
10. Control de concurrència per bloqueig
11. Bloqueigs explícits: FOR UPDATE, FOR SHARE, LOCK TABLE, SKIP LOCKED
12. Control multiversió (MVCC) i per què existeix VACUUM
13. Interbloqueigs: com es produeixen, com es detecten, com s'eviten
14. Bloqueig optimista enfront de pessimista
15. La solució completa: l'última plaça del club de lectura
16. Fora de PostgreSQL: SQLite i el retorn del problema a NoSQL

Comencem per l'obvi, perquè explica per què no n'hi ha prou de "fer-ho d'un en un".

Per què és imprescindible. BiblioRed té quatre taulells, un catàleg web obert a 12.000 socis, una aplicació mòbil i diversos processos automàtics (avisos de venciment, generació d'informes). Si les operacions s'executessin estrictament una darrere l'altra, cada taulell esperaria que acabessin tots els altres. I no només això: mentre una transacció espera que el disc confirmi el seu fsync() —aquells mil·lisegons de l'apartat 12 de la lliçó anterior—, el processador estaria ociós. La concurrència és el que permet que el temps d'espera d'una operació sigui el temps de treball d'una altra.

Per què és perillosa. Aquella última fila és tota la lliçó. Quan dues transaccions toquen les mateixes dades alhora, el resultat pot dependre de l'ordre exacte en què s'entrellacin les seves instruccions. I aquell ordre no el controles: el decideix el planificador del sistema operatiu, la latència de la xarxa i quina pàgina hi havia a la memòria cau en aquell microsegon.

La formulació clàssica del problema és aquesta:

L'objectiu del control de concurrència és que l'execució entrellaçada de diverses transaccions produeixi el mateix resultat que alguna execució en què s'haguessin executat una darrere l'altra. Aquesta propietat s'anomena serialitzabilitat.

Fixa't en l'"alguna": no s'exigeix un ordre concret. Si A i I s'executen alhora, val que el resultat sigui el d'"A i després I" o el d'"I i després A". El que no val és que sigui un resultat que cap ordre seqüencial no hauria produït. Quan això passa, tenim una anomalia.

Abans de provocar res, cal preparar l'escenari. Obre dos terminals:

Un truc molt pràctic: fes que cada sessió s'identifiqui a l'indicador i mostri sempre el número de procés, que caldrà en parlar de bloqueigs.

I l'estat de partida de les dades que maltractarem:

Una plaça lliure. Un exemplar disponible. Tot el que cal per trencar el sistema.

Actualització perduda (lost update). Dues transaccions llegeixen la mateixa dada, totes dues calculen un valor nou a partir del que han llegit, i totes dues escriuen. La segona escriptura trepitja la primera, que es perd sense deixar rastre ni error.

Aquesta és la fallada de l'exemplar EJ-3081. L'aplicació del taulell fa el que és natural: llegeix l'estat, comprova que està disponible, i el presta.

Els dos taulells de la sucursal Centre atenen alhora. La Sessió A és el taulell 1, atenent la Marta Alsina (sòcia 14). La Sessió B és el taulell 2, atenent l'Ivan Pereda (soci 15). Executa en l'ordre de la columna "moment":

Comprova el desastre:

Dos préstecs oberts del mateix exemplar físic. La Marta se l'ha endut a casa i l'Ivan és al taulell preguntant on és el seu llibre. No hi ha hagut cap error, cap excepció, cap traça. Les dues transaccions han estat atòmiques, consistents i durables. I el resultat és impossible.

La comprovació d'A (t3) i l'escriptura d'A (t8) estan separades en el temps, i en aquell forat B ha llegit. B ha pres la seva decisió amb informació que va deixar de ser certa abans que B actués. És el que s'anomena una seqüència llegir-modificar-escriure sense protecció.

Fixa't en un detall important: cap ordre seqüencial no produeix aquest resultat. Si A s'hagués executat sencera i després B, B hauria llegit prestat i hauria rebutjat el préstec. A l'inrevés, igual. El resultat obtingut no correspon a cap execució en sèrie: és una anomalia en el sentit estricte de l'apartat 1.

És important entendre per què això passa malgrat que un UPDATE solt sí que és segur. Compara:

La segona forma és atòmica de debò: el gestor bloqueja la fila per actualitzar-la i avalua la condició sobre la versió més recent. Si una altra transacció ja la va posar en prestat, la resposta és:

I aquell UPDATE 0 és el senyal que l'aplicació ha de llegir com "algú se m'ha avançat, cancel·la l'operació". Comprovar el nombre de files afectades és la defensa més barata que existeix contra l'actualització perduda, i sorprèn quant codi la ignora.

Lectura bruta (dirty read). Una transacció llegeix dades que una altra ha modificat però encara no ha confirmat. Si l'altra fa ROLLBACK, la primera ha treballat amb dades que mai no van existir.

En un gestor que permetés la lectura bruta, a t4 la Sessió B llegiria pagada i l'informe comptaria un cobrament que mai no va passar.

A PostgreSQL, a t4 la Sessió B llegeix pendent. Sempre. No hi ha manera de provocar una lectura bruta, ni tan sols demanant-ho explícitament:

PostgreSQL accepta la sintaxi READ UNCOMMITTED per compatibilitat amb l'estàndard, però internament la tracta com READ COMMITTED. La raó és la seva arquitectura: com veurem a l'apartat 12, el control multiversió fa que una transacció llegeixi sempre una versió confirmada de cada fila. Llegir dades brutes no és que estigui prohibit: és que no hi ha cap mecanisme amb què fer-ho.

Això no significa que el fenomen sigui una curiositat històrica. Altres gestors sí que el permeten —SQL Server amb READ UNCOMMITTED o l'infame WITH (NOLOCK), MySQL amb READ UNCOMMITTED— i hi ha qui l'activa "perquè els informes no bloquegin". És una mala idea: a més de llegir dades que poden desaparèixer, en alguns motors pot llegir files duplicades o saltar-se files si l'índex es reorganitza durant el recorregut.

Lectura no repetible (non-repeatable read). Una transacció llegeix una fila, i en tornar-la a llegir dins de la mateixa transacció obté valors diferents, perquè una altra transacció la va modificar i confirmar pel mig.

La direcció de BiblioRed demana un informe que primer compta les multes pendents i després en suma l'import:

La mateixa consulta, dins de la mateixa transacció, ha tornat dos valors diferents. L'informe que A està construint barreja xifres de dos instants: si la primera lectura va alimentar un total i la segona un desglossament, el total i el desglossament no quadren. I qui el rebi pensarà que hi ha un error de càlcul.

En REPEATABLE READ, PostgreSQL pren una foto (snapshot) de la base de dades a la primera instrucció de la transacció, i totes les lectures posteriors veuen aquella foto, ignorant el que altres confirmin després. És exactament el que un informe necessita: una fotografia coherent d'un instant.

Regla pràctica. Tot informe que executi més d'una consulta i presenti els resultats junts hauria d'anar dins d'una transacció REPEATABLE READ. És gratis, és una línia, i elimina d'arrel la família sencera de "els números no quadren".

Lectura fantasma (phantom read). Una transacció executa una consulta amb una condició, i en repetir-la apareixen files noves que compleixen aquella condició i que una altra transacció va inserir i confirmar pel mig. La diferència amb la lectura no repetible és que allà canviaven els valors d'una fila; aquí canvia el conjunt de files.

Aquesta és la fallada del club de lectura, i és més subtil que les anteriors perquè el codi que la produeix sembla impecable.

L'aplicació d'inscripcions fa el següent: compta les places ocupades, comprova que en queden de lliures, i insereix.

Recorda l'estat: esdeveniment 51, 25 places, 24 ocupades, una de lliure. La Marta Alsina (14) s'inscriu des del mòbil mentre l'Ivan Pereda (15) s'inscriu al taulell Nord.

26 persones per a 25 cadires. El dia del club de lectura, algú es queda dret.

Aquest és el punt que cal interioritzar, perquè és contraintuïtiu:

Comptar abans d'inserir no protegeix de res. El recompte és cert en l'instant en què es fa i deixa de ser-ho immediatament després. Entre el SELECT i l'INSERT hi ha un forat, i per aquell forat hi cap una transacció sencera.

I hi ha una cosa pitjor. A la lectura no repetible, pujar a REPEATABLE READ n'hi havia prou. Aquí no n'hi ha prou amb cap foto, perquè el problema no és el que A veu: és que A i B estan decidint sobre la mateixa plaça sense saber-ho. Ni tan sols un bloqueig sobre les files llegides serviria, perquè les files conflictives —les inscripcions de l'altra— encara no existien quan es va llegir. No es pot bloquejar una fila que no existeix. D'aquí el nom "fantasma".

Les solucions reals es veuen a l'apartat 15, i són tres, amb conseqüències diferents.

L'estàndard SQL diu que REPEATABLE READ permet lectures fantasma. PostgreSQL no les permet: el seu REPEATABLE READ és en realitat snapshot isolation, i la foto és de la base sencera, així que les files inserides després són invisibles. És més estricte que l'estàndard.

Però compte amb la conclusió: això resol el fantasma de lectura, no el problema de l'última plaça. Sota REPEATABLE READ, A no veuria la inscripció de B... i tot i així inseriria la seva, i el COMMIT de totes dues tindria èxit perquè no hi ha conflicte d'escriptura sobre una mateixa fila. El resultat continuaria sent 26. Aquest és el pont natural cap al fenomen següent.

Biaix d'escriptura (write skew). Dues transaccions llegeixen un mateix conjunt de dades, cadascuna decideix alguna cosa basant-s'hi, i cadascuna escriu en files diferents. Cap no trepitja l'altra, així que no hi ha conflicte detectable, però juntes violen una regla que individualment respectaven.

És el fenomen que sorprèn fins i tot qui porta anys treballant amb bases de dades, perquè passa en REPEATABLE READ / snapshot isolation, que molta gent dóna per "el nivell segur".

Regla de la casa: tot esdeveniment publicat ha de tenir almenys un ponent confirmat. L'esdeveniment 51 en té dos: la Núria Bastos i un ponent extern. L'aplicació, en cancel·lar un ponent, comprova que en quedi algun.

Zero ponents en un esdeveniment publicat. I les dues transaccions tenien raó quan van decidir.

Fixa't en per què el gestor no ha protestat: A va escriure a la fila del ponent 7, B a la del ponent 9. No hi ha cap fila en conflicte. Els mecanismes que detecten actualitzacions perdudes treballen a nivell de fila, i aquí no hi ha dues escriptures sobre la mateixa fila. El conflicte és a la premissa: totes dues van llegir un conjunt que l'altra anava a modificar.

Altres exemples del mateix patró, perquè el reconeguis quan el vegis:

Les úniques defenses contra el biaix d'escriptura són:

1. Aïllament SERIALIZABLE (amb reintents, perquè avortarà transaccions).
2. Materialitzar el conflicte: forçar que les dues transaccions escriguin a la mateixa fila, encara que sigui artificialment —per exemple, bloquejant la fila d'esdeveniments amb SELECT ... FOR UPDATE abans de tocar-ne els ponents—.
3. Una restricció de la base de dades que expressi la regla, quan sigui possible. Aquí no ho és directament (un CHECK no pot comptar files d'una altra taula), cosa que il·lustra el límit de les restriccions declaratives enfront de les regles que abasten diverses files.

A l'apartat 15 aplicarem exactament aquestes tres idees al problema de l'última plaça.

L'estàndard SQL:1992 va definir quatre nivells, precisament en funció de quins fenòmens permeten. Aquesta és la taula canònica, la que cal saber-se:

Les dues últimes columnes mereixen una nota: el biaix d'escriptura no apareix a l'estàndard de 1992. Es va descriure després, quan l'snapshot isolation es va popularitzar i es va veure que complia la taula de l'estàndard fins a REPEATABLE READ i tot i així permetia anomalies. L'incloem perquè a la pràctica és el que més problemes causa avui.

I ara la taula que de debò importa quan treballes amb PostgreSQL:

Tres lectures d'aquesta taula:

• PostgreSQL és més estricte que l'estàndard en REPEATABLE READ: prohibeix els fantasmes de lectura, que l'estàndard permet.
• El nivell per omissió és READ COMMITTED, que permet tres dels quatre fenòmens. No és un descuit: és un equilibri deliberat entre correcció i rendiment, i significa que el nivell d'aïllament amb què treballa la teva aplicació avui, si ningú no l'ha tocat, és el segon més feble.
• El salt de garantia és entre REPEATABLE READ i SERIALIZABLE, i és el més car: SERIALIZABLE és l'únic que elimina el biaix d'escriptura, i ho fa avortant transaccions.

És en quan es pren la foto:

Aquella fila de "conflicte d'escriptura" és la que sorprèn en producció. Sota REPEATABLE READ:

La transacció d'A queda avortada i cal reintentar-la sencera. No és una fallada: és el gestor negant-se a produir una anomalia. Però si la teva aplicació no sap reintentar, l'usuari veu un error.

Tres maneres, de més local a més global:

Consultar el nivell actual:

També es pot canviar el valor per omissió del servidor a postgresql.conf amb default_transaction_isolation, però no és recomanable: fa que el comportament de l'aplicació depengui d'un fitxer que probablement no és al mateix repositori que el codi. El nivell és una decisió del codi, i s'ha de veure al codi.

Dos modificadors útils per a informes:

• READ ONLY impedeix escriptures i permet al gestor optimitzacions.
• DEFERRABLE, combinat amb SERIALIZABLE READ ONLY, fa que la transacció esperi fins a poder prendre una foto que garanteixi que mai no avortarà per serialització. És ideal per a un informe nocturn llarg: pot trigar una mica a arrencar, però no fallarà a la meitat després de vint minuts de feina.

Històricament, la primera resposta al problema de la concurrència va ser el bloqueig: si una transacció farà servir una dada, la reserva i els altres esperen.

La idea és intuïtiva: molts poden llegir alhora, però escriure requereix exclusivitat. La taula de compatibilitat es llegeix així: si A té un bloqueig S sobre la fila 3081 i B en demana un altre de S, B passa. Si B demana X, B espera.

Un bloqueig es pot prendre sobre unitats de mida diferent, i hi ha un compromís clar:

Alguns gestors practiquen l'escalada de bloqueigs: si una transacció acumula massa bloqueigs de fila, els substitueixen per un de taula per estalviar memòria, amb l'efecte col·lateral de bloquejar tothom. PostgreSQL no escala bloqueigs: desa la marca de bloqueig a la mateixa fila, així que en pot tenir milions sense gastar memòria del servidor. És una diferència pràctica notable.

El protocol que garanteix la serialitzabilitat mitjançant bloqueigs s'anomena bloqueig en dues fases (two-phase locking, 2PL), i la seva regla és d'una simplicitat enganyosa:

Una transacció té una fase de creixement, en què només pot adquirir bloqueigs, i una fase de decreixement, en què només els pot alliberar. Un cop ha alliberat el primer bloqueig, no en pot adquirir cap més.

A la pràctica, gairebé tots els gestors usen 2PL estricte: els bloqueigs exclusius es mantenen fins al COMMIT o el ROLLBACK. Això garanteix que ningú no llegeixi canvis no confirmats i simplifica la recuperació.

I explica la conseqüència operativa més gran de tot això: com més dura una transacció, més temps reté els seus bloqueigs i més gent espera. És la justificació tècnica de la regla "transaccions curtes" de la lliçó 06-01.

El preu del 2PL és que les transaccions s'esperen les unes a les altres, i d'aquí neixen els interbloqueigs de l'apartat 13.

Per completar el quadre, PostgreSQL té vuit modes de bloqueig a nivell de taula. No cal memoritzar-los, però sí saber que existeixen i que els pren tot sol:

Aquella última fila és la causa de la meitat de les caigudes de servei durant els desplegaments: un ALTER TABLE que espera darrere d'una consulta llarga, i tota la cua de peticions esperant darrere de l'ALTER TABLE, inclosos els SELECT que abans funcionaven. Veure els bloqueigs en curs:

El wait_event_type = Lock de la segona fila és la signatura inconfusible de "estic esperant un altre".

A més dels bloqueigs automàtics, SQL permet demanar-los a mà. És l'eina del bloqueig pessimista (apartat 14).

Bloqueja les files llegides com si s'haguessin de modificar. Qualsevol altra transacció que intenti modificar-les —o bloquejar-les— espera.

Ara la fila 3081 està reservada. Reproducció amb dues sessions:

I aquí hi ha la clau: quan B finalment obté la fila, la veu amb el valor nou. La seva comprovació de "està disponible?" ara és correcta, i rebutjarà el préstec. Això resol l'actualització perduda de l'apartat 3 de manera neta.

Bloqueig compartit: impedeix que altres modifiquin les files, però permet que altres les llegeixin amb FOR SHARE. S'usa quan necessites garantir que una fila no canviï mentre treballes amb dades relacionades, sense pretendre modificar-la tu.

Existeixen a més dues variants més suaus, FOR NO KEY UPDATE i FOR KEY SHARE, que PostgreSQL usa internament per a les claus foranes i que permeten més concurrència. Saber que existeixen n'hi ha prou.

Bloqueja la taula sencera. És contundent i gairebé sempre desproporcionat.

El seu ús legítim és el procés de manteniment nocturn que necessita una taula quieta, o el pas d'una migració que reorganitza dades. En el camí d'una operació d'usuari, mai.

Dos modificadors que canvien què passa quan la fila està ocupada:

NOWAIT serveix per donar una resposta ràpida a l'usuari en lloc de deixar-lo esperant:

L'aplicació tradueix aquell error a "un altre taulell està atenent aquest exemplar en aquest moment, torneu-ho a provar", que és infinitament millor que una pantalla congelada.

SKIP LOCKED és la base de les cues de treball. BiblioRed té un procés que envia els avisos de venciment; amb diversos treballadors en paral·lel, cadascun ha de prendre avisos diferents:

Cada treballador rep deu avisos que cap altre no està processant, sense esperes i sense duplicats. És un patró que substitueix una cua de missatges en molts sistemes de mida mitjana, i funciona sorprenentment bé.

El bloqueig té un defecte greu: si escriure requereix exclusivitat, els lectors destorben els escriptors i viceversa. L'informe mensual de direcció, que recorre tres milions de préstecs, bloquejaria el taulell durant tot el seu recorregut.

La solució que adopten PostgreSQL, Oracle i InnoDB és el control de concurrència multiversió.

MVCC. El gestor no sobreescriu les dades: cada modificació crea una nova versió de la fila. Cada transacció veu la versió que era visible en el moment de la seva foto. Així, els lectors mai no bloquegen els escriptors ni els escriptors els lectors.

Cada fila física porta dues columnes ocultes:

Les pots veure:

Ara un UPDATE, i tornem a mirar:

El ctid —l'adreça física de la fila— ha canviat de (12,7) a (12,41). La fila no s'ha modificat: se n'ha escrit una de nova en un altre lloc, i l'antiga ha quedat marcada amb xmax = 90118. Un UPDATE a PostgreSQL és, físicament, un INSERT més un marcatge de la versió anterior.

Quan una transacció llegeix, aplica una regla senzilla: una versió és visible si el seu xmin correspon a una transacció confirmada abans de la meva foto i el seu xmax és zero o correspon a una transacció no confirmada a la meva foto.

Aquest disseny té una conseqüència inevitable. Després d'un temps de funcionament, la taula exemplars conté la versió vigent de cada fila i totes les versions antigues que ja no veu ningú. S'anomenen versions mortes (dead tuples).

Les versions mortes costen de tres maneres:

1. Espai en disc, que creix sense parar.
2. Temps de lectura: un recorregut de la taula llegeix també les versions mortes i les descarta una a una.
3. Consum d'identificadors de transacció, que són finits.

Per veure-ho:

VACUUM recorre les taules i marca com a reutilitzable l'espai de les versions mortes que ja no pot veure cap transacció.

Variants:

En condicions normals no cal executar-lo a mà: el procés autovacuum ho fa tot sol. Però cal saber què passa si no s'executa:

• Inflament (bloat): la taula ocupa diverses vegades el que hauria d'ocupar i les consultes s'alenteixen progressivament. Una taula prestecs de 2 GB de dades útils pot arribar a ocupar 9 GB.
• Estadístiques velles, i amb elles plans d'execució dolents (06-03).
• Esgotament d'identificadors de transacció: PostgreSQL usa un comptador de 32 bits. Si VACUUM no "congela" a temps les files antigues, el servidor s'atura per complet per evitar la pèrdua de dades, amb un missatge inoblidable: database is not accepting commands to avoid wraparound data loss. És una de les poques maneres de deixar una base de dades PostgreSQL fora de servei per descuit operatiu.

L'enemic número u de VACUUM són les transaccions llargues. Una transacció oberta des de fa tres hores obliga a conservar totes les versions mortes creades des d'aleshores, perquè teòricament aquella transacció les podria necessitar. És una altra raó —la tercera ja— perquè les transaccions siguin curtes. Detectar culpables:

Aquell idle in transaction de tres hores és exactament el patró que la lliçó 06-01 demanava evitar amb idle_in_transaction_session_timeout.

Interbloqueig (deadlock). Dues o més transaccions s'esperen mútuament en un cicle: A espera un recurs que té B, i B espera un recurs que té A. Sense intervenció externa, esperarien per sempre.

El cas clàssic: dues transaccions que toquen les mateixes dues files en ordre invers. A BiblioRed, un trasllat d'exemplars entre les sucursals Centre i Nord, executat alhora en els dos sentits.

A la Sessió A:

El graf d'espera que el gestor ha construït:

Un cicle. Quan el detector troba un cicle, tria una transacció víctima —normalment la que menys ha treballat— i l'avorta. L'altra continua i acaba bé.

No comprova el cicle a cada espera: seria caríssim. Quan una transacció porta esperant més de deadlock_timeout (1 segon per omissió), i només aleshores, construeix el graf i busca cicles.

Conseqüència: un interbloqueig costa com a mínim un segon abans de resoldre's. En un sistema amb molts interbloqueigs, això sol ja és un problema de rendiment.

Per investigar-los, convé activar el registre dels bloqueigs que triguen:

La primera és la fonamental, i és fàcil d'aplicar. El trasllat d'exemplars reescrit:

Amb les dues sessions prenent els bloqueigs en el mateix ordre, el cicle és impossible: la segona espera la primera i acaba després. Hi ha espera, però no hi ha interbloqueig.

I sobre els reintents: un interbloqueig s'identifica pel SQLSTATE 40P01, i una fallada de serialització pel 40001. Tots dos són transitoris i reintentables. Una aplicació seriosa els captura i reintenta amb una espera creixent i una mica d'aleatorietat, fins a un màxim de tres o cinc intents.

Les dues estratègies generals per protegir una seqüència llegir-modificar-escriure. La diferència és en la suposició de partida.

És el patró estàndard. S'afegeix a la taula una columna que s'incrementa a cada modificació, i l'actualització només s'aplica si la versió continua sent la que es va llegir.

El flux, aplicat a l'edició d'un esdeveniment de BiblioRed des del panell de gestió:

Si ningú no ha modificat l'esdeveniment:

Si un altre gestor el va modificar mentre el nostre usuari pensava:

I aquell UPDATE 0 és tota la detecció. L'aplicació mostra "un altre usuari ha modificat aquest esdeveniment; reviseu els canvis i torneu a desar" en lloc de trepitjar silenciosament la feina aliena.

Aquest patró resol el problema de l'apartat 15.2 de la lliçó 06-01: no hi ha cap transacció oberta mentre l'humà decideix. La transacció dura el que dura un UPDATE.

Per automatitzar l'increment i que ningú no se n'oblidi, un activador —dels que vam veure breument a 05-04—:

Compte: amb l'activador, l'aplicació ja no ha d'escriure versio = versio + 1 al seu UPDATE, però sí que ha de continuar posant AND versio = ? al WHERE. La condició és la protecció; l'increment és només comptabilitat.

• Optimista si entre la lectura i l'escriptura hi ha un ésser humà, o si els conflictes són rars (edició de fitxes de socis, catalogació de materials, gestió d'esdeveniments). És el que s'usa per omissió a les aplicacions web.
• Pessimista si la contenció és alta i el conflicte és la norma (el préstec de l'últim exemplar en hora punta), o si refer la feina és car.

Tornem al problema de l'apartat 6 i resolguem-lo de debò. Estat: esdeveniment 51, 25 places, 24 ocupades, dos socis inscrivint-se alhora.

Hi ha tres enfocaments correctes. Els tres funcionen; no són equivalents.

La idea: convertir l'aforament en una dada d'una sola fila, amb una restricció declarada. Així el conflicte deixa de ser un fantasma i passa a ser un xoc sobre la mateixa fila, que el gestor sap resoldre.

I la inscripció:

Amb dues sessions simultànies:

A la Sessió B:

És impossible passar de 25. No importa el nivell d'aïllament, no importa el client, no importa si demà algú escriu un script que insereix a mà: la restricció és a la base de dades i es compleix sempre.

Dos detalls tècnics que convé entendre:

• L'UPDATE ... SET x = x + 1 no és llegir-modificar-escriure de l'aplicació: el gestor bloqueja la fila, llegeix el valor vigent i escriu. Sota READ COMMITTED, quan B es desbloqueja reavalua el seu UPDATE sobre la versió més recent, així que suma sobre 25 i no sobre 24.
• Sota REPEATABLE READ, en lloc de l'error de CHECK, B obtindria could not serialize access due to concurrent update. També correcte, però exigeix reintent.

Cost: la fila de l'esdeveniment es converteix en un punt de serialització. Totes les inscripcions a aquell esdeveniment es posen en cua sobre ella. Per a un club de lectura de 25 places és irrellevant; per vendre 60.000 entrades en dos minuts seria un coll d'ampolla.

La idea: bloquejar la fila de l'esdeveniment abans de comptar, de manera que només una transacció alhora pugui estar decidint sobre aquell esdeveniment.

La Sessió B espera al seu FOR UPDATE fins al COMMIT d'A, després compta 25, veu que no hi ha lloc i rebutja netament.

Avantatges: no requereix columnes noves ni desnormalització, i la lògica d'aforament (que pot ser complexa: acompanyants, places reservades per a escolars, llista d'espera) queda en un sol lloc.

Inconvenients: la protecció viu al codi de l'aplicació. Si un altre programa, un altre equip o un script de manteniment insereix a inscripcions sense prendre el bloqueig, la garantia desapareix sense que ningú se n'assabenti. I cal recordar bloquejar sempre la mateixa fila i en el mateix ordre respecte a altres bloqueigs, o tornen els interbloqueigs de l'apartat 13.

La idea: demanar al gestor la garantia completa i deixar que ell detecti el conflicte.

Amb les dues sessions entrellaçades com a l'apartat 6, la primera a confirmar té èxit i la segona rep, al COMMIT:

El motor SSI de PostgreSQL ha detectat que B va llegir un conjunt de files que A va modificar, i que el resultat combinat no correspon a cap execució en sèrie. Avorta B.

Avantatges: és l'única solució que resol tots els fenòmens alhora, inclòs el biaix d'escriptura de l'apartat 7. El codi de l'aplicació s'escriu com si no hi hagués concurrència, cosa que és enormement més fàcil de raonar.

Inconvenients:

• Obliga a implementar reintents. Sense ells, l'usuari veu un error críptic.
• Té cost: el gestor rastreja les dependències de lectura/escriptura de cada transacció.
• L'error arriba al COMMIT, quan ja s'ha fet tota la feina.
• I, com l'enfocament 2, no protegeix d'un script que usi un altre nivell d'aïllament.

Recomanació raonada per a BiblioRed: l'enfocament 1, complementat amb el 3 on calgui.

L'argument decisiu és el de la primera fila. Una regla de negoci tan dura com "no hi caben més de 25 persones" no pot dependre que tots els programes que toquin la base de dades, avui i d'aquí a cinc anys, recordin prendre un bloqueig o demanar un nivell d'aïllament. La restricció chk_aforament és a l'esquema, es compleix sempre, i a més es documenta sola: qui llegeixi la definició d'esdeveniments veurà la regla escrita. És el mateix argument de l'apartat 21 de la lliçó 04-04 sobre quines regles van a la base de dades.

El comptador places_ocupades_total és una desnormalització —cal mantenir-lo coherent amb inscripcions, amb les cauteles de 05-04, inclosa la comprovació periòdica que quadra—, i aquell és el seu preu. Es paga de gust.

I per a les regles que no es poden expressar com a restricció d'una fila —el "almenys un ponent" de l'apartat 7, que abasta diverses files d'una altra taula— s'usa l'enfocament 3 amb reintent, perquè és l'únic que les cobreix.

La concurrència és la diferència més gran entre SQLite i un servidor de bases de dades, i convé tenir-la clara per no triar malament.

El BEGIN IMMEDIATE mereix una nota: a SQLite, un BEGIN normal és diferit i no pren el bloqueig d'escriptura fins a la primera escriptura, cosa que produeix el clàssic "llegeixo, decideixo, escric i em diuen que la base està ocupada" amb la feina ja feta. Si la transacció escriurà, comença amb BEGIN IMMEDIATE i prendràs el bloqueig des del principi.

La conclusió pràctica: SQLite és excel·lent amb un escriptor i molts lectors. Per als quatre taulells de BiblioRed escrivint alhora, no. Per a l'aplicació d'inventari en una tauleta, perfecta.

Recordaràs de 03-04 que la consistència eventual és el preu de la disponibilitat i la tolerància a particions. El que aquí convé afegir és que la consistència eventual no elimina el problema de l'última plaça: l'agreuja.

Les eines canvien de nom però responen a les mateixes idees:

• El quòrum de lectura i escriptura (w + r > n) és una manera de comprar consistència en un sistema distribuït, anàloga a pujar el nivell d'aïllament.
• El writeConcern: {w: "majority"} de MongoDB és la petició explícita d'aquella garantia.
• Les operacions atòmiques de document ($inc, findAndModify amb condició) són l'equivalent de l'UPDATE ... SET x = x + 1 WHERE ... de l'enfocament 1: la comprovació i l'escriptura en una sola operació indivisible.

La lliçó de fons, que convé endur-se: la concurrència no és un problema de les bases de dades relacionals, és un problema de la realitat. Canviar de tecnologia no l'elimina; canvia les eines amb què s'afronta i, gairebé sempre, trasllada més responsabilitat al codi de l'aplicació.

Comptar abans d'inserir i creure que això protegeix. És l'error d'aquesta lliçó. Entre el SELECT count(*) i l'INSERT hi cap una transacció sencera. Si la regla és dura, expressa-la com a restricció.

No comprovar el nombre de files afectades. UPDATE ... WHERE estat='disponible' que torna UPDATE 0 està dient "algú se m'ha avançat". Ignorar-ho converteix una protecció perfecta en una decoració.

Usar REPEATABLE READ sense lògica de reintent. En READ COMMITTED un conflicte d'escriptura espera i continua; en REPEATABLE READ avorta. Pujar el nivell sense reintents canvia un error silenciós per un error sorollós, que és millor, però continua sent un error visible per a l'usuari.

Creure que SERIALIZABLE és "el nivell segur i prou". És segur i obliga a reintentar. Sense reintents no és més segur: només falla més.

Donar per fet que el nivell per omissió és el més estricte. És READ COMMITTED, el segon més feble. Comprova-ho amb SHOW transaction_isolation; abans de raonar sobre res.

Mantenir oberta una transacció mentre un humà decideix. Bloqueja files, impedeix el VACUUM i no arregla res que el bloqueig optimista no arregli millor.

Accedir a les mateixes files en ordre diferent en llocs diferents del codi. És la recepta de l'interbloqueig. Adopta un ordre canònic —per clau primària ascendent— i respecta'l a tot arreu.

Diagnosticar un interbloqueig com una fallada de la base de dades. No ho és: la base de dades ha fet exactament el que havia de fer en detectar-lo i trencar el cicle. La fallada és a l'ordre d'accés del codi.

Ignorar el VACUUM fins que fa mal. Vigila n_dead_tup i last_autovacuum a pg_stat_user_tables, i persegueix les transaccions idle in transaction llargues, que són la causa habitual que autovacuum no pugui fer la seva feina.

Usar VACUUM FULL en horari de servei. Pren un bloqueig ACCESS EXCLUSIVE: bloqueja fins i tot els SELECT. És una operació de finestra de manteniment.

Consell final: reprodueix sempre la fallada abans d'arreglar-la. Tots els fenòmens d'aquesta lliçó es provoquen amb dos terminals psql en menys d'un minut. Un error de concurrència que no saps reproduir és un error que no saps si has arreglat.

Per a cadascuna d'aquestes tres situacions reals de BiblioRed, indica quin fenomen dels cinc estudiats s'està produint, per què el nivell READ COMMITTED no l'evita, i quina és la defensa més adequada.

(a) L'informe mensual de direcció mostra "312 préstecs vençuts" al titular i, tres pàgines més avall, un desglossament per sucursal que suma 315.

(b) El procés nocturn que trasllada exemplars poc usats de Centre a Sud, i un altre que els porta de Sud a Centre, es queden penjats i un dels dos mor amb un error al cap d'un segon.

(c) La sucursal Est ha de conservar sempre almenys dos exemplars del material 907 (bibliografia escolar). En té tres. Dos bibliotecaris tramiten alhora un trasllat d'un exemplar cadascun a altres sucursals; tots dos comproven que en quedarien dos i tots dos confirmen. Al final en queda un.

Fent servir dos terminals psql, reprodueix el préstec doble de l'exemplar EJ-3081 de l'apartat 3. Després, reescriu l'operació de préstec de manera que sigui impossible que dos taulells prestin el mateix exemplar, sense canviar el nivell d'aïllament i sense afegir columnes noves. Escriu la transacció completa i indica què ha de comprovar l'aplicació i quin missatge ha de mostrar a l'operari.

La biblioteca vol admetre acompanyants als esdeveniments: un soci es pot inscriure amb fins a 3 acompanyants, i la columna inscripcions.places_ocupades recull el total de places que consumeix aquella inscripció (1 + acompanyants). A més, cada esdeveniment reserva 5 de les seves places per a grups escolars, que no poden ocupar els socis.

Amb aquestes dues regles noves, decideix quin dels tres enfocaments de l'apartat 15 faries servir, justifica-ho, i escriu el SQL de la solució.

Solució 1

(a) Lectura no repetible.

L'informe executa dues consultes diferents dins de la mateixa sessió. Sota READ COMMITTED, cada instrucció pren una foto nova, així que la segona consulta veu tres préstecs que van vèncer —o que es van registrar— entre l'una i l'altra. Cada xifra és correcta en el seu instant; juntes són incoherents.

Defensa: embolcallar l'informe en una transacció amb foto estable.

Per a un informe llarg, SERIALIZABLE READ ONLY DEFERRABLE és encara millor: garanteix que no avortarà a la meitat.

(b) Interbloqueig.

Dues transaccions que actualitzen el mateix conjunt d'exemplars en ordre invers. READ COMMITTED no hi té res a veure: els interbloqueigs són independents del nivell d'aïllament, perquè sorgeixen de l'ordre d'adquisició de bloqueigs, no de la visibilitat. L'error al cap d'un segon és exactament deadlock_timeout fent la seva feina.

Defensa: ordre canònic d'accés. Que els dos processos bloquegin els exemplars afectats amb SELECT ... FOR UPDATE ... ORDER BY exemplar_id al principi de la transacció, sigui quin sigui el sentit del trasllat. I reintent davant de 40P01 com a xarxa de seguretat.

(c) Biaix d'escriptura.

Els dos bibliotecaris llegeixen el mateix conjunt (els tres exemplars del material 907 a Est), cadascun decideix que en pot moure un de diferent, i cadascun escriu en una fila diferent. No hi ha conflicte de files, així que cap mecanisme basat en files no ho detecta. Ni READ COMMITTED ni REPEATABLE READ no ho eviten: és el fenomen de l'apartat 7.

Defensa, per ordre de preferència:

1. Materialitzar el conflicte: bloquejar una fila comuna abans de decidir, per exemple la del material o la de la sucursal.

   BEGIN;
   SELECT 1 FROM materials WHERE material_id = 907 FOR UPDATE;
   SELECT count(*) FROM exemplars
   WHERE material_id = 907 AND sucursal_id = 4 AND estat <> 'baixa';
   -- si count > 2, traslladar
   COMMIT;
   

2. SERIALIZABLE amb reintent, que ho detecta en general sense haver d'anticipar el cas.

Un CHECK no serveix aquí: la regla compta files d'una altra taula, i això queda fora del que una restricció declarativa de fila pot expressar.

Solució 2

La reproducció és la taula de l'apartat 3. La solució sense canviar nivell ni esquema, amb bloqueig pessimista sobre l'exemplar:

La segona sessió espera al FOR UPDATE fins al COMMIT de la primera, i aleshores llegeix prestat, així que avorta amb ROLLBACK.

Una variant encara millor, que no necessita ni tan sols que l'aplicació comprovi res abans:

A la sessió perdedora:

Què ha de comprovar l'aplicació: el nombre de files afectades per l'UPDATE. Si és 0, executar ROLLBACK.

Què ha de mostrar a l'operari: un missatge que reflecteixi la realitat i li digui què fer. Per exemple: "L'exemplar EJ-3081 ja no està disponible: un altre taulell l'ha prestat fa uns segons. Consulteu altres exemplars del mateix títol o creeu una reserva." Res de "Error de base de dades" ni de codis numèrics: qui és al taulell té un soci al davant.

Nota addicional: la segona variant és preferible a la primera perquè fa la comprovació i l'escriptura en una sola instrucció, sosté el bloqueig menys temps i no depèn que l'aplicació recordi comparar l'estat llegit.

Solució 3

Enfocament triat: l'1, la restricció a la base de dades, adaptat a les dues regles noves.

Justificació: les dues regles noves —places per acompanyants i reserva escolar— fan la lògica d'aforament més complexa, i per tant més fàcil d'implementar malament en algun punt del codi. Com més complicada és una regla, més arguments hi ha perquè visqui en un sol lloc i no a cada programa que insereixi inscripcions. A més, totes dues regles continuen expressant-se sobre valors d'una sola fila d'esdeveniments, que és just el que un CHECK pot comprovar.

La inscripció de la Marta amb dos acompanyants:

Si l'esdeveniment té 25 places, 5 reservades a escolars i ja n'hi ha 18 ocupades per socis, l'operació falla perquè 18 + 3 > 20:

Observacions sobre la solució:

• chk_places_ocupades garanteix que la columna desnormalitzada places_ocupades no es pugui desviar d'acompanyants. És la disciplina de 05-04 aplicada: una columna calculada ha de portar la seva comprovació al costat. De fet, aquí encara seria millor declarar-la com a columna generada (GENERATED ALWAYS AS (acompanyants + 1) STORED), com vam veure a 04-04.
• El nom de la restricció importa: chk_aforament_socis apareix literalment al missatge d'error, i l'aplicació el pot traduir a "No queden places suficients per a vostè i els seus acompanyants".
• Continua sent necessària una comprovació periòdica que places_ocupades_total quadri amb la suma real d'inscripcions confirmades, exactament com es va explicar a 05-04 per a les taules de resum.
• Si en el futur aparegués una regla que abastés diverses files d'altres taules —per exemple, "un soci no pot estar inscrit a dos esdeveniments que se solapin"—, aquella ja no cabria en un CHECK, i caldria anar a l'enfocament 3 amb SERIALIZABLE i reintent. Convé saber on és aquella frontera.

Aquesta lliçó ha demostrat una cosa incòmoda: el codi correcte per a un usuari pot ser codi trencat per a dos. Les tres avaries amb què vam començar —l'exemplar prestat dues vegades, les 26 persones en un club de 25 cadires, l'esdeveniment publicat sense ponents— no venien d'errors de programació en el sentit habitual. Venien d'una suposició implícita que el món real no respecta: que entre llegir i escriure no passa res.

Hem provocat els cinc fenòmens amb dos terminals i els hem entès de dins cap enfora. L'actualització perduda, la defensa més barata de la qual és posar la condició dins del mateix UPDATE i mirar quantes files s'han vist afectades. La lectura bruta, que a PostgreSQL simplement no pot passar. La lectura no repetible, que arruïna tots els informes de més d'una consulta i es cura amb una línia. La lectura fantasma, que ensenya la lliçó més important de totes: comptar abans d'inserir no protegeix de res, perquè no es pot bloquejar una fila que encara no existeix. I el biaix d'escriptura, que passa en nivells alts d'aïllament, no el detecta cap mecanisme basat en files, i només es resol amb SERIALIZABLE o materialitzant el conflicte a mà.

Hem vist la taula canònica dels quatre nivells i —més útil encara— la taula del que PostgreSQL fa en realitat: que no té READ UNCOMMITTED de debò, que el seu REPEATABLE READ és snapshot isolation i prohibeix fantasmes que l'estàndard permet, que el seu nivell per omissió és el segon més feble, i que el salt de garantia real és a SERIALIZABLE, que es paga amb transaccions avortades i, per tant, amb lògica de reintent obligatòria.

Després hem baixat a la maquinària: els bloqueigs compartits i exclusius, la granularitat i el fet que PostgreSQL bloqueja files i mai no escala; el bloqueig en dues fases, que explica per què una transacció llarga és un problema col·lectiu; els bloqueigs explícits, amb FOR UPDATE per protegir una lectura, NOWAIT per respondre ràpid en lloc de congelar la pantalla i SKIP LOCKED per repartir una cua de treball entre diversos processos. Hem obert el MVCC fins a veure el xmin, el xmax i el ctid canviant davant dels nostres ulls, i n'hem entès la factura: les versions mortes, el VACUUM que les neteja i el que passa quan no arriba a temps. Hem provocat un interbloqueig, llegit el seu missatge real, dibuixat el seu graf d'espera i après que la defensa fonamental cap en una frase: accedeix sempre a les files en el mateix ordre.

I hem resolt el problema de l'última plaça tres vegades, per descobrir que les tres solucions funcionen i només una protegeix de debò. El bloqueig pessimista i el SERIALIZABLE viuen al codi de l'aplicació, i per tant es perden tan bon punt un altre programa toqui la base de dades. La restricció chk_aforament viu a l'esquema, es compleix sempre i es documenta sola. És la mateixa conclusió a què vam arribar a 04-04 i a 05-04, i ja la podem enunciar com a principi: una regla que no es pot trencar ha d'estar on no es pugui saltar.

Amb això, el sistema de BiblioRed ja és correcte sota concurrència. Falta que sigui ràpid. Perquè hi ha un problema pendent des de la primera línia del mòdul: el llistat de préstecs vençuts per sucursal, aquell mateix que acabem de fer servir als exercicis, triga catorze segons des que la taula prestecs va superar els dos milions de files. I catorze segons en un taulell amb un soci al davant és una eternitat.

La lliçó 06-03, Índexs i Optimització de Consultes, és la que arregla això —i és la lliçó a la qual 05-04 ens va remetre explícitament quan vam dir que els índexs són el primer que cal provar abans de desnormalitzar. Veurem què és un índex i com un B-tree converteix milions de comparacions en quatre lectures; què costa un índex, perquè no són gratis i per això no s'indexa tot; els índexs únics, compostos, parcials i d'expressió, amb la regla del prefix més a l'esquerra que explica per què l'ordre de les columnes ho canvia tot; què s'indexa i què no, inclòs l'advertiment que PostgreSQL no indexa sol les claus foranes; com es llegeix un pla d'execució d'EXPLAIN ANALYZE línia a línia i què significa que les files estimades no s'assemblin a les reals; i el cas complet d'aquells catorze segons, amb el seu pla abans, el seu diagnòstic, l'índex que ho arregla i el seu pla després.