Zéro blocage SQL à la conception - des modèles de codage ?

Question

Je rencontre des blocages SQL très peu fréquents mais ennuyeux sur une application Web .NET 2.0 fonctionnant au-dessus de MS SQL Server 2005. Dans le passé, nous avons traité les blocages SQL de manière très empirique - en modifiant les requêtes jusqu'à ce qu'elles fonctionnent.

Pourtant, j'ai trouvé cette approche très insatisfaisante : elle prenait du temps et n'était pas fiable. Je préférerais nettement suivre des modèles de requête déterministes qui garantiraient par le design qu'aucun blocage SQL ne sera rencontré - jamais.

Par exemple, dans la programmation multithread de C#, une règle de conception simple telle que les serrures doivent être prises en suivant leur ordre lexicographique garantit qu'aucune impasse ne sera jamais se produire.

Existe-t-il des modèles de codage SQL garantis contre les blocages ?

Answer 1

Il est très difficile d'écrire du code résistant aux blocages. Même si vous accédez aux tables dans le même ordre, vous pouvez toujours obtenir des blocages [1]. J'ai écrit un article sur mon blog qui développe certaines approches qui vous aideront à éviter et à résoudre les situations de blocage.

Si vous voulez vous assurer que deux instructions/transactions ne se bloqueront jamais, vous pouvez y parvenir en observant les verrous consommés par chaque instruction à l'aide de l'attribut sp_lock procédure stockée du système. Pour ce faire, vous devez soit être très rapide, soit utiliser une transaction ouverte avec un indice de verrouillage.

---

Notes :

1. Toute instruction SELECT qui nécessite plus d'un verrou à la fois peut se bloquer contre une transaction intelligemment conçue qui saisit les verrous dans l'ordre inverse.

Answer 2

L'absence de blocages est un problème incroyablement coûteux dans le cas général, car vous devez connaître toutes les tables/objets que vous allez lire et modifier pour chaque transaction en cours (y compris les SELECT). La philosophie générale s'appelle verrouillage biphasé strict et ordonné (à ne pas confondre avec l'engagement biphasé) ( http://en.wikipedia.org/wiki/Two_phase_locking même 2PL n'est pas garantie pas de blocages)

Très peu de SGBD mettent en œuvre un système 2PL strict en raison de l'impact considérable sur les performances (il n'y a pas de repas gratuit) lorsque toutes les transactions attendent l'exécution d'une simple instruction SELECT.

Quoi qu'il en soit, si ce sujet vous intéresse vraiment, jetez un coup d'œil à SET ISOLATION LEVEL dans SQL Server. Vous pouvez modifier cela si nécessaire. http://en.wikipedia.org/wiki/Isolation_level

Pour plus d'informations, voir wikipedia sur Serializability : http://en.wikipedia.org/wiki/Serializability

Cela dit, une bonne analogie est celle des révisions du code source : vérifiez tôt et souvent. Faites en sorte que vos transactions soient petites (en nombre d'instructions SQL, en nombre de lignes modifiées) et rapides (le temps d'horloge aide à éviter les collisions avec d'autres). Il peut être agréable et ordonné de faire BEAUCOUP de choses dans une seule transaction - et en général, je suis d'accord avec cette philosophie - mais si vous rencontrez beaucoup de blocages, vous pouvez diviser la transaction en plusieurs petites transactions et vérifier leur état dans l'application au fur et à mesure que vous avancez. TRAN 1 - OK Y/N ? Si O, envoyer TRAN 2 - OK O/N ? etc. etc.

Soit dit en passant, au cours de mes nombreuses années d'expérience en tant que DBA et développeur (d'applications de bases de données multi-utilisateurs mesurant des milliers d'utilisateurs simultanés), je n'ai jamais trouvé que les blocages étaient un problème si important que je devais en prendre conscience (ou changer les niveaux d'isolation bon gré mal gré, etc.).

Answer 3

Il n'existe pas de solution générale magique à ce problème qui fonctionne dans la pratique. Vous pouvez pousser la concurrence vers l'application, mais cela peut être très complexe, en particulier si vous devez coordonner avec d'autres programmes fonctionnant dans des espaces mémoire distincts.

Réponses générales pour réduire les possibilités de blocage :

1. Optimisation de base des requêtes (utilisation correcte de l'index), conception évitant les points chauds, maintien des transactions pendant les périodes les plus courtes possibles... etc.

2. Dans la mesure du possible, fixez des délais d'interrogation raisonnables, de sorte que si un blocage se produit, il se résorbe de lui-même après l'expiration du délai d'attente.

3. Les blocages dans MSSQL sont souvent dus à son modèle de concurrence en lecture par défaut, il est donc très important de ne pas en dépendre - assumez le style Oracle MVCC dans toutes les conceptions. Utilisez l'isolation snapshot ou si possible le niveau d'isolation READ UNCOMMITED.

Answer 4

Je crois que le modèle de lecture/écriture utile suivant est à l'épreuve des blocages, compte tenu de certaines contraintes :

Contraintes :

1. Une table
2. Un index ou un PK est utilisé pour la lecture/écriture afin que le moteur n'ait pas recours aux verrous de table.
3. Un lot d'enregistrements peut être lu à l'aide d'une seule clause SQL where.
4. Utilisation de la terminologie du serveur SQL.

Cycle d'écriture :

1. Toutes les écritures dans une seule transaction "Read Committed".
2. La première mise à jour de la transaction concerne un enregistrement spécifique, toujours présent. dans chaque groupe de mise à jour.
3. Plusieurs enregistrements peuvent alors être écrits dans n'importe quel ordre. (Ils sont "protégés" par l'écriture du premier enregistrement).

Lire le cycle :

1. Le niveau de transaction par défaut en lecture engagée
2. Aucune transaction
3. Lire les enregistrements comme une seule instruction de sélection.

Avantages :

1. Les cycles d'écriture secondaires sont bloqués à l'écriture du premier enregistrement jusqu'à ce que la première transaction d'écriture soit entièrement terminée.
2. Les lectures sont bloquées/en file d'attente/exécutées de manière atomique entre les commandes d'écriture.
3. Obtenir une cohérence au niveau des transactions sans avoir recours à "Serializable".

J'ai besoin que cela fonctionne aussi, alors s'il vous plaît, commentez/corrigez !

Answer 5

Si vous avez suffisamment de contrôle sur la conception de votre application, limitez vos mises à jour/insertions à des procédures stockées spécifiques et supprimez les privilèges de mise à jour/insertion des rôles de base de données utilisés par l'application (n'autorisez explicitement les mises à jour qu'à travers ces procédures stockées).

Isolez vos connexions à la base de données à une classe spécifique dans votre application (chaque connexion doit provenir de cette classe) et spécifiez que les connexions "query only" définissent le niveau d'isolation à "dirty read" ... l'équivalent d'un (nolock) sur chaque jointure.

De cette façon, vous isolez les activités qui peut provoquent des verrous (sur des procédures stockées spécifiques) et sortent les "lectures simples" de la "boucle de verrouillage".