MS SQL Server
  1. MS SQL Server
  3. Blocage SQL Server 2005 avec index non cluster
Intereting Posts
Filtrage des résultats en fonction de plusieurs relations Ajout de colonne rowguid a cassé cette procédure stockée? Requête SQL pour un nombre inconnu de mots-keys Comment changer les raccourcis keyboard par défaut pour SSMS Comment get toutes les keys primaires avec un nom spécifique? Exemple de classment de text intégral SQL Server Sélectionner l'identité d'étendue de Sql UPDATE et SELECT vers / depuis la même table Comment comparer une valeur de zone de list déroulante avec une valeur de table stockée dans SQL Server Soustrayant deux colonnes dans la requête sql comment split les valeurs séparées par des virgules et les stocker dans la database EXEC %% System (). FireTrigger ADO stocké Proc manquant Erreur de paramètre … mssql convertit la structure xml en table Comment concaténer le "débordement" de champs avec des limites de caractères

Blocage SQL Server 2005 avec index non cluster

Quelqu'un peut-il m'aider pour un blocage dans SQL Server 2005?

Pour un test simple, j'ai une table "Book" qui a une key primaire (id), et un nom de colonne. L'index par défaut de cette key primaire est non cluster .

L'impasse se produit lorsque deux sessions se déroulent en même time. Le moniteur d'activité montre la première session "// étape 1" verrouille la ligne (verrou rid) avec X lock. La deuxième session garde la ligne U lock et la key U lock. L'image de l'impasse montre que "// step2" de la première session nécessite le locking de la key U.

Si l'index est en cluster , il n'y a aucun blocage dans ce cas. "// étape 1" conservera la ligne et le locking des touches en même time, donc il n'y a pas de problème. Je peux comprendre que le locking d'une ligne verrouillera également l'index puisque le noeud feuille de l'index clusterisé est une ligne de données.

Mais, pourquoi l'index non cluster est de cette manière? Si la deuxième session contient la key U, pourquoi "l'étape 1" de la première session ne contient pas ce verrou car ils sont identiques à l'instruction de mise à jour. 

--// first session BEGIN TRAN update Book set name = name where id = 1 //step 1 WaitFor Delay '00:00:20' update Book set name = 'trans' where id = 1 //step2 COMMIT --// second session BEGIN TRAN --// this statement will keep both RID(U lock) and KEY(U lock) if first session did not use HOLDLOCK update Book set name = name where id = 1 COMMIT

Le facteur pertinent ici est que vous utilisez une colonne dans votre clause where qui a un index non cluster. Lorsque SQL Server traite une mise à jour, cela ressemble à ceci:

  1. Trouver des lignes à mettre à jour, en prenant des verrous U sur datatables touchées
  2. Mettre à jour les lignes, en prenant X verrous sur datatables modifiées

Une fois l'instruction terminée (sous l'isolation READ COMMITTED par défaut), les verrous U sont libérés mais les verrous X sont conservés jusqu'à la fin de la transaction pour maintenir l'isolation.

Dans votre situation d'index non cluster, SQL Server search sur l'index sur ID et l'utilise pour searchr la ligne réelle. Le locking joue comme ceci:

  1. (Session 1, étape 1) U verrouillé sur la valeur de la key d'index pour id = 1
  2. (Session 1, étape 1) X lock pris sur RID pour row avec id = 1
  3. (Session 1, étape 1) U verrouillé libéré
  4. (Session 2) U verrouillé sur la valeur de la key d'index pour id = 1
  5. (Session 2) X lock bloqué pour RID pour row avec id = 1
  6. (Session 1, étape 2) U verrouillé sur la valeur de la key d'index pour id = 1 – DEADLOCK

Toutefois, lorsque l'index est l'index cluster, il n'existe pas une étape distincte pour convertir la key d'index dans la ligne – la valeur d'index cluster est l'identificateur de ligne. Par conséquent, le locking se termine comme ceci:

  1. (Session 1, étape 1) U verrouillé sur la valeur de la key d'index pour id = 1
  2. (Session 1, étape 1) Verrou U mis à niveau vers X lock
  3. (Session 2) U verrouillé sur la valeur de la key d'index pour id = 1
  4. (Session 1, étape 2) verrou déjà maintenu sur la valeur de la key d'index pour id = 1
  5. (Session 1, commit) verrouillé libéré
  6. (Session 2) U verrouillé accordé
  7. (Session 2) U verrou mis à jour vers X locking
  8. (Session 2) verrou libéré

Comme toujours, gardez à l'esprit que, bien que cela puisse être le plan de requête utilisé dans ce cas, l'optimiseur est libre de faire les choses différemment. Par exemple, il peut choisir une parsing de table ou supprimer des verrous plus grossiers. Dans ces cas, l'impasse peut ne pas se produire.

Ce lien a beaucoup de suggestions utiles: deadlocks SQL Server entre sélection / mise à jour ou plusieurs sélections .

Voici quelques points à considérer qui pourraient aider les gens à répondre à votre question:

  1. Quel niveau d'isolation de transaction utilisez-vous?
  2. L'escalade de la serrure (par exemple d'une ligne à l'autre) est-elle autorisée?
  3. Y a-t-il un index sur la colonne 'nom'?

Votre première mise à jour ne modifie en réalité rien: 

 update Book set name = name where id = 1

Thy une command qui change réellement votre colonne, puis un verrou exclusif aura lieu sur la ligne.