Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Windows Windows Serveur SQL Serveur process was deadlocked on lock resources

process was deadlocked on lock resources

3 réponses
Avatar
Richard Urrutia
Bonjour,
j'ai un message d'erreur qui revient de temps en temps sur une application
asp.net 1.1 / sql server 7 :
process was deadlocked on lock resources with another process and has been
chosen as the deadlock victim.

il s'agit en fait d'un compteur sur une page qui va insérer une ligne dans
une table à chaque fois qu'un client passe sur la page.
La procédure stockée appelée exécute le traitement suivant :


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION


DECLARE @ErrMsg as varchar(512)

INSERT INTO [CodeTHit] ([CodeTHitID], [DateHit], [Referer],
[UserID],[CodeTID]) VALUES (@CodeTHitID, @DateHit, @Referer, @UserID,
@CodeTID)
IF @@ERROR<>0
GOTO LBL_ERROR

IF @@ERROR<>0
BEGIN
Set @ErrMsg=' ERROR<>0 sur le select de la ps CodeTHit'
GOTO LBL_ERROR
END

COMMIT TRANSACTION

GOTO LBL_END

LBL_ERROR:
ROLLBACK TRANSACTION
RAISERROR (@ErrMsg,16,1)

LBL_END:


La table "CodeTHit" éxecute également un trigger d'insertion afin d'auto
générer sa primary key.

Je ne sais pas d'ou vient le problème...

Merci de votre aide.


Richard
Signaler un problème avec ce contenu

3 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
Fred BROUARD
Bonjour,

Richard Urrutia a écrit :
Bonjour,
j'ai un message d'erreur qui revient de temps en temps sur une application
asp.net 1.1 / sql server 7 :
process was deadlocked on lock resources with another process and has been
chosen as the deadlock victim.

il s'agit en fait d'un compteur sur une page qui va insérer une ligne dans
une table à chaque fois qu'un client passe sur la page.
La procédure stockée appelée exécute le traitement suivant :


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION


DECLARE @ErrMsg as varchar(512)

INSERT INTO [CodeTHit] ([CodeTHitID], [DateHit], [Referer],
[UserID],[CodeTID]) VALUES (@CodeTHitID, @DateHit, @Referer, @UserID,
@CodeTID)
IF @@ERROR<>0
GOTO LBL_ERROR

IF @@ERROR<>0
BEGIN
Set @ErrMsg=' ERROR<>0 sur le select de la ps CodeTHit'
GOTO LBL_ERROR
END

COMMIT TRANSACTION

GOTO LBL_END

LBL_ERROR:
ROLLBACK TRANSACTION
RAISERROR (@ErrMsg,16,1)

LBL_END:




Beaucoup d'horreur dans ce code !

1) vous faites une transaction en niveau d'isoaltion SERIALIZABLE et
vous ne revenez pas au niveau READ COMMITTED en fin de transaction...
Or ce paramètre est valable pendant toute la session.

2) vous utilisez un niveau SERIALIZABLE alors que la transaction ne fait
pas de lecture. Or le mode SERIALIZABLE permet d'éviter les tuples
fantômes qui peuvent apparaître dans les lectures. Ce mode est donc
inadéquat à votre code.

or le mode SERIALIZABLE est le mode qui pose les verrous les plus forts,
en l'occurrence un verrous exclusif sur la table empêchant toute lecture
et bien entendu mise à jour (INSERT, UPDATE, DELETE)

3) vous testez deux fois @@ERROR et reroutez deux fois après ce qui à
l'évidence ne sert à rien.

4) une transaction n'a d'intérêt que s'il y a plusieurs ordres de mise à
jour combinés et d'éventuelles lecture. Or dans ce code il n'y a qu'une
seule requête...

pour ma part je résumerais votre proc à :

INSERT INTO [CodeTHit]
([CodeTHitID], [DateHit], [Referer], [UserID],[CodeTID])
VALUES (@CodeTHitID, @DateHit, @Referer, @UserID, @CodeTID)
IF @@ERROR <> 0
GOTO LBL_ERROR

IF @@ERROR <> 0
RAISERROR ('mon message à mois',16,1)

Soit 3 lignes de code... !





La table "CodeTHit" éxecute également un trigger d'insertion afin d'auto
générer sa primary key.



Déportez alors ce code dans la procédure !



Je ne sais pas d'ou vient le problème...

Merci de votre aide.


Richard





A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Avatar
Richard Urrutia
Bonjour et merci de votre réponse.
A la lecture de vos remarques, j'ai 2 nouvelles questions :
1. qu'est ce que le READ COMMITTED ? Dois je écrire en fin de procédure SET
TRANSACTION ISOLATION LEVEL READCOMMITTED ?
2. Sur certaines tables nous avons mis en place un système de clé primaire.
lors de l'insertion, un trigger est activé et va récupérer dans une autre
table la clé primaire à insérer.
Faut-il également se passer du SERIALIZABLE sur les procédures d'insertion
qui utilisent ce trigger ? Car j'ai peur d'avoir des problèmes sur les accès
concurrents (mélange des ids, erreur de type violation primary key.
Encore merci pour votre aide


voici un exemple du trigger en question :


CREATE TRIGGER TRIGGER_INSERT_PK_CodeMarket ON dbo.CodeMarket
INSTEAD OF INSERT
NOT FOR REPLICATION
AS


BEGIN
--Build an INSERT statement ignoring inserted.PrimaryKey and
--inserted.ComputedCol.
DECLARE @RC bigint
DECLARE @NOM_TABLE varchar(128)
SET @NOM_TABLE='CodeMarket'

DECLARE @CodeTID bigint,
@CodeT varchar(32) ,
@LinkID bigint,
@StartDate datetime,
@EndDate datetime,
@Description varchar(256),
@LinkURL varchar(256)

IF (SELECT COUNT(*) FROM Inserted) = 1
BEGIN
-- génération de l'iD
EXEC [dbo].[PS_SYS_DB_CALC_NEW_KEY] @NOM_TABLE,@RC OUTPUT

-- insertion de la ligne
INSERT INTO [CodeMarket]([CodeTID], [CodeT], [LinkID], [StartDate],
[EndDate], [Description],[LinkURL])
SELECT @RC, [CodeT], [LinkID], [StartDate], [EndDate],
[Description],[LinkURL] FROM inserted

END

ELSE

BEGIN
-- On cree un curseur sinon
-- qui va inserer ligne après ligne
DECLARE IDCursor CURSOR LOCAL
READ_ONLY FOR
SELECT [CodeT], [LinkID], [StartDate], [EndDate], [Description] ,[LinkURL]
FROM inserted



OPEN IDcursor

FETCH NEXT FROM IDCursor INTO @CodeT,
@LinkID,@StartDate,@EndDate,@Description,@LinkURL


WHILE (@@fetch_status <> -1)
BEGIN

IF (@@fetch_status <> -2)
BEGIN

-- Si la valeur de clé existe deja alors on en génére une nouvelle
EXEC [dbo].[PS_SYS_DB_CALC_NEW_KEY] @NOM_TABLE,@RC OUTPUT

-- et puis on insere
INSERT INTO CodeMarket
SELECT @RC, @CodeT, @LinkID,@StartDate,@EndDate,@Description,@LinkURL

END

-- next row
FETCH NEXT FROM IDCursor INTO @CodeT,
@LinkID,@StartDate,@EndDate,@Description,@LinkURL

END


END



END






"Fred BROUARD" wrote in message
news:uDwC$
Bonjour,

Richard Urrutia a écrit :
Bonjour,
j'ai un message d'erreur qui revient de temps en temps sur une
application asp.net 1.1 / sql server 7 :
process was deadlocked on lock resources with another process and has
been chosen as the deadlock victim.

il s'agit en fait d'un compteur sur une page qui va insérer une ligne
dans une table à chaque fois qu'un client passe sur la page.
La procédure stockée appelée exécute le traitement suivant :


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION


DECLARE @ErrMsg as varchar(512)

INSERT INTO [CodeTHit] ([CodeTHitID], [DateHit], [Referer],
[UserID],[CodeTID]) VALUES (@CodeTHitID, @DateHit, @Referer, @UserID,
@CodeTID)
IF @@ERROR<>0
GOTO LBL_ERROR

IF @@ERROR<>0
BEGIN
Set @ErrMsg=' ERROR<>0 sur le select de la ps CodeTHit'
GOTO LBL_ERROR
END

COMMIT TRANSACTION

GOTO LBL_END

LBL_ERROR:
ROLLBACK TRANSACTION
RAISERROR (@ErrMsg,16,1)

LBL_END:




Beaucoup d'horreur dans ce code !

1) vous faites une transaction en niveau d'isoaltion SERIALIZABLE et vous
ne revenez pas au niveau READ COMMITTED en fin de transaction...
Or ce paramètre est valable pendant toute la session.

2) vous utilisez un niveau SERIALIZABLE alors que la transaction ne fait
pas de lecture. Or le mode SERIALIZABLE permet d'éviter les tuples
fantômes qui peuvent apparaître dans les lectures. Ce mode est donc
inadéquat à votre code.

or le mode SERIALIZABLE est le mode qui pose les verrous les plus forts,
en l'occurrence un verrous exclusif sur la table empêchant toute lecture
et bien entendu mise à jour (INSERT, UPDATE, DELETE)

3) vous testez deux fois @@ERROR et reroutez deux fois après ce qui à
l'évidence ne sert à rien.

4) une transaction n'a d'intérêt que s'il y a plusieurs ordres de mise à
jour combinés et d'éventuelles lecture. Or dans ce code il n'y a qu'une
seule requête...

pour ma part je résumerais votre proc à :

INSERT INTO [CodeTHit]
([CodeTHitID], [DateHit], [Referer], [UserID],[CodeTID])
VALUES (@CodeTHitID, @DateHit, @Referer, @UserID, @CodeTID)
IF @@ERROR <> 0
GOTO LBL_ERROR

IF @@ERROR <> 0
RAISERROR ('mon message à mois',16,1)

Soit 3 lignes de code... !





La table "CodeTHit" éxecute également un trigger d'insertion afin d'auto
générer sa primary key.



Déportez alors ce code dans la procédure !



Je ne sais pas d'ou vient le problème...

Merci de votre aide.


Richard



A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************


Avatar
Fred BROUARD
Richard Urrutia a écrit :
Bonjour et merci de votre réponse.
A la lecture de vos remarques, j'ai 2 nouvelles questions :
1. qu'est ce que le READ COMMITTED ? Dois je écrire en fin de procédure SET
TRANSACTION ISOLATION LEVEL READCOMMITTED ?



oui, lisez moi ici pour comprendre :*
http://sqlpro.developpez.com/cours/sqlaz/techniques/#L1.4

2. Sur certaines tables nous avons mis en place un système de clé primaire.
lors de l'insertion, un trigger est activé et va récupérer dans une autre
table la clé primaire à insérer.



Dans SQL Server on peut faire une lecture/écriture combinée dans
l'UPDATE. point n'est alors besoin de transaction ni de piloter le
niveau d'isolation pour faire ce calcul d'auto incrément.

Exemple :

UPDATE MaTableDesClefs
SET MaClef = MaClef + 1,
@MaNouvelleClef = MaClef
WHERE MaTable = @MaTable



Faut-il également se passer du SERIALIZABLE sur les procédures d'insertion
qui utilisent ce trigger ? Car j'ai peur d'avoir des problèmes sur les accès
concurrents (mélange des ids, erreur de type violation primary key.



oh que oui il faut s'en passer !


Encore merci pour votre aide


voici un exemple du trigger en question :



éliminez les curseurs dans les triggers, c'est le pire du pire !!!

Lisez l'article que j'ai écrit à ce sujet :

http://sqlpro.developpez.com/cours/sqlserver/MSSQLServer_avoidCursor/

A +



CREATE TRIGGER TRIGGER_INSERT_PK_CodeMarket ON dbo.CodeMarket
INSTEAD OF INSERT
NOT FOR REPLICATION
AS


BEGIN
--Build an INSERT statement ignoring inserted.PrimaryKey and
--inserted.ComputedCol.
DECLARE @RC bigint
DECLARE @NOM_TABLE varchar(128)
SET @NOM_TABLE='CodeMarket'

DECLARE @CodeTID bigint,
@CodeT varchar(32) ,
@LinkID bigint,
@StartDate datetime,
@EndDate datetime,
@Description varchar(256),
@LinkURL varchar(256)

IF (SELECT COUNT(*) FROM Inserted) = 1
BEGIN
-- génération de l'iD
EXEC [dbo].[PS_SYS_DB_CALC_NEW_KEY] @NOM_TABLE,@RC OUTPUT

-- insertion de la ligne
INSERT INTO [CodeMarket]([CodeTID], [CodeT], [LinkID], [StartDate],
[EndDate], [Description],[LinkURL])
SELECT @RC, [CodeT], [LinkID], [StartDate], [EndDate],
[Description],[LinkURL] FROM inserted

END

ELSE

BEGIN
-- On cree un curseur sinon
-- qui va inserer ligne après ligne
DECLARE IDCursor CURSOR LOCAL
READ_ONLY FOR
SELECT [CodeT], [LinkID], [StartDate], [EndDate], [Description] ,[LinkURL]
FROM inserted



OPEN IDcursor

FETCH NEXT FROM IDCursor INTO @CodeT,
@LinkID,@StartDate,@EndDate,@Description,@LinkURL


WHILE (@@fetch_status <> -1)
BEGIN

IF (@@fetch_status <> -2)
BEGIN

-- Si la valeur de clé existe deja alors on en génére une nouvelle
EXEC [dbo].[PS_SYS_DB_CALC_NEW_KEY] @NOM_TABLE,@RC OUTPUT

-- et puis on insere
INSERT INTO CodeMarket
SELECT @RC, @CodeT, @LinkID,@StartDate,@EndDate,@Description,@LinkURL

END

-- next row
FETCH NEXT FROM IDCursor INTO @CodeT,
@LinkID,@StartDate,@EndDate,@Description,@LinkURL

END


END



END






"Fred BROUARD" wrote in message
news:uDwC$
Bonjour,

Richard Urrutia a écrit :
Bonjour,
j'ai un message d'erreur qui revient de temps en temps sur une
application asp.net 1.1 / sql server 7 :
process was deadlocked on lock resources with another process and has
been chosen as the deadlock victim.

il s'agit en fait d'un compteur sur une page qui va insérer une ligne
dans une table à chaque fois qu'un client passe sur la page.
La procédure stockée appelée exécute le traitement suivant :


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION


DECLARE @ErrMsg as varchar(512)

INSERT INTO [CodeTHit] ([CodeTHitID], [DateHit], [Referer],
[UserID],[CodeTID]) VALUES (@CodeTHitID, @DateHit, @Referer, @UserID,
@CodeTID)
IF @@ERROR<>0
GOTO LBL_ERROR

IF @@ERROR<>0
BEGIN
Set @ErrMsg=' ERROR<>0 sur le select de la ps CodeTHit'
GOTO LBL_ERROR
END

COMMIT TRANSACTION

GOTO LBL_END

LBL_ERROR:
ROLLBACK TRANSACTION
RAISERROR (@ErrMsg,16,1)

LBL_END:



Beaucoup d'horreur dans ce code !

1) vous faites une transaction en niveau d'isoaltion SERIALIZABLE et vous
ne revenez pas au niveau READ COMMITTED en fin de transaction...
Or ce paramètre est valable pendant toute la session.

2) vous utilisez un niveau SERIALIZABLE alors que la transaction ne fait
pas de lecture. Or le mode SERIALIZABLE permet d'éviter les tuples
fantômes qui peuvent apparaître dans les lectures. Ce mode est donc
inadéquat à votre code.

or le mode SERIALIZABLE est le mode qui pose les verrous les plus forts,
en l'occurrence un verrous exclusif sur la table empêchant toute lecture
et bien entendu mise à jour (INSERT, UPDATE, DELETE)

3) vous testez deux fois @@ERROR et reroutez deux fois après ce qui à
l'évidence ne sert à rien.

4) une transaction n'a d'intérêt que s'il y a plusieurs ordres de mise à
jour combinés et d'éventuelles lecture. Or dans ce code il n'y a qu'une
seule requête...

pour ma part je résumerais votre proc à :

INSERT INTO [CodeTHit]
([CodeTHitID], [DateHit], [Referer], [UserID],[CodeTID])
VALUES (@CodeTHitID, @DateHit, @Referer, @UserID, @CodeTID)
IF @@ERROR <> 0
GOTO LBL_ERROR

IF @@ERROR <> 0
RAISERROR ('mon message à mois',16,1)

Soit 3 lignes de code... !




La table "CodeTHit" éxecute également un trigger d'insertion afin d'auto
générer sa primary key.


Déportez alors ce code dans la procédure !


Je ne sais pas d'ou vient le problème...

Merci de votre aide.


Richard


A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************








--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************