Données relationnelles, SQL et transactions
Un système de commandes doit répondre à deux sortes de questions : combien un client a dépensé, et combien d’articles restent lorsque deux requêtes diminuent le stock en même temps. La première exige de relier correctement les enregistrements des tables. La seconde exige des mises à jour qui restent correctes en présence d’accès concurrents. Le schéma définit ce que représente un enregistrement, les contraintes imposent des états valides, les index facilitent la recherche et les transactions valident ensemble les modifications liées.
Le tutoriel PostgreSQL offre un point de départ pour SQL. Une petite boutique fictive permet de relier ces notions à partir d’un même jeu de données.
Lignes, clés, relations et schéma
Dans les concepts relationnels de PostgreSQL, une table comporte des lignes et des colonnes nommées et typées. Ici, une ligne de customers représente un client ; une ligne de orders représente une commande. Le schéma des données précise aussi quelles colonnes acceptent des valeurs NULL, comment identifier les enregistrements et quelles relations doivent être respectées.
Une clé primaire identifie une ligne de manière unique et ne peut pas être NULL. Un nom peut changer ou appartenir à plusieurs personnes : le client est donc identifié par customer_id. Dans une commande, customer_id désigne un client grâce à une clé étrangère. Cet exemple exige que chaque commande appartienne à un seul client existant ; un client peut avoir zéro, une ou plusieurs commandes. La relation des clients vers les commandes est de type un-à-plusieurs.
Les montants sont des entiers exprimés en centimes : 5000 centimes valent 50,00 unités monétaires. Enregistrez le code suivant dans relational_demo.py, ajoutez les blocs suivants au même fichier dans l’ordre de la page, puis lancez python3 relational_demo.py. Le module Python sqlite3 exécute ce SQL sans serveur de base de données séparé ; isolation_level=None permet ici de contrôler explicitement les transactions. Dans SQLite, il faut activer la vérification des clés étrangères sur la connexion ; le code vérifie cette activation. La base de l’exemple reste en mémoire.
Dans une table SQLite ordinaire, la déclaration INTEGER détermine une affinité de type : la colonne peut encore contenir une valeur fractionnaire ou du texte non numérique. Les contraintes sur les montants, le stock et les quantités réservées utilisent donc la fonction SQLite typeof pour exiger que les valeurs stockées soient des entiers, puis vérifient qu’elles sont positives ou non négatives.
import sqlite3
# Explicit transaction control; the database lives only in memory.
db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("PRAGMA foreign_keys = ON")
assert db.execute("PRAGMA foreign_keys").fetchone() == (1,)
db.executescript("""
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
amount_cents INTEGER NOT NULL
CHECK (typeof(amount_cents) = 'integer' AND amount_cents > 0)
);
INSERT INTO customers VALUES
(1, 'Ada', 'ada@example.com'),
(2, 'Lin', NULL),
(3, 'Sam', 'sam@example.com');
INSERT INTO orders VALUES
(101, 1, 5000), (102, 1, 3000),
(103, 2, 2000), (104, 2, 1000);
""")
print("customers:", db.execute("SELECT COUNT(*) FROM customers").fetchone()[0])
print("orders:", db.execute("SELECT COUNT(*) FROM orders").fetchone()[0])
customers: 3
orders: 4
Ada a deux commandes de 5000 et 3000 centimes ; Lin en a deux de 2000 et 1000 centimes. Sam n’a aucune commande. Lin n’a pas fourni d’adresse électronique, représentée par NULL. Ces données permettent de calculer les totaux et d’observer ce qui se passe en l’absence de correspondance.
Requêtes, jointures et agrégation
SELECT et WHERE choisissent respectivement les colonnes renvoyées et les lignes qui satisfont une condition. Une jointure associe les lignes de deux tables selon une condition. Une jointure interne conserve les correspondances ; une jointure gauche conserve aussi les lignes de gauche sans correspondance, avec NULL dans les colonnes de droite.
Pour afficher le nombre de commandes et le total dépensé par chaque client, partez des clients et faites une jointure gauche afin de conserver Sam. GROUP BY rassemble les enregistrements de chaque client et SUM additionne les montants. Selon les règles des fonctions d’agrégation, COUNT(*) compte les lignes, tandis que COUNT(o.order_id) compte seulement les identifiants de commande non nuls. Après la jointure gauche, Sam possède une ligne sans commande : ces comptes valent donc respectivement 1 et 0. Sans montant non nul à additionner, SUM renvoie NULL ; la requête utilise COALESCE pour afficher 0.
query = """
SELECT c.customer_id, c.name,
COUNT(*) AS joined_rows,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.amount_cents), 0) AS total_cents
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
"""
for row in db.execute(query):
print(row)
print("orders >= 3000:", db.execute(
"SELECT order_id FROM orders WHERE amount_cents >= 3000 ORDER BY order_id"
).fetchall())
(1, 'Ada', 2, 2, 8000)
(2, 'Lin', 2, 2, 3000)
(3, 'Sam', 1, 0, 0)
orders >= 3000: [(101,), (102,)]
Les colonnes donnent l’identifiant du client, son nom, le nombre de lignes après jointure, le nombre de commandes et le total en centimes. Ada a dépensé 5000 + 3000 = 8000 centimes ; Lin, 2000 + 1000 = 3000. Le total de la boutique est 11000 centimes, soit 110,00 unités monétaires. La dernière requête sélectionne les commandes 101 et 102, d’au moins 3000 centimes. WHERE filtre les lignes avant l’agrégation ; HAVING filtre les groupes après. Pour retenir les clients dont le total atteint 4000 centimes, appliquez HAVING au total : seule Ada est retenue ici. Utilisez ORDER BY si l’ordre des résultats doit être stable.
NULL et cardinalité des jointures
Les règles de comparaison avec NULL introduisent un troisième résultat logique : inconnu. Une comparaison ordinaire dont l’un des opérandes est nul donne inconnu ; WHERE conserve uniquement les résultats vrais. Ainsi, email = NULL ne trouve aucune adresse manquante : il faut email IS NULL. De même, deux clés nulles ne correspondent pas dans une jointure par égalité.
La cardinalité d’une jointure est le nombre de lignes qu’elle produit. Les identifiants sont uniques dans customers : une commande peut donc correspondre à un seul client au maximum. Mais si la table jointe contient plusieurs enregistrements par client, chaque commande peut apparaître plusieurs fois. La requête suivante construit deux étiquettes promotionnelles pour Ada. Chacune de ses deux commandes correspond aux deux étiquettes : quatre lignes sont produites et le total atteint 16000 centimes.
print("= NULL:", db.execute(
"SELECT COUNT(*) FROM customers WHERE email = NULL"
).fetchone()[0])
print("IS NULL:", db.execute(
"SELECT COUNT(*) FROM customers WHERE email IS NULL"
).fetchone()[0])
print("empty SUM:", db.execute(
"SELECT SUM(amount_cents) FROM orders WHERE customer_id = 3"
).fetchone()[0])
print("multiplied:", db.execute("""
WITH promotions(customer_id, label) AS (
VALUES (1, 'spring'), (1, 'member')
)
SELECT COUNT(*), SUM(o.amount_cents)
FROM orders AS o
JOIN promotions AS p ON p.customer_id = o.customer_id;
""").fetchone())
= NULL: 0
IS NULL: 1
empty SUM: None
multiplied: (4, 16000)
None est la représentation de SQL NULL affichée par Python. Le vrai total des commandes d’Ada reste 8000 centimes. Les 16000 centimes correspondent à une somme après dédoublement des commandes par étiquette promotionnelle ; ils ne mesurent pas directement ses achats. Déterminez d’abord si une ligne du résultat doit représenter un client, une commande ou une étiquette, puis choisissez la jointure ou agrégez en amont. SUM(DISTINCT amount_cents) n’est pas une correction générale : deux commandes distinctes peuvent avoir le même montant. Pour déclarer les relations de jointure et repérer les clés sans correspondance dans des DataFrames, voir Combiner des DataFrames ; leur traitement des clés nulles diffère de celui de SQL.
Les contraintes comme invariants exécutables
Les contraintes inscrivent dans la définition des tables les règles qui doivent rester vraies. Ici, la clé primaire des commandes interdit les identifiants en double, la clé étrangère interdit les clients inexistants, NOT NULL exige un montant et le CHECK exige que le montant stocké soit un entier positif. Ces règles s’appliquent aux différentes voies ordinaires d’écriture, sans que chaque appelant ait à refaire ses propres vérifications.
for label, values in [
("foreign key", (105, 99, 1000)),
("positive amount", (106, 1, -1000)),
("required amount", (107, 1, None)),
("unique order", (101, 1, 1000)),
]:
try:
db.execute("INSERT INTO orders VALUES (?, ?, ?)", values)
except sqlite3.IntegrityError:
print(label, "rejected")
print("orders:", db.execute("SELECT COUNT(*) FROM orders").fetchone()[0])
foreign key rejected
positive amount rejected
required amount rejected
unique order rejected
orders: 4
Les quatre insertions sont refusées et les quatre commandes initiales restent présentes. La base applique les règles au lieu de les laisser dans des commentaires.
La documentation des contraintes PostgreSQL précise une condition : CHECK accepte un résultat vrai ou NULL. Une vérification de valeur positive doit donc être accompagnée de NOT NULL. Par défaut, UNIQUE dans PostgreSQL autorise plusieurs valeurs nulles ; si chaque client doit avoir une adresse unique, exigez aussi qu’elle soit non nulle. Les règles entre plusieurs lignes demandent une conception adaptée : un CHECK ordinaire ne peut pas garantir en permanence que la somme de toutes les commandes reste sous un plafond. Il faut des contraintes, des transactions ou un verrouillage de l’état concerné qui couvrent cette règle.
Index : compromis entre lecture et écriture
Un index conserve une structure de recherche supplémentaire et occupe un espace de stockage distinct. Il peut accélérer les filtres et les jointures, mais les écritures doivent le maintenir à jour avec sa table. Créons un index pour retrouver les commandes d’un client :
db.execute("CREATE INDEX orders_customer_idx ON orders(customer_id)")
print("index exists:", db.execute(
"SELECT COUNT(*) FROM sqlite_master WHERE type = 'index' AND name = ?",
("orders_customer_idx",),
).fetchone()[0] == 1)
index exists: True
Cette sortie confirme seulement la création de l’index. Quatre commandes ne démontrent aucun gain de performance ; la documentation sur l’utilisation des index explique pourquoi un petit jeu de données peut favoriser un parcours séquentiel. Choisissez les index à partir des filtres réels, des conditions de jointure et des plans de requête, puis évaluez leur coût à l’écriture. Un index ordinaire n’exige pas l’unicité des clés et ne transforme pas deux mises à jour indépendantes en une transaction.
Transactions, isolation et mise à jour perdue
L’atomicité d’une transaction signifie que les modifications liées des lignes sont validées ensemble, ou toutes annulées lors d’une annulation complète de la transaction. BEGIN démarre la transaction, COMMIT la valide et ROLLBACK l’annule. Cela concerne les lignes des tables ; les compteurs de séquence PostgreSQL ne reviennent pas à leur valeur précédente si la transaction échoue. L’atomicité porte sur la réussite ou l’échec d’un ensemble de modifications ; l’isolation détermine ce que voient les transactions concurrentes et comment leurs conflits sont traités.
Le stock initial vaut 10. Les requêtes A et B achètent chacune un article : elles lisent d’abord la quantité, puis soustraient 1 dans l’application. L’ordre suivant est possible avec Read Committed dans PostgreSQL, même si chacune place sa lecture et son écriture dans une transaction :
Deux achats devraient laisser 10 - 1 - 1 = 8 articles, mais il en reste 9. B écrase le résultat d’A : c’est une mise à jour perdue. La valeur respecte toujours quantity >= 0 ; la contrainte sur la ligne ne détecte donc pas cette erreur métier.
Le bloc suivant exécute successivement, sur une même connexion, deux requêtes ayant conservé une ancienne valeur. Il reproduit l’écrasement, puis le compare à des mises à jour relatives calculées dans la base. C’est une démonstration déterministe de l’utilisation de valeurs périmées, pas une expérience d’isolation avec des connexions concurrentes.
db.execute("""
CREATE TABLE stock (
sku INTEGER PRIMARY KEY,
quantity INTEGER NOT NULL
CHECK (typeof(quantity) = 'integer' AND quantity >= 0)
)
""")
db.execute("INSERT INTO stock VALUES (1, 10)")
a_read = db.execute("SELECT quantity FROM stock WHERE sku = 1").fetchone()[0]
b_read = db.execute("SELECT quantity FROM stock WHERE sku = 1").fetchone()[0]
for old_value in (a_read, b_read):
db.execute("UPDATE stock SET quantity = ? WHERE sku = 1", (old_value - 1,))
print("stale writes:", db.execute("SELECT quantity FROM stock").fetchone()[0])
db.execute("UPDATE stock SET quantity = 10 WHERE sku = 1")
for _ in range(2):
changed = db.execute("""
UPDATE stock SET quantity = quantity - 1
WHERE sku = 1 AND quantity >= 1
""").rowcount
assert changed == 1
print("relative updates:", db.execute("SELECT quantity FROM stock").fetchone()[0])
stale writes: 9
relative updates: 8
Selon les règles Read Committed de PostgreSQL, une mise à jour attend si une autre transaction modifie encore la même ligne ; après validation de celle-ci, elle réévalue WHERE sur la nouvelle version de la ligne. Dans cet exemple, calculez donc quantity - 1 dans la base et placez quantity >= 1 dans la même instruction. Zéro ligne modifiée signifie qu’aucun stock correspondant ne pouvait être diminué ; l’appelant doit traiter ce résultat.
Les niveaux d’isolation PostgreSQL offrent des garanties différentes :
Repeatable Read et Serializable exigent tous deux de pouvoir reprendre toute la transaction après un échec de sérialisation. Si la décision de mise à jour dépend d’une lecture préalable, verrouillez les lignes concernées dans la même transaction, par exemple avec SELECT FOR UPDATE dans PostgreSQL. Pour une règle portant sur plusieurs lignes, choisissez une isolation ou un verrouillage qui couvre la règle entière.
Validation en base et effets externes
La documentation du transactional outbox décrit deux moments où un échec peut survenir lorsque la modification en base et l’envoi d’une notification sont séparés. Si l’on envoie avant de valider, la notification reste envoyée même si la base annule la transaction. Si l’on valide avant d’envoyer, le processus peut s’arrêter entre les deux étapes. Annuler la transaction ne retire pas une requête déjà acceptée par un service externe.
Une boîte d’envoi transactionnelle, ou transactional outbox, enregistre la modification métier et l’événement à envoyer dans la même transaction. Un autre processus lit les événements validés et les transmet. En repartant du stock de 8, le bloc suivant associe la diminution du stock à l’enregistrement d’un événement. L’identifiant désigne une réservation logique et reste identique lors des nouvelles tentatives.
db.execute("""
CREATE TABLE outbox (
event_id TEXT PRIMARY KEY NOT NULL,
sku INTEGER NOT NULL REFERENCES stock(sku),
units INTEGER NOT NULL
CHECK (typeof(units) = 'integer' AND units > 0)
)
""")
def reserve(event_id, units):
db.execute("BEGIN")
try:
changed = db.execute("""
UPDATE stock SET quantity = quantity - ?
WHERE sku = 1 AND quantity >= ?
""", (units, units)).rowcount
if changed != 1:
raise ValueError("insufficient stock")
db.execute("INSERT INTO outbox VALUES (?, 1, ?)", (event_id, units))
db.execute("COMMIT")
except Exception:
db.execute("ROLLBACK")
raise
def state():
return (db.execute("SELECT quantity FROM stock WHERE sku = 1").fetchone()[0],
db.execute("SELECT COUNT(*) FROM outbox").fetchone()[0])
try:
reserve("reservation-1", 0)
except sqlite3.IntegrityError:
print("invalid event:", state())
reserve("reservation-1", 1)
print("committed:", state())
try:
reserve("reservation-1", 1)
except sqlite3.IntegrityError:
print("duplicate event:", state())
db.close()
invalid event: (8, 0)
committed: (7, 1)
duplicate event: (7, 1)
Le tuple d’état donne la quantité en stock puis le nombre d’événements dans la boîte d’envoi. L’événement invalide viole une contrainte et provoque l’annulation : le stock reste à 8 et aucun événement n’est enregistré. Une réservation réussie laisse 7 articles et un événement. L’insertion d’un identifiant en double échoue et annule aussi la diminution de stock de cette tentative, préservant (7, 1). L’application qui reçoit une requête répétée doit encore comparer les paramètres initiaux et renvoyer le résultat enregistré ; un même identifiant ne suffit pas à prouver que les requêtes sont identiques.
Les événements validés attendent encore leur transmission. Si l’émetteur s’arrête après que le service externe a accepté un événement, mais avant d’enregistrer sa livraison, il peut l’envoyer de nouveau. La boîte d’envoi conserve l’intention sans éliminer les doublons de livraison. Les principes de conception des API idempotentes d’AWS expliquent pourquoi le destinataire doit valider atomiquement l’identifiant de la requête et son effet local, et comparer les paramètres si l’identifiant est réutilisé. Pour une API externe, réutilisez une clé uniquement avec une opération qui prend en charge l’idempotence, pendant sa durée de conservation. Si le délai de réponse est dépassé, gardez le résultat comme inconnu : utilisez une consultation d’état disponible ou réessayez avec la clé initiale dans les limites des garanties d’idempotence du service.
Agents de longue durée situe cette limite dans les mécanismes de points de reprise et de récupération ; Cloudflare Workers présente les choix de stockage et de files de messages. Une fois le service choisi, vérifiez son interface de transaction et ses garanties de concurrence avant d’intégrer ces schémas et ces règles de mise à jour à l’application.