SQL atomique d'incrémentation et de verrouillage des stratégies - est-ce sûr?

J'ai une question à propos de SQL et de verrouillage des stratégies. Comme exemple, supposons que j'ai un compteur de vue pour les images sur mon site. Si j'ai une procédure stockée ou similaire à effectuer les déclarations suivantes:

START TRANSACTION;
UPDATE images SET counter=counter+1 WHERE image_id=some_parameter;
COMMIT;

Supposer que le compteur pour un image_id a une valeur de " 0 " à l'instant t0. Si deux sessions de mise à jour de la même image, compteur, s1 et s2, de démarrer simultanément à t0, est-il possible que ces deux sessions à la fois de lire la valeur "0", l'augmenter à '1' et les deux essayer de mettre à jour le compteur à '1', de sorte que le compteur d'obtenir la valeur '1' au lieu de '2'?

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok

Résultat final: pas la valeur " 1 " pour image_id=15, doit avoir été 2.

Mes questions sont:

  1. Ce scénario est-il possible?
  2. Si oui, le niveau d'isolation de transaction question?
  3. Est-il un outil de résolution de conflits qui permettrait de détecter un tel conflit comme une erreur?
  4. Peut-on utiliser n'importe syntaxe particulière afin d'éviter un problème (quelque chose comme Comparer Et Swap (CAS) ou explicite verrouillage des techniques)?

Je suis intéressé par une réponse générale, mais si on n'y fait, je suis intéressé par MySql et InnoDB-des réponses précises, car je suis en train d'utiliser cette technique pour mettre en œuvre des séquences sur InnoDB.

EDIT:
Le scénario suivant pourrait également être possible, le même comportement. Je suis en supposant que nous sommes dans le niveau d'isolation READ_COMMITED ou plus, de sorte que le s2 obtient la valeur depuis le début de la transaction, même si s1 déjà écrit " 1 " pour le compteur.

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: read counter for image_id=15, get 0 (since another tx), store in temp2
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok
InformationsquelleAutor Alexander Torstling | 2010-09-29
  1. 29

    UPDATE requête place un verrou de mise à jour sur les pages ou documents qu'il lit.

    Quand une décision est prise quant à la mise à jour de l'enregistrement, le verrouillage est levée ou promotion pour le verrouillage exclusif.

    Cela signifie que, dans ce scénario:

    s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1

    s2 va attendre jusqu'à ce que s1 décide d'écrire le compteur ou pas, et ce scénario est en fait impossible.

    Il sera présent:

    s1: place an update lock on image_id = 15
s2: try to place an update lock on image_id = 15: QUEUED
s1: read counter for image_id=15, get 0, store in temp1
s1: promote the update lock to the exclusive lock
s1: write counter for image_id=15 to (temp1+1), which is 1 
s1: commit: LOCK RELEASED
s2: place an update lock on image_id = 15
s2: read counter for image_id=15, get 1, store in temp2
s2: write counter for image_id=15 to (temp2+1), which is 2

    Noter que dans InnoDB, DML requêtes de ne pas lever les verrous de mise à jour de fiches de lecture.

    Cela signifie que dans le cas d'un full table scan, les enregistrements qui ont été lus, mais a décidé de ne pas mettre à jour, restera verrouillé jusqu'à la fin de l'opération et ne peut pas être mis à jour à partir d'une autre transaction.

    • Merci pour la grande explication. Je pense que la phrase-clé ici est "commit: VERROUILLAGE LIBÉRÉ". Cela signifie que toutes les transactions en voulant mettre à jour la ligne doit attendre l'achèvement de la transaction, la tenue de la serrure, effectivement la sérialisation de toutes les transactions de la lutte pour la ligne. Savez-vous comment serait-ce de travailler en plusieurs version de simultanéité db comme Postgres? Depuis, il utilise de multiples versions, je suppose qu'il va laisser les transactions à progresser de façon autonome et d'essayer de fusionner les résultats. Ou faut-il employer la même stratégie?
    • Il y a un minimum niveau d'isolation de transaction nécessaire pour s1 pour placer un verrou de mise à jour au début comme suggéré par ConcernedOfTunbridgeWells dans sa réponse?
    • Dans MVCC qui PostgreSQL utilise il n'y a pas de notion de verrouillage à tous. Au lieu de cela, plusieurs versions d'un enregistrement sont stockées, à l'aide de l'identifiant de transaction comme un marqueur. Le verrouillage se produit uniquement lorsque vous tentez de modifier une version dans les limbes.
    • les requêtes toujours lieu UPDATE serrures pour lire les enregistrements, pas sur le niveau d'isolation de transaction (même si c'est READ UNCOMMITTED). Je parle SQL Server maintenant.
    • la mise à jour est atomique à n'importe quel niveau d'isolation, mais si vous lisez la valeur après le fait pour votre séquence, vous obtenez toujours une course à condition que la lecture est maintenant pas synchronisée. Vous pourriez potentiellement obtenir à la fois les processus de lecture 2 comme la valeur actuelle.
    • Merci à vous deux pour de grandes explications et de discussions. Je ne peux pas accepter les réponses, donc je vais régler pour Quassnoi qui explique comment faire la mise à jour de verrouillage des œuvres.
    • L'instruction de mise à jour doivent être à l'intérieur d'une transaction pour être à l'abri de la condition de la course? Ou est-il en sécurité, peu importe? (single instruction de mise à jour, pas de transaction)
    • Est votre explication pertinente pour une UPDATE interroger sur son propre ou pour un UPDATE requête enveloppé dans une opération comme c'est écrit dans le début de l'OP question? (Même question que @Despertar ci-dessus).
    • les principaux SGBDR moteurs implicitement envelopper personne DML dans une transaction

    InformationsquelleAutor Quassnoi
  2. 8

    Si le verrouillage n'est pas fait correctement, il est certainement possible d'obtenir ce type de condition de course, et le défaut de verrouillage de mode (mode read committed) ne lui permettent. Dans ce mode, le lit ne placer un verrou partagé sur le dossier, afin qu'ils puissent voir les 0, l'incrémenter et écrire 1 à la base de données.

    Afin d'éviter cette condition de course, vous devez définir un verrou exclusif sur l'opération de lecture. 'Serializable" et "Repeatable Read' simultanéité des modes de faire, et pour une opération sur une seule ligne, ils sont à peu près équivalent.

    Pour le rendre complètement atomique, vous devez:

    • Des niveau d'isolation de transaction comme Serializable. Normalement, vous pouvez le faire à partir d'un client de la bibliothèque ou explicilty en SQL.
    • De commencer l'opération
    • Lire les données
    • Mise à jour il
    • De valider la transaction.

    Vous pouvez également forcer un verrou exclusif sur la lecture avec un HOLDLOCK (T-SQL), ou l'équivalent de l'indice, en fonction de votre dialecte SQL.

    Une seule requête de mise à jour va le faire automatiquement, mais vous ne pouvez pas diviser l'opération (peut-être à la lecture de la valeur et de la retourner au client) sans s'assurer que le lit de prendre un verrou exclusif. Vous aurez besoin d'obtenir la valeur atomiquement afin de mettre en œuvre une séquence, de sorte que la mise à jour par lui-même est probablement pas tout à fait tout ce que vous devez. Même avec l'atome de mise à jour, vous avez encore une course à condition de lire la valeur après la mise à jour. La lecture reste à prendre place au sein d'une transaction (stockage de ce qu'il a obtenu dans une variable) et émettre un verrou exclusif au cours de la lecture.

    Noter que pour ce faire sans la création d'un point chaud de votre base de données doit avoir un soutien adéquat pour autonome (imbriqué) transactions à l'intérieur d'une procédure stockée. Notez que, parfois, "imbriquées" est utilisé pour désigner le chaînage des transactions ou des points de sauvegarde, de sorte que le terme peut être un peu déroutant. J'ai édité ce à consulter les transactions autonomes.

    Sans les transactions autonomes vos serrures sont héritées par les parents de la transaction, ce qui peut faire reculer l'ensemble du lot. Cela signifie qu'ils seront tenus jusqu'à ce que le parent validation de transaction, qui peuvent transformer votre séquence dans un endroit chaud que serialises toutes les transactions à l'aide de cette séquence. Rien d'autre d'essayer d'utiliser la séquence de bloc jusqu'à ce que le parent validation de transaction.

    IIRC Oracle prend en charge les transactions autonomes mais DB/2 n'a pas jusqu'à assez récemment et SQL Server n'a pas. Sur le dessus de ma tête, je ne sais pas si InnoDB supporte, mais Gris et Reuter aller à une certaine longueur sur la façon dont ils sont difficiles à mettre en œuvre. Dans la pratique, je pense qu'il est tout à fait probable qu'il ne pourrait pas. YMMV.

    • Avec le seul UPDATE requête, il n'est pas possible d'obtenir cette condition de concurrence dans les principaux systèmes qui prennent en charge les transactions, peu importe le niveau d'isolation de transaction est utilisé.
    • Si je comprends bien le problème correctement, avoir de l'isolement n'est pas assez, SERIALIZABLE peut-être, mais peut-être pas l'isolement d'instantané (en.wikipedia.org/wiki/Snapshot_isolation), car je ne suis pas sûr de la définition de "conflit" dans ce cas. Sont les deux en conflit ou pas? Et même si vous avez un verrou exclusif sur la ligne pendant la mise à jour, si s2:s mise à jour vient entre s1:s mise à jour et de s'engager? Ce qui va s2 lire alors? Sans doute 0. La seule façon que je peux voir pour s'assurer du bon comportement dans ce cas serait de s1 à tenir le verrou exclusif jusqu'à ce que après sa validation.
    • Ce n'est d'une autre opération de lecture après la mise à jour et avant le commit alors? Si vous avez, par exemple, READ_COMMITED, laissant l'autre transaction de voir la valeur " 1 " serait erroné (serait de jeter un oeil dans les autres transactions). "0" est la seule autre valeur logique que les autres concurrentes de la transaction doivent être en mesure de voir. Donc, soit cette condition de course devrait être en mesure de se produire (et éventuellement suite à une faute), ou de la db doit en quelque sorte sérialiser les transactions.
    • si s1 détient le verrou exclusif, s2 ne peut pas mettre à jour.
    • S1 pour le bloquer S2 à l'étape de lecture afin de prévenir S1 et S2 de deux voir 0. La seule façon de le faire est de S1 à prendre une exclusive (intention de modifier) de verrouillage lors de la lecture, ce qui nécessite un Serializable ou niveau d'isolation Repeatable Read ou HOLDLOCK ou l'équivalent de l'indice sur la lecture. Même si enveloppé dans une transaction d'un verrou partagé ne permettra pas de prévenir une situation de concurrence où S1 et S2 peut à la fois lire 0 avant de S1 a écrit la valeur mise à jour.
    • un verrou exclusif empêche s2 à partir de la lecture jusqu'à ce que le verrou est libéré. Un verrou partagé n'empêchera pas la s2 de la lecture, mais bloque uniquement lorsque s2 tente de mettre à jour. Pour rendre la lecture atomique, S1 & S2 besoin de prendre un verrou exclusif pour l'opération de lecture.
    • après la mise à jour, une autre transaction va attendre pour la première opération de commettre.
    • un UPDATE requête ne pas placer des verrous partagés. Il lit toutes les données à l'aide de verrous de mise à jour.
    • La requête de mise à jour de verrouillage, les enregistrements, quel que soit le niveau d'isolation, mais vous ne serez pas en mesure de récupérer les données pendant. c'est à dire que vous ne pouvez pas voir ce que c'était d'afficher un nombre de pages.
    • récupérer des données où?
    • Il veut mettre en place quelque chose comme des séquences, ce qui implique un désir d'obtenir la valeur actuelle de la séquence de manière atomique. Juste la mise à jour de l'enregistrement in situ ne va pas l'aider à le faire à moins qu'il puisse obtenir le numéro de séquence et de la mettre à jour de manière atomique. L'instruction de mise à jour par lui-même est peut-être pas le bon exemple pour aller avec sa demande.
    • rien dans la question mentionne ce désir. De toute façon, UPDATE prend en charge RETURNING clause SQL Server 2005 et au-dessus.
    • Oui, il n': 'depuis que je suis en train d'utiliser cette technique pour mettre en œuvre des séquences sur InnoDB.' Otoh, que je ne sais pas si InnoDB prend en charge le RETOUR. Cependant, sur SQL Server à l'intérieur transactions seront encore accroché dans le contexte du parent, ce qui peut les transformer en un hot spot.

    InformationsquelleAutor ConcernedOfTunbridgeWells