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

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 ?

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é ?

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:

---

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:

---

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 !

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 ^^

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

https://blog.developpez.com/elsuket/...process-report

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 !

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 +