Décrire le blocage et le verrouillage
L’une des fonctionnalités des bases de données relationnelles est le verrouillage, lequel est essentiel pour maintenir les propriétés d’atomicité, de cohérence et d’isolation du modèle ACID. Tous les systèmes de gestion de base de données relationnelle (SGBDR) bloquent les actions qui enfreignent la cohérence et l’isolation des écritures dans une base de données. Les programmeurs SQL doivent commencer et terminer les transactions au bon endroit afin de garantir la cohérence logique de leurs données. Le moteur de base de données fournit quant à lui des mécanismes de verrouillage qui protègent également la cohérence logique des tables affectées par ces requêtes. Ces actions font partie intégrante du modèle relationnel.
Sur SQL Server, un blocage se produit lorsqu’un processus détient un verrou sur une ressource spécifique (ligne, page, table, base de données) et qu’un deuxième processus tente d’acquérir un verrou avec un type de verrou incompatible sur la même ressource. En général, les verrous sont détenus pendant une brève période. Quand le processus qui détient le verrou le libère, le processus bloqué peut alors acquérir le verrou et terminer sa transaction.
SQL Server verrouille la plus petite quantité de données nécessaire pour effectuer la transaction. Ce comportement permet une concurrence maximale. Par exemple, si SQL Server verrouille une seule ligne, toutes les autres lignes de la table sont disponibles pour d’autres processus, si bien qu’un travail peuvent être effectué simultanément. Toutefois, chaque verrou nécessitant des ressources mémoire, il n’est pas rentable d’avoir un processus avec des milliers de verrous individuels sur une seule table. SQL Server tente de trouver un équilibre entre concurrence et coût. L’une des techniques utilisées est l’escalade de verrous. Si SQL Server doit verrouiller plus de 5 000 lignes sur un seul objet dans une seule instruction, il réaffecte les verrous de ligne à un verrou de table unique.
Le verrouillage est un comportement normal et se produit plusieurs fois au cours d’une journée normale. Le verrouillage ne devient problématique que s’il provoque un blocage qui n’est pas résolu rapidement. Les blocages peuvent être à l’origine de deux types de problèmes de performances :
Un processus détient des verrous sur un ensemble de ressources pendant une période prolongée avant de libérer ces ressources. Ces verrous entraînent le blocage d’autres processus, ce qui peut nuire aux performances des requêtes et à la concurrence.
Un processus obtient des verrous sur un ensemble de ressources et ne les libère jamais. Ce problème nécessite l’intervention de l’administrateur.
L’interblocage est un autre scénario de blocage. Celui-ci se produit lorsqu’une transaction a un verrou sur une ressource et qu’une autre transaction a un verrou sur une deuxième ressource. Chaque transaction tente alors de prendre un verrou sur la ressource qui est actuellement verrouillée par l’autre transaction. En théorie, ce scénario entraîne une attente infinie car aucune transaction ne peut se terminer. Toutefois, le moteur SQL Server est doté d’un mécanisme de détection de ces scénarios et tue l’une des transactions afin d’atténuer l’interblocage (en fonction de la transaction ayant effectué le moins de travail devant être restauré). La transaction qui est tuée est appelée la « victime de l’interblocage ». Les interblocages sont enregistrés dans la session d’événements étendus system_health qui est activée par défaut.
Il est important de comprendre le concept d’une transaction. La validation automatique est le mode par défaut de SQL Server et d’Azure SQL Database, ce qui signifie que les modifications apportées par l’instruction ci-dessous sont automatiquement enregistrées dans le journal des transactions de la base de données.
INSERT INTO DemoTable (A) VALUES (1);
Pour permettre aux développeurs de contrôler avec plus de précision le code de leur application, SQL Server vous permet également de contrôler explicitement vos transactions. La requête ci-dessous prendrait un verrou sur une ligne de la table DemoTable qui ne serait pas libéré tant qu’une commande ultérieure de validation de la transaction ne serait pas ajoutée.
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
La méthode appropriée pour écrire la requête ci-dessus est la suivante :
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
COMMIT TRANSACTION
La commande COMMIT TRANSACTION valide explicitement un enregistrement des modifications apportées au journal des transactions. Les données modifiées finissent par arriver dans le fichier de données de façon asynchrone. Ces transactions représentent une unité de travail pour le moteur de base de données. Si le développeur oublie d’émettre la commande COMMIT TRANSACTION, la transaction restera ouverte et les verrous ne seront pas libérés. C’est l’une des principales raisons des transactions longues.
L’autre mécanisme utilisé par le moteur de base de données pour contribuer à la concurrence de la base de données est le contrôle de version de ligne. Quand un niveau d’isolation de contrôle de version de ligne est activé dans la base de données, le moteur gère les versions de chaque ligne modifiée dans TempDB. Ce mode est généralement utilisé dans les charges de travail mixtes pour empêcher les requêtes de lecture de bloquer les requêtes qui écrivent dans la base de données.
Pour surveiller les transactions ouvertes en attente de validation ou de restauration, exécutez la requête suivante :
SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, transaction_type = CASE tat.transaction_type WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow
, transaction_state = CASE tat.transaction_state
WHEN 0 THEN 'The transaction has not been completely initialized yet.'
WHEN 1 THEN 'The transaction has been initialized but has not started.'
WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
WHEN 6 THEN 'The transaction has been committed.'
WHEN 7 THEN 'The transaction is being rolled back.'
WHEN 8 THEN 'The transaction has been rolled back.' END
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat
INNER JOIN sys.dm_tran_session_transactions tst on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;
Niveaux d'isolement
SQL Server offre plusieurs niveaux d’isolation pour vous permettre de définir le niveau de cohérence et d’exactitude que vous devez garantir pour vos données. Les niveaux d’isolation vous permettent de trouver un équilibre entre concurrence et cohérence. Le niveau d’isolation n’affecte pas les verrous pris pour empêcher la modification des données. Une transaction obtient toujours un verrou exclusif sur les données qui sont modifiées. Toutefois, votre niveau d’isolation peut affecter la durée pendant laquelle vos verrous sont détenus. Des niveaux d’isolation inférieurs augmentent la capacité de plusieurs processus utilisateur à accéder aux données en même temps, mais augmentent les risques de cohérence des données qui peuvent se produire. Les niveaux d’isolation dans SQL Server sont les suivants :
Lecture non validée : niveau d’isolation le plus bas disponible. Les lectures erronées sont autorisées, ce qui signifie qu’une transaction peut voir les modifications apportées par une autre transaction qui n’ont pas encore été validées.
Lecture validée : ce niveau permet à une transaction de lire les données précédemment lues, mais non modifiées par une autre transaction sans attendre la fin de la première transaction. Ce niveau libère également les verrous de lecture dès que l’opération de sélection est effectuée. Il s’agit du niveau de SQL Server par défaut.
Lecture renouvelée : ce niveau conserve les verrous de lecture et d’écriture acquis sur les données sélectionnées jusqu’à la fin de la transaction.
Sérialisable : niveau d’isolation le plus élevé dans lequel les transactions sont totalement isolées. Les verrous de lecture et d’écriture sont acquis sur les données sélectionnées et ne sont libérés qu’à la fin de la transaction.
SQL Server inclut également deux niveaux d’isolation qui incluent le contrôle de version de ligne.
Capture instantanée de Lecture validée : à ce niveau, les opérations de lecture ne prennent pas de verrous de ligne ni de page. Le moteur présente à chaque opération un instantané cohérent des données telles qu’elles existaient au début de la requête. Ce niveau est généralement utilisé quand les utilisateurs exécutent fréquemment des requêtes de rapport sur une base de données OLTP afin d’éviter que les opérations de lecture ne bloquent les opérations d’écriture.
Capture instantanée : ce niveau fournit une cohérence de lecture au niveau des transactions par le biais du contrôle de version de ligne. Ce niveau est vulnérable aux conflits de mise à jour. Si une transaction qui s’exécute sous ce niveau lit des données modifiées par une autre transaction, une mise à jour par la transaction d’instantané est arrêtée et annulée. Ce n’est pas un problème avec l’isolation de capture instantanée de lecture validée.
Les niveaux d’isolation sont définis pour chaque session avec la commande SET T-SQL, comme indiqué ci-dessous :
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
Il n’existe aucun moyen de définir un niveau d’isolation global pour toutes les requêtes exécutées dans une base de données ou toutes les requêtes exécutées par un utilisateur particulier. Il s’agit d’un paramètre au niveau de la session.
Supervision des problèmes de blocage
L’identification des problèmes de blocage peut être problématique, car ils peuvent être de nature sporadique. Il existe une DMV appelée sys.dm_tran_locks, qui peut être jointe à sys.dm_exec_requests afin de fournir des informations supplémentaires sur les verrous détenus par chaque session. Il est recommandé de superviser les problèmes de blocage en continu à l’aide du moteur des événements étendus.
Il existe deux catégories de problèmes de blocage :
Conception transactionnelle médiocre. Comme indiqué ci-dessus, une transaction sans
COMMIT TRANSACTIONne se termine jamais. Bien qu’il s’agisse d’un exemple simple, essayer d’en faire trop dans une transaction unique ou d’avoir une transaction distribuée qui utilise une connexion de serveur lié peut conduire à des performances imprévisibles.Transactions à long terme provoquées par la conception de schéma. Il s’agit fréquemment d’une mise à jour sur une colonne avec un index manquant ou d’une requête de mise à jour mal conçue.
La supervision des problèmes de performances liés au verrouillage vous permet d’identifier rapidement une dégradation des performances liée au verrouillage.
Pour plus d’informations sur la surveillance des blocages, consultez Comprendre et résoudre les problèmes de blocage SQL Server.