• Article

Utilisation de niveaux d'isolement basés sur la gestion de la version des lignes

L'infrastructure de gestion de la version des lignes est toujours activée dans SQL Server et est utilisée par plusieurs fonctionnalités. En plus de fournir des niveaux d'isolement basés sur la gestion de la version des lignes, elle permet la prise en charge des modifications apportées aux déclencheurs et aux sessions MARS (Multiple Active Result Sets), ainsi que la prise en charge des lectures de données pour les opérations d'index ONLINE.

Les niveaux d'isolement basés sur la gestion de la version des lignes sont activés au niveau de la base de données. Toute application accédant à des objets de bases de données activées peut exécuter des requêtes en utilisant les niveaux d'isolement suivants :

  • READCOMMITTED (lu-validé) avec utilisation de la gestion de la version des lignes par l'activation de l'option de base de données READ_COMMITTED_SNAPSHOT (valeur ON) comme illustré dans l'exemple de code suivant :

    ALTER DATABASE AdventureWorks2008R2
    SET READ_COMMITTED_SNAPSHOT ON;

    Lorsque l'option READ_COMMITTED_SNAPSHOT est activée pour la base de données, toutes les requêtes s'exécutant sous le niveau d'isolement READCOMMITTED utilisent la gestion de la version des lignes, ce qui signifie que les opérations de lecture ne bloquent pas les opérations de mise à jour.

  • Niveau d'isolement d'instantané en définissant l'option de base de données ALLOW_SNAPSHOT_ISOLATION avec la valeur ON comme illustré dans l'exemple de code suivant :

    ALTER DATABASE AdventureWorks2008R2
    SET ALLOW_SNAPSHOT_ISOLATION ON;

    Une transaction s'exécutant sous le niveau d'isolement d'instantané (SNAPSHOT) peut accéder aux tables de la base de données qui ont été activées pour les instantanés. Pour accéder aux tables qui n'ont pas été activées pour les instantanés, le niveau d'isolement doit être modifié. Ainsi, dans l'exemple de code suivant, une instruction SELECT exécutée dans le cadre d'une transaction d'instantané joint deux tables. Une table appartient à une base de données dans laquelle le niveau d'isolement d'instantané (SNAPSHOT) n'est pas activé. Lorsque l'instruction SELECT s'exécute sous le niveau d'isolement d'instantané, elle échoue.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
    SELECT t1.col5, t2.col5
        FROM Table1 as t1
        INNER JOIN SecondDB.dbo.Table2 as t2
            ON t1.col1 = t2.col2;

    Dans l'exemple de code suivant, la même instruction SELECT a été modifiée pour faire passer le niveau d'isolation de la transaction à READCOMMITTED (lu-validé). Grâce à cette modification, l'exécution de l'instruction SELECT aboutit.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
    SELECT t1.col5, t2.col5
        FROM Table1 as t1
        WITH (READCOMMITTED)
        INNER JOIN SecondDB.dbo.Table2 as t2
            ON t1.col1 = t2.col2;

Pour plus d'informations sur le mode de définition du niveau d'isolement au sein d'une application, consultez Optimisation des niveaux d'isolement des transactions.

Limites liées aux transactions utilisant les niveaux d'isolement basés sur la gestion de la version des lignes

Tenez compte des limites suivantes lors de l'utilisation des niveaux d'isolement basés sur la gestion de la version des lignes :

  • READ_COMMITTED_SNAPSHOT ne peut pas être activé dans les bases de données tempdb, msdb et master.

  • Les tables temporaires globales sont stockées dans tempdb. Si une transaction d'instantané implique l'accès à des tables temporaires globales, vous devez effectuer l'une des opérations suivantes 

    • Définir l'option de base de données ALLOW_SNAPSHOT_ISOLATION sur ON (activé) dans tempdb.

    • Utilisez un indicateur d'isolement afin de modifier le niveau d'isolement pour l'instruction.

  • Les transactions d'instantané échouent dans les cas suivants :

    • Une base de données est passée en lecture seule après que la transaction d'instantané ait démarré, mais avant que celle-ci ait accédé à la base de données.

    • S'il y a eu accès à des objets de plusieurs bases de données, l'état d'une base de données a été modifié au point qu'une récupération de base de données a eu lieu après que la transaction d'instantané ait démarré, mais avant que celle-ci ait accédé à la base de données. Par exemple, la base de données a été définie sur OFFLINE puis sur ONLINE, la base de données s'est fermée automatiquement puis rouverte, ou elle s'est détachée puis rattachée.

  • Les transactions distribuées, notamment les requêtes dans les bases de données partitionnées distribuées, ne sont pas prises en charge sous le niveau d'isolement d'instantané.

  • SQL Server ne conserve pas plusieurs versions des métadonnées système. Les instructions DDL (Data Definition Language) portant sur des tables et autres objets de base de données (index, vues, types de données, procédures stockées et fonctions CLR (Common Language Runtime)) modifient les métadonnées. Si une instruction DDL modifie un objet, toute référence simultanée à l'objet sous le niveau d'isolement d'instantané entraînera l'échec de la transaction d'instantané. Les transactions READCOMMITTED (lu-validé) ne présentent pas cette limitation lorsque l'option de base de données READ_COMMITTED_SNAPSHOT est activée (valeur ON).

    Supposons, par exemple, qu'un administrateur de base de données exécute l'instruction ALTER INDEX suivante.

    USE AdventureWorks2008R2;
GO
ALTER INDEX AK_Employee_LoginID
    ON HumanResources.Employee REBUILD;
GO

    Toute transaction d'instantané qui est active au moment de l'exécution de l'instruction ALTER INDEX recevra une erreur si elle tente de faire référence à la table HumanResources.Employee après l'exécution de l'instruction ALTER INDEX. Les transactions READCOMMITTED (lu-validé) utilisant la gestion de la version des lignes ne sont pas affectées.

    Notes

    Les opérations BULK INSERT peuvent entraîner des modifications au niveau des métadonnées de la table cible (par exemple, lors de la désactivation des vérifications de contraintes). Dans ce cas, les transactions simultanées d'isolement d'instantané qui accèdent à des tables faisant l'objet d'insertion en bloc échouent.