IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Problème de transaction


Sujet :

Développement SQL Server

  1. #1
    Membre à l'essai
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Novembre 2009
    Messages
    22
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Alimentation

    Informations forums :
    Inscription : Novembre 2009
    Messages : 22
    Points : 12
    Points
    12
    Par défaut Problème de transaction
    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 :

    Nom : Capture d'écran 2024-03-15 104125.png
Affichages : 112
Taille : 26,4 Ko

    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.

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 920
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 920
    Points : 51 712
    Points
    51 712
    Billets dans le blog
    6
    Par défaut
    Postez la requête qui vous donne ce resultat... et l'intégralité de ce qui figure dans le résultat

    A +

  3. #3
    Membre à l'essai
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Novembre 2009
    Messages
    22
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Alimentation

    Informations forums :
    Inscription : Novembre 2009
    Messages : 22
    Points : 12
    Points
    12
    Par défaut
    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 :

    Nom : Capture d'écran 2024-03-15 140106.png
Affichages : 99
Taille : 31,1 Ko

    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

  4. #4
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 920
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 920
    Points : 51 712
    Points
    51 712
    Billets dans le blog
    6
    Par défaut
    À 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 +

  5. #5
    Membre à l'essai
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Novembre 2009
    Messages
    22
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Alimentation

    Informations forums :
    Inscription : Novembre 2009
    Messages : 22
    Points : 12
    Points
    12
    Par défaut
    Un grand merci pour votre réponse, je comprend mieux le problème. Je vais essayer tout ça.

Discussions similaires

  1. gestion d'erreur et de transactions....
    Par Dge dans le forum MS SQL Server
    Réponses: 9
    Dernier message: 08/02/2006, 23h20
  2. Apropos des Transactions au sein d'un Stored Procedure
    Par Sarbacane dans le forum Connexion aux bases de données
    Réponses: 6
    Dernier message: 16/11/2004, 09h21
  3. [Petite requête] Nombre de transactions par jour
    Par Braim dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 15/04/2003, 11h53
  4. [PostgreSQL]transactions
    Par adri1 dans le forum PostgreSQL
    Réponses: 3
    Dernier message: 10/04/2003, 15h37
  5. Transaction avec DoCmd.runsql ???
    Par Gandalf24 dans le forum VBA Access
    Réponses: 29
    Dernier message: 11/02/2003, 21h35

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo