Blocage provoqué par une instruction SELECT JOIN avec SQL Server

Lors de l'exécution d'une instruction SELECT avec une jointure de deux tables, SQL Server semble verrouiller les deux tables de l'instruction individuellement. Par exemple par une requête comme ceci:

SELECT ... FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE ...

J'ai découvert que l'ordre des verrous dépend de la condition WHERE. L'optimiseur de requête essaie de produire un plan d'exécution qui lit uniquement autant de lignes que nécessaire. Donc, si la condition WHERE contient une colonne de table1, elle obtiendra d'abord les lignes de résultats de table1 et ensuite les lignes correspondantes de table2. Si la colonne provient de la table 2, elle le fera dans l'autre sens. Des conditions plus complexes ou l'utilisation d'index peuvent également avoir un effet sur la décision de l'optimiseur de requête.

Lorsque datatables lues par une instruction doivent être mises à jour plus tard dans la transaction avec les instructions UPDATE, il n'est pas garanti que l'ordre des instructions UPDATE corresponde à l'ordre utilisé pour lire datatables des deux tables. Si une autre transaction essaie de lire des données pendant qu'une transaction met à jour les tables, elle peut provoquer un blocage lorsque l'instruction SELECT est exécutée entre les instructions UPDATE car ni le SELECT ne peut get le verrou sur la première table ni le verrou sur UPDATE la deuxième table. Par exemple:

 T1: SELECT ... FROM ... JOIN ... T1: UPDATE table1 SET ... WHERE id = ? T2: SELECT ... FROM ... JOIN ... (locks table2, then blocked by lock on table1) T1: UPDATE table2 SET ... WHERE id = ?

Les deux tables représentent une hiérarchie de types et sont toujours chargées set. Il est donc logique de charger un object en utilisant un SELECT avec un JOIN. Le chargement des deux tables individuellement ne donnerait pas à l'optimiseur de requête une chance de find le meilleur plan d'exécution. Mais puisque les instructions UPDATE ne peuvent mettre à jour qu'une seule table à la fois, cela peut provoquer des blocages lorsqu'un object est chargé alors que l'object est mis à jour par une autre transaction. Les mises à jour d'objects provoquent souvent des UPDATE sur les deux tables lorsque les propriétés de l'object qui appartiennent à différents types de la hiérarchie de type sont mises à jour.

J'ai essayé d'append des indications de locking à l'instruction SELECT, mais cela ne change pas le problème. Il provoque simplement le blocage dans les instructions SELECT lorsque les deux instructions tentent de verrouiller les tables et une instruction SELECT obtient le verrou dans l'ordre inverse de l'autre instruction. Peut-être qu'il serait possible de charger datatables pour les mises à jour toujours avec la même instruction forçant les verrous à être dans le même ordre. Cela empêcherait un blocage entre deux transactions qui souhaitent mettre à jour datatables, mais n'empêcherait pas une transaction qui lit uniquement datatables dans un interblocage qui doit avoir des conditions WHERE différentes.

Le seul work-a-round donc ceci semble être que les lectures peuvent ne pas avoir de verrous du tout. Avec SQL Server 2005, cela peut être fait en utilisant SNAPSHOT ISOLATION. La seule façon pour SQL Server 2000 serait d'utiliser le niveau d'isolation READ UNCOMMITED.

Je voudrais savoir s'il existe une autre possibilité d'empêcher le SQL Server de provoquer ces interblocages?

Cela ne se produira jamais sous l'isolement d'instantané, quand les lecteurs ne bloquent pas les auteurs. À part cela, il n'y a aucun moyen d'empêcher de telles choses. J'ai écrit beaucoup de scripts de repro ici: Reproduire des blocages impliquant une seule table

Modifier:

Je n'ai pas access à SQL 2000, mais j'essayerais de sérialiser l'access à l'object en utilisant sp_getapplock, afin que la lecture et les modifications ne soient jamais exécutées simultanément. Si vous ne pouvez pas utiliser sp_getapplock, déployez votre propre mutex.

Une autre façon de résoudre ce problème consiste à split le select … de … join en plusieurs instructions select. Définissez le niveau d'isolation sur read committed. Utilisez la variable table pour canaliser datatables de select à joindre à other. Utilisez distinct pour filterr les insertions dans ces variables de table.

Donc, si j'ai deux tables A, B. Je suis en train d'insert / mettre à jour dans A et ensuite B. Où l'optimiseur de requêtes de sql préfère lire B d'abord et A. Je vais séparer le select unique en 2 sélections. Je vais d'abord lire B. Ensuite, transmettez ces données à l'instruction select suivante qui lit A.

Ici l'impasse ne se produira pas parce que les verrous de lecture sur la table B seront libérés dès que la première déclaration est faite.

PS J'ai fait face à ce problème et cela a très bien fonctionné. Beaucoup mieux que ma réponse de command de force.

Je faisais face au même problème. L'utilisation de l'indice de requête FORCE ORDER résoudra ce problème. L'inconvénient est que vous ne serez pas en mesure d'exploiter le meilleur plan que l'optimiseur de requête a pour votre requête, mais cela permettra d'éviter le blocage.

Donc (ceci provient de l'user de "Bill the Lizard") si vous avez une requête FROM table1 LEFT JOIN table2 et que votre clause WHERE ne contient que des colonnes de table2, le plan d'exécution sélectionnera normalement les lignes de table2 puis searchra les lignes de table1 . Avec un petit jeu de résultats de table2 seulement quelques lignes de table1 doivent être récupérées. Avec FORCE ORDER d'abord toutes les lignes de table1 doivent être récupérées, parce qu'il n'a pas de clause WHERE, puis les lignes de table2 sont jointes et le résultat est filtré à l'aide de la clause WHERE. Cela dégrade les performances.

Mais si vous savez que ce ne sera pas le cas, utilisez ceci. Vous souhaiterez peut-être optimiser la requête manuellement.

La syntaxe est

 SELECT ... FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE ... OPTION (FORCE ORDER)