Transacties gebruiken in een SQL-pool in Azure Synapse
Dit artikel bevat tips voor het implementeren van transacties en het ontwikkelen van oplossingen in een SQL-pool.
Wat u kunt verwachten
Zoals u zou verwachten, ondersteunt SQL-pool transacties als onderdeel van de datawarehouse-workload. Om ervoor te zorgen dat de SQL-pool op schaal wordt gehandhaafd, zijn sommige functies echter beperkt in vergelijking met SQL Server. In dit artikel worden de verschillen beschreven.
Transactie-isolatieniveaus
SQL-pool implementeert ACID-transacties. Het isolatieniveau van de transactionele ondersteuning is standaard ingesteld op READ UNCOMMITTED. U kunt deze wijzigen in READ COMMITTED SNAPSHOT ISOLATION door de optie READ_COMMITTED_SNAPSHOT database in te schakelen voor een SQL-gebruikerspool wanneer deze is verbonden met de hoofddatabase.
Zodra deze optie is ingeschakeld, worden alle transacties in deze database uitgevoerd onder READ COMMITTED SNAPSHOT ISOLATION en wordt de instelling READ UNCOMMITTED op sessieniveau niet gehonoreerd. Controleer ALTER DATABASE SET options (Transact-SQL) voor meer informatie.
Transactiegrootte
Eén transactie voor het wijzigen van gegevens is beperkt. De limiet wordt toegepast per distributie. Daarom kan de totale toewijzing worden berekend door de limiet te vermenigvuldigen met het distributieaantal.
Als u het maximum aantal rijen in de transactie wilt benaderen, deelt u de distributielimiet door de totale grootte van elke rij. Voor kolommen met variabele lengte kunt u overwegen een gemiddelde kolomlengte te nemen in plaats van de maximale grootte te gebruiken.
In de volgende tabel zijn twee veronderstellingen gemaakt:
- Er is een gelijkmatige verdeling van gegevens opgetreden
- De gemiddelde rijlengte is 250 bytes
Gen2
| DWU | Limiet per distributie (GB) | Aantal distributies | MAXIMALE transactiegrootte (GB) | Aantal rijen per distributie | Maximum aantal rijen per transactie |
|---|---|---|---|---|---|
| DW100c | 1 | 60 | 60 | 4,000,000 | 240,000,000 |
| DW200c | 1.5 | 60 | 90 | 6,000,000 | 360,000,000 |
| DW300c | 2.25 | 60 | 135 | 9,000,000 | 540,000,000 |
| DW400c | 3 | 60 | 180 | 12,000,000 | 720,000,000 |
| DW500c | 3,75 | 60 | 225 | 15,000,000 | 900,000,000 |
| DW1000c | 7,5 | 60 | 450 | 30,000,000 | 1,800,000,000 |
| DW1500c | 11.25 | 60 | 675 | 45,000,000 | 2,700,000,000 |
| DW2000c | 15 | 60 | 900 | 60,000,000 | 3,600,000,000 |
| DW2500c | 18.75 | 60 | 1125 | 75,000,000 | 4,500,000,000 |
| DW3000c | 22.5 | 60 | 1,350 | 90,000,000 | 5,400,000,000 |
| DW5000c | 37.5 | 60 | 2,250 | 150,000,000 | 9,000,000,000 |
| DW6000c | 45 | 60 | 2,700 | 180,000,000 | 10,800,000,000 |
| DW7500c | 56.25 | 60 | 3,375 | 225,000,000 | 13,500,000,000 |
| DW10000c | 75 | 60 | 4.500 | 300,000,000 | 18,000,000,000 |
| DW15000c | 112.5 | 60 | 6750 | 450,000,000 | 27,000,000,000 |
| DW30000c | 225 | 60 | 13,500 | 900,000,000 | 54,000,000,000 |
Gen1
| DWU | Limiet per distributie (GB) | Aantal distributies | MAXIMALE transactiegrootte (GB) | # Rijen per distributie | Maximum aantal rijen per transactie |
|---|---|---|---|---|---|
| DW100 | 1 | 60 | 60 | 4,000,000 | 240,000,000 |
| DW200 | 1.5 | 60 | 90 | 6,000,000 | 360,000,000 |
| DW300 | 2.25 | 60 | 135 | 9,000,000 | 540,000,000 |
| DW400 | 3 | 60 | 180 | 12,000,000 | 720,000,000 |
| DW500 | 3,75 | 60 | 225 | 15,000,000 | 900,000,000 |
| DW600 | 4.5 | 60 | 270 | 18,000,000 | 1,080,000,000 |
| DW1000 | 7,5 | 60 | 450 | 30,000,000 | 1,800,000,000 |
| DW1200 | 9 | 60 | 540 | 36,000,000 | 2,160,000,000 |
| DW1500 | 11.25 | 60 | 675 | 45,000,000 | 2,700,000,000 |
| DW2000 | 15 | 60 | 900 | 60,000,000 | 3,600,000,000 |
| DW3000 | 22.5 | 60 | 1,350 | 90,000,000 | 5,400,000,000 |
| DW6000 | 45 | 60 | 2,700 | 180,000,000 | 10,800,000,000 |
De limiet voor de transactiegrootte wordt toegepast per transactie of bewerking. Deze wordt niet toegepast op alle gelijktijdige transacties. Daarom is elke transactie toegestaan om deze hoeveelheid gegevens naar het logboek te schrijven.
Raadpleeg het artikel Best practices voor transacties om de hoeveelheid gegevens die naar het logboek worden geschreven, te optimaliseren en te minimaliseren.
Waarschuwing
De maximale transactiegrootte kan alleen worden bereikt voor HASH- of ROUND_ROBIN gedistribueerde tabellen waarbij de spreiding van de gegevens gelijkmatig is. Als de transactie gegevens op een scheve manier naar de distributies schrijft, wordt de limiet waarschijnlijk bereikt vóór de maximale transactiegrootte.
Transactiestatus
SQL-pool maakt gebruik van de functie XACT_STATE() om een mislukte transactie te rapporteren met behulp van de waarde -2. Deze waarde betekent dat de transactie is mislukt en alleen is gemarkeerd voor terugdraaien.
Notitie
Het gebruik van -2 door de functie XACT_STATE om een mislukte transactie aan te geven, vertegenwoordigt een ander gedrag dan SQL Server. SQL Server gebruikt de waarde -1 om een niet-gecommitteerde transactie aan te geven. SQL Server kan bepaalde fouten in een transactie tolereren zonder dat deze als niet-gecommitteerd hoeft te worden gemarkeerd. Zou bijvoorbeeld een fout veroorzaken, SELECT 1/0 maar een transactie niet forceren in een niet-aaneenvolgbare status.
SQL Server staat ook leesbewerkingen in de niet-aaneenbare transactie toe. U kunt dit echter niet doen met een SQL-pool. Als er een fout optreedt in een SQL-pooltransactie, krijgt deze automatisch de status -2 en kunt u geen verdere selectieinstructies maken totdat de instructie is teruggedraaid.
Daarom is het belangrijk om te controleren of uw toepassingscode gebruikmaakt van XACT_STATE(), omdat u mogelijk codewijzigingen moet aanbrengen.
In SQL Server ziet u bijvoorbeeld een transactie die er als volgt uitziet:
SET NOCOUNT ON;
DECLARE @xact_state smallint = 0;
BEGIN TRAN
BEGIN TRY
DECLARE @i INT;
SET @i = CONVERT(INT,'ABC');
END TRY
BEGIN CATCH
SET @xact_state = XACT_STATE();
SELECT ERROR_NUMBER() AS ErrNumber
, ERROR_SEVERITY() AS ErrSeverity
, ERROR_STATE() AS ErrState
, ERROR_PROCEDURE() AS ErrProcedure
, ERROR_MESSAGE() AS ErrMessage
;
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN;
PRINT 'ROLLBACK';
END
END CATCH;
IF @@TRANCOUNT >0
BEGIN
PRINT 'COMMIT';
COMMIT TRAN;
END
SELECT @xact_state AS TransactionState;
De voorgaande code geeft het volgende foutbericht:
Msg 111233, Niveau 16, Staat 1, Regel 1 111233; De huidige transactie is afgebroken en eventuele in behandeling zijnde wijzigingen zijn teruggedraaid. De oorzaak van dit probleem is dat een transactie met alleen terugdraaistatus niet expliciet wordt teruggedraaid vóór een DDL-, DML- of SELECT-instructie.
U krijgt geen uitvoer van de ERROR_*-functies.
In sql-pool moet de code enigszins worden gewijzigd:
SET NOCOUNT ON;
DECLARE @xact_state smallint = 0;
BEGIN TRAN
BEGIN TRY
DECLARE @i INT;
SET @i = CONVERT(INT,'ABC');
END TRY
BEGIN CATCH
SET @xact_state = XACT_STATE();
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN;
PRINT 'ROLLBACK';
END
SELECT ERROR_NUMBER() AS ErrNumber
, ERROR_SEVERITY() AS ErrSeverity
, ERROR_STATE() AS ErrState
, ERROR_PROCEDURE() AS ErrProcedure
, ERROR_MESSAGE() AS ErrMessage
;
END CATCH;
IF @@TRANCOUNT >0
BEGIN
PRINT 'COMMIT';
COMMIT TRAN;
END
SELECT @xact_state AS TransactionState;
Het verwachte gedrag wordt nu waargenomen. De fout in de transactie wordt beheerd en de ERROR_*-functies bieden waarden zoals verwacht.
Alles wat is gewijzigd, is dat het TERUGDRAAIEN van de transactie moest plaatsvinden voordat de foutinformatie in het CATCH-blok werd gelezen.
Error_Line() functie
Het is ook vermeldenswaardig dat SQL-pool de functie ERROR_LINE() niet implementeert of ondersteunt. Als u deze in uw code hebt, moet u deze verwijderen om te voldoen aan de SQL-pool.
Gebruik in plaats daarvan querylabels in uw code om equivalente functionaliteit te implementeren. Zie het LABEL-artikel voor meer informatie.
THROW en RAISERROR gebruiken
THROW is de modernere implementatie voor het verhogen van uitzonderingen in SQL-pool, maar RAISERROR wordt ook ondersteund. Er zijn echter een paar verschillen die de moeite waard zijn om op te letten.
- Door de gebruiker gedefinieerde foutberichten kunnen niet binnen het bereik van 100.000 - 150.000 voor THROW liggen
- RAISERROR-foutberichten zijn opgelost op 50.000
- Het gebruik van sys.messages wordt niet ondersteund
Beperkingen
SQL-pool heeft nog enkele andere beperkingen die betrekking hebben op transacties.
De verschillen zijn als volgt:
- Geen gedistribueerde transacties
- Geen geneste transacties toegestaan
- Geen opslagpunten toegestaan
- Geen benoemde transacties
- Geen gemarkeerde transacties
- Geen ondersteuning voor DDL zoals CREATE TABLE binnen een door de gebruiker gedefinieerde transactie
Volgende stappen
Zie Best practices voor transacties voor meer informatie over het optimaliseren van transacties. Zie Aanbevolen procedures voor SQL-pools voor meer informatie over andere aanbevolen procedures voor SQL-pools.