Acasă/Metode

Inginerie · Baze de date

Capcane SQLite și de baze de date în aplicații mici de producție

Capcanele de baze de date care lovesc aplicațiile mici nu țin de scară: o citire lungă pe SQLite blochează toate scrierile, un filtru pe o coloană criptată nu găsește nimic fără să dea eroare, DISTINCT crapă pe un tabel cu coloană json, commit() și rollback() expiră obiectele ORM chiar și în handlerul de eroare, iar un epsilon fix e prea mic pentru o coloană float32. Toate trec un test rapid și cedează în producție, de obicei fără o eroare clară.

Actualizat 2026-10-11

Ce capcane de baze de date lovesc aplicațiile mici

Cele mai frecvente capcane de baze de date într-o aplicație mică de producție nu țin de scară. O citire lungă pe SQLite blochează toate scrierile, un WHERE pe o coloană criptată nu găsește nimic fără să dea eroare, SELECT DISTINCT crapă pe un tabel care are o coloană json, obiectele ORM expiră după commit() și rollback() (inclusiv în handlerul de eroare), iar o gardă cu epsilon fix e prea mică pentru o coloană float32. Fiecare trece un test rapid și cedează în producție, de obicei fără o eroare clară.

Rulăm aplicații mici pe SQLite și Postgres, în spatele unor servicii Python care folosesc SQLAlchemy. Fiecare capcană de mai jos ne-a costat un incident sau un raport greșit. Pentru fiecare dăm cauza, cum s-a văzut și regula pe care o urmăm acum.

Capcana 1: o interogare „doar citire” pe SQLite poate opri producția

Pe SQLite, în modul clasic cu jurnal de rollback, un cititor ține un lacăt partajat cât rulează interogarea, iar niciun scriitor nu poate face commit până nu termină cititorul. Pe Postgres un SELECT lung nu blochează scriitorii; pe SQLite în afara modului WAL (jurnal cu scriere în avans) îi blochează de fiecare dată.

Am aflat asta într-o investigație cu mandat explicit de „doar citire”. Doi dintre agenții noștri AI au scris un script de verificare și au rulat o interogare lungă pe fișierul live al bazei unei aplicații de depozit. Baza nu era în WAL. Cât a rulat interogarea, endpointul prin care o stație din depozit își revendică un lot de lucru a răspuns HTTP 500 „database is locked”, iar stația nu și-a putut încărca lotul. O altă sesiune a observat, a oprit procesul, a făcut o copie de siguranță și a trecut baza definitiv în WAL.

Regula de acum. Nu investigăm niciodată pe un fișier SQLite live din producție. Facem o copie consistentă cu API-ul de backup online (sqlite3 live.db ".backup copie.db" sau connection.backup() în Python) și lucrăm pe copie; același API ne face și copiile de noapte, pentru că o simplă copiere a fișierului nu e garantat consistentă cât baza primește scrieri. Dacă trebuie neapărat citit fișierul live, îl deschidem doar pentru citire (?mode=ro) și verificăm întâi PRAGMA journal_mode; dacă nu e wal, ne oprim și cerem voie.

Capcana 2: o coloană criptată nu se filtrează din SQL

Când o coloană e criptată în aplicație cu un vector de inițializare (IV) aleator, același text în clar dă de fiecare dată alt text cifrat. Un filtru SQL precum WHERE zip = '-' compară cu textul cifrat și nu se poate potrivi niciodată. Interogarea nu crapă; întoarce zero rânduri, iar zero rânduri arată exact ca „nu există asemenea cazuri”.

Am lărgit un selector ca să prindă codurile poștale de umplutură. Sintaxa a trecut, rularea a trecut și a raportat 21 de comenzi în plus. Filtrul nu putea funcționa niciodată: codul poștal era criptat în 19.390 din 19.549 de rânduri pe 7 zile, iar comenzile în plus erau doar rândurile în care coloana era NULL. Am confirmat că criptarea e aleatoare, nu deterministă, verificând că cele mai frecvente valori cifrate apăreau fiecare o singură dată.

Regula de acum. Înainte de orice WHERE pe un câmp de adresă, telefon sau email, verificăm în model dacă tipul coloanei e criptat. Dacă e, filtrăm în codul aplicației după ce ORM-ul a decriptat valoarea sau folosim un index orb, acolo unde există: o coloană separată cu un hash cu cheie al valorii normalizate, care permite căutări de egalitate fără să dezvăluie valoarea. Sau normalizăm la intrare, cât valoarea e încă în clar: regula „un cod poștal fără nicio cifră e umplutură” stă acum în handlerul de webhook.

Capcana 3: DISTINCT pe o entitate cu coloană json crapă

În Postgres, SELECT DISTINCT are nevoie de un operator de egalitate pentru fiecare coloană selectată, iar tipul json nu are unul (jsonb are). O interogare ORM care selectează o entitate întreagă selectează toate coloanele, deci select(Order).join(LineItem).where(...).distinct() crapă cu could not identify an equality operator for type json ori de câte ori tabelul de comenzi are vreo coloană json, indiferent ce scrie în WHERE.

Mesajul nu numește nici coloana, nici DISTINCT, iar join-ul pare vinovatul evident, fiindcă un produs poate apărea pe mai multe linii ale aceleiași comenzi și tocmai de aceea ai cerut rânduri distincte. Nici .unique() pe rezultat nu ajută: el elimină dublurile în Python, după ce SQL-ul a crăpat deja.

Regula de acum. Selectăm întâi id-urile, pentru că DISTINCT pe o singură coloană scalară merge. Eliminăm dublurile în Python păstrând ordinea (dict.fromkeys(ids)), apoi încărcăm entitățile după id, în loturi, cu IN (...), cu relațiile de care avem nevoie încărcate din prima.

Capcana 4: commit() și rollback() îți expiră obiectele, chiar și în handlerul de eroare

În SQLAlchemy, implicit, toate obiectele dintr-o sesiune expiră după commit() și după rollback(), iar următorul acces la un atribut le reîncarcă din bază. Într-o sesiune asincronă reîncărcarea implicită nu e permisă și aruncă MissingGreenlet. O buclă care face commit după fiecare element merge deci la primul și cade de la al doilea, chiar dacă relațiile au fost încărcate din prima.

Partea mai rea e handlerul de eroare. Bucla noastră de curățenie scria în log numele comenzii din blocul except. Atributul expirase și el, așa că handlerul arunca o a doua excepție chiar din except și oprea toată rularea. În loguri apăreau două traceback-uri lipite, fără niciun mesaj de-al nostru, iar efectul era opusul scopului funcției: tichetele pe care trebuia să le închidă rămâneau deschise la nesfârșit.

Aceeași capcană a revenit cu rollback(). Un job care marchează ca plătite comenzile cu plata la livrare a dat de o eroare așteptată, a făcut rollback, apoi a citit numele comenzii pentru log. O singură sesiune era folosită pentru toate magazinele din buclă, așa că eroarea le-a oprit pe toate. Timp de 4 zile nicio comandă cu plata la livrare n-a fost marcată plătită, nicăieri în flotă, cu un singur avertisment pe rulare.

Regula de acum. Într-o buclă care face commit pe fiecare element, nu ținem obiecte ORM peste commit. Selectăm de la început id-urile și valorile simple de care avem nevoie în log, reîncărcăm fiecare obiect proaspăt în iterație, iar în except folosim doar acele valori simple, niciodată atribute ale obiectului care tocmai a eșuat. Scriem cu UPDATE ... WHERE id unde se poate și dăm fiecărei unități independente (un magazin, un client al platformei) propria sesiune, ca o eroare să nu le oprească pe celelalte.

Capcana 5: un epsilon fix calibrat pentru alt tip de float

O gardă de rotunjire precum ceil(x - 1e-9) e corectă pentru o valoare float64, a cărei eroare în jurul lui unu e de ordinul 2e-16. E greșită pentru o valoare citită dintr-o coloană Postgres de tip real, care e float32: 0.1 se citește ca 0.10000000149011612, zece bucăți dau 1.0000000149, iar eroarea de 1.5e-8 e de 15 ori mai mare decât garda. Rotunjirea în sus dă atunci 2, nu unu.

Am găsit-o când o comandă de o singură cutie a plecat cu două etichete de transport. Densitățile de colete pe bucată stăteau într-o coloană real, iar comentariul gărzii spunea, corect, că există ca zece ori 0.1 să dea unu. Era adevărat pentru literalul din test și fals pentru valoarea care ajungea de fapt acolo. Măsurat pe harta reală, 939 de densități și cantități de la 1 la 60, 849 de perechi pe 136 de produse ieșeau cu un colet în plus, toate la cantitățile rotunde la care se vând seturile, adică exact acolo unde se comandă. Fiecare însemna o cutie goală trimisă prin curier.

Garda trebuie să fie și relativă. Un prag fix cedează pe măsură ce numărul crește: 0.3 ca float32 greșea cu 6e-7 la 50 de bucăți, deci 15.0000006 devenea 16. Rotunjirea la șase zecimale cedează la fel. Acum luăm toleranța din eroarea sursei (o coloană real sau float4, un numpy.float32, JSON dintr-un sistem scris în C), o facem relativă, de exemplu max(1e-9, abs(x) * 1e-6), și păstrăm o singură gardă comună: aceeași gardă exista în trei copii și doar una fusese reparată când am găsit defectul.

Unde am greșit

De fiecare dată ceva ne-a liniștit: un mandat de „doar citire”, o rulare care a întors rânduri, un comentariu care explica garda, o încărcare din prima care ar fi trebuit să facă atributul disponibil. Tocmai liniștea aceea ne-a oprit să verificăm. O interogare care întoarce zero rânduri, sau câteva, nu dovedește că filtrul merge; rulezi filtrul pe un caz despre care știi sigur că există.

Prima noastră verificare a reparației pentru float a rescris rotunjirea în interiorul testului și a confirmat o reparație care încă greșea în 10 cazuri. Un test trebuie să execute funcția din fișierul care pleacă în producție, nu o copie a ei.

Limitele acestor reguli

Regulile vin dintr-o singură combinație: SQLite, Postgres și SQLAlchemy cu sesiuni asincrone. Alte ORM-uri expiră obiectele altfel, alte baze de date blochează altfel, deci verifici comportamentul celor pe care le folosești.

Listă de verificare

1. SQLite. Investighezi pe o copie făcută cu API-ul de backup; verifici PRAGMA journal_mode înainte să atingi un fișier live.

2. Criptare. Verifici tipul coloanei înainte de orice WHERE pe date personale; filtrezi după decriptare, printr-un index orb sau la intrare.

3. DISTINCT. Selectezi id-uri, elimini dublurile în Python, încarci entitățile în loturi.

4. Sesiuni. Fără obiecte ORM peste commit() sau rollback(); doar valori simple în except; câte o sesiune pe unitate independentă.

5. Float. Iei toleranța din tipul sursei și o faci relativă; o singură gardă comună, testată din fișierul care pleacă în producție.

Întrebări și răspunsuri

De ce dă SQLite „database is locked” la o interogare doar de citire?

În modul implicit al SQLite, cu jurnal de rollback, un cititor ține un lacăt partajat cât rulează interogarea, iar scriitorii nu pot face commit până nu e eliberat. O interogare lungă de citire pe un fișier din producție blochează deci toate scrierile, iar aplicația răspunde „database is locked”. Modul WAL lasă cititorii și un scriitor să lucreze simultan, dar investigația e tot mai sigură pe o copie făcută cu API-ul de backup online.

De ce nu întoarce niciun rând un WHERE pe o coloană criptată?

Dacă aplicația criptează o coloană cu un vector de inițializare aleator, aceeași valoare e stocată de fiecare dată ca alt text cifrat, așa că o comparație cu textul în clar din SQL nu se poate potrivi niciodată. Interogarea nu crapă; întoarce zero rânduri, care arată ca „nu există cazuri”. Filtrezi în codul aplicației după decriptare, folosești un index orb cu hash-ul cu cheie al valorii normalizate sau normalizezi valoarea la intrare.

Cum rezolv „could not identify an equality operator for type json” la DISTINCT?

Postgres nu definește operator de egalitate pentru tipul json, iar SELECT DISTINCT compară toate coloanele selectate, deci DISTINCT pe o entitate întreagă care are o coloană json crapă. Selectezi cu DISTINCT doar id-urile, ceea ce merge pe o coloană scalară, elimini dublurile în Python și încarci apoi entitățile după id, în loturi. Metoda unique() pe rezultat nu ajută, pentru că SQL-ul a crăpat deja.

De ce aruncă SQLAlchemy MissingGreenlet după commit() într-o buclă?

Implicit, SQLAlchemy expiră toate obiectele din sesiune după commit() și rollback(). Într-o sesiune asincronă, următorul acces la un atribut ar cere o reîncărcare implicită, care nu e permisă, așa că apare MissingGreenlet. Selectezi id-urile și valorile simple înainte de buclă, reîncarci fiecare obiect în iterație și folosești doar valori simple în blocul except, altfel chiar handlerul de eroare poate arunca și opri rularea.

Ce epsilon folosesc când rotunjesc valori dintr-o coloană float32?

Iei toleranța din tipul sursei, nu din literalul din test. O coloană Postgres de tip real e float32, deci 0.1 se citește cu o eroare de ordinul 1.5e-8, mult peste un epsilon de 1e-9 ales pentru float64. Folosești o toleranță relativă, de exemplu max(1e-9, abs(x) * 1e-6), pentru că orice prag fix cedează când valoarea crește, și păstrezi o singură funcție de gardă comună.

Toate metodele