Contrôler la durabilité d'une transaction
S’applique à : 
SQL Server Azure SQL Database Azure SQL Managed Instance
Les validations de transactions SQL Server peuvent avoir une durabilité complète, la durabilité par défaut de SQL Server ou une durabilité retardée (également appelée Validation différée).
Les validations de transactions à durabilité complète sont synchrones. Elles signalent la réussite de la validation (COMMIT) et restituent le contrôle au client uniquement lorsque les enregistrements de journal de transactions ont été écrits sur le disque. Les validations de transactions à durabilité retardée sont asynchrones. Elles signalent la réussite de la validation (COMMIT) avant que les enregistrements de journal de transactions ne soient écrits sur le disque. L'écriture des entrées du journal des transactions sur le disque est nécessaire pour qu'une transaction soit durable. Les transactions à durabilité retardée deviennent durables lorsque les enregistrements de journal de transactions sont vidés sur le disque.
Cet article décrit en détail les transactions à durabilité retardée.
Durabilité complète/ retardée des transactions
La durabilité complète et la durabilité retardée des transactions présentent chacune des avantages et des inconvénients. Une application peut comporter à la fois des transactions à durabilité complète et à durabilité retardée. Vous devez évaluer soigneusement vos besoins professionnels et déterminer de quelle manière chaque méthode peut y répondre.
Durabilité complète des transactions
Les transactions à durabilité complète écrivent la sécurisation du journal des transactions sur le disque avant de restituer le contrôle au client. Vous devez utiliser des transactions à durabilité complète dans les cas suivants :
Le système ne tolère aucune perte de données. Consultez la section Quand puis-je perdre des données ? pour obtenir des informations sur la perte de certaines données.
Le goulot d'étranglement n'est pas attribuable à une latence d'écriture du journal des transactions.
La durabilité retardée des transactions réduit la latence attribuable aux E/S du journal, car elle permet de conserver les enregistrements du journal de transactions en mémoire et d'écrire ces enregistrements par lots, ce qui nécessite moins d'opérations d'E/S. La durabilité différée des transactions réduit potentiellement les contentions d'E/S du journal, et diminue de fait les temps d'attente dans le système.
Garanties offertes par la durabilité complète des transactions
Une fois qu'une transaction a été validée, les modifications apportées par la transaction sont visibles par les autres transactions du système. Pour plus d’informations sur les niveaux d’isolation des transactions, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL) ou Transactions avec des tables optimisées en mémoire.
La durabilité est garantie lors de la validation. Les enregistrements de journal correspondants sont conservés sur le disque jusqu'à ce que la transaction soit validée et que le contrôle soit restitué au client.
Durabilité retardée des transactions
La durabilité retardée des transactions est obtenue par des écritures asynchrones d'enregistrements de journal sur le disque. Les enregistrements du journal des transactions sont conservés dans une mémoire tampon et écrits sur le disque lorsque la mémoire tampon se remplit ou qu'un événement de vidage de la mémoire tampon a lieu. La durabilité retardée des transactions réduit à la fois la latence et les contentions dans le système pour les raisons suivantes :
Les validations de transactions sont effectuées sans attendre la fin des E/S du journal et la restitution du contrôle au client.
Les transactions concomitantes sont moins susceptibles d'entrer en conflit pour les E/S du journal ; à la place, le tampon journal peut être vidé sur le disque sous forme de blocs plus volumineux, ce qui réduit les risques de contention et augmente le débit.
Remarque
Vous pouvez toujours assister à une contention d'E/S du journal s'il existe un niveau élevé d'accès concurrentiel, en particulier si vous remplissez le tampon journal plus vite que vous ne le videz.
Circonstances dans lesquelles utiliser la durabilité retardée des transactions
Voici quelques-uns des cas dans lesquels vous pouvez tirer parti de l'utilisation de la durabilité retardée des transactions :
Vous tolérez la perte de certaines données.
Si vous tolérez la perte de certaines données, par exemple des enregistrements qui ne sont pas indispensables tant que vous disposez de la majeure partie des données, il peut être intéressant d'utiliser la durabilité retardée. Si, en revanche, vous ne tolérez aucune perte de données, n'utilisez pas la durabilité retardée des transactions.
Vous rencontrez un goulot d’étranglement au niveau des écritures du journal de transactions.
Si les problèmes de performances sont attribuables à une latence dans les écritures du journal de transactions, votre application bénéficiera probablement de l'utilisation de la durabilité retardée des transactions.
Vos charges de travail présentent un niveau de contention élevé.
Si votre système a des charges de travail présentant un haut degré de contention, beaucoup de temps est perdu à attendre la libération de verrous. La durabilité retardée des transactions réduit la durée de validation. De ce fait, les verrous sont libérés plus rapidement, ce qui se traduit par un débit supérieur.
Garanties offertes par la durabilité retardée des transactions
Une fois qu'une transaction a été validée, les modifications apportées par la transaction sont visibles par les autres transactions du système.
La durabilité des transactions est garantie uniquement après un vidage du journal des transactions en mémoire sur le disque. Le journal des transactions en mémoire est vidé sur le disque dans les circonstances suivantes :
Une transaction à durabilité complète dans la même base de données apporte une modification dans la base de données, puis est validée.
L'utilisateur exécute la procédure stockée système
sp_flush_logavec succès.Si une transaction à durabilité complète ou un sp_flush_log est validé avec succès, toutes les transactions à durabilité retardée déjà validées sont assurées d’être rendues durables.
SQL Server tente de vider le journal sur le disque en se basant sur la génération du journal et sur le minutage, même si toutes les transactions sont à durabilité différée. Cette opération réussit généralement si le périphérique d’E/S suit le rythme. Cependant, SQL Server ne fournit pas de garantie solide de durabilité autre que les transactions durables et sp_flush_log.
Procédure pour contrôler la durabilité d'une transaction
Contrôle au niveau de la base de données
En tant qu'administrateur de base de données, vous pouvez contrôler si les utilisateurs peuvent utiliser la durabilité retardée des transactions sur une base de données avec l'instruction suivante. Vous devez définir le paramètre de durabilité retardée avec ALTER DATABASE.
ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }
DISABLED
[Valeur par défaut] Avec ce paramètre, toutes les transactions qui sont validées sur la base de données ont une durabilité complète, quel que soit le paramètre de niveau de validation ([DELAYED_DURABILITY= ON | OFF]). Aucune modification ni recompilation de procédure stockée n'est nécessaire. Cela vous permet de vous assurer que les données ne sont jamais mises en danger par la durabilité retardée.
ALLOWED
Avec ce paramètre, la durabilité de chaque transaction est déterminée au niveau de la transaction - DELAYED_DURABILITY = { OFF | ON }. Pour plus d’informations, consultez Contrôle au niveau du bloc atomique - Procédures stockées compilées en mode natif et Contrôle au niveau de la validation.
FORCED
Avec ce paramètre, chaque transaction qui est validée sur la base de données a une durabilité retardée, même si la transaction spécifie une durabilité complète (DELAYED_DURABILITY = OFF) ou omette toute indication de durabilité. Ce paramètre est utile lorsque la durabilité retardée des transactions a un intérêt pour une base de données, mais que vous ne souhaitez pas modifier le code de l'application.
Contrôle au niveau du bloc atomique - Procédures stockées compilées en mode natif
Le code suivant s'insère à l'intérieur du bloc atomique.
DELAYED_DURABILITY = { OFF | ON }
OFF
[Valeur par défaut] La transaction a une durabilité complète, sauf si l'option de base de données DELAYED_DURABLITY = FORCED est activée, auquel cas la validation COMMIT est asynchrone et donc à durabilité retardée. Pour plus d’informations, consultez Contrôle au niveau de la base de données.
ON
La transaction a une durabilité retardée, sauf si l'option de base de données DELAYED_DURABLITY = DISABLED est activée, auquel cas la validation COMMIT est synchrone et donc à durabilité complète. Pour plus d’informations, consultez Contrôle au niveau de la base de données.
Exemple de code :
CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
DELAYED_DURABILITY = ON,
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'English'
)
/* procedure body goes here */
END
Tableau 1 : durabilité dans les blocs atomiques
| Option de durabilité de bloc atomique | Aucune transaction existante | Transaction en cours (à durabilité complète ou retardée) |
|---|---|---|
| DELAYED_DURABILITY = OFF | Le bloc atomique démarre une nouvelle transaction à durabilité complète. | Le bloc atomique crée un point d'enregistrement dans la transaction existante, puis démarre la nouvelle transaction. |
| DELAYED_DURABILITY = ON | Le bloc atomique démarre une nouvelle transaction à durabilité retardée. | Le bloc atomique crée un point d'enregistrement dans la transaction existante, puis démarre la nouvelle transaction. |
Contrôle au niveau de la validation (COMMIT) – Transact-SQL
La syntaxe de l'option COMMIT est étendue pour vous permettre de forcer la durabilité retardée des transactions. Si DELAYED_DURABILITY a la valeur DISABLED ou FORCED au niveau de la base de données (voir ci-dessus), cette option COMMIT est ignorée.
COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]
OFF
[Valeur par défaut] La validation COMMIT de la transaction a une durabilité complète, sauf si l'option de base de données DELAYED_DURABLITY = FORCED est activée, auquel cas la validation COMMIT est asynchrone et donc à durabilité retardée. Pour plus d’informations, consultez Contrôle au niveau de la base de données.
ON
La validation COMMIT de la transaction a une durabilité retardée, sauf si l'option de base de données DELAYED_DURABLITY = DISABLED est activée, auquel cas la validation COMMIT est synchrone et donc à durabilité complète. Pour plus d’informations, consultez Contrôle au niveau de la base de données.
Récapitulatif des options et de leurs interactions
Ce tableau récapitule les interactions entre les paramètres de durabilité retardée au niveau de la base de données et les paramètres au niveau de la validation. Les paramètres au niveau de la base de données ont toujours priorité sur les paramètres au niveau de la validation.
| Paramètre de validation (COMMIT)/Paramètre de base de données | DELAYED_DURABILITY = DISABLED | DELAYED_DURABILITY = ALLOWED | DELAYED_DURABILITY = FORCED |
|---|---|---|---|
| DELAYED_DURABILITY = OFF Transactions au niveau de la base de données. | La transaction a une durabilité complète. | La transaction a une durabilité complète. | La transaction a une durabilité retardée. |
| DELAYED_DURABILITY = ON Transactions au niveau de la base de données. | La transaction a une durabilité complète. | La transaction a une durabilité retardée. | La transaction a une durabilité retardée. |
| DELAYED_DURABILITY = OFF Transaction distribuée ou de bases de données croisées. | La transaction a une durabilité complète. | La transaction a une durabilité complète. | La transaction a une durabilité complète. |
| DELAYED_DURABILITY = ON Transaction distribuée ou de bases de données croisées. | La transaction a une durabilité complète. | La transaction a une durabilité complète. | La transaction a une durabilité complète. |
Procédure pour forcer le vidage du journal de transactions
Il existe deux moyens pour forcer le vidage du journal de transactions sur le disque.
Exécutez chaque transaction à durabilité complète qui modifie la même base de données. De cette façon, vous forcez le vidage sur le disque des enregistrements de journal de toutes les transactions à durabilité retardée déjà validées.
Exécutez la procédure stockée système
sp_flush_log. Cette procédure force le vidage sur le disque des enregistrements de journal de toutes les transactions à durabilité retardée déjà validées. Pour plus d’informations, consultez sys.sp_flush_log (Transact-SQL).
Durabilité retardée et autres fonctionnalités de SQL Server
La réplication transactionnelle, le Change Tracking et la capture des changements de données
Pour les bases de données activées pour la réplication transactionnelle ou la capture des données de changement (CDC), l'utilisation de la durabilité retardée n'est pas prise en charge.
Le suivi des modifications avec durabilité différée est pris en charge. Toutes les transactions avec suivi des modifications ont une durabilité complète. Une transaction présente la propriété de suivi des modifications si elle effectue des opérations d’écriture dans des tables qui sont activées pour le Change Tracking.
À compter de SQL Server 2022 CU 2 et SQL Server 2019 CU 20, vous pouvez voir :
Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability setsi vous essayez d’activer la réplication transactionnelle ou la capture des changements de données sur une base de données qui a activé la durabilité différée.Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabledsi vous essayez d’activer la durabilité différée sur une base de données configurée avec la réplication transactionnelle ou la capture des changements de données.
Récupération sur incident
La cohérence est garantie, mais certaines modifications des transactions à durabilité retardée qui ont été validées peuvent être perdues.
Bases de données croisées et DTC
Les transactions distribuées ou de bases de données croisées ont une durabilité complète, quel que soit le paramètre de validation des bases de données ou des transactions.
Groupes de disponibilité Always On et mise en miroir
Les transactions à durabilité retardée ne garantissent aucune durabilité sur le réplica principal ni sur les réplicas secondaires. En outre, elles ne garantissent aucune information sur la transaction au niveau du réplica secondaire. Après une validation (COMMIT), le contrôle est restitué au client avant qu’un accusé de réception (ACK) ne soit reçu d’un réplica synchrone secondaire. La réplication sur les réplicas secondaires se poursuit lors du vidage sur disque sur le réplica principal.
Clustering de basculement
Certaines écritures de transactions à durabilité retardée peuvent être perdues.
Azure Synapse Link pour SQL
Les transactions durables différées ne sont pas prises en charge avec Azure Synapse Link pour SQL.
Copie des journaux de transaction
Seules les transactions rendues durables sont incluses dans le journal qui est copié.
Sauvegarde de fichier journal
Seules les transactions rendues durables sont incluses dans la sauvegarde.
Quand puis-je perdre des données ?
Si vous implémentez la durabilité retardée sur l'une de vos tables, certaines circonstances peuvent entraîner une perte de données. Si vous ne pouvez pas vous permettre de perdre des données, n'utilisez pas la durabilité retardée sur les tables.
Événements graves
Dans le cas d'événements graves, par exemple une défaillance du serveur, vous pouvez perdre les données de toutes les transactions validées qui n'ont pas été enregistrées sur le disque. Les transactions durables retardées sont enregistrées sur le disque quand une transaction à durabilité complète est exécutée sur une table (durable optimisé en mémoire ou sur disque) dans la base de données, ou quand sp_flush_log est appelé. Si vous utilisez des transactions durables retardées, vous pouvez créer une petite table dans la base de données afin de mettre à jour ou d'appeler périodiquement sp_flush_log pour enregistrer toutes les transactions validées en attente. Le journal des transactions est également vidé quand il est plein, mais cela est difficile à prévoir et impossible à contrôler.
Arrêt et démarrage de SQL Server
Pour une durabilité retardée, il n'y a pas de différence entre un arrêt inattendu et un arrêt/redémarrage attendu de SQL Server. Comme pour les événements graves, vous devez prévoir une perte de données. Dans un arrêt/redémarrage planifié, certaines transactions qui n’ont pas été écrites sur disque avant l’arrêt peuvent tout d’abord l’être, mais vous ne pouvez pas planifier cela. La planification comme pour un arrêt/redémarrage, planifiée ou non, entraîne la perte de données comme pour un événement grave.