SQL do's and dont's
Uit Yapf
Inhoud |
Do's
Dont's
Haal nooit het volgende 'id' op met MAX(id)+1
1 Het probleem.
Records in een database hebben een primary-key en die key is meestal een integer-kolom zodat er een gewoon getal in staat. Elke record heeft een eigen uniek getal. Om dat getal automatisch te genereren gebruikt men in databases een sequence, of in mysql een auto_increment. Die dingen zijn er op gemaakt om door te tellen. Elke keer dat er een waarde wordt opgevraagd wordt "de vorige waarde +1" teruggegeven.
1.1 Foute conclusie #1, hoger id = nieuwe record.
Door de manier van genereren zijn de waarden van achtereenvolgend ingevoerde records meestal oplopend en daaruit trekken veel mensen de foute conclusie dat het hoogste id altijd bij het nieuwste record hoort.
Waarom is dat een foute gedachte? Databases zijn multi-user, meerdere processen kunnen er tegelijk bewerkingen op uitvoeren. Dat betekent dat de handelingen die de processen uitvoeren door elkaar heen uitgevoerd zullen worden. Dat betekent weer dat dit kan gebeuren:
- A vraagt nieuw ID op, krijgt 5
- A krijgt een vertraging, om welke reden dan ook moet hij 0.01 seconde wachten.
- B vraagt nieuw ID op, krijgt 6
- A is nog aan het wachten en B is klaar om zijn record in te voeren en doet dat.
- A is klaar met wachten en voert zijn record in.
Als je nu met 'MAX(id)' kijkt welk id het hoogst is, dan is dat '6', en dat is record B. En daar heb je een probleem want het laatste record is ingevoerd door A, met waarde '5'.
1.2 Foute conclusie #2, Het nieuwe id zal MAX(id)+1 zijn.
Omdat de getallen oplopend lijken volgt vaak de foute conclusie dat het volgende getal te voorspellen is met MAX(id).
Dat is fout om dezelfde reden als foute conclusie #1, er zijn meerdere processen actief. In dit geval krijg je bijvoorbeeld:
- A vraagt max(id)+1 op, krijgt 5
- B vraagt max(id)+1 op, krijgt ook 5 want er is nog geen waarde '5' ingevoerd!
- A voert zijn record in, '5' bestaat nu in de tabel.
- B voert zijn record in krijgt een foutmelding omdat '5' al bestaat.
Hoe doe je dit dan goed?
Veilig maar zeer ongewenst: locken.
Een methode die werkt, maar die zeer ongewenst is is om de tabel een exclusief lock te geven voordat je max+1 doet en dat lock pas weer op te heffen na de insert. Tijdens zo'n lock mag er maar één process tegelijk in de tabel lezen en/of schrijven. Dit betekent echter ook dat de processen die geen max+1 willen doen maar gewoon records willen lezen er ook niet bij kunnen zolang er een max+1 process loopt. Op drukke databases is dit dus heel erg niet wat je wilt.
Goed: sequences.
De juiste manier om unieke waarden voor het id te genereren is door het gebruik van een sequence. Dit is een aparte entiteit in je database die speciaal gemaakt is om unieke getallen te genereren. Je kunt ze in PostgreSQL uitlezen met de nextval() functie. Voordat je insert draai je een query die de nextval van de sequence ophaalt en dat getal gebruik je in de insert. Dat lijkt dus erg veel op de max(id)+1 methode, maar de sequence zal zelf zorgen dat hij nooit twee keer hetzelfde getal uitdeelt. Deze methode is dus veilig, ook als je het verkregen getal niet in een record gebruikt.
Bijna goed: MySQL's auto_increment
In MySQL moet het weer anders (het zal eens niet). In MySQL moet je de kolom definieren als 'auto_increment' en dan zal MySQL zelf tijdens het inserten een nieuwe waarde aanmaken. Die waarde kun je achteraf (en alleen achteraf) ophalen met mysql_insert_id()
Q: Maar wat is nou de kans dat dat gebeurt, het gaat allemaal zo snel!
Persoonlijk vind ik geen enkele kans op een fout klein genoeg om hem te laten zitten, maar als je hem wilt laten zitten, realiseer je dan goed wat de gevolgen kunnen zijn. Als door deze fout een forumpost niet op de juiste volgorde getoond wordt zal dat niemand boeien. Als door deze fout een factuur bij de verkeerde klant terecht komt is dat al een stuk minder leuk. Als je door deze fout een nierdialise apparaat met het verkeerde middel schoon laat maken dan gaat er iemand dood.