|
|
Publié sur 20/07/2016 12:37:53
|
|
|
Aperçu des écluses
1. Pourquoi introduire des serrures
Lorsque plusieurs utilisateurs effectuent simultanément des opérations sur la base de données, les incohérences de données suivantes surviennent :
Mises à jour manquantes
Deux utilisateurs, A et B, lisent les mêmes données et les modifient, et le résultat de la modification d’un utilisateur détruit celui de l’autre, comme le système de réservation de billets
Lecture salace
L’utilisateur A modifie les données, puis l’utilisateur B lit les données, mais l’utilisateur A annule la modification des données pour une raison quelconque, et les données reviennent à leur valeur initiale
Ne pas lire à répétition
L’utilisateur A lit les données, puis l’utilisateur B les lit et les modifie
La principale méthode de contrôle de la concurrence est le blocage, qui consiste à interdire aux utilisateurs d’effectuer certaines opérations pendant un certain temps afin d’éviter des incohérences de données
2. Classification des écluses
Il existe deux divisions en catégories d’écluses :
1 . Du point de vue du système de base de données : il est divisé en verrous exclusifs (c’est-à-dire verrous exclusifs), verrous partagés et verrous de mise à jour
MS - SQL Server utilise les schémas de verrouillage de ressources suivants.
Description du mode de verrouillage
Le ou les partages sont utilisés pour les opérations qui ne modifient ni ne mettent à jour les données (opérations en lecture seule), telles que les instructions SELECT.
La mise à jour (U) est utilisée dans les ressources à mise à jour. Évite les formes courantes d’impasses lorsque plusieurs sessions sont lues, verrouillées, et éventuellement une mise à jour de ressources qui peut survenir.
Exclusive (X) est utilisé pour les opérations de modification des données, telles que INSERT, UPDATE ou DELETE. Assurez-vous que plusieurs mises à jour ne sont pas effectuées simultanément sur la même ressource.
Les verrous d’intention servent à établir une hiérarchie de verrous. Les types de verrouillages d’intention sont : Intention Partagée (IS), Intention Exclusive (IX) et Intention Exclusive (SIX).
Les verrous de schéma sont utilisés lors d’opérations dépendant du schéma de la table. Les types de verrous de schéma sont : modification de schéma (Sch -M) et stabilité de schéma (Sch -S).
Les mises à jour en masse (BU) sont utilisées lorsque de grands volumes de données sont copiés dans une table et qu’un indice TABLOCK est spécifié.
Serrures partagées
Un verrou partagé permet à des transactions concurrentes de lire (SELECT) une ressource. Lorsqu’un verrou partagé (S) existe sur une ressource, aucune autre transaction ne peut modifier les données. Libérez le verrou partagé (S) sur la ressource dès que les données ont été lues, sauf si le niveau d’isolation de la transaction est réglé sur répétabilité ou supérieur, ou que le verrou partagé (S) est conservé avec un indice de verrouillage pendant toute la durée de vie de la transaction.
Verrouillage de mise à jour
Les verrous de mise à jour (U) empêchent les blocages dans leur forme habituelle. Un schéma de mise à jour typique consiste en une transaction qui lit un enregistrement, obtient un verrou partagé (S) pour une ressource (page ou ligne), puis modifie une ligne, ce qui nécessite que le verrou soit converti en un verrou exclusif (X). Si deux transactions acquièrent un verrou en mode partagé sur une ressource puis tentent de mettre à jour les données en même temps, une transaction tente de convertir le verrou en un verrou exclusif (X). La transition du mode partagé au verrou exclusif doit attendre un certain temps car le verrou exclusif d’une transaction n’est pas compatible avec le verrou en mode partagé d’une autre transaction ; Une attente de verrouillage a lieu. La seconde transaction tente d’obtenir un verrou exclusif (X) pour une mise à jour. Un blocage se produit parce que les deux transactions sont converties en verrous exclusifs (X), et chaque transaction attend que l’autre transaction libère le verrou en mode partagé.
Pour éviter ce problème potentiel de blocage, utilisez un verrou (U) mis à jour. Une seule transaction à la fois peut obtenir un verrou (U) mis à jour pour une ressource. Si la transaction modifie la ressource, le verrou de mise à jour (U) est converti en un verrou exclusif (X). Sinon, la serrure est convertie en serrure partagée.
Serrures exclusives
Les verrous exclusifs (X) empêchent les transactions concurrentes d’accéder aux ressources. Les autres transactions ne peuvent ni lire ni modifier les données verrouillées par le verrou exclusif (X).
Verrouillage d’intention
Un verrou d’intention indique que SQL Server doit acquérir un verrou partagé (S) ou un verrou exclusif (X) sur certaines ressources sous-jacentes dans la hiérarchie. Par exemple, un verrou à intention de partage placé au niveau de la table indique que la transaction vise à placer un verrou de partage sur une page ou une ligne du tableau. Définir un verrou d’intention au niveau de la table empêche une autre transaction d’acquérir par la suite un verrou exclusif (X) sur la table contenant cette page. Les verrous d’intention peuvent améliorer les performances car SQL Server ne vérifie le verrou d’intention qu’au niveau de la table pour déterminer si une transaction peut acquérir un verrou en toute sécurité sur cette table. Au lieu de vérifier les verrous sur chaque ligne ou page du tableau pour déterminer si une transaction peut verrouiller toute la table.
Les verrous d’intention incluent le partage d’intention (IS), l’exclusivité à l’intention (IX) et le partage exclusif à l’intention (SIX).
Description du mode de verrouillage
Le partage d’intention (IS) indique que l’intention de la transaction concerne certaines, et non toutes, les ressources sous-jacentes de la hiérarchie de lecture en plaçant des verrous S sur chaque ressource.
Intent Exclusive (IX) indique que l’intention de la transaction est de modifier certaines, mais pas toutes, des ressources sous-jacentes de la hiérarchie en plaçant un X-lock sur chaque ressource. IX est un super-ensemble de l’IS.
Le partage exclusif avec intention (SIX) indique que l’intention de la transaction est de lire toutes les ressources sous-jacentes de la hiérarchie et de modifier certaines, mais pas toutes, des ressources sous-jacentes en plaçant des verrous IX sur chaque ressource. Autoriser les verrouillages IS simultanés sur les ressources de haut niveau. Par exemple, le verrou SIX d’une table place un verrou SIX sur la table (autorisant des verrous IS concurrents) et un verrou IX sur la page modifiée actuellement (un verrou X sur la ligne modifiée). Bien que chaque ressource ne puisse avoir qu’un seul verrou SIX pendant une période de temps pour empêcher d’autres transactions de mettre à jour la ressource, d’autres transactions peuvent lire les ressources sous-jacentes dans la hiérarchie en acquérant des verrous IS au niveau de la table.
Verrouillage exclusif : Seul le programme qui effectue l’opération de verrouillage est autorisé à l’utiliser, et toute autre opération ne sera pas acceptée. Lorsque vous exécutez une commande de mise à jour des données, SQL Server utilise automatiquement un verrou exclusif. Lorsque d’autres verrous existent sur un objet, vous ne pouvez pas ajouter de verrou exclusif.
Verrou partagé : La ressource verrouillée par le verrou partagé peut être lue par d’autres utilisateurs, mais les autres ne peuvent pas la modifier.
Verrouillage de mise à jour : Lorsque SQL Server est prêt à mettre à jour les données, il verrouille d’abord l’objet de données afin que les données ne puissent pas être modifiées mais lues. Lorsque SQL Server détermine qu’il souhaite mettre à jour les données, il remplacera automatiquement le verrou de mise à jour par un verrou exclusif, et ne peut pas ajouter de verrou de mise à jour lorsque d’autres verrous existent sur l’objet.
2 . Du point de vue du programmeur : il se divise en verrouillage optimiste et verrouillage pessimiste.
Verrouillage Optimisme : Repose entièrement sur la base de données pour gérer le fonctionnement de la serrure.
Verrous pessimistes : Les programmeurs gèrent la gestion des verrous sur les données ou les objets eux-mêmes.
MS - SQLSERVER utilise des verrous pour implémenter un contrôle pessimiste de la concurrence entre plusieurs utilisateurs qui effectuent des modifications simultanément dans la base de données
3. La taille des particules de la serrure
La granularité du blocage correspond à la taille de la cible bloquée, la petite granularité du blocage est à une forte concurrence, mais la surcharge est importante, et la grande granularité au blocage est faible en concurrence mais la surcharge est faible
SQL Server prend en charge la granularité des verrous pour les lignes, pages, clés, plages de clés, index, tables ou bases de données
Description de la ressource
Identifiant de ligne RID. On avait l’habitude de verrouiller une rangée individuellement dans une table.
Verrouillage de la rangée de clés dans l’index. Utilisé pour protéger la plage de clés dans les transactions sérialisables.
8 kilooctets (Ko) de pages de données ou de pages d’index.
Disque étendu Un ensemble de huit pages de données ou pages d’index adjacentes.
Tableau : Le tableau complet incluant toutes les données et les index.
Base de données DB.
4. La durée du temps de verrouillage
La durée de la conservation d’un éclus correspond au temps nécessaire pour protéger la ressource au niveau demandé.
Le temps de conservation du verrou partagé utilisé pour protéger les opérations de lecture dépend du niveau d’isolation de la transaction. Avec le niveau d’isolation par défaut de la transaction READ COMMITTED, le verrou partagé est contrôlé uniquement pendant la durée de la page de lecture. Lors d’un scan, le verrou n’est pas libéré tant qu’il n’est pas acquis à la page suivante du scan. Si vous spécifiez une invite HOLDLOCK ou réglez le niveau d’isolation de la transaction sur LECTURE RÉPÉTABLE ou SÉRIALISABLE, le verrou n’est libéré qu’à la fin de la transaction.
Selon l’option de concurrence définie pour le curseur, celui-ci peut acquérir un verrouillage de défilement en mode partagé pour protéger l’extraction. Lorsqu’un verrou défilement est nécessaire, celui-ci n’est libéré qu’au prochain extrait ou fermé du curseur, selon ce qui arrive en premier. Cependant, si vous spécifiez un VERROUILLAGE, le verrouillage de défilement n’est libéré qu’à la fin de la transaction.
Le verrou exclusif utilisé pour protéger la mise à jour ne sera libéré qu’à la fin de la transaction.
Si une connexion tente d’acquérir un verrou qui entre en conflit avec un verrou contrôlé par une autre connexion, la connexion qui tente d’acquérir le verrou sera bloquée jusqu’à ce que :
Le verrou en conflit est libéré et la connexion acquiert le verrou demandé.
Le délai d’attente de la connexion est expiré. Il n’y a pas d’intervalle d’expiration par défaut, mais certaines applications définissent des intervalles de temps d’attente pour éviter l’attente indéfinie
Cinq personnalisations des verrous dans SQL Server
1 Gérer les blocages et définir les priorités d’impasse
Le blocage est l’attente interminable causée par plusieurs utilisateurs qui postulent pour des blocages différents, car le demandeur détient une partie du droit de blocage et attend le blocage partiel détenu par d’autres utilisateurs
Vous pouvez utiliser le DEADLOCK_PRIORITY SET pour contrôler la réaction de la session en cas de blocage. Si les deux processus verrouillent les données, et que chaque processus ne peut pas libérer son propre verrou tant que l’autre processus n’a pas libéré le sien, une situation d’impasse se produit.
2 Gérer les délais d’attente et définir les durées de verrouillage.
@@LOCK_TIMEOUT Rétablit le réglage d’expiration actuel de verrouillage pour la session en cours en millisecondes
Le paramètre SET LOCK_TIMEOUT permet à l’application de définir le temps maximum d’attente que l’instruction attend pour bloquer la ressource. Lorsque le temps d’attente de l’instruction dépasse le réglage LOCK_TIMEOUT, le système annule automatiquement l’instruction de blocage et renvoie à l’application un message d’erreur de 1222 indiquant que le délai de délai de la demande de verrouillage a été dépassé
exemple
Dans l’exemple suivant, la période d’expiration du verrouillage est fixée à 1 800 millisecondes.
SET LOCK_TIMEOUT1800
3) Définir le niveau d’isolation des transactions.
4) Utiliser des indices de verrouillage au niveau de la table pour les instructions SELECT, INSERT, UPDATE et DELETE.
5) Configurer la granularité de verrouillage de l’index
Vous pouvez utiliser sp_indexoption procédures stockées système pour définir la granularité du verrou pour l’indexation
6. Voir les informations de la serrure
1 Effectuer un rapport EXEC SP_LOCK informations concernant la serrure
2 Appuyez sur Ctrl + 2 dans l’analyseur de requêtes pour voir les informations du verrou
7. Précautions d’utilisation
Comment éviter les blocages
1. Lors de l’utilisation de transactions, essayez de raccourcir le processus de traitement logique des transactions, et soumettez ou annulez les transactions plus tôt.
2 Régler le paramètre de délai d’impasse à une plage raisonnable, telle que : 3 minutes - 10 minutes ; Après ce temps, l’opération sera automatiquement abandonnée pour éviter que le processus ne se bloque ;
3. Optimiser le programme, vérifier et éviter le phénomène de blocage ;
4. Tester attentivement tous les scripts et SP avant la version exacte.
5 Tous les SP doivent avoir une gestion des erreurs (via @error)
6 Ne modifiez pas le niveau par défaut des transactions SQL SERVER. Le verrouillage forcé n’est pas recommandé
Résoudre le problème Comment verrouiller une base de données à table de lignes
8. Plusieurs questions sur les serrures
1 Comment verrouiller une rangée d’une table
NIVEAU TRANSACTIONISOLATION REDÉFINI NON ENGAGÉ
SELECT *FROM table ROWLOCKWHERE id = 1
2 Verrouiller une table dans la base de données
SELECT *FROM table AVEC( MAINTENEZ le verrouillage )
Déclaration de verrouillage :
sybase:
mettre à jour la table col1 = col1 où 1 = 0 ;
MSSQL:
sélectionner col1à partir du tableau (tablockx)où 1= 0 ;
oracle:
TABLE DE VERROUILLAGE EN MODE EXCLUSIF ;
Après le verrouillage, personne d’autre ne peut l’utiliser tant que l’utilisateur verrouillé ne le déverrouille pas, et il est déverrouillé par commit ou rollback
Quelques exemples vous aideront à approfondir votre impression
Table de dressage1(A,B,C)
A B C
a1 b1 c1
A2 B2 C2
A3 B3 C3
1) Verrouillage exclusif
Créer deux nouvelles connexions
Exécutez l’instruction suivante dans la première connexion
Début de la trance
Tableau de mise à jour 1
Ensemble A= ' aa '
où B= ' b2 '
attends un retard 00:00:30' --attends 30 secondes
commit tran
Exécutez l’instruction suivante dans la seconde connexion
Début de la trance
select *depuis table1
où B= ' b2 '
commit tran
Si les deux instructions ci-dessus sont exécutées simultanément, la requête select doit attendre l’exécution de la mise à jour, c’est-à-dire attendre 30 secondes
2) Verrou partagé
Exécutez l’instruction suivante dans la première connexion
Début de la trance
select *from table1 holdlock - Le holdlock est artificiellement ajouté au verrou
où B= ' b2 '
attends un retard 00:00:30' --attends 30 secondes
commit tran
Exécutez l’instruction suivante dans la seconde connexion
Début de la trance
sélectionnez A,C dans le tableau1
où B= ' b2 '
Tableau de mise à jour 1
Ensemble A= ' aa '
où B= ' b2 '
commit tran
Si les deux instructions ci-dessus sont exécutées en même temps, la requête select dans la seconde connexion peut être exécutée
La mise à jour doit attendre la première transaction pour libérer le verrou partagé et le convertir en verrou exclusif avant de pouvoir être exécutée, c’est-à-dire attendre 30 secondes
3) Impasse
Ajout du tableau2(D,E)
D E
d1 e1
d2 e2
Exécutez l’instruction suivante dans la première connexion
Début de la trance
Tableau de mise à jour 1
Ensemble A= ' aa '
où B= ' b2 '
attends un retard 00:00:30'
Tableau de mise à jour2
Ensemble D= ' d5'
où E = ' e1 '
commit tran
Exécutez l’instruction suivante dans la seconde connexion
Début de la trance
Tableau de mise à jour2
Ensemble D= ' d5'
où E = ' e1 '
attends un retard 00:00:10'
Tableau de mise à jour 1
Ensemble A= ' aa '
où B= ' b2 '
commit tran
En même temps, le système détecte l’impasse et abandonne le processus
Pour ajouter :
Astuces de verrouillage au niveau de table prises en charge par SQL Server 2000
HOLDLOCK conserve le verrou partagé jusqu’à la fin de la transaction complète et doit être libéré dès que l’objet verrouillé n’est plus nécessaire, soit le niveau d’isolation de la transaction SÉRIALISABLE
L’instruction NOLOCK est exécutée sans émettre de verrou partagé, permettant des lectures sale, qui correspondent au niveau d’isolation de la transaction READ UNCOMMITTED
PAGLOCK utilise plusieurs verrous de page où un seul verrou de table est utilisé
READPAST permet au serveur SQL de sauter toutes les lignes verrouillées et d’exécuter des transactions, et pour les niveaux d’isolement des transactions READ NON engagés, ne sauter que les verrous RID, pas les verrous de page, de zone et de table
ROWLOCK impose l’utilisation des rowlocks
TABLOCKX impose l’utilisation d’un verrou exclusif au niveau de la table, qui empêche toute autre transaction d’utiliser la table pendant la transaction
UPLOCK impose l’utilisation de mises à jour lors de la lecture d’une table sans verrou partagé
Verrouillage de l’application :
Un verrou d’application est un verrou généré par le code client, et non un verrou généré par SQL Server lui-même
Deux processus pour gérer les verrous d’application
sp_getapplock Verrouiller les ressources de l’application
sp_releaseapplock Déverrouiller les ressources de l’application
Note : La différence entre verrouiller une table dans une base de données
SELECT *FROM table avec( HOLDLOCK ) D’autres transactions peuvent lire la table, mais ne peuvent ni les mettre à jour ni les supprimer
SELECT *FROM table WITH(TABLOCKX) Les autres transactions ne peuvent pas lire, mettre à jour et supprimer la table
|
Précédent:Il n’y avait pas de point final à l’écoute à http://localhost:111/xxx.svc que c...Prochain:Verrous SQL NOLOCK, HOLDLOCK, UPDLOCK, TABLOCKX, TABLOCKX
|
