Problème de deadlocked

Bonjour à toutes et à tous et merci à ceux qui nous aident et font vivre ce forum :ccool:

Je rencontre des problèmes avec une application, des traitements me génèrent souvent les erreurs ci-dessous et j'aimerai avoir votre avis, la cause de ces deadlocked et quel outil sqlserver utiliser pour m'aider à trouver la source du problème, trace etc

La version sqlserver utilisée et 2008

Je vous remercie par avance

exemple numéro 1 :

J'ai remplacé certaines infos pour raison de confidentialité

Citation:

---

An Error has occurred while Executing Query:
UPDATE nom de ma table
SET amount = amount + 0.000000
WHERE client = 'TOTO' AND period = 201003 AND ple_amount > 0.0
AND dim1 = '75-201000145'

ERROR 1205: [Microsoft][SQL Native Client][SQL Server]Transaction (Process ID 173) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
16:13:56 > ERROR 1205: Error in AGRExecSql: Couldn't execute statement when Update funds
16:13:56 > Aborting transaction.

Exemple numéro 2 :

An Error has occurred while Executing Query:
UPDATE a
SET a.apar_name = b.name , a.apar_bank = b.bank_account ,
a.apar_addr = b.address , a.apar_place = b.place ,
a.apar_zipcode = b.zip_code
FROM ##Hxxxx_DEP00000 a , aagaXXXXX b
WHERE b.client = 'TOTO' AND b.dim_value = a.xxxxx AND
b.apar_type = a.apar_type AND b.attribute_id in ( 'xx' ,
'yy' )

ERROR 1205: [Microsoft][SQL Native Client][SQL Server]Transaction (Process ID 243) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Error in UpdateTableId; Table name: ##Hxxxx_DEP00000 avalue: SET a.apar_name = b.name, a.apar_bank = b.bank_account, a.apar_addr = b.address, a.apar_place = b.place, a.apar_zipcode = b.zip_codeindex:

Message:Error in XXXUpdTable: Cannot execute update when xxxxxxxxx

Arrêt du traitement

---

Les deadlock (verrous mortel, étreinte fatale, interblocage...) sont liés aux transactions et ne sont en théorie pas évitable. On peut cependant en réduire leur fréquence en adoptant un style de développement axé sur les points suivants :
1) minimiser la durée de la transaction
a) en la faisant côté serveur SGBDR et non côté client (éviter donc Hibernate et autres saloperie du même genre), les
b) en impliquant moins possible d'ordre SQL
c) en diminuant le niveau d'isolation des transactions (SET TRANSACTION ISOLATION LEVEL...)
...

2) respecter une modélisation relationnelle
a) s'assurer que les formes normales ont été respectées au moins jusqu'à la 3e.
b) faire des tables les plus courtes possible (ne pas faire des tables fourre tout avec 80 colonnes - personnellement à plus de 20 colonnes, je considère que la table est mauvaise)
c) prendre des types de données adéquat et donc éviter le VARCHAR(250) systèmatique par exemple
d) mettre en place des clefs primaire les plus courte possible. A ce sujet l'auto incrément sur un INT est la meilleure des choses.
...

Lisez les articles que j'ai écrit sur l'optimisation : http://sqlpro.developpez.com/optimisation/

Pour débusquer les deadlock, vous pouvez utiliser le profiler SQL et vous trouverez dans la rubrique Lock, les événéments Deadlock, DeadlockChain et DeadlockGraph à suivre.

Bon courage, car la multiplication des deadlock est généralement le signe d'un développement épouvantable : modèle de données sans respect des règles de l'art, type de données inappropriés, style de codage imbécile (par exemple ne pas avoir utilisé les procédures stockées...).

A +

Merci à toi pour ta réponse précise et rapide, je vais tracer via sqlprofiler :ccool:

Bonjour, j'ai donc essayer d'installer une trace avec filtres sur les deadlock.

Je me pose la question de savoir comment filtrer si mon logiciel n'utilise pas les procédures stockées, quels evenements prévoir, security audit, sessions, tsql, je ne parle pas de stored procedure puisqu'il porte sur les procédures stockées.
Dois-je laisser de coté les événements et cliquer sur filtres de colonnes et choisir les filtres sur les locks ou n'est-ce pas suffisant.

Autre question, comment arrêter ma trace en cours qui est programmée pendant une semaine, fichier, arreter la trace est grisé.

Je vous remercie par avance pour vos réponses

Lorsque tu passe le curseur de la souris sur chaque 'bulle' du graphe, tu dois voir la dernière requête impliquée pour chaque session. cf capture.

Citation:

---

Envoyé par ocean_78

Autre question, comment arrêter ma trace en cours qui est programmée pendant une semaine, fichier, arrêter la trace est grisé.

---

1) Vérifier si la trace par défaut est active:

Code:

---

1 2	sp_configure 'default trace enable' GO

---

2a) Si run_value est à 1:

Code:

---

1 2	select * from fn_trace_getinfo(null) GO

---

Repérer la ou les traceids > 1 (traceid = 1 est la trace par défaut)

Code:

---

1 2 3 4

exec sp_trace_setstatus <traceid>, 0 GO exec sp_trace_setstatus <traceid>, 2 GO

---

0 stoppe la trace, et 2 la supprime.

2b) Si run_value=0, considérer toute traceid et opérer comme en 2a)

Super merci dbaffaleuf

Donc si je comprends bien il suffit que je trace uniquement en événement Lock et sous événements deadlock graph, lock:deadlock, lock:deadlock chain, lock:timeout
Et cela doit suffire à pister un éventuel problème.

J'ai pu arrêter ma trace en cours avec l'aide des tes commandes, dommage qu'on ne trouve pas ces commandes facilement.

Par contre pour la commande :
sp_configure 'default trace enable'
Il me renvoie un message d'erreur, je vais chercher à quoi cela correspond.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
L'option de configuration*'default trace enable' n'existe pas ou il s'agit d'une option avancée.

Surtout n'arrêtez pas ce paramètre malheureux, c'est la trace qui collecte les statistiques d'utilisation de votre base....

A +

Une autre solution est de créer une alerte WMI qui insère dans une table d'historisation les différentes DEADLOCKS qui surviennent sur votre base.

++

Citation:

---

Envoyé par SQLpro

Surtout n'arrêtez pas ce paramètre malheureux, c'est la trace qui collecte les statistiques d'utilisation de votre base....

A +

---

Merci

Non tout est ok, je n'ai pas arrêté la trace installée en standard par contre cela m'a permis d'arrêter ma trace perso qui était toujours RUN et dont l'option arrêt de la trace n'est plus utilisable une fois que l'on est sorti de sqlserver.

Citation:

---

Envoyé par SQLpro

Surtout n'arrêtez pas ce paramètre malheureux, c'est la trace qui collecte les statistiques d'utilisation de votre base....
A +

---

+1. Si run_value est à 1, il faut considérer les traceid > 1, et stopper seulement celles-là.

Pour afficher les paramètres avancés:

Code:

---

1 2 3 4

sp_configure 'show advanced options', 1 GO reconfigure GO

---

Encore merci à tous.

J'ai créé une trace qui est à l'écoute pendant une semaine par exemple par contre lorsque je ferme ma session windows la trace s'arrête, y a t il un moyen de laisser la trace à l'écoute même session fermée ou dois je avoir la session windows ouverte en permanence à l'écran ?

Merci

Il faut scripter la trace depuis Profiler et la démarrer depuis un job SQL Agent par exemple.

Bonjour, je cherche toujours à finaliser ce processus de trace.

J'ai donc sauvegardé ma trace sous forme de script sql et j'ai créé un job qui démarre à 8H tous les jours et se termine à 23H59 le soir pour se relancer théoriquement le lendemain à 8H.

Cela fonctionne bien le premier jour et le fichier de trace est bien créé mais le deuxième jour le job bloque avec le message d'erreur suivant :

Date 14/10/2010 08:00:01
Journal Historique du travail (Deadlock_trace)

ID de l'étape 1
Serveur FRPA-DBSQL01P\MSSQLSERVER2008
Nom du travail Deadlock_trace
Nom de l'étape deadlock_trace
Durée 00:00:00
Gravité SQL 16
ID de message SQL 19067
Opérateur averti par courrier électronique
Opérateur averti par envoi réseau
Opérateur averti par radiomessagerie
Tentatives exécutées 0

Message
Executed as user: NT AUTHORITY\SYSTEM. Impossible de créer une nouvelle trace car le chemin d'accès au fichier de trace est trouvé dans les traces existantes. [SQLSTATE 42000] (Error 19067). The step failed.

Est-ce que le nouveau fichier trace ne parvient pas à écraser le plus ancien ?

Dans mon job, onglet général, propriétaire, j'avais mis un compte de domaine, j'ai changé par un compte sqlserver SA on verra bien mais si vous avez une réponse précise je suis preneur.

merci encore et bonne journée à toutes et à tous

Si vous utilisez une trace SQL côté serveur, il faut au préalable supprimer / déplacer votre fichier de trace. Le fichier n'est pas écrasé automatiquement.

Vous pouvez également dans votre script nommer votre fichier de maniére unique à chaque exécution (Par exemple avec la date)

++

Merci, on ne peut pas supprimer le fichier, car il est vu comme utilisé, je suis obligé de stopper la trace puis de la supprimer avant d'avoir accès au fichier physique et de le supprimer.

Concernant la création d'un fichier différent par jour, j'ai pourtant bien activé la substitution de fichier mais peut être que je me trompe d'onglet et que ce n'est pas là qu'il faut le paramétrer

http://data.imagup.com/3/1287125523.JPG

Si c'est bien là ce paramétrage de substitution est-il bien repris dans le fichier sql exporté ?

Voilà mon script sql que j'utilise

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

****************************************************/ /* Created by: SQL Server 2008 Profiler */ /* Date: 15/10/2010 10:26:15 */ /****************************************************/     -- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 exec @rc = sp_trace_create @TraceID output, 2, N'F:\temp\LGT\log traces\deadlocktrace.trc', @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, 4, @on exec sp_trace_setevent @TraceID, 148, 12, @on exec sp_trace_setevent @TraceID, 148, 14, @on exec sp_trace_setevent @TraceID, 148, 1, @on exec sp_trace_setevent @TraceID, 25, 1, @on exec sp_trace_setevent @TraceID, 25, 10, @on exec sp_trace_setevent @TraceID, 25, 35, @on exec sp_trace_setevent @TraceID, 25, 4, @on exec sp_trace_setevent @TraceID, 25, 12, @on exec sp_trace_setevent @TraceID, 25, 13, @on exec sp_trace_setevent @TraceID, 25, 14, @on exec sp_trace_setevent @TraceID, 59, 1, @on exec sp_trace_setevent @TraceID, 59, 14, @on exec sp_trace_setevent @TraceID, 59, 35, @on exec sp_trace_setevent @TraceID, 59, 4, @on exec sp_trace_setevent @TraceID, 59, 12, @on exec sp_trace_setevent @TraceID, 189, 1, @on exec sp_trace_setevent @TraceID, 189, 10, @on exec sp_trace_setevent @TraceID, 189, 35, @on exec sp_trace_setevent @TraceID, 189, 4, @on exec sp_trace_setevent @TraceID, 189, 12, @on exec sp_trace_setevent @TraceID, 189, 13, @on exec sp_trace_setevent @TraceID, 189, 14, @on     -- Set the Filters declare @intfilter int declare @bigintfilter bigint   exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Générateur de profils SQL Server - 094aae1d-8915-41ee-92b4-20029f75e2b5' exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Générateur de profils SQL Server - 985bb67d-83ab-491a-b394-68e91b2c1555' -- 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

---

Bonjour,

Citation:

---

Envoyé par SQLPro

Pour débusquer les deadlock, vous pouvez utiliser le profiler SQL et vous trouverez dans la rubrique Lock, les événéments Deadlock, DeadlockChain et DeadlockGraph à suivre.

---

Citation:

---

Envoyé par Mikedavem

Une autre solution est de créer une alerte WMI qui insère dans une table d'historisation les différentes DEADLOCKS qui surviennent sur votre base.

---

Ou tout simplement d'utiliser la session d'évènements étendus pour les retrouver, comme je l'ai montré ici ;)

Citation:

---

Envoyé par ocean_78

J'ai créé une trace qui est à l'écoute pendant une semaine par exemple par contre lorsque je ferme ma session windows la trace s'arrête, y a t il un moyen de laisser la trace à l'écoute même session fermée ou dois je avoir la session windows ouverte en permanence à l'écran ?

---

Et hop le tutoriel sur les traces côté serveur ;)

@++ ;)

Super ! Je te remercie pour ces précieuses informations. :ccool:

Bonjour,

Vous avez également la possibilité d'activé les traces serveurs qui collectent directement les deadlocks dans les journaux de SQL server. les traces sont 1222 ou 1224. Elles peuvent être utilisé avec la syntax suivante sous TSQL

Code:

---

DBCC TRACEON(1224,-1)

---

et/ou directement activer dans les options de démarrage du service SQL Server en ajoutant dans les propriétés l'option -T1224 et/ou -T1222.

L'avantage de cette méthode est que vous n'étes pas tenu de faire de développement pour mettre en place ces traces, elles sont automatiquement concervées dans le journal de log de SQL server (ERRORLOG). Si vous utilisez l'option de démarrage vous aurez la garanti que les traces des deadlocks seront automatiquement tracées en cas de crash ou d'arrêt et redémarrage du serveur.

Il faut ensuite une petite procédure ou un script pour lire les fichiers ERRORLOG.* et collecter les informations relatifs au deadlock.

A+

Erratum sur les numeros des traces à activer:

Citation:

---

Vous avez également la possibilité d'activé les traces serveurs qui collectent directement les deadlocks dans les journaux de SQL server. les traces sont 1222 ou 1224. Elles peuvent être utilisé avec la syntax suivante sous TSQL

---

Il s'agit des traces 1204 ou 1222 et non de la trace 1224

Code:

---

DBCC TRACEON(1222,-1)

---

Citation:

---

et/ou directement activer dans les options de démarrage du service SQL Server en ajoutant dans les propriétés l'option -T1224 et/ou -T1222.

---

l'option de trace dans les options de démarrage du service SQL doit être -T1204 et/ou -T1222