Quoi de plus naturel pour un moteur de base de données relationnelles SQL que le verrouillage ? En effet, toute exécution de requête entraîne l’acquisition de verrous. Comme je me rends compte que c’est un sujet quelque peu méconnu, je vous propose ici d’explorer dans quelles conditions une escalade de verrous se produit, après avoir succinctement décrit ce qu’est le verrouillage.
Qu’est-ce que le verrouillage ?
Le verrouillage est un mécanisme qui permet à plusieurs utilisateurs d’accéder à des ressources, tout en évitant que cela résulte en des incohérences de données. Des verrous sont acquis lorsque le moteur de base de données a besoin d’accéder à une ressource pour la lire ou la modifier. Sous SQL Server, cette opération est purement logique, et sert le support de l’ACIDité de toute transaction.
Ce mécanisme est entièrement géré par le moteur de stockage de SQL Server, avec lequel l’utilisateur n’a que peu de moyens d’interagir (et tant mieux !).
On peut bien sûr affecter le comportement d’une transaction en indiquant le niveau d’isolation de transaction sous lequel elle doit être traitée. Il est rarement nécessaire d’indiquer à SQL Server quel doit être le type de verrouillage à choisir à l’aide d’indicateurs de table ou de requête. SQL Server a de toute façon la primauté sur ce choix, puisqu’il outrepasse parfois les recommandations du développeur ou du DBA. Globalement, le moteur de base de données SQL Server effectue le meilleur choix.
Tout ceci fait qu’outre le niveau d’isolation de transaction, lorsqu’on code pour SQL Server, on n’a pas à se soucier de ce que telle ou telle opération DML va produire en termes de verrouillage. Cependant, l’accès concurrent à une même ressource provoque parfois des situations de blocage, voire d’étreinte mortelle (deadlock) : il est alors nécessaire de comprendre ce qui a provoqué cette situation pour pouvoir orienter les choix de SQL Server, de sorte à éviter qu’un tel phénomène se reproduise. A ce titre, il est nécessaire de savoir :
Рquelles sont les ressources sur lesquelles les transactions acqui̬rent des verrous (ligne ou cl̩ ou page, partition, table, sch̩ma, base de donn̩es)
– quel est le type de verrou qui a été acquis sur la ressource (intentionnel, partagé, de mise à jour, exclusif, de copie en bloc)
Dans quelles conditions une escalade de verrou se produit-elle ?
SQL Server choisit toujours d’acquérir des verrous de faible granularité, de façon à maximiser la concurrence d’accès. Donc la plupart du temps, SQL Server acquiert des verrous de ligne/clé, ou de page. Il faut noter que l’acquisition de verrous se fait directement soit au niveau ligne/clé, soit au niveau page : l’escalade de verrous ne se fait jamais de ligne/clé à page. En effet, si le moteur de base de données a calculé qu’il devra accéder à toutes les lignes d’un jeu de pages, autant qu’il acquière un unique verrou sur chacune desdites pages. Tout verrou, quelle que soit sa granularité, consomme 96 octets de mémoire, et est une structure purement logique.
Le principe général de l’escalade de verrou est de remplacer un grand nombre de verrous par un verrou de plus forte granularité, de façon à consommer moins de mémoire. Cela se fait au détriment de la concurrence d’accès, mais en faveur :
– de la vitesse d’exécution : un verrous a forte granularité est acquis pour une durée plus courte
Рmais aussi et surtout de la consommation de m̩moire : moins on acquiert de verrous, moins on consomme de m̩moire.
Au niveau requête, l’escalade est réalisée dès qu’elle acquiert ou requiert plus de 5000 verrous.
Au niveau instance, lorsque SQL Server détecte que la gestion des verrous consomme 24% de la taille du cache de données (à l’exclusion de la mémoire AWE sur les systèmes x86), il va scruter les sessions qui en détiennent en grand nombre, et escalader les verrous de celles-ci. Par ailleurs, l’option de configuration locks permet de contrôler l’escalade de verrous : si la valeur configurée est différente zéro (valeur par défaut), alors SQL Server réalisera une escalade de verrous dès que 40% de cette valeur en nombre de verrous acquis ou requis est dépassée. Il réalisera ensuite une nouvelle escalade de verrous après chaque bloc de 1250 verrous acquis ou requis dans ces conditions. L’utilisateur n’a aucun contrôle sur les sessions que SQL Server va sélectionner pour l’escalade de verrous, mais gageons que comme pour bien d’autres fonctionnalités, il n’ait pas besoin d’aide.
Enfin, si la gestion des verrous consomme 60% de la mémoire allouée à SQL Server à travers l’option max server memory (MB), ce dernier peut prendre la décision d’annuler une ou plusieurs requêtes responsables de cette grande consommation. Dans ce cas, les requêtes impactées lèvent une exception de numéro 1204, dont le libellé est le suivant :
L’instance du moteur de base de données SQL Server ne peut pas obtenir une ressource LOCK en ce moment. Réexécutez votre instruction lorsque le nombre d’utilisateurs actifs est moindre. Demandez à l’administrateur de base de données de vérifier la configuration du verrou et de la mémoire pour cette instance, ou de vérifier les longues transactions.
ou en Anglais :
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
Cette erreur est aussi écrite dans les journaux de SQL Server.
Quelques requêtes
La DMV sys.dm_os_memory_clerks nous permet de trouver la taille du gestionnaire de verrous. Voici la requête pour jusqu’à SQL Server 2008 R2 inclus :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO SELECT type , name , SUM(single_pages_kb) AS cache_size_from_buffer_pool_KB , SUM(multi_pages_kb) AS cache_size_out_buffer_pool_KB FROM sys.dm_os_memory_clerks --WHERE name LIKE 'Lock Manager%' GROUP BY type, name ORDER BY security_token_cache_size_from_buffer_pool_KB DESC</code> Et dès SQL Server 2012 : <code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO SELECT type , name , SUM(pages_kb) AS cache_size_KB FROM sys.dm_os_memory_clerks --WHERE name LIKE 'Lock Manager%' GROUP BY type, name ORDER BY cache_size_KB DESC |
On trouve un gestionnaire de verrous par noeud NUMA. Si nous laissons la clause WHERE de ces deux requêtes en commentaire, on doit trouver CACHESTORE_SQLCP et CACHESTORE_OBJCP sur les deux premières lignes du résultat de la requête, puisque le premier représente le cache de plan de requêtes ad-hoc ou en SQL dynamique, et le second celui des procédures stockées et déclencheurs. Si on y trouve OBJECTSTORE_LOCK_MANAGER, c’est que le gestionnaire de verrous consomme probablement une trop grande quantité de mémoire.
LOCK_ESCALATION : cas des tables partitionnées
Dès SQL Server 2008, il est possible de réaliser l’escalade de verrous au niveau partition, avant que celle-ci se produise au niveau table. Mais pour ce faire, il est nécessaire que celle-ci soit positionnée à AUTO : or par défaut, elle est à TABLE. On trouve les tables candidates à ce changement à l’aide de la requête suivante :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | ;WITH PARTITIONNED_TABLE AS ( SELECT DISTINCT object_id FROM sys.dm_db_partition_stats WHERE partition_number > 1 ) SELECT S.name + '.' + T.name AS table_or_indexed_view_name , T.lock_escalation_desc , CASE T.lock_escalation_desc WHEN 'TABLE' THEN 'ALTER TABLE ' + S.name + '.' + T.name + ' SET (LOCK_ESCALATION = AUTO)' ELSE NULL END AS sql_change_lock_escalation_level FROM sys.schemas AS S INNER JOIN sys.tables AS T ON S.schema_id = T.schema_id INNER JOIN PARTITIONNED_TABLE AS PT ON PT.object_id = T.object_id --WHERE T.lock_escalation_desc = 'TABLE' |
Bon verrouillage à tous !
ElSüket.