MySQL: Transactions vs Tables de locking

Je suis un peu confus avec les transactions et les tables de locking pour assurer l’intégrité de la firebase database et m’assurer que les commandes SELECT et UPDATE restnt synchronisées et qu’aucune autre connexion n’interfère avec elle. J’ai besoin de:

SELECT * FROM table WHERE (...) LIMIT 1 if (condition passes) { // Update row I got from the select UPDATE table SET column = "value" WHERE (...) ... other logic (including INSERT some data) ... } 

Je dois m’assurer qu’aucune autre requête n’interfèrera et n’effectuera le même SELECT (en lisant «l’ancienne valeur» avant que cette connexion ne termine la mise à jour de la ligne).

Je sais que je peux par défaut utiliser la LOCK TABLES table pour m’assurer que seule une connexion effectue cette opération à la fois et la déverrouiller lorsque cela est terminé, mais cela semble exagéré. Est-ce que l’enroulement dans une transaction ferait la même chose (en veillant à ce qu’aucune autre connexion n’essaie le même processus alors qu’une autre est encore en cours de traitement)? Ou serait un SELECT ... FOR UPDATE ou SELECT ... LOCK IN SHARE MODE être mieux?

Le locking des tables empêche d’autres utilisateurs de la firebase database d’affecter les lignes / tables que vous avez verrouillées. Mais les verrous en eux-mêmes ne garantiront PAS que votre logique sortira dans un état cohérent.

Pensez à un système bancaire. Lorsque vous payez une facture en ligne, il y a au moins deux comptes affectés par la transaction: Votre compte, à partir duquel l’argent est pris. Et le compte du destinataire, dans lequel l’argent est transféré. Et le compte de la banque, dans lequel ils déposeront avec plaisir tous les frais de service facturés sur la transaction. Étant donné (comme tout le monde le sait ces jours-ci) que les banques sont extraordinairement stupides, disons que leur système fonctionne comme ceci:

 $balance = "GET BALANCE FROM your ACCOUNT"; if ($balance < $amount_being_paid) { charge_huge_overdraft_fees(); } $balance = $balance - $amount_being paid; UPDATE your ACCOUNT SET BALANCE = $balance; $balance = "GET BALANCE FROM receiver ACCOUNT" charge_insane_transaction_fee(); $balance = $balance + $amount_being_paid UPDATE receiver ACCOUNT SET BALANCE = $balance 

Maintenant, sans verrous ni transactions, ce système est vulnérable à diverses conditions de course, la plus importante étant les paiements multiples effectués sur votre compte ou le compte du récepteur en parallèle. Pendant que votre solde a votre solde récupéré et fait le énorme_overdraft_fees () et tout le rest, il est tout à fait possible qu'un autre paiement exécute le même type de code en parallèle. Ils récupéreront votre solde (disons 100 $), feront leurs transactions (retirez les 20 $ que vous payez et les 30 $ avec lesquels ils vous bousent), et les deux chemins de code ont maintenant deux soldes différents: 80 $ et 70 $ En fonction de ceux qui se terminent en dernier, vous obtiendrez l'un de ces deux soldes dans votre compte, au lieu de 50 $ (100 $ - 20 $ - 30 $). Dans ce cas, "erreur bancaire en votre faveur".

Maintenant, disons que vous utilisez des serrures. Votre paiement de facture (20 $) frappe en premier lieu, il gagne donc et verrouille votre enregistrement de compte. Maintenant que vous avez une utilisation exclusive, vous pouvez déduire les 20 $ du solde et réécrire le nouveau solde en toute tranquillité ... et votre compte se retrouve avec 80 $ comme prévu. Mais ... euh ... Vous essayez de mettre à jour le compte du destinataire, et il est verrouillé et verrouillé plus longtemps que le code le permet, en arrêtant votre transaction ... Nous avons affaire à des banques stupides, donc au lieu d'avoir une erreur correcte manipulant, le code tire simplement une exit() , et vos 20 $ disparaissent dans une bouffée d’électrons. Vous avez maintenant 20 $ et vous devez encore 20 $ au destinataire et votre téléphone est repris.

Alors ... entrez des transactions. Vous commencez une transaction, vous débitez votre compte 20 $, vous essayez de créditer le destinataire avec 20 $ ... et quelque chose explose à nouveau. Mais cette fois-ci, au lieu de exit() , le code peut simplement effectuer une rollback , et poof, vos 20 $ sont ajoutés comme par magie à votre compte.

En fin de compte, cela se résume à ceci:

Les verrous empêchent quiconque d'interférer avec les enregistrements de firebase database avec lesquels vous traitez. Les transactions empêchent toute erreur "ultérieure" d'interférer avec les actions "antérieures" que vous avez effectuées. Ni l'un ni l'autre ne peut garantir que les choses se passent bien à la fin. Mais ensemble, ils le font.

dans la leçon de demain: La joie des impasses.

Vous voulez un SELECT ... FOR UPDATE ou SELECT ... LOCK IN SHARE MODE dans une transaction, comme vous l’avez dit, puisque normalement les SELECT, qu’ils soient dans une transaction ou non, ne verrouillent pas une table. La méthode que vous choisirez dépendra si vous souhaitez que d’autres transactions puissent lire cette ligne pendant que votre transaction est en cours.

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

START TRANSACTION WITH CONSISTENT SNAPSHOT ne fera pas l’affaire pour vous, car d’autres transactions peuvent encore se produire et modifier cette ligne. Ceci est mentionné en haut du lien ci-dessous.

Si d’autres sessions mettent à jour simultanément la même table, […] vous pouvez voir la table dans un état qui n’a jamais existé dans la firebase database.

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

J’ai rencontré un problème similaire lors d’une tentative IF NOT EXISTS ... et ensuite lors d’une INSERT qui a provoqué une situation de concurrence lorsque plusieurs threads mettaient à jour la même table.

J’ai trouvé la solution au problème ici: Comment écrire des requêtes INSERT IF NOT EXISTS en SQL standard

Je me rends compte que cela ne répond pas directement à votre question, mais le même principe consistant à effectuer une vérification et à insérer une seule déclaration est très utile. vous devriez pouvoir le modifier pour effectuer votre mise à jour.

Les concepts de transaction et les verrous sont différents. Cependant, la transaction a utilisé des verrous pour l’aider à suivre les principes de l’ACID. Si vous voulez que la table empêche les autres de lire / écrire au même moment pendant que vous êtes en lecture / écriture, vous avez besoin d’un verrou pour ce faire. Si vous voulez vous assurer de l’intégrité et de la cohérence des données, vous devez utiliser les transactions de manière plus efficace. Je pense que les concepts de niveaux d’isolement sont mélangés dans les transactions avec des serrures. S’il vous plaît rechercher des niveaux d’isolement des transactions, SERIALIZE devrait être le niveau que vous voulez.

Vous êtes confondu avec lock & transaction. Ce sont deux choses différentes dans RMDB. Le locking empêche les opérations simultanées pendant que la transaction se concentre sur l’isolation des données. Découvrez cet excellent article pour la clarification et une solution gracieuse.

J’utiliserais un

 START TRANSACTION WITH CONSISTENT SNAPSHOT; 

pour commencer, et un

 COMMIT; 

pour finir avec.

Tout ce que vous faites entre-temps est isolé des autres utilisateurs de votre firebase database si votre moteur de stockage prend en charge les transactions (InnoDB).