Décrire le blocage et le verrouillage
Le verrouillage est une fonctionnalité clé des bases de données relationnelles, essentielle pour maintenir l’atomicité, la cohérence et les propriétés d’isolation du modèle ACID. Toutes les SGBDR bloquent les actions qui violent la cohérence et l’isolation des écritures de base de données. Les programmeurs SQL doivent démarrer et mettre fin aux transactions aux bons points pour garantir la cohérence des données. Le moteur de base de données fournit des mécanismes de verrouillage pour protéger la cohérence logique des tables affectées, qui est fondamentale pour le modèle relationnel.
Dans SQL Server, le blocage se produit lorsqu’un processus contient 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 règle générale, les verrous sont conservés pendant une courte période, et une fois que le processus contenant le verrou le libère, le processus bloqué peut acquérir le verrou et terminer sa transaction.
SQL Server verrouille la plus petite quantité de données nécessaire pour effectuer une transaction, ce qui permet une concurrence maximale. Par exemple, si SQL Server verrouille une seule ligne, toutes les autres lignes de la table restent disponibles pour d’autres processus, ce qui active le travail simultané. Toutefois, chaque verrou nécessite des ressources de mémoire, de sorte qu’il n’est pas rentable pour un processus de contenir des milliers de verrous individuels sur une seule table. Pour équilibrer la concurrence avec les coûts, SQL Server utilise une technique appelée escalade de verrous. Si plus de 5 000 lignes sur un seul objet doivent être verrouillées dans une seule instruction, SQL Server réaffecte les verrous de plusieurs lignes à un seul verrou de table.
Le verrouillage est un comportement normal et se produit fréquemment tout au long de la journée. Il ne devient problématique que lorsqu’il provoque le blocage qui n’est pas rapidement résolu. Il existe deux types de problèmes de performances provoqués par le blocage :
- Un processus maintient des verrous sur un ensemble de ressources pendant une période prolongée avant de les relâcher, ce qui entraîne le blocage et la dégradation des performances des requêtes et de l’accès concurrentiel.
- Un processus acquiert des verrous sur un ensemble de ressources et ne les libère jamais, nécessitant une intervention de l’administrateur pour résoudre.
L’interblocage est un autre scénario de blocage qui se produit lorsqu’une transaction contient un verrou sur une ressource et qu’une autre transaction contient un verrou sur une autre ressource. Chaque transaction tente ensuite d’acquérir un verrou sur la ressource actuellement verrouillée par l’autre transaction, ce qui entraîne une attente infinie, car aucune transaction ne peut se terminer. Le moteur SQL Server détecte ces scénarios et résout le blocage en interrompant l’une des transactions, en fonction de celle qui a effectué le moins de travail à annuler. 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 suivante 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 d’avoir un contrôle plus précis sur leur code d’application, SQL Server vous permet également de contrôler explicitement vos transactions. La requête suivante prendrait un verrou sur une ligne de la table DemoTable qui ne serait pas libérée 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 façon appropriée d’écrire la requête suivante 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 se rendre dans le fichier de données de manière 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 COMMIT TRANSACTION commande, la transaction reste ouverte et les verrous ne seront pas libérés. Il s’agit de l’une des principales raisons des transactions à long terme.
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. Lorsqu’un niveau d’isolation de contrôle de version de ligne est activé pour la base de données, le moteur gère les versions de chaque ligne modifiée dans TempDB. Cela est généralement utilisé dans les charges de travail à usage mixte, afin d’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’isolation
SQL Server offre plusieurs niveaux d’isolation pour vous permettre de définir le niveau de cohérence et de correction dont vous avez besoin pour vos données. Les niveaux d’isolation vous permettent de trouver un équilibre entre la concurrence et la 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 conservés. Les 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.
Read committed – permet à une transaction de lire des données précédemment lues, mais pas 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 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 pas publiés jusqu’à la fin de la transaction.
SQL Server inclut également deux niveaux d’isolation qui incluent le contrôle de version des lignes.
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é lorsque les utilisateurs exécutent des requêtes de création de rapports fréquentes sur une base de données OLTP afin d’empêcher les opérations de lecture de bloquer 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 T-SQL #D0, comme indiqué :
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 pour toutes les requêtes exécutées par un utilisateur particulier. Il s’agit d’un paramètre de niveau session.
Surveillance des problèmes de blocage
L’identification des problèmes de blocage peut être difficile en raison de leur nature sporadique. La DMV sys.dm_tran_locks, lorsqu’elle est associée à sys.dm_exec_requests, fournit des informations sur les verrous détenus par chaque session. Un moyen plus efficace de surveiller les problèmes de blocage consiste à utiliser le moteur d’événements étendus de manière continue.
Les problèmes de blocage se répartissent généralement en deux catégories :
- Conception transactionnelle médiocre : par exemple, une transaction sans
COMMIT TRANSACTIONne se terminera jamais. Essayer de faire trop de travail dans une transaction unique ou avoir une transaction distribuée à l’aide d’une connexion de serveur lié peut entraîner des performances imprévisibles. - Transactions longues provoquées par la conception de schéma : cela implique souvent une mise à jour sur une colonne avec un index manquant ou une requête de mise à jour mal conçue.
La surveillance des problèmes de performances liés au verrouillage vous permet d’identifier rapidement la dégradation des performances liée au verrouillage.
Afin d'obtenir plus d'informations sur la surveillance du blocage, consultez Comprendre et résoudre les problèmes de blocage SQL Server.