Décrire la concurrence
Une fonctionnalité principale des bases de données multiutilisateurs est la concurrence. La concurrence utilise le verrouillage et le blocage pour permettre aux données de rester cohérentes avec de nombreux utilisateurs qui mettent à jour et lisent les données en même temps. Par exemple, en raison des frais d’expédition, tous nos produits ont une augmentation de prix de 5 $. Dans le même temps, en raison des taux de devise, tous les produits ont une baisse de 3 % de leur prix. Si ces mises à jour se produisent exactement en même temps, le prix final sera variable et il y aura probablement de nombreuses erreurs. À l’aide du verrouillage, vous pouvez vous assurer qu’une mise à jour se termine avant le début de l’autre.
La concurrence se produit au niveau de la transaction. Une transaction d’écriture peut empêcher d’autres transactions de mettre à jour et même de lire les mêmes données. De même, une transaction de lecture peut bloquer d’autres lecteurs ou même certains écrivains. Pour cette raison, il est important d’éviter les transactions inutilement longues ou les transactions couvrant des quantités excessives de données.
Il existe de nombreux niveaux d’isolation des transactions spécifiques qui peuvent être utilisés pour définir la façon dont un système de base de données gère plusieurs utilisateurs. Pour les besoins de ce module, nous allons examiner les grandes catégories de niveau d’isolation, de verrouillage optimiste et de verrouillage pessimiste.
Remarque
Le détail complet du verrouillage des transactions au-delà de l’accès concurrentiel est lié davantage aux performances et non seulement en fonction du code, bien que le bon code fonctionne mieux. Pour plus d’informations, consultez le Guide de verrouillage des transactions et de contrôle de version des lignes SQL Server détaillé. Pour plus d’informations sur le blocage, consultez également la documentation sur les performances de SQL Server.
Accès concurrentiel optimiste
Avec le verrouillage optimiste, on suppose que peu de mises à jour conflictuelles se produisent. Au début de la transaction, l’état initial des données est enregistré. Avant la validation de la transaction, l’état actuel est comparé à l’état initial. Si les états sont identiques, la transaction est terminée. Si les états sont différents, la transaction est annulée.
Par exemple, vous disposez d’une table contenant les commandes des dernières années. Ces données sont rarement mises à jour, mais les rapports sont souvent générés. En utilisant le verrouillage optimiste, les transactions ne se bloquent pas mutuellement et le système s’exécute plus efficacement. Malheureusement, des erreurs ont été détectées au cours des dernières années, les données et les mises à jour doivent avoir lieu. Alors qu’une transaction met à jour chaque ligne, une autre transaction apporte une modification mineure à une seule ligne en même temps. Étant donné que l’état des données a été modifié pendant l’exécution de la transaction initiale, la transaction entière est restaurée.
Accès concurrentiel pessimiste
Avec le verrouillage pessimiste, on suppose que de nombreuses mises à jour sont effectuées en même temps sur les données. En utilisant des verrous, une seule mise à jour peut se produire en même temps et les lectures des données sont empêchées pendant la mise à jour. Cela peut empêcher des restaurations importantes, comme illustré dans l’exemple précédent, mais aussi entraîner un blocage inutile des requêtes.
Il est important de prendre en compte la nature de vos données et les requêtes en cours d’exécution sur les données lors de la décision d’utiliser une concurrence optimiste ou pessimiste pour garantir des performances optimales.
Isolement d'instantané
Il existe cinq niveaux d’isolation différents dans SQL Server, mais pour ce module, nous allons nous concentrer uniquement sur READ_COMMITTED_SNAPSHOT_OFF et READ_COMMITTED_SNAPSHOT_ON. READ_COMMITTED_SNAPSHOT_OFF est le niveau d’isolation par défaut pour SQL Server. READ_COMMITTED_SNAPSHOT_ON est le niveau d’isolation par défaut pour Azure SQL Database.
READ_COMMITTED_SNAPSHOT_OFF gère des verrous sur les lignes affectées jusqu’à la fin de la transaction si la requête utilise le niveau d’isolation de la transaction lecture validée. Bien qu’il soit possible que certaines mises à jour se produisent, telles que la création d’une nouvelle ligne, cela empêche la plupart des modifications conflictuelles apportées aux données lues ou mises à jour. Il s’agit d’une concurrence pessimiste.
READ_COMMITTED_SNAPSHOT_ON prend un instantané des données. Les mises à jour sont ensuite effectuées sur cet instantané, ce qui permet à d’autres connexions d’interroger les données d’origine. À la fin de la transaction, l’état actuel des données est comparé à l’instantané. Si les données sont identiques, la transaction est validée. Si les données diffèrent, la transaction est annulée et rétablie à son état initial.
Pour modifier le niveau d’isolation en « READ_COMMITTED_SNAPSHOT_ON », émettez la commande suivante :
ALTER DATABASE *db_name* SET READ_COMMITTED_SNAPSHOT ON;
Pour modifier le niveau d’isolation en READ_COMMITTED_SNAPSHOT_OFF, exécutez la commande suivante :
ALTER DATABASE *db_name* SET READ_COMMITTED_SNAPSHOT OFF;
Si la base de données a été modifiée pour activer l’instantané à lecture validée, toute transaction qui utilise le niveau d’isolation lecture validée par défaut utilise le verrouillage optimiste.
Remarque
L’isolation d’instantané se produit uniquement pour les transactions à lecture validée. Les transactions qui utilisent d’autres niveaux d’isolation ne sont pas affectées.