Transactions dans Fabric Data Warehouse

S'applique à :✅ point de terminaison d'analytique SQL et entrepôt dans Microsoft Fabric

À l’instar de leur comportement dans SQL Server, les transactions vous permettent de contrôler la validation ou la restauration des requêtes de lecture et d’écriture.

Fabric Data Warehouse prend en charge les transactions conformes à ACID. Chaque transaction est atomique, cohérente, isolée et durable (ACID). Toutes les opérations au sein d’une seule transaction sont traitées atomiquement, toutes ayant réussi ou échoué. Si une instruction dans la transaction échoue, la transaction entière est annulée.

Transactions explicites

Vous pouvez modifier les données stockées dans des tables d’un entrepôt à l’aide de transactions explicites pour regrouper les modifications.

Par exemple, vous pouvez committer des insertions dans plusieurs tables, ou aucune des tables si une erreur se produit. Si vous modifiez les détails d’un bon de commande qui affecte trois tables, vous pouvez regrouper ces modifications en une seule transaction. Cela signifie que lorsque ces tables sont interrogées, soit elles incorporent toutes les modifications, soit aucune ne le fait. Les transactions sont une pratique courante lorsque vous devez vous assurer que vos données sont cohérentes entre plusieurs tables.

Vous pouvez utiliser des mécanismes de contrôle de syntaxe T-SQL standard (BEGIN TRAN, COMMIT TRANet ROLLBACK TRAN) pour les transactions explicites. Pour plus d’informations, consultez : - BEGIN TRANSACTION - ROLLBACK TRANSACTION -

Par exemple, Fabric Data Warehouse traite ces modifications de schéma en tant qu’unité atomique unique :

-- Sample Syntax--- 
BEGIN TRAN; 
ALTER TABLE <table_name> ADD <column_name> <type>; 
ALTER TABLE <table_name> DROP COLUMN <column_name>; 
COMMIT; 

Si une instruction dans la transaction échoue, toutes les modifications de schéma sont automatiquement restaurées.

Fabric Data Warehouse prend en charge l’exécution des éléments suivants à l’intérieur d’une transaction explicite :

• CREATE TABLE
• DROP TABLE
• TRUNCATE TABLE
• CTAS
• sp_rename
• ALTER TABLE ajouter des colonnes nullables
• ALTER TABLE supprimer des colonnes
• ALTER TABLE ajouter ou supprimer PRIMARY KEY, UNIQUEet FOREIGN KEY contraintes avec le NOT ENFORCED mot clé
• Déclarations multiples ALTER TABLE
• ALTER TABLE sur les tables temporaires distribuées

Prise en charge des transactions de requête inter-bases de données

L'entrepôt dans Microsoft Fabric prend en charge les transactions qui s'étendent sur les entrepôts situés dans le même espace de travail, y compris la lecture à partir du point de terminaison analytique SQL du Lakehouse. Pour obtenir un exemple, consultez Écrire une requête SQL inter-bases de données.

Comprendre le verrouillage et le blocage dans Fabric Data Warehouse

Fabric Data Warehouse utilise le verrouillage au niveau de la table, que la requête touche une ligne ou plusieurs. Le tableau suivant fournit la liste des verrous utilisés pour différentes opérations T-SQL.

Vous pouvez interroger les verrous actuellement détenus avec la vue de gestion dynamique (DMV) sys.dm_tran_locks.

Pour plus d’informations sur les verrous, l’escalade de verrous et la compatibilité des verrous, consultez le guide de verrouillage des transactions et de versionnage des lignes.

Isolation des instantanés

Fabric Data Warehouse applique l’isolation des instantanés sur toutes les transactions. L’isolation d’instantané est un niveau d’isolation basé sur les lignes qui assure une cohérence des données au niveau des transactions, et utilise les versions de lignes stockées dans tempdb pour sélectionner les lignes à mettre à jour. La transaction utilise les versions de ligne de données qui existent au début de la transaction. Cela garantit que chaque transaction fonctionne sur un instantané cohérent des données telles qu’elles existaient au début de la transaction.

Dans l’isolation des instantanés, les requêtes de la transaction voient la même version ou l’instantané, en fonction de l’état de la base de données au début de la transaction. Dans l’isolation des instantanés, les transactions qui modifient les données ne bloquent pas les transactions qui lisent les données et les transactions qui lisent les données ne bloquent pas les transactions qui écrivent des données. Ce comportement optimiste et non bloquant réduit considérablement la probabilité d’interblocages pour les transactions complexes.

Si vous utilisez T-SQL pour modifier votre niveau d’isolation, la modification est ignorée au moment de l’exécution de la requête et l’isolation d’instantané est appliquée.

Dans l’isolation des instantanés, les conflits d’écriture-écriture ou de mise à jour sont possibles, pour plus d’informations, consultez Comprendre les conflits d’écriture-écriture dans Fabric Data Warehouse.

Verrous de schéma

Les verrous de schéma empêchent les conflits sur les instructions DDL, telles que le schéma d’une table modifié pendant la mise à jour des lignes dans une transaction. N’oubliez pas que les opérations DDL, telles que les modifications de schéma et les migrations, peuvent bloquer ou être bloquées par des charges de travail de lecture actives.

• Pendant les opérations DDL (Data Definition Language), la Moteur de base de données utilise des verrous de modification de schéma (Sch-M). Pendant le temps qu’il est conservé, le Sch-M verrou empêche tout accès simultané à la table jusqu’à ce que le verrou soit libéré.
• Pendant les opérations DML (Data Manipulation Language), le Moteur de base de données utilise des verrous de stabilité de schéma (Sch-S). Les opérations qui acquièrent Sch-M sont bloquées par les Sch-S verrous. D’autres transactions continuent d’être exécutées pendant la compilation d’une requête, mais les opérations DDL sont bloquées jusqu’à ce qu’elles puissent obtenir un accès exclusif au schéma.
• Les opérations DDL acquièrent également un verrou exclusif (X) sur des lignes dans des vues système comme sys.tables et sys.objects associées à la table cible, pendant la durée de la transaction. Cela bloque les instructions simultanées SELECT sur sys.tables et sys.objects.

Meilleures pratiques pour éviter le blocage

• Évitez les transactions longues ou planifiez pendant des périodes de faible ou aucune activité simultanée.
• Planifiez les opérations DDL uniquement pendant les fenêtres de maintenance pour réduire le blocage.
• Bien que les instructions DDL puissent être exécutées à l’intérieur de transactions utilisateur explicites (BEGIN TRAN), elles doivent être utilisées avec prudence dans les charges de travail simultanées. En raison du comportement de verrouillage, DDL au sein d’une transaction peut bloquer les opérations DML ou SELECT simultanées sur les tables affectées, ainsi que les requêtes SELECT sur les vues de catalogue système telles sys.tables ou sys.objects. Pour surveiller et résoudre les conflits de verrou potentiels, utilisez sys.dm_tran_locks.
• Surveillez les verrous et les conflits dans l’entrepôt.
  • Utilisez sys.dm_tran_locks pour inspecter les verrous actuels.

Comprendre les conflits d’écriture-écriture dans Fabric Data Warehouse

Les conflits d’écriture-écriture peuvent se produire lorsque deux transactions tentent de UPDATE, DELETE, MERGE ou TRUNCATE la même table.

Les conflits d’écriture-écriture ou les conflits de mise à jour sont possibles au niveau de la table, car Fabric Data Warehouse utilise le verrouillage au niveau de la table. Si deux transactions tentent de modifier des lignes différentes dans la même table, elles peuvent toujours entrer en conflit.

Les conflits d'écriture proviennent principalement de deux scénarios :

• Conflits de charge de travail induits par l’utilisateur
  • Plusieurs utilisateurs ou processus modifient simultanément la même table.
  • Peut se produire dans des pipelines ETL, des mises à jour par lots ou des transactions qui se chevauchent.
• Conflits induits par le système
  • Les tâches système en arrière-plan, telles que le compactage automatique des données, réécrivent les fichiers de mauvaise qualité.
  • Ceux-ci peuvent entrer en conflit avec les transactions utilisateur, bien que la préemption de compactage des données empêche activement les conflits d’écriture-écriture de ce type.

Si un conflit d’écriture-écriture se produit, vous pouvez voir des messages d’erreur tels que :

• Erreur 24556 : Transaction d’isolation d’instantané abandonnée en raison d’un conflit de mise à jour. L'utilisation de l'isolation par instantané pour accéder à la table '%.*ls' directement ou indirectement dans la base de données '%.*ls' peut provoquer des conflits de mise à jour si des lignes de cette table ont été supprimées ou mises à jour par une transaction concurrente. Réexécutez la transaction.
• Erreur 24706 : Transaction d’isolation d’instantané abandonnée en raison d’un conflit de mise à jour. Vous ne pouvez pas utiliser l’isolation instantanée pour accéder, directement ou indirectement, à la table '%.*ls' dans la base de données '%.*ls' afin de mettre à jour, supprimer ou insérer une ligne qui a été modifiée ou supprimée par une autre transaction. Réessayez la transaction.

Si vous rencontrez ces messages d’erreur, une ou plusieurs transactions ont réussi et une ou plusieurs transactions en conflit ont échoué. Réessayez les transactions qui ont échoué.

Meilleures pratiques pour éviter les conflits d'écriture simultanée

Pour éviter les conflits de double écriture :

• Évitez les opérations simultanées UPDATE, DELETE, MERGE sur la même table.
  • Portez une attention particulière à UPDATE, DELETE opérations MERGE au sein des transactions à plusieurs étapes.
• Utilisez la logique de nouvelle tentative dans toutes les applications et requêtes.
  • Implémentez la logique de nouvelle tentative dans les procédures stockées et les pipelines ETL.
  • Ajoutez une logique de nouvelle tentative avec un délai dans les pipelines ou les applications pour gérer les conflits temporaires.
    • Utilisez la stratégie de reculement exponentiel pour éviter les tempêtes de réessais qui aggravent les interruptions transitoires du réseau. Pour plus d’informations, consultez le modèle de réessai.
• Les conflits d’écriture/écriture avec le service de compactage de données en arrière-plan Fabric Data Warehouse sont possibles, mais sont généralement empêchés par la fonctionnalité de préemption de compactage de données Data.

Blocage des fichiers Table et Parquet

Les conflits de deux transactions simultanées ou plus qui mettent à jour une ou plusieurs lignes d’une table sont évalués à la fin de la transaction. La première transaction à valider se termine correctement et les autres transactions sont annulées en renvoyant une erreur. Ces conflits sont évalués au niveau de la table et non au niveau du fichier parquet individuel.

Les instructions INSERT créent toujours de nouveaux fichiers parquet, ce qui signifie moins de conflits avec d’autres transactions, à l’exception de DDL, car le schéma de la table peut être modifié.

Limites

• Les transactions distribuées ne sont pas prises en charge, par exemple BEGIN DISTRIBUTED TRANSACTION.
• Les points de sauvegarde ne sont pas pris en charge.
• Les transactions nommées ne sont pas prises en charge.
• Les transactions marquées ne sont pas prises en charge.
• À l’heure actuelle, les fonctionnalités T-SQL sont limitées dans l’entrepôt. Consultez la zone de surface T-SQL dans Fabric Data Warehouse pour obtenir la liste des commandes T-SQL qui ne sont actuellement pas disponibles.
• Si une transaction comporte une insertion de données dans une table vide et émet un SELECT avant la restauration, les statistiques générées automatiquement peuvent toujours refléter les données non validées, ce qui entraîne des statistiques inexactes. Des statistiques inexactes peuvent entraîner des plans de requête et des temps d’exécution non optimisés. Si vous restaurez une transaction avec des SELECT après un INSERT volumineux, mettez à jour les statistiques pour les colonnes mentionnées dans votre SELECT.

Contenu connexe

• Interrogez le point de terminaison de l'analyse SQL ou Warehouse avec Microsoft Fabric
• Tables