NIVEAU VAN TRANSACTIEISOLATIE INSTELLEN (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-database in Microsoft Fabric Preview

Bepaalt het vergrendelings- en rijversiebeheergedrag van Transact-SQL instructies die zijn uitgegeven door een verbinding met SQL Server.

Transact-SQL syntaxis-conventies

Syntaxis

Syntaxis voor SQL Server, Azure SQL Database en SQL-database in Microsoft Fabric Preview.

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Syntaxis voor Azure Synapse Analytics en Parallel Data Warehouse.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Arguments

NIET-VERZONDEN LEZEN

Hiermee geeft u op dat instructies rijen kunnen lezen die zijn gewijzigd door andere transacties, maar nog niet zijn doorgevoerd.

Transacties die op het READ UNCOMMITTED niveau worden uitgevoerd, geven geen gedeelde vergrendelingen uit om te voorkomen dat andere transacties gegevens wijzigen die door de huidige transactie worden gelezen. READ UNCOMMITTED transacties worden ook niet geblokkeerd door exclusieve vergrendelingen die verhinderen dat de huidige transactie rijen leest die zijn gewijzigd, maar die niet door andere transacties zijn doorgevoerd. Wanneer deze optie is ingesteld, is het mogelijk om niet-doorgevoerde wijzigingen te lezen, die vuile leesbewerkingen worden genoemd. Waarden in de gegevens kunnen worden gewijzigd en rijen kunnen worden weergegeven of verdwijnen in de gegevensset vóór het einde van de transactie. Deze optie heeft hetzelfde effect als het instellen NOLOCK van alle tabellen in alle SELECT instructies in een transactie. Dit is de minst beperkende van de isolatieniveaus.

In SQL Server kunt u ook vergrendelingsconflicten minimaliseren terwijl u transacties beveiligt tegen vuile leesbewerkingen van niet-doorgevoerde gegevenswijzigingen met behulp van:

• Het READ COMMITTED isolatieniveau waarop de READ_COMMITTED_SNAPSHOT databaseoptie is ingesteld op ON.

• Het SNAPSHOT isolatieniveau. Zie Momentopname-isolatie in SQL Server voor meer informatie over isolatie van momentopnamen.

VASTGELEGD LEZEN

Hiermee geeft u op dat instructies geen gegevens kunnen lezen die zijn gewijzigd, maar niet zijn doorgevoerd door andere transacties. Dit voorkomt vuile leesbewerkingen. Gegevens kunnen worden gewijzigd door andere transacties tussen afzonderlijke instructies binnen de huidige transactie, wat resulteert in niet-herpeatbare lees- of fantoomgegevens. Deze optie is de standaardinstelling voor SQL Server.

Het gedrag is afhankelijk van READ COMMITTED de instelling van de READ_COMMITTED_SNAPSHOT databaseoptie:

• Als READ_COMMITTED_SNAPSHOT deze optie is ingesteld OFF op (de standaardinstelling voor SQL Server), gebruikt de database-engine gedeelde vergrendelingen om te voorkomen dat andere transacties rijen wijzigen terwijl de huidige transactie een leesbewerking uitvoert. De gedeelde vergrendelingen blokkeren ook dat de instructie rijen leest die door andere transacties zijn gewijzigd totdat de andere transactie is voltooid. Het type gedeelde vergrendeling bepaalt wanneer het wordt vrijgegeven. Rijvergrendelingen worden vrijgegeven voordat de volgende rij wordt verwerkt. Paginavergrendelingen worden vrijgegeven wanneer de volgende pagina wordt gelezen en tabelvergrendelingen worden vrijgegeven wanneer de instructie is voltooid.

• Als READ_COMMITTED_SNAPSHOT dit is ingesteld ON, gebruikt de database-engine rijversiebeheer om elke instructie te presenteren met een transactioneel consistente momentopname van de gegevens zoals deze aan het begin van de instructie bestonden. Vergrendelingen worden niet gebruikt om de gegevens te beschermen tegen updates door andere transacties.

  • READ_COMMITTED_SNAPSHOT ON is de standaardinstelling voor Azure SQL Database en SQL Database in Microsoft Fabric Preview.

Isolatie van momentopnamen ondersteunt FILESTREAM-gegevens. In de isolatiemodus voor momentopnamen zijn FILESTREAM-gegevens die worden gelezen door een instructie in een transactie de transactioneel consistente versie van de gegevens die aan het begin van de transactie bestonden.

Wanneer de READ_COMMITTED_SNAPSHOT databaseoptie is ON, kunt u de READCOMMITTEDLOCK tabelhint gebruiken om gedeelde vergrendeling aan te vragen in plaats van rijversiebeheer voor afzonderlijke instructies in transacties die op isolatieniveau READ COMMITTED worden uitgevoerd.

HERHAALBARE LEESBEWERKING

Hiermee geeft u op dat instructies geen gegevens kunnen lezen die zijn gewijzigd maar nog niet zijn doorgevoerd door andere transacties en dat er geen andere transacties gegevens kunnen wijzigen die door de huidige transactie zijn gelezen totdat de huidige transactie is voltooid.

Gedeelde vergrendelingen worden op alle gegevens geplaatst die door elke instructie in de transactie worden gelezen en worden bewaard totdat de transactie is voltooid. Hiermee voorkomt u dat andere transacties rijen wijzigen die zijn gelezen door de huidige transactie. Andere transacties kunnen nieuwe rijen invoegen die overeenkomen met de zoekvoorwaarden van instructies die zijn uitgegeven door de huidige transactie. Als de huidige transactie de instructie opnieuw probeert uit te voeren, worden de nieuwe rijen opgehaald, wat resulteert in fantoomleesbewerkingen. Omdat gedeelde vergrendelingen aan het einde van een transactie worden gehouden in plaats van aan het einde van elke instructie te worden vrijgegeven, is gelijktijdigheid lager dan het standaardisolatieniveau READ COMMITTED . Gebruik deze optie alleen wanneer dat nodig is.

MOMENTOPNAME

Hiermee geeft u op dat gegevens die worden gelezen door een instructie in een transactie de transactioneel consistente versie is van de gegevens die aan het begin van de transactie bestonden. De transactie kan alleen gegevenswijzigingen herkennen die vóór het begin van de transactie zijn doorgevoerd. Gegevenswijzigingen die door andere transacties na het begin van de huidige transactie zijn aangebracht, zijn niet zichtbaar voor instructies die worden uitgevoerd in de huidige transactie. Het effect is alsof de instructies in een transactie een momentopname krijgen van de vastgelegde gegevens zoals deze bestonden aan het begin van de transactie.

Behalve wanneer een database wordt hersteld, SNAPSHOT vragen transacties geen vergrendelingen aan bij het lezen van gegevens. SNAPSHOT transacties die gegevens lezen, blokkeren niet dat andere transacties gegevens schrijven. Transacties die gegevens schrijven, blokkeren SNAPSHOT transacties niet om gegevens te lezen.

Tijdens de terugdraaifase van een databaseherstel SNAPSHOT vragen transacties een vergrendeling aan als er een poging wordt gedaan om gegevens te lezen die zijn vergrendeld door een andere transactie die wordt teruggedraaid. De SNAPSHOT transactie wordt geblokkeerd totdat die transactie wordt teruggedraaid. De vergrendeling wordt onmiddellijk vrijgegeven nadat deze is verleend.

De ALLOW_SNAPSHOT_ISOLATION databaseoptie moet worden ingesteld op ON voordat u een transactie kunt starten die gebruikmaakt van het SNAPSHOT isolatieniveau. Als een transactie die gebruikmaakt van het SNAPSHOT isolatieniveau toegang heeft tot gegevens in meerdere databases, ALLOW_SNAPSHOT_ISOLATION moet deze worden ingesteld ON op elke database.

Een transactie kan niet worden ingesteld op SNAPSHOT isolatieniveau dat is gestart met een ander isolatieniveau. Hierdoor wordt de transactie afgebroken. Als een transactie in het SNAPSHOT isolatieniveau begint, kunt u deze wijzigen in een ander isolatieniveau en vervolgens terug naar SNAPSHOT. Een transactie start de eerste keer dat deze gegevens opent.

Een transactie die wordt uitgevoerd onder SNAPSHOT isolatieniveau, kan wijzigingen bekijken die door die transactie zijn aangebracht. Als de transactie bijvoorbeeld een UPDATE op een tabel uitvoert en vervolgens een SELECT instructie uitgeeft voor dezelfde tabel, worden de gewijzigde gegevens opgenomen in de resultatenset.

SERIALIZABLE

Hiermee geeft u de volgende voorwaarden:

• Instructies kunnen geen gegevens lezen die zijn gewijzigd, maar nog niet zijn doorgevoerd door andere transacties.

• Er kunnen geen andere transacties gegevens wijzigen die door de huidige transactie zijn gelezen totdat de huidige transactie is voltooid.

• Andere transacties kunnen geen nieuwe rijen invoegen met sleutelwaarden die in het bereik van sleutels zouden vallen, gelezen door instructies in de huidige transactie totdat de huidige transactie is voltooid.

Bereikvergrendelingen worden geplaatst in het bereik van sleutelwaarden die overeenkomen met de zoekvoorwaarden van elke instructie die in een transactie wordt uitgevoerd. Hierdoor kunnen andere transacties geen rijen bijwerken of invoegen die in aanmerking komen voor een van de instructies die door de huidige transactie worden uitgevoerd. Dit betekent dat als een van de instructies in een transactie een tweede keer wordt uitgevoerd, dezelfde set rijen wordt gelezen. De bereikvergrendelingen worden bewaard totdat de transactie is voltooid. Dit is de meest beperkende van de isolatieniveaus, omdat hiermee volledige bereiken van sleutels worden vergrendeld en de vergrendelingen worden opgeslagen totdat de transactie is voltooid. Omdat gelijktijdigheid lager is, gebruikt u deze optie alleen wanneer dat nodig is. Deze optie heeft hetzelfde effect als het instellen HOLDLOCK van alle tabellen in alle SELECT instructies in een transactie.

Opmerkingen

Er kan slechts één van de opties voor isolatieniveau tegelijk worden ingesteld en deze blijft ingesteld voor die verbinding totdat deze expliciet wordt gewijzigd. Alle leesbewerkingen die in de transactie worden uitgevoerd, werken onder de regels voor het opgegeven isolatieniveau, tenzij een tabelhint in de FROM component van een instructie een ander vergrendelings- of versiebeheergedrag voor een tabel specificeert.

De transactieisolatieniveaus definiëren het type vergrendelingen dat is verkregen bij leesbewerkingen. Gedeelde vergrendelingen die zijn verkregen voor READ COMMITTED of REPEATABLE READ zijn doorgaans rijvergrendelingen, hoewel de rijvergrendelingen kunnen worden geëscaleerd naar pagina- of tabelvergrendelingen als een aanzienlijk aantal rijen in een pagina of tabel wordt verwezen door de leesbewerking. Als de transactie een rij wijzigt nadat deze is gelezen, verkrijgt de transactie een exclusieve vergrendeling om die rij te beveiligen en blijft de exclusieve vergrendeling behouden totdat de transactie is voltooid. Als een REPEATABLE READ transactie bijvoorbeeld een gedeelde vergrendeling op een rij heeft en de transactie vervolgens de rij wijzigt, wordt de gedeelde rijvergrendeling geconverteerd naar een exclusieve rijvergrendeling.

Met één uitzondering kunt u op elk gewenst moment tijdens een transactie overschakelen van het ene isolatieniveau naar het andere. De uitzondering treedt op bij het wijzigen van een isolatieniveau naar SNAPSHOT isolatie. Als u dit doet, mislukt de transactie en wordt de transactie teruggedraaid. U kunt echter een transactie die is gestart in isolatie wijzigen in SNAPSHOT elk ander isolatieniveau.

Wanneer u een transactie wijzigt van het ene isolatieniveau naar het andere, worden resources die na de wijziging worden gelezen, beveiligd volgens de regels van het nieuwe niveau. Resources die worden gelezen voordat de wijziging wordt beveiligd volgens de regels van het vorige niveau. Als een transactie bijvoorbeeld is gewijzigd van READ COMMITTED in SERIALIZABLE, worden de gedeelde vergrendelingen die na de wijziging zijn verkregen, bewaard tot het einde van de transactie.

Als u problemen met een opgeslagen procedure of trigger uitvoert SET TRANSACTION ISOLATION LEVEL , wordt het isolatieniveau opnieuw ingesteld op het niveau dat van kracht is toen het object werd aangeroepen. Als u bijvoorbeeld in een batch instelt REPEATABLE READ en de batch vervolgens een opgeslagen procedure aanroept waarmee het isolatieniveau SERIALIZABLEwordt ingesteld, keert de instelling van het isolatieniveau terug naar REPEATABLE READ het moment dat de opgeslagen procedure de controle naar de batch retourneert.

Wanneer u twee sp_bindsession sessies koppelt, behoudt elke sessie de instelling op isolatieniveau. Als SET TRANSACTION ISOLATION LEVEL u de instelling voor isolatieniveau van één sessie wilt wijzigen, heeft dit geen invloed op de instelling van andere sessies die eraan zijn gebonden.

SET TRANSACTION ISOLATION LEVEL wordt van kracht tijdens het uitvoeren of uitvoeren en niet op parseringstijd.

Geoptimaliseerde bulksgewijs laden bewerkingen op heaps blokquery's die worden uitgevoerd onder de volgende isolatieniveaus:

• SNAPSHOT
• READ UNCOMMITTED
• READ COMMITTED versiebeheer voor rijen gebruiken

Omgekeerd blokkeren query's die worden uitgevoerd onder deze isolatieniveaus geoptimaliseerde bulkbelastingbewerkingen op heaps. Zie Bulkimport en Export van gegevens (SQL Server) voor meer informatie over bulksgewijs laden.

Databases met FILESTREAM-functionaliteit ondersteunen de volgende isolatieniveaus voor transacties.

Voorbeelden

In het volgende voorbeeld wordt de TRANSACTION ISOLATION LEVEL voor de sessie ingesteld. Voor elke Transact-SQL instructie die volgt, bevat SQL Server alle gedeelde vergrendelingen tot het einde van de transactie.

USE AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

Verwante inhoud

• ALTER DATABASE (Transact-SQL)
• DBCC USEROPTIONS (Transact-SQL)
• SELECT (Transact-SQL)
• SET-instructies (Transact-SQL)
• Tabelhints (Transact-SQL)