[SQL2K] Deadlock sur transaction

Version imprimable

Bonjour à tous.

J'ai une procédure stockée qui me permet de générer des numéros uniques.
Elle est utilisée par une dizaine d'application en même temps sur la base et est appellée plusieurs fois par seconde.

Le problème est que j'ai des deadlock :

Citation:

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Est ce que quelqu'un peut me dire ce qui ne va pas dans le code?
Merci.

Voici son code.

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
 
CREATE Procedure [dbo].[spr_getcounter]
	@countername AS VARCHAR(128),
	@countervalue AS BIGINT = NULL OUTPUT
AS
 
	DECLARE @strQuery VARCHAR(8000)
	DECLARE @ok INT
	SET @ok = 0
	DECLARE @nextvalue INT
	SET @nextvalue = -1
 
	SET NOCOUNT ON
 
--	Highest transaction level. Only one connection can enter this at the same time
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRAN GET_COUNTER
 
--	Search for coutername entry in counter table
	SET @ok = 0
	SELECT @ok = COUNT(*) FROM tbl_counters WHERE cnt_name = @countername
-- Insert countername entry if necessary
	IF @ok = 0
	BEGIN
		SET @strQuery = 'INSERT INTO tbl_counters (cnt_name, cnt_lastvalue) VALUES (''' +  @countername + ''', 0)'
		EXEC(@strQuery)
	END
--	Getting next counter value
	SELECT @nextvalue = cnt_lastvalue + 1 FROM tbl_counters WHERE cnt_name = @countername
--	Update last value in counters table
	UPDATE tbl_counters SET cnt_lastvalue = @nextvalue WHERE cnt_name = @countername
 
--	Return counter
	SET @countervalue = @nextvalue	
--	Commit transaction
	COMMIT TRAN GET_COUNTER
 
	SET NOCOUNT OFF
	RETURN 0
 
ERROR_HANDLER:
	ROLLBACK TRAN GET_COUNTER
	SET NOCOUNT OFF
	RETURN -1

De nombreuses horreurs dans ce code :
1) vous placez votre transaction au niveau SERIALIZABLE dans la session, mais vous ne revenez pas au niveau antérieur à la fin de la transaction.
Conséquence, tout ce qui se fait dans la session est dorénavant en mode SERIALIZABLE ce qui pose un verrou exclusif sur toutes les tables.
2) vous utilisez des count(*) gourmand (verrouillage exclusif de la table) pour des requêtes qui devraient être faites avec l'opérateur EXISTS
3) vous ne testez jamais le retour d'exécution de vos requêtes et tentez de forcer un COMMIT même si des requêtes échouent...

Lisez l'article que j'ai écrit à ce sujet; il contient une procédure un peu plus pro...
http://sqlpro.developpez.com/cours/clefs/

Mais en voici une encore plus "juste"
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE Procedure [dbo].[spr_getcounter] @countername AS VARCHAR(128), @countervalue AS BIGINT = NULL OUTPUT AS UPDATE tbl_counters SET cnt_lastvalue = cnt_lastvalue + 1, @countervalue = cnt_lastvalue + 1 WHERE cnt_name = @countername GO
A +

Citation:

Envoyé par SQLpro

De nombreuses horreurs dans ce code :

Effectivement .. c'est pour cela que ça ne marche pas.

Citation:

1) vous placez votre transaction au niveau SERIALIZABLE dans la session, mais vous ne revenez pas au niveau antérieur à la fin de la transaction.
Conséquence, tout ce qui se fait dans la session est dorénavant en mode SERIALIZABLE ce qui pose un verrou exclusif sur toutes les tables.

le niveau par défaut est READ COMMITTED. Si je met

Code:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

en fin de procedure cela corrige t il ce point?

Citation:

2) vous utilisez des count(*) gourmand (verrouillage exclusif de la table) pour des requêtes qui devraient être faites avec l'opérateur EXISTS

Oui c'est juste. J'ai donc remplacé @ok = count(*) par @ok=1

Citation:

3) vous ne testez jamais le retour d'exécution de vos requêtes et tentez de forcer un COMMIT même si des requêtes échouent...

J'ai les tests de retours dans la procédure, mais je ne l'ai ai pas mis dans le message.

Citation:

Lisez l'article que j'ai écrit à ce sujet; il contient une procédure un peu plus pro...
http://sqlpro.developpez.com/cours/clefs/

Déjà fait depuis longtemps, d'ailleurs je me suis inspiré de la procédure SP_SYS_DB_CALC_NEW_KEY présente dans ce cours pour faire la mienne.

On y retrouve les horreurs précédement citées 1), 2) et 3) :aie: . Vous me direz, c'est marqué valable pour SQL Server V7 (ce que je n'avais pas vu il y a quelques moi lorsque j'ai lu ce cours)
Citation:
Mais en voici une encore plus "juste"
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE Procedure [dbo].[spr_getcounter] @countername AS VARCHAR(128), @countervalue AS BIGINT = NULL OUTPUT AS UPDATE tbl_counters SET cnt_lastvalue = cnt_lastvalue + 1, @countervalue = cnt_lastvalue + 1 WHERE cnt_name = @countername GO
A +
La synthèse de tout ceci m'a fait changer le code. Apparement plus de deadlock (pour l'instant). Le voici:
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 CREATE Procedure [dbo].[spr_getcounter] @countername AS VARCHAR(128), @countervalue AS BIGINT = NULL OUTPUT AS DECLARE @strQuery VARCHAR(8000) DECLARE @ok INT SET @ok = 0 SET NOCOUNT ON -- Highest transaction level. Only one connection can enter this at the same time SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN GET_COUNTER -- Search for coutername entry in counter table SET @ok = 0 SELECT @ok = 1 FROM tbl_counters WHERE cnt_name = @countername IF @@ERROR > 0 BEGIN GOTO ERROR_HANDLER END -- Insert countername entry if necessary IF @ok = 0 BEGIN SET @strQuery = 'INSERT INTO tbl_counters (cnt_name, cnt_lastvalue) VALUES (''' + @countername + ''', 0)' EXEC(@strQuery) IF @@ERROR > 0 BEGIN GOTO ERROR_HANDLER END END -- Update last value in counters table UPDATE tbl_counters SET cnt_lastvalue = cnt_lastvalue + 1, @countervalue = cnt_lastvalue + 1 WHERE cnt_name = @countername IF @@ERROR > 0 BEGIN GOTO ERROR_HANDLER END -- Commit transaction COMMIT TRAN GET_COUNTER SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET NOCOUNT OFF RETURN 0 ERROR_HANDLER: DECLARE @err INT SET @err = @@ERROR ROLLBACK TRAN GET_COUNTER SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET NOCOUNT OFF RETURN @err
Merci.
@++

Vous n'avez même pas besoin du niveau d'isolation SERIALIZABLE dans ce cas... En effet, L'UPDATE fait un verrou exclusif au moins sur la ligne, ce qui protège en même temps la lecture.

Voici donc la version épurée et fonctionnelle :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE Procedure [dbo].[spr_getcounter]
	@countername AS VARCHAR(128),
	@countervalue AS BIGINT = NULL OUTPUT
AS
 
 
        IF NOT EXISTS(SELECT * 
                      FROM   tbl_counters 
                      WHERE cnt_name = @countername)
           INSERT INTO tbl_counters (cnt_name,     cnt_lastvalue) 
                             VALUES (@countername, 0)  
 
	UPDATE tbl_counters 
        SET    cnt_lastvalue = cnt_lastvalue + 1, 
               @countervalue = cnt_lastvalue + 1 
        WHERE cnt_name = @countername

A +

04/04/2007, 09h37
abelman

Je ne suis pas très à l'aise avec les verrous et le transact SQL et j'ai besoin de comprendre quelque chose.

Que se passe t il lors du scénario suivant?

Soit deux processus (donc deux connexions) qui appellent cette procédure au même moment avec le paramètre @countername = 'TRADEID'. On considère que cette entrée n'existe pas encore dans la table tbl_counters.

- Le processus N°1 fait le IF NOT EXIST qui renvoie TRUE.

- Avant que le processus N°1 éxecute la requête INSERT, le processus N°2 fait lui aussi le IF NOT EXIST, qui renvoie TRUE.

- Le processus N°1 fait un INSERT qui se passe bien.

- Le processus N°2 fait un INSERT qui va échouer car la colonne cnt_name est la clé unique de la table tbl_counters.

Je pense que ce scénario est envisageable. Vrai ou Faux?
C'est pour cela que je pensais avoir besoin d'un niveau d'isolation serializable.

Si on enlève la partie sur l'insertion ça marche bien, car l'update mets un verrou sur la ligne effectivement. Mais il faut penser à creer les nouvelles entrées ailleurs et cela ne me convient pas.

Merci de votre aide ;)
04/04/2007, 10h50
SQLpro

Citation:

Que se passe t il lors du scénario suivant?

Soit deux processus (donc deux connexions) qui appellent cette procédure au même moment avec le paramètre @countername = 'TRADEID'. On considère que cette entrée n'existe pas encore dans la table tbl_counters.

- Le processus N°1 fait le IF NOT EXIST qui renvoie TRUE.

- Avant que le processus N°1 éxecute la requête INSERT, le processus N°2 fait lui aussi le IF NOT EXIST, qui renvoie TRUE.

- Le processus N°1 fait un INSERT qui se passe bien.

- Le processus N°2 fait un INSERT qui va échouer car la colonne cnt_name est la clé unique de la table tbl_counters.

Je pense que ce scénario est envisageable. Vrai ou Faux?

Cet échec n'a aucune conséquence puisque vous continuez et allez récupérer la valeur dans la seconde requête !

CQFD...

A +

Tjrs pas résolu

Citation:

Envoyé par SQLpro

Cet échec n'a aucune conséquence puisque vous continuez et allez récupérer la valeur dans la seconde requête !

CQFD...

A +

:roll: L'erreur ne va t elle pas arrêter l'éxécution de la procédure stockée?
Merci pour votre aide.

Je me suis permis d'améliorer encore la proc stock, afin de ne pas faire systematiquement de IF NO EXISTS. En effet une fois que l'entrée existe dans la table, un update suffit.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE Procedure [dbo].[spr_getcounter] @countername AS VARCHAR(128), @countervalue AS BIGINT OUTPUT AS SET @countervalue = 0 UPDATE tbl_counters SET cnt_lastvalue = cnt_lastvalue + 1, @countervalue = cnt_lastvalue + 1 WHERE cnt_name = @countername IF @countervalue = 0 BEGIN INSERT INTO tbl_counters (cnt_name, cnt_lastvalue) VALUES (@countername, 0) UPDATE tbl_counters SET cnt_lastvalue = cnt_lastvalue + 1, @countervalue = cnt_lastvalue + 1 WHERE cnt_name = @countername END
@+