mySQL Transactions vs Verrouillage des Tables

Question

Je suis un peu confus avec des transactions vs verrouillage de tables afin de garantir l'intégrité de base de données et assurez-vous de SÉLECTIONNER et de mettre à JOUR restent synchronisés et aucune autre connexion 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) ...
}

J'ai besoin de s'assurer qu'aucun autre des requêtes d'interférer et d'effectuer le même SELECT (la lecture de "la vieille valeur" avant que la connexion des finitions de la mise à jour de la ligne.

Je sais que je peux défaut d' LOCK TABLES table juste assurez-vous que seulement 1 connexion est en train de faire ça à un moment, et le déverrouiller quand je suis fait, mais qui semble exagéré. Serait d'emballage que, dans une transaction de faire la même chose (en s'assurant de l'absence d'autres tentatives de connexion le même processus, alors que l'autre est toujours en cours de traitement)? Ou un SELECT ... FOR UPDATE ou SELECT ... LOCK IN SHARE MODE de mieux?

Answer 1

Verrouillage des tables empêche les autres DB utilisateurs d'affecter les lignes/tables que vous avez verrouillé. Mais les verrous, en eux-mêmes, ne permettra PAS d'assurer que votre logique est livré dans un état cohérent.

Pensez à un système bancaire. Lorsque vous payez une facture en ligne, il y a au moins deux comptes touchés par l'opération: Votre compte, à partir de laquelle l'argent est prélevé. Et le compte du bénéficiaire, dans lequel l'argent est transféré. Et le compte de la banque, dans lequel ils seront ravis de dépôt de tous les frais de service facturés sur la transaction. Étant donné (que tout le monde connaît ces jours-ci) que les banques sont extraordinairement stupide, 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, avec pas de serrures et pas de transactions, ce système est vulnérable à diverses conditions de course, le plus important est de multiples paiements effectués sur votre compte ou sur le compte séquestre en parallèle. Alors que votre code a votre équilibre récupéré et est en train de faire la huge_overdraft_fees() et autres joyeusetés, il est tout à fait possible que certains autres de paiement sera exécuté le même type de code en parallèle. Ils vont récupérer votre solde ($100), faire leurs transactions (sortir le 20 $que vous payez, et de 30 $pour les elles sont de vissage plus avec vous), et maintenant les deux chemins de code ont deux différents soldes: $80 $à 70$. Selon celles qui termine dernière, vous vous retrouverez avec l'une de ces deux soldes dans votre compte, au lieu de 50$, vous devriez en avoir fini avec ($100 - $20 - $30). Dans ce cas, "erreur de la banque en votre faveur".

Maintenant, disons que vous utiliser des verrous. Votre paiement de facture ($20) frappe le tuyau d'abord, donc, il gagne et verrouille le dossier de votre compte. Maintenant vous avez l'usage exclusif, et peut déduire le montant de 20 $de l'équilibre, et d'écrire le nouvel équilibre de retour à la paix... et votre compte se retrouve avec $80 comme c'est prévu. Mais... uhoh... Vous essayez de mettre à jour le compte du bénéficiaire, et c'est fermé et verrouillé plus long que le code permet, le calendrier de votre transaction... Nous avons affaire à des stupides les banques, de sorte qu'au lieu d'avoir une bonne gestion d'erreur, le code tire juste un exit(), et de vos 20 $disparaît dans un nuage d'électrons. Maintenant que vous êtes hors de 20$, et il vous reste à payer 20 $pour le récepteur, et votre téléphone reçoit repris.

Alors... à saisir les transactions. Vous démarrer une transaction, vous débiter votre compte de 20$, vous essayez de crédit le récepteur avec 20 $... et quelque chose de nouveau coups. Mais cette fois, au lieu de exit(), le code peut juste faire rollback, et hop, votre 20$, c'est comme par magie ajouté à votre compte.

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

Serrures empêcher quiconque d'interférer avec les enregistrements de base de données que vous avez affaire. Transactions gardez tout "plus tard" erreurs d'interférer avec le "plus haut" les choses que vous avez fait. Ni elle seule peut garantir que les choses fonctionnent ok à la fin. Mais ensemble, ils le font.

demain dans la leçon: La Joie de Blocages.

Answer 2

Vous voulez un SELECT ... FOR UPDATE ou SELECT ... LOCK IN SHARE MODE à l'intérieur d'une transaction, comme vous l'avez dit, depuis Sélectionne normalement, peu importe si elles sont dans une transaction ou pas, ne sera pas verrouiller une table. Celui que vous choisissez dépendra de savoir si vous voulez d'autres transactions pour être en mesure de 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, comme d'autres transactions peuvent encore venir le long et modifier cette ligne. Ceci est mentionné juste au dessus du lien ci-dessous.

Si d'autres sessions simultanément mise à jour de la même table [...] vous pouvez voir le tableau dans un état qui n'a jamais existe dans la base de données.

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

Answer 3

J'ai eu un problème similaire lors d'une tentative de IF NOT EXISTS ... , puis en appliquant un INSERT qui a provoqué une situation de concurrence lorsque plusieurs threads ont été mise à jour le même tableau.

J'ai trouvé la solution au problème qui se pose ici: Comment écrire INSÉRER SI n'EXISTE PAS de requêtes en SQL standard

Je constate que ce n'est pas directement répondre à votre question, mais le même principe de l'exécution d'une vérification et de l'insérer comme une seule instruction est très utile; vous devriez être en mesure de le modifier pour effectuer votre mise à jour.

Answer 4

J'aimerais utiliser un

START TRANSACTION WITH CONSISTENT SNAPSHOT;

pour commencer, et un

COMMIT;

à la fin, avec.

Tout ce que vous faites entre les deux est isolé des autres utilisateurs de votre base de données si votre moteur de stockage prend en charge les transactions (qui est InnoDB).