Discussion :

[lebracfr]

Bonjour,

Je viens de changer de serveur de BDD et je rencontre un problème de blocage que je n'avais pas avant.
D'une manière qui semble aléatoire, des utilisateurs provoque des blocages de table et ces blocages ne sont pas levés même après exécution des COMMIT, par exemple :

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
SET NOCOUNT ON
BEGIN TRANsaction TRANS
DECLARE @Errors int
set @Errors = 0
DECLARE @VN int
 
SET @VN = (SELECT VN FROM GALAXY_C_TEST.dbo.SQL_NUMERO WHERE CODE = 'CPT_OXY_GCPREGLN' )
SET @errors = @errors + @@ERROR
 
UPDATE GALAXY_C_TEST.dbo.SQL_NUMERO
SET VN = @VN+1
WHERE CODE = 'CPT_OXY_GCPREGLN' 
SET @errors = @errors + @@ERROR
 
SELECT @VN AS Valeur
SET @errors = @errors + @@ERROR
IF @errors = 0
COMMIT TRANSACTION  TRANS
ELSE ROLLBACK TRANSACTION  TRANS
SET NOCOUNT OFF

Cette requête est sensé lever les verrous à l'issue de son exécution pourtant il arrive que la table SQL_numero reste verrouillée.
J'ai alors ce type de situation :



J'ai alors exécuté cette requête :

Code :

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

select * from sys.dm_exec_requests where session_id = 73

qui ne me retourne aucune requête et je ne comprend pas qu'une session qui n'exécute plus de requête puisse bloquer un autre utilisateur.
Pour autant la vue "sys.dm_exec_sessions" indique bien 1 dans la colonne "open_transaction_count" pour la session 73

Je suis sous Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2022 Datacenter 10.0 <X64> (Build 20348: ) (Hypervisor)

Merci pour votre aide.

[SQLpro]

Postez la requête qui vous donne ce resultat... et l'intégralité de ce qui figure dans le résultat

A +

[lebracfr]

Bonjour Frédérique,

J'espérait bien tomber sur vous ;-)

Mon applicatif lance cette requête

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
SET NOCOUNT ON
BEGIN TRANsaction TRANS
DECLARE @Errors int
set @Errors = 0
DECLARE @VN int
 
SET @VN = (SELECT VN FROM GALAXY_C_TEST.dbo.SQL_NUMERO WHERE CODE = 'CPT_OXY_GCPREGLN' )
SET @errors = @errors + @@ERROR
 
UPDATE GALAXY_C_TEST.dbo.SQL_NUMERO
SET VN = @VN+1
WHERE CODE = 'CPT_OXY_GCPREGLN' 
SET @errors = @errors + @@ERROR
 
SELECT @VN AS Valeur
SET @errors = @errors + @@ERROR
IF @errors = 0
COMMIT TRANSACTION  TRANS
ELSE ROLLBACK TRANSACTION  TRANS
SET NOCOUNT OFF

qui renvois un compteur.

Lorsque je met un point d'arrêt dans mon applicatif et que j'ai bien récupéré la valeur du compteur SQL serveur est dans cette situation

Table SQL_NUMERO verrouillée
et SSMS m'indique ça :



La session n'execute aucune requête mais une transaction est toujours ouverte.

Il n'y a que si je ferme l'applicatif que la transaction est levée pourtant ma requête comporte bien le COMMIT qui va bien.

Alexis

[SQLpro]

À priori c'est normal.... Vous terminez votre code par un SELECT qui renvoie une requête à l'application cliente. Ce SELECT est situé dans les frontières de la transaction... Tant que ce SELECT affiche la valeur vous êtes dans la transaction....

Soit vous externalisez ce SELECT comme suit :

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
SET NOCOUNT ON
BEGIN TRANsaction TRANS
DECLARE @Errors int
set @Errors = 0
DECLARE @VN int
 
SET @VN = (SELECT VN FROM GALAXY_C_TEST.dbo.SQL_NUMERO WHERE CODE = 'CPT_OXY_GCPREGLN' )
SET @errors = @errors + @@ERROR
 
UPDATE GALAXY_C_TEST.dbo.SQL_NUMERO
SET VN = @VN+1
WHERE CODE = 'CPT_OXY_GCPREGLN' 
SET @errors = @errors + @@ERROR
 
SET @errors = @errors + @@ERROR
IF @errors = 0
COMMIT TRANSACTION  TRANS
ELSE ROLLBACK TRANSACTION  TRANS
SET NOCOUNT OFF
 
SELECT @VN AS Valeur

Soit vous passez cette valeur dans une procédure en paramètre "OUT". Au passage l'utilisation de @@ERRROR est très vieillotte. Il vaudrait mieux utiliser le TRY / CATCH.

Exemple :

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
CREATE PROCEDURE P_MAPROC @VN int OUTPUT
AS
 
SET NOCOUNT ON;
 
BEGIN TRANsaction; 
 
BEGIN TRY
 
   SELECT @VN= VN 
   FROM GALAXY_C_TEST.dbo.SQL_NUMERO 
   WHERE CODE = 'CPT_OXY_GCPREGLN';
 
   UPDATE GALAXY_C_TEST.dbo.SQL_NUMERO
   SET VN = @VN+1
   WHERE CODE = 'CPT_OXY_GCPREGLN' 
 
   COMMIT;
 
END TRY
BEGIN CATCH
 
   IF XACT_STATE() <> 0
      ROLLBACK;
 
   THROW;
 
END CATCH
GO

Au passage si j'ai bien compris, vous voulez faire une auto incrémentation fonctionnelle manuelle ? Si c'est cela votre code aboutira à des doublons et des verrous mortels... En effet :
1) en commençant par la lecture de la valeur, vous n'empêchez pas que d'autres utilisateurs concurrents fassent de même. Les lectures posent des verrous partagés, donc la valeur récupérée sera la même pour tous les utilisateurs en parallèle.
2) en commençant par la lecture vous induisez de multiples verrous partagés au même moment, mais tous attendrons ensuite séquentiellement l'écriture (UPDATE) qui impose un verrou exclusif. S'il existe ensuite dans le code client une autre commande SQL vous risquez alors le verrou mortel...

Pour contourner ce problème, deux solutions :
1) utiliser un objet SEQUENCE au lieu d'une table. Vous pouvez en créer autant que vous voulez dans la limite de 4 milliards par base...
2) utiliser uniquement la commende UPDATE pour mettre à jour et lire la valeur unique de ligne, comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE PROCEDURE P_MAPROC @VN int OUTPUT
AS
 
SET NOCOUNT ON;
 
UPDATE GALAXY_C_TEST.dbo.SQL_NUMERO
SET    VN = @VN+1,
       @VN= VN
WHERE  CODE = 'CPT_OXY_GCPREGLN'; 
 
GO

Vous constatez qu'il n'y a aucune transaction explicite puisque toute commande SQL est une transaction implicite...

A +

[lebracfr]

Un grand merci pour votre réponse, je comprend mieux le problème. Je vais essayer tout ça.