1. #1
    Membre averti
    Homme Profil pro
    Freelance
    Inscrit en
    février 2008
    Messages
    312
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 29
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Freelance

    Informations forums :
    Inscription : février 2008
    Messages : 312
    Points : 397
    Points
    397

    Par défaut DeadLock SQL server avec TransactionScope ReadCommited

    Bonjour à tous,

    Je rencontre des problème de deadlock sur ma couche business lors de scénario d'achat (appel à la chaine de méthode venant lire, insérer et updater des données de réservation).

    Chaque appel de service vient partiellement remplir des données de réservations.
    Certaines méthodes sont englobés dans une TransactionsScope en mode ReadCommited.
    Les transactions scope de ne sont pas chainés.
    Les ordre SQL sont envoyés via ADO.NET sans utiliser de SqlTransaction, sur une base SQL server 2012
    Normalement, chaque ordre Insert/Update/Delete faite sur les tables dans ces services sont restreint par un identifiant de réservation (ce qui fait que deux parcours de réservation parallèle ne peuvent pas venir modifier les mêmes données).

    Dans les faits :
    Je lance un bout de code qui me lance 50 parcours de réservation complet en parallèle. A noter que dans un parcours de réservation tout est séquentielle, rien n'est lancé en parallèle. Au bout de peu de temps je recoit un deadlock.
    Selon les lancement de ce bout de code, le deadlock ne se produit pas toujours (voir rarement) au même endroit
    j'ai branché le sql server profiler qui m'indique bien des graphes d'erreurs mais c'est assez compliqué à comprendre
    Dans un exemple de deadlock j'ai par exemple :
    - une requete select participant au deadlock, un select qui renvoie les x dernières réservations sans filtres particulier (donc potentiellement "bloqué" si un ordre update non commité existe sur l'une de ces réservations)
    - une requete select participant au deadlock, un select qui renvoie les x dernières réservations faite par un customer (théoriquement unique par parcours de réservation, donc ne doit pas se croiser avec le reste)
    - une requete update "victime" du deadlock, qui mettait a jour un champ dans la table des réservations sur une réservation précise.
    j'ai aurai aimé recevoir les paramètres envoyés dans la requête pour bien m'assurer si les données recues ou updatés se croisent mais je n'arrivent pas à trouver ca dans le profiler (j'ai filtré par tout ce qui concerne les locks)

    J’aperçois aussi que si j'ai un ordre update sur un row dans une transaction non complete, un select sur la table qui ramènerait le row finit en timeout (si je laisse un point d’arrêt avant le transaction.complete bien sûr). Est-ce normal ? J'ai cru comprendre que oui mais c'était pas hyper clair. A noter que mes transactions ne sont pas hyper longues, de l'ordre de 2 3 secondes max. Car effectivement je peux avoir en parallèle un ordre update sur un row et un autre thread qui fait un select sur ce row. Mais je m'attend dans ce cas a ce que le select reste en wait et dès que la transaction du update est commité ca renvoie la résultat.

    Bref je n'ai plus trop d'idées, si vous avez des conseils je suis preneur,

    Merci d'avance

  2. #2
    Modérateur
    Avatar de DotNetMatt
    Homme Profil pro
    CTO
    Inscrit en
    février 2010
    Messages
    3 352
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 29
    Localisation : Etats-Unis

    Informations professionnelles :
    Activité : CTO
    Secteur : Finance

    Informations forums :
    Inscription : février 2010
    Messages : 3 352
    Points : 8 862
    Points
    8 862
    Billets dans le blog
    3

    Par défaut

    Pas simple.

    Le niveau d'isolation ReadCommitted n'affecte que les lectures. Les instructions UPDATE et DELETE poseront toujours un lock exclusif sur une ligne, voire sur plusieurs si la requete affecte un grand nombre de lignes, voire meme la table entiere si besoin (lock escalation).

    Quand tu dis que dans in parcours de reservation tout est sequentiel, est-ce que les tables utilisees le sont toujours dans le meme ordre ?
    Est-ce que tu peux poster le XML du graph du cas que tu mentionnes en exemple ?
    Less Is More
    Pensez à utiliser les boutons , et les balises code
    Desole pour l'absence d'accents, clavier US oblige

  3. #3
    Rédacteur/Modérateur

    Avatar de François DORIN
    Homme Profil pro
    Consultant informatique
    Inscrit en
    juillet 2016
    Messages
    1 416
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : juillet 2016
    Messages : 1 416
    Points : 4 772
    Points
    4 772
    Billets dans le blog
    5

    Par défaut

    Bonjour,

    Typiquement, il est très difficile de résoudre les problèmes d'interblocage sans avoir les requêtes en jeu, ni la structure des tables. Je t'invite donc à nous fournir tout cela.

    De plus, je pense que tu auras beaucoup plus de chance d'obtenir une réponse dans le forum dédié à SQL Server. Souhaites-tu que je déplace la discussion dans le forum approprié ?
    François DORIN
    Consultant informatique : conception, modélisation, développement (C#/.Net et SQL Server)
    Site internet | Profils Viadéo & LinkedIn
    ---------
    Page de cours : fdorin.developpez.com
    ---------
    N'oubliez pas de consulter la FAQ C# ainsi que les cours et tutoriels

  4. #4
    Membre averti
    Homme Profil pro
    Freelance
    Inscrit en
    février 2008
    Messages
    312
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 29
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Freelance

    Informations forums :
    Inscription : février 2008
    Messages : 312
    Points : 397
    Points
    397

    Par défaut

    Merci de vos réponses,

    alors j'ai avancé depuis hier, j'ai sorti tout ce que j'ai pu des transactions (notamment des requêtes select qui pouvait être fait en dehors), j'ai divisé certaines transactions en plusieurs lorsque c'était cohérent, j'en ai supprimé certaines.
    Résultat : plus de deadlock MAIS au bout d'un moment certaines requêtes finisse en timeout "le délai d'attente a expiré" (timeout du a un lock)
    Lorsque j'observe les lock et instructions en cours directement sur la base, j'ai du mal a comprendre ce qu'il se passe
    cas concret :
    J'insère un certain nombre de ligne mouvements dans la base, cela dans un transaction
    je rencontre le timeout (dans certains cas) sur l'un de ces insert, lorsque j'analyse ce qu'il se passe en base
    ordres sql en cours
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT * FROM sys.dm_exec_requests 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
    where blocking_session_id <> 0
    -> je ne retrouve pas la trace de la table mouvements dans les requêtes en cours. Globalement cette table n'est jamais updaté ni delete, il y a seulement des insert et des select
    lorsque je regarde les locks sur la table
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT * FROM sys.dm_tran_locks
      WHERE resource_database_id = DB_ID()
      AND resource_associated_entity_id = OBJECT_ID(N'dbo.tMovement');
    effectivement j'en trouve (3) de type IX (exclusion intentionnelle (IX)). Je ne sait pas si ce sont ces verrous qui bloque l'insert, toujours est-il que les select ne passent pas sur la table (le select d'un champ précis mais pas un select global)

    A noter que dans ce cas précis, il y a une transactions autour de tous les insert et un transaction sur l'insert en lui même (car l'insert est suivi d'un update, sur une autre table)

    J'ai du mal a comprendre d'ou proviennent ces locks (je suppose d'insert into dans la table de la même transaction ou potentiellement d'un transaction différente), si ils sont la cause de mon timeout, et si c'est avéré, comment faire des insert sans locké la table.
    Peut etre qu'en faisant une jointure de sys.dm_tran_locks avec d'autres tables systèmes je pourrai récupérer plus d'information tel que les requêtes ayant participé au lock ce genre de choses ?

    Autre cas intéressant : un timeout sur un select, alors que tous les ordres en attentes sur la base était des select ! après peut etre qure la liste qui m'est renvoyé n'est pas complète ou n'inclut pas les ordres inclus dans une transaction non commité, ce genre de choses)

    A noter : J'ai commencé à m'intéresser au ALLOW_ROW_LOCKS et ALLOW_PAGE_LOCKS sur mes index de table, ils sont mis a ON. Je suis tombé sur d'autre thread indiquant que les mettre a OFF avait résolu leur problème. Néanmoins d'autres source indiquent que mettre a OFF au niveau PAGE et ROW fait que la table va automatiquement se locker au niveau TABLE si besoin, ce qui rendrait le problème encore pire. Vous avez une idée la dessus ?

    Concernant la structure de base et les requêtes, mon cas concerne des parcours de réservation complet sur un site e-commerce (simulé par TU lancé parallèlement). Ca implique beaucoup trop de tables et de requêtes différentes pour que je puisse vous fournir ca de manière simple et synthétique. Peut être des bouts si réellement il y a besoin mais sinon le scénario est beaucoup trop complexe.

    @François : OK pour déplacer le thread c'est effectivement plus pertinent !

    Merci !

  5. #5
    Expert éminent sénior Avatar de Pol63
    Homme Profil pro
    .NET / SQL SERVER
    Inscrit en
    avril 2007
    Messages
    12 849
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 35
    Localisation : France, Puy de Dôme (Auvergne)

    Informations professionnelles :
    Activité : .NET / SQL SERVER

    Informations forums :
    Inscription : avril 2007
    Messages : 12 849
    Points : 23 147
    Points
    23 147

    Par défaut

    il faut aussi prendre en compte l'escalade des verrous
    si tu as une requête qui doit lire ou modifier plusieurs lignes, il arrive parfois qu'un verrou de page soit utilisé à la place de x verrous de lignes, ce qui gagne potentiellement du temps, mais qui peut bloquer des lignes qui ne sont pas utilisées par ta requête
    après plusieurs pages viennent ensuite les verrous de tables (plus rares je pense)
    il est possible de spécifier qu'on ne veut pas de verrous de page/table sur une table précise, à tester voire si ca éviter les deadlocks dans ton cas
    tu peux aussi faire une trace pour voir s'il y a des escalades de verrous qui arrivent

    sachant que pour ce genre de problématique, c'est à un DBA de trancher ce qu'il faut faire ou pas (le paramétrage et les requêtes peuvent influencer beaucoup de choses, et pas toujours dans le bon sens)
    il y a peu de développeurs formés à tout ca
    si vous n'avez pas de DBA, tu peux aussi en profiter pour te former un peu plus et être le DBA ^^
    Cours complets, tutos et autres FAQ ici : C# - VB.NET

  6. #6
    Expert confirmé
    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
    Points : 4 164
    Points
    4 164

    Par défaut

    Peux-tu utiliser le blocked process report et poster son résultat ?

    https://blog.developpez.com/elsuket/...process-report
    Rudi Bruchez
    Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
    LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
    LIVRES : Optimiser SQL Server -
    Microsoft SQL Server 2012 Security Cookbook
    - les bases de données NoSQL

    e-learning : LinkedIn Learning - Pluralsight

  7. #7
    Membre averti
    Homme Profil pro
    Freelance
    Inscrit en
    février 2008
    Messages
    312
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 29
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Freelance

    Informations forums :
    Inscription : février 2008
    Messages : 312
    Points : 397
    Points
    397

    Par défaut

    J'ai fini par trouvé, j'avais bien des threads qui s'interblocaient mais par deux processus de blocage différent sur une méthode : les locks posés par sql server et des locks "nommés" C# (lock selon une clé)
    Dans cette méthode, j'ai un cas particulier qui fait qu'un même row est updaté par l'ensemble des threads, opération d'update critique locké par une méthode c# sur la PK du row. Cette méthode critique est appelé plusieurs fois dans la méthode mère, le tout englobé dans une transaction scope.
    concrètement :
    - un thread T1 rentre dans la méthode, commence des ordres Update et insert dans une transaction. Il n'est pas forcément encore passé par la méthode critique qui lock sur la clé unique
    - un Thread T2 rentre dans la méthode, commence aussi des ordres et se retrouve dans l'opération critique locké, étant le premier il y rentre, a l'éxecution sql il se retouve bloqué par T1 qui étant donné le volume des ordres update/insert à posé des lock page/row
    - T1 continue son chemin, il appel la méthode critique sur la clé unique, mais se retrouve bloqué par le lock c# actuellement monopolisés par T2 (qui attend que T1 commit sa transaction)
    => les deux threads s'interbloquent, au bout de 30s, T2 en attente de retour de son ordre update tombe en timeout

    J'ai l'impression de simplifier le problème mais en gros j'en suis arrivé à cette conclusion

    J'ai résolu ce cas en m'assurant que la transactionScope dans cette méthode (qui génère les INSERT/Update potentiellement sur la même clé avec un verrou c# en plus) soit englobé dans un lock globale (lock(object)), en gros il n'y aura jamais d'ordre parralèles sur ce bouts de code . A priori ca passe même avec du 30 parcours simultanés.
    En revanche cette solution n'est pas optimale j'en suis conscient. Cette méthode fait partie du parcours de réservation et ne peut plus être parallélisé, J'ai du coup un potentiel point d'engorgement en cas de grosses activités sur le site.
    On fait pas 1000 ventes à la minutes donc ca devrait aller, mais j'ai quand même loggué les temps d'éxecution du lock globale que j'ai ajouté pour surveiller.

    @Pol63 : effectivement pas de DBA je suis freelance et on bosse à deux sur le site. J'en ai bouffé de la doc sur ce sujet ces derniers jours en tout cas c'est pas plus mal j'ai compris plein de truc :-)

    merci pour votre aide !

  8. #8
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 334
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 334
    Points : 40 140
    Points
    40 140
    Billets dans le blog
    1

    Par défaut

    Une des solutions "quick win" pour minimiser les verrous mortels consiste à activer le niveau d'isolation SAPSHOT pour toutes les lectures. Ainsi les cas que vous nous avez présentés n'auraient pas eut lieu.
    Bien sûr il faut vérifier que votre logique de code soit compatible.

    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...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

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

Discussions similaires

  1. Changer le nom d'une table sur SQL server avec une requete
    Par Oluha dans le forum MS SQL-Server
    Réponses: 6
    Dernier message: 02/02/2014, 00h35
  2. Importer des données dans sql server avec DELPHI ???
    Par moutanakid dans le forum MS SQL-Server
    Réponses: 2
    Dernier message: 11/08/2004, 18h22
  3. Connexion à SQL Server avec ASP
    Par ayobo dans le forum ASP
    Réponses: 3
    Dernier message: 25/05/2004, 18h06
  4. attaquer base sql server avec easyphp sous windows
    Par jarod71 dans le forum MS SQL-Server
    Réponses: 7
    Dernier message: 11/12/2003, 15h17
  5. Réponses: 3
    Dernier message: 18/11/2002, 17h36

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