[SQL2K] Deadlock sur transaction

**abelman** · 03/04/2007, 13h41

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 :

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 :

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
 
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

**SQLpro** · 03/04/2007, 15h38

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 :

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

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 +

**abelman** · 03/04/2007, 19h51

Envoyé par SQLpro

De nombreuses horreurs dans ce code :

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

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 :

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

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

en fin de procedure cela corrige t il ce point?

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

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.

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)

. 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)

Mais en voici une encore plus "juste"

Code :

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

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 :

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
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.
@++

**SQLpro** · 04/04/2007, 08h59

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 :

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
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 +

**abelman** · 04/04/2007, 09h37

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

**SQLpro** · 04/04/2007, 10h50

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 +

**abelman** · 04/04/2007, 12h55

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 +

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 :

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

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

@+

[SQL2K] Deadlock sur transaction

MS SQL Server

Discussions similaires

Partager

Partager