Comment puis-je, dans un environnement de session / transaction multiple, insérez-moi en toute sécurité une ligne dans une table contenant une clé de composite primaire avec une clé d'incrémentation (manuelle).
et comment puis-je récupérer la dernière valeur incrémenée de J'ai examiné Quel est le meilleur moyen d'y parvenir en termes de sécurité de transaction (verrouillage), niveau d'isolation et Le point de vue de la performance. update - une autre prise sur le problème permet de dire deux transactions / sessions Essayez d'insérer la même colonne_a, colonne_b paire ( Exemple 1,1) simultanément. Comment est-ce que je exécuter les requêtes d'insertion dans la séquence. Le premier insert (transaction 1) devrait entraîner une clé composite de 1,1, récupérez la valeur de colonne_c de l'insert. J'ai probablement besoin d'utiliser des variables?
Définition de la table colonne_c , last_insert_id () ne renvoie pas la valeur souhaitée. Sélectionnez pour mettre à jour ... Insérez et insérer dans Sélectionnez mais ne peut pas décider sur lequel utiliser.
6 Réponses :
BEGIN;
SELECT @c := MAX(c) + 1
FROM t
WHERE a = ? AND b = ?
FOR UPDATE; -- important
if row found -- in application code (or Stored Proc)
then
INSERT INTO t (a,b,c)
VALUES
(?, ?, @c);
else
INSERT INTO t (a,b,c)
VALUES
(?, ?, 1);
COMMIT;
The hope is that the FOR UPDATE will stall until it can get a lock and the desired c value. Then the rest of the transaction should go smoothly.I don't think that the setting of transaction_isolation matters, but that is worth studying.
Et que se passe-t-il si "A" et "B" n'existe pas encore? (Ensemble de clé composite entièrement neuf). La serrure aura-t-elle un effet? Est-ce que cela verrouille toute la table?
Hmmm ... j'ai mis à jour mon code, mais je ne me sens pas confiant.
Vous pouvez utiliser une procédure stockée pour cela:
Je n'ai jamais rencontré ce genre de problème et si je le faisais, je ferais comme suit: utilisation serait: résultat: même s'applique dans une transaction: Cependant, je n'ai pas essayé de transactions parallèles!
Appelons la table contenant ces 3 colonnes comme C'est la colonne Étapes de la solution: strong> p> Démonstration de la solution de la solution: strong> p> Pour conserver la démonstration généralisée, considérez que vous avez n em> demandes simultanées Pour l'insertion dans Tous les demandes n em> devront d'abord appeler la procédure stockée. Étant donné que le procédé stocké utilise une transaction sur le threeecolumntable code> pour éviter toute confusion résultant du nom de vous avez donné - Table code>. colonne_c code> qui est incrémenté manuellement. Tirez-le et gardez une trace de la dernière valeur utilisée pour cette colonne dans une autre table. P>
colonne_c code>. Appelons cette table lastronomeAdtable code>. lastrydAdTable code> ne contiendra que trois colonnes:
threeecolumntable code>); li>
colonne_c code>); li>
121 code>). LI>
ul> li>
lastrydAdtable code>. Ce processus lira la dernière valeur utilisée pour le colonne_c code> dans votre cas. Incrémenter. Renvoyez la valeur incrémentée à vous. (Bien sûr, vous pouvez faire une requête directe trop à chaque fois. La procédure stockée est un meilleur choix.) Li>
colonne_c code> est gelée pour vous, car la procédure stockée est incrémentée de la valeur dans lastronomeAdable code> pour le threeecolumntable code> ligne pour bon . Toute personne qui veut ajouter une autre rangée au threeecolumnTable code> appellera la valeur stockée et obtiendra la valeur non conflictuelle et incrémentée, même si vous n'êtes pas encore effectué avec insertion de votre valeur précédente dans votre Tableau Code>. LI>
OL>
threeecolumntable code>. p> lastrydAdTable code>, à une heure seulement 1 Demande accédera à la ligne pour threeecolumnTable code>, qui ressemble actuellement à: p> +-----------------------------------------+
| ThreeColumnTable | column_c | (121 + n) |
+-----------------------------------------+
Vous pouvez utiliser une valeur nominale pour colonne_c code> pour verrouiller la combinaison (colonne_a, colonne_b) code> pour d'autres inserts, qui s'assure particulièrement qu'il sera verrouillé même s'il n'ya pas de rangée pour cette combinaison existe encore. start transaction;
set @a = 1;
set @b = 1;
insert into `table` (column_a, column_b, column_c)
values (@a,@b,0)
on duplicate key update column_c = 0; -- , column_d = null, ...
select max(column_c) + 1 into @c
from `table` where column_a = @a and column_b = @b;
update `table` set column_c = @c
where column_a = @a and column_b = @b and column_c = 0;
select @c;
commit;
Ceci doit être atomique et semble insérer les valeurs correctes: où Malheureusement si vous souhaitez utiliser la fonction Vous pouvez ajouter une clé primaire de substitution: et exécutez le même Si vous ajoutez une clé de substitution, il peut être utile de réévaluer si Vous pouvez également être capable d'ajouter une clé de substitution à l'aide d'une variable utilisateur dans une seule connexion / transaction / procédure: S'il s'agit du seul endroit que vous insérez ou mettez à jour ces colonnes Dans votre table, vous pouvez faire du verrouillage manuel basé sur le nom. Simulation de serrures d'enregistrement avec Démarrer une transaction. Choisissez un nom spécifique pour les lignes que vous souhaitez verrouillez. par exemple. appel Faites votre Utilisez commettre la transaction. soyez au courant; Appelant : colonne_a et : colonne_b sont vos nouvelles valeurs. last_insert_id () fonctionner uniquement avec Auto_incrènement valeurs. insérer dessus. Maintenant, votre last_insert_id () Référencera la ligne nouvellement insérée. colonne_c est toujours nécessaire.
get_lock () dans une seule transaction. 'insert_table_name_aaa_bbb' . Où 'aaa' est la valeur de colonne_a et 'BBB' est la valeur de colonne_b. Sélectionnez get_Lock ('insertion_table_name_aaaa_bbb' , 30) pour verrouiller le nom 'insert_table_name_aaa_bbb' .. Il retournera 1 et définissez le verrou si le nom devient disponible ou renvoyer 0 si le verrouillage n'est pas disponible après 30 secondes ( Le deuxième paramètre est le délai d'attente). Sélectionnez et INSERT INSERTEZ ICI. Do Living_Lock ('insertion_table_name_aaa_bbb') Lorsque vous avez terminé. get_lock () à nouveau dans une transaction libérera le verrou défini précédemment. De plus, cette verroue nommée s'appliquera uniquement à ce scénario ou où le nom exact est utilisé. Le verrou ne s'applique que sur le nom!
L'option 1 et 2 fonctionnera-t-elle vraiment? Rien n'empêche deux ou plusieurs transactions de récupérer la même valeur de colonne_c? Aucun verrou n'est appliqué?
Je crois que les inserts simples sont atomiques, il n'y a donc aucun problème avec l'insert. L'option 1 utilise un pc de substitution unique à chaque rangée, alors lorsque vous exécutez Last_Insert_id (), vous obtenez la dernière ligne spécifique insérée par cette transaction. Vous pouvez ensuite utiliser cela pour récupérer la colonne de la ligne. Peu importe si une autre transaction insère une autre ligne, car Last_Insert_ID () renvoie la PK unique de l'autre rangée à cette transaction.
L'option 2 fonctionne car les variables utilisateur sont spécifiques à la session. Chaque transaction doit être sur une session distincte et, comme chaque @c est défini pendant l'insert atomique, chaque session @c doit être correct pour la ligne utilisée.
Personnellement, je ne pense pas que l'ajout d'un substitut est quelque chose que vous devez «se déplacer». Cela semble juste comme la bonne chose à faire.
Si l'intégrité des données vous importe, alors considérez les éléments suivants:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,m CHAR(1) NOT NULL
,n CHAR(1) NOT NULL
) ENGINE=InnoDB;
INSERT INTO my_table (m,n) VALUES
('a','b'),
('a','b'),
('a','c'),
('a','b'),
('j','p'),
('j','b'),
('j','p'),
('a','c');
SELECT x.*
, COUNT(*) i
FROM my_table x
JOIN my_table y
ON y.m = x.m
AND y.n = x.n
AND y.id <= x.id
GROUP
BY x.id
ORDER
BY m,n,i;
+----+---+---+---+
| id | m | n | i |
+----+---+---+---+
| 1 | a | b | 1 |
| 2 | a | b | 2 |
| 4 | a | b | 3 |
| 3 | a | c | 1 |
| 8 | a | c | 2 |
| 6 | j | b | 1 |
| 5 | j | p | 1 |
| 7 | j | p | 2 |
+----+---+---+---+
Insert dans ... Sélectionnez Devrait être Atomic, c'est-à-dire qu'il devrait réussir ou échouer, au moins pour InnoDB. Quel est le problème exact?Disons que deux transactions / session tentent d'insérer simultanément la même colonne_a, colonne_b paire (exemple 1,1) simultanément. Comment puis-je; 1. Exécutez les requêtes d'insertion dans la séquence. Le premier insert (transaction 1) entraînera une clé composite de 1,1,1 et la seconde (transaction 2) 1,1,1. J'ai besoin d'une sorte de mécanisme de verrouillage 2. Récupérer la valeur de colonne_c de l'insert.
On envisimez peut-être ajouter un nouveau champ de clé primaire qui a une auto-crème automatique. Ensuite, les gens font leurs inserts, vous obtenez le
last_insert_id en fonction de cette clé primaire automatique, puis de récupérer votrecolonne_c . Tout ce que vous essayez d'essayer de causer toutes sortes de maux de tête. Comme il n'existe pas d'insert simultané que vous n'avez pas à vous en soucier.Cela doit-il être une solution pure SQL? J'ai résolu ce problème exact en utilisant le code, pas sûr s'il peut être résolu à 100% dans Just SQL.
Insérer dans ... Ensemble ... colonne_c = last_insert_id (colonne_c + 1) Où ... ne fonctionne pas? Peut-être que je ne comprends pas le problème, mais je pense que cela fonctionne pour un problème que j'ai similaire.Toutes mes excuses, cela fonctionne bien pour la mise à jour mais pas pour insertion. Problème intéressant.
Le «meilleur», est de ne pas le faire du tout. Et utilisez plutôt la propre fonctionnalité d'incrémentation automatique d'InnoDB pour maintenir l'intégrité des données. Si vous avez vraiment besoin de ces sous-valeurs, vous pouvez toujours les dériver à la volée par la volée.