Supposons que ma requête de mise à jour ressemble à ce qui suit:
SELECT a, b, c
INTO
FROM tableA
INNER JOIN tableB b WITH (NOLOCK)
ON a.colA= b.colB
INNER JOIN tableC c WITH (NOLOCK)
ON c.colC= a.colA
UPDATE a SET a.colSomething=1
FROM tableA a INNER JOIN #tmp t ON a.colA= t.colA
3 Réponses :
Je mettrais des index sur vos clés étrangères, il peut accélérer la mise à jour et supprimer des opérations ainsi que pour soulager votre situation d'impasse.
Blocage vs Verrouillage mort
Je pense que vous pouvez perfuser le verrouillage et le blocage avec des blocages.
sur n'importe quelle requête de mise à jour SQL Server verrouille les données concernées. Bien que ce verrou soit actif, d'autres processus seront bloqués (retardés) de modifier les données. Si la mise à jour originale prend beaucoup de temps (du point de vue des utilisateurs 'comme quelques secondes), le système d'extrémité avant peut sembler "suspendre" ou même le délai d'attente d'un processus avant d'utilisateurs et signaler une erreur.
Ce n'est pas une impasse. Ce blocage se résoudra, fondamentalement de manière non destructive en retardant légèrement l'utilisateur ou dans certains cas en forçant l'extrémité avant pour être intelligente sur le délai d'attente. Dans le problème, le problème est de bloquer en raison de mises à jour longues, vous pourriez corriger les utilisateurs qui devaient soumettre à nouveau en augmentant le délai d'attente avant.
Une impasse ne peut toutefois pas être résolue, peu importe combien vous augmentez le délai d'attente. Un ou les processus seront terminés avec des préjugés (perdre la mise à jour).
Les blocages ont des causes profondes différentes que de bloquer. Les blocages sont généralement causés par une logique séquentielle incohérente à l'extrémité avant, qui accède et verrouille des données de deux tables dans différentes commandes dans deux parties différentes de l'extrémité avant. Lorsque ces deux parties fonctionnent simultanément dans un environnement multi-utilisateurs, elles peuvent fondamentalement, de manière non déterministe, provoquent des blocages et une perte de données essentiellement insoluble (jusqu'à ce que la cause des blocages soit résolue) par opposition à la blocage qui puisse généralement être traitée.
SQL Server choisira Server la ligne ou verrouillage de la table entière?
Généralement, cela dépend et pourrait être différent à chaque fois. Selon le nombre de lignes, la requête optimiseur détermine sera affectée, la serrure peut être une ligne ou une table. Si c'est sur un certain seuil, il ira table car ce sera plus rapide.
Comment puis-je réduire le blocage tout en adhérant aux principes de base de l'intégrité transactionnelle?
SQL Server va tenter de verrouiller les tables que vous rejoignez car leur contenu est important pour générer le jeu de résultats qui est mis à jour. Vous devriez pouvoir afficher un plan d'exécution estimé pour la mise à jour pour voir ce qui sera verrouillé en fonction de la taille des tables d'aujourd'hui. Si la serrure prédite est la table, vous pouvez remplacer peut-être un indice de verrouillage de ligne, mais cela ne garantit pas de blocage. Il peut réduire les chances de bloquer par inadvertance des données éventuellement non liées dans le tableau. Vous obtiendrez toujours toujours le blocage des données directement du matériel à la mise à jour.
garder à l'esprit, cependant;
Gardez également à l'esprit que les serrures prises sur la table jointe seront des serrures partagées. Signification Les autres processus peuvent toujours lire ces tables, ils ne peuvent tout simplement pas les mettre à jour, jusqu'à ce que votre mise à jour soit effectuée en utilisant comme référence. En revanche, d'autres processus bloqueront activement de tenter de simplement lire les données que vous mettrez à jour disposent d'une serrure exclusive (la table principale étant mise à jour).
Merci pour l'explication. Yup, je cherchais comment la déclaration de mise à jour ci-dessus provoquera un blocage inutile ... Je me demandais si SQL Server utilisera une serrure de ligne ou une serrure de table pour le scénario de jointure ci-dessus?
Tout cela dans cet article, mais en général, cela dépend et pourrait être différent à chaque fois. Selon le nombre de lignes, la requête optimiseur détermine sera affectée, la serrure peut être une ligne ou une table. Si c'est sur un certain seuil, il ira Table car ce sera plus rapide.
En outre, en mettant toutes les données dans une table externe, vous vaincuez le but du moteur de verrouillage. Vous faites vos propres décisions de concurrence, de manière très ad hoc. Une manière qui est certainement mieux représentée en utilisant les notes de verrouillage existantes et les niveaux d'isolation.
Je vois. Mais si cela ne me dérange pas de lire des lectures sales, il est plus sûr de mettre des données dans une table externe afin d'éviter de bloquer le droit?
Je pense que vous êtes peut-être sous-estimé les conséquences des lectures sales. Une lecture sale typique donnera à l'utilisateur une version de retour éventuellement roulée d'un champ. Il appartient à l'utilisateur de prendre une décision sur la base de ces informations, et si vous les avez informés de la stratégie, ils pourraient envisager de regarder temporairement (données qui ont ensuite été renvoyées par la suite) de données à traiter. Mais dans ce cas, vous dites au serveur SQL de faire une mise à jour automatique en fonction des données. Par exemple, que si quelqu'un supprime la ligne dans l'une des tables jointes pendant que vous mettez à jour, vous courez la table TEMP.
La ligne qui jointe précédemment à la ligne supprimée doit être laissée hors de la mise à jour de statut!
Je comprends les risques d'avoir des lectures sales. Mais mettre de côté, je voudrais toujours savoir quel est le risque de blocage de jointures dans la mise à jour car le blocage est parfois plus préoccupé que la lecture sale.
SQL Server va tenter de verrouiller les tables que vous rejoignez, car leur contenu est important pour générer le jeu de résultats qui est mis à jour. Vous devriez pouvoir afficher un plan d'exécution estimé pour la mise à jour pour voir ce qui sera verrouillé en fonction de la taille des tables d'aujourd'hui. Si la serrure prédite est la table, vous pouvez remplacer peut-être un indice de verrouillage de ligne, mais cela ne garantit pas de blocage. Il peut réduire les chances de bloquer par inadvertance des données éventuellement non liées dans le tableau. Vous obtiendrez toujours toujours le blocage des données directement du matériel à la mise à jour.
Je vois, ça a du sens. Pourriez-vous modifier votre réponse un peu afin que je puisse l'accepter?
Gardez également à l'esprit que les serrures prises sur la table jointe seront des serrures partagées. Signification Les autres processus peuvent toujours lire ces tables, ils ne peuvent tout simplement pas les mettre à jour, jusqu'à ce que votre mise à jour soit effectuée en utilisant comme référence. En revanche, d'autres processus bloqueront activement d'essayer de lire simplement des données que vous mettrez à jour disposent d'une serrure exclusive (la table principale étant mise à jour).
Peut-être en ajoutant ces blocs sur la serrure de la table pendant la jointure? Je pense que cela est plus pertinent pour la conversation.
Ok je pense que c'est bien. Voyez ce que vous pensez et laissez supprimer le reste de ces TET TETES
J'ai eu exactement le même problème à essayer de mettre à jour une table avec des enregistrements de 800K joints à une autre table avec 10 conditions de jointure. Cette mise à jour a pris 30 minutes de plus de 30 minutes.
J'ai réduit à 8 secondes en créant une table Temp qui contenait uniquement les rangées qui devaient être mises à jour. Ensuite, j'ai mis à jour la première table avec ces résultats, seules 20 000 lignes réelles devaient être mises à jour. La commande SELECT par défaut ne se connecte pas, et je pense (mais je ne suis pas sûr), lorsque vous créez une table temporaire avec un SELECT dans, il n'est pas non plus connecté (quelqu'un s'il vous plaît confirmer).
Lors de la publication d'une mise à jour sur une grande table, vous avez une autre grande table, vous enregistrez chaque champ mis à jour sur le journal des transactions, puis recherchez le prochain candidat disponible, puis connectez-vous à nouveau et recherchez. Si vous pouvez accepter une lecture sale, créez une table TEMP avec uniquement les enregistrements qui seront mis à jour, vous réduirez considérablement votre temps de mise à jour et donc vos chances d'une impasse.
Il est également important de supprimer toutes les fonctions de votre lieu, telles que des comparaisons de chaîne ISnull ou même calculées. Ceux-ci peuvent augmenter radicalement votre heure de mise à jour.
C'est beaucoup plus complexe que ce que vous faites. Vous devez probablement probablement passer du temps à lire sur Cet article .
Mais au niveau supérieur, je pense qu'il devrait exister une explication sur l'approche de SQL Server dans une mise à jour comme celle-là? J'ai cherché autour du web mais je n'ai pas trouvé de bonne ressource pour cela. Mais votre lien offre une excellente couverture sur les serrures.
N'utilisez pas avec (nolock) ici. Vous demandez des lectures sales, ce qui est le contraire de la plus sûre que vous visez.
Merci d'avoir fait remarquer cela. Je viens de changer le libellé un peu pour mieux répondre à ma préoccupation.