Procédure stockée déclenchant un trigger et Deadlock

**elsuket** · 16/07/2007, 12h46

Bonjour,

Je suis confronté au problème des deadlock sous SQL Server 2005, par une procédure stockée exécutée de nombreuses fois sur la BD.
J'ai utilisé SQL Server Profiler (catégorie d'événements Locks) pour trouver le statement à l'origine du problème, mais rien de remonte. Or les triggers ne remontent pas sous SQL Server Profiler, je pense donc que mon problème se situe là.
Voici la procédure stockée:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
BEGIN
	-- Retrieves IDOPERATOR and IDGATEWAY for this message in SMSSENT
	DECLARE @GET_ID_OPERATOR INT;
	DECLARE @GET_ID_GATEWAY TINYINT;
 
	SELECT @GET_ID_OPERATOR = IDOPERATOR,
			@GET_ID_GATEWAY = IDGATEWAY
	FROM SMSSENT
	WHERE REFNO = @REF_NO;
 
	IF (@GET_ID_OPERATOR IS NOT NULL) AND (@GET_ID_GATEWAY IS NOT NULL)
	BEGIN
		SET XACT_ABORT ON;
		BEGIN TRY
			-- Gets the IDOPERATORRESPONSE if we do not know it
			-- and/or updates the SMSOPRATORRESPONSESTATS
			DECLARE @GEN_ID_OPERATOR_RESPONSE INT;
			EXEC @GEN_ID_OPERATOR_RESPONSE = spSMSOPERATORRESPONSESTAT_UPDATE @ID_OPERATOR_RESPONSE, @OPERATOR_RESPONSE, @GET_ID_OPERATOR, @GET_ID_GATEWAY;
			BEGIN TRANSACTION;
				-- Updates the informations of this MT
				UPDATE SMSSENT
				SET LASTUPDATEDDATETIME = GETDATE(),
					IDBILLINGSTATUS = @ID_BILLING_STATUS,
					DLVSTATUSRECEIVEDTIME = @DLV_STATUS_RECEIVED_TIME,
					DLVSTATUSSTAMPTIME = @DLV_STATUS_STAMP_TIME,
					IDOPERATORRESPONSE = @GEN_ID_OPERATOR_RESPONSE
				WHERE REFNO = @REF_NO;
				SELECT 1;
			COMMIT TRANSACTION;
		END TRY
		BEGIN CATCH
			IF (XACT_STATE()) = -1 -- If a transaction stays uncommittable
			BEGIN
				ROLLBACK TRANSACTION;
				DECLARE @ERR_MSG VARCHAR(512);
				SET @ERR_MSG = 'spPARSER_SMSSENT_UPDATE_IDBILLINGSTATUS : Unable to update the IDBILLINGSTATUS for REFNO ' + CONVERT(VARCHAR, @REF_NO) + 'Error : '+  ERROR_MESSAGE();
				RAISERROR (@ERR_MSG, 16, 1);
			END;
		END CATCH;
	END;
	SELECT 0;
END;

Je trouve le code de gestion d'erreur un peu lourd, il doit y avoir des choses à corriger là-dedans ...

Et voici le code de mon trigger:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
BEGIN
	-- Preventing resultsets to be returned to some application
	SET NOCOUNT ON;
 
	DECLARE @ID_PARENT BIGINT;
	DECLARE @ID_MESSAGE BIGINT;
	DECLARE @ID_BILLING_STATUS TINYINT;
	DECLARE @ID_CUSTOMER INT;
	DECLARE @ID_BILLING INT;
 
	-- Gathering infos from the message that has just been inserted
	SELECT @ID_PARENT = INSERTED.IDPARENT, 
			@ID_MESSAGE = INSERTED.IDMESSAGE,
			@ID_BILLING_STATUS = INSERTED.IDBILLINGSTATUS,
			@ID_CUSTOMER = INSERTED.IDCUSTOMER,
			@ID_BILLING = INSERTED.IDBILLING
	FROM INSERTED;
 
	SET XACT_ABORT ON;
	BEGIN TRY
		------------------------------
		-- IDBILLINGSTATUS :  BILLED -
		------------------------------
		IF @ID_BILLING_STATUS = 1
		BEGIN
			IF @ID_PARENT IS NULL OR @ID_PARENT = 0 
			BEGIN
				BEGIN TRANSACTION;
				-- Moves the message from SMSSENT to SMSSENDSTAT with BILLINGSTATUS at 'BILLED' (1)
				INSERT dbo.SMSSENDSTAT
				SELECT IDMESSAGE, GUID, MSN, MSG, SENDDATETIME, LASTUPDATEDDATETIME, SNO, TPOA, REFNO, IDCUSTOMER, IDCONTENTTYPE, IDCONTENTOWNER, IDCONTENT, IDCONTENTCERTIFICATE,
						IDPRICEGROUP, IDOPERATOR, IDNETWORK, IDGATEWAY, IDPREFIX, IDSHORTSNO, IDSERVICE, IDMEDIACODE, IDACTION, IDBILLING, @ID_BILLING_STATUS,
						DLVSTATUSRECEIVEDTIME, DLVSTATUSSTAMPTIME, IDOPERATORRESPONSE, IDDELIVERYTYPE, IDACKTYPE, OPERATORCUSTOMERID, LINKEDID, RETRYCOUNT
				FROM dbo.SMSSENT (nolock)
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				DELETE FROM dbo.SMSSENT
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				COMMIT TRANSACTION;
			END;
			-- If ID_PARENT is not null and != 0
			ELSE
			BEGIN
				BEGIN TRANSACTION;
				-- Moves the message from SMSSENT to SMSRETRYSTAT with RETRYSTATUS at 'SUCCESSFULLY SENT' (2)
				INSERT dbo.SMSRETRYSTAT
				SELECT IDMESSAGE, TRANSID, GUID, MSN, MSG, UNICODE, SENDDATETIME, LASTUPDATEDDATETIME, SNO, TPOA, PRIORITY, REFNO, IDCUSTOMER, IDCONTENTTYPE, IDCONTENTOWNER,
						IDCONTENT, IDCONTENTCERTIFICATE, IDPRICEGROUP, IDOPERATOR, IDNETWORK, IDGATEWAY, IDPREFIX, IDSHORTSNO, IDSERVICE, IDMEDIACODE,
						IDACTION, IDBILLING, IDBILLINGSTATUS, DLVSTATUSRECEIVEDTIME, DLVSTATUSSTAMPTIME, IDOPERATORRESPONSE, IDDELIVERYTYPE, IDACKTYPE,
						OPERATORCUSTOMERID, LINKEDID, 2 /*IDRETRYSTATUS*/, RETRYCOUNT, IDPARENT
				FROM dbo.SMSSENT (nolock)
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				DELETE FROM SMSSENT
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				-- Moves the message that has its IDPARENT = @ID_MESSAGE from SMSSENT to SMSSENDSTAT with BILLINGSTATUS at 'BILLED' (1)
				INSERT dbo.SMSSENDSTAT
				SELECT IDMESSAGE, GUID, MSN, MSG, SENDDATETIME, LASTUPDATEDDATETIME, SNO, TPOA, REFNO, IDCUSTOMER, IDCONTENTTYPE, IDCONTENTOWNER, IDCONTENT, IDCONTENTCERTIFICATE,
						IDPRICEGROUP, IDOPERATOR, IDNETWORK, IDGATEWAY, IDPREFIX, IDSHORTSNO, IDSERVICE, IDMEDIACODE, IDACTION, IDBILLING, @ID_BILLING_STATUS,
						DLVSTATUSRECEIVEDTIME, DLVSTATUSSTAMPTIME, IDOPERATORRESPONSE, IDDELIVERYTYPE, IDACKTYPE, OPERATORCUSTOMERID, LINKEDID, RETRYCOUNT
				FROM dbo.SMSSENT (nolock)
				WHERE IDMESSAGE = @ID_PARENT;
 
				DELETE FROM dbo.SMSSENT
				WHERE IDMESSAGE = @ID_PARENT;
 
				COMMIT TRANSACTION;
			END;
 
			-- Updates the LASTBILLEDDATETIME in SMSSUBSCRIBER if the price is greater than 0
			DECLARE @PRICE DECIMAL (8,2)
			SELECT @PRICE = PRICE
			FROM dbo.SMSBILLING (nolock)
			WHERE IDBILLING = @ID_BILLING;
 
			IF @PRICE IS NOT NULL AND @PRICE > 0
			BEGIN
				UPDATE SMSSUBSCRIBER
				SET LASTBILLEDDATETIME = GETDATE()
				WHERE IDCUSTOMER = @ID_CUSTOMER		
			END;
		END;
 
		--------------------------------------
		-- IDBILLINGSTATUS : TEMPORARY ERROR -
		--------------------------------------
		IF @ID_BILLING_STATUS = 2
		BEGIN
			IF @ID_PARENT IS NOT NULL AND @ID_PARENT != 0
			BEGIN
				BEGIN TRANSACTION;
				-- Copies the message from SMSSENT to SMSRETRYSTAT with RETRYSTATUS at 'SUCCESSFULLY SENT' (2)
				INSERT dbo.SMSRETRYSTAT
				SELECT IDMESSAGE, TRANSID, GUID, MSN, MSG, UNICODE, SENDDATETIME, LASTUPDATEDDATETIME, SNO, TPOA, PRIORITY, REFNO, IDCUSTOMER, IDCONTENTTYPE, IDCONTENTOWNER,
						IDCONTENT, IDCONTENTCERTIFICATE, IDPRICEGROUP, IDOPERATOR, IDNETWORK, IDGATEWAY, IDPREFIX, IDSHORTSNO, IDSERVICE, IDMEDIACODE,
						IDACTION, IDBILLING, IDBILLINGSTATUS, DLVSTATUSRECEIVEDTIME, DLVSTATUSSTAMPTIME, IDOPERATORRESPONSE, IDDELIVERYTYPE, IDACKTYPE,
						OPERATORCUSTOMERID, LINKEDID, 2 /*IDRETRYSTATUS*/, RETRYCOUNT, IDPARENT
				FROM dbo.SMSSENT (nolock)
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				COMMIT TRANSACTION;
			END;
		END;
 
		-----------------------------------
		-- IDBILLINGSTATUS :  FINAL ERROR -
		-----------------------------------
		IF @ID_BILLING_STATUS = 3
		BEGIN
			IF @ID_PARENT IS NULL OR @ID_PARENT = 0 
			BEGIN
				BEGIN TRANSACTION;
				-- Moves the message from SMSSENT to SMSSENDSTAT with BILLINGSTATUS at 'FINAL ERROR' (3)
				INSERT dbo.SMSSENDSTAT
				SELECT IDMESSAGE, GUID, MSN, MSG, SENDDATETIME, LASTUPDATEDDATETIME, SNO, TPOA, REFNO, IDCUSTOMER, IDCONTENTTYPE, IDCONTENTOWNER, IDCONTENT, IDCONTENTCERTIFICATE,
						IDPRICEGROUP, IDOPERATOR, IDNETWORK, IDGATEWAY, IDPREFIX, IDSHORTSNO, IDSERVICE, IDMEDIACODE, IDACTION, IDBILLING, @ID_BILLING_STATUS,
						DLVSTATUSRECEIVEDTIME, DLVSTATUSSTAMPTIME, IDOPERATORRESPONSE, IDDELIVERYTYPE, IDACKTYPE, OPERATORCUSTOMERID, LINKEDID, RETRYCOUNT
				FROM dbo.SMSSENT (nolock)
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				DELETE FROM dbo.SMSSENT
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				COMMIT TRANSACTION;
			END;
			-- If ID_PARENT is not null and != 0
			ELSE
			BEGIN
				BEGIN TRANSACTION;
				-- Moves the message from SMSSENT to SMSRETRYSTAT with RETRYSTATUS at 'SUCCESSFULLY SENT' (2)
				INSERT dbo.SMSRETRYSTAT
				SELECT IDMESSAGE, TRANSID, GUID, MSN, MSG, UNICODE, SENDDATETIME, LASTUPDATEDDATETIME, SNO, TPOA, PRIORITY, REFNO, IDCUSTOMER, IDCONTENTTYPE, IDCONTENTOWNER,
						IDCONTENT, IDCONTENTCERTIFICATE, IDPRICEGROUP, IDOPERATOR, IDNETWORK, IDGATEWAY, IDPREFIX, IDSHORTSNO, IDSERVICE, IDMEDIACODE,
						IDACTION, IDBILLING, IDBILLINGSTATUS, DLVSTATUSRECEIVEDTIME, DLVSTATUSSTAMPTIME, IDOPERATORRESPONSE, IDDELIVERYTYPE, IDACKTYPE,
						OPERATORCUSTOMERID, LINKEDID, 2 /*IDRETRYSTATUS*/, RETRYCOUNT, IDPARENT
				FROM dbo.SMSSENT (nolock)
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				DELETE FROM dbo.SMSSENT
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				-- Moves the message that has its IDPARENT = @ID_MESSAGE from SMSSENT to SMSSENDSTAT with BILLINGSTATUS at 'FINAL ERROR' (3)
				INSERT dbo.SMSSENDSTAT
				SELECT IDMESSAGE, GUID, MSN, MSG, SENDDATETIME, LASTUPDATEDDATETIME, SNO, TPOA, REFNO, IDCUSTOMER, IDCONTENTTYPE, IDCONTENTOWNER, IDCONTENT, IDCONTENTCERTIFICATE,
						IDPRICEGROUP, IDOPERATOR, IDNETWORK, IDGATEWAY, IDPREFIX, IDSHORTSNO, IDSERVICE, IDMEDIACODE, IDACTION, IDBILLING, 3 /*IDBILLINGSTATUS*/,
						DLVSTATUSRECEIVEDTIME, DLVSTATUSSTAMPTIME, IDOPERATORRESPONSE, IDDELIVERYTYPE, IDACKTYPE, OPERATORCUSTOMERID, LINKEDID, RETRYCOUNT
				FROM dbo.SMSSENT (nolock)
				WHERE IDMESSAGE = @ID_PARENT;
 
				DELETE FROM dbo.SMSSENT
				WHERE IDMESSAGE = @ID_PARENT;
 
				COMMIT TRANSACTION;
			END;
		END;
 
		-----------------------------------------------------
		-- IDBILLINGSTATUS : ABORTING RETRY : LIMIT REACHED -
		-----------------------------------------------------
		IF @ID_BILLING_STATUS = 4
		BEGIN
			IF @ID_PARENT IS NULL OR @ID_PARENT = 0 
			BEGIN
				BEGIN TRANSACTION;
				-- Moves the message from SMSSENT to SMSSENDSTAT with BILLINGSTATUS at 'ABORTING RETRY' (4)
				INSERT dbo.SMSSENDSTAT
				SELECT IDMESSAGE, GUID, MSN, MSG, SENDDATETIME, LASTUPDATEDDATETIME, SNO, TPOA, REFNO, IDCUSTOMER, IDCONTENTTYPE, IDCONTENTOWNER, IDCONTENT, IDCONTENTCERTIFICATE,
						IDPRICEGROUP, IDOPERATOR, IDNETWORK, IDGATEWAY, IDPREFIX, IDSHORTSNO, IDSERVICE, IDMEDIACODE, IDACTION, IDBILLING, @ID_BILLING_STATUS,
						DLVSTATUSRECEIVEDTIME, DLVSTATUSSTAMPTIME, IDOPERATORRESPONSE, IDDELIVERYTYPE, IDACKTYPE, OPERATORCUSTOMERID, LINKEDID, RETRYCOUNT
				FROM dbo.SMSSENT (nolock)
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				DELETE FROM dbo.SMSSENT
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				COMMIT TRANSACTION;
			END
			-- If ID_PARENT is not null and != 0
			ELSE
			BEGIN
				BEGIN TRANSACTION;
				/*
				-- Moves the message from SMSSENT to SMSRETRYSTAT with RETRYSTATUS at 'SUCCESSFULLY SENT' (2)
				INSERT dbo.SMSRETRYSTAT
				SELECT IDMESSAGE, TRANSID, GUID, MSN, MSG, UNICODE, SENDDATETIME, SNO, PRIORITY, REFNO, IDCUSTOMER, IDCONTENTTYPE, IDCONTENTOWNER,
						IDCONTENT, IDCONTENTCERTIFICATE, IDPRICEGROUP, IDOPERATOR, IDNETWORK, IDGATEWAY, IDPREFIX, IDSHORTSNO, IDSERVICE, IDMEDIACODE,
						IDACTION, IDBILLING, IDBILLINGSTATUS, DLVSTATUSRECEIVEDTIME, DLVSTATUSSTAMPTIME, IDOPERATORRESPONSE, IDDELIVERYTYPE, IDACKTYPE,
						OPERATORCUSTOMERID, LINKEDID, 2 /*IDRETRYSTATUS*/, RETRYCOUNT, IDPARENT
				FROM dbo.SMSSENT (nolock)
				WHERE IDMESSAGE = @ID_MESSAGE
				*/
 
				DELETE FROM dbo.SMSSENT
				WHERE IDMESSAGE = @ID_MESSAGE;
 
				-- Moves the message that has its IDPARENT = @ID_MESSAGE from SMSSENT to SMSSENDSTAT with BILLINGSTATUS at 'ABORTING RETRY' (4)
				INSERT dbo.SMSSENDSTAT
				SELECT IDMESSAGE, GUID, MSN, MSG, SENDDATETIME, LASTUPDATEDDATETIME, SNO, TPOA, REFNO, IDCUSTOMER, IDCONTENTTYPE, IDCONTENTOWNER, IDCONTENT, IDCONTENTCERTIFICATE,
						IDPRICEGROUP, IDOPERATOR, IDNETWORK, IDGATEWAY, IDPREFIX, IDSHORTSNO, IDSERVICE, IDMEDIACODE, IDACTION, IDBILLING, @ID_BILLING_STATUS,
						DLVSTATUSRECEIVEDTIME, DLVSTATUSSTAMPTIME, IDOPERATORRESPONSE, IDDELIVERYTYPE, IDACKTYPE, OPERATORCUSTOMERID, LINKEDID, RETRYCOUNT
				FROM dbo.SMSSENT (nolock)
				WHERE IDMESSAGE = @ID_PARENT;
 
				DELETE FROM dbo.SMSSENT
				WHERE IDMESSAGE = @ID_PARENT;
 
				COMMIT TRANSACTION;
			END;
		END;
 
	---------------------
	-- Error Management -
	---------------------
	END TRY
	BEGIN CATCH
		IF (XACT_STATE()) = -1
		BEGIN
			ROLLBACK TRANSACTION;
		END;
 
		DECLARE @MAIL_MSG VARCHAR(1024);
		SET @MAIL_MSG = ERROR_MESSAGE() + '<br><br>@ID_MESSAGE : ' + COALESCE(CONVERT(VARCHAR, @ID_MESSAGE), 'NULL') + '<br>@ID_PARENT : ' + COALESCE(CONVERT(VARCHAR, @ID_PARENT), 'NULL') +'<br>@ID_BILLING_STATUS : ' + COALESCE(CONVERT(VARCHAR, @ID_BILLING_STATUS), 'NULL') +'<br>@ID_CUSTOMER : ' + COALESCE(CONVERT(VARCHAR, @ID_CUSTOMER), 'NULL') +'<br>@ID_ACTION : ' + COALESCE(CONVERT(VARCHAR, @ID_BILLING), 'NULL');
		EXEC spSEND_DBMAIL 'adresses', '[TR_AIU_SMSSENT]', @MAIL_MSG;
 
		SET @MAIL_MSG = 'TR_AIU_SMSSENT : ' + REPLACE(@MAIL_MSG, '<br>', '');
		RAISERROR(@MAIL_MSG, 16, 1);
	END CATCH;
	SET NOCOUNT OFF;
END;

Je suis débutant, je ne sais pas où sont les erreurs dans mon code.
Dois-je factoriser le code du trigger dans deux procédures stockées ?

En vous remerciant

**kagemaru** · 16/07/2007, 14h08

je vois des delete, des inserts d'une même table dans la proc et dans le trigger .... ça me parait un peu louche :-)

est-ce que tu as le détail du deadlock ? cela te permettra de voir où agir. Il faut activer le traceflag 1204.

si tu peux reproduire dans une session d'analyseur de requêtes, avant de lancer ta requêtes tu tapes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
dbcc traceon(1204)
go
requete....

tu auras alors le tables impliquées et les sessions. sinon tu dois activer le traceflag au niveau du serveur (en utilisant le service de configuration du serveur)

Procédure stockée déclenchant un trigger et Deadlock

Développement SQL Server

Discussions similaires

Partager

Partager