Discussion :

[kpdev]

Bonjour,

j'ai un problème de deadlock sur 2 deletes simultanés d'enregistrements dans une table B dont dépend une table C.
Je travaille sur une base de données SQL server 2005. Je suis en niveau d'isolation READ_COMMITED.

Voici le modèle des tables :
Table A, dont dépend Table B, dont dépend Table C. Pas de delete cascade sur toutes ces tables. Colonne idA indexée dans les 3 tables.

On veut supprimer dans un thread 1 les enregistrements des tables B et C d’idA = 1111.
On veut supprimer dans un thread 2 les enregistrements des tables B et C d’idA = 2222.

Voici les traces de SQL Server profiler

Begin transaction T1
Delete from C with (rowlock) where idA=1111
Delete from B with (rowlock) where idA=1111

Begin transaction T2
Delete from C with (rowlock) where idA=2222
Delete from B with (rowlock) where idA=2222
Deadlock
Rollback transaction T2

Commit transaction T1

Ayant précisé un rowlock, je ne comprend pas pourquoi j'ai un deadlock sur la table B.

En analysant la trace du profiler j'ai vu que le delete from B fait 44000 reads, alors qu'il n'y a que 3 enregistrements à supprimer dans B et 14 dans C.
Il ne semble donc pas utiliser les index sur le delete ?

J'ai essayé de lui préciser d'utiliser l'index
delete from B with (index (PK_B)) where idA=1111
mais j'ai une erreur de syntaxe : Les indicateurs d'index ne sont autorisés que dans une clause FROM.

Est-ce que quelqu'un a déjà rencontré ce type de problème ?
Est-ce qu'un delete cascade pourrait le résoudre ?

D'avance merci pour vos réponses.

[elsuket]

Bonjour,

Ayant précisé un rowlock, je ne comprend pas pourquoi j'ai un deadlock sur la table B.

SQL Server peut "décider" d'ignorer cet indicateur de table, et bloquer la table entière : en effet l'escalade de verrous peut se faire soit :

- de ligne à partition à table
- de page à partition à table

Le schéma ligne à page à partition à table n'existe pas.
D'autre part le niveau partition ne peut exister que si l'escalade de verrou est définie à AUTO pour la table, ce qui se fait par un ALTER TABLE maTable SET LOCK_ESCALATION = AUTO. Par défaut c'est TABLE.

Notez que dans des cas très particuliers de modifications de données massives sur des tables à gros volumes, il est parfois nécessaire de reconstruire l'index qui supporte la requête en précisant ALLOW_PAGE_LOCKS = OFF.

Il ne semble donc pas utiliser les index sur le delete ?

Seul le plan de requête peut vous l'indiquer.
Vous pouvez le capturer avec SQL Server Profiler en choisissant l’évènement Showplan XML de la catégorie Performance.

D'un autre côté, pour des instructions aussi simples que celle-ci, vous pouvez vous contenter du plan d'exécution estimé, que l'on obtient après avoir surligné la requête à étudier, par CTRL+L.
Est-ce que vous maintenez les statistiques de colonnes et d'index ?

Vous pouvez aussi capturer le graphe de l'étreinte mortelle avec SQL Server Profiler en utilisant l'événement Deadlock Graph de la catégorie Locks, ce qui vous aidera à comprendre exactement ce qui se passe.

Et ce qui se passe, c'est que si vous validez la transaction imbriquée, le nombre de transactions ouvertes, que l'on obtient avec la fonction système @@TRANCOUNT est à 1, alors qu'avant de le faire, il est à 2.
Si vous essayez de lire les tables affectées dans la même fenêtre de requête, c'est à dire dans la même session, toutes les modifications de données sont bien là. Mais si vous tentez de le faire depuis une autre fenêtre de requête, vous serez bien sûr bloqué, puisque l'exécution d'une modification de données se fait à l'aide de verrous exclusifs : vous ne pouvez pas :

- lire les données de la table, sauf s'il est tolérable pour votre besoin de lire des données qui n'ont pas été validées, et qui peuvent donc être modifiées ou supprimées durant la lecture : dans ce cas, et dans ce cas seulement, le niveau d'isolation de la transaction peut être READ UNCOMMITTED (par défaut c'est READ COMMITTED). S'il vous plaît ne faites pas l'erreur très commune d'utiliser ce niveau à tors et à travers, ou l'indicateur de table NOLOCK (équivalent) en croyant que cela a le même effet que le bouton Turbo Boost de K2000 : c'est faux, et la seule chose qui le justifie c'est un modèle de données faux, si on peut encore parler de modèle de données.
- (grossièrement) modifier les données de la table pour la durée de la transaction qui en modifie déjà

Or si vous ne validez ni n'annulez la transaction, vous bloquez des lignes, pages, partitions, ou la table.
Si vous faites un ROLLBACK TRAN T1, tout est annulé, @@TRANCOUNT vous donnera zéro, et tous les verrous sous-jacents sont libérés.

Le seul moyen dont vous disposez pour annuler la transaction imbriquée et valider la transaction "mère", et par là même libérer les verrous sous-jacents, c'est d’exécuter un SAVE TRAN T2 avant son annulation.

Cela étant, je ne vois pas l'intérêt d'annuler une transaction que l'on vient d'exécuter si ce n'est pas dans le cadre d'une gestion d'erreurs.

@++

[mikedavem]

Vous pouvez aussi capturer le graphe de l'étreinte mortelle avec SQL Server Profiler en utilisant l'événement Deadlock Graph de la catégorie Locks, ce qui vous aidera à comprendre exactement ce qui se passe.

Je pense qu'il faut commencer par cela

++

[SQLpro]

Sans la description SQL de vos tables sous forme DDL avec tous les index il est impossible de vous aider

A =

[kpdev]

Merci pour vos réponses et surtout merci à Elsuket d'avoir pris le temps d'analyser mon problème.

Le rollback est effectivement une gestion d'erreur et les transactions sont lancées dans des fenêtres différentes.

Le Deadlock Graph (je ne connaissais pas, ) a montré un pagelock sur ma table B sur la requête
Delete from B with (rowlock) where idA=2222.

Et le plan de requête a effectivement montré que l'index de la table C sur la colonne idA n'était pas utilisé.

En fait (sorry, je ne l'avais pas précisé mais je ne pensais pas que ça avait son importance) la clé étrangère de la table C sur la table B était constituée de 2 colonnes (idA, + une autre).
En ajoutant cette deuxième colonne dans l'index de la table C, plus de deadlock !

Donc problème résolu, merci !