Discussion :

[redah75]

Bonjour,

J'aurais une autre petite question concernant une table de facturation.
Dans cette table je sais que je suis censé utiliser une colonne Auto_Increment pour les numéros de factures. Sauf que cette table contiendra les factures de 2 pays différents. Voici un exemple simplifié de la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
sequence | numero
01       | 10001
01       | 10002
01       | 10003
02       | 10001
02       | 10002
01       | 10004
...

Est-ce que déjà cette solution est bien ? Ou faut-il créer 2 tables, une pour chaque pays ?

Merci

[redah75]

Une petite précision, j'ai oublié de mentionner que j'utilise le type InnoDB.

[TiranusKBX]

tout dépend si il est absolument nécessaire que les numéros soient en fonction du pays ou uniques avec une différenciation par pays

[redah75]

Bonjour,

Il est absolument nécessaire que les numéros soient en fonction du pays

Ex de facture pour la france: F01-100001, F01-100002, F01-100003, F01-100004
et pour la belgique: F02-100001, F02-100002, F02-100003, F02-100004

[TiranusKBX]

alors je verrais :
ID(id absolus de facture dans la table)
pays(id pays)
numero(id facture)
data,
....

Mais pour gérer les numéros il te faudra dans ton code appelant récupérer à chaque fois le dernier id facture du pays concerné avant d'en générer une nouvelle.
C'est le seul moyen de le faire ou alors il de faut passer par les procédures stockées.

[redah75]

Oui, j'ai fait quelque chose du genre. voici mon code si cela peut interesser quelqu'un:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
$id_order = 10001;
$invoice_sequence = 01;
$ins = $dbh->prepare("INSERT INTO invoices (id_order, invoice_type, invoice_sequence, number)
	SELECT ?, ?, ?, IFNULL(MAX(number) + 1, 100001) FROM invoices WHERE invoice_sequence = ?");
 
do { // au cas ou 2 facture se créent a la meme micro seconde
	if($ins->execute(array($id_order, "F", $invoice_sequence, $invoice_sequence))) break;
} while(0);

[punkoff]

Gérez vos séquences dans une table à part.

Créez une procédure qui retournera le max+1, tout en gérant correctement le niveau de transaction (serializable si ça marche sous mysql).
C'est le seul moyen de vous prémunir des doublons http://sqlpro.developpez.com/cours/clefs/#L3.2 (un exemple à adapter).


N'hésitez pas sinon à rajouter un index unique sur vos 2 colonnes...

[redah75]

Désolé, je n'ai pas été très précis dans mes précédents posts.
Effectivement, je gère déjà les séquences dans une autre table, dans laquelle la colonne invoice_sequence est une PK UNSIGNED ZEROFILL.

Puis dans la table factures, j'ai mis PK sur les 2 colonnes: number et invoice_sequence

[Exia_]

Bonjour,

il ne faut pas faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT MAX(id)
FROM maTable

Car cette requête si elle est exécutée au même moment par deux clients, deux facture auront le même ID.
Pour contrer ça SELECT .. FOR UPDATE

[redah75]

Bonsoir Exia93,

En fait, j'avais déjà essaye avec SELECT ... FOR UPDATE mais MySQL renvoie une erreur:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1

Je pense que c'est parce que j'utilise innoDB.

Mais avec ce que j'ai mis, on ne peut pas avoir le même ID puisque ID, sequence présentent une clef primaire. Et avec la boucle do while, il ré-exécutera la requête sql s'il y a un doublon au cas où 2 clients exécutent la requête en même temps...

[Exia_]

Le SELECT .. FOR UPDATE est utilisable seulement sur les moteurs supportant les transactions, ce que fait parfaitement InnoDB.
Quelle était votre requête avec le SELECT ... FOR UPDATE ? Il est possible que vous ayez simplement fait une erreur de syntaxe.

Je ne comprends pas l'utilité de votre boucle, vu que vous ne modifiez aucune des variables de la requête à chaque tour de boucle...

[redah75]

Bonjour,

j'ai fait un select tout simple de cette facon:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT number FROM invoices FOR UPDATE;

[Exia_]

Dans l'erreur il est question d'un LIMIT 1 alors que dans l'exemple il n'y a pas cette clause...

[redah75]

Désolé, j'avais juste omis de le mettre. Voici la requête que j'ai mise :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT number FROM invoices FOR UPDATE LIMIT 1

[Exia_]

Aviez vous essayé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT number FROM invoices LIMIT 1 FOR UPDATE

Car il me semble que la clause FOR UPDATE doit être mise à la fin de la requête.

Maintenant j'imagine que votre requête finale ne ressemblera pas à celle-là, car le LIMIT 1 ne garantit pas le dernier enregistrement inséré. Avec la clause FOR UPDATE, faire un :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT MAX(number) FROM invoices FOR UPDATE

est alors possible.

[redah75]

Car il me semble que la clause FOR UPDATE doit être mise à la fin de la requête.

Ah ouaissss jamais je n'aurais pensé à mettre la clause FOR UPDATE à la fin
Effectivement ça marche bien et bien évidemment, je mettrai le MAX(number)

Mais sinon pour mieux comprendre le SELECT ... FOR UPDATE, qu'est ce qui se passe concrètement après l’exécution de cette requête ?

[Exia_]

La clause FOR UPDATE permet de poser un verrou sur la ligne (ou la table je ne me souviens plus ^^') qui est retourné par la requête. Si d'autres clients essaient d'accéder à la ligne (ou table) InnoDB les fera patienter jusqu'à la fin de la transaction, ce qui permet d'être assuré que la donnée n'est modifiée que par un seul client à la fois.

Il est même possible au lieu de faire un MAX d'utiliser une table de séquence.
Elle serait composée de 3 colonnes :
nomTable : varChar(255)
nomSequence : varChar(255)
value : BIGINT

Ensuite lorsqu'une nouvelle table est créée, il suffit d'ajouter un enregistrement dans la table sequence pour chaque colonne ayant une colonne avec le même comportement que l'auto_increment.
Ensuite lorsque l'on veut ajouter un nouvel enregistrement dans une table, par exemple un nouvel invoices dans votre cas. On récupère à l'aide d'un SELECT ... FOR UPDATE l'id de cette colonne dans la table sequence, on crée le nouvel enregistrement dans la table invoices puis on ajoute 1 à la colonne dans la table sequence.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
-- exemple de code
int lastId = db.query("SELECT value FROM sequence WHERE nomTable = 'invoices' AND nomSequence = 'number' FOR UPDATE");
lastId = lastId + 1;
db.query("INSERT INTO invoices VALUES(lastId, 'toto', 'titi', 'tutu')");
db.query("UPDATE sequence set value = value +1 WHERE nomTable = 'invoices' AND nomSequence = 'number'");

Il me semble que le fait de faire un UPDATE sur la table sequence valide la transaction et permet aux autres clients d'accéder à l'information, mais c'est à vérifier.

[redah75]

Donc en gros, un SELECT max(colonne) ... FOR UPDATE bloquera toute autre requêtes sur cette même table, c'est bien ça ?
Mais s'il y a disons une dizaine de requêtes sur cette table en même temps, est-ce que ça risque de créer un conflit avec ces requêtes "différées" ?

Je ne comprends pas l'utilité de votre boucle vu que vous ne modifiez aucune des variables de la requête à chaque tour de boucle

En fait, je n'ai pas besoin de modifier les variables, puisque si la 1ere exécution échoue, alors il va ré-exécuter la requête en récupérant la nouvelle valeur Max(number) puis ajoute + 1...
J'espère que vous voyez mieux ce que je veux dire.

[Exia_]

Comme dit plus haut, je ne me souviens pas si la requête bloque la ligne spécifique ou la table entière (sachant que avec la table séquence je suis sûr que ce n'est que la ligne qui est lockée, maintenant avec un MAX vu que le MAX est dépendant de la table je ne sais pas).

Cependant, pour réduire le temps de lock de la table (ou ligne), il est préférable d'utiliser une procédure stockée qui reçoit en paramètres toutes les informations à l'insertion d'une nouvelle ligne dans votre table invoices, va utiliser le SELECT ... FOR UPDATE, insérer la ligne et faire la mise à jour.

Étant seulement côté SGBD pour faire c'est 3 traitements, le temps de lock sera beaucoup moins long, car il n'y a qu'un seul envoi sur le réseaux.

[redah75]

OK merci. Je vais regarder cela de plus près... mais bon, ça me fait un peu peur, cette histoire de lock...