[MSSQL] Problème de deadlock [Résolu]

[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://img1.lemondeinformatique.fr/f...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.

++

[pitipilot]

Pour créer une trace c'est bien ce code ?


"sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
[ , [ @filecount = ] 'max_rollover_files' ]
"


Désolé je me suis mis à SQL depuis peu alors les traces j'en ai pas trop entendu parler. En tout cas merci tout le monde pour votre aide parce que ici c'est vraiment l'impasse.

[elsuket]

Désolé je me suis mis à SQL depuis peu alors les traces j'en ai pas trop entendu parler

Il n'y a aucun mal.
Effectivement votre code doit marcher.
Le problème, ce sera pour ouvrir le fichier ensuite ...
Vous pouvez toujours utiliser fn_trace_gettable, mais vous n'aurez pas les graphes.

Je me rappelle avoir succinctement utilisé le logiciel que SQLPro vous recommande, mais je n'avais pas cherché à l'utiliser pour les deadlocks.
De toute façon, cela ne vous coûte rien de le tester

@++

[SQLpro]

Pas besoin du profiler....

1) créer une trace

lancez ce script en modifiant préalablement le répertoire de stockage des fichiers de trace ou bien en créant le répertoire C:\MesTraceProfilerSQL\

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
SET @maxfilesize = 25 
 
exec @rc = sp_trace_create @TraceID output, 2, N'C:\MesTraceProfilerSQL\ProfilerDeadLock20110622', @maxfilesize, NULL 
IF (@rc != 0) goto error
 
-- Client side File and Table cannot be scripted
 
-- Set the events
declare @ON bit
SET @ON = 1
exec sp_trace_setevent @TraceID, 148, 11, @ON
exec sp_trace_setevent @TraceID, 148, 51, @ON
exec sp_trace_setevent @TraceID, 148, 4, @ON
exec sp_trace_setevent @TraceID, 148, 12, @ON
exec sp_trace_setevent @TraceID, 148, 14, @ON
exec sp_trace_setevent @TraceID, 148, 26, @ON
exec sp_trace_setevent @TraceID, 148, 60, @ON
exec sp_trace_setevent @TraceID, 148, 64, @ON
exec sp_trace_setevent @TraceID, 148, 1, @ON
exec sp_trace_setevent @TraceID, 148, 41, @ON
exec sp_trace_setevent @TraceID, 25, 7, @ON
exec sp_trace_setevent @TraceID, 25, 15, @ON
exec sp_trace_setevent @TraceID, 25, 55, @ON
exec sp_trace_setevent @TraceID, 25, 8, @ON
exec sp_trace_setevent @TraceID, 25, 32, @ON
exec sp_trace_setevent @TraceID, 25, 56, @ON
exec sp_trace_setevent @TraceID, 25, 64, @ON
exec sp_trace_setevent @TraceID, 25, 1, @ON
exec sp_trace_setevent @TraceID, 25, 9, @ON
exec sp_trace_setevent @TraceID, 25, 25, @ON
exec sp_trace_setevent @TraceID, 25, 41, @ON
exec sp_trace_setevent @TraceID, 25, 49, @ON
exec sp_trace_setevent @TraceID, 25, 57, @ON
exec sp_trace_setevent @TraceID, 25, 2, @ON
exec sp_trace_setevent @TraceID, 25, 10, @ON
exec sp_trace_setevent @TraceID, 25, 26, @ON
exec sp_trace_setevent @TraceID, 25, 58, @ON
exec sp_trace_setevent @TraceID, 25, 66, @ON
exec sp_trace_setevent @TraceID, 25, 3, @ON
exec sp_trace_setevent @TraceID, 25, 11, @ON
exec sp_trace_setevent @TraceID, 25, 35, @ON
exec sp_trace_setevent @TraceID, 25, 51, @ON
exec sp_trace_setevent @TraceID, 25, 4, @ON
exec sp_trace_setevent @TraceID, 25, 12, @ON
exec sp_trace_setevent @TraceID, 25, 52, @ON
exec sp_trace_setevent @TraceID, 25, 60, @ON
exec sp_trace_setevent @TraceID, 25, 13, @ON
exec sp_trace_setevent @TraceID, 25, 6, @ON
exec sp_trace_setevent @TraceID, 25, 14, @ON
exec sp_trace_setevent @TraceID, 25, 22, @ON
exec sp_trace_setevent @TraceID, 59, 55, @ON
exec sp_trace_setevent @TraceID, 59, 32, @ON
exec sp_trace_setevent @TraceID, 59, 56, @ON
exec sp_trace_setevent @TraceID, 59, 64, @ON
exec sp_trace_setevent @TraceID, 59, 1, @ON
exec sp_trace_setevent @TraceID, 59, 21, @ON
exec sp_trace_setevent @TraceID, 59, 25, @ON
exec sp_trace_setevent @TraceID, 59, 41, @ON
exec sp_trace_setevent @TraceID, 59, 49, @ON
exec sp_trace_setevent @TraceID, 59, 57, @ON
exec sp_trace_setevent @TraceID, 59, 2, @ON
exec sp_trace_setevent @TraceID, 59, 14, @ON
exec sp_trace_setevent @TraceID, 59, 22, @ON
exec sp_trace_setevent @TraceID, 59, 26, @ON
exec sp_trace_setevent @TraceID, 59, 58, @ON
exec sp_trace_setevent @TraceID, 59, 3, @ON
exec sp_trace_setevent @TraceID, 59, 35, @ON
exec sp_trace_setevent @TraceID, 59, 51, @ON
exec sp_trace_setevent @TraceID, 59, 4, @ON
exec sp_trace_setevent @TraceID, 59, 12, @ON
exec sp_trace_setevent @TraceID, 59, 52, @ON
exec sp_trace_setevent @TraceID, 59, 60, @ON
 
 
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
 
-- display trace id for future references
SELECT TraceID=@TraceID
goto finish
 
error: 
SELECT ErrorCode=@rc
 
finish: 
go

2) arrêt

Retenez le n° qui est affiché et qui va servir à arrêter ultérieurement la trace.

Laissez tourner autant de temps que nécessaire (plusieurs jours si vous voulez).

Pour arrêter la trace, modifiez le script ci dessous en remplaçant les ??? par le n° affiché précédemment et lancez le code suivant :

Code :

1
2
exec sp_trace_setstatus ???, 0; -- arrêt de la trace
exec sp_trace_setstatus ???, 2; -- détachement des fichiers et suppression du paramétrage

3) intégration des données en table

Pour intégrer les fichiers de trace dans une table, faites :

Code :

1
2
3
SELECT *
INTO   MaTableTrace
FROM   sys.fn_trace_gettable ('C:\MesTraceProfilerSQL\ProfilerDeadLock20110622.trc', DEFAULT);

Dès lors les données de trace sont intégrées à la table MaTableTrace

Pour voir un rapport de deadlock il suffit de cliquer double sur le XML du deadlock graphe ce qui ouvrira une fenêtre de visualisation.

A +

[pitipilot]

J'ai réussi à créer la trace et à l'extraire dans une table. Comment effectue t'on un deadlock graph dans sql server express ? Car j'ai extrait les données dans un tableur excel mais je doute que cela puisse nous aider.

Merci pour vos réponses

[elsuket]

La colonne TextData doit contenir un document XML.
Essayez de le coller dans un fichier texte dont vous changez l'extension par xdl, et voyez si vous pouvez l'ouvrir avec SQL Server Management Studio.

@++

[pitipilot]

Je pense peut être connaître la cause du problème : étant donné que j'ai beaucoup d'informations inutiles qui arrivent sur ma base, pour en limiter le poids j'ai créée des triggers de suppression par exemple :

Code :

1
2
3
4
5
6
7
8
9
10
CREATE TRIGGER supress_FTLD_session
ON Action_OPE
AFTER INSERT
 
AS
 
DELETE FROM Action_OPE WHERE messagetext LIKE ‘%FTLD session%’
 
 
GO

Ce trigger supprime donc à chaque insertion les mots clé choisis (je dois en avoir une 15aine). Cela surcharge t'il mes requêtes ?

[WOLO Laurent]

Envoyé par pitipilot

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.

[elsuket]

en regardant dans le moniteur d’activités, j'ai vu que les opérations DELETE étaient interminable.

Il faut cependant savoir pourquoi elles sont interminables.
Cela dit le traitement est de toute façon à changer.

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.

Il y a bien une application qui insère les données dans la base de données.
Donc le "filtrage" des données devrait être implémenté dans cette application.

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

Non, car sous SQL Server, les triggers de type BEFORE n'existent pas.
On peut aisément les "remplacer" par un trigger INSTEAD OF comme celui qui suit, et qui va se substituer à l'INSERT :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TRIGGER TR_IOF_I_Action_OPE
	ON dbo.Action_OPE
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT	INTO dbo.Action_OPE
	(
		listeDesColonnes
	)
	SELECT	listeDesColonnes
	FROM	inserted
	WHERE	messagetext NOT LIKE ‘%FTLD session%’
 END

La table virtuelle inserted expose les données que l'on a inséré, et a la même structure que la table à laquelle est attaché le trigger.

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

Il suffit d'aller à la ligne

@++

[pitipilot]

En fait, les instructions DELETE sont interminables étant donné qu'elles vérifient à chaque enregistrement si aucun autre enregistrement dans la table contient les mots clés (environ une bonne 20aine). Sachant que j'ai plusieurs enregistrements inutiles qui arrivent par seconde, à chaque on ré effectue le trigger. Voilà pourquoi j'aurai voulu savoir si avant chaque requête, il était possible de lancer le trigger et non sur chaque enregistrement.


Le logiciel en amont est RSVIEW, je peux filtrer certaines données :


Cependant c'est un filtrage très vague et même si pas mal de données sont éliminées, il m'en reste 90% à supprimer.

EDIT : j'ai essayé d'insérer une image mais ça n'a pas fonctionné voici le lien : http://img855.imageshack.us/i/temp1n.jpg/

Concernant la syntaxe de saut de ligne pour une requête, j'ai oublié de préciser que ma requête en question était sous VBA et le simple saut à la ligne ne marche malheureusement pas.

Merci pour ton aide.

Bonne journée

[elsuket]

En fait, les instructions DELETE sont interminables étant donné qu'elles vérifient à chaque enregistrement si aucun autre enregistrement dans la table contient les mots clés (environ une bonne 20aine)

Effectivement SQL Server maintient des statistiques sur les valeurs de chaînes sur les 40 premiers et 40 derniers caractères d'une chaîne de caractères.

Si la colonne n'est pas indexée, (le faire dans votre cas ne présente aucun avantage), alors le balayage de la table est obligatoire, et plus vous avez de lignes, plus la requête est lente.

Cependant c'est un filtrage très vague et même si pas mal de données sont éliminées, il m'en reste 90% à supprimer.

A ce moment là stockez les expressions à éliminer dans une table, lisez la table à chaque fois que des données doivent être insérées dans la table en question, et filtrez en amont

Voilà pourquoi j'aurai voulu savoir si avant chaque requête, il était possible de lancer le trigger et non sur chaque enregistrement.

C'est ce que fait le trigger INSTEAD OF que je vous ai donné, quelque soit le nombre de lignes ajoutés.

Concernant la syntaxe de saut de ligne pour une requête, j'ai oublié de préciser que ma requête en question était sous VBA et le simple saut à la ligne ne marche malheureusement pas.

Il me semble qu'il faut ajouter un simple underscore en fin de ligne ...

@++

[mikedavem]

Envoyé par pitipilot

Je pense peut être connaître la cause du problème : étant donné que j'ai beaucoup d'informations inutiles qui arrivent sur ma base, pour en limiter le poids j'ai créée des triggers de suppression par exemple :

Code :

Ce trigger supprime donc à chaque insertion les mots clé choisis (je dois en avoir une 15aine). Cela surcharge t'il mes requêtes ?

Vous avez le même problème que chez un de mes clients qui supprimait les données après les avoir insérés. La question est la suivante : Avez vous besoin de faire cela de manière synchrone ? Si non pourquoi ne pas le faire de manière asynchrone dans un job à une heure déterminée par exemple ...

++

[pitipilot]

A ce moment là stockez les expressions à éliminer dans une table, lisez la table à chaque fois que des données doivent être insérées dans la table en question, et filtrez en amont

C'est à dire que je vais créer une table contenant mes mots clés inutiles et l'autre va vérifier si les enregistrements ne contiennent pas ceux ci ? Pouvez vous me guider dans cette démarche car cela dépasse le peu de connaissances en sql actuelles.

Vous avez le même problème que chez un de mes clients qui supprimait les données après les avoir insérés. La question est la suivante : Avez vous besoin de faire cela de manière synchrone ? Si non pourquoi ne pas le faire de manière asynchrone dans un job à une heure déterminée par exemple ...




++

J'y ai également pensé sauf qu'un viewer excel vient lire les infos de la table et il est primordial que celles ci soient pertinentes donc déjà triées. De plus je suis sous SQL Express et je suis limité à 4Go de base je crois.



Merci pour votre aide, bonne journée



EDIT : ElSuket, j'ai essayé votre code et malheureusement cela ne fonctionne pas.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE TRIGGER TR_IOF_I_Action_OPE
	ON Action_Ope
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT	INTO dbo.Action_OPE
	(
		RecordID, ParentID, SequenceNumber, Chaintime, TimeStmp, MessageText, Audience, Severity, Verbosity, Location, Provider, UserID, UserFullName
	)
	SELECT	RecordID, ParentID, SequenceNumber, Chaintime, TimeStmp, MessageText, Audience, Severity, Verbosity, Location, Provider, UserID, UserFullName
	FROM	inserted
	WHERE	messagetext LIKE '%AM''.'OR messagetext LIKE '%AUTO''.' OR messagetext LIKE '%MANU''.' OR messagetext LIKE '%HS''.' OR messagetext LIKE '%S''.'  OR    (DATEDIFF(dd, TimeStmp, GETDATE()) > 2558)
 
 END