Identificateurs autoincrémentés: comment éviter les trous

Les colonnes autoincrémentées dans une base de données (je fais référence au type de donnée serial en PostgreSQL, ou encore au modificateur de type auto_increment en MySQL) sont très utiles pour obtenir un numéro unique lorsqu'on ajoute une rangée dans une table. Cependant, j'ai appris récemment qu'annuler une transaction SQL (abort ou rollback) ne remet pas le compteur à ce qu'il était avant de débuter la transaction.

Imaginez que vous utilisez une colonne autoincrémentée pour assigner un numéro de facture. La dernière facture que vous avez créée porte le numéro 42. Vous débutez votre transaction SQL avec begin puis vous ajoutez une nouvelle facture: le numéro 43 lui est automatiquement attribué. Disons qu'au moment d'ajuster les tables d'inventaire, un problème survient et vous annulez la transaction SQL avec rollback. Le SGBD effacera toute trace de votre facture comme vous le souhaitez, mais le compteur demeurera à 43. La prochaine facture portera donc le numéro 44 et vous aurez un trou entre la facture 42 et 44, ce qui est guère apprécié des comptables!

Cela se produit avec la plupart des SGBD. Comment se fait-il alors que personne n'ait corrigé ce bogue? Parce que ce n'en est pas un! Pour reprendre l'exemple ci-haut, si un deuxième processus ajoutait une facture (numéro 44) entre le moment où la facture 43 est créée et le moment où la transaction SQL de la facture 43 est annulée, ce serait impensable de défaire aussi la facture 44 sous prétexte que son numéro devrait maintenant être 43...

Alors que faire? Ne pas utiliser de colonne autoincrémentée, malheureusement. Avant de créer une facture, il faut verrouiller la table des factures pour empêcher qu'un autre processus ne la lise (ouch!), faire un "select max(facture_id) + 1" pour obtenir le prochain numéro de facture et ajouter une facture en spécifiant ce numéro. Et bien sûr, ne pas oublier de libérer le verrou sur la table le plus tôt possible!