Triiger DeadLock Stop trigger

Version imprimable

Bonjour

Je fais mes premiere tentative d'usage d'un trigger ett il me semble avooir reussi a creer un DeadLock

Toute les tables de la DB intervenant dans le trigger son inaccessible depuis SSMS (Querry s'execute sans fin)
Pas de problème pour les autres tables

- Est il possible de debuguer un triger ?
- Peut on savoir s'il s'est exectuté ou s'il a généré un probleme

Voici la Syntaxe de mon Trigger
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 USE [Clark] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[tg_Total] ON [Clark].[dbo].[BakMove] AFTER INSERT AS BEGIN SET NOCOUNT ON IF EXISTS ( SELECT * FROM inserted WHERE TotalStatus = 9 ) BEGIN EXEC Clark.dbo.sp_DoTotal NULL,9; EXEC Clark.dbo.sp_ClarkMail; END END
Merci de votre aide

09/03/2011, 09h12
mikedavem

Bonjour,

Oui vous pouvez utiliser le profiler pour votre déboggage avec les événements qui concernent l'éxecution de votre code + deadlock.

Locks : Deadlock
Locks : Deadlock chain
TSQL: StmtCompleted
SP : Completed
...

++
09/03/2011, 09h22
SQLpro

Postez le code de vos proc :
Clark.dbo.sp_DoTotal
Clark.dbo.sp_ClarkMail

A +
09/03/2011, 10h07
olibara
Citation:

Envoyé par SQLpro

Postez le code de vos proc :
Clark.dbo.sp_DoTotal
Clark.dbo.sp_ClarkMail

Merci SqlPro
Je n'ai pas acces au server ce matin je les posterai cet apres-midi
Hier j'ai fini par stopper le service MS Sql pour le relancer (il prenait 100% du CPU)

J'ai donc supprimé le trigger et ajouté l'appel des deux procedures stockées depuis l'application et ca ne pose pour le moment pas de souci ?

Ce que je soupconne c'est que l'application genere en boucle une suite d'insert unitaire pour chaque record a inserer dans la table
Code:

1 2 3 4 5 6 7 8 INSERT INTO ...; INSERT INTO ...; INSERT INTO ...; -- Plutot qu'un seul insert avec la liste des de chaque row INSERT INTO (fields) (values..........);
Meme si ca ne fait qu'une dizaine de row avec deux déclencheur ça a peut etre donné la migraine au trigger
09/03/2011, 12h38
iberserk

Un déclenchement en boucle infini de vos TRIGGERS semble plus probable...

A voir avec le code de vos SP...

Bonjour

Merci de votre aide
Voici le code des SP

Mais j'ai aussi contaté que le trigger je j'avais mis en place a une petite nuance de code qui peut etre de taille... Ca peut expliquer la boucle ?

J'avais écris : (sans les point virgule à la fin des deux EXEC)

Code:

1
2
3
4
BEGIN
	EXEC Clark.dbo.sp_DoTotal NULL,9
	EXEC Clark.dbo.sp_ClarkMail
END

Voici donc les deux sp :

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
USE [Clark]
GO
/****** Object:  StoredProcedure [dbo].[sp_ClarkMail]    Script Date: 03/09/2011 18:46:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ClarkMail]
AS
BEGIN
 
IF NOT EXISTS 
   (
     SELECT * FROM [TotalChauffeur]
     WHERE  MailStatus=0
    ) 
    RETURN;
 
 
EXEC msdb.dbo.sysmail_start_sp;
 
EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'oxg@skynet.com;bartmissa@wareever.com;machin@recutex.com',
    @query = 'SELECT *  FROM [Clark].[dbo].[TotalChauffeur] 
    WHERE CAST(DATE AS DATE)=CAST(GETDATE() AS DATE)
    OR MailStatus=0' ,
    @subject = 'Chauffeur Gedaan !',
    @attach_query_result_as_file = 0 ;
 
   UPDATE [Clark].[dbo].[TotalChauffeur]
   SET MailStatus=1
   WHERE CAST(DATE AS DATE)=CAST(GETDATE() AS DATE)
   OR MailStatus=0;
 
 
END

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
USE [Clark]
GO
/****** Object:  StoredProcedure [dbo].[sp_DoTotal]    Script Date: 03/09/2011 18:47:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[sp_DoTotal]
   @ChauffeurID  int,
   @TotalType  int
AS
BEGIN
INSERT INTO Clark.dbo.TotalChauffeur 
(ChauffeurID,
 ChauffeurName,
 Date,
 Gewicht)
SELECT ChauffeurID
      ,ChauffeurName
      ,MAX(Date)
      ,SUM(bruto)-sum(tarra)
  FROM 
  Clark.dbo.BakMove
  WHERE ChauffeurID IN (SELECT DISTINCT COALESCE(@ChauffeurID, ChauffeurID) 
                        FROM   BakMove 
                        WHERE  TotalStatus=CASE @TotalType
                                              WHEN 0 THEN 0
                                              WHEN 9 THEN 9
                                              ELSE 0
                                           END)
  GROUP BY ChauffeurID
          ,ChauffeurName
          ,Cast(Date AS DATE)
END
BEGIN
UPDATE
 Clark.dbo.BakMove
  SET TotalStatus=1
  WHERE ChauffeurID IN (SELECT DISTINCT COALESCE(@ChauffeurID, ChauffeurID) 
                        FROM   BakMove 
                        WHERE  TotalStatus=CASE @TotalType
                                              WHEN 0 THEN 0
                                              WHEN 9 THEN 9
                                              ELSE 0
                                           END)
 
END
          return;

Avez vous d'autres TRIGGERS ? Sur BakMove par exemple?

Vos sous requètes avec les IN m'ont l'air un peu lourdes! :zoubi:

Code:

1
2
3
4
5
6
7
8
9
10
UPDATE
 Clark.dbo.BakMove
  SET TotalStatus=1
  WHERE ChauffeurID IN (SELECT DISTINCT COALESCE(@ChauffeurID, ChauffeurID) 
                        FROM   BakMove 
                        WHERE  TotalStatus=CASE @TotalType
                                              WHEN 0 THEN 0
                                              WHEN 9 THEN 9
                                              ELSE 0
                                           END)

09/03/2011, 21h08
olibara

Aucun autre trigger !
Un peu lourdes : Qu'est ce que ca a de lourd ?

C'est pas tres sorcier et cette table ne contient et ne contiendra que qq centaines de records !!

Bonjour,

L'appel à msdb.dbo.sysmail_start_sp est inutile.
Je ne sais pas quel est l'effet de l'appel à plusieurs reprises ...

La requête pour l'envoi de mail :
Code:

1 2 3 SELECT * FROM Clark.dbo.TotalChauffeur WHERE CAST(DATE AS date) = CAST(GETDATE() AS date)
le WHERE est cochon, puisqu'il à priori pas SARGable.
Tu as de la chance d'être sous SQL Server 2008, autrement, scan de table ;)
Ça fonctionne assez bien donc, mais si tu écris :

Code:

WHERE DATE BETWEEN CAST(GETDATE() AS date) AND CAST(DATEADD(day, 1, GETDATE()) AS date)

Ça marchera un poil mieux ;)
Cela dit je ne crois pas que ce soit l'origine de ton problème de deadlocks.
Cela explique par contre le CPU, puisque la résolution de deadlocks coûte assez cher en CPU.

Au passage, vire les crochets, ils ne servent à rien à part alourdir le texte de la requête.
Ils servent aux gens qui ne respectent pas les règles de nommage SQL (en mettant des espaces ou des caractères de ponctuation dans les noms des objets ou des colonnes).

Tu peux changer l'UPDATE en :
Code:

1 2 3 4 UPDATE dbo.TotalChauffeur SET MailStatus=1 WHERE DATE BETWEEN CAST(GETDATE() AS date) AND CAST(DATEADD(day, 1, GETDATE()) AS date) OR MailStatus = 0
Cela dit le OR est moche, pour la même raison que précédemment, d'autant que tu as écrit :
Code:

1 2 3 4 5 6 7 IF NOT EXISTS ( SELECT * FROM dbo.TotalChauffeur WHERE MailStatus = 0 ) RETURN
Je pense que tu peux enlever le OR.

Pour sp_DoTotal, le BEGIN au milieu de la procédure est inapproprié, malheureusement SQL Server le compile quand même ...
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 ALTER PROCEDURE [dbo].[sp_DoTotal] @ChauffeurID int, @TotalType int AS BEGIN INSERT INTO Clark.dbo.TotalChauffeur ( ChauffeurID , ChauffeurName , Date , Gewicht ) SELECT BM.ChauffeurID , BM.ChauffeurName , MAX(BM.Date) , SUM(BM.bruto) - SUM(BM.tarra) FROM Clark.dbo.BakMove AS BM INNER JOIN Clark.dbo.BakMove AS BM2 ON BM.ChauffeurID = BM2.ChauffeurID WHERE BM2.TotalStatus = CASE WHEN @TotalType = 9 THEN 9 ELSE 0 END AND ( @ChauffeurID IS NULL OR BM2.ChauffeurID = @ChauffeurID ) GROUP BY BM.ChauffeurID, BM.ChauffeurName, CAST(BM.Date AS DATE) UPDATE Clark.dbo.BakMove SET TotalStatus = 1 WHERE ( @ChauffeurID IS NULL OR ChauffeurID = @ChauffeurID ) AND TotalStatus = CASE WHEN @TotalType = 9 THEN 9 ELSE 0 END END
Mais pour moi cela n'explique toujours pas tes deadlocks.

Citation:

cette table ne contient et ne contiendra que qq centaines de records !!

Ce n'est pas une raison (dans ton cas c'est même pire :)).
On ne dit pas record ou enregistrement, on dit row ou ligne ;) (Voir ma signature et ceci) ;)

Fais-toi une trace au Profiler en choisissant uniquement l'événement Deadlock Graph :

- Sous SSMS, Outils > SQL Profiler
- Connectes-toi à l'instance à surveiller
- Coches la case "Sauvegarder dans un fichier"
- Bascules dans l'onglet "Evénements"
- Décoches toutes les cases les plus à gauche
- Coches "Afficher tous les événements"
- Coches uniquement, sous la catégorie "Locks" l'événement "Deadlock Graph"
- Cliques "Exécuter"

Dès qu'un deadlock va se produire, une ligne va s'afficher dans la trace.
Si tu cliques sur celle-ci, un graphe va apparaître dans la partie inférieure du Profiler, te donnant des indications sur l'origine du deadlock.
Tu peux poster le graphe sur le forum ;)

@++ ;)

10/03/2011, 20h06
olibara

Citation:

Envoyé par elsuket

L'appel à msdb.dbo.sysmail_start_sp est inutile.
Je ne sais pas quel est l'effet de l'appel à plusieurs reprises ...

Bonsoir elsuket

Je viens d'essayer de l'enlever et l'envoi de mail ne marche plus !
Il n'est pas impossible que ce soit lié au fait que je travaille avex Xpress et que dans ce cas le service mail doit etre démaré explicitement !

Car c'est depuis que j'ai ajouté cette instruction que mes envois fonctionnenent avec Xpress
Sinon il me dit "mail queued" sans plus
10/03/2011, 23h01
olibara

Bonsoir Elsuket

J'ai lu ton article concernant le SARGable, Si je comprends en gros ce que tu explique tu veux dire Que dans ce cas ci

Code:

WHERE CAST(DATE AS date) = CAST(GETDATE() AS date)

On force un cast de Date a chaque ligne
Alors qu'ici

Code:

WHERE DATE BETWEEN CAST(GETDATE() AS date) AND CAST(DATEADD(day, 1, GETDATE()) AS date)

Le cast se fait une fois pour la definition de l'intervalle et la date est evaluée a chaque ligne par rapport a l'intervalle défini, ce qui en theorie allege le process

Mais je ne suis pas certain que le gain soit vraiment signifiant ?

Citation:

Le cast se fait une fois pour la definition de l'intervalle et la date est evaluée a chaque ligne par rapport a l'intervalle défini, ce qui en theorie allege le process

J'aurai plutôt écrit :

Le cast se fait une fois pour la définition de l'intervalle et les valeurs de la colonne date sont évaluées a chaque ligne par rapport a l'intervalle défini, ce qui en théorie allège le process.

Désolé de chipoter.

Citation:

Mais je ne suis pas certain que le gain soit vraiment signifiant ?

J'ai mesuré ça sur une très petite table à moi d'une base de données SQL Server 2008, et je suis passé de 31 pages à 5.
Le gain n'est donc pas énorme, mais ma table très est petite (quelques milliers de lignes).

Que te donne la requête suivante :
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 DECLARE @ts_now BIGINT -- 2005 select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info -- 2008 SELECT @ts_now = ms_ticks FROM sys.dm_os_sys_info; SELECT DATEADD(ms, -1 * (@ts_now - time_stamp), GETDATE()) AS event_date_time , SQLProcessUtilization AS SQLServer , 100 - SystemIdle - SQLProcessUtilization AS Other , SystemIdle AS Free , UserModeTime , KernelModeTime , PageFaults , WorkingSetDelta , MemoryUtilPct FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'tinyint') AS SystemIdle , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'tinyint') AS SQLProcessUtilization , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]', 'bigint') AS UserModeTime , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]', 'bigint') AS KernelModeTime , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/PageFaults)[1]', 'bigint') AS PageFaults , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta)[1]', 'bigint') AS WorkingSetDelta , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'bigint') AS MemoryUtilPct , time_stamp FROM ( SELECT timestamp AS time_stamp , CAST(record AS xml) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%<SystemHealth>%' ) AS x ) AS y ORDER BY record_id DESC GO
@++ ;)

11/03/2011, 11h11
aieeeuuuuu

Bonjour

Citation:

Envoyé par olibara

J'ai lu ton article concernant le SARGable,...

Peut-être que celui-ci, également écrit par Elsuket, concernant les deadlocks pourra aussi t’intéresser
11/03/2011, 12h51
elsuket

'M'en rappelais pas de celui-là ... je prends zéro :)

Merci aieeeuuuuu !

@++ ;)
11/03/2011, 17h49
aieeeuuuuu

Citation:

Envoyé par elsuket

'M'en rappelais pas de celui-là ... je prends zéro :)

Merci aieeeuuuuu !

@++ ;)

Merci à toi... pour l'article ! car si moi je m'en souvenais, c'est que je l'avais trouvé fort utile ! :ccool: