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

MS SQL Server Discussion :

transactions et verrous : qqchose d'étonnant


Sujet :

MS SQL Server

  1. #1
    Membre actif
    Inscrit en
    Janvier 2008
    Messages
    70
    Détails du profil
    Informations forums :
    Inscription : Janvier 2008
    Messages : 70
    Par défaut transactions et verrous : qqchose d'étonnant
    Bonjour,


    Avec les collègues nous avons constaté quelque chose d'étonnant.

    Dans une transaction en mode Read Commited (par défaut sous SQL Server), nous avons fait ce test :

    Dans un processus (une "nouvelle requête") sous Sql Server Management, nous avons mis un verrou sur une ligne comme ceci :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SET TRANSACTION ISOLATION LEVEL READ COMMITED
    BEGIN TRANSACTION
    update t_exemple where id=1234;
    WAITFOR DELAY '00:00:20'
    COMMIT TRANSACTION
    La ligne de t_exemple dont l'id est 1234 est verrouillé pendant 20 secondes.

    Dans ce laps de temps, nous avons verifié que la ligne était bien verrouillé, avec une nouvelle requête dans Sql Server Management (2eme processus sur Sql Server) avec cette procédure :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    select * from t_exemple where id=1234;
    Effectivement, la ligne était bien verrouillé car elle ne nous a été rendu qu'après la fin de la première requête.

    Ensuite on a fait la même chose mais nous avons remplacé la deuxième requête par :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    select * from t_exemple
    Sans préciser que nous voulions l'id 1234.

    Eh bien, le résultat comprenant l'id 1234 nous a été retourné avant la fin de la première requête.. Il semble donc qu'il n'y avait pas de verrou sur la ligne dont l'id est 1234.

    Auriez vous une explication à cela ?


    D'avance merci beaucoup

  2. #2
    Membre habitué
    Profil pro
    Inscrit en
    Juin 2008
    Messages
    16
    Détails du profil
    Informations personnelles :
    Âge : 41
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : Juin 2008
    Messages : 16
    Par défaut
    Est que vous avez repeté plusieurs fois l'expérience ?

  3. #3
    Membre actif
    Inscrit en
    Janvier 2008
    Messages
    70
    Détails du profil
    Informations forums :
    Inscription : Janvier 2008
    Messages : 70
    Par défaut
    bonjour,

    oui bien sur. Je vous invite à faire la même expérience vous même pour le vérifier (ou non) ..

  4. #4
    Membre actif
    Inscrit en
    Janvier 2008
    Messages
    70
    Détails du profil
    Informations forums :
    Inscription : Janvier 2008
    Messages : 70
    Par défaut
    rebonjour,

    Je n'ai toujours pas résolu ce problème..

    Je suis à l'affut de toutes explications..

  5. #5
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    22 010
    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 : 22 010
    Billets dans le blog
    6
    Par défaut
    Vous êtes totalement à côté de la plaque... en effet :
    La ligne de t_exemple dont l'id est 1234 est verrouillé pendant 20 secondes.
    Non ! En mode READ COMMITTED vous avez l'assurance que le SELECT lira des données validées. Une fois le SELECT effectué il peut se passer tout ce que l'on veut. Autrement dit une fois les données sont lues les verrous sont immédiatement libérés.
    Ce comportement est donc parfaitement logique et attendu...

    Si vous aviez lu mon livre sur SQL vous auriez vu quels sont les différents niveaux d'isolation de la norme SQL et ce que cela implique.
    Néanmoins, rien ne vous empêche de lire l'article que j'ai écrit au sujet des différents niveaux d'isolation normatifs et SQL Server, avec ses différentes démonstrations : http://www.sqlspot.com/Gerer-l-isola...nsactions.html

    Si vous utilisez le niveau REPEATABLE READ alors les verrous sont maintenus, mais cela n'empêche pas les anomalies transactionnelles venant de tuples fantômes...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  6. #6
    Membre actif
    Inscrit en
    Janvier 2008
    Messages
    70
    Détails du profil
    Informations forums :
    Inscription : Janvier 2008
    Messages : 70
    Par défaut
    Bonjour SqlPro,


    Si vous saviez le nombre de fois ou vous m'avez aidé (sous différents pseudo)..

    Je vais commander votre livre dans la semaine..

    Merci

  7. #7
    Membre actif
    Inscrit en
    Janvier 2008
    Messages
    70
    Détails du profil
    Informations forums :
    Inscription : Janvier 2008
    Messages : 70
    Par défaut
    Par contre je suis pas sur que vous ayiez compris mon problème (ou c'est moi qui arrive pas à vous suivre)..

    En fait la premiere transaction n'est pas un SELECT mais un UPDATE.

    un UPDATE fait bien un verrouillage en mode Read commited ?
    Donc ma ligne est verouillé 20 secondes non ?

    Par contre, c'est après, avec mes 2 SELECT (durant les 20 secondes) que je n'obtiens pas les mêmes résultats.

    non ?

  8. #8
    Membre Expert
    Avatar de rudib
    Homme Profil pro
    Fakir SQL Server & NoSQL
    Inscrit en
    Mai 2006
    Messages
    2 573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Fakir SQL Server & NoSQL

    Informations forums :
    Inscription : Mai 2006
    Messages : 2 573
    Par défaut
    Bonjour,

    C'est dû à la granularité de verrouillage ou à la ressource. Ton UPDATE verrouille une ligne, ton SELECT escalade sur la table, ou c'est un index qui est verrouillé, et une autres index pour le SELECT.

    Pour en avoir le coeur net : exécute le SELECT dans une transaction en isolation REPEATABLE READ sans la refermer, et envoie-nous le résultat ensuite de ceci :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    SELECT resource_type, request_mode, request_status, FROM sys.dm_tran_locks
    WHERE request_session_id IN (spid1, spid2)
    où tu remplaces spid1 et spid2 par les numéros de @@SPID de tes deux sessions. Ensuite, on détaillera

    (ou la requête listée dans ce blog : http://decipherinfosys.wordpress.com...l-server-2005/)

  9. #9
    Membre actif
    Inscrit en
    Janvier 2008
    Messages
    70
    Détails du profil
    Informations forums :
    Inscription : Janvier 2008
    Messages : 70
    Par défaut
    Bonjour rudib,

    Merci.

    Voila j'ai fait le test.

    SPID=54
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    BEGIN TRANSACTION
    UPDATE t_exemple SET libelle='tata' WHERE id=2;
    WAITFOR DELAY '00:00:20'
    COMMIT TRANSACTION
    SPID=52
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT * FROM t_exemple;
    J'ai exécuté la première, puis la deuxième puis :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    SELECT resource_type, request_mode, request_status, request_session_id FROM sys.dm_tran_locks
    WHERE request_session_id IN (52, 54)
    Et j'ai obtenu le résultat :
    DATABASE S GRANT 54
    DATABASE S GRANT 52
    RID X GRANT 54
    PAGE IX GRANT 54
    OBJECT IX GRANT 54


    Par contre j'ai remarqué un autre truc :

    Si la première requête (UPDATE) modifie vraiment le libellé, par exemple si j'update libelle="toto" alors que le libellé était à "tata", alors la deuxième requête (SELECT) attend que la première (transaction) ait terminée pour s'exécuter.

    Mais si j'update le libelle="toto" alors qu'il était déjà à "toto", alors la requête SELECT n'attend pas la fin de la première. (c'est ça que j'avais remarqué et qui était l'objet de mon post)

    C'est comme s'il y avait un mécanisme dans SQL Server qui ne verouille pas une ligne si l'UPDATE ne modifie rien "dans les faits". Je sais pas si je m'exprime bien. Et dans ce cas, c'est rassurant, et le problème ne se pose plus pour moi, car c'est ça que je craignais (qu'eune donnée soit pas vérouillée).

    Merci

  10. #10
    Membre Expert
    Avatar de rudib
    Homme Profil pro
    Fakir SQL Server & NoSQL
    Inscrit en
    Mai 2006
    Messages
    2 573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Fakir SQL Server & NoSQL

    Informations forums :
    Inscription : Mai 2006
    Messages : 2 573
    Par défaut
    Tu n'as pas le verrou du SELECT, car tu n'étais pas en REAPEATABLE READ. Mais en effet, tu az raison : s'il n'y a pas de modification effective de la ligne, il n'y a pas de verrou posé sur la ligne. Tu peux voir la différence dans le journal de transaction entre les deux UPDATE à l'aide de la fonction non documentée fn_dblog(), qui montre ce que SQL server écrit dans le log de transactions. En cas de non-modification, il n'y a pas de réelle modification de la ligne.

  11. #11
    Membre actif
    Inscrit en
    Janvier 2008
    Messages
    70
    Détails du profil
    Informations forums :
    Inscription : Janvier 2008
    Messages : 70
    Par défaut
    rebonjour rudib,

    Merci !

    Ok tout s'explique. En fait mon étonnement lors de ce test était du à ce phénomène. Le verrou n'était pas posé car la ligne n'était pas modifiée "en vrai".. Tout est clair maintenant merci !

    J'ai appris quelque chose aujourd'hui

    Je mets en résolu

    encore merci
    bon après midi

  12. #12
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    22 010
    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 : 22 010
    Billets dans le blog
    6
    Par défaut
    En fait il faut comprnedre que :
    1) READ UNCOMMITTED : pas de verrous si lecture, verrou exclusif si mise à jour, libéré en fin d'exécution de l'ordre SQL
    2) READ COMMITTED : verrous en lecture comme en écriture mais exclusif dans ce dernier cas, libéré en fin d'exécution de l'ordre SQL
    3) REPEATABLE READ : verrous en lecture comme en écriture mais exclusif dans ce dernier cas, libéré en fin de transaction.
    4) SERIALIZABLE : verrous de table en lecture comme en écriture mais exclusif dans ce dernier cas, libéré en fin de transaction.

    Pour la granularité du verrou, c'est SQL Server qui décide à l'aide de son gestionnaire de verrous.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [SQL SERVER 2000] Transaction, verrous et utilisation de NOLOCK
    Par luimême dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 18/02/2009, 17h21
  2. Réponses: 2
    Dernier message: 20/09/2007, 11h28
  3. Réponses: 5
    Dernier message: 19/09/2007, 14h23
  4. gestion d'erreur et de transactions....
    Par Dge dans le forum MS SQL Server
    Réponses: 9
    Dernier message: 08/02/2006, 22h20
  5. 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, 08h21

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