MySQL AUTO_INCREMENT ne pas ROLLBACK

Je suis en utilisant MySQL champ de type AUTO_INCREMENT et InnoDB pour soutenir les transactions. J'ai remarqué que quand je rollback de la transaction, le champ de type AUTO_INCREMENT n'est pas rollbacked? J'ai trouvé qu'il a été conçu de cette façon, mais existe-il des solutions de contournement pour cela?

Juste une remarque: les valeurs auto_increment réinitialiser un max de+1 de la colonne après une réinitialisation du serveur.
Ce n'est pas spécifique à mysql, Postgres se comporte de la même manière. Les explications du sens.

InformationsquelleAutor codegy | 2009-01-16

55

Permettez-moi de vous faire remarquer quelque chose de très important:

Vous ne devez jamais dépendre du numérique dispose d'générée automatiquement les clés.

Qui est, autres que les comparant pour l'égalité (=) ou unequality (<>), vous ne devriez pas faire autre chose. Aucun des opérateurs relationnels (<, >), pas de tri par index, etc. Si vous avez besoin de trier par "date d'ajout", avoir une "date d'ajout" de la colonne.

De les traiter comme des pommes et des oranges: est-il judicieux de demander si une pomme est le même comme une orange? Oui. Est-il judicieux de demander si une pomme est plus gros qu'une orange? Pas de. (En fait, il n', mais vous avez mon point.)

Si vous vous en tenez à cette règle, les lacunes dans la continuité de l'générée automatiquement les index ne sera pas causer des problèmes.
- Pouvez-vous qualifier cela un peu? Je veux dire, pourquoi en ajouter une autre colonne, avec les inconvénients que cela implique (indice de la gestion, de l'utilisation du disque, plus de IO, etc., etc.), quand il y a déjà une très bonne valeur à utiliser dans l'autoinc de la colonne? Par définition, il offre une valeur unique qui ne se répète jamais et toujours augmente à mesure que les enregistrements sont ajoutés. La seule chose qu'il n'est pas continu, mais aussi longtemps que vous assumez des lacunes dans la séquence, je ne vois aucun problème à l'utiliser pour faire des choses comme le tri d'un ensemble d'enregistrements par leur insertion de l'ordre. En fait, je dirais que c'est la meilleure façon, en termes de performances et de clarté.
- C'est un conseil, pas une réponse
- L'ajout de la date qui se passe au moment de la commettre ou au moment de l'insertion. Cela pourrait-il se produire dans le cas de plusieurs threads insertion dans la table plus tard timestamp est visible avant un horodatage antérieur en raison du retard dans l'exécution des fois?
- Être un développeur pour de nombreuses années, après la lecture de cette réponse, je ressens, j'ai été éclairé!
- le conseil est si bonne que j'ai fini par oublier à propos de la réponse
InformationsquelleAutor Tamas Czinege
74

Il ne peut pas travailler de cette façon. Considérer:
- programme, vous ouvrez une opération et les insérer dans une table FOO qui a un autoinc clé primaire (arbitrairement, nous disons qu'il obtient 557 pour sa valeur de clé).
- Programme des deux commence, il ouvre une transaction et l'insère dans la table FOO arriver 558.
- Programme de deux insertions dans la table de BAR qui a une colonne qui est une clé étrangère à TOTO. Alors maintenant, la 558 est situé dans les deux FOO et BAR.
- Programme de deux désormais s'engage.
- Programme de trois départs et génère un rapport à partir de la table FOO. 558 dossier est imprimé.
- Après cela, le programme de l'un des rouleaux de retour.
Comment fonctionne la base de données de récupérer l'557 valeur? Faut-il aller dans FOO et de décrémentation toutes les autres clés primaires de plus de 557? Comment est-il fixer la BARRE? Comment est-il effacer l'558 imprimé sur le rapport du programme des trois sortie?

Oracle numéros de séquence sont également indépendants des transactions pour la même raison.

Si vous pouvez résoudre ce problème en temps constant, je suis sûr que vous pouvez faire beaucoup d'argent dans le champ de base de données.

Maintenant, si vous avez une exigence particulière de votre auto incrément de terrain n'ont jamais lacunes (à des fins de vérification, par exemple). Ensuite, vous ne pouvez pas la restauration de vos transactions. Au lieu de cela vous devez disposer d'un indicateur d'état sur vos dossiers. Sur la première insertion, l'enregistrement de l'état est "Incomplète", puis vous commencez l'opération, de faire leur travail et de mettre à jour le statut de "concurrencer" (ou ce que vous avez besoin). Puis, quand vous vous engagez, l'enregistrement en direct. Si la transaction rollsback, l'incomplétude de l'enregistrement est toujours là pour l'audit. Cela vous fera beaucoup d'autres maux de tête, mais est un moyen de traiter avec les pistes d'audit.

InformationsquelleAutor jmucchiello
12

J'ai eu un client qui avait besoin de l'ID de la restauration sur une table de factures, d'où la commande doit être consécutives

Ma solution dans MySQL était de supprimer l'AUTO-INCRÉMENTATION et tirez le dernier Id de la table, ajoutez un (+1), puis l'insérer manuellement.

Si la table est nommée "TableA" et l'Auto-incrémentation de la colonne est "Id"
```
INSERT INTO TableA (Id, Col2, Col3, Col4, ...)
VALUES (
(SELECT Id FROM TableA t ORDER BY t.Id DESC LIMIT 1)+1,
Col2_Val, Col3_Val, Col4_Val, ...)
```
- Pourquoi un downvote ? Upvote, cette réponse semble correct pour moi.
- Peut-être ce serait mieux (SELECT MAX(id) AS maxval FROM table_name;)+1
- Ce ne sera pas une opération atomique, il n'y aurait certainement petite fenêtre lorsque plus d'une requête est même maxID.
- Ne pourriez-vous pas l'utilisation de LOCK TABLES pour éviter cela?
- Qui devrait être mis dans la réponse.
- pourquoi nous verrouiller une table pour cette stupide chose, car une application peut nécessiter de nombreux tableaux nécessitant continue auto_increments. Maintenant, si nous commençons à verrouillage d'une seule instance de l'application peut être utilisée qu'une fois
- Il sent à des difficultés. Pourquoi ne pas créer un champ supplémentaire à côté de l'id comme fakeId pour gérer cela? Vous gérer le rendant consécutives
- Vous pouvez utiliser la commande LOCK TABLES, mais cela ne signifie qu'une seule transaction à la fois peut avoir accès à votre table. Dans de nombreuses applications, il ya des dizaines ou des centaines de sessions simultanées de l'insertion ou de mise à jour des lignes dans la table. L'auto-incrémentation n'est pas titulaire d'un verrou jusqu'à ce que vous validez votre transaction, il verrouille uniquement brièvement, assez longtemps pour incrémenter le numéro d'identification. (c'est un vieux de réponse, mais il est juste apparu dans mon alimentation aujourd'hui)
InformationsquelleAutor Michael Corrigan
11

Pourquoi s'en faire si elle est annulée? AUTO_INCREMENT de la clé des champs ne sont pas censés avoir un sens de sorte que vous ne devriez vraiment pas attention à ce que la valeur est utilisée.

Si vous avez des informations que vous essayez de préserver, peut-être à un autre non-colonne de clé est nécessaire.

InformationsquelleAutor Paul Lefebvre
8

Je ne sais pas de toute façon de le faire. Selon le Documentation De MySQL, ce comportement est attendu et sera le cas avec tous innodb_autoinc_lock_mode modes de verrouillage. Le texte spécifique est:

Dans tous les modes de verrouillage (0, 1 et 2), si un
opération qui a généré
l'auto-incrémentation des valeurs annule,
ces auto-incrémenter les valeurs sont
“lost”. Une fois qu'une valeur est générée pour
auto-incrémentée, il ne peut pas être
roulé en arrière, si oui ou non le
“INSÉREZ-comme” l'instruction est terminée,
et si oui ou non le contenant
la transaction est annulée. Ces pertes de
les valeurs ne sont pas réutilisés. Ainsi, il peut y
avoir des lacunes dans les valeurs stockées dans une
AUTO_INCREMENT colonne d'une table.

InformationsquelleAutor gpojd
4

Si vous définissez auto_increment à 1 après une annulation ou d'une suppression, sur la prochaine insertion, MySQL va voir que 1 est déjà utilisé et au lieu d'obtenir la MAX() valeur et ajouter 1.

Cela permettra d'assurer que si la ligne avec la dernière valeur est supprimée (ou l'insert est annulée), elle sera réutilisée.

Pour définir le type auto_increment à 1, faire quelque chose comme ceci:
```
ALTER TABLE tbl auto_increment = 1
```
Ce n'est pas aussi efficace que de simplement continuer avec le prochain numéro, car MAX() peuvent être coûteux, mais si vous supprimez/rollback rarement et sont obsédés par la réutilisation de la valeur la plus élevée, alors c'est une approche réaliste.

Être conscient que cela n'empêche pas les lacunes d'enregistrements supprimés dans le milieu ou si un autre insert doit se produire avant que vous ne le réglage de type auto_increment à 1.

InformationsquelleAutor Marcus Adams
2
```
INSERT INTO prueba(id) 
VALUES (
(SELECT IFNULL( MAX( id ) , 0 )+1 FROM prueba target))
```
Si la table ne contient pas de valeurs ou des lignes nulles

ajouter une cible pour l'erreur mysql type de mise à jour à PARTIR sur une sélection de

InformationsquelleAutor danis
1

SOLUTION:

Nous allons utiliser 'tbl_test" comme un exemple de tableau, et supposons que le champ 'Id' a l'attribut AUTO_INCREMENT
```
CREATE TABLE tbl_test (
Id int NOT NULL AUTO_INCREMENT ,
Name varchar(255) NULL ,
PRIMARY KEY (`Id`)
)
;
```
Supposons que le tableau a houndred ou milliers de lignes déjà inséré et vous ne voulez pas utiliser de type AUTO_INCREMENT plus; parce que quand vous la restauration d'une transaction le champ 'Id' est toujours à l'ajout de +1 à la valeur AUTO_INCREMENT.
Donc, pour éviter ce que vous pourriez faire ceci:
- Nous allons les supprimer AUTO_INCREMENT valeur de la colonne " Id " (cela ne supprimera pas vos lignes insérées):
```
ALTER TABLE tbl_test MODIFIER la COLONNE Id int(11) not NULL PREMIER;
```
- Enfin, nous avons créer un AVANT de Déclencheur d'INSERTION pour générer un 'Id' valeur automatiquement. Mais l'utilisation de cette façon, n'influe pas sur la valeur de l'Id, même si vous rollback toute transaction.
```
CREATE TRIGGER trg_tbl_test_1 
AVANT d'INSÉRER SUR tbl_test 
POUR CHAQUE LIGNE 
COMMENCER 
DÉFINIR DE NOUVEAUX.Id= FUSIONNENT((SELECT MAX(Id) from tbl_test),0) + 1; 
FIN;
```
Que c'est! Vous avez terminé!

Vous êtes les bienvenus.

InformationsquelleAutor mld.oscar
0

Si vous avez besoin d'avoir l'id dans l'ordre numérique, sans lacunes, alors vous ne pouvez pas utiliser une colonne d'auto-incrémentation. Vous aurez besoin de définir une norme colonne de type integer et utiliser une procédure stockée qui calcule le nombre suivant dans l'insertion de la séquence et insère l'enregistrement au sein d'une transaction. Si l'insertion échoue, alors la prochaine fois que la procédure est appelée, elle va recalculer le numéro suivant.

Cela dit, c'est une mauvaise idée de s'appuyer sur les numéros en cours dans un ordre particulier avec pas de lacunes. Si vous avez besoin de préserver l'ordre, vous devriez probablement timestamp de la ligne à insérer (et potentiellement sur la mise à jour).
- Cela signifie deux opérations de frapper de la même table à la fois ne peut jamais réussi à la fois à s'engager. Qui peut être un énorme goulot d'étranglement. Et si vous avez beaucoup de clés étrangères, vous pourriez facilement vous retrouver avec des blocages quand ils ont beaucoup de nombreuses relations. Ma réponse pour l'écart de la manipulation seulement un peu mal. 🙂
InformationsquelleAutor tvanfosson
0

Réponse concrète à ce dilemme (que j'ai eu) est la suivante:

1) Créer une table qui contient les différents compteurs de différents documents (factures, reçus, RMA, etc..); l'Insertion d'un enregistrement pour chacun de vos documents et d'ajouter l'initiale du compteur à 0.

2) Avant de créer un nouveau document, procédez de la manière suivante (pour les factures, par exemple):
```
UPDATE document_counters SET counter = LAST_INSERT_ID(counter + 1) where type = 'invoice'
```
3) pour Obtenir la dernière valeur que vous avez juste mis à jour, comme suit:
```
SELECT LAST_INSERT_ID()
```
ou tout simplement utiliser votre PHP (ou autre) mysql_insert_id() fonction permettant d'obtenir la même chose

4) Insérez votre nouveau dossier avec l'ID primaire que vous venez de rentrer de la DB. Cela permettra de remplacer l'auto incrémentation de l'index, et assurez-vous que vous n'avez pas d'ID écarts entre les documents.

Cet ensemble doit être enveloppé à l'intérieur d'une transaction, bien sûr. La beauté de cette méthode est que, quand vous la restauration d'une transaction, votre instruction de mise à JOUR à partir de l'Étape 2 sera annulée, et le compteur ne changera plus. D'autres transactions simultanées se bloque jusqu'à ce que la première transaction soit validée ou annulée, donc ils n'auront pas accès à l'ancien comptoir OU un nouveau, jusqu'à ce que toutes les autres opérations sont a terminé la première.

InformationsquelleAutor teomor

-1

$masterConn = mysql_connect("localhost", "root", '');
mysql_select_db("sample", $masterConn);

for($i=1; $i<=10; $i++) {
    mysql_query("START TRANSACTION",$masterConn);
    $qry_insert = "INSERT INTO `customer` (id, `a`, `b`) VALUES (NULL, '$i', 'a')";
    mysql_query($qry_insert,$masterConn);
    if($i%2==1) mysql_query("COMMIT",$masterConn);
    else mysql_query("ROLLBACK",$masterConn);

    mysql_query("ALTER TABLE customer auto_increment = 1",$masterConn);

}



echo "Done";

InformationsquelleAutor frangly

Vous devez vous connecter pour publier un commentaire.

Vous ne devez jamais dépendre du numérique dispose d'générée automatiquement les clés.