Discussion :

[pitipilot]

Bonjour, j'ai actuellement un gros problème avec une base sql express 2005 : plusieurs pc de supervision écrivent des logs dans une même table. Cependant, j'ai souvent ce message d'erreur : "The ODBC Database destination failed to send message to the database. Reason: La transaction (ID de processus X) a été bloquée sur les ressources verrou par un autre processus et a été choisie comme victime. Réexécutez la transaction."

X correspondant à un pc.


Comment faire pour éviter ce deadlock ?


Merci et bonne journée.

[SQLpro]

En général les deadlock sont du à un mauvais développement des applications :
1) transactions inutiles
2) verrouillage explicite
3) pilotage incorrecte du niveau d'isolation des transactions.
Et surtout lorsque les applications sont écrites à l'aide de d'ORM ou de Framework catastrophiques comme Hibernate par exemple. A lire : http://sqlpro.developpez.com/cours/b...s-epaisses.pdf

Pour les détecter, utilisez le profiler SQL et tracez les événements de deadlock.
Voir : http://sqlpro.developpez.com/sqlserv...ge=tsql#LIII-M

Ensuite il vous faudra remonter cela aux développeurs et surtout commencer par les former !!!!!!!!

A +

[pitipilot]

Merci pour ta réponse rapide SQLpro.

Comment accéder aux traces et à SQL profiler ?

[elsuket]

Bonjour,

Depuis SQL Server Management Studio, dans le menu Outils choisissez SQL Server Profiler : une nouvelle GUI s'ouvre

Une fois connecté à votre instance, dans l'onglet "Général", cochez la case "Enregistrer dans le fichier" et spécifiez où vous souhaitez stocker le fichier de trace.
En général, si la trace est bien filtrée, le fichier n'occupe que quelques Mo.

Une fois cela fait, passez dans l'onglet "Sélection des événements".
Décochez toutes les cases à gauche : vous n'en avez pas besoin (dans votre cas), puis réduisez tous les nœuds.

Une fois cela fait, cochez "Afficher tous les événements" et "Afficher toutes les colonnes".
Dans la catégorie "Locks", cochez la case "Deadlock graph".
C'est cet événement qui va vous permettre :

- de capturer les requêtes participant à un verrou mortel
- de connaître les ressources qui participent aux requêtes et au verrou mortel
- de connaître le verrouillage utilisé
- de visualiser le tout sous la forme d'un graphe.

Au moment où vous allez cocher cette case, vous verrez apparaître un onglet nommé "Paramètres d'extraction des événements".
Si vous le souhaitez, vous pouvez "Sauvegarder les événements Deadlock XML séparément" dans un dossier de votre choix, ce qui vous permettra de les distribuer à vos collègues facilement.
Ce n'est pas obligatoire pour capturer les deadlocks.

Une fois cela fait, il ne vous reste plus qu'à cliquer sur le bouton "Exécuter" ... et à attendre qu'un deadlock se produise.
A ce moment là vous aurez une nouvelle ligne dans la partie supérieure de la nouvelle fenêtre.
Il vous suffira de cliquer dessus pour voir apparaître le graphe du deadlock.
En passant le curseur sur les divers éléments du graphe, vous en apprendrez un peu plus

Une fois cela terminé, n'oubliez pas de sauvegarder la trace (CTRL+S) puis de l'arrêter avec le bouton stop dans la barre d'outils (sinon elle continuera de tourner).

Si vous souhaitez voir une telle trace s'exécuter en tâche de fond côté serveur, vous pouvez regarder le tutoriel que j'ai publié à ce sujet.

Bonne chance

@++

[pitipilot]

Merci beaucoup mais dans outils je n'ai que "external tools, customize, options". Cela est peut être dut à la version express de sql server ?

[mikedavem]

Avec SQLExpress le profiler n'est pas disponible.
Cependant il existe des outils tiers qui permettent de faire cela (avoir si les événements deadlock sont disponibles, je n'ai pas testé).

Une autre solution de contournement si vous avez une autre edition d'installée en local ou en réseau est d'utiliser le profiler disponible de l'édition en question.

Enfin une dernière solution serait d'activer le trace flag 1222 au démarrage du serveur SQL qui permet de tracer les deadlocks depuis le moteur de bases de données. A essayer avec l'édition Express mais je ne pense pas que cela pose problème.

++

[WOLO Laurent]

Merci pour ta réponse rapide SQLpro.

Comment accéder aux traces et à SQL profiler ?

il y'a même un tuto dans notre section sur les tutos.

[elsuket]

Il faut reconnaître que c'est assez dommage d'insérer un tuple pour le supprimer juste ensuite :

- cela ne sert à rien
- cela rallonge la durée de la transaction inutilement
- cela augmente la quantité de maintenance des index de la table ...

Pour éviter cela :

- soit vous faites un contrôle avant l'INSERT, en filtrant la donnée
- soit vous ajoutez une contrainte de domaine (CHECK)
- soit vous écrivez un trigger INSTEAD qui se substituera à l'INSERT, et dans lequel vous pourrez filtrer les données.

Cela dit, je pense que ce type de "filtrage" peut tout à fait être réalisé en amont.

@++

[pitipilot]

Le problème est bien due au trigger de suppression, puisque je les ai supprimés et là plus de DeadLock, en regardant dans le moniteur d’activités, j'ai vu que les opérations DELETE étaient interminables.
Cependant, je dois réellement filtrer ces données mais en amont je n'ai pas l'impression de pouvoir le faire, je peux juste créer le lien ODBC et configurer la fréquence des insertions.


Est-il possible de déclencher mon Trigger de suppression avant chaque instruction d'insert et non pas à chaque insertion d'enregistrement ?


Et juste un petit problème de syntaxe, comment effectuer une requête SQL sur plusieurs ligne ?

Merci beaucoup pour vos réponses, on y est presque.