Comment résoudre Index/Clés liés Impasse
J'ai diagnostiqué un problème de blocage dans mon sql server à l'aide de la graphique de blocage dans SQL Server 2008.
Le problème a à voir avec mon index. J'ai deux requêtes: un long rapport en cours d'exécution avec beaucoup de jointures et sous-requêtes qui extrait des données selon deux dates différentes sur une table de base, et une rapide requête de mise à jour, les mises à jour de ces mêmes dates que la table de base. J'ai deux index, et le rapport veut une CLÉ partagée de verrouillage sur les deux d'entre eux, tandis que la requête de mise à jour veut exclusif de verrouillage à CLÉ sur les deux d'entre eux, et en quelque sorte à chaque requête ne parvient à obtenir l'une des clés, afin que personne ne puisse continuer.
Que puis-je faire pour résoudre ce problème?
Voici tous les détails sur ma situation:
Ma table de base ressemble à ceci:
CREATE TABLE job_tb{
job_id int IDENTITY(1,1),
createDate datetime NULL,
upDate datetime NULL,
dataField1 nchar(1),
dataField2 nchar(2),
--etc...
}
Mon indices ressembler à ceci:
CREATE NONCLUSTERED INDEX idx_createDate ON job_tb(
createDate DESC
)
INCLUDE(dataField1, dataField2)
CREATE NONCLUSTERED INDEX idx_upDate ON job_tb(
upDate DESC
)
INCLUDE(dataField1, dataField2)
Enfin, ma mise à jour ressemble à ceci:
BEGIN TRANSACTION;
UPDATE job_tb
SET
dataField1 = @data
upDate = @upDate
WHERE
job_id = @job_id
COMMIT TRANSACTION;
Et le rapport est en comptant tous types de statistiques par date, donc je ne vais pas l'inclure ici. J'ai intentionnellement conçu idx_createDate et idx_upDate à "couvrir" ou inclure dataField1, car il est largement utilisé dans ce rapport.
Je crois que le rapport attrape un verrou partagé sur l'un des indices, frappe, puis une sous-requête et demande un verrou sur le deuxième indice. En attendant la mise à jour de la requête veut un verrou exclusif sur les deux indices afin de mettre à jour la mise à jour et à la dataField1.
Ce que vous en pensez?
EDIT:
Voici le graphique de blocage XML, comme l'a demandé:
<deadlock-list> <deadlock>
<victim-list>
<victimProcess id="processcf65288"/>
</victim-list>
<process-list>
<process id="processcf65288" taskpriority="0" logused="0" waitresource="KEY: 6:72057597970874368 (eee1799e706c)" waittime="122" ownerId="421742704" transactionname="SELECT" lasttranstarted="2012-08-03T05:37:21.257" XDES="0x8611e8800" lockMode="S" schedulerid="50" kpid="8560" status="suspended" spid="70" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2012-08-03T05:37:21.257" lastbatchcompleted="2012-08-03T05:37:21.257" clientapp="Internet Information Services" hostname="xxx" hostpid="11964" loginname="xxx" isolationlevel="read committed (2)" xactid="421742704" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="" line="28" stmtstart="1276" stmtend="4826" sqlhandle="0x03000600311ac36c65a31701a1a000000100000000000000">
</frame>
<frame procname="" line="1" sqlhandle="0x01000600f61bee3600932ae3090000000000000000000000">
</frame>
</executionStack>
<inputbuf> exec MonthlyReport @id = 41
</inputbuf>
</process>
<process id="processd2b6bc8" taskpriority="0" logused="1908" waitresource="KEY: 6:72057597970939904 (8e8117a49479)" waittime="2242" ownerId="421742551" transactionname="user_transaction" lasttranstarted="2012-08-03T05:37:20.447" XDES="0x7e84ad0a0" lockMode="X" schedulerid="63" kpid="12700" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-08-03T05:37:20.443" lastbatchcompleted="2012-08-03T05:37:20.443" clientapp="Internet Information Services" hostname="xxx" hostpid="11964" loginname="xxx" isolationlevel="read committed (2)" xactid="421742551" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="" line="47" stmtstart="2342" stmtend="2640" sqlhandle="0x03000600e7dd9c717cbbb900ec9f00000100000000000000">
</frame>
<frame procname="" line="1" sqlhandle="0x01000600311d7a152032f9be040000000000000000000000">
</frame>
</executionStack>
<inputbuf> exec UpdateJob @dataField1 = 'C', @upDate = '8/3/2012 5:37:20 AM', @job_id = 1542687
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057597970874368" dbid="6" objectname="" indexname="" id="lock612859900" mode="X" associatedObjectId="72057597970874368">
<owner-list>
<owner id="processd2b6bc8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="processcf65288" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057597970939904" dbid="6" objectname="" indexname="" id="lock612a15300" mode="S" associatedObjectId="72057597970939904">
<owner-list>
<owner id="processcf65288" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="processd2b6bc8" mode="X" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock> /deadlock-list>
begin transaction
?aussi pouvez-vous partager le graphique de blocage de fichier xml?
nope le rapport ne contient pas de
begin transaction
est tout le rapport de la requête complète couvertes par ces deux indices? n'plan de requête de requête de rapport ne fournit aucune clé de recherche ou d'analyse de table sur la table primaire?
oui, elle est une des clés de recherche sur l'index de clé primaire. il peut y avoir un peu plus de champs que je pourrais ajouter à ces indices pour éviter cela...
OriginalL'auteur Slider345 | 2012-08-03
Vous devez vous connecter pour publier un commentaire.
Basé sur le Q/r de la discussion dans les commentaires et après analyse de la graphique de blocage, c'est le cas où le rapport de la requête n'est pas entièrement couverte par le présent deux indices. Le rapport va commencer à la recherche sur les non index cluster en premier. Il ne trouve pas toutes les informations nécessaires. De sorte qu'il n'clé de recherche primaire de la table pour obtenir les données restantes.
Mais les mises à jour de travail à l'exact opposé. Les mises à jour seront d'abord verrouiller la table principale, et le mettre à jour les données et ensuite pour tous les index et les mettre à jour. D'où l'impasse.
Une façon de résoudre ce problème est de couvrir l'ensemble du rapport de recherche par index. Mais cela a des implications que les mises à jour deviennent lents.
Autre solution consiste à décomposer la requête de rapport en deux et à l'utilisation temporaire de la variable de table pour recueillir les données à partir des index et ensuite faire la clé de recherche. Note du rapport de la requête ne doit pas être exécuté en vertu de la transaction sérialisable mode. Sinon, la transaction ne sera pas communiqué les verrous en lecture dont il vient de lire.
Espère que je suis clair. Laissez-moi savoir si vous avez des doutes.
assurez-vous que dans le plan de requête de requête de rapport ne contient pas toutes les keylook jusqu'à la table primaire.
encore une chose, si vous avez encore de la situation, voir si vous pouvez obtenir de l'indice des ressources de verrouillage. Dans le dernier graphique, il était vide. Dans le pire des cas, il est possible de mettre à jour pour mettre à jour ces indices dans un ordre différent de la façon dont ils sont lus par rapport à la requête.
Yep, ce qui a fixé l'impasse! Merci pour l'aide.
Bienvenue 🙂
OriginalL'auteur Ankush