Gids voor transactie-vergrendeling en rijversiebeheer

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

In elke database leidt wanbeheer van transacties vaak tot conflicten en prestatieproblemen in systemen met veel gebruikers. Naarmate het aantal gebruikers dat toegang heeft tot de gegevens toeneemt, wordt het belangrijk om toepassingen te hebben die efficiënt gebruikmaken van transacties. In deze handleiding worden vergrendelings- en rijversiebeheermechanismen beschreven die de Database Engine gebruikt om de integriteit van elke transactie te waarborgen en informatie te bieden over hoe toepassingen transacties efficiënt kunnen beheren.

Notitie

Geoptimaliseerde vergrendeling is een database-enginefunctie die in 2023 is geïntroduceerd, waardoor het vergrendelingsgeheugen drastisch wordt verminderd en het aantal vergrendelingen dat nodig is voor gelijktijdige schrijfbewerkingen. Dit artikel wordt bijgewerkt om het gedrag van de database-engine te beschrijven met en zonder geoptimaliseerde vergrendeling.

• Zie Geoptimaliseerde vergrendelingvoor meer informatie en voor meer informatie over waar geoptimaliseerde vergrendeling beschikbaar is.
• Als u wilt bepalen of geoptimaliseerde vergrendeling is ingeschakeld voor uw database, raadpleegt u Is geoptimaliseerde vergrendeling ingeschakeld?

Geoptimaliseerde vergrendeling introduceert belangrijke wijzigingen in sommige secties van dit artikel, waaronder:

• Vergrendelen in het databasesysteem
• bewerking verwijderen
• bewerking invoegen
• escalatie van vergrendelen
• Vergrendeling en escalatie beperken
• gedrag bij het wijzigen van gegevens
• gedrag in samenvatting
• Aanwijzingen voor vergrendeling

Basisprincipes van transacties

Een transactie is een reeks bewerkingen die worden uitgevoerd als één logische werkeenheid. Een logische werkeenheid moet vier eigenschappen hebben, de atomische eigenschappen, consistentie, isolatie en duurzaamheid (ACID) genoemd, om als transactie in aanmerking te komen.

Atomiciteit
Een transactie moet een atomische werkeenheid zijn; alle wijzigingen in de gegevens worden uitgevoerd of ze worden niet uitgevoerd.

consistentie
Wanneer deze is voltooid, moet een transactie alle gegevens in een consistente status achterlaten. In een relationele database moeten alle regels worden toegepast op de wijzigingen van de transactie om alle gegevensintegriteit te behouden. Alle interne gegevensstructuren, zoals B-tree-indexen of dubbel gekoppelde lijsten, moeten correct zijn aan het einde van de transactie.

Notitie

Documentatie maakt gebruik van de term B-tree in het algemeen in verwijzing naar indexen. In rowstore-indexen implementeert de Database Engine een B+ tree. Dit geldt niet voor columnstore-indexen of indexen voor tabellen die zijn geoptimaliseerd voor geheugen. Zie de SQL Server- en Azure SQL-indexarchitectuur en ontwerphandleidingvoor meer informatie.

isolatie
Wijzigingen die door gelijktijdige transacties worden aangebracht, moeten worden geïsoleerd van de wijzigingen die zijn aangebracht door andere gelijktijdige transacties. Een transactie herkent gegevens in de toestand waarin deze zich bevond voordat een andere gelijktijdige transactie deze heeft gewijzigd, of herkent de gegevens nadat de tweede transactie is voltooid, maar er wordt geen tussenliggende status herkend. Dit wordt serializeerbaarheid genoemd, omdat het resulteert in de mogelijkheid om de begingegevens opnieuw te laden en een reeks transacties opnieuw af te spelen om te eindigen met de gegevens in dezelfde staat waarin deze zich bevond nadat de oorspronkelijke transacties zijn uitgevoerd.

Duurzaamheid
Nadat een volledig duurzame transactie is voltooid, zijn de effecten permanent aanwezig in het systeem. De wijzigingen blijven behouden, zelfs in het geval van een systeemfout. SQL Server 2014 (12.x) en hoger maken vertraagde duurzame transacties mogelijk. Vertraagde doorvoer van duurzame transacties voordat de transactielogboekrecord op schijf wordt bewaard. Zie het artikel Duurzaamheid van transacties beherenvoor meer informatie over de duurzaamheid van vertraagde transacties.

Toepassingen zijn verantwoordelijk voor het starten en beëindigen van transacties op punten die de logische consistentie van de gegevens afdwingen. De toepassing moet de volgorde definiëren van gegevenswijzigingen die de gegevens in een consistente status laten ten opzichte van de bedrijfsregels van de organisatie. De toepassing voert deze wijzigingen uit in één transactie, zodat de database-engine de integriteit van de transactie kan afdwingen.

Het is de verantwoordelijkheid van een bedrijfsdatabasesysteem, zoals een exemplaar van de database-engine, om mechanismen te bieden die de integriteit van elke transactie garanderen. De database-engine biedt:

• Vergrendelingsfaciliteiten die transactieisolatie behouden.

• Logboekregistratiefaciliteiten om de duurzaamheid van transacties te garanderen. Voor volledig duurzame transacties wordt de logboekrecord gehard op schijf voordat de transacties worden doorgevoerd. Zelfs als de serverhardware, het besturingssysteem of het exemplaar van de database-engine zelf mislukt, gebruikt het exemplaar de transactielogboeken bij het opnieuw opstarten om eventuele onvolledige transacties automatisch terug te draaien naar het punt van de systeemfout. Vertraging bij het uitvoeren van duurzame transacties voordat het transactie-logboekrecord op schijf wordt vastgelegd. Dergelijke transacties kunnen verloren gaan als er een systeemfout optreedt voordat de logboekrecord wordt beveiligd op schijf. Zie het artikel Duurzaamheid van transacties beherenvoor meer informatie over de duurzaamheid van vertraagde transacties.

• Functies voor transactiebeheer die atomiciteit en consistentie van transacties afdwingen. Nadat een transactie is gestart, moet deze zijn voltooid (doorgevoerd) of maakt de database-engine alle gegevenswijzigingen ongedaan die door de transactie zijn aangebracht sinds de transactie is gestart. Deze bewerking wordt het terugdraaien van een transactie genoemd omdat deze de gegevens herstelt naar de toestand zoals deze was vóór de wijzigingen.

Transacties beheren

Toepassingen beheren transacties voornamelijk door op te geven wanneer een transactie wordt gestart en eindigt. Dit kan worden opgegeven met behulp van Transact-SQL instructies of API-functies (Database Application Programming Interface). Het systeem moet ook fouten die een transactie beëindigen correct kunnen afhandelen voordat deze is voltooid. Zie Transactions, Performing Transactions in ODBCen Transactions in SQL Server Native Clientvoor meer informatie.

Transacties worden standaard beheerd op verbindingsniveau. Wanneer een transactie wordt gestart op een verbinding, maken alle Transact-SQL instructies die op die verbinding worden uitgevoerd deel uit van de transactie totdat de transactie eindigt. Onder een MARS-sessie (Multiple Active Result Set) wordt een Transact-SQL expliciete of impliciete transactie echter een transactie met batchbereik die op batchniveau wordt beheerd. Wanneer de batch is voltooid, en als de transactie met batchbereik niet is doorgevoerd of teruggedraaid, wordt deze automatisch teruggedraaid door de Database Engine. Raadpleeg Gebruik maken van meerdere actieve resultaatsets (MARS)voor meer informatie.

Transacties starten

Met api-functies en Transact-SQL-instructies kunt u transacties starten als expliciete, automatische opdracht of impliciete transacties.

Expliciete transacties

Een expliciete transactie is een transactie waarin u zowel het begin als het einde van de transactie expliciet definieert via een API-functie of door de Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTIONof ROLLBACK WORK Transact-SQL-instructies uit te geven. Wanneer de transactie eindigt, keert de verbinding terug naar de transactiemodus waarin deze zich bevond voordat de expliciete transactie werd gestart. Dit kan de impliciete of automatische opdrachtmodus zijn.

U kunt alle Transact-SQL instructies in een expliciete transactie gebruiken, met uitzondering van de volgende instructies:

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• Opgeslagen procedures voor volledig tekstsysteem
• sp_dboption om databaseopties in te stellen of een systeemprocedure die de master database binnen expliciete of impliciete transacties wijzigt.

Notitie

UPDATE STATISTICS kan worden gebruikt binnen een expliciete transactie. Echter, UPDATE STATISTICS committeert zich onafhankelijk van de insluitende transactie en kan niet worden teruggedraaid.

autotoewijzingstransacties

De modus Autocommit is de standaardmodus voor transactiebeheer van de Database Engine. Elke Transact-SQL statement wordt vastgezet of teruggedraaid wanneer deze is voltooid. Als een verklaring succesvol is voltooid, wordt deze doorgevoerd; als er een fout optreedt, wordt deze teruggedraaid. Een verbinding met een instantie van de Database Engine werkt in autocommit-modus wanneer deze standaardmodus niet is overschreven door expliciete of impliciete transacties. De modus Automatisch toewijzen is ook de standaardmodus voor SqlClient, ADO, OLE DB en ODBC.

impliciete transacties

Wanneer een verbinding wordt uitgevoerd in de impliciete transactiemodus, start het exemplaar van de database-engine automatisch een nieuwe transactie nadat de huidige transactie is doorgevoerd of teruggedraaid. U doet niets om het begin van een transactie af te bakenen; u alleen elke transactie doorvoert of terugdraait. Impliciete transactiemodus genereert een continue keten van transacties. Stel de impliciete transactiemodus in via een API-functie of de Transact-SQL SET IMPLICIT_TRANSACTIONS ON-instructie. Deze modus wordt ook wel Autocommit OFF genoemd. Zie setAutoCommit Method (SQLServerConnection).

Nadat de impliciete transactiemodus is ingeschakeld voor een verbinding, start het exemplaar van de database-engine automatisch een transactie wanneer deze voor het eerst een van deze instructies uitvoert:

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

Transacties met batch-omvang

Alleen van toepassing op meerdere actieve resultatensets (MARS): een Transact-SQL expliciete of impliciete transactie die onder een MARS-sessie begint, wordt een transactie met batch-bereik. Een transactie met batchbereik die niet wordt doorgevoerd of teruggedraaid wanneer een batch is voltooid, wordt automatisch teruggedraaid door de Database Engine.

gedistribueerde transacties

Gedistribueerde transacties omvatten twee of meer servers die resourcemanagers worden genoemd. Het beheer van de transactie moet worden gecoördineerd tussen de resourcemanagers door een serveronderdeel dat een transactiebeheerder wordt genoemd. Elk exemplaar van de database-engine kan functioneren als resourcemanager in gedistribueerde transacties die worden gecoördineerd door transactiebeheerders, zoals Microsoft Distributed Transaction Coordinator (MS DTC) of andere transactiebeheerders die ondersteuning bieden voor de Open Group XA-specificatie voor gedistribueerde transactieverwerking. Zie de MS DTC-documentatie voor meer informatie.

Een transactie binnen één exemplaar van de database-engine die twee of meer databases omvat, is een gedistribueerde transactie. De instantie beheert de gedistribueerde transactie intern; voor de gebruiker werkt het als een lokale transactie.

In de toepassing wordt een gedistribueerde transactie veel hetzelfde beheerd als een lokale transactie. Aan het einde van de transactie vraagt de toepassing om de transactie vast te zetten of terug te draaien. Een gedistribueerde commit moet anders worden beheerd door de transactiebeheerder om het risico te minimaliseren dat een netwerkfout ertoe kan leiden dat sommige resourcemanagers committeren, terwijl anderen terugdraaien. Dit wordt bereikt door het doorvoerproces in twee fasen (de voorbereidingsfase en de doorvoerfase) te beheren. Dit wordt een doorvoer in twee fasen genoemd.

• Voorbereidingsfase

  Wanneer de transactiebeheerder een doorvoeraanvraag ontvangt, wordt er een voorbereidingsopdracht verzonden naar alle resourcemanagers die betrokken zijn bij de transactie. Elke Resource Manager doet vervolgens alles wat nodig is om de transactie duurzaam te maken en alle transactielogboekbuffers voor de transactie worden leeggemaakt op schijf. Wanneer elke resourcemanager de voorbereidingsfase voltooit, stuurt deze het resultaat, geslaagd of mislukt, terug naar de transactiebeheerder. SQL Server 2014 (12.x) heeft de duurzaamheid van vertraagde transacties geïntroduceerd. Vertraagde duurzame transacties worden doorgevoerd voordat de buffers van het transactielogboek bij elke Resource Manager naar de schijf worden weggeschreven. Zie het artikel Duurzaamheid van transacties beherenvoor meer informatie over de duurzaamheid van vertraagde transacties.

• doorvoerfase

  Als de transactiebeheerder geslaagde voorbereiding ontvangt van alle resourcemanagers, worden doorvoeropdrachten naar elke resourcemanager verzonden. De resourcemanagers kunnen de doorvoer vervolgens voltooien. Als alle resourcebeheerders een geslaagde doorvoering rapporteren, stuurt de transactiebeheerder vervolgens een melding naar de toepassing. Als een resourcemanager een fout heeft gerapporteerd bij de voorbereiding, stuurt de transactiemanager een terugdraaiopdracht naar elke resourcemanager en geeft de fout van de commit door aan de toepassing.

  Database Engine-toepassingen kunnen gedistribueerde transacties beheren via Transact-SQL of via de database-API. Zie BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)voor meer informatie.

Transacties beëindigen

U kunt transacties beëindigen met een COMMIT- of ROLLBACK-instructie, of via een bijbehorende API-functie.

• doorvoeren

  Als een transactie is geslaagd, voert u deze door. Een COMMIT verklaring garandeert dat alle wijzigingen van de transactie een permanent onderdeel van de database zijn. Met een doorvoering worden ook middelen, zoals vergrendelingen die door de transactie worden gebruikt, vrijgegeven.

• terugdraaien

  Als er een fout optreedt in een transactie of als de gebruiker besluit de transactie te annuleren, rolt u de transactie terug. Een ROLLBACK instructie herroept alle wijzigingen die in de transactie zijn aangebracht door de gegevens terug te zetten naar de status waarin deze aan het begin van de transactie bevond. Transacties terugdraaien bevrijdt ook door de transactie ingenomen middelen.

Notitie

Bij meerdere MARS-sessies (Active Result Sets) kan een expliciete transactie die is gestart via een API-functie, niet worden doorgevoerd terwijl er uitvoeringsaanvragen in behandeling zijn. Elke poging om dit type transactie door te voeren terwijl er aanvragen worden uitgevoerd, resulteert in een fout.

Fouten tijdens transactieverwerking

Als een fout ervoor zorgt dat een transactie niet met succes kan worden voltooid, wordt de transactie automatisch teruggedraaid door de Database Engine en worden alle resources die door de transactie worden gebruikt, vrijgemaakt. Als de clientnetwerkverbinding met een exemplaar van de database-engine wordt verbroken, worden openstaande transacties voor de verbinding teruggedraaid wanneer het netwerk het exemplaar waarschuwt over de verbindingsonderbreking. Als de clienttoepassing faalt of als de clientcomputer uitvalt of opnieuw wordt opgestart, wordt de verbinding verbroken. Het exemplaar van de database-engine draait openstaande transacties terug wanneer het netwerk melding maakt van de verbindingsonderbreking. Als de client de verbinding met de database-engine verbreekt, worden openstaande transacties teruggedraaid.

Als een runtime-instructiefout (zoals een schending van een beperking) in een batch optreedt, is het standaardgedrag in de database-engine om alleen de instructie terug te draaien die de fout heeft gegenereerd. U kunt dit gedrag wijzigen met behulp van de instructie SET XACT_ABORT ON. Nadat SET XACT_ABORT ON is uitgevoerd, veroorzaakt een runtime-instructiefout een automatische terugdraaiactie van de huidige transactie. Compileerfouten, zoals syntaxisfouten, worden niet beïnvloed door SET XACT_ABORT. Zie SET XACT_ABORT (Transact-SQL)voor meer informatie.

Wanneer er fouten optreden, moet de juiste actie (COMMIT of ROLLBACK) worden opgenomen in de toepassingscode. Een effectief hulpmiddel voor het afhandelen van fouten, waaronder die in transacties, is de Transact-SQL TRY...CATCH constructie. Voor meer informatie over voorbeelden met transacties, zie TRY...CATCH (Transact-SQL). Vanaf SQL Server 2012 (11.x) kunt u de THROW-instructie gebruiken om een uitzondering te genereren en de uitvoering over te dragen naar een CATCH blok van een TRY...CATCH-constructie. Voor meer informatie, zie THROW (Transact-SQL).

Compileer- en runtime-fouten in de modus AutoCommit

In de modus voor automatisch samenvoegen lijkt het soms alsof een exemplaar van de database-engine een volledige batch heeft teruggedraaid in plaats van slechts één SQL-instructie. Dit gebeurt als de fout die is opgetreden een compileerfout is, geen runtimefout. Een compileerfout voorkomt dat de database-engine een uitvoeringsplan bouwt, waardoor er niets in de batch kan worden uitgevoerd. Hoewel het lijkt alsof alle instructies voordat de instructie die de fout genereert, zijn teruggedraaid, heeft de fout voorkomen dat iets in de batch wordt uitgevoerd. In het volgende voorbeeld worden geen van de INSERT instructies in de derde batch uitgevoerd vanwege een compileerfout. Het lijkt erop dat de eerste twee INSERT instructies worden teruggedraaid wanneer ze nooit worden uitgevoerd.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

In het volgende voorbeeld genereert de derde INSERT-instructie een runtimefout met dubbele primaire sleutel. De eerste twee INSERT-instructies zijn geslaagd en doorgevoerd, dus blijven ze behouden na de uitvoeringsfout.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

De database-engine maakt gebruik van uitgestelde naamomzetting, waarbij objectnamen worden omgezet tijdens de uitvoering, niet op compilatietijd. In het volgende voorbeeld worden de eerste twee INSERT-instructies uitgevoerd en doorgevoerd en blijven deze twee rijen in de TestBatch tabel staan nadat de derde INSERT instructie een runtimefout genereert door te verwijzen naar een tabel die niet bestaat.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Basisbeginselen van vergrendeling en rijversiesbeheer

De database-engine gebruikt de volgende mechanismen om de integriteit van transacties te waarborgen en de consistentie van databases te behouden wanneer meerdere gebruikers tegelijkertijd toegang hebben tot gegevens:

• vergrendelen

  Elke transactie vraagt om vergrendelingen van verschillende typen op de resources, zoals rijen, pagina's of tabellen, waarvan de transactie afhankelijk is. De vergrendelingen blokkeren dat andere transacties de resources op een manier wijzigen die problemen zou veroorzaken voor de transactie die de vergrendeling aanvraagt. Elke transactie maakt de vergrendelingen vrij wanneer deze geen afhankelijkheid meer heeft van de vergrendelde resources.

• rijversiebeheer

  Wanneer een isolatieniveau op basis van rijversiebeheer wordt gebruikt, onderhoudt de Database Engine versies van elke rij die wordt gewijzigd. Toepassingen kunnen specificeren dat een transactie de rijversies gebruikt om gegevens weer te geven zoals ze aan het begin van de transactie of verklaring bestonden, in plaats van alle leesbewerkingen middels vergrendelingen te beveiligen. Door het gebruik van rijversiebeheer wordt de kans dat een leesbewerking andere transacties blokkeert aanzienlijk verminderd.

Vergrendelings- en rijversiebeheer verhinderen dat gebruikers niet-doorgevoerde gegevens lezen en voorkomen dat meerdere gebruikers tegelijkertijd dezelfde gegevens proberen te wijzigen. Zonder vergrendeling of versiebeheer van rijen kunnen query's die op die gegevens worden uitgevoerd, onverwachte resultaten opleveren door gegevens te retourneren die nog niet zijn doorgevoerd in de database.

Toepassingen kunnen transactieisolatieniveaus kiezen, die het beveiligingsniveau voor de transactie definiëren op basis van wijzigingen die door andere transacties zijn aangebracht. Hints op tabelniveau kunnen worden opgegeven voor afzonderlijke Transact-SQL instructies om het gedrag verder aan te passen aan de vereisten van de toepassing.

Gelijktijdige gegevenstoegang beheren

Gebruikers die tegelijkertijd toegang hebben tot een resource, zouden gelijktijdig toegang hebben tot de resource. Gelijktijdige gegevenstoegang vereist mechanismen om nadelige effecten te voorkomen wanneer meerdere gebruikers resources proberen te wijzigen die andere gebruikers actief gebruiken.

Gelijktijdigheidseffecten

Gebruikers die gegevens wijzigen, kunnen van invloed zijn op andere gebruikers die dezelfde gegevens tegelijkertijd lezen of wijzigen. Deze gebruikers hebben naar verwachting gelijktijdig toegang tot de gegevens. Als een database geen gelijktijdigheidsbeheer heeft, kunnen gebruikers de volgende bijwerkingen zien:

• Verloren updates

  Er zijn updates verloren gegaan wanneer twee of meer transacties dezelfde rij selecteren en vervolgens de rij bijwerken op basis van de oorspronkelijk geselecteerde waarde. Elke transactie is niet op de hoogte van de andere transacties. De laatste update overschrijft updates van de andere transacties, wat resulteert in verloren gegevens.

  Twee editors maken bijvoorbeeld een elektronisch exemplaar van hetzelfde document. Elke editor wijzigt de kopie onafhankelijk en slaat vervolgens de gewijzigde kopie op, waardoor het oorspronkelijke document wordt overschreven. De editor die de gewijzigde kopie het laatst opslaat, overschrijft de wijzigingen die door de andere editor zijn aangebracht. Dit probleem kan worden vermeden als de ene editor geen toegang heeft tot het bestand totdat de andere editor is voltooid en de transactie heeft doorgevoerd.

• Niet-toegewijde afhankelijkheid (vuile lezing)

  Niet-doorgevoerde afhankelijkheid doet zich voor wanneer een tweede transactie een rij leest die door een andere transactie wordt bijgewerkt. De tweede transactie leest gegevens die nog niet zijn doorgevoerd en kan worden gewijzigd door de transactie die de rij bijwerkt.

  Een editor wijzigt bijvoorbeeld wijzigingen in een elektronisch document. Tijdens de wijzigingen neemt een tweede editor een kopie van het document met alle wijzigingen die tot nu toe zijn aangebracht, en distribueert het document naar de beoogde doelgroep. De eerste editor bepaalt vervolgens de wijzigingen die tot nu toe zijn aangebracht, zijn onjuist en verwijdert de bewerkingen en slaat het document op. Het gedistribueerde document bevat bewerkingen die niet meer bestaan en moeten worden behandeld alsof ze nooit bestaan. Dit probleem kan worden vermeden als niemand het gewijzigde document kan lezen totdat de eerste editor de laatste opslag van wijzigingen doet en de transactie doorvoert.

• inconsistente analyse (niet-herhaalbare leesbewerking)

  Inconsistente analyse treedt op wanneer een tweede transactie meerdere keren dezelfde rij opent en verschillende gegevens elke keer leest. Inconsistente analyse is vergelijkbaar met niet-doorgevoerde afhankelijkheid omdat een andere transactie de gegevens wijzigt die door een tweede transactie worden gelezen. Bij een inconsistente analyse zijn de gegevens die door de tweede transactie worden gelezen echter doorgevoerd door de transactie die de wijziging heeft aangebracht. Een inconsistente analyse omvat ook meerdere leesbewerkingen (twee of meer) van dezelfde rij, waarbij elke keer de informatie wordt gewijzigd door een andere transactie; vandaar de term niet-herhaalbare leesactie.

  Een redacteur leest bijvoorbeeld hetzelfde document twee keer, maar tussen de lezingen door wordt het document herschreven. Wanneer de editor het document voor de tweede keer leest, is het gewijzigd. De oorspronkelijke leesbewerking was niet herhaalbaar. Dit probleem kan worden vermeden als de schrijver het document pas kan wijzigen als de editor het de laatste keer heeft gelezen.

• Phantom leest

  Een fantoomleesbewerking is een situatie die optreedt wanneer twee identieke query's worden uitgevoerd en de set rijen die door de tweede query worden geretourneerd, verschilt. In het volgende voorbeeld ziet u hoe dit kan gebeuren. Stel dat de twee transacties tegelijkertijd worden uitgevoerd. De twee SELECT-instructies in de eerste transactie kunnen verschillende resultaten retourneren omdat de INSERT-instructie in de tweede transactie de gegevens wijzigt die door beide worden gebruikt.

  --Transaction 1
  BEGIN TRAN;
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 AND ID < 10;
  
  --The INSERT statement from the second transaction occurs here.
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee (Id, Name)
  VALUES(6 ,'New');
  
  COMMIT;
  

• Ontbrekende en dubbele leesbewerkingen veroorzaakt door rijupdates

  • Een bijgewerkte rij ontbreekt of u ziet een bijgewerkte rij meerdere keren

    Transacties die worden uitgevoerd op READ UNCOMMITTED niveau (of instructies die gebruikmaken van de NOLOCK tabelhint), geven geen gedeelde vergrendelingen uit om te voorkomen dat andere transacties gegevens wijzigen die door de huidige transactie worden gelezen. Transacties die op READ COMMITTED-niveau draaien, brengen gedeelde vergrendelingen tot stand, maar de rij- of paginavergrendelingen worden vrijgegeven zodra de rij is gelezen. Wanneer u een index scant en een andere gebruiker de kolom met de indexsleutel van de rij tijdens uw lezing wijzigt, kan de rij mogelijk opnieuw verschijnen als de sleutelwijziging de rij naar een positie vooruit in uw scan verplaatst. Op dezelfde manier kan de rij helemaal niet worden gelezen als de sleutel de rij verplaatst naar een positie in de index die u al gelezen had. U kunt dit voorkomen door de SERIALIZABLE of HOLDLOCK hint, of rijversiebeheer te gebruiken. Zie tabelhints (Transact-SQL)voor meer informatie.

  • Een of meer rijen ontbreken die niet het doel van de update waren

    Wanneer u READ UNCOMMITTEDgebruikt, als uw query rijen leest met behulp van een scan voor toewijzingsorders (met behulp van IAM-pagina's), mist u mogelijk rijen als een andere transactie een paginasplitsing veroorzaakt. Dit gebeurt niet wanneer u het READ COMMITTED isolatieniveau gebruikt.

Typen van gelijktijdigheid

Wanneer meerdere transacties tegelijkertijd proberen gegevens in een database te wijzigen, moet een systeem van besturingselementen worden geïmplementeerd, zodat wijzigingen die door de ene transactie zijn aangebracht, geen nadelige invloed hebben op die van een andere transactie. Dit wordt gelijktijdigheidsbeheer genoemd.

De theorie van gelijktijdigheidscontrole heeft twee classificaties voor de methoden voor het implementeren van gelijktijdigheidsbeheer.

• Pessimistisch concurrentiebeheer

  Een systeem van vergrendelingen voorkomt dat transacties gegevens op een manier wijzigen die van invloed is op andere transacties. Nadat een transactie een actie heeft uitgevoerd die ervoor zorgt dat een vergrendeling wordt toegepast, kunnen andere transacties geen acties uitvoeren die conflicteren met de vergrendeling totdat de eigenaar deze vrijgeeft. Dit wordt pessimistische controle genoemd omdat deze doorgaans wordt gebruikt in systemen met een hoog conflict voor gegevens, waarbij de kosten voor het beveiligen van gegevens met vergrendelingen lager zijn dan de kosten voor het terugdraaien van transacties als er gelijktijdigheidsconflicten optreden.

• Optimistisch gelijktijdigheidsbeheer

  Bij optimistisch gelijktijdigheidsbeheer vergrendelen transacties geen gegevens wanneer ze deze lezen. Wanneer een transactie echter gegevens bijwerken, controleert het systeem of een andere transactie de gegevens heeft gewijzigd nadat deze zijn gelezen. Als een andere transactie de gegevens heeft bijgewerkt, wordt er een fout gegenereerd. Normaal gesproken wordt de transactie die de fout ontvangt, teruggedraaid en opnieuw gestart. Dit wordt optimistisch genoemd omdat deze doorgaans wordt gebruikt in systemen met weinig conflicten voor gegevens en waarbij de kosten voor het af en toe terugdraaien van een transactie lager zijn dan de kosten voor het vergrendelen van gegevens wanneer ze worden gelezen.

De database-engine ondersteunt beide gelijktijdigheidsbeheermethoden. Gebruikers geven het type gelijktijdigheidsbeheer op door transactieisolatieniveaus te selecteren voor verbindingen of gelijktijdigheidsopties op cursors. Deze kenmerken kunnen worden gedefinieerd met behulp van Transact-SQL instructies of via de eigenschappen en kenmerken van API's (Database Application Programming Interfaces), zoals ADO, ADO.NET, OLE DB en ODBC.

Isolatieniveaus in de database-engine

Transacties geven een isolatieniveau op waarmee wordt gedefinieerd in welke mate één transactie moet worden geïsoleerd van de resource of gegevenswijzigingen die door andere transacties zijn aangebracht. Isolatieniveaus worden beschreven in termen van welke gelijktijdigheidsneveneffecten, zoals vuile leesbewerkingen of fantoomleesbewerkingen, zijn toegestaan.

Beheer van transactieisolatieniveaus:

• Of er vergrendelingen worden verkregen wanneer gegevens worden gelezen en welk type vergrendelingen worden aangevraagd.
• Hoe lang de leesvergrendelingen worden bewaard.
• Of een leesbewerking verwijst naar rijen die zijn gewijzigd door een andere transactie:
  • Blokken totdat de exclusieve vergrendeling op de rij wordt vrijgemaakt.
  • Haalt de vastgelegde versie van de rij op die bestond op het moment dat de instructie of transactie is gestart.
  • Leest de niet-doorgevoerde gegevenswijziging.

Belangrijk

Het kiezen van een transactieisolatieniveau heeft geen invloed op de vergrendelingen die zijn verkregen om gegevenswijzigingen te beschermen. Een transactie heeft altijd een exclusieve vergrendeling voor het wijzigen van gegevens en houdt die vergrendeling vast totdat de transactie is voltooid, ongeacht het isolatieniveau dat voor die transactie is ingesteld. Voor leesbewerkingen definiëren transactieisolatieniveaus voornamelijk het niveau van beveiliging tegen de gevolgen van wijzigingen die door andere transacties zijn aangebracht.

Een lager isolatieniveau verhoogt de mogelijkheid van veel transacties om tegelijkertijd toegang te krijgen tot gegevens, maar verhoogt ook het aantal gelijktijdigheidseffecten (zoals vuile lees- of verloren updates) die kunnen optreden. Een hoger isolatieniveau vermindert daarentegen de typen gelijktijdigheidseffecten die transacties kunnen tegenkomen, maar vereist meer systeemresources en verhoogt de kans dat de ene transactie een andere blokkeert. Het kiezen van het juiste isolatieniveau is afhankelijk van het verdelen van de vereisten voor gegevensintegriteit van de toepassing tegen de overhead van elk isolatieniveau. Het hoogste isolatieniveau, SERIALIZABLE, garandeert dat een transactie exact dezelfde gegevens ophaalt telkens wanneer een leesbewerking wordt herhaald, maar dit gebeurt door een vergrendelingsniveau uit te voeren dat waarschijnlijk van invloed is op andere transacties in systemen met meerdere gebruikers. Het laagste isolatieniveau, READ UNCOMMITTED, kan gegevens ophalen die zijn gewijzigd, maar die niet zijn doorgevoerd door andere transacties. Alle gelijktijdigheidseffecten kunnen optreden in READ UNCOMMITTED, maar er is geen leesvergrendeling of versiebeheer, dus overhead wordt geminimaliseerd.

Isolatieniveaus van de database engine

De ISO-standaard definieert de volgende isolatieniveaus, die allemaal worden ondersteund door de database-engine:

Isolatieniveau	Definitie
READ UNCOMMITTED	Het laagste isolatieniveau waarbij transacties alleen worden geïsoleerd om ervoor te zorgen dat fysiek inconsistente gegevens niet worden gelezen. In dit niveau zijn vuile leesbewerkingen toegestaan, zodat één transactie mogelijk nog niet doorgevoerde wijzigingen ziet die door andere transacties zijn aangebracht.
READ COMMITTED	Hiermee kan een transactie gegevens lezen die eerder zijn gelezen (niet gewijzigd) door een andere transactie zonder te wachten tot de eerste transactie is voltooid. De database-engine bewaart schrijfvergrendelingen (verkregen op geselecteerde gegevens) tot het einde van de transactie, maar leesvergrendelingen worden vrijgegeven zodra de leesbewerking wordt uitgevoerd. Dit is het standaardniveau van de database-engine.
REPEATABLE READ	De database-engine bewaart lees- en schrijfvergrendelingen die zijn verkregen op geselecteerde gegevens tot het einde van de transactie. Omdat bereikvergrendelingen echter niet worden beheerd, kunnen fantoomleesbewerkingen optreden.
SERIALIZABLE	Het hoogste niveau waar transacties volledig van elkaar worden geïsoleerd. De database-engine bewaart lees- en schrijfvergrendelingen die zijn verkregen op geselecteerde gegevens tot het einde van de transactie. Bereikvergrendelingen worden verkregen wanneer een SELECT-bewerking gebruikmaakt van een range-WHERE-clausule om fantoomlezingen te voorkomen. Opmerking: DDL-bewerkingen en -transacties in gerepliceerde tabellen kunnen mislukken wanneer het SERIALIZABLE isolatieniveau wordt aangevraagd. Dit komt doordat replicatiequery's hints gebruiken die mogelijk niet compatibel zijn met het SERIALIZABLE isolatieniveau.

De database-engine ondersteunt ook twee extra isolatieniveaus voor transacties die gebruikmaken van rijversiebeheer. Een is een implementatie van READ COMMITTED isolatieniveau en één is het SNAPSHOT transactieisolatieniveau.

Isolatieniveau voor rijversies	Definitie
Read Committed Snapshot (RCSI)	Wanneer de READ_COMMITTED_SNAPSHOT databaseoptie is ingesteld op ON, wat de standaardinstelling is in Azure SQL Database, maakt het READ COMMITTED-isolatieniveau gebruik van rijversiebeheer om leesconsistentie op instructieniveau te bieden. Leesbewerkingen vereisen alleen de schemastabiliteit (Sch-S) vergrendelingen op tabelniveau en geen pagina- of rijvergrendelingen. Dat wil zeggen dat de Database Engine rijversies gebruikt om voor elke instructie een transactioneel consistente momentopname van de gegevens te maken, zoals die bestonden op het moment dat de instructie begon. Vergrendelingen worden niet gebruikt om de gegevens te beschermen tegen updates door andere transacties. Een door de gebruiker gedefinieerde functie kan gegevens retourneren die zijn doorgevoerd na de tijd dat de instructie met de UDF is gestart. Wanneer de optie READ_COMMITTED_SNAPSHOT database is ingesteld OFF, wat de standaardinstelling is in SQL Server en Azure SQL Managed Instance, gebruikt READ COMMITTED isolatie 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. Beide implementaties voldoen aan de ISO-definitie van READ COMMITTED isolatie.
SNAPSHOT	Het isolatieniveau voor momentopnamen maakt gebruik van rijversiebeheer om leesconsistentie op transactieniveau te bieden. Leesbewerkingen krijgen geen pagina- of rijvergrendelingen; er worden alleen tabelvergrendelingen voor schemastabiliteit (Sch-S) verkregen. Bij het lezen van rijen die zijn gewijzigd door een andere transactie, halen leesbewerkingen de versie op van de rij die bestond toen de transactie werd gestart. U kunt alleen SNAPSHOT isolatie gebruiken wanneer de optie ALLOW_SNAPSHOT_ISOLATION database is ingesteld op ON. Deze optie is standaard ingesteld op OFF voor gebruikersdatabases in SQL Server en Azure SQL Managed Instance en ingesteld op ON voor databases in Azure SQL Database. Opmerking: De database-engine biedt geen ondersteuning voor versiebeheer van metagegevens. Daarom gelden er beperkingen voor welke DDL-bewerkingen kunnen worden uitgevoerd in een expliciete transactie die wordt uitgevoerd onder isolatie van momentopnamen. De volgende DDL-instructies zijn niet toegestaan onder snapshot-isolatie na een BEGIN TRANSACTION-instructie: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEMEof een DDL-instructie voor de common language runtime (CLR). Deze verklaringen zijn toegestaan wanneer u momentopname-isolatie binnen impliciete transacties gebruikt. Een impliciete transactie is per definitie één instructie waarmee u de semantiek van isolatie van momentopnamen kunt afdwingen, zelfs met DDL-instructies. Schendingen van dit principe kunnen fout 3961 veroorzaken: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

In de volgende tabel ziet u de gelijktijdigheidseffecten die zijn ingeschakeld door de verschillende isolatieniveaus.

Isolatieniveau	Ongecommitteerde lezing	Niet-herhaalbare uitlezing	Spook
READ UNCOMMITTED	Ja	Ja	Ja
READ COMMITTED	Nee	Ja	Ja
REPEATABLE READ	Nee	Nee	Ja
SNAPSHOT	Nee	Nee	Nee
SERIALIZABLE	Nee	Nee	Nee

Voor meer informatie over de specifieke typen vergrendelings- of rijversies die door elk transactie-isolatieniveau worden beheerd, zie SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Transactieisolatieniveaus kunnen worden ingesteld met behulp van Transact-SQL of via een database-API.

Transact-SQL
Transact-SQL scripts gebruiken de instructie SET TRANSACTION ISOLATION LEVEL.

ADO
ADO-toepassingen stellen de eigenschap IsolationLevel van het Connection-object in op adXactReadUncommitted, adXactReadCommitted, adXactRepeatableReadof adXactReadSerializable.

ADO.NET
ADO.NET toepassingen die de beheerde naamruimte System.Data.SqlClient gebruiken, kunnen de methode SqlConnection.BeginTransaction aanroepen en de optie IsolationLevel instellen op Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializableof Snapshot.

OLE DB
Bij het starten van een transactie roepen toepassingen die gebruikmaken van OLE DB, aanroep ITransactionLocal::StartTransaction aan waarbij isoLevel is ingesteld op ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOTof ISOLATIONLEVEL_SERIALIZABLE.

Wanneer u het isolatieniveau voor transacties opgeeft in de modus Automatisch toewijzen, kunnen OLE DB-toepassingen de eigenschap DBPROPSET_SESSION instellen DBPROP_SESS_AUTOCOMMITISOLEVELS op DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATEDof DBPROPVAL_TI_SNAPSHOT.

ODBC
ODBC-toepassingen roepen SQLSetConnectAttr aan met Attribute ingesteld op SQL_ATTR_TXN_ISOLATION en ValuePtr ingesteld op SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READof SQL_TXN_SERIALIZABLE.

Voor momentopnametransacties roepen toepassingen SQLSetConnectAttr aan met kenmerk ingesteld op SQL_COPT_SS_TXN_ISOLATION en ValuePtr ingesteld op SQL_TXN_SS_SNAPSHOT. Een momentopnametransactie kan worden opgehaald met behulp van SQL_COPT_SS_TXN_ISOLATION of SQL_ATTR_TXN_ISOLATION.

Vergrendelen in de database-engine

Vergrendelen is een mechanisme dat door de database-engine wordt gebruikt om de toegang door meerdere gebruikers te synchroniseren met hetzelfde stukje gegevens tegelijk.

Voordat een transactie een afhankelijkheid verkrijgt van de huidige status van een stukje gegevens, zoals door de gegevens te lezen of te wijzigen, moet deze zichzelf beschermen tegen de gevolgen van een andere transactie die dezelfde gegevens wijzigt. De transactie doet dit door een vergrendeling op het gegevensstuk aan te vragen. Vergrendelingen hebben verschillende modi, zoals gedeeld (S) of exclusief (X). De vergrendelingsmodus definieert het afhankelijkheidsniveau van de transactie op de gegevens. Er kan geen transactie een vergrendeling worden verleend die strijdig is met de modus van een vergrendeling die al aan die gegevens is verleend voor een andere transactie. Als een transactie een vergrendelingsmodus aanvraagt die conflicteert met een vergrendeling die al op dezelfde gegevens is verleend, wordt de aanvraagtransactie door de database-engine onderbroken totdat de eerste vergrendeling wordt vrijgegeven.

Wanneer een transactie een stukje gegevens wijzigt, bevat deze bepaalde vergrendelingen die de wijziging beveiligen tot het einde van de transactie. Hoe lang een transactie de vergrendelingen bevat die zijn verkregen om leesbewerkingen te beveiligen, is afhankelijk van de instelling op transactieisolatieniveau en of geoptimaliseerde vergrendeling is ingeschakeld.

• Wanneer geoptimaliseerde vergrendeling niet is ingeschakeld, worden rij- en paginavergrendelingen die nodig zijn voor schrijfbewerkingen bewaard tot het einde van de transactie.

• Wanneer geoptimaliseerde vergrendeling is ingeschakeld, wordt alleen een TID-vergrendeling (Transaction ID) bewaard tot het einde van de transactie. Onder het standaardniveau van READ COMMITTED isolatie worden voor transacties geen rij- en paginavergrendelingen bewaard die nodig zijn voor schrijfbewerkingen tot het einde van de transactie. Dit vermindert het vereiste vergrendelingsgeheugen en vermindert de noodzaak van escalatie van vergrendelingen. Verder, wanneer geoptimaliseerde vergrendeling is ingeschakeld, evalueert de vergrendeling na kwalificatie (LAQ) optimalisatie de predicaten van een query op de nieuwste vastgelegde versie van de rij zonder een vergrendeling te verkrijgen, wat de gelijktijdigheid verbetert.

Alle vergrendelingen die door een transactie worden bewaard, worden vrijgegeven wanneer de transactie is voltooid (wordt bevestigd of wordt teruggedraaid).

Toepassingen vragen doorgaans geen vergrendelingen rechtstreeks aan. Vergrendelingen worden intern beheerd door een deel van de database-engine, de vergrendelingsmanager genoemd. Wanneer een exemplaar van de Database Engine een Transact-SQL instructie verwerkt, bepaalt de queryprocessor van de Database Engine welke bronnen moeten worden geopend. De queryprocessor bepaalt welke typen vergrendelingen vereist zijn om elke resource te beveiligen op basis van het type toegang en de instelling op transactieisolatieniveau. De queryprocessor vraagt vervolgens de juiste vergrendelingen aan bij de vergrendelingsmanager. De vergrendelingsbeheerder verleent de vergrendelingen als er geen conflicterende vergrendelingen zijn die door andere transacties worden gehouden.

Granulariteit en hiërarchieën vergrendelen

De database-engine heeft multigranulaire vergrendeling waarmee verschillende typen resources door een transactie kunnen worden vergrendeld. Om de kosten van vergrendeling te minimaliseren, vergrendelt de database-engine resources automatisch op een niveau dat geschikt is voor de taak. Het vergrendelen van een kleinere granulariteit, zoals rijen, verhoogt de gelijktijdigheid, maar heeft een hogere overhead omdat er meer vergrendelingen moeten worden bewaard als veel rijen zijn vergrendeld. Het vergrendelen van een grotere granulariteit, zoals tabellen, is duur in termen van gelijktijdigheid, omdat het vergrendelen van een hele tabel de toegang beperkt tot een deel van de tabel door andere transacties. Het heeft echter een lagere overhead omdat er minder vergrendelingen worden onderhouden.

De database-engine moet vaak vergrendelingen verkrijgen op meerdere granulariteitsniveaus om een resource volledig te beveiligen. Deze groep vergrendelingen op meerdere granulariteitsniveaus wordt een vergrendelingshiërarchie genoemd. Als u bijvoorbeeld een leesbewerking van een index volledig wilt beveiligen, moet een exemplaar van de Database Engine mogelijk gedeelde vergrendelingen verkrijgen op rijen en intentie-gedeelde vergrendelingen op de pagina's en de tabel.

In de volgende tabel ziet u de resources die de database-engine kan vergrendelen.

Hulpbron	Beschrijving
RID	Een rij-id die wordt gebruikt om één rij binnen een heap te vergrendelen.
KEY	Een rijvergrendeling om één rij in een B-tree-index te vergrendelen.
PAGE	Een pagina van 8 kilobyte (KB) in een database, zoals gegevens of indexpagina's.
EXTENT	Een aaneengesloten groep van acht pagina's, zoals gegevens of indexpagina's.
HoBT 1	Een heap of B-boom. Een vergrendeling die een B-tree (index) of de heap-gegevenspagina's in een tabel beveiligt die geen geclusterde index heeft.
TABLE 1	De hele tabel, inclusief alle gegevens en indexen.
FILE	Een databasebestand.
APPLICATION	Een door de toepassing opgegeven resource.
METADATA	Metagegevensvergrendelingen.
ALLOCATION_UNIT	Een toewijzingseenheid.
DATABASE	De hele database.
XACT 2	TID-vergrendeling (Transaction ID) gebruikt in Geoptimaliseerde vergrendeling. Zie Transaction ID (TID)-vergrendelingvoor meer informatie.

¹HoBT en TABLE vergrendelingen kunnen worden beïnvloed door de optie LOCK_ESCALATION van ALTER TABLE-.

² Aanvullende vergrendelingsbronnen zijn beschikbaar voor XACT vergrendelingsbronnen. Zie Diagnostische toevoegingen voor geoptimaliseerde vergrendeling.

Vergrendelingsmodi

De database-engine vergrendelt resources met verschillende vergrendelingsmodi die bepalen hoe de resources kunnen worden geopend door gelijktijdige transacties.

In de volgende tabel ziet u de resourcevergrendelingsmodi die door de database-engine worden gebruikt.

Vergrendelingsmodus	Beschrijving
Gedeeld (S)	Wordt gebruikt voor leesbewerkingen die geen gegevens wijzigen of bijwerken, zoals een SELECT-instructie.
bijwerken (U)	Wordt gebruikt voor bronnen die kunnen worden bijgewerkt. Voorkomt een veelvoorkomende impasse die optreedt wanneer meerdere sessies eerst lezen, vervolgens vergrendelen en daarna mogelijk de resources bijwerken.
Exclusive (X)	Wordt gebruikt voor bewerkingen voor gegevenswijziging, zoals INSERT, UPDATEof DELETE. Zorgt ervoor dat er niet tegelijkertijd meerdere updates kunnen worden aangebracht in dezelfde resource.
Intentie	Wordt gebruikt om een vergrendelingshiërarchie tot stand te brengen. De typen intentievergrendelingen zijn: intent shared (IS), intent exclusive (IX) en gedeeld met intent exclusive (SIX).
schema	Wordt gebruikt wanneer een bewerking wordt uitgevoerd die afhankelijk is van het schema van een tabel. De typen schemavergrendelingen zijn: schemawijziging (Sch-M) en schemastabiliteit (Sch-S).
bulkupdate (BU)	Wordt gebruikt bij het bulksgewijs kopiëren van gegevens naar een tabel met de TABLOCK hint.
sleutelbereik	Beveiligt het bereik van rijen dat wordt gelezen door een query wanneer het isolatieniveau SERIALIZABLE van de transactie wordt gebruikt. Zorgt ervoor dat andere transacties geen rijen kunnen invoegen die in aanmerking komen voor de query's van de SERIALIZABLE transactie als de query's opnieuw zijn uitgevoerd.

Gedeelde vergrendelingen

Met gedeelde vergrendelingen (S) kunnen gelijktijdige transacties een resource lezen onder pessimistisch gelijktijdigheidsbeheer. Er kunnen geen andere transacties de gegevens wijzigen terwijl gedeelde (S) vergrendelingen aanwezig zijn op de resource. Gedeelde (S) vergrendelingen voor een resource worden vrijgegeven zodra de leesbewerking is voltooid, tenzij het transactieisolatieniveau is ingesteld op REPEATABLE READ of hoger, of een vergrendelingshint wordt gebruikt om de gedeelde (S) vergrendelingen voor de duur van de transactie te behouden.

Vergrendelingen bijwerken

De Database Engine plaatst update (U)-vergrendelingen wanneer het zich voorbereidt op het uitvoeren van een update. U vergrendelingen zijn compatibel met S vergrendelingen, maar slechts één transactie kan een U vergrendeling tegelijk op een bepaalde resource bevatten. Dit is belangrijk: veel gelijktijdige transacties kunnen S vergrendelingen bevatten, maar slechts één transactie kan een U vergrendeling op een resource bevatten. Updatevergrendelingen (U) worden uiteindelijk bijgewerkt naar exclusieve (X) vergrendelingen om een rij bij te werken.

Update (U) vergrendelingen kunnen ook worden uitgevoerd door andere instructies dan UPDATE, wanneer de hint voor de UPDLOCK-tabel is opgegeven in de instructie.

• Sommige toepassingen gebruiken het patroon 'Selecteer een rij en werk vervolgens de rij bij', waarbij de lees- en schrijfbewerkingen expliciet worden gescheiden binnen de transactie. Als in dit geval het isolatieniveau is REPEATABLE READ of SERIALIZABLE, kunnen gelijktijdige updates een impasse veroorzaken, als volgt:

  Een transactie leest gegevens, verkrijgt een gedeelde (S) vergrendeling op de resource en wijzigt vervolgens de gegevens, waarvoor vergrendelingsconversie naar een exclusieve (X) vergrendeling is vereist. Als twee transacties gedeelde (S) vergrendelingen op een bron verkrijgen en vervolgens proberen gegevens gelijktijdig bij te werken, probeert één transactie de vergrendelingsconversie naar een exclusieve (X) vergrendeling. De conversie van gedeelde naar exclusieve vergrendeling moet wachten omdat de exclusieve (X) vergrendeling voor één transactie niet compatibel is met de gedeelde (S) vergrendeling van de andere transactie; een wacht op vergrendeling vindt plaats. De tweede transactie probeert een exclusieve (X) vergrendeling voor de update te verkrijgen. Omdat beide transacties worden geconverteerd naar exclusieve (X) vergrendelingen en ze wachten op de andere transactie om de gedeelde (S) vergrendeling vrij te geven, treedt er een impasse op.

  In het standaard READ COMMITTED isolatieniveau zijn S vergrendelingen korte duur, vrijgegeven zodra ze worden gebruikt. Hoewel de hierboven beschreven impasse nog steeds mogelijk is, is het veel minder waarschijnlijk met korte duurvergrendelingen.

  Om dit type impasse te voorkomen, kunnen toepassingen een patroon volgen waarbij ze "selecteer een rij met de hint UPDLOCK" gebruiken en vervolgens de rij bijwerken.

• Als bij een schrijfopdracht de UPDLOCK hint wordt gebruikt, terwijl de SNAPSHOT isolatie actief is, moet de transactie toegang hebben tot de nieuwste versie van de rij. Als de nieuwste versie niet meer zichtbaar is, is het mogelijk om Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflictte ontvangen. Zie bijvoorbeeld Werken met isolatie van momentopnamen.

Exclusieve sloten

Exclusieve (X) vergrendelingen verhinderen de toegang tot een resource door gelijktijdige transacties. Met een exclusieve (X) vergrendeling kunnen er geen andere transacties de gegevens wijzigen die door de vergrendeling worden beveiligd; leesbewerkingen kunnen alleen plaatsvinden met het gebruik van de NOLOCK hint of het READ UNCOMMITTED isolatieniveau.

Instructies voor gegevenswijziging, zoals INSERT, UPDATEen DELETE, bevatten gecombineerde lees- en wijzigingsbewerkingen. De instructie voert eerst leesbewerkingen uit om gegevens te verkrijgen voordat de vereiste wijzigingsbewerkingen worden uitgevoerd. Instructies voor gegevenswijziging vragen daarom doorgaans zowel gedeelde vergrendelingen als exclusieve vergrendelingen aan. Een UPDATE-instructie kan bijvoorbeeld rijen in de ene tabel wijzigen op basis van een join met een andere tabel. In dit geval vraagt de UPDATE-verklaring gedeelde vergrendelingen aan op de rijen die in de join-tabel worden gelezen, en vraagt tevens om exclusieve vergrendelingen op de bijgewerkte rijen.

Intentievergrendelingen

De database-engine gebruikt intentievergrendelingen om het plaatsen van een gedeelde (S) vergrendeling of exclusieve (X) vergrendeling op een resource lager in de vergrendelingshiërarchie te beveiligen. Intentievergrendelingen worden 'intentievergrendelingen' genoemd omdat ze worden verkregen vóór een vergrendeling op een lager niveau en daarom de intentie aangeven om vergrendelingen op een lager niveau te plaatsen.

Intentievergrendelingen dienen twee doeleinden:

• Om te voorkomen dat andere transacties de resource op een hoger niveau op een manier wijzigen die de vergrendeling op een lager niveau ongeldig maakt.
• Om de efficiëntie van de database-engine te verbeteren bij het detecteren van vergrendelingsconflicten op een hoger granulariteitsniveau.

Een gedeelde intentievergrendeling wordt bijvoorbeeld aangevraagd op tabelniveau voordat gedeelde (S) vergrendelingen worden aangevraagd op pagina's of rijen in die tabel. Als u een intentievergrendeling instelt op tabelniveau, voorkomt u dat een andere transactie vervolgens een exclusieve (X) vergrendeling op de tabel met die pagina kan verkrijgen. Intentievergrendelingen verbeteren de prestaties omdat de database-engine intentievergrendelingen alleen op tabelniveau onderzoekt om te bepalen of een transactie veilig een vergrendeling op die tabel kan verkrijgen. Hiermee verwijdert u de vereiste om elke rij- of paginavergrendeling in de tabel te onderzoeken om te bepalen of een transactie de hele tabel kan vergrendelen.

intentievergrendelingen omvatten intentie gedeeld (IS), intentie exclusief (IX) en gedeeld met intentie-exclusief (SIX).

Vergrendelingsmodus	Beschrijving
gedeelde intentie (IS)	Beschermt aangevraagde of verkregen gedeelde vergrendelingen op sommige (maar niet alle) resources lager in de hiërarchie.
Intent exclusive (IX)	Beschermt aangevraagde of aangeschafte exclusieve vergrendelingen op sommige (maar niet alle) resources die lager in de hiërarchie zijn. IX is een superset van ISen beschermt bovendien het aanvragen van gedeelde vergrendelingen op resources op een lager niveau.
gedeeld met exclusieve intentie (SIX)	Beschermt aangevraagde of verkregen gedeelde vergrendelingen op alle bronnen lager in de hiërarchie en intentie-exclusieve vergrendelingen op sommige, maar niet alle, bronnen op een lager niveau. Gelijktijdige IS vergrendelingen op het hoogste niveau zijn toegestaan. Als u bijvoorbeeld een SIX-vergrendeling op een tabel verkrijgt, worden ook intentie-exclusieve vergrendelingen verkregen op de pagina's die worden gewijzigd en exclusieve vergrendelingen op de gewijzigde rijen. Er kan slechts één SIX vergrendeling per resource tegelijk zijn, waardoor updates voor de resource die door andere transacties worden uitgevoerd, worden voorkomen, hoewel andere transacties resources lager in de hiërarchie kunnen lezen door IS vergrendelingen op tabelniveau te verkrijgen.
intentie-update (IU)	Beveiligt aangevraagde of aangeschafte updatevergrendelingen op alle resources lager in de hiërarchie. IU vergrendelingen worden alleen gebruikt op paginabronnen. IU vergrendelingen worden geconverteerd naar IX vergrendelingen als er een updatebewerking plaatsvindt.
update van gedeelde intentie (SIU)	Een combinatie van S en IU sloten, als gevolg van het afzonderlijk verkrijgen van deze vergrendelingen en tegelijkertijd het vasthouden van beide vergrendelingen. Een transactie voert bijvoorbeeld een query uit met de PAGLOCK hint en voert vervolgens een updatebewerking uit. De query met de PAGLOCK hint verkrijgt de S-vergrendeling en de updatebewerking verkrijgt de IU-vergrendeling.
Intentie bijwerken exclusief (UIX)	Een combinatie van U en IX sloten, als gevolg van het afzonderlijk verkrijgen van deze vergrendelingen en tegelijkertijd het vasthouden van beide vergrendelingen.

Schemavergrendelingen

De Database Engine gebruikt schema-aanpassings (Sch-M) vergrendelingen tijdens een Data Definition Language (DDL) bewerking, zoals het toevoegen van een kolom of het verwijderen van een tabel. Tijdens de tijd dat deze wordt bewaard, voorkomt de Sch-M-vergrendeling gelijktijdige toegang tot de tabel. Dit betekent dat de Sch-M vergrendeling alle externe bewerkingen blokkeert totdat de vergrendeling wordt vrijgegeven.

Sommige DML-bewerkingen (Data Manipulat Language), zoals het afkappen van tabellen, gebruiken Sch-M vergrendelingen om toegang tot betrokken tabellen door gelijktijdige bewerkingen te voorkomen.

De Database Engine gebruikt schemastabiliteit (Sch-S) vergrendelingen bij het compileren en uitvoeren van query's. Sch-S vergrendelingen blokkeren geen enkele soort transactionele vergrendeling, inclusief exclusieve vergrendelingen (X). Daarom blijven andere transacties, waaronder transacties met X vergrendelingen op een tabel, actief terwijl een query wordt gecompileerd. Gelijktijdige DDL-bewerkingen en gelijktijdige DML-bewerkingen die Sch-M vergrendelingen verkrijgen, worden echter geblokkeerd door de Sch-S vergrendelingen.

Bulkvergrendelingen bijwerken

Met vergrendelingen voor het bijwerken in bulkmodus (BU) kunnen meerdere threads gelijktijdig data in dezelfde tabel laden, terwijl ze voorkomen dat andere processen, die geen data in bulkmodus laden, toegang krijgen tot de tabel. De Database Engine maakt gebruik van bulk-update (BU) vergrendelingen wanneer aan beide volgende voorwaarden is voldaan.

• U gebruikt de Transact-SQL BULK INSERT-instructie of de functie OPENROWSET(BULK), of u gebruikt een van de API-opdrachten bulksgewijs invoegen, zoals .NET SqlBulkCopy, OLEDB Fast Load-API's of de ODBC-API's voor bulksgewijs kopiëren van gegevens naar een tabel.
• De TABLOCK hint is opgegeven of de table lock on bulk load tabeloptie is ingesteld met behulp van sp_tableoption.

Tip

In tegenstelling tot de instructie BULK INSERT, die een minder beperkende bulkupdate (BU) vergrendeling bevat, bevat INSERT INTO...SELECT met de TABLOCK hint een intentie-exclusieve (IX) vergrendeling op de tabel. Dit betekent dat u geen rijen kunt invoegen met behulp van parallelle invoegbewerkingen.

Sleutelbereikvergrendelingen

Sleutelbereikvergrendelingen beschermen een reeks rijen die impliciet zijn opgenomen in een recordset die worden gelezen door een Transact-SQL instructie terwijl het SERIALIZABLE transactieisolatieniveau wordt gebruikt. Vergrendeling van sleutelbereik voorkomt fantoomleesacties. Door het beveiligen van de sleutelbereiken tussen de rijen voorkomt het ook fantoominvoegingen of verwijderingen in een recordset die wordt geopend door een transactie.

Vergrendelingscompatibiliteit

Vergrendelingscompatibiliteit bepaalt of meerdere transacties tegelijkertijd vergrendelingen op dezelfde resource kunnen verkrijgen. Als een resource al is vergrendeld door een andere transactie, kan alleen een nieuwe vergrendelingsaanvraag worden verleend als de modus van de aangevraagde vergrendeling compatibel is met de modus van de bestaande vergrendeling. Als de modus van de aangevraagde vergrendeling niet compatibel is met de bestaande vergrendeling, wacht de transactie die de nieuwe vergrendeling aanvraagt tot de bestaande vergrendeling wordt vrijgegeven of het time-outinterval voor de vergrendeling verloopt. Zo zijn er geen vergrendelingsmodi compatibel met exclusieve vergrendelingen. Hoewel een exclusieve (X) vergrendeling wordt bewaard, kan er geen andere transactie een vergrendeling van elk type (gedeeld, bijgewerkt of exclusief) op die resource verkrijgen totdat de exclusieve (X) vergrendeling wordt vrijgegeven. Als een gedeelde (S) vergrendeling is toegepast op een resource, kunnen andere transacties ook een gedeelde vergrendeling of een update (U) op die resource verkrijgen, zelfs als de eerste transactie niet is voltooid. Andere transacties kunnen echter geen exclusieve vergrendeling verkrijgen totdat de gedeelde vergrendeling is vrijgegeven.

In de volgende tabel ziet u de compatibiliteit van de meest voorkomende vergrendelingsmodi.

Bestaande verleende modus	IS	S	U	IX	SIX	X
aangevraagde modus
gedeelde intentie (IS)	Ja	Ja	Ja	Ja	Ja	Nee
Gedeeld (S)	Ja	Ja	Ja	Nee	Nee	Nee
bijwerken (U)	Ja	Ja	Nee	Nee	Nee	Nee
Intent exclusive (IX)	Ja	Nee	Nee	Ja	Nee	Nee
gedeeld met exclusieve intentie (SIX)	Ja	Nee	Nee	Nee	Nee	Nee
Exclusive (X)	Nee	Nee	Nee	Nee	Nee	Nee

Notitie

Een intentie-exclusieve (IX) vergrendeling is compatibel met een IX vergrendelingsmodus, omdat IX betekent dat het de bedoeling is om slechts enkele rijen bij te werken in plaats van alle rijen. Andere transacties die proberen sommige rijen te lezen of bij te werken, zijn ook toegestaan zolang ze niet dezelfde rijen zijn die door andere transacties worden bijgewerkt. Als twee transacties dezelfde rij proberen bij te werken, krijgen beide transacties een IX-vergrendeling op tabel- en paginaniveau. Aan één transactie wordt echter een X-vergrendeling op rijniveau verleend. De andere transactie moet wachten totdat de vergrendeling op rijniveau is verwijderd.

Gebruik de volgende tabel om de compatibiliteit te bepalen van alle vergrendelingsmodi die beschikbaar zijn in de database-engine.

Sleutel	Beschrijving
N	Geen conflict
Ik	Illegaal
C	Conflict
NL	Geen vergrendeling
SCH-S	Vergrendeling van schemastabiliteit
SCH-M	Schemawijzigingsvergrendeling
S	Gedeeld
U	Bijwerken
X	Exclusief
IS	Intentie gedeeld
IU	Update van intentie
IX	Intentie exclusief
SIU	Delen met intentie-update
ZES	Delen met een exclusieve bedoeling
UIX	Bijwerken met exclusieve intentie
BU	Bulksgewijs bijwerken
RS-S	Gedeeld bereik
RS-U	Gedeelde bereik-update
RI-N	Bereik-null invoegen
RI-S	Gedeeld bereik invoegen
RI-U	Bereikupdate invoegen
RI-X	Bereik exclusief invoegen
RX-S	Exclusief gedeeld bereik
RX-U	Exclusieve assortiment-update
RX-X	Exclusief assortiment

Sleutelbereikvergrendeling

Sleutelbereikvergrendelingen beschermen een reeks rijen die impliciet zijn opgenomen in een recordset die worden gelezen door een Transact-SQL instructie terwijl het SERIALIZABLE transactieisolatieniveau wordt gebruikt. Het SERIALIZABLE isolatieniveau vereist dat elke query die tijdens een transactie wordt uitgevoerd, dezelfde set rijen moet verkrijgen telkens wanneer deze wordt uitgevoerd tijdens de transactie. Een sleutelbereikvergrendeling voldoet aan deze vereiste door te voorkomen dat andere transacties nieuwe rijen invoegen waarvan de sleutels in het bereik van sleutels zouden vallen dat door de SERIALIZABLE transactie wordt gelezen.

Vergrendeling van sleutelbereik voorkomt fantoomleesacties. Door de reikwijdtes van sleutels tussen rijen te beveiligen, voorkomt het ook fantoominvoegingen in een set records waar een transactie toegang tot heeft.

Een sleutelbereikvergrendeling wordt op een index geplaatst, waarbij een begin- en eindsleutelwaarde wordt opgegeven. Deze vergrendeling blokkeert pogingen om een rij in te voegen, bij te werken of te verwijderen met een sleutelwaarde die binnen het bereik valt, omdat deze bewerkingen eerst een vergrendeling op de index moeten verkrijgen. Een SERIALIZABLE transactie kan bijvoorbeeld een SELECT-instructie uitgeven waarmee alle rijen worden gelezen waarvan de sleutelwaarden overeenkomen met de voorwaarde BETWEEN 'AAA' AND 'CZZ'. Een sleutelbereikvergrendeling op de sleutelwaarden in het bereik van 'AAA-' tot 'CZZ-' voorkomt dat andere transacties rijen met sleutelwaarden overal in dat bereik invoegen, zoals 'ADG', 'BBD'of 'CAL'.

Vergrendelingsmodi voor sleutelbereik

Sleutelbereikvergrendelingen bevatten zowel een bereik als een rijonderdeel dat is opgegeven in bereik-rijformaat:

• Het bereik vertegenwoordigt de vergrendelingsmodus die het bereik tussen twee opeenvolgende indexvermeldingen beveiligt.
• De rij vertegenwoordigt de vergrendelingsmodus die de indexvermelding beveiligt.
• De modus vertegenwoordigt de gecombineerde vergrendelingsmodus die wordt gebruikt. Sleutelbereikvergrendelingsmodi bestaan uit twee delen. De eerste vertegenwoordigt het type vergrendeling dat wordt gebruikt voor het vergrendelen van het indexbereik (bereikT) en het tweede staat voor het vergrendelingstype dat wordt gebruikt om een specifieke sleutel (K) te vergrendelen). De twee delen zijn verbonden met een afbreekstreepje (-), zoals RangeT-K.

Bereik	Rij	Wijze	Beschrijving
RangeS	S	RangeS-S	Gedeeld bereik en gedeelde bronvergrendeling; SERIALIZABLE bereikscan.
RangeS	U	RangeS-U	Gedeeld bereik, bijwerken van resourcevergrendeling; SERIALIZABLE scan bijwerken.
RangeI	Null	RangeI-N	Bereik invoegen, null-resourcevergrendeling; wordt gebruikt om bereiken te testen voordat u een nieuwe sleutel in een index invoegt.
RangeX	X	RangeX-X	Exclusief bereik, de exclusieve bronvergrendeling; wordt gebruikt bij het bijwerken van een sleutel in een bereik.

Notitie

De interne Null-vergrendelingsmodus is compatibel met alle andere vergrendelingsmodi.

Sleutelbereikvergrendelingsmodi hebben een compatibiliteitsmatrix die laat zien welke vergrendelingen compatibel zijn met andere vergrendelingen die zijn verkregen op overlappende sleutels en bereiken.

Bestaande verleende modus	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
aangevraagde modus
Gedeeld (S)	Ja	Ja	Nee	Ja	Ja	Ja	Nee
bijwerken (U)	Ja	Nee	Nee	Ja	Nee	Ja	Nee
Exclusive (X)	Nee	Nee	Nee	Nee	Nee	Ja	Nee
RangeS-S	Ja	Ja	Nee	Ja	Ja	Nee	Nee
RangeS-U	Ja	Nee	Nee	Ja	Nee	Nee	Nee
RangeI-N	Ja	Ja	Ja	Nee	Nee	Ja	Nee
RangeX-X	Nee	Nee	Nee	Nee	Nee	Nee	Nee

Conversievergrendelingen

Conversievergrendelingen worden gemaakt wanneer een sleutelbereikvergrendeling een andere vergrendeling overlapt.

Vergrendelen 1	Vergrendelen 2	Conversievergrendeling
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

Conversievergrendelingen kunnen gedurende korte tijd worden waargenomen onder verschillende complexe omstandigheden, soms tijdens het uitvoeren van gelijktijdige processen.

Serialiseerbare bereikscan, singleton ophalen, verwijderen en invoegen.

Vergrendelen van sleutelbereik zorgt ervoor dat de volgende bewerkingen serialiseerbaar zijn:

• Bereikzoekopdracht
• Singleton-ophaal van niet-bestaande rij
• Bewerking verwijderen
• Bewerking invoegen

Voordat sleutelbereikvergrendeling kan plaatsvinden, moeten aan de volgende voorwaarden worden voldaan:

• Het niveau van transactieisolatie moet worden ingesteld op SERIALIZABLE.
• De queryprocessor moet een index gebruiken om het bereikfilterpredicaat te implementeren. De WHERE-clausule in een SELECT-instructie kan bijvoorbeeld een bereikvoorwaarde met dit predicaat instellen: ColumnX BETWEEN N'AAA' AND N'CZZ'. Een sleutelbereikvergrendeling kan alleen worden verkregen als ColumnX wordt gedekt door een indexsleutel.

Voorbeelden

De volgende tabel en index worden gebruikt als basis voor de voorbeelden van sleutelbereikvergrendeling die volgen.

Bereikzoekopdracht

Om ervoor te zorgen dat een bereikscanquery serialiseerbaar is, moet dezelfde query dezelfde resultaten retourneren telkens wanneer deze wordt uitgevoerd binnen dezelfde transactie. Nieuwe rijen mogen niet worden ingevoegd binnen het bereik van de scanquery door andere transacties; anders ontstaan er fantoominvoegingen. De volgende query maakt bijvoorbeeld gebruik van de tabel en index in de vorige afbeelding:

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

Sleutelbereikvergrendelingen worden op de indexvermeldingen geplaatst die overeenkomen met het bereik van rijen waarin de naam zich bevindt tussen de waarden Adam en Dale, waardoor nieuwe rijen die in de vorige query in aanmerking komen, niet kunnen worden toegevoegd of verwijderd. Hoewel de voornaam in dit bereik Adamis, zorgt de RangeS-S modustoetsbereikvergrendeling voor deze indexvermelding ervoor dat er geen nieuwe namen kunnen worden toegevoegd die beginnen met de letter A kunnen worden toegevoegd voordat Adam, zoals Abigail. Op dezelfde manier zorgt de RangeS-S sleutelbereikvergrendeling voor de indexvermelding voor Dale ervoor dat er geen nieuwe namen kunnen worden toegevoegd die beginnen met de letter C na Carlos, zoals Clive.

Notitie

Het aantal RangeS-S vergrendelingen dat wordt vastgehouden is n+1, waarbij n het aantal rijen is dat aan de query voldoet.

Singleton opvragen van niet bestaande gegevens

Als een query binnen een transactie probeert een rij te selecteren die niet bestaat, moet de query op een later tijdstip binnen dezelfde transactie hetzelfde resultaat retourneren. Er kan geen andere transactie worden toegestaan om die niet-bestaande rij in te voegen. Bijvoorbeeld, op de volgende query:

SELECT name
FROM mytable
WHERE name = 'Bill';

Er wordt een sleutelbereikvergrendeling geplaatst op de indexvermelding die overeenkomt met het naambereik van Ben tot Bing omdat de naam Bill tussen deze twee aangrenzende indexvermeldingen wordt ingevoegd. De grendel voor het toetsbereik van modus RangeS-S is geplaatst op de indexinvoer Bing. Dit voorkomt dat andere transacties waarden, zoals Bill, invoegen tussen de indexvermeldingen Ben en Bing.

Verwijderbewerking zonder geoptimaliseerde vergrendeling

Wanneer u een rij binnen een transactie verwijdert, hoeft het bereik van de rij niet te worden vergrendeld voor de duur van de transactie die de verwijderbewerking uitvoert. Het vergrendelen van de waarde van de verwijderde sleutel tot het einde van de transactie is voldoende om serialisatie te behouden. Bijvoorbeeld, gezien deze DELETE-verklaring:

DELETE mytable
WHERE name = 'Bob';

Een exclusieve (X) vergrendeling wordt geplaatst op de indexvermelding die overeenkomt met de naam Bob. Andere transacties kunnen waarden invoegen of verwijderen vóór of na de rij met de waarde Bob die wordt verwijderd. Elke transactie die rijen probeert te lezen, in te voegen of te verwijderen die overeenkomen met de waarde Bob wordt echter geblokkeerd totdat de verwijderende transactie is voltooid of is teruggedraaid. De READ_COMMITTED_SNAPSHOT-databaseoptie en het SNAPSHOT-isolatieniveau staan ook toe dat er gelezen wordt van een rijversie van de eerder vastgelegde staat.

Het verwijderen van het bereik kan worden uitgevoerd met behulp van drie basisvergrendelingsmodi: rij-, pagina- of tabelvergrendeling. De strategie voor het vergrendelen van rijen, pagina's of tabellen wordt bepaald door Query Optimizer of kan door de gebruiker worden opgegeven via hints voor queryoptimalisatie, zoals ROWLOCK, PAGLOCKof TABLOCK. Wanneer PAGLOCK of TABLOCK wordt gebruikt, geeft de database-engine onmiddellijk een indexpagina vrij als alle rijen op deze pagina zijn verwijderd. Wanneer ROWLOCK daarentegen wordt gebruikt, worden alle verwijderde rijen alleen gemarkeerd als verwijderd; ze worden later verwijderd van de indexpagina met behulp van een achtergrondtaak.

Verwijderbewerking met geoptimaliseerde vergrendeling

Wanneer u een rij binnen een transactie verwijdert, worden de rij- en paginavergrendelingen incrementeel verkregen en vrijgegeven en niet bewaard voor de duur van de transactie. Bijvoorbeeld, gezien deze DELETE-instructie:

DELETE mytable
WHERE name = 'Bob';

Een TID-vergrendeling wordt voor de duur van de transactie op alle gewijzigde rijen geplaatst. Er wordt een vergrendeling verkregen op de TID van de indexrijen die overeenkomstig zijn met de waarde Bob. Met geoptimaliseerde vergrendeling blijven pagina- en rijvergrendelingen worden verkregen voor updates, maar elke pagina- en rijvergrendeling wordt vrijgegeven zodra elke rij wordt bijgewerkt. De TID-vergrendeling beschermt de rijen tegen het bijwerken totdat de transactie is voltooid. Transacties die proberen rijen met de waarde Bob te lezen, in te voegen of te verwijderen, worden geblokkeerd totdat de transactie ofwel is voltooid of is teruggedraaid. De READ_COMMITTED_SNAPSHOT-databaseoptie en het SNAPSHOT-isolatieniveau staan ook toe dat er gelezen wordt van een rijversie van de eerder vastgelegde staat.

Anders zijn de vergrendelingsmechanica van een verwijderbewerking hetzelfde als zonder geoptimaliseerde vergrendeling.

Bewerking invoegen zonder geoptimaliseerde vergrendeling

Wanneer u een rij in een transactie invoegt, hoeft het bereik van de rij niet te worden vergrendeld voor de duur van de transactie die de invoegbewerking uitvoert. Het vergrendelen van de ingevoegde sleutelwaarde tot het einde van de transactie is voldoende om serialisatie te behouden. Bijvoorbeeld, gegeven deze INSERT-instructie:

INSERT mytable VALUES ('Dan');

De RangeI-N modus sleutelbereikvergrendeling wordt op de indexrij geplaatst die overeenkomt met de naam David om het bereik te testen. Als de vergrendeling wordt verleend, wordt een rij met de waarde Dan ingevoegd en wordt een exclusieve (X) vergrendeling op de ingevoegde rij geplaatst. De RangeI-N modus sleutelbereikvergrendeling is alleen nodig om het bereik te testen en wordt niet bewaard voor de duur van de transactie die de invoegbewerking uitvoert. Andere transacties kunnen waarden invoegen of verwijderen vóór of na de ingevoegde rij met de waarde Dan. Echter, elke transactie die probeert de rij met de waarde Dan te lezen, in te voegen of te verwijderen, wordt geblokkeerd totdat de transactie die de rij invoegt deze doorvoert of terugdraait.

Invoegbewerking met geoptimaliseerde vergrendeling

Wanneer u een rij in een transactie invoegt, hoeft het bereik van de rij niet te worden vergrendeld voor de duur van de transactie die de invoegbewerking uitvoert. Rij- en paginavergrendelingen worden zelden verkregen, alleen wanneer er een online index opnieuw wordt opgebouwd of wanneer er gelijktijdige SERIALIZABLE transacties zijn. Als rij- en paginavergrendelingen worden verkregen, worden ze snel vrijgegeven en niet bewaard voor de duur van de transactie. Het plaatsen van een exclusieve TID-vergrendeling op de ingevoegde sleutelwaarde tot het einde van de transactie volstaat om de serialisatie te behouden. Bijvoorbeeld, gezien deze INSERT-verklaring:

INSERT mytable VALUES ('Dan');

Met geoptimaliseerde vergrendeling wordt een RangeI-N-vergrendeling alleen verkregen als er ten minste één transactie is die gebruikmaakt van het SERIALIZABLE-isolatieniveau in de instantie. De RangeI-N modus sleutelbereikvergrendeling wordt op de indexrij geplaatst die overeenkomt met de naam David om het bereik te testen. Als de vergrendeling wordt verleend, wordt een rij met de waarde Dan ingevoegd en wordt een exclusieve (X) vergrendeling op de ingevoegde rij geplaatst. De RangeI-N modus sleutelbereikvergrendeling is alleen nodig om het bereik te testen en wordt niet bewaard voor de duur van de transactie die de invoegbewerking uitvoert. Andere transacties kunnen waarden invoegen of verwijderen vóór of na de ingevoegde rij met de waarde Dan. Echter, elke transactie die probeert de rij met de waarde Dan te lezen, in te voegen of te verwijderen, wordt geblokkeerd totdat de transactie die de rij invoegt deze doorvoert of terugdraait.

Escalatie vergrendelen

Escalatie van vergrendelingen is het proces van het converteren van veel fijnmazige vergrendelingen naar minder grofkorrelige vergrendelingen, waardoor de systeemoverhead wordt verminderd terwijl de kans op gelijktijdigheidsconflicten wordt verhoogd.

Escalatie van vergrendeling gedraagt zich anders, afhankelijk van of geoptimaliseerde vergrendeling is ingeschakeld.

Escalatie van vergrendeling zonder geoptimaliseerde vergrendelingen

Wanneer de Database Engine lage-niveau vergrendelingen verkrijgt, plaatst het ook intentievergrendelingen op de objecten die de lagere-niveau objecten bevatten.

• Bij het vergrendelen van rijen of indexsleutelbereiken plaatst de database-engine een intentievergrendeling op de pagina's die de rijen of sleutels bevatten.
• Bij het vergrendelen van pagina's plaatst de database-engine een intentievergrendeling op de objecten op een hoger niveau die de pagina's bevatten. Naast intentievergrendeling voor het object worden paginavergrendelingen voor intenties aangevraagd op de volgende objecten:
  • Pagina's op bladniveau van niet-geclusterde indexen
  • Gegevenspagina's van geclusterde indexen
  • Heap-gegevenspagina's

De database-engine kan zowel rij- als paginavergrendelingen uitvoeren voor dezelfde instructie om het aantal vergrendelingen te minimaliseren en de kans te verminderen dat escalatie van vergrendelingen noodzakelijk is. De database-engine kan bijvoorbeeld paginavergrendelingen plaatsen op een niet-geclusterde index (als er voldoende aaneengesloten sleutels in het indexknooppunt zijn geselecteerd om aan de query te voldoen) en rijvergrendelingen op de geclusterde index of heap.

Als u vergrendelingen wilt escaleren, probeert de Database Engine de intentievergrendeling op de tabel te wijzigen in de bijbehorende volledige vergrendeling, bijvoorbeeld door een exclusieve (IX) vergrendeling te wijzigen in een exclusieve vergrendeling (X) of een intentievergrendeling (IS) te wijzigen in een gedeelde (S) vergrendeling. Als de escalatiepoging van de vergrendeling slaagt en de volledige tabelvergrendeling wordt verkregen, worden alle HoBT-, pagina- (PAGE) of rijniveau (RID, KEY) vergrendelingen vrijgegeven die door de transactie op de heap of index worden gehouden. Als de volledige vergrendeling niet kan worden verkregen, vindt er op dat moment geen escalatie van de vergrendeling plaats en blijft de database-engine rij-, sleutel- of paginavergrendelingen verkrijgen.

De database-engine escaleert geen rij- of sleutelbereikvergrendelingen naar paginavergrendelingen, maar escaleert ze rechtstreeks naar tabelvergrendelingen. Op dezelfde manier worden paginavergrendelingen altijd geëscaleerd naar tabelvergrendelingen. Het vergrendelen van gepartitioneerde tabellen kan escaleren naar het HoBT-niveau voor de gekoppelde partitie in plaats van naar de tabelvergrendeling. Een HoBT-niveauvergrendeling vergrendelt niet noodzakelijkerwijs de op één lijn staande HoBT's voor de partitie.

Notitie

Vergrendelingen op HoBT-niveau verhogen meestal de gelijktijdigheid, maar introduceren de kans op impasses wanneer transacties die verschillende partities vergrendelen, elk hun exclusieve vergrendelingen naar de andere partities willen uitbreiden. In zeldzame gevallen kan TABLE het vergrendelen van granulariteit beter presteren.

Als een escalatiepoging van vergrendeling mislukt vanwege conflicterende vergrendelingen die door gelijktijdige transacties worden bewaard, probeert de Database Engine de escalatie van de vergrendeling opnieuw voor elke extra 1250 vergrendelingen die zijn verkregen door de transactie.

Elke escalatiegebeurtenis werkt voornamelijk op het niveau van één Transact-SQL-instructie. Wanneer de gebeurtenis wordt gestart, probeert de database-engine alle vergrendelingen die eigendom zijn van de huidige transactie te escaleren in een van de tabellen waarnaar is verwezen door de actieve instructie, mits deze voldoet aan de escalatiedrempelvereisten. Als de escalatiegebeurtenis begint voordat het statement een tabel benaderd heeft, wordt er geen poging gedaan om de vergrendelingen op die tabel te escaleren. Als de escalatie van een vergrendeling slaagt, worden de vergrendelingen die door de transactie in een eerder statement zijn verkregen en op het moment dat de gebeurtenis start nog steeds worden vastgehouden, geëscaleerd als de tabel door het huidige statement wordt verwezen en in de escalatiegebeurtenis is opgenomen.

Stel dat een sessie deze bewerkingen uitvoert:

• Begint een transactie.
• Updates TableA. Hiermee worden exclusieve rijvergrendelingen gegenereerd in TableA die worden bewaard totdat de transactie is voltooid.
• Updates TableB. Hiermee worden exclusieve rijvergrendelingen gegenereerd in TableB die worden bewaard totdat de transactie is voltooid.
• Voert een SELECT uit waarmee TableA wordt samengevoegd met TableC. Het queryuitvoeringsplan roept aan dat de rijen worden opgehaald uit TableA voordat de rijen worden opgehaald uit TableC.
• De SELECT-instructie activeert een escalatie van vergrendeling terwijl het rijen ophaalt uit TableA en voordat het toegang heeft verkregen tot TableC.

Als escalatie van vergrendelingen slaagt, worden alleen de vergrendelingen die door de sessie op TableA worden gehouden geëscaleerd. Dit omvat zowel de gedeelde vergrendelingen van de SELECT-instructie als de exclusieve vergrendelingen van de vorige UPDATE-instructie. Hoewel alleen de vergrendelingen die de sessie in TableA voor de SELECT instructie heeft verkregen, worden meegeteld om te bepalen of lock-escalatie moet plaatsvinden, worden, zodra escalatie succesvol is, alle vergrendelingen door de sessie in TableA bewaard geëscaleerd naar een exclusieve vergrendeling die op de tabel wordt toegepast, terwijl alle andere vergrendelingen met een lagere granulariteit, inclusief intentievergrendelingen, op TableA worden vrijgegeven.

Er wordt geen poging gedaan om vergrendelingen op TableB te escaleren omdat er geen actieve verwijzing naar TableB in de SELECT-instructie was. Er wordt ook geen poging gedaan om de vergrendelingen op TableCte escaleren, die niet zijn geëscaleerd omdat deze nog niet was geopend toen de escalatie plaatsvond.

Vergrendelingsescalatie met geoptimaliseerd vergrendelen

Geoptimaliseerde vergrendeling helpt bij het verminderen van het vergrendelingsgeheugen, omdat er zeer weinig vergrendelingen worden bewaard voor de duur van de transactie. Omdat de database-engine rij- en paginavergrendelingen verkrijgt, kan escalatie van vergrendelingen op dezelfde manier optreden, maar veel minder vaak. Geoptimaliseerde vergrendeling slaagt er meestal in om escalaties van vergrendelingen te voorkomen, waardoor het aantal vergrendelingen en de hoeveelheid geheugen die nodig is, wordt verlaagd.

Wanneer geoptimaliseerde vergrendeling is ingeschakeld en op het standaard READ COMMITTED-isolatieniveau, geeft de database-engine rij- en paginavergrendelingen vrij zodra de rij is gewijzigd. Er worden geen rij- en paginavergrendelingen bewaard voor de duur van de transactie, met uitzondering van één TID-vergrendeling (Transaction ID). Dit vermindert de kans op escalatie van vergrendelingen.

Drempels voor vergrendelingsescalatie

Escalatie van vergrendeling wordt geactiveerd wanneer escalatie van vergrendeling niet is uitgeschakeld in de tabel met behulp van de optie ALTER TABLE SET LOCK_ESCALATION en wanneer een van de volgende voorwaarden bestaat:

• Eén Transact-SQL-instructie verkrijgt ten minste 5000 vergrendelingen voor één niet-gepartitioneerde tabel of index.
• Als de optie ALTER TABLE SET LOCK_ESCALATION is ingesteld op AUTO, verkrijgt een enkele Transact-SQL instructie ten minste 5.000 vergrendelingen op één partitie van een gepartitioneerde tabel.
• Het aantal vergrendelingen in een exemplaar van de database-engine overschrijdt de geheugen- of configuratiedrempels.

Als vergrendelingen niet kunnen worden geëscaleerd vanwege vergrendelingsconflicten, activeert de database-engine periodiek escalatie van vergrendelingen bij elke 1250 nieuwe vergrendelingen die zijn verkregen.

Escalatiedrempel voor een Transact-SQL-instructie

Wanneer de Database Engine controleert op mogelijke escalaties bij elke 1.250 nieuw verkregen locks, treedt er alleen een lock-escalatie op als een Transact-SQL-instructie ten minste 5.000 locks heeft verkregen op een enkele verwijzing naar een tabel. Escalatie van vergrendeling wordt geactiveerd wanneer een Transact-SQL-instructie ten minste 5000 vergrendelingen verkrijgt op één verwijzing van een tabel. Vergrendelings escalatie wordt bijvoorbeeld niet geactiveerd als een instructie 3000 vergrendelingen in één index verkrijgt en 3000 vergrendelingen in een andere index van dezelfde tabel. Op dezelfde manier wordt escalatie van vergrendelingen niet geactiveerd als een instructie een self-join voor een tabel heeft en elke verwijzing naar de tabel alleen 3000 vergrendelingen in de tabel verkrijgt.

Escalatie van vergrendeling vindt alleen plaats voor tabellen die toegankelijk zijn op het moment dat de escalatie wordt geactiveerd. Stel dat één SELECT-instructie een join is die in deze volgorde toegang heeft tot drie tabellen: TableA, TableBen TableC. De instructie krijgt 3.000 rijvergrendelingen in de geclusterde index voor TableA en ten minste 5.000 rijvergrendelingen in de geclusterde index voor TableB, maar heeft nog geen toegang gehad tot TableC. Wanneer de database-engine detecteert dat de instructie ten minste 5.000 rijvergrendelingen heeft verkregen in TableB, wordt geprobeerd alle vergrendelingen die door de huidige transactie op TableBworden bewaard, te escaleren. Het probeert ook alle vergrendelingen die door de huidige transactie op TableAworden vastgehouden te verhogen, maar omdat het aantal vergrendelingen op TableA minder dan 5.000 is, zal de escalatie niet slagen. Er wordt geen escalatie van vergrendelingen uitgevoerd voor TableC omdat deze nog niet is geopend toen de escalatie plaatsvond.

Escalatiedrempel voor een exemplaar van de database-engine

Wanneer het aantal vergrendelingen groter is dan de geheugendrempel voor escalatie van vergrendelingen, activeert de database-engine escalatie van vergrendelingen. De geheugendrempel is afhankelijk van de instelling van de vergrendeling configuratieoptie:

• Als de locks optie is ingesteld op de standaardinstelling van 0, wordt de drempelwaarde voor escalatie van vergrendeling bereikt wanneer het geheugen dat wordt gebruikt door vergrendelingsobjecten 24 procent is van het geheugen dat door de database-engine wordt gebruikt, met uitzondering van AWE-geheugen. De gegevensstructuur die wordt gebruikt om een vergrendeling weer te geven, is ongeveer 100 bytes lang. Deze drempelwaarde is dynamisch omdat de database-engine dynamisch geheugen verkrijgt en vrij maakt om aan te passen voor verschillende werkbelastingen.

• Als de optie locks een andere waarde is dan 0, is de drempelwaarde voor escalatie van vergrendeling 40 procent (of minder als er een geheugendruk is) van de waarde van de optie Vergrendelingen.

De database-engine kan elke actieve instructies kiezen uit elke sessie voor escalatie en voor elke 1250 nieuwe vergrendelingen worden instructies gekozen voor escalatie zolang het vergrendelingsgeheugen dat door het exemplaar wordt gebruikt, boven de drempelwaarde blijft.

Escalatie van vergrendelingsniveau met gemengde vergrendelingstypen

Wanneer escalatie van vergrendeling plaatsvindt, is de vergrendeling die is geselecteerd voor de heap of index sterk genoeg om te voldoen aan de vereisten van de meest beperkende vergrendeling op lager niveau.

Stel een sessie voor:

• Begint een transactie.
• Hiermee wordt een tabel bijgewerkt die een geclusterde index bevat.
• Geeft een SELECT instructie die verwijst naar dezelfde tabel.

De UPDATE-instructie verkrijgt deze vergrendelingen:

• Exclusieve (X) vergrendelt de bijgewerkte gegevensrijen.
• Intent exclusive (IX) vergrendelt de geclusterde indexpagina's die deze rijen bevatten.
• Een IX vergrendeling op de geclusterde index en een andere op de tabel.

De SELECT-instructie verkrijgt deze vergrendelingen:

• Gedeeld (S) vergrendelt alle gegevensrijen die worden gelezen, tenzij de rij al is beveiligd door een X vergrendeling van de UPDATE-instructie.
• Intent Shared (IS) vergrendelt alle geclusterde indexpagina's die deze rijen bevatten, tenzij de pagina al is beveiligd door een IX-vergrendeling.
• Geen vergrendeling op de geclusterde index of tabel omdat deze al zijn beveiligd door IX vergrendelingen.

Als de SELECT-instructie voldoende vergrendelingen verkrijgt om escalatie van vergrendelingen te activeren en de escalatie slaagt, wordt de IX vergrendeling in de tabel geconverteerd naar een X-vergrendeling en worden alle rij-, pagina- en indexvergrendelingen vrijgegeven. Zowel de updates als het lezen worden beveiligd door de X-vergrendeling van de tabel.

Vergrendeling en escalatie van vergrendelingen verminderen

In de meeste gevallen levert de database-engine de beste prestaties bij gebruik van de standaardinstellingen voor vergrendeling en blokkade-escalatie.

• Profiteer van de geoptimaliseerde vergrendelingsfunctie .

  • Geoptimaliseerde vergrendeling biedt een verbeterd mechanisme voor transactievergrendeling dat het geheugenverbruik van de vergrendeling vermindert en blokkeert voor gelijktijdige transacties. Escalatie van vergrendelingen is veel minder waarschijnlijk wanneer geoptimaliseerde vergrendeling is ingeschakeld.
  • Vermijd het gebruik van tabelhints met geoptimaliseerde vergrendeling. Tabelhints kunnen de effectiviteit van geoptimaliseerde vergrendeling verminderen.
  • Schakel de READ_COMMITTED_SNAPSHOT-optie voor de database in om optimaal te profiteren van geoptimaliseerde vergrendeling. Dit is de standaardinstelling in Azure SQL Database.
  • Voor geoptimaliseerde vergrendeling moet versneld databaseherstel (ADR) zijn ingeschakeld voor de database.

Als een exemplaar van de database-engine veel vergrendelingen genereert en frequente escalaties van vergrendelingen ziet, kunt u overwegen de hoeveelheid vergrendeling te verminderen met de volgende strategieën:

• Gebruik een isolatieniveau waarmee geen gedeelde vergrendelingen worden gegenereerd voor leesbewerkingen:

  • READ COMMITTED isolatieniveau wanneer de READ_COMMITTED_SNAPSHOT-databaseoptie ONis.
  • SNAPSHOT isolatieniveau.
  • READ UNCOMMITTED isolatieniveau. Dit kan alleen worden gebruikt voor systemen die kunnen werken met onvolledige gegevenslezingen.

• Gebruik de tabelhints PAGLOCK of TABLOCK om de Database Engine pagina-, heap- of indexvergrendelingen te laten gebruiken in plaats van grove vergrendelingen. Als u deze optie gebruikt, neemt echter de problemen toe van gebruikers die andere gebruikers blokkeren die toegang proberen te krijgen tot dezelfde gegevens en mogen ze niet worden gebruikt in systemen met meer dan een paar gelijktijdige gebruikers.

• Als geoptimaliseerde vergrendeling niet beschikbaar is, gebruikt u voor gepartitioneerde tabellen de optie LOCK_ESCALATION van ALTER TABLE om vergrendelingen naar de partitie te escaleren in plaats van de tabel, of om escalatie van vergrendelingen voor een tabel uit te schakelen.

• Grote batchbewerkingen opsplitsen in verschillende kleinere bewerkingen. Stel dat u de volgende query hebt uitgevoerd om enkele honderdduizend oude rijen uit een audittabel te verwijderen en u hebt ontdekt dat het een escalatie van vergrendeling heeft veroorzaakt waardoor andere gebruikers zijn geblokkeerd:

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

  Door deze rijen een paar honderd per keer te verwijderen, kunt u het aantal vergrendelingen dat per transactie wordt verzameld aanzienlijk verminderen en escalatie van vergrendelingen voorkomen. Bijvoorbeeld:

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• Verminder de vergrendelingsvoetafdruk van een query door de query zo efficiënt mogelijk te maken. Grote scans of grote aantallen sleutelzoekacties kunnen de kans op escalatie van vergrendeling vergroten; daarnaast vergroot dit de kans op deadlocks en heeft het doorgaans een negatieve invloed op de gelijktijdigheid en prestaties. Nadat u de query hebt gevonden die escalatie van vergrendeling veroorzaakt, zoekt u naar mogelijkheden om nieuwe indexen te maken of kolommen toe te voegen aan een bestaande index om volledige index- of tabelscans te verwijderen en om de efficiëntie van indexzoekopdrachten te maximaliseren. Overweeg het gebruik van de Database Engine Tuning Advisor om een automatische indexanalyse uit te voeren op de query. Zie Zelfstudie: Database Engine Tuning Advisorvoor meer informatie. Eén doel van deze optimalisatie is om indexzoekopdrachten zo weinig mogelijk rijen te laten retourneren om de kosten van sleutelzoekacties te minimaliseren (maximaliseer de selectiviteit van de index voor de specifieke query). Als de database-engine inschat dat een logische operator voor sleutelopzoeking veel rijen kan retourneren, kan een voorafoptimalisatie worden toegepast om de opzoeking uit te voeren. Als de database-engine gebruikmaakt van prefetch voor een zoekactie, moet het isolatieniveau van een transactie van een deel van de query worden verhoogd naar REPEATABLE READ. Dit betekent dat wat eruit kan zien als een SELECT-instructie op een READ COMMITTED isolatieniveau vele duizenden sleutelvergrendelingen kan verkrijgen (op zowel de geclusterde index als één niet-geclusterde index), waardoor een dergelijke query de drempelwaarden voor escalatie van vergrendelingen kan overschrijden. Dit is vooral belangrijk als u merkt dat de geëscaleerde vergrendeling een gedeelde tabelvergrendeling is, wat echter niet vaak voorkomt bij het standaard READ COMMITTED isolatieniveau.

  Als een sleutelzoekactie met de prefetch-optimalisatie vergrendelingsescalatie veroorzaakt, kunt u overwegen extra kolommen toe te voegen aan de niet-geclusterde index die verschijnt in de logische operator Indexzoekopdracht of indexscan onder de sleutelzoeken-logische operator in het queryplan. Het kan mogelijk zijn om een dekkingsindex te maken (een index die alle kolommen in een tabel bevat die in de query zijn gebruikt), of ten minste een index die betrekking heeft op de kolommen die zijn gebruikt voor joincriteria of in de WHERE component als alles in de SELECT kolomlijst niet praktisch is. Een geneste lusdeelname kan ook gebruikmaken van de prefetch-optimalisatie en dit veroorzaakt hetzelfde vergrendelingsgedrag.

• Escalatie van vergrendeling kan niet optreden als een andere SPID momenteel een incompatibele tabelvergrendeling bevat. Escalatie van vergrendeling escaleert altijd naar een tabelvergrendeling en nooit naar paginavergrendelingen. Als een escalatiepoging voor vergrendeling mislukt omdat een andere SPID een incompatibele tabelvergrendeling bevat, wordt de query die escalatie heeft geprobeerd niet geblokkeerd terwijl wordt gewacht op een tabelvergrendeling. In plaats daarvan blijft het vergrendelingen verkrijgen op het oorspronkelijke, fijnere niveau (rij, sleutel of pagina) en doet het periodiek extra escalatiepogingen. Een methode om escalatie van vergrendelingen in een bepaalde tabel te voorkomen, is door een vergrendeling op een andere verbinding te verkrijgen en vast te houden die niet compatibel is met het geëscaleerde vergrendelingstype. Een intentie-exclusieve (IX) vergrendeling op tabelniveau vergrendelt geen rijen of pagina's, maar is nog steeds niet compatibel met een geëscaleerd gedeelde (S) of exclusief (X) tabelvergrendeling. Stel dat u een batchtaak moet uitvoeren waarmee een groot aantal rijen in de mytable tabel wordt gewijzigd en dat blokkeringen heeft veroorzaakt vanwege escalatie van de vergrendeling. Als deze taak altijd binnen een uur is voltooid, kunt u een Transact-SQL taak met de volgende code maken en de nieuwe taak plannen om enkele minuten vóór de begintijd van de batchtaak te beginnen:

  BEGIN TRAN;
  
  SELECT *
  FROM mytable WITH (UPDLOCK, HOLDLOCK)
  WHERE 1 = 0;
  
  WAITFOR DELAY '1:00:00';
  
  COMMIT TRAN;
  

  Deze query verkrijgt en bevat een IX-vergrendeling op mytable gedurende één uur, waardoor escalatie van vergrendelingen van de tabel gedurende die tijd wordt voorkomen. In deze batch worden geen gegevens gewijzigd of andere query's geblokkeerd (tenzij de andere query een tabelvergrendeling met de TABLOCK hint dwingt of als een beheerder pagina- of rijvergrendelingen op een index op mytableheeft uitgeschakeld).

• U kunt ook traceringsvlagmen 1211 en 1224 gebruiken om alle of sommige escalaties van vergrendelingen uit te schakelen. Echter, deze traceringsvlaggen schakelen alle escalatie van vergrendelingen globaal uit voor het hele Database Engine-exemplaar. Escalatie van vergrendeling is een nuttig doel in de database-engine door de efficiëntie van query's te maximaliseren die anders worden vertraagd door de overhead van het verkrijgen en vrijgeven van enkele duizenden vergrendelingen. Escalatie van vergrendelingen helpt ook het vereiste geheugen te minimaliseren om vergrendelingen bij te houden. Het geheugen dat de database-engine dynamisch kan toewijzen voor vergrendelingsstructuren is eindig, dus als u escalatie van vergrendeling uitschakelt en het vergrendelingsgeheugen groot genoeg wordt, kunnen pogingen om extra vergrendelingen toe te wijzen voor elke query mislukken en treedt de volgende fout op: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  Notitie

  Wanneer de MSSQLSERVER_1204 fout optreedt, wordt de verwerking van de huidige instructie gestopt en wordt de actieve transactie teruggedraaid. Het terugdraaien zelf kan gebruikers blokkeren of leiden tot een lange hersteltijd van de database als u de databaseservice opnieuw start.

  Notitie

  Het gebruik van een vergrendelingshint zoals ROWLOCK wijzigt alleen de eerste vergrendelingsaankoop. Vergrendelingshints verhinderen geen escalatie van vergrendelingen.

Vanaf SQL Server 2008 (10.0.x) is het gedrag van escalatie van vergrendeling gewijzigd met de introductie van de LOCK_ESCALATION tabeloptie. Zie de optie LOCK_ESCALATION van ALTER TABLEvoor meer informatie.

Escalatie van vergrendeling bewaken

Bewaak escalatie van vergrendeling met behulp van de uitgebreide gebeurtenis lock_escalation, zoals in het volgende voorbeeld:

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
    (
    SET collect_database_name=1,collect_statement=1
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
    )
ADD TARGET package0.histogram
    (
    SET source=N'sqlserver.database_id'
    )
GO

Dynamische vergrendeling

Het gebruik van vergrendelingen op laag niveau, zoals rijvergrendelingen, verhoogt de gelijktijdigheid door de kans te verkleinen dat twee transacties tegelijkertijd vergrendelingen aanvragen voor hetzelfde stukje gegevens. Het gebruik van vergrendelingen op laag niveau verhoogt ook het aantal vergrendelingen en de resources die nodig zijn om ze te beheren. Het gebruik van tabel- of paginavergrendelingen op hoog niveau verlaagt de overhead, maar ten koste van gelijktijdigheid.

De database-engine maakt gebruik van een dynamische vergrendelingsstrategie om de meest effectieve vergrendelingen te bepalen. De database-engine bepaalt automatisch welke vergrendelingen het meest geschikt zijn wanneer de query wordt uitgevoerd, op basis van de kenmerken van het schema en de query. Als u bijvoorbeeld de overhead van vergrendeling wilt verminderen, kan de optimizer paginavergrendelingen in een index kiezen bij het uitvoeren van een indexscan.

Vergrendelingspartitionering

Voor grote computersystemen kunnen vergrendelingen op vaak gebruikte objecten een prestatieknelpunt worden omdat het verkrijgen en vrijgeven van vergrendelingen conflicten op interne vergrendelingsbronnen plaatst. Partitionering van vergrendeling verbetert de vergrendelingsprestaties door één vergrendelingsresource te splitsen in meerdere vergrendelingsresources. Deze functie is alleen beschikbaar voor systemen met 16 of meer logische CPU's en wordt automatisch ingeschakeld en kan niet worden uitgeschakeld. Alleen objectvergrendelingen kunnen worden gepartitioneerd. Objectvergrendelingen met een subtype worden niet gepartitioneerd. Zie sys.dm_tran_locks (Transact-SQL)voor meer informatie.

Begrijpen van vergrendelingspartitionering

Vergrendelingstaken gebruiken meerdere gedeelde resources, waarvan er twee zijn geoptimaliseerd door vergrendelingspartitionering.

• Spinlock

  Hiermee bepaalt u de toegang tot een vergrendelingsresource, zoals een rij of een tabel.

  Zonder vergrendelingspartitionering zorgt één spinlock ervoor dat alle vergrendelingsaanvragen voor één vergrendelingsbron worden beheerd. Op systemen die een grote hoeveelheid activiteit ervaren, kan conflicten optreden wanneer vergrendelingsaanvragen wachten tot de spinlock beschikbaar is. In deze situatie kan het verkrijgen van vergrendelingen een knelpunt worden en de prestaties negatief beïnvloeden.

  Door vergrendelingspartitionering wordt een enkele vergrendelingsresource opgesplitst in meerdere vergrendelingsresources om de belasting over meerdere spinlocks te verdelen.

• geheugen

  Dit wordt gebruikt om de vergrendelingsresourcestructuren op te slaan.

  Zodra de spinlock is verkregen, worden vergrendelingsstructuren opgeslagen in het geheugen en vervolgens geopend en mogelijk gewijzigd. Het verdelen van locktoegang over meerdere resources vermindert de noodzaak tot het overdragen van geheugenblokken tussen CPU's, zodat de prestaties verbeterd worden.

Partitionering van vergrendelingen implementeren en bewaken

Lock-partitionering is standaard ingeschakeld voor systemen met 16 of meer CPU's. Wanneer vergrendelingspartitionering is ingeschakeld, wordt een informatief bericht vastgelegd in het SQL Server-foutenlogboek.

Bij het verkrijgen van vergrendelingen voor een gepartitioneerde resource:

• Alleen NL, Sch-S, IS, IUen IX vergrendelingsmodi worden verkregen op één partitie.

• Gedeeld (S), exclusief (X) en andere vergrendelingen in andere modi dan NL, Sch-S, IS, IUen IX moeten worden verkregen op alle partities die beginnen met partitie-id 0 en volgen in de volgorde van de partitie-id. Deze vergrendelingen op een gepartitioneerde resource gebruiken meer geheugen dan vergrendelingen in dezelfde modus op een niet-gepartitioneerde resource, omdat elke partitie effectief een afzonderlijke vergrendeling is. De geheugentoename wordt bepaald door het aantal partities. De prestatiemeteritems voor vergrendeling van SQL Server geven informatie weer over het geheugen dat wordt gebruikt door gepartitioneerde en niet-gepartitioneerde vergrendelingen.

Een transactie wordt toegewezen aan een partitie wanneer de transactie wordt gestart. Voor de transactie gebruiken alle vergrendelingsaanvragen die kunnen worden gepartitioneerd de partitie die aan die transactie is toegewezen. Met deze methode wordt de toegang tot het vergrendelen van resources van hetzelfde object door verschillende transacties verdeeld over verschillende partities.

De resource_lock_partition-kolom in de dynamische beheerweergave sys.dm_tran_locks biedt de vergrendelingspartitie-id voor een gepartitioneerde vergrendelingsresource. Zie sys.dm_tran_locks (Transact-SQL)voor meer informatie.

Werken met lockpartitionering

De volgende codevoorbeelden illustreren het partitioneren van vergrendelingen. In de voorbeelden worden twee transacties uitgevoerd in twee verschillende sessies om vergrendelingsgedrag op een computersysteem met 16 CPU's weer te geven.

Met deze Transact-SQL-instructies worden testobjecten gemaakt die worden gebruikt in de volgende voorbeelden.

-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO

Voorbeeld A

Sessie 1:

Een SELECT verklaring wordt uitgevoerd onder een transactie. Vanwege de HOLDLOCK-vergrendelingshint verkrijgt en behoudt deze instructie een intentievergrendeling (IS) in de tabel (voor deze afbeelding worden rij- en paginavergrendelingen genegeerd). De IS-vergrendeling wordt alleen verkregen op de partitie die aan de transactie is toegewezen. In dit voorbeeld wordt ervan uitgegaan dat de IS-vergrendeling wordt verkregen op partitie-id 7.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Sessie 2:

Er wordt een transactie gestart en de SELECT instructie die wordt uitgevoerd onder deze transactie verkrijgt en behoudt een gedeelde (S) vergrendeling op de tabel. De S-vergrendeling wordt verkregen op alle partities, wat resulteert in meerdere tabelvergrendelingen, één voor elke partitie. Op een systeem met 16 CPU's worden bijvoorbeeld 16 S-vergrendelingen uitgegeven voor de partitie-ID's van 0 tot 15. Omdat de S-vergrendeling compatibel is met de IS-vergrendeling die wordt bewaard op partitie-id 7 door de transactie in sessie 1, is er geen blokkering tussen transacties.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

Sessie 1:

De volgende SELECT-instructie wordt uitgevoerd binnen de transactie die nog actief is binnen sessie 1. Vanwege de exclusieve vergrendelingshint (X) voor de tabel, probeert de transactie een X-vergrendeling op de tabel te krijgen. De S vergrendeling die door de transactie in sessie 2 wordt bewaard, blokkeert echter de X-vergrendeling bij partitie-id 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Voorbeeld B

Sessie 1:

Een SELECT verklaring wordt uitgevoerd onder een transactie. Vanwege de HOLDLOCK-vergrendelingshint verkrijgt en behoudt deze instructie een intentievergrendeling (IS) in de tabel (voor deze afbeelding worden rij- en paginavergrendelingen genegeerd). De IS-vergrendeling wordt alleen verkregen op de partitie die aan de transactie is toegewezen. In dit voorbeeld wordt ervan uitgegaan dat de IS-vergrendeling wordt verkregen op partitie-id 6.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Sessie 2:

Een SELECT verklaring wordt uitgevoerd onder een transactie. Door de TABLOCKX-vergrendelingshint probeert de transactie een exclusieve (X) vergrendeling op de tabel te verkrijgen. Houd er rekening mee dat de X-vergrendeling moet worden verkregen op alle partities die beginnen met partitie-id 0. De X vergrendeling wordt verkregen op alle partitie-id's 0-5, maar wordt geblokkeerd door de IS-vergrendeling die is verkregen op partitie-id 6.

Voor partitie-ID's 7 tot en met 15 die nog niet door de X-vergrendeling zijn bereikt, kunnen andere transacties vergrendelingen blijven verkrijgen.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Isolatieniveaus op basis van rijversies in de Database Engine

Vanaf SQL Server 2005 (9.x) biedt de database-engine een implementatie van een bestaand isolatieniveau voor transacties, READ COMMITTED, dat een momentopname op instructieniveau biedt met behulp van rijversiebeheer. Database Engine biedt ook een isolatieniveau voor transacties, SNAPSHOT, dat een momentopname op transactieniveau biedt die ook gebruikmaakt van rijversiebeheer.

Rijversiebeheer is een algemeen framework in SQL Server dat een copy-on-write-mechanisme aanroept wanneer een rij wordt gewijzigd of verwijderd. Dit vereist dat terwijl de transactie wordt uitgevoerd, de oude versie van de rij beschikbaar moet zijn voor transacties waarvoor een eerdere transactioneel consistente status is vereist. Rijversiebeheer wordt gebruikt om de volgende functies te implementeren:

• Bouw de tabellen inserted en deleted in triggers. Alle rijen die door de trigger worden gewijzigd, worden geversioneerd. Dit omvat de rijen die zijn gewijzigd door de instructie waarmee de trigger is gestart, evenals alle gegevenswijzigingen die door de trigger zijn aangebracht.
• Ondersteuning voor meerdere actieve resultatensets (MARS). Als een MARS-sessie een instructie voor gegevenswijziging (zoals INSERT, UPDATEof DELETE) uitvoert op een moment dat er een actieve resultatenset is, worden de rijen waarop de wijzigingsinstructie betrekking heeft versiebeheer.
• Ondersteuning voor indexbewerkingen die de optie ONLINE opgeven.
• Ondersteuning voor isolatieniveaus voor transacties op basis van rijversies:
  • Een nieuwe implementatie van het READ COMMITTED isolatieniveau dat gebruikmaakt van rijversies om leesconsistentie op het niveau van statements te bieden.
  • Een nieuw isolatieniveau, SNAPSHOT, om leesconsistentie op transactieniveau te bieden.

Rijversies worden opgeslagen in een versieopslag. Als versneld databaseherstel (ADR) is ingeschakeld voor een database, wordt het versiearchief in die database gemaakt. Anders wordt het versiearchief gemaakt in de tempdb-database.

De database moet voldoende ruimte hebben voor het versiearchief. Wanneer het versiearchief zich in tempdbbevindt en de tempdb-database vol is, stoppen updatebewerkingen met het genereren van versies, maar blijven ze slagen, maar kunnen leesbewerkingen mislukken omdat een bepaalde rijversie die nodig is niet bestaat. Dit is van invloed op bewerkingen zoals triggers, MARS en online indexering.

Wanneer ADR wordt gebruikt en het versiearchief vol is, blijven leesbewerkingen slagen, maar schrijfbewerkingen die versies genereren, zoals UPDATE en DELETE mislukken. INSERT bewerkingen blijven slagen als de database voldoende ruimte heeft.

Het gebruik van rijversies voor READ COMMITTED- en SNAPSHOT-transacties is een proces dat uit twee stappen bestaat.

1. Stel de READ_COMMITTED_SNAPSHOT- en ALLOW_SNAPSHOT_ISOLATION-databaseopties in op ON.

2. Stel het juiste isolatieniveau voor transacties in een toepassing in:

   • Wanneer de databaseoptie READ_COMMITTED_SNAPSHOT is ON, maken transacties die het isolatieniveau READ COMMITTED instellen gebruik van rijversiebeheer.
   • Wanneer de ALLOW_SNAPSHOT_ISOLATION databaseoptie is ON, kunnen transacties het SNAPSHOT isolatieniveau instellen.

Wanneer READ_COMMITTED_SNAPSHOT of ALLOW_SNAPSHOT_ISOLATION databaseoptie is ingesteld op ON, wijst de database-engine een transactiereeksnummer (XSN) toe aan elke transactie die gegevens bewerkt met behulp van rijversiebeheer. Transacties beginnen op het moment dat een BEGIN TRANSACTION instructie wordt uitgevoerd. Het transactiereeksnummer begint echter met de eerste lees- of schrijfbewerking na de BEGIN TRANSACTION-instructie. Het transactiereeksnummer wordt verhoogd met één keer wanneer het wordt toegewezen.

Wanneer de READ_COMMITTED_SNAPSHOT of ALLOW_SNAPSHOT_ISOLATION databaseopties zijn ingesteld op ON, worden logische kopieën (versies) bewaard voor alle gegevenswijzigingen die in de database worden uitgevoerd. Telkens wanneer een rij wordt gewijzigd door een specifieke transactie, slaat het exemplaar van de Database Engine een versie op van het eerder doorgevoerde beeld van de rij in het versiearchief. Elke versie wordt gemarkeerd met het transactiereeksnummer van de transactie die de wijziging heeft aangebracht. De versies van gewijzigde rijen worden gekoppeld met behulp van een koppelingslijst. De nieuwste rijwaarde wordt altijd opgeslagen in de huidige database en gekoppeld aan de versierijen in het versiearchief.

Notitie

Voor het wijzigen van grote objecten (LOBs) wordt alleen het gewijzigde fragment gekopieerd naar het versiearchief.

Rijversies worden lang genoeg bewaard om te voldoen aan de vereisten van transacties die worden uitgevoerd onder op rijversiebeheer gebaseerde isolatieniveaus. De database-engine houdt het vroegste nuttige transactiereeksnummer bij en verwijdert periodiek alle rijversies die zijn gestempeld met transactiereeksnummers die lager zijn dan het vroegste nuttige volgnummer.

Wanneer beide databaseopties zijn ingesteld op OFF, worden alleen rijen die worden gewijzigd door triggers of MARS-sessies, of gelezen door online indexbewerkingen, geversioneerd. Deze rijversies worden uitgebracht als ze niet langer nodig zijn. Een achtergrondproces verwijdert verouderde rijversies.

Notitie

Voor kortlopende transacties kan een versie van een gewijzigde rij in de cache worden opgeslagen in de buffergroep zonder naar het versiearchief te worden geschreven. Als de behoefte aan de versierij kort is, wordt de rij verwijderd uit de buffergroep en wordt er geen I/O-overhead in rekening gebracht.

Gedrag bij het lezen van gegevens

Wanneer transacties die worden uitgevoerd onder een op rijversioning gebaseerde isolatieniveau gegevens lezen, verkrijgen de leesbewerkingen geen gedeelde (S) vergrendelingen op de gelezen gegevens en blokkeren ze daarom geen transacties die gegevens aan het wijzigen zijn. Ook wordt de overhead van vergrendelingsresources geminimaliseerd omdat het aantal verkregen vergrendelingen wordt verminderd. READ COMMITTED isolatie met behulp van rijversiebeheer en SNAPSHOT isolatie is ontworpen om leesconsistentie op instructie- of transactieniveau van versiegegevens te bieden.

Alle queries, inclusief transacties die worden uitgevoerd onder isolatieniveaus op basis van rijversies, verkrijgen schema stabiliteitsvergrendelingen (Sch-S) tijdens de compilatie en uitvoering. Daarom worden queries geblokkeerd wanneer een gelijktijdige transactie een schemawijzigingsslot (Sch-M) op de tabel heeft. Een DDL-bewerking (Data Definition Language) verkrijgt bijvoorbeeld een Sch-M-vergrendeling voordat de schemagegevens van de tabel worden gewijzigd. Transacties, waaronder transacties die worden uitgevoerd onder een isolatieniveau op basis van rijversiebeheer, worden geblokkeerd bij het verkrijgen van een Sch-S-vergrendeling. Omgekeerd blokkeert een query met een Sch-S-vergrendeling een gelijktijdige transactie die probeert een Sch-M-vergrendeling te verkrijgen.

Wanneer een transactie met het SNAPSHOT isolatieniveau wordt gestart, registreert het exemplaar van de database-engine alle momenteel actieve transacties. Wanneer de SNAPSHOT transactie een rij met een versieketen leest, volgt de database-engine de keten en haalt de rij op waarin het transactiereeksnummer is:

• Het dichtst bij maar lager dan het volgnummer van de momentopnametransactie die de rij leest.

• Niet in de lijst met transacties die actief zijn wanneer de momentopnametransactie is gestart.

Leesbewerkingen die door een SNAPSHOT transactie worden uitgevoerd, halen de laatste versie op van elke rij die is doorgevoerd op het moment dat de SNAPSHOT transactie is gestart. Dit biedt een transactioneel consistente momentopname van de gegevens zoals deze bestonden aan het begin van de transactie.

READ COMMITTED transacties die gebruikmaken van rijversiebeheer, werken op bijna dezelfde manier. Het verschil is dat de READ COMMITTED transactie geen eigen transactiereeksnummer gebruikt bij het kiezen van rijversies. Elke keer dat een instructie wordt gestart, leest de READ COMMITTED transactie het meest recente transactievolgnummer dat is uitgegeven voor dat exemplaar van de Database Engine. Dit is het transactienummer dat wordt gebruikt om de rijversies voor die verklaring te selecteren. Hierdoor kunnen READ COMMITTED transacties een momentopname van de gegevens zien zoals deze aan het begin van elke verklaring bestaan.

Notitie

Hoewel READ COMMITTED transacties met behulp van rijversiebeheer een transactioneel consistente weergave biedt van de gegevens op instructieniveau, worden rijversies die door dit type transactie worden gegenereerd of geopend, gehandhaafd totdat de transactie is voltooid.

Gedrag bij het wijzigen van gegevens

Het gedrag van gegevensschrijfbewerkingen verschilt met en zonder geoptimaliseerde vergrendeling ingeschakeld.

Gegevens wijzigen zonder geoptimaliseerde vergrendeling

In een READ COMMITTED transactie met behulp van rijversiebeheer wordt de selectie van rijen die moeten worden bijgewerkt uitgevoerd met behulp van een blokkerende scan waarbij een updatevergrendeling (U) wordt verkregen op de gegevensrij wanneer gegevenswaarden worden gelezen. Dit is hetzelfde als een READ COMMITTED transactie die geen rij versiebeheer gebruikt. Als de gegevensrij niet voldoet aan de updatecriteria, wordt de updatevergrendeling op die rij vrijgegeven en wordt de volgende rij vergrendeld en gescand.

Transacties die worden uitgevoerd onder SNAPSHOT isolatie, nemen een optimistische benadering van gegevenswijziging door vergrendelingen op gegevens te verkrijgen voordat de wijziging alleen wordt uitgevoerd om beperkingen af te dwingen. Anders worden er geen vergrendelingen op gegevens verkregen totdat de gegevens daadwerkelijk moeten worden gewijzigd. Wanneer een gegevensrij voldoet aan de updatecriteria, controleert de SNAPSHOT transactie of de gegevensrij niet is gewijzigd door een gelijktijdige transactie die is doorgevoerd nadat de SNAPSHOT transactie is gestart. Als de gegevensrij buiten de SNAPSHOT transactie is gewijzigd, treedt er een updateconflict op en wordt de SNAPSHOT transactie beëindigd. Het updateconflict wordt afgehandeld door de database-engine en er is geen manier om de detectie van updateconflicten uit te schakelen.

Notitie

Updatebewerkingen die intern worden uitgevoerd onder SNAPSHOT-isolatie worden onder READ COMMITTED-isolatie uitgevoerd wanneer de SNAPSHOT-transactie toegang heeft tot een van de volgende elementen:

Een tabel met een beperking voor een vreemde sleutel.

Een tabel waarnaar wordt verwezen in een foreign key-beperking van een andere tabel.

Een geïndexeerde weergave die verwijst naar meer dan één tabel.

Zelfs onder deze voorwaarden blijft de updatebewerking echter controleren of de gegevens niet zijn gewijzigd door een andere transactie. Als gegevens zijn gewijzigd door een andere transactie, ondervindt de SNAPSHOT transactie een updateconflict en wordt beëindigd. Updateconflicten moeten worden verwerkt en opnieuw worden uitgevoerd door de toepassing.

Gegevens wijzigen met geoptimaliseerde vergrendeling

Wanneer geoptimaliseerde vergrendeling is ingeschakeld en waarvoor de rcsi-databaseoptie (READ_COMMITTED_SNAPSHOT) is ingeschakeld en het standaard READ COMMITTED isolatieniveau wordt gebruikt, verkrijgen lezers geen vergrendelingen en krijgen schrijvers vergrendelingen met een lage duur, in plaats van vergrendelingen die aan het einde van de transactie verlopen.

Het inschakelen van RCSI wordt aanbevolen voor de meeste efficiëntie met geoptimaliseerde vergrendeling. Wanneer u strengere isolatieniveaus gebruikt, zoals REPEATABLE READ of SERIALIZABLE, bevat de database-engine rij- en paginavergrendelingen tot het einde van de transactie, voor zowel lezers als schrijvers, wat resulteert in meer blokkeren en vergrendelen van geheugen.

Als RCSI is ingeschakeld en bij gebruik van het standaardniveau READ COMMITTED isolatie, kwalificeren schrijvers rijen per predicaat op basis van de meest recente vastgelegde versie van de rij, zonder U vergrendelingen te verkrijgen. Een query wacht alleen als de rij in aanmerking komt en er nog een actieve schrijftransactie is op die rij of pagina. Kwalificeren op basis van de meest recente vastgelegde versie en het vergrendelen van alleen de gekwalificeerde rijen vermindert de blokkering en verhoogt de gelijktijdigheid.

Als updateconflicten worden gedetecteerd met RCSI en in het standaardniveau READ COMMITTED isolatieniveau, worden ze automatisch verwerkt en opnieuw geprobeerd zonder dat dit gevolgen heeft voor workloads van klanten.

Wanneer geoptimaliseerde vergrendeling is ingeschakeld en wanneer u het isolatieniveau SNAPSHOT gebruikt, is het gedrag van updateconflicten hetzelfde als zonder geoptimaliseerde vergrendeling. Updateconflicten moeten worden verwerkt en opnieuw worden uitgevoerd door de toepassing.

Notitie

Zie Wijzigingen in querygedrag met geoptimaliseerde vergrendeling en RCSI-voor meer informatie over gedragswijzigingen met de functie vergrendeling na kwalificatie (LAQ).

Gedrag in samenvatting

De volgende tabel bevat een overzicht van de verschillen tussen SNAPSHOT isolatie en READ COMMITTED isolatie met behulp van rijversies.

Eigenschap	READ COMMITTED isolatieniveau met behulp van rijversiebeheer	SNAPSHOT isolatieniveau
De databaseoptie die moet worden ingesteld op ON om de vereiste ondersteuning in te schakelen.	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
Hoe een sessie het specifieke type rijversie aanvraagt.	Gebruik het standaard READ COMMITTED isolatieniveau of voer de SET TRANSACTION ISOLATION LEVEL-instructie uit om het READ COMMITTED isolatieniveau op te geven. Dit kan gebeuren nadat de transactie is gestart.	Vereist de uitvoering van SET TRANSACTION ISOLATION LEVEL om het SNAPSHOT isolatieniveau op te geven vóór het begin van de transactie.
De versie van gegevens die wordt gelezen door uitspraken.	Alle gegevens die vóór de start van elke opdracht vastgelegd zijn.	Alle gegevens die vóór het begin van elke transactie zijn vastgelegd.
Hoe updates worden verwerkt.	Zonder geoptimaliseerde vergrendeling: schakelt terug van rijversies naar werkelijke gegevens om rijen te selecteren die moeten worden bijgewerkt en gebruikt updatevergrendelingen voor de geselecteerde gegevensrijen. Verwerft exclusieve vergrendelingen voor concrete gegevensrijen die moeten worden gewijzigd. Er is geen detectie van updateconflicten. Met geoptimaliseerde vergrendeling: rijen zijn geselecteerd op basis van de laatste doorgevoerde versie zonder dat er vergrendelingen worden verkregen. Als rijen in aanmerking komen voor de update, worden exclusieve rij- of paginavergrendelingen verkregen. Als er updateconflicten worden gedetecteerd, worden ze automatisch verwerkt en opnieuw geprobeerd.	Maakt gebruik van rijversies om rijen te selecteren die moeten worden bijgewerkt. Probeert een exclusieve vergrendeling te verkrijgen voor de werkelijke gegevensrij die moet worden gewijzigd en als de gegevens zijn gewijzigd door een andere transactie, treedt er een updateconflict op en wordt de momentopnametransactie beëindigd.
Conflictdetectie bijwerken	Zonder geoptimaliseerde vergrendeling: Geen. Met geoptimaliseerde vergrendeling: Als er updateconflicten worden gedetecteerd, worden ze automatisch verwerkt en opnieuw geprobeerd.	Geïntegreerde ondersteuning. Kan niet worden uitgeschakeld.

Geheugengebruik door rijversiebeheer

Het framework voor rijversiebeheer ondersteunt de volgende functionaliteiten van de database-engine:

• Activeringsmechanismen
• Meerdere actieve resultatensets (MARS)
• Online indexering

Het framework voor rijversiebeheer ondersteunt ook de volgende transactie-isolatieniveaus op basis van rijversies:

• Wanneer de READ_COMMITTED_SNAPSHOT databaseoptie is ingesteld op ON, bieden READ_COMMITTED transacties leesconsistentie op niveau van statements met behulp van rijversiebeheer.
• Wanneer de optie ALLOW_SNAPSHOT_ISOLATION database is ingesteld op ON, bieden SNAPSHOT transacties leesconsistentie op transactieniveau met behulp van rijversiebeheer.

Isolatieniveaus op basis van rijversies verminderen het aantal vergrendelingen dat door transacties wordt verkregen door het elimineren van het gebruik van gedeelde vergrendelingen bij leesoperaties. Dit verhoogt de systeemprestaties door het verminderen van de resources die worden gebruikt voor het beheren van vergrendelingen. De prestaties worden ook verhoogd door het aantal keren dat een transactie wordt geblokkeerd door vergrendelingen die zijn verkregen door andere transacties te verminderen.

Isolatieniveaus op basis van rijversies verhogen de resources die nodig zijn voor gegevenswijzigingen. Als u deze opties inschakelt, wordt er versiebeheer toegepast op alle datamodificaties voor de database. Er wordt een kopie van de gegevens voor de wijziging opgeslagen in het versiearchief, zelfs als er geen actieve transacties zijn die gebruikmaken van rijversiebeheer-gebaseerde isolatie. De gegevens na wijziging bevatten een verwijzing naar de versiegegevens in het versiearchief. Voor grote objecten wordt slechts een deel van het gewijzigde object opgeslagen in het versiearchief.

Gebruikte ruimte in tempdb

Voor elk exemplaar van de Database Engine moet de versieopslag voldoende ruimte hebben om de rijversies op te slaan. De databasebeheerder moet ervoor zorgen dat tempdb en andere databases (indien ADR is ingeschakeld) voldoende ruimte hebben om het versiearchief te ondersteunen. Er zijn twee typen versiearchieven:

• Het online index build versiearchief wordt gebruikt voor online index builds.
• Het algemene versiearchief wordt gebruikt voor alle andere bewerkingen voor het wijzigen van gegevens.

Rijversies moeten worden opgeslagen zolang een actieve transactie er toegang toe nodig heeft. Periodiek verwijdert een achtergrondthread rijversies die niet meer nodig zijn en maakt ruimte vrij in het versiearchief. Een langlopende transactie voorkomt dat ruimte in het versiearchief wordt vrijgegeven als deze voldoet aan een van de volgende voorwaarden:

• Hierbij wordt isolatie op basis van rijversies gebruikt.
• Er worden triggers, MARS of online indexopbouwbewerkingen gebruikt.
• Hiermee worden rijversies gegenereerd.

Notitie

Wanneer een trigger wordt aangeroepen binnen een transactie, worden de rijversies die door de trigger zijn gemaakt, gehandhaafd tot het einde van de transactie, ook al zijn de rijversies niet meer nodig nadat de trigger is voltooid. Dit geldt ook voor READ COMMITTED transacties die gebruikmaken van rijversiebeheer. Met dit type transactie is een transactioneel consistente weergave van de database alleen nodig voor elke instructie in de transactie. Dit betekent dat de rijversies die zijn gemaakt voor een verklaring in de transactie niet meer nodig zijn nadat de verklaring is voltooid. Rijversies die door elke instructie in de transactie worden gemaakt, worden echter gehandhaafd totdat de transactie is voltooid.

Als het versiearchief zich in tempdbbevindt en tempdb onvoldoende ruimte heeft, dwingt de database-engine de versiearchieven te verkleinen. Tijdens het verkleiningsproces worden de langst lopende transacties die nog geen rijversies hebben gegenereerd gemarkeerd als slachtoffers. Er wordt een bericht 3967 gegenereerd in het foutenlogboek voor elke transactie van het slachtoffer. Als een transactie is gemarkeerd als slachtoffer, kan het de rijversies in het versiearchief niet meer lezen. Wanneer wordt geprobeerd rijversies te lezen, wordt bericht 3966 gegenereerd en wordt de transactie teruggedraaid. Als het verkleiningsproces slaagt, wordt er ruimte beschikbaar in tempdb. Anders heeft tempdb onvoldoende ruimte en vindt het volgende plaats:

• Schrijfbewerkingen worden nog steeds uitgevoerd, maar genereren geen versies. Er wordt een informatiebericht (3959) weergegeven in het foutenlogboek, maar de transactie die gegevens schrijft, wordt niet beïnvloed.

• Transacties die proberen toegang te krijgen tot rijversies die niet zijn gegenereerd als gevolg van een volledige terugdraaiing met tempdb, eindigen met fout 3958.

Ruimte die wordt gebruikt in gegevensrijen

Elke databaserij kan maximaal 14 bytes aan het einde van de rij gebruiken voor informatie over rijversiebeheer. De informatie over het versiebeheer van rijen bevat het transactiereeksnummer van de transactie die de versie heeft doorgevoerd en de aanwijzer naar de geversiede rij. Deze 14 bytes worden toegevoegd wanneer de rij voor het eerst wordt gewijzigd of wanneer een nieuwe rij wordt ingevoegd, onder een van deze voorwaarden:

• READ_COMMITTED_SNAPSHOT of ALLOW_SNAPSHOT_ISOLATION opties zijn ingesteld op ON.
• De tabel heeft een trigger.
• Er worden meerdere MARS-sets (Active Results Sets) gebruikt.
• Online opbouwbewerkingen van indexen worden momenteel uitgevoerd op de tabel.

Als het versiearchief zich in tempdbbevindt, worden deze 14 bytes verwijderd uit de databaserij wanneer de rij voor het eerst wordt gewijzigd onder al deze voorwaarden:

• READ_COMMITTED_SNAPSHOT en ALLOW_SNAPSHOT_ISOLATION opties zijn ingesteld op OFF.
• De trigger bestaat niet meer in de tabel.
• MARS wordt niet gebruikt.
• Online indexbuildbewerkingen worden momenteel niet uitgevoerd.

De 14 bytes worden ook verwijderd wanneer een rij wordt gewijzigd als ADR niet meer is ingeschakeld en aan de bovenstaande voorwaarden wordt voldaan.

Als u een van de functies voor rijversiebeheer gebruikt, moet u mogelijk extra schijfruimte toewijzen voor de database om plaats te bieden aan de 14 bytes per databaserij. Het toevoegen van de informatie over rijversiebeheer kan leiden tot indexpaginasplitsingen of de toewijzing van een nieuwe gegevenspagina als er onvoldoende ruimte beschikbaar is op de huidige pagina. Als de gemiddelde rijlengte bijvoorbeeld 100 bytes is, zorgt de extra 14 bytes ervoor dat een bestaande tabel tot 14 procent toeneemt.

Het verlagen van de opvulfactor kan helpen om fragmentatie van indexpagina's te voorkomen of te verminderen. Als u de huidige paginadichtheidsinformatie voor de gegevens en indexen van een tabel of weergave wilt weergeven, kunt u sys.dm_db_index_physical_statsgebruiken.

Ruimte die wordt gebruikt door het permanente versiearchief (PVS)

Wanneer ADR is ingeschakeld, kunnen rijversies worden opgeslagen in permanente versieopslag (PVS) op een van de volgende manieren, afhankelijk van de grootte van de rij vóór wijziging:

• Als de grootte klein is, wordt de volledige oude rijversie opgeslagen als onderdeel van de gewijzigde rij.
• Als de grootte tussenliggend is, wordt het verschil tussen de oude rijversie en de gewijzigde rij opgeslagen als onderdeel van de gewijzigde rij. Het verschil is zodanig opgebouwd dat de database-engine indien nodig de volledige oude rijversie kan reconstrueren.
• Als de grootte groot is, wordt de hele oude rijversie opgeslagen in een afzonderlijke interne tabel.

De eerste twee methoden worden in-rij versieopslag genoemd. De laatste methode wordt off-row versieopslag genoemd. Wanneer versies in rij niet meer nodig zijn, worden ze verwijderd om ruimte vrij te maken op pagina's. Op dezelfde manier worden pagina's in de interne tabel die verouderde off-row versies bevatten en niet meer nodig zijn, verwijderd door de versieopruimer.

Het opslaan van rijversies als onderdeel van de rij optimaliseert de gegevensopvraging door transacties die rijversies moeten lezen. Als een versie in rij wordt opgeslagen, is een afzonderlijke leesbewerking van een PVS-pagina buiten rij niet vereist.

De sys.dm_db_index_physical_stats DMV biedt het aantal en het type versies dat is opgeslagen in rij en buiten rij voor een partitie van een index. De totale grootte van versiegegevens die in de rij zijn opgeslagen, wordt gerapporteerd in de kolom total_inrow_version_payload_size_in_bytes.

De grootte van de opslag buiten rij wordt gerapporteerd in de kolom persistent_version_store_size_kb in de sys.dm_tran_persistent_version_store_stats DMV.

Ruimte die wordt gebruikt in grote objecten

De database-engine ondersteunt verschillende gegevenstypen die grote tekenreeksen tot 2 GIGABYTE (GB) kunnen bevatten, zoals: nvarchar(max), varchar(max), varbinary(max), ntext, texten image. Grote gegevens die zijn opgeslagen met behulp van deze gegevenstypen, worden opgeslagen in een reeks gegevensfragmenten die zijn gekoppeld aan de gegevensrij. Rijversie-informatie wordt opgeslagen in elk fragment dat wordt gebruikt om deze lange tekenreeksen op te slaan. Gegevensfragmenten worden opgeslagen in een set pagina's die zijn toegewezen aan grote objecten in een tabel.

Omdat er nieuwe grote waarden worden toegevoegd aan een database, worden ze toegewezen met een maximum van 8040 bytes aan gegevens per fragment. In eerdere versies van de database-engine zijn maximaal 8080 bytes van ntext, textof image gegevens per fragment opgeslagen.

Bestaande grote objectgegevens (LOB) ntext, texten image worden niet geüpdatet om ruimte te maken voor de informatie over rijversiebeheer wanneer een database wordt geüpgraded naar SQL Server vanuit een eerdere versie van SQL Server. De eerste keer dat de LOB-gegevens worden gewijzigd, wordt deze echter dynamisch bijgewerkt om de opslag van versiegegevens mogelijk te maken. Dit gebeurt zelfs als rijversies niet worden gegenereerd. Nadat de LOB-gegevens zijn bijgewerkt, wordt het maximum aantal bytes dat per fragment is opgeslagen, gereduceerd van 8080 bytes tot 8040 bytes. Het upgradeproces is gelijk aan het verwijderen van de LOB-waarde en het opnieuw invoegen van dezelfde waarde. De LOB-gegevens worden bijgewerkt, zelfs als er slechts 1 byte wordt gewijzigd. Dit is een eenmalige bewerking voor elke ntext, textof image kolom, maar elke bewerking kan een grote hoeveelheid paginatoewijzingen en I/O-activiteit genereren, afhankelijk van de grootte van de LOB-gegevens. Het kan ook een grote hoeveelheid logboekactiviteit genereren als de wijziging volledig is geregistreerd. WRITETEXT- en UPDATETEXT-bewerkingen worden minimaal geregistreerd als het databaseherstelmodel niet is ingesteld op VOLLEDIG.

Er moet voldoende schijfruimte worden toegewezen om aan deze vereiste te voldoen.

Rijversiebeheer en het versiearchief bewaken

Voor het monitoren van rijversiebeheer, versiewinkel, en momentopname-isolatieprocessen ten behoeve van prestaties en problemen, biedt de Database Engine hulpmiddelen in de vorm van Dynamische Beheervensters (DMV's) en prestatiecounters.

RDW's

De volgende DMVs bieden informatie over de huidige systeemstatus van tempdb en de versieopslag, evenals transacties die gebruikmaken van rijversiebeheer.

• sys.dm_db_file_space_usage. Retourneert informatie over ruimtegebruik voor elk bestand in de database. Zie sys.dm_db_file_space_usage (Transact-SQL)voor meer informatie.

• sys.dm_db_session_space_usage. Geeft paginatoewijzing en deallocatieactiviteiten per sessie voor de database weer. Zie sys.dm_db_session_space_usage (Transact-SQL)voor meer informatie.

• sys.dm_db_task_space_usage. Geeft paginatoewijzing en deallocatie-activiteiten per taak voor de database weer. Zie sys.dm_db_task_space_usage (Transact-SQL)voor meer informatie.

• sys.dm_tran_top_version_generators. Retourneert een virtuele tabel voor de objecten die de meeste versies in het versiearchief produceren. Het groepeert de top 256 samengevoegde recordlengten op database_id en rowset_id. Gebruik deze functie om de grootste gebruikers van het versiearchief te vinden. Alleen van toepassing op het versiearchief in tempdb. Zie sys.dm_tran_top_version_generators (Transact-SQL)voor meer informatie.

• sys.dm_tran_version_store. Hiermee wordt een virtuele tabel geretourneerd waarin alle versierecords in het algemene versiearchief worden weergegeven. Alleen van toepassing op het versiearchief in tempdb. Zie sys.dm_tran_version_store (Transact-SQL)voor meer informatie.

• sys.dm_tran_version_store_space_usage. Retourneert een virtuele tabel die de totale ruimte in tempdb weergeeft die wordt gebruikt door records van versieopslag voor elke database. Alleen van toepassing op het versiearchief in tempdb. Zie sys.dm_tran_version_store_space_usage (Transact-SQL)voor meer informatie.

  Notitie

  Het uitvoeren van query's op sys.dm_tran_top_version_generators en sys.dm_tran_version_store kan duur zijn, omdat beide het hele versiearchief scannen, wat groot kan zijn. sys.dm_tran_version_store_space_usage is efficiënt en niet duur om uit te voeren, omdat deze niet door afzonderlijke versiearchiefrecords navigeert en in plaats daarvan geaggregeerde versieopslagruimte retourneert die wordt verbruikt in tempdb per database.

• sys.dm_tran_active_snapshot_database_transactions. Retourneert een virtuele tabel voor alle actieve transacties in alle databases binnen het SQL Server-exemplaar die gebruikmaken van rijversiebeheer. Systeemtransacties worden niet weergegeven in deze DMV. Zie sys.dm_tran_active_snapshot_database_transactions (Transact-SQL)voor meer informatie.

• sys.dm_tran_transactions_snapshot. Retourneert een virtuele tabel waarin momentopnamen worden weergegeven die door elke transactie zijn gemaakt. De momentopname bevat het volgnummer van de actieve transacties die gebruikmaken van rijversiebeheer. Zie sys.dm_tran_transactions_snapshot (Transact-SQL)voor meer informatie.

• sys.dm_tran_current_transaction. Retourneert één rij met informatie over de status van rijversiebeheer van de transactie in de huidige sessie. Zie sys.dm_tran_current_transaction (Transact-SQL)voor meer informatie.

• sys.dm_tran_current_snapshot. Retourneert een virtuele tabel die alle actieve transacties weergeeft op het moment dat de huidige isolatietransactie voor momentopnamen wordt gestart. Als de huidige transactie gebruikmaakt van isolatie van momentopnamen, retourneert deze functie geen rijen. De DMV-sys.dm_tran_current_snapshot is vergelijkbaar met sys.dm_tran_transactions_snapshot, behalve dat alleen de actieve transacties voor de huidige momentopname worden geretourneerd. Zie sys.dm_tran_current_snapshot (Transact-SQL)voor meer informatie.

• sys.dm_tran_persistent_version_store_stats. Retourneert statistieken voor het permanente versiearchief in elke database die wordt gebruikt wanneer versneld databaseherstel is ingeschakeld. Zie sys.dm_tran_persistent_version_store_stats (Transact-SQL)voor meer informatie.

Prestatiemetingen

De volgende prestatiemeteritems bewaken het versiearchief in tempdb, evenals transacties met behulp van rijversiebeheer. De prestatiemeteritems bevinden zich in het SQLServer:Transactions prestatieobject.

• Vrije Ruimte in tempdb (KB). Hiermee wordt de hoeveelheid, in kilobytes (KB), van vrije ruimte in de tempdb-database bewaakt. Er moet voldoende vrije ruimte zijn in tempdb om het versiearchief te verwerken dat ondersteuning biedt voor isolatie van momentopnamen.

  De volgende formule bevat een ruwe schatting van de grootte van het versiearchief. Voor langlopende transacties kan het handig zijn om de generatie- en opschoonsnelheid te bewaken om de maximale grootte van het versiearchief te schatten.

  [grootte van algemeen versiearchief] = 2 * [versiearchiefgegevens gegenereerd per minuut] * [langste duur (minuten) van de transactie]

  De langstlopende tijd van transacties mag geen online index build-processen bevatten. Omdat deze bewerkingen lang kunnen duren voor zeer grote tabellen, maken online indexversies gebruik van een afzonderlijk versiearchief. De geschatte grootte van de online indexversiesopslag is gelijk aan de hoeveelheid gegevens die in de tabel is gewijzigd, inclusief alle indexen, terwijl de online indexopbouw actief is.

• Versiearchiefgrootte (KB). Controleert de grootte in kB van alle versiearchieven in tempdb. Deze informatie helpt bij het bepalen van de hoeveelheid ruimte die nodig is in de tempdb-database voor het versiearchief. Het bewaken van deze teller gedurende een bepaalde periode biedt een nuttige schatting van de extra ruimte die nodig is voor tempdb.

• Versiegeneratiesnelheid (KB/s). Bewaakt de generatiesnelheid van de versie in KB per seconde in alle versiearchieven in tempdb.

• versie opschoningssnelheid (KB/s). Controleert de opschoonsnelheid van versies in KB per seconde in alle versieopslagplaatsen in tempdb.

  Notitie

  Informatie over het genereren van versies (KB/s) en de snelheid van het opschonen van versies (KB/s) kan worden gebruikt om tempdb ruimtevereisten te voorspellen.

• aantal versieopslageenheden. Bewaakt het aantal versieopslageenheden.

• Versiearchief-eenheid aanmaken. Controleert het totale aantal versieopslageenheden dat is gemaakt om rijversies op te slaan sinds het exemplaar is gestart.

• Versiearchief-eenheid inkorting. Controleert het totale aantal versieopslageenheden dat is afgekapt sinds het exemplaar is gestart. Een versiearchiefeenheid wordt afgekapt wanneer SQL Server bepaalt dat geen van de versierijen die zijn opgeslagen in de versieopslageenheid nodig zijn om actieve transacties uit te voeren.

• conflictverhouding bijwerken. Hiermee wordt de verhouding gecontroleerd van momentopnametransacties met updateconflicten tot het totale aantal updatemomentopnametransacties.

• langste duur van transacties. Bewaakt de langstlopende transactietijd in seconden van elke transactie met behulp van rijversiebeheer. Dit kan worden gebruikt om te bepalen of een transactie gedurende een onverwachte tijd wordt uitgevoerd.

• Transacties. Bewaakt het totale aantal actieve transacties. Dit omvat geen systeemtransacties.

• momentopnametransacties. Bewaakt het totale aantal actieve momentopnametransacties.

• momentopnametransacties bijwerken. Bewaakt het totale aantal actieve momentopnametransacties dat updatebewerkingen uitvoert.

• Niet-Snapshot Versie Transacties. Bewaakt het totale aantal actieve niet-momentopnametransacties waarmee versierecords worden gegenereerd.

  Notitie

  De som van Update Snapshot Transactions en NonSnapshot Version Transactions vertegenwoordigt het totale aantal transacties dat deelneemt aan het genereren van versies. Het verschil tussen momentopnametransacties en bijwerken van momentopnametransacties vertegenwoordigt het aantal alleen-lezen momentopnametransacties.

Voorbeeld van een isolatieniveau gebaseerd op rijversies

In de volgende voorbeelden ziet u de verschillen in gedrag tussen SNAPSHOT isolatietransacties en READ COMMITTED transacties die gebruikmaken van rijversiebeheer.

Een. Werken met momentopname-isolatie

In dit voorbeeld leest een transactie, die onder SNAPSHOT-isolatie wordt uitgevoerd, gegevens die vervolgens door een andere transactie worden gewijzigd. De SNAPSHOT transactie blokkeert de updatebewerking die door de andere transactie wordt uitgevoerd niet en blijft gegevens uit de versierij lezen, waarbij de wijziging van de gegevens wordt genegeerd. Wanneer de SNAPSHOT transactie echter probeert de gegevens te wijzigen die al door de andere transactie zijn gewijzigd, genereert de SNAPSHOT transactie een fout en wordt beëindigd.

Tijdens sessie 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Op sessie 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Tijdens sessie 1:

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Op sessie 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

Tijdens sessie 1:

-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

B. Werken met READ COMMITTED-isolatie met behulp van rijversiebeheer

In dit voorbeeld wordt een READ COMMITTED transactie met behulp van rijversiebeheer gelijktijdig uitgevoerd met een andere transactie. De READ COMMITTED transactie gedraagt zich anders dan een SNAPSHOT transactie. Net als bij een SNAPSHOT transactie leest de READ COMMITTED transactie versierijen, zelfs nadat de andere transactie gegevens heeft gewijzigd. Echter, in tegenstelling tot een SNAPSHOT-transactie, is een READ COMMITTED-transactie:

• Leest de gewijzigde gegevens nadat de andere transactie de gegevenswijzigingen heeft doorgevoerd.
• Kan de gegevens bijwerken die zijn gewijzigd door de andere transactie waarbij de SNAPSHOT transactie niet kon.

Tijdens sessie 1:

USE AdventureWorks2022;
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Op sessie 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Tijdens sessie 1:

-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Op sessie 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

Tijdens sessie 1:

-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Isolatieniveaus op basis van rijversies inschakelen

Databasebeheerders beheren de instellingen op databaseniveau voor rijversiebeheer met behulp van de READ_COMMITTED_SNAPSHOT en ALLOW_SNAPSHOT_ISOLATION databaseopties in de instructie ALTER DATABASE.

Wanneer de READ_COMMITTED_SNAPSHOT databaseoptie is ingesteld op ON, worden de mechanismen die worden gebruikt om de optie te ondersteunen onmiddellijk geactiveerd. Wanneer u de optie READ_COMMITTED_SNAPSHOT instelt, is alleen de verbinding die de opdracht ALTER DATABASE uitvoert, toegestaan in de database. Er mag geen andere open verbinding in de database zijn totdat ALTER DATABASE is voltooid. De database hoeft zich niet in de modus voor één gebruiker te bevinden.

Met de volgende Transact-SQL instructie wordt READ_COMMITTED_SNAPSHOTingeschakeld:

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

Wanneer de ALLOW_SNAPSHOT_ISOLATION databaseoptie is ingesteld op ON, begint het exemplaar van de database-engine niet met het genereren van rijversies voor gewijzigde gegevens totdat alle actieve transacties met gewijzigde gegevens in de database zijn voltooid. Als er actieve wijzigingstransacties zijn, stelt de database-engine de status van de optie in op PENDING_ON. Nadat alle wijzigingstransacties zijn voltooid, wordt de status van de optie gewijzigd in ON. Gebruikers kunnen geen SNAPSHOT transactie in de database starten totdat de optie is ON. Op dezelfde manier doorloopt de database een PENDING_OFF status wanneer de databasebeheerder de optie ALLOW_SNAPSHOT_ISOLATION instelt op OFF.

Met de volgende Transact-SQL instructie wordt ALLOW_SNAPSHOT_ISOLATIONingeschakeld:

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

In de volgende tabel worden de statussen van de optie ALLOW_SNAPSHOT_ISOLATION vermeld en beschreven. Als u ALTER DATABASE gebruikt met de optie ALLOW_SNAPSHOT_ISOLATION, worden gebruikers die momenteel toegang hebben tot de databasegegevens, niet geblokkeerd.

Status van SNAPSHOT isolatie voor de huidige database	Beschrijving
OFF	De ondersteuning voor SNAPSHOT isolatietransacties is niet geactiveerd. Er zijn geen SNAPSHOT isolatietransacties toegestaan.
PENDING_ON	De ondersteuning voor SNAPSHOT isolatietransacties heeft de overgangsstatus (van OFF tot ON). Openstaande transacties moeten worden voltooid. Er zijn geen SNAPSHOT isolatietransacties toegestaan.
ON	De ondersteuning voor SNAPSHOT isolatietransacties is geactiveerd. SNAPSHOT transacties zijn toegestaan.
PENDING_OFF	De ondersteuning voor SNAPSHOT isolatietransacties heeft de overgangsstatus (van ON tot OFF). SNAPSHOT transacties die na deze tijd zijn gestart, hebben geen toegang tot deze database. Bestaande SNAPSHOT transacties hebben nog steeds toegang tot deze database. Bestaande schrijftransacties maken nog steeds gebruik van versiebeheer in deze database. De status PENDING_OFF wordt pas OFF wanneer alle SNAPSHOT transacties die zijn gestart toen de isolatiestatus van de database SNAPSHOTON was, zijn voltooid.

Gebruik de sys.databases catalogusweergave om de status van beide databaseopties voor rijversiebeheer te bepalen.

Alle updates voor gebruikerstabellen en sommige systeemtabellen die zijn opgeslagen in master en msdb rijversies genereren.

De optie ALLOW_SNAPSHOT_ISOLATION wordt automatisch ingesteld op ON in de master- en msdb-databases en kan niet worden uitgeschakeld.

Gebruikers kunnen de optie READ_COMMITTED_SNAPSHOT niet instellen op ON in master, tempdbof msdb.

Gebruik isolatieniveaus op basis van rijversies

Het raamwerk voor rijversiebeheer is altijd geactiveerd en wordt gebruikt door meerdere functionaliteiten. Naast het bieden van isolatieniveaus op basis van rijversies, wordt het gebruikt om wijzigingen in triggers en meerdere MARS-sessies (Active Result Sets) te ondersteunen en om gegevensleesbewerkingen voor online indexbewerkingen te ondersteunen.

Isolatieniveaus op basis van rijversies zijn ingeschakeld op databaseniveau. Elke toepassing die toegang heeft tot objecten vanuit ingeschakelde databases, kan query's uitvoeren met behulp van de volgende isolatieniveaus:

• READ COMMITTED die gebruikmaakt van rijversiebeheer door de database-optie READ_COMMITTED_SNAPSHOT in te stellen op ON, zoals wordt weergegeven in het volgende codevoorbeeld:

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

  Wanneer de database is ingeschakeld voor READ_COMMITTED_SNAPSHOT, gebruiken alle query's die worden uitgevoerd onder het isolatieniveau READ COMMITTED rijversiebeheer, wat betekent dat leesbewerkingen geen updatebewerkingen blokkeren.

• SNAPSHOT isolatie door de optie ALLOW_SNAPSHOT_ISOLATION database in te stellen op ON, zoals wordt weergegeven in het volgende codevoorbeeld:

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  Wanneer u query's voor meerdere databases gebruikt, heeft een transactie die wordt uitgevoerd onder SNAPSHOT isolatie toegang tot tabellen in de database(s) waarvoor de ALLOW_SNAPSHOT_ISOLATION databaseoptie is ingesteld op ON. Als u toegang wilt krijgen tot tabellen in databases waarvoor de ALLOW_SNAPSHOT_ISOLATION databaseoptie niet is ingesteld op ON, moet het isolatieniveau worden gewijzigd. In het volgende codevoorbeeld ziet u bijvoorbeeld een SELECT instructie waarmee twee tabellen worden samengevoegd terwijl deze worden uitgevoerd onder een SNAPSHOT transactie. Eén tabel behoort tot een database waarin SNAPSHOT isolatie niet is ingeschakeld. Wanneer de SELECT instructie wordt uitgevoerd onder SNAPSHOT isolatie, wordt deze niet succesvol uitgevoerd.

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

  In het volgende codevoorbeeld ziet u dezelfde SELECT instructie die is gewijzigd om het niveau van transactieisolatie te wijzigen in READ COMMITTED bij het openen van een specifieke tabel. Vanwege deze wijziging wordt de SELECT-instructie succesvol uitgevoerd.

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

Beperkingen van transacties met isolatieniveaus op basis van rijversies

Houd rekening met de volgende beperkingen bij het werken met isoleringsniveaus op basis van versieverwerking:

• READ_COMMITTED_SNAPSHOT kan niet worden ingeschakeld in tempdb, msdbof master.

• Globale tijdelijke tabellen worden opgeslagen in tempdb. Wanneer u globale tijdelijke tabellen opent binnen een SNAPSHOT transactie, moet een van de volgende dingen gebeuren:

  • Stel de ALLOW_SNAPSHOT_ISOLATION databaseoptie in op ON in tempdb.
  • Gebruik een isolatiehint om het isolatieniveau van de opdracht te wijzigen.

• SNAPSHOT transacties mislukken wanneer:

  • Een database wordt alleen-lezen gemaakt nadat de SNAPSHOT-transactie is gestart, maar voordat de SNAPSHOT-transactie toegang krijgt tot de database.
  • Als u objecten uit meerdere databases opent, is de databasestatus zodanig gewijzigd dat het herstel van de database is opgetreden nadat een SNAPSHOT transactie is gestart, maar voordat de SNAPSHOT transactie toegang heeft tot de database. Bijvoorbeeld: de database is ingesteld op OFFLINE en vervolgens op ONLINE, de database wordt automatisch gesloten en opnieuw geopend omdat de optie AUTO_CLOSE is ingesteld op ON, of de database is losgekoppeld en opnieuw is gekoppeld.

• Gedistribueerde transacties, inclusief query's in gedistribueerde gepartitioneerde databases, worden niet ondersteund onder SNAPSHOT isolatie.

• De database-engine bewaart niet meerdere versies van systeemmetagegevens. DDL-instructies (Data Definition Language) voor tabellen en andere databaseobjecten (indexen, weergaven, gegevenstypen, opgeslagen procedures en algemene taalruntimefuncties) wijzigen metagegevens. Als een DDL-instructie een object wijzigt, zorgt een gelijktijdige verwijzing naar het object onder SNAPSHOT isolatie ervoor dat de SNAPSHOT transactie mislukt. READ COMMITTED transacties hebben deze beperking niet wanneer de optie READ_COMMITTED_SNAPSHOT database is ingesteld op ON.

  Een databasebeheerder voert bijvoorbeeld de volgende ALTER INDEX instructie uit.

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

  Elke momentopnametransactie die actief is wanneer de ALTER INDEX instructie wordt uitgevoerd, krijgt een foutmelding als deze probeert te verwijzen naar de HumanResources.Employee tabel nadat de ALTER INDEX instructie is uitgevoerd. READ COMMITTED transacties die gebruikmaken van rijversiebeheer, worden niet beïnvloed.

  Notitie

  BULK INSERT bewerkingen kunnen leiden tot wijzigingen in metagegevens van de doeltabel (bijvoorbeeld bij het uitschakelen van beperkingscontroles). Wanneer dit gebeurt, mislukken gelijktijdige SNAPSHOT isolatietransacties die toegang hebben tot bulksgewijs ingevoegde tabellen.

Aanpassen van vergrendeling en rijversiebeheer

De time-out voor vergrendeling aanpassen

Wanneer een exemplaar van de database-engine geen vergrendeling aan een transactie kan verlenen omdat een andere transactie al eigenaar is van een conflicterende vergrendeling voor de resource, wordt de eerste transactie geblokkeerd totdat de bestaande vergrendeling wordt vrijgegeven. Er is standaard geen time-outperiode voor wachttijden voor vergrendelingen, waardoor een transactie mogelijk voor onbepaalde tijd wordt geblokkeerd.

Notitie

Gebruik de sys.dm_os_waiting_tasks dynamische beheerweergave om te bepalen of een taak wordt geblokkeerd en wat deze blokkeert. Zie Sql Server-blokkeringsproblemenbegrijpen en oplossen voor meer informatie en voorbeelden.

Met de instelling LOCK_TIMEOUT kan een toepassing een maximale tijd instellen waarop een instructie wacht op een geblokkeerde resource. Wanneer een instructie langer heeft gewacht dan de LOCK_TIMEOUT instelling, wordt de geblokkeerde instructie automatisch geannuleerd en wordt foutbericht 1222 (Lock request time-out period exceeded) geretourneerd. Elke transactie die de verklaring bevat, wordt echter niet teruggedraaid. Daarom moet de toepassing een fouthandler hebben die het foutbericht 1222 kan ondervangen. Als een toepassing de fout niet onderschempelt, kan de toepassing zich er niet van bewust zijn dat een afzonderlijke instructie binnen een transactie is geannuleerd, maar de transactie actief blijft. Er kunnen fouten optreden omdat instructies later in de transactie mogelijk afhankelijk zijn van de instructie die nooit is uitgevoerd.

Door een fouthandler te implementeren die foutbericht 1222 opvangt, kan een toepassing de time-outsituatie afhandelen en herstelacties uitvoeren, zoals: automatisch de geblokkeerde instructie opnieuw verzenden en de hele transactie terugdraaien.

Belangrijk

Toepassingen die gebruikmaken van expliciete transacties en vereisen dat de transactie wordt beëindigd bij het ontvangen van fout 1222, moet de transactie expliciet terugdraaien als onderdeel van foutafhandeling. Zonder dit kunnen andere instructies onbedoeld worden uitgevoerd op dezelfde sessie terwijl de transactie actief blijft, wat leidt tot niet-gebonden groei van het transactielogboek en gegevensverlies als de transactie later wordt teruggedraaid.

Voer de LOCK_TIMEOUT-functie uit om de huidige @@LOCK_TIMEOUT-instelling te bepalen:

SELECT @@LOCK_TIMEOUT;
GO

Niveau van transactieisolatie aanpassen

READ COMMITTED is het standaardisolatieniveau voor de database-engine. Als een toepassing op een ander isolatieniveau moet werken, kan deze de volgende methoden gebruiken om het isolatieniveau in te stellen:

• Voer de instructie SET TRANSACTION ISOLATION LEVEL uit.
• ADO.NET toepassingen die gebruikmaken van de System.Data.SqlClient-naamruimte, kunnen een IsolationLevel-optie opgeven met behulp van de methode SqlConnection.BeginTransaction.
• Toepassingen die gebruikmaken van ADO kunnen de eigenschap Autocommit Isolation Levels instellen.
• Bij het starten van een transactie kunnen toepassingen die OLE DB gebruiken, ITransactionLocal::StartTransaction aanroepen met isoLevel ingesteld op het gewenste niveau van transactieisolatie. Wanneer u het isolatieniveau opgeeft in de modus automatisch toewijzen, kunnen toepassingen die GEBRUIKMAKEN van OLE DB de eigenschap DBPROPSET_SESSION instellen DBPROP_SESS_AUTOCOMMITISOLEVELS op het gewenste niveau van transactieisolatie.
• Toepassingen die gebruikmaken van ODBC kunnen het kenmerk SQL_COPT_SS_TXN_ISOLATION instellen met behulp van SQLSetConnectAttr.

Wanneer het isolatieniveau is opgegeven, werkt het vergrendelingsgedrag voor alle query's en DML-instructies (Data Manipulat Language) in de sessie op dat isolatieniveau. Het isolatieniveau blijft van kracht totdat de sessie wordt beëindigd of totdat het isolatieniveau is ingesteld op een ander niveau.

In het volgende voorbeeld wordt het SERIALIZABLE isolatieniveau ingesteld:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

Het isolatieniveau kan indien nodig worden overschreven voor afzonderlijke query- of DML-instructies door een hint op tabelniveau op te geven. Het opgeven van een hint op tabelniveau heeft geen invloed op andere instructies in de sessie.

Als u wilt bepalen welk niveau voor transactieisolatie momenteel is ingesteld, gebruikt u de DBCC USEROPTIONS-instructie, zoals wordt weergegeven in het volgende voorbeeld. De resultatenset kan verschillen van de resultatenset op uw systeem.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Dit is de resultaatset.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Vergrendelingshints

Hints voor vergrendeling kunnen worden opgegeven voor afzonderlijke tabelverwijzingen in de instructies SELECT, INSERT, UPDATE, DELETE en MERGE. De aanwijzingen specificeren het type vergrendeling of rijversiebeheer dat de database-engine voor de tabelgegevens gebruikt. Hints voor vergrendeling op tabelniveau kunnen worden gebruikt wanneer een nauwkeurigere controle over de typen vergrendelingen die op een object zijn verkregen, vereist is. Deze vergrendelingshints overschrijven het huidige transactie-isolatieniveau voor de sessie.

Notitie

Vergrendelingshints worden niet aanbevolen voor gebruik wanneer geoptimaliseerde vergrendeling is ingeschakeld. Hoewel tabel- en queryhints worden gehonoreerd, verminderen ze het voordeel van geoptimaliseerde vergrendeling. Zie Vergrendelingshints vermijden met geoptimaliseerde vergrendelingvoor meer informatie.

Zie tabelhints (Transact-SQL)voor meer informatie over de specifieke vergrendelingshints en hun gedrag.

Notitie

We raden u aan om hints voor vergrendeling op tabelniveau te gebruiken om het standaardvergrendelingsgedrag alleen te wijzigen wanneer dat nodig is. Het afdwingen van een vergrendelingsniveau kan de gelijktijdigheid nadelig beïnvloeden.

De database-engine moet mogelijk vergrendelingen verkrijgen bij het lezen van metagegevens, zelfs bij het verwerken van een instructie met een vergrendelingshint die aanvragen voor gedeelde vergrendelingen voorkomt bij het lezen van gegevens. Een SELECT-instructie die wordt uitgevoerd onder het READ UNCOMMITTED-isolatieniveau of bij gebruik van de NOLOCK-hint, verkrijgt geen gedeelde vergrendelingen bij het lezen van gegevens, maar zou soms vergrendelingen kunnen aanvragen bij het lezen van een systeemcatalogusweergave. Dit betekent dat een dergelijke SELECT instructie kan worden geblokkeerd wanneer een gelijktijdige transactie de metagegevens van de tabel wijzigt.

Zoals getoond in het volgende voorbeeld, worden sleutelbereikvergrendelingen die normaal worden gebruikt om SERIALIZABLE-transacties te behouden, niet verkregen als het transactieniveau is ingesteld op NOLOCKen de hint voor vergrendeling op tabelniveau SELECT wordt gebruikt met de instructie SERIALIZABLE.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
       resource_subtype,
       request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

-- End the transaction.
ROLLBACK;
GO

De enige vergrendeling die naar HumanResources.Employee verwijst, is een schemastabiliteitsvergrendeling (Sch-S). In dit geval is serializeerbaarheid niet meer gegarandeerd.

De LOCK_ESCALATION optie van ALTER TABLE vermijdt tabelsloten tijdens vergrendelingsescalatie en maakt HoBT-sloten op gepartitioneerde tabellen mogelijk. Deze optie is geen vergrendelingshint en kan worden gebruikt om vergrendelingsescalatie te verminderen. Zie ALTER TABLE (Transact-SQL)voor meer informatie.

Aanpassen van de vergrendeling voor een index

De database-engine maakt gebruik van een dynamische vergrendelingsstrategie die in de meeste gevallen automatisch de beste vergrendelingsgranulariteit voor query's kiest. U wordt aangeraden de standaardvergrendelingsniveaus niet te overschrijven, tenzij de toegangspatronen voor tabellen of indexen goed worden begrepen en consistent zijn, en er een probleem van resourceconflicten moet worden opgelost. Het overschrijven van een vergrendelingsniveau kan de gelijktijdige toegang tot een tabel of index aanzienlijk belemmeren. Als u bijvoorbeeld alleen vergrendelingen op tabelniveau opgeeft voor een grote tabel waartoe gebruikers intensief toegang hebben, kunnen knelpunten veroorzaken, omdat gebruikers moeten wachten tot de vergrendeling op tabelniveau wordt vrijgegeven voordat ze de tabel openen.

Er zijn enkele gevallen waarin het niet toestaan van pagina- of rijvergrendelingen nuttig kan zijn, als de toegangspatronen goed worden begrepen en consistent zijn. Een databasetoepassing maakt bijvoorbeeld gebruik van een opzoektabel die wekelijks wordt bijgewerkt in een batchproces. Gelijktijdige lezers openen de tabel met een gedeelde (S) vergrendeling en de wekelijkse batch-update krijgt toegang tot de tabel met een exclusieve (X) vergrendeling. Door pagina- en rijvergrendeling in de tabel uit te schakelen, vermindert u de vergrendelingsoverhead gedurende de week doordat lezers gelijktijdig toegang hebben tot de tabel via gedeelde tabelvergrendelingen. Wanneer de batchtaak wordt uitgevoerd, kan de update efficiënt worden voltooid omdat er een exclusieve tabelvergrendeling wordt verkregen.

Het uitschakelen van pagina- en rijvergrendeling is mogelijk of niet acceptabel omdat de wekelijkse batch-update de gelijktijdige lezers geen toegang tot de tabel biedt terwijl de update wordt uitgevoerd. Als de batchtaak slechts enkele rijen of pagina's wijzigt, kunt u het vergrendelingsniveau wijzigen om vergrendeling op rij- of paginaniveau toe te staan, waardoor andere sessies uit de tabel kunnen lezen zonder te blokkeren. Als de batchtaak een groot aantal updates heeft, is het verkrijgen van een exclusieve vergrendeling op de tabel mogelijk de beste manier om ervoor te zorgen dat de batchtaak efficiënt wordt uitgevoerd.

In sommige workloads kan een type impasse optreden wanneer twee gelijktijdige bewerkingen rijvergrendelingen op dezelfde tabel verkrijgen en elkaar vervolgens blokkeren omdat ze beide de pagina moeten vergrendelen. Het verbieden van rijvergrendelingen dwingt een van de bewerkingen te wachten, waardoor de deadlock wordt vermeden. Zie de gids Deadlocksvoor meer over deadlocks.

De granulariteit van vergrendeling die wordt gebruikt voor een index, kan worden ingesteld met behulp van de instructies CREATE INDEX en ALTER INDEX. Daarnaast kunnen de CREATE TABLE- en ALTER TABLE-instructies worden gebruikt om vergrendelingsgranulariteit in te stellen op PRIMARY KEY- en UNIQUE beperkingen. Voor compatibiliteit met eerdere versies kan de opgeslagen procedure van het sp_indexoption systeem ook de granulariteit instellen. Als u de huidige vergrendelingsoptie voor een bepaalde index wilt weergeven, gebruikt u de functie INDEXPROPERTY. Vergrendelingen op paginaniveau, vergrendelingen op rijniveau of zowel pagina- als rijniveauvergrendelingen kunnen niet worden toegestaan voor een bepaalde index.

Niet-toegestane vergrendelingen	Index geopend door
Paginaniveau	Vergrendelingen op rij- en tabelniveau
Rijniveau	Vergrendelingen op pagina- en tabelniveau
Paginaniveau en rijniveau	Vergrendelingen op tabelniveau

Geavanceerde transactiegegevens

Nest-transacties

Expliciete transacties kunnen worden genest. Dit is voornamelijk bedoeld ter ondersteuning van transacties in opgeslagen procedures die kunnen worden aangeroepen vanuit een proces dat al in een transactie staat of vanuit processen die geen actieve transactie hebben.

In het volgende voorbeeld ziet u het gebruik van geneste transacties. Als TransProc wordt aangeroepen wanneer een transactie actief is, wordt het resultaat van de geneste transactie in TransProc beheerd door de buitenste transactie en worden de INSERT-instructies doorgevoerd of teruggedraaid op basis van de doorvoer of terugdraaien van de buitenste transactie. Als TransProc wordt uitgevoerd door een proces dat geen openstaande transactie heeft, bevestigt COMMIT TRANSACTION aan het einde van de procedure de INSERT instructies.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

Het doorvoeren van interne transacties wordt genegeerd door de database-engine wanneer een buitenste transactie actief is. De transactie wordt vastgelegd of teruggedraaid op basis van de vastlegging of terugdraaien aan het einde van de uiterste transactie. Als de buitenste transactie wordt doorgevoerd, worden de binnenste transacties ook uitgevoerd. Als de buitenste transactie wordt teruggedraaid, worden alle interne transacties ook teruggedraaid, ongeacht of de binnenste transacties afzonderlijk zijn doorgevoerd.

Elke aanroep naar COMMIT TRANSACTION of COMMIT WORK is van toepassing op de laatst uitgevoerde BEGIN TRANSACTION. Als de BEGIN TRANSACTION-instructies zijn genest, is een COMMIT-instructie alleen van toepassing op de laatste geneste transactie, de binnenste transactie. Zelfs als een COMMIT TRANSACTION transaction_name-instructie binnen een geneste transactie verwijst naar de transactienaam van de buitenste transactie, is de commit alleen van toepassing op de binnenste transactie.

Het is niet toegestaan dat de parameter transaction_name van een ROLLBACK TRANSACTION instructie verwijst naar de binnenste transactie in een set benoemde geneste transacties. transaction_name kan alleen verwijzen naar de naam van de buitenste transactie. Als een ROLLBACK TRANSACTION transaction_name-instructie met de naam van de buitenste transactie wordt uitgevoerd op een willekeurig niveau van een set geneste transacties, worden alle geneste transacties teruggedraaid. Als een ROLLBACK WORK- of ROLLBACK TRANSACTION-instructie zonder transaction_name parameter wordt uitgevoerd op een willekeurig niveau van een set geneste transacties, worden alle geneste transacties teruggedraaid, inclusief de buitenste transactie.

De @@TRANCOUNT-functie registreert het huidige transactienestniveau. Met elke BEGIN TRANSACTION-instructie wordt @@TRANCOUNT met één verhoogd. Elke COMMIT TRANSACTION- of COMMIT WORK-instructie verlaagt @@TRANCOUNT met één. Wanneer een ROLLBACK WORK- of ROLLBACK TRANSACTION-instructie geen transactienaam heeft, worden alle geneste transacties teruggedraaid en wordt @@TRANCOUNT verlaagd naar 0. Een ROLLBACK TRANSACTION die gebruikmaakt van de transactienaam van de buitenste transactie in een set geneste transacties draait alle geneste transacties terug en verlaagt @@TRANCOUNT tot 0. Als u wilt bepalen of u al een transactie hebt, SELECT @@TRANCOUNT om te zien of deze 1 of meer is. Als @@TRANCOUNT 0 is, bevindt u zich niet in een transactie.

Afhankelijke sessies gebruiken

Gebonden sessies vereenvoudigen de coördinatie van acties voor meerdere sessies op dezelfde server. Met afhankelijke sessies kunnen twee of meer sessies dezelfde transactie en vergrendelingen delen en kunnen zonder vergrendelingsconflicten aan dezelfde gegevens worden gewerkt. Gebonden sessies kunnen worden gemaakt op basis van meerdere sessies binnen dezelfde toepassing of vanuit meerdere toepassingen met afzonderlijke sessies.

Als u wilt deelnemen aan een afhankelijke sessie, roept een sessie aan sp_getbindtoken of srv_getbindtoken (via Open Data Services) om een bindingstoken op te halen. Een bindingstoken is een tekenreeks die elke afhankelijke transactie uniek identificeert. Het bindingstoken wordt vervolgens naar de andere sessies verzonden om te worden gebonden aan de huidige sessie. De andere sessies koppelen aan de transactie door sp_bindsessionaan te roepen, met behulp van de bind-token ontvangen van de eerste sessie.

Notitie

Een sessie moet een actieve gebruikerstransactie hebben om sp_getbindtoken of srv_getbindtoken te laten slagen.

Bindingstokens moeten worden verzonden vanuit de toepassingscode die de eerste sessie maakt naar de toepassingscode die vervolgens hun sessies verbindt met de eerste sessie. Er is geen Transact-SQL-instructie of API-functie die een toepassing kan gebruiken om het bindingstoken op te halen voor een transactie die door een ander proces is gestart. Enkele van de methoden die kunnen worden gebruikt om een bindingstoken te verzenden, zijn onder andere:

• Als de sessies allemaal worden gestart vanuit hetzelfde toepassingsproces, kunnen bindingstokens worden opgeslagen in het globale geheugen of als parameter worden doorgegeven aan functies.

• Als de sessies worden uitgevoerd vanuit afzonderlijke toepassingsprocessen, kunnen bindingstokens worden verzonden met behulp van IPC (Interprocess Communication), zoals een externe procedure-aanroep (RPC) of dynamische gegevensuitwisseling (DDE).

• Bindingstokens kunnen worden opgeslagen in een tabel in een exemplaar van de database-engine die kan worden gelezen door processen die verbinding willen maken met de eerste sessie.

Slechts één sessie in een set gebonden sessies kan op elk gewenst moment actief zijn. Als een sessie een instructie uitvoert op het exemplaar of resultaten heeft die in behandeling zijn van het exemplaar, heeft geen andere sessie die is gebonden aan hetzelfde token, toegang tot het exemplaar totdat de huidige sessie de verwerking heeft voltooid of de huidige instructie annuleert. Als het exemplaar bezig is met het verwerken van een instructie van een andere van de afhankelijke sessies, treedt er een fout op die aangeeft dat de transactieruimte in gebruik is en dat de sessie het later opnieuw moet proberen.

Wanneer u sessies bindt, behoudt elke sessie de instelling voor isolatieniveau. Het gebruik van SET TRANSACTION ISOLATION LEVEL om de instelling van het isolatieniveau van één sessie te wijzigen, heeft geen invloed op de instelling van een andere sessie die is gebonden aan hetzelfde token.

Typen gebonden sessies

De twee soorten gebonden sessies zijn lokaal en gedistribueerd.

• lokale gebonden sessie Hiermee kunnen afhankelijke sessies de transactieruimte van één transactie delen in één exemplaar van de database-engine.

• Gedistribueerde gebonden sessie Hiermee kunnen gebonden sessies dezelfde transactie delen over twee of meer exemplaren totdat de hele transactie is doorgevoerd of teruggedraaid met behulp van Microsoft Distributed Transaction Coordinator (MS DTC).

Gedistribueerde afhankelijke sessies worden niet geïdentificeerd door een tekenreeksbindingstoken; ze worden geïdentificeerd door gedistribueerde transactieidentificatienummers. Als een afhankelijke sessie betrokken is bij een lokale transactie en een RPC uitvoert op een externe server met SET REMOTE_PROC_TRANSACTIONS ON, wordt de lokale gebonden transactie automatisch gepromoveerd naar een gedistribueerde gebonden transactie door MS DTC en wordt een MS DTC-sessie gestart.

Wanneer afhankelijke sessies gebruiken

In eerdere versies van SQL Server werden gebonden sessies voornamelijk gebruikt bij het ontwikkelen van uitgebreide opgeslagen procedures die Transact-SQL instructies moeten uitvoeren namens het proces dat ze aanroept. Als het aanroepende proces een bindingstoken als één parameter van de uitgebreide opgeslagen procedure doorgeeft, kan de procedure de transactieruimte van het aanroepende proces samenvoegen, waardoor de uitgebreide opgeslagen procedure wordt geïntegreerd met het aanroepende proces.

In de database-engine zijn opgeslagen procedures die zijn geschreven met CLR veiliger, schaalbaar en stabieler dan uitgebreide opgeslagen procedures. Door CLR opgeslagen procedures maken gebruik van het SqlContext-object om deel te nemen aan de context van de aanroepende sessie, niet sp_bindsession.

Gebonden sessies kunnen worden gebruikt voor het ontwikkelen van toepassingen met drie lagen waarin bedrijfslogica wordt opgenomen in afzonderlijke programma's die samenwerken aan één zakelijke transactie. Deze programma's moeten worden gecodeerd om de toegang tot een database zorgvuldig te coördineren. Omdat de twee sessies dezelfde vergrendelingen delen, mogen de twee programma's niet tegelijkertijd dezelfde gegevens wijzigen. Op elk moment kan slechts één sessie werken als onderdeel van de transactie; er kan geen parallelle uitvoering zijn. De transactie kan alleen worden overgeschakeld tussen sessies op goed gedefinieerde rendementspunten, zoals wanneer alle DML-instructies zijn voltooid en hun resultaten zijn opgehaald.

Code efficiënte transacties

Het is belangrijk om transacties zo kort mogelijk te houden. Wanneer een transactie wordt gestart, moet een databasebeheersysteem (DBMS) veel resources bevatten tot het einde van de transactie om de eigenschappen atomiciteit, consistentie, isolatie en duurzaamheid van de transactie te beschermen. Als gegevens worden gewijzigd, moeten de gewijzigde rijen worden beveiligd met exclusieve vergrendelingen die voorkomen dat andere transacties de rijen lezen en moeten exclusieve vergrendelingen worden bewaard totdat de transactie is doorgevoerd of teruggedraaid. Afhankelijk van de instellingen voor het transactie-isolatieniveau kunnen SELECT instructies vergrendelingen verkrijgen die moeten worden behouden totdat de transactie is bevestigd of teruggedraaid. Met name in systemen met veel gebruikers moeten transacties zo kort mogelijk worden gehouden om vergrendelingsconflicten voor resources tussen gelijktijdige verbindingen te verminderen. Langlopende, inefficiënte transacties zijn mogelijk geen probleem met kleine aantallen gebruikers, maar ze zijn zeer problematisch in een systeem met duizenden gebruikers. Vanaf SQL Server 2014 (12.x) ondersteunt de database-engine vertraagde duurzame transacties. Vertraagde duurzame transacties kunnen de schaalbaarheid en prestaties verbeteren, maar bieden geen garantie voor duurzaamheid. Zie Control Transaction Durabilityvoor meer informatie.

Coderichtlijnen

Dit zijn de richtlijnen voor het coderen van efficiënte transacties:

• Voor een transactie is geen invoer van gebruikers vereist. Haal alle vereiste invoer van gebruikers op voordat een transactie wordt gestart. Als er extra gebruikersinvoer is vereist tijdens een transactie, rolt u de huidige transactie terug en start u de transactie opnieuw nadat de gebruikersinvoer is opgegeven. Zelfs als gebruikers onmiddellijk reageren, zijn menselijke reactietijden enorm langzamer dan computersnelheden. Alle resources die door de transactie worden vastgehouden, worden voor een extreem lange tijd vastgehouden, wat het potentieel heeft om te leiden tot blokkerende problemen. Als gebruikers niet reageren, blijft de transactie actief en worden kritieke resources vergrendeld totdat ze reageren, wat mogelijk enkele minuten of zelfs uren niet gebeurt.

• Open geen transactie tijdens het bladeren door gegevens, indien mogelijk. Transacties mogen pas worden gestart als alle voorlopige gegevensanalyse is voltooid.

• Houd de transactie zo kort mogelijk. Nadat u de wijzigingen kent die moeten worden aangebracht, start u een transactie, voert u de wijzigingsinstructies uit en voert u deze onmiddellijk door of terug. Open de transactie niet voordat deze is vereist.

• Als u het blokkeren wilt verminderen, kunt u overwegen om een isolatieniveau op basis van rijversies te gebruiken voor alleen-lezenquery's.

• Maak intelligent gebruik van lagere isolatieniveaus voor transacties. Veel toepassingen kunnen worden gecodeerd om het READ COMMITTED transactieisolatieniveau te gebruiken. Voor weinig transacties is het SERIALIZABLE niveau van transactieisolatie vereist.

• Maak intelligent gebruik van optimistische gelijktijdigheidsopties. In een systeem met een lage kans op gelijktijdige updates kan de overhead van het omgaan met een incidentele fout 'iemand anders uw gegevens heeft gewijzigd nadat u deze hebt gelezen' veel lager zijn dan de overhead van het altijd vergrendelen van rijen terwijl ze worden gelezen.

• Krijg toegang tot de minste hoeveelheid gegevens die mogelijk is tijdens een transactie. Dit vermindert het aantal vergrendelde rijen, waardoor conflicten tussen transacties worden verminderd.

• Vermijd waar mogelijk pessimistische vergrendelingshints zoals HOLDLOCK. Hints zoals HOLDLOCK of SERIALIZABLE isolatieniveau kunnen ertoe leiden dat processen zelfs op gedeelde vergrendelingen wachten en gelijktijdigheid verminderen.

• Vermijd het gebruik van impliciete transacties indien mogelijk. Impliciete transacties kunnen onvoorspelbaar gedrag veroorzaken vanwege hun aard. Zie impliciete transacties en gelijktijdigheidsproblemen.

Impliciete transacties en het voorkomen van gelijktijdigheid en resourceproblemen

Als u gelijktijdigheid en resourceproblemen wilt voorkomen, beheert u impliciete transacties zorgvuldig. Wanneer u impliciete transacties gebruikt, start de volgende Transact-SQL instructie na COMMIT of ROLLBACK automatisch een nieuwe transactie. Dit kan ertoe leiden dat een nieuwe transactie wordt geopend terwijl de toepassing door gegevens bladert, of zelfs wanneer er invoer van de gebruiker nodig is. Nadat u de laatste transactie hebt voltooid die nodig is om gegevenswijzigingen te beveiligen, schakelt u impliciete transacties uit totdat een transactie opnieuw is vereist om gegevenswijzigingen te beveiligen. Met dit proces kan de database-engine de modus voor automatisch toewijzen gebruiken terwijl de toepassing bladert naar gegevens en invoer van de gebruiker ophaalt.

Bovendien, wanneer het SNAPSHOT isolatieniveau is ingeschakeld, hoewel een nieuwe transactie geen vergrendelingen bevat, voorkomt een langlopende transactie dat de oude versies uit het versiearchief worden verwijderd.

Langlopende transacties beheren

Een langlopende transactie is een actieve transactie die niet tijdig is doorgevoerd of teruggedraaid. Als het begin en einde van een transactie bijvoorbeeld worden beheerd door de gebruiker, is een typische oorzaak van een langlopende transactie een gebruiker die een transactie start en vervolgens vertrekt terwijl de transactie wacht op een reactie van de gebruiker.

Een langdurige transactie kan als volgt ernstige problemen voor een database veroorzaken:

• Als een serverexemplaar wordt afgesloten nadat een actieve transactie veel niet-doorgevoerde wijzigingen heeft uitgevoerd, kan de herstelfase van de volgende herstart veel langer duren dan de tijd die is opgegeven door de serverconfiguratieoptie recovery interval of de optie ALTER DATABASE ... SET TARGET_RECOVERY_TIME. Met deze opties kunt u respectievelijk actieve en indirecte controlepunten beheren. Zie Database-controlepunten (SQL Server)voor meer informatie over de typen controlepunten.

• Belangrijker nog, hoewel een wachtende transactie maar erg weinig logboek kan genereren, houdt het het afkappen van het logboek voor onbepaalde tijd tegen, waardoor het transactielogboek toeneemt en mogelijk vol raakt. Als het transactielogboek vol raakt, kan de database geen schrijfbewerkingen meer uitvoeren. Zie architectuur en beheerhandleiding voor SQL Server-transactielogboeken, Problemen met een volledig transactielogboek (SQL Server-fout 9002) oplossen en Het transactielogboekvoor meer informatie.

Belangrijk

In Azure SQL Database worden niet-actieve transacties (transacties die zes uur niet naar het transactielogboek zijn geschreven) automatisch beëindigd om resources vrij te maken.

Langlopende transacties detecteren

Gebruik een van de volgende opties om te zoeken naar langlopende transacties:

• sys.dm_tran_database_transactions

  Deze dynamische beheerweergave retourneert informatie over transacties op databaseniveau. Voor een langlopende transactie zijn kolommen van bijzonder belang de tijd van de eerste logboekrecord (database_transaction_begin_time), de huidige status van de transactie (database_transaction_state) en het logboekreeksnummer (LSN) van de beginnen record in het transactielogboek (database_transaction_begin_lsn).

  Zie sys.dm_tran_database_transactions (Transact-SQL)voor meer informatie.

• DBCC OPENTRAN

  Met deze instructie kunt u de gebruikers-id van de eigenaar van de transactie identificeren, zodat u mogelijk de bron van de transactie kunt opsporen voor de juiste beëindiging (doorvoeren of terugdraaien). Zie DBCC OPENTRAN (Transact-SQL)voor meer informatie.

Een transactie beëindigen

Als u een transactie voor een specifieke sessie wilt beëindigen, gebruikt u de KILL-instructie. Gebruik deze instructie echter heel zorgvuldig, vooral wanneer kritieke processen worden uitgevoerd. Zie KILL (Transact-SQL)voor meer informatie.

Deadlocks

Deadlocks zijn een complex onderwerp met betrekking tot vergrendeling, maar verschillen van blokkeren.

• Zie de handleiding voor deadlocksvoor meer informatie over deadlocks, waaronder monitoring, diagnose en voorbeelden.
• Zie Impasses analyseren en voorkomen in Azure SQL Databasevoor meer informatie over impasses die specifiek zijn voor Azure SQL Database.

Verwante inhoud

• Problemen met blokkerende SQL Server begrijpen en oplossen
• Problemen met blokkeren van Azure SQL Database begrijpen en oplossen
• dynamische beheerweergaven en -functies met betrekking tot transacties (Transact-SQL)
• Overhead van rij-versiebeheer
• sys.dm_tran_locks (Transact-SQL)