Instructions pour les niveaux d’isolation des transactions avec des tables Memory-Optimized

Dans de nombreux scénarios, vous devez spécifier le niveau d’isolation des transactions. L’isolation des transactions pour les tables mémoire optimisées diffère des tables sur disque.

Conditions requises pour spécifier le niveau d’isolation des transactions :

TRANSACTION ISOLATION LEVEL est une option requise pour le bloc ATOMIC comprenant le contenu d’une procédure stockée compilée en mode natif.
En raison des restrictions relatives à l’utilisation au niveau d’isolation dans les transactions entre conteneurs, les utilisations de tables optimisées en mémoire dans les Transact-SQL interprétées doivent souvent être accompagnées d’un indicateur de table spécifiant le niveau d’isolation utilisé pour accéder à la table. Pour plus d’informations sur les indicateurs de niveau d’isolation et les transactions entre conteneurs, consultez Niveaux d’isolation des transactions.
Le niveau d’isolation de transaction souhaité doit être déclaré explicitement. Il n’est pas possible d’utiliser des indicateurs de verrouillage (tels que XLOCK) pour garantir l’isolation de certaines lignes ou tables dans la transaction.
L’application qui accède à la base de données doit implémenter une logique de nouvelle tentative pour traiter les erreurs résultant de conflits, d’échecs de validation et d’échecs de dépendance de validation. Notez que les échecs de dépendance de validation peuvent se produire même avec des transactions en lecture seule.
Les transactions de longue durée doivent être évitées avec des tables optimisées en mémoire. Ces transactions augmentent la probabilité de conflits et de terminaisons de transaction suivants. Une transaction de longue durée reporte également le garbage collection. Plus une transaction s’exécute, plus In-Memory OLTP conserve les versions de lignes récemment supprimées, ce qui peut réduire les performances de recherche des nouvelles transactions.

Les tables sur disque reposent généralement sur le verrouillage et le blocage pour l’isolation des transactions. Les tables optimisées en mémoire s’appuient sur la détection de plusieurs versions et des conflits pour garantir l’isolation. Pour plus d’informations, consultez la section sur la détection, la validation et la validation des dépendances dans les transactions dans les tables Memory-Optimized.

Les tables basées sur disque autorisent le contrôle multiversion avec les niveaux d’isolation SNAPSHOT et READ_COMMITTED_SNAPSHOT. Pour les tables optimisées en mémoire, tous les niveaux d’isolation sont basés sur plusieurs versions, notamment REPEATABLE READ et SERIALIZABLE.

Types de transactions

Chaque requête de SQL Server s’exécute dans le contexte d’une transaction.

Il existe trois types de transactions dans SQL Server :

Transactions de validation automatique. S’il n’existe aucun contexte de transaction actif et que les transactions implicites ne sont pas définies sur ON dans la session, chaque requête a son propre contexte de transaction. La transaction démarre lorsque l’instruction démarre l’exécution et se termine à la fin de l’instruction.
Transactions explicites. L’utilisateur démarre la transaction via une opération BEGIN TRAN ou BEGIN ATOMIC explicite. La transaction est terminée en suivant le COMMIT et ROLLBACK ou END correspondants (dans le cas d’un bloc atomique).
Transactions implicites. Lorsque l’option IMPLICIT_TRANSACTIONS est définie sur ON, une transaction est démarrée implicitement chaque fois que l’utilisateur exécute une instruction et qu’il n’existe aucun contexte de transaction actif. La transaction est effectuée par le biais d’une validation explicite et de ROLLBACK.

Isolation READ COMMITTED de référence

READ COMMITTED est le niveau d’isolation par défaut dans SQL Server.

Le niveau d’isolation READ COMMITTED garantit que les transactions ne voient aucune donnée non validée des modifications en dehors de la transaction actuelle. En d’autres termes, la transaction lit uniquement les données qui ont été validées dans la base de données ou ont été modifiées par la transaction actuelle.

Tous les niveaux d’isolation pris en charge pour les tables optimisées en mémoire fournissent la garantie validée en lecture. Par conséquent, si la transaction ne nécessite pas de garanties plus fortes, vous pouvez utiliser l’un des niveaux d’isolation pris en charge pour les tables optimisées en mémoire. SNAPSHOT utilise les ressources système les plus rares, par rapport à d’autres niveaux d’isolation.

La garantie fournie par le niveau d’isolation SNAPSHOT (le niveau d’isolation le plus bas pris en charge pour les tables optimisées en mémoire) inclut les garanties de READ COMMITTED. Chaque instruction de la transaction lit la même version cohérente de la base de données. Non seulement toutes les lignes lues par la transaction validée dans la base de données, également toutes les opérations de lecture voient l’ensemble des modifications apportées par le même ensemble de transactions.

Instructions : Si seule la garantie d’isolation READ COMMITTED est requise, utilisez l’isolation SNAPSHOT avec des procédures stockées compilées en mode natif et pour accéder aux tables optimisées en mémoire via des Transact-SQL interprétées.

Pour les transactions de validation automatique, le niveau d’isolation READ COMMITTED est implicitement mappé à SNAPSHOT pour les tables mémoire optimisées. Par conséquent, si le TRANSACTION ISOLATION LEVEL paramètre de session est défini sur READ COMMITTED, il n’est pas nécessaire de spécifier le niveau d’isolation via un indicateur de table lors de l’accès aux tables optimisées en mémoire.

L’exemple de transaction de validation automatique suivant montre une jointure entre une table optimisée en mémoire Clients et une table régulière [Historique des commandes], dans le cadre d’un lot ad hoc :

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  
SELECT *   
FROM dbo.Customers AS c   
LEFT JOIN dbo.[Order History] AS oh   
    ON c.customer_id = oh.customer_id;

L’exemple de transactions explicites ou implicites suivant montre la même jointure, mais cette fois dans une transaction utilisateur explicite. La table optimisée en mémoire Les clients sont accessibles sous isolation d’instantané, comme indiqué par le biais de l’indicateur de table WITH (SNAPSHOT), et la table régulière [Historique des commandes] est accessible sous l’isolation validée en lecture :

SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
BEGIN TRAN  
SELECT * FROM dbo.Customers c with (SNAPSHOT)   
LEFT JOIN dbo.[Order History] oh   
    ON c.customer_id=oh.customer_id  
...  
COMMIT

Différences opérationnelles

Outre la garantie validée en lecture, il existe également deux détails d’implémentation clés sur lesquels les applications utilisant des tables sur disque peuvent s’appuyer. Tenez compte des éléments suivants lors de la conversion d’une table basée sur disque accessible à l’aide de l’isolation READ COMMITTED vers une table optimisée en mémoire accessible à l’aide de l’isolation SNAPSHOT :

L’implémentation du niveau d’isolation READ COMMITTED pour les tables sur disque (en supposant que READ_COMMITTED_SNAPSHOT est OFF) utilise des verrous pour empêcher les conflits entre les lecteurs et les enregistreurs. Lorsqu’un enregistreur commence à mettre à jour une ligne, il prend un verrou et ne libère pas le verrou tant que la transaction n’est pas validée. Toutes les opérations de lecture sont bloquées et attendent la validation de la transaction d’écriture.

Certaines applications peuvent supposer que les lecteurs attendent toujours la validation des enregistreurs, en particulier s’il existe une synchronisation entre les deux transactions du niveau application.

Ligne directrice: Les applications ne peuvent pas s’appuyer sur le comportement de blocage. Si une application a besoin d’une synchronisation entre les transactions simultanées, une telle logique peut être implémentée dans la couche Application ou dans la couche Base de données, via sp_getapplock (Transact-SQL).
Dans les transactions qui utilisent l’isolation READ COMMITTED, chaque instruction voit la version la plus récente des lignes de la base de données. Par conséquent, les instructions suivantes voient les modifications apportées à l’état de la base de données.

Interroger une table à l’aide d’une boucle WHILE jusqu’à ce qu’une nouvelle ligne ait été trouvée est un exemple de modèle d’application qui utilise cette hypothèse. Avec chaque itération de la boucle, la requête voit les dernières mises à jour dans la base de données.

Ligne directrice: Si une application doit interroger une table optimisée en mémoire pour obtenir les lignes les plus récentes écrites dans la table, déplacez la boucle d’interrogation en dehors de l’étendue de la transaction.

Voici un exemple de modèle d’application qui utilise cette hypothèse. Interrogation d’une table à l’aide d’une boucle WHILE jusqu’à ce qu’une nouvelle ligne soit trouvée. Dans chaque itération de boucle, la requête accède aux dernières mises à jour de la base de données.

L’exemple de script suivant interroge une table t1 jusqu’à ce qu’elle ait une ligne. Il supprime ensuite une seule ligne de la table pour un traitement ultérieur.

Notez que la logique d’interrogation doit être en dehors de l’étendue de la transaction, car elle utilise l’isolation d’instantané pour accéder à la table t1. L’utilisation de la logique d’interrogation à l’intérieur de l’étendue d’une transaction créerait une transaction longue, ce qui est une mauvaise pratique.

-- poll table  
WHILE NOT EXISTS (SELECT 1 FROM dbo.t1)  
BEGIN   
  -- if empty, wait and poll again  
  WAITFOR DELAY '00:00:01'  
END  
  
BEGIN TRANSACTION  
  DECLARE @id int  
  SELECT TOP 1 @id=id FROM dbo.t1 WITH (SNAPSHOT)  
  DELETE FROM dbo.t1 WITH (SNAPSHOT) WHERE id=@id  
  
  -- insert processing based on @id  
COMMIT

Indicateurs de table de verrouillage

Les indicateurs de verrouillage (indicateurs de table (Transact-SQL)) tels que HOLDLOCK et XLOCK peuvent être utilisés avec des tables basées sur disque pour avoir SQL Server prendre plus de verrous que nécessaire pour le niveau d’isolation spécifié.

Les tables à mémoire optimisée n’utilisent pas de verrous. Des niveaux d’isolation plus élevés tels que REPEATABLE READ et SERIALIZABLE peuvent être utilisés pour déclarer les garanties souhaitées.

Les indicateurs de verrouillage ne sont pas pris en charge. Au lieu de cela, déclarez les garanties requises par le biais des niveaux d’isolation des transactions. (NOLOCK est pris en charge, car SQL Server ne prend pas de verrous sur les tables optimisées en mémoire. Notez que, contrairement aux tables sur disque, NOLOCK n’implique pas le comportement READ UNCOMMITTED pour les tables optimisées en mémoire.)

Voir aussi

Présentation des transactions sur les tables Memory-Optimized
Instructions pour la logique de nouvelle tentative pour les transactions sur les tables Memory-Optimized
Niveaux d’isolation des transactions

Last updated on 2017-03-06