Transactions with Memory-Optimized Tables

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Cet article décrit tous les aspects des transactions propres aux tables optimisées en mémoire et aux procédures stockées compilées en mode natif.

Les niveaux d’isolation de la transaction dans SQL Server s’appliquent différemment aux tables optimisées en mémoire et aux tables sur disque. En outre, les mécanismes sous-jacents sont différents. La compréhension de ces différences permet au programmeur de concevoir un système à débit élevé. L’objectif de l’intégrité de la transaction est partagé dans tous les cas.

Pour connaître les conditions d’erreur spécifiques aux transactions dans les tables optimisées en mémoire, passez à la section Détection des conflits et logique des nouvelles tentatives.

Pour des informations générales, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Approches pessimiste et optimiste

Les différences fonctionnelles sont dues à la différence entre les approches pessimiste et optimiste pour l’intégrité de la transaction. Les tables optimisées en mémoire utilisent l’approche optimiste :

• L’approche pessimiste utilise des verrous pour bloquer les conflits potentiels avant qu’ils surviennent. Les verrous sont appliqués lorsque l’instruction est exécutée et ouverts lorsque la transaction est validée.

• L’approche optimiste détecte les conflits à mesure qu’ils se produisent et effectue les vérifications au moment de la validation.

  • Erreur 1205, un interblocage ne peut pas se produire pour une table optimisée en mémoire.

L’approche optimiste est moins lourde et souvent plus efficace, en partie parce que les conflits de transaction sont rares dans la plupart des applications. La principale différence d’ordre fonctionnel entre ces deux approches se situe au niveau des conflits. Avec l’approche pessimiste, vous attendez, alors qu’avec l’approche optimiste, l’une des transactions échoue et doit être retentée par le client. Les différences fonctionnelles sont plus grandes lorsque le niveau d’isolation REPEATABLE READ est appliqué et sont encore plus importantes pour le niveau SERIALIZABLE.

Modes d’initiation de la transaction

SQL Server dispose des modes suivants pour l’initiation de la transaction :

• Validation automatique : le début d’une requête simple ou d’une instruction DML ouvre implicitement une transaction, et la fin d’une instruction valide implicitement la transaction. Validation automatique est la valeur par défaut.

  • En mode Validation automatique, vous n’êtes généralement pas obligé de coder un indicateur de table à propos du niveau d’isolation de la transaction sur la table optimisée en mémoire dans la clause FROM.

• Explicite : votre instruction Transact-SQL contient le code BEGIN TRANSACTION, et éventuellement COMMIT TRANSACTION. Plusieurs instructions peuvent être rassemblées dans une même transaction.

  • En mode Explicite, vous devez utiliser l’option de base de données MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ou coder un indicateur de table à propos du niveau d’isolation de la transaction sur la table optimisée en mémoire dans la clause FROM.

• Implicite : lorsque SET IMPLICIT_TRANSACTION ON est appliqué. Le nom IMPLICIT_BEGIN_TRANSACTION aurait sans doute été plus approprié, car cette option ne fait qu’appliquer implicitement l’équivalent d’une instruction BEGIN TRANSACTION explicite avant chaque instruction UPDATE si 0 = @@trancount. Par conséquent, c’est à votre code T-SQL d’émettre finalement une instruction COMMIT TRANSACTION explicite.

• Bloc ATOMIC : toutes les instructions des blocs ATOMIC s’exécutent toujours dans le cadre d’une seule transaction. Les actions du bloc ATOMIC sont validées comme un tout en cas de réussite, ou les actions sont toutes annulées quand une défaillance se produit. Chaque procédure stockée compilée en mode natif nécessite un bloc ATOMIC.

Exemple de code avec le mode Explicite

Le script Transact-SQL interprété suivant utilise :

• une transaction explicite ;
• une table optimisée en mémoire, nommée dbo.Order_mo ;
• le contexte de niveau d’isolation de la transaction READ COMMITTED.

Par conséquent, il est nécessaire d’avoir un indicateur de table sur la table optimisée en mémoire. L’indicateur doit être pour SNAPSHOT ou un niveau d’isolation supérieur. Dans le cas de l’exemple de code, l’indicateur est WITH (SNAPSHOT). Si cet indicateur est supprimé, le script rencontre une erreur 41368, pour laquelle une nouvelle tentative automatique n’est pas adaptée :

Erreur 41368

L'accès aux tables optimisées en mémoire selon le niveau d'isolement READ COMMITTED est pris en charge uniquement pour les transactions validées automatiquement. Cela n'est pas pris en charge pour les transactions explicites ou implicites. Spécifiez un niveau d’isolation pris en charge pour la table optimisée en mémoire à l’aide d’un indicateur de table, comme WITH (SNAPSHOT).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  

BEGIN TRANSACTION;  -- Explicit transaction.  

-- Order_mo  is a memory-optimized table.  
SELECT * FROM  
           dbo.Order_mo  as o  WITH (SNAPSHOT)  -- Table hint.  
      JOIN dbo.Customer  as c  on c.CustomerId = o.CustomerId;  
COMMIT TRANSACTION;

L’utilisation de l’indicateur WITH (SNAPSHOT) peut être évitée grâce à l’utilisation de l’option de base de données MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. Lorsque cette option a la valeur ON, si une table optimisée en mémoire dispose d’un niveau d’isolation bas, celui-ci est élevé automatiquement au niveau d’isolation SNAPSHOT.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

Contrôle de version de ligne

Les tables optimisées en mémoire utilisent un système de contrôle de version de ligne très sophistiqué qui rend l’approche optimiste efficace, même au niveau d’isolation le plus strict de SERIALIZABLE. Pour en savoir plus, consultez Introduction aux tables optimisées en mémoire.

Les tables sur disque disposent indirectement d’un système de contrôle de version de ligne lorsque le niveau d’isolation READ_COMMITTED_SNAPSHOT ou SNAPSHOT est appliqué. Ce système est basé sur tempdb, alors que les structures de données optimisées en mémoire ont un contrôle de version de ligne intégré pour une efficacité maximale.

Niveaux d’isolation

La table suivante répertorie les niveaux d’isolation des transactions possibles, du plus faible au plus élevé. Pour plus d’informations sur les conflits qui peuvent se produire et la logique de nouvelles tentatives permettant de résoudre ces conflits, consultez Détection des conflits et logique des nouvelles tentatives.

Durée de vie et phases de la transaction

Quand une table optimisée en mémoire est impliquée, la durée de vie d’une transaction passe par les phases décrites dans l’image suivante :

Voici une description des phases.

Traitement normal : phase 1 (sur 3)

• Cette phase se compose de l’exécution de toutes les requêtes et des instructions DML de la requête.
• Pendant cette phase, les instructions voient la version des tables optimisées en mémoire comme l’heure de début logique de la transaction.

Validation : phase 2 (sur 3)

• La phase de validation commence par l’assignation d’une heure de fin et donc le marquage d’une transaction comme logiquement terminée. Ceci rend toutes les modifications de la transaction visibles aux autres transactions qui dépendent de cette transaction. La validation des transactions dépendantes n’est pas autorisée tant que la validation de cette transaction n’a pas réussi. En outre, les transactions qui ont ces dépendances ne sont pas autorisées à retourner des jeux de résultats au client. De cette manière, le client voit seulement les données dont la validation a réussi dans la base de données.
• Cette phase comprend les validations REPEATABLE READ et SERIALIZABLE. La validation REPEATABLE READ vérifie si des lignes lues par la transaction ont été mises à jour entre-temps. La validation SERIALIZABLE vérifie si une ligne a été insérée dans une plage de données analysée par cette transaction. Selon le tableau de la section Niveaux d’isolation et conflits, les validations REPEATABLE READ et SERIALIZABLE peuvent avoir lieu quand vous utilisez l’isolation SNAPSHOT pour valider la cohérence des contraintes de clés étrangères et uniques.

Traitement de validation : phase 3 (sur 3)

• Durant la phase de validation, les modifications apportées aux tables durables sont écrites dans le journal, et le journal est écrit sur le disque. Le contrôle est ensuite rendu au client.
• Une fois le traitement de validation terminé, toutes les transactions dépendantes sont informées qu’elles peuvent être validées.

Comme toujours, nous vous recommandons d’utiliser des unités transactionnelles de travail aussi courtes et minimales que le permettent vos besoins en données.

Détection des conflits et logique des nouvelles tentatives

Il existe deux types de conditions d’erreur qui peuvent causer l’échec et l’annulation d’une transaction. Dans la plupart des cas, lorsqu’un tel échec se produit, la transaction doit être retentée, comme dans le cas d’un blocage.

• Conflits entre plusieurs transactions simultanées. Il s’agit là de conflits de mise à jour et d’échecs de validation qui peuvent être dus à une violation du niveau d’isolation des transactions ou à une violation des contraintes.
• Échecs de dépendance. Ceux-ci sont causés par l’échec de la validation de transactions dont vous dépendez ou par un nombre excessif de dépendances.

Voici les conditions d’erreur qui peuvent entraîner l’échec des transactions quand elles accèdent à des tables optimisées en mémoire.

Logique de nouvelle tentative

Lorsqu’une transaction échoue en raison de l’une des conditions mentionnées ci-dessus, la transaction doit être retentée.

La logique des nouvelles tentatives peut être implémentée côté client ou côté serveur. La recommandation générale consiste à implémenter la logique des nouvelles tentatives côté client, car cela est plus efficace et vous permet d’accéder aux jeux de résultats retournés par la transaction avant que la défaillance ne se produise.

Exemple de code T-SQL de nouvelle tentative

Une logique de nouvelle tentative côté serveur utilisant T-SQL doit être utilisée seulement pour des transactions qui ne retournent pas de jeux de résultats au client. Dans le cas contraire, les nouvelles tentatives peuvent engendrer des jeux de résultats supplémentaires s’ajoutant à ceux qu’il était prévu de retourner au client.

Le script T-SQL interprété suivant illustre l’apparence de la logique de nouvelle tentative pour les erreurs associées à des conflits de transactions impliquant des tables optimisées en mémoire.

-- Retry logic, in Transact-SQL.
DROP PROCEDURE If Exists usp_update_salesorder_dates;
GO

CREATE PROCEDURE usp_update_salesorder_dates
AS
BEGIN
    DECLARE @retry INT = 10;

    WHILE (@retry > 0)
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
                set OrderDate = GetUtcDate()
                where CustomerId = 42;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
                set OrderDate = GetUtcDate()
                where CustomerId = 43;

            COMMIT TRANSACTION;

            SET @retry = 0;  -- //Stops the loop.
        END TRY

        BEGIN CATCH
            SET @retry -= 1;

            IF (@retry > 0 AND
                ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41823, 41840, 41839, 1205)
                )
            BEGIN
                IF XACT_STATE() = -1
                    ROLLBACK TRANSACTION;

                WAITFOR DELAY '00:00:00.001';
            END
            ELSE
            BEGIN
                PRINT 'Suffered an error for which Retry is inappropriate.';
                THROW;
            END
        END CATCH

    END -- //While loop
END;
GO

--  EXECUTE usp_update_salesorder_dates;

Transaction entre conteneurs

Une transaction est appelée transaction entre conteneurs si :

• elle accède à une table optimisée en mémoire à partir du code Transact-SQL interprété ; ou
• Exécute une procédure native alors qu’une transaction est déjà ouverte (XACT_STATE() = 1).

Le qualificatif « entre conteneurs » signifie que la transaction s’exécute sur les deux conteneurs de gestion de transactions, celui pour les tables sur disque et celui pour les tables optimisées en mémoire.

Dans une transaction entre conteneurs, différents niveaux d’isolation permettent d’accéder aux tables sur disque et optimisées en mémoire. Cette différence est exprimée par le biais des indicateurs de table explicites comme WITH (SERIALIZABLE) ou l’option de base de données MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, qui élève implicitement le niveau d’isolation de la table optimisée en mémoire vers le niveau SNAPSHOT si TRANSACTION ISOLATION LEVEL est configuré en tant que READ COMMITTED ou READ UNCOMMITTED.

Dans l’exemple de code Transact-SQL suivant :

• L’accès à la table sur disque (Table_D1) se fait à l’aide du niveau d’isolation READ COMMITTED.
• L’accès à la table optimisée en mémoire (Table_MO7) se fait à l’aide du niveau d’isolation SERIALIZABLE. La Table_MO6 n’a pas de niveau d’isolation spécifique, dans la mesure où les insertions sont toujours cohérentes et sont exécutées essentiellement avec un niveau d’isolation SERIALIZABLE.

-- Different isolation levels for
-- disk-based tables versus memory-optimized tables,
-- within one explicit transaction.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
go

BEGIN TRANSACTION;

    -- Table_D1 is a traditional disk-based table, accessed using READ COMMITTED isolation.

    SELECT * FROM Table_D1;


    -- Table_MO6 and Table_MO7 are memory-optimized tables.
    -- Table_MO7 is accessed using SERIALIZABLE isolation,
    --   while Table_MO6 does not have a specific isolation level.

    INSERT Table_MO6
        SELECT * FROM Table_MO7 WITH (SERIALIZABLE);

COMMIT TRANSACTION;
go

Limites

• Les transactions entre bases de données ne sont pas prises en charge avec les tables optimisées en mémoire. Si une transaction accède à une table optimisée en mémoire, elle ne peut pas accéder à une autre base de données, à l’exception de :

  • base de données tempdb
  • lecture seule à partir de la base de données MASTER.

• Transactions distribuées non prises en charge : Lorsque BEGIN DISTRIBUTED TRANSACTION est utilisé, la transaction ne peut pas accéder à une table optimisée en mémoire.

Procédures stockées compilées en mode natif

• Dans une procédure native, le bloc ATOMIQUE doit déclarer le niveau d’isolation de la transaction pour l’ensemble du bloc, par exemple :

  • ... BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) ...

• Les instructions de contrôle de transaction explicite ne sont pas autorisées dans le corps d’une procédure native. Les instructions BEGIN TRANSACTION, ROLLBACK TRANSACTION, etc. sont toutes interdites.

• Pour plus d’informations sur le contrôle des transactions à l’aide de blocs ATOMIC, consultez Blocs ATOMIC

Autres liens de transaction

• SET IMPLICIT_TRANSACTIONS

• sp_getapplock (Transact-SQL)

• Niveaux d'isolation basés sur le contrôle de version de ligne dans le moteur de base de données

• Contrôler la durabilité d'une transaction