Taal
Notitie
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen u aan te melden of de directory te wijzigen.
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen de mappen te wijzigen.
Isolatie van momentopnamen verbetert gelijktijdigheid voor OLTP-toepassingen.
Inzicht in Snapshotisolatie en rijversies
Zodra isolatie van momentopnamen is ingeschakeld, moeten bijgewerkte rijversies voor elke transactie worden onderhouden. Vóór SQL Server 2019 werden deze versies opgeslagen in tempdb. SQL Server 2019 introduceert een nieuwe functie, Accelerated Database Recovery (ADR), waarvoor een eigen set rijversies is vereist. Dus, vanaf SQL Server 2019, als ADR niet is ingeschakeld, worden rijversies zoals gebruikelijk bewaard in tempdb. Als ADR is ingeschakeld, worden alle rijversies, die betrekking hebben op isolatie van momentopnamen en ADR, bewaard in de PERSISTENT Version Store (PVS) van ADR, die zich in de gebruikersdatabase bevindt in een bestandsgroep die de gebruiker opgeeft. Een uniek transactiereeksnummer identificeert elke transactie en deze unieke nummers worden vastgelegd voor elke rijversie. De transactie maakt gebruik van de meest recente rijversies met een volgnummer dat voorafgaat aan het volgnummer van de transactie. Nieuwere rijversies die zijn gemaakt nadat de transactie is gestart, worden genegeerd door de transactie.
De term 'momentopname' weerspiegelt het feit dat alle query's in de transactie dezelfde versie of momentopname van de database zien, op basis van de status van de database op het moment waarop de transactie begint. Er worden geen vergrendelingen verkregen op de onderliggende gegevensrijen of gegevenspagina's in een momentopnametransactie, waardoor andere transacties kunnen worden uitgevoerd zonder te worden geblokkeerd door een eerdere niet-voltooide transactie. Transacties die gegevens wijzigen, blokkeren transacties die gegevens lezen niet en transacties die gegevens lezen, blokkeren geen transacties die gegevens schrijven, zoals normaal gesproken onder het standaardisolatieniveau READ COMMITTED in SQL Server. Dit niet-blokkerende gedrag vermindert ook de kans op impasses voor complexe transacties aanzienlijk.
Isolatie van momentopnamen maakt gebruik van een optimistisch gelijktijdigheidsmodel. Als een momentopnametransactie probeert wijzigingen door te voeren aan gegevens die zijn gewijzigd sinds de transactie is gestart, wordt de transactie teruggedraaid en wordt er een fout gegenereerd. U kunt dit voorkomen door UPDLOCK hints te gebruiken voor SELECT instructies die gegevens benaderen die moeten worden gewijzigd. Zie hints (Transact-SQL)voor meer informatie.
Snapshot-isolatie moet worden ingeschakeld door de ALLOW_SNAPSHOT_ISOLATION ON databaseoptie in te stellen voordat deze wordt gebruikt in transacties. Hiermee wordt het mechanisme voor het opslaan van rijversies in de tijdelijke database (tempdb) geactiveerd. U moet isolatie van momentopnamen inschakelen in elke database die deze gebruikt met de instructie Transact-SQL ALTER DATABASE . In dit opzicht verschilt de isolatie van momentopnamen van de traditionele isolatieniveaus van READ COMMITTED, REPEATABLE READSERIALIZABLEen READ UNCOMMITTED, waarvoor geen configuratie is vereist. Met de volgende instructies/uitspraken worden READ COMMITTED transacties en met rijversies uitgevoerde SNAPSHOT leesbewerkingen ingeschakeld:
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
Als u de READ_COMMITTED_SNAPSHOT ON optie instelt, hebt u toegang tot geversioneerde rijen onder het standaardisolatieniveau READ COMMITTED. Als de READ_COMMITTED_SNAPSHOT optie is ingesteld op OFF, moet u voor elke sessie expliciet het isolatieniveau voor momentopnamen instellen om toegang te krijgen tot rijen met versies.
Gelijktijdigheid beheren met isolatieniveaus
Het isolatieniveau waaronder een Transact-SQL instructie wordt uitgevoerd, bepaalt het vergrendelings- en rijversiegedrag. Een isolatieniveau heeft een verbindingsbreed bereik en zodra een verbinding met de SET TRANSACTION ISOLATION LEVEL instructie is ingesteld, blijft dit van kracht totdat de verbinding is gesloten of een ander isolatieniveau is ingesteld. Wanneer een verbinding wordt gesloten en aan de pool wordt geretourneerd, blijft het isolatieniveau van de laatste SET TRANSACTION ISOLATION LEVEL instructie behouden. Volgende verbindingen die een gegroepeerde verbinding hergebruiken, gebruiken het isolatieniveau dat van kracht was op het moment dat de verbinding is gegroepeerd.
Afzonderlijke query's die binnen een verbinding worden uitgegeven, kunnen vergrendelingshints bevatten die de isolatie voor één instructie of transactie wijzigen, maar geen invloed hebben op het isolatieniveau van de verbinding. Isolatieniveaus of vergrendelingshints die zijn ingesteld in opgeslagen procedures of functies wijzigen niet het isolatieniveau van de verbinding die deze aanroept en zijn alleen van kracht voor de duur van de opgeslagen procedure of functieaanroep.
Vier isolatieniveaus die zijn gedefinieerd in de SQL-92-standaard, worden ondersteund in vroege versies van SQL Server:
READ UNCOMMITTEDis het minst beperkende isolatieniveau omdat hiermee vergrendelingen worden genegeerd die door andere transacties worden geplaatst. Transacties die worden uitgevoerd onderREAD UNCOMMITTED, kunnen gewijzigde gegevenswaarden lezen die nog niet zijn gecommit door andere transacties; deze worden 'dirty reads' genoemd.READ COMMITTEDis het standaardisolatieniveau voor SQL Server. Het voorkomt vuile leesbewerkingen door op te geven dat instructies geen gegevenswaarden kunnen lezen die zijn gewijzigd, maar die nog niet zijn doorgevoerd door andere transacties. Andere transacties kunnen nog steeds gegevens wijzigen, invoegen of verwijderen tussen uitvoeringen van afzonderlijke instructies binnen de huidige transactie, wat resulteert in niet-herhaalbare lees- of fantoomgegevens.REPEATABLE READis een meer beperkend isolatieniveau danREAD COMMITTED. Het omvatREAD COMMITTEDen geeft bovendien aan dat er geen andere transacties gegevens kunnen wijzigen of verwijderen die door de huidige transactie zijn gelezen totdat de huidige transactie wordt vastgelegd. De gelijktijdigheid is lager dan bijREAD COMMITTEDomdat gedeelde vergrendelingen voor leesgegevens worden bewaard voor de duur van de transactie in plaats van aan het einde van elke instructie te worden vrijgegeven.SERIALIZABLEis het meest beperkende isolatieniveau, omdat het volledige sleutelbereiken vergrendelt en de vergrendelingen vasthoudt totdat de transactie is voltooid. Het omvatREPEATABLE READen voegt de beperking toe dat andere transacties geen nieuwe rijen kunnen invoegen in bereiken die door de transactie zijn gelezen totdat de transactie is voltooid.
Voor meer informatie raadpleeg de Handleiding voor transactievergrendeling en rijversiebeheer.
Extensies op isolatieniveau voor momentopnamen
SQL Server heeft uitbreidingen geïntroduceerd voor de SQL-92-isolatieniveaus met de introductie van het SNAPSHOT isolatieniveau en een extra implementatie van READ COMMITTED.
SNAPSHOTisolatie garandeert dat gegevens die binnen een transactie worden gelezen, nooit wijzigingen van andere gelijktijdige transacties weerspiegelen. De transactie maakt gebruik van de gegevensrijversies die bestaan wanneer de transactie begint. Er worden geen vergrendelingen op de gegevens geplaatst wanneer deze worden gelezen, dusSNAPSHOTtransacties voorkomen niet dat andere transacties gegevens schrijven. Transacties die gegevens schrijven, blokkeren niet dat momentopnametransacties gegevens lezen. U moet snapshot-isolatie inschakelen door de databaseoptieALLOW_SNAPSHOT_ISOLATIONin te stellen om deze te kunnen gebruiken.De
READ_COMMITTED_SNAPSHOTdatabaseoptie bepaalt hoeREAD COMMITTEDgegevens leest. Met deze optieOFF, de standaardinstelling in SQL Server, gebruikt de Database Engine gedeelde vergrendelingen om leesbewerkingen te beveiligen. Met deze optieON,READ COMMITTEDgebruikt rijversies op statementniveau in plaats van gedeelde vergrendelingen om leesbewerkingen te beveiligen.
Hoe isolatie van momentopnamen en rijversiebeheer werken
Wanneer het SNAPSHOT-isolatieniveau is ingeschakeld, wordt er telkens wanneer een rij wordt bijgewerkt een kopie van de oorspronkelijke rij opgeslagen in tempdb, en wordt er een transactiesequentienummer aan de rij toegevoegd. Hier volgt een reeks gebeurtenissen die zich voordoen:
Er wordt een nieuwe transactie gestart en er wordt een transactiereeksnummer toegewezen.
De database-engine leest een rij in de transactie en haalt de rijversie op van
tempdbwiens volgnummer het dichtst bij en lager is dan het transactiereeksnummer.De database-engine controleert of het transactiereeksnummer zich niet in de lijst met transactiereeksnummers bevindt van de niet-doorgevoerde transacties die actief zijn wanneer de momentopnametransactie is gestart.
De transactie leest de versie van de rij van
tempdbzoals die was bij het begin van de transactie. Er worden geen nieuwe rijen ingevoegd nadat de transactie is gestart, omdat deze reeksnummerwaarden hoger zijn dan de waarde van het transactiereeksnummer.De huidige transactie ziet rijen die zijn verwijderd nadat de transactie is gestart, omdat er een rijversie
tempdbis met een lagere reeksnummerwaarde.
Het netto-effect van isolatie van momentopnamen is dat de transactie alle gegevens ziet zoals deze aan het begin van de transactie bestonden, zonder vergrendelingen op de onderliggende tabellen te respecteren of te plaatsen. Dit kan leiden tot prestatieverbeteringen in situaties waarin sprake is van conflicten.
Een momentopnametransactie maakt altijd gebruik van optimistische gelijktijdigheidscontrole, waarbij vergrendelingen worden vermeden die zouden voorkomen dat andere transacties rijen bijwerken. Als een momentopnametransactie probeert een update door te voeren naar een rij die is gewijzigd nadat de transactie is gestart, wordt de transactie teruggedraaid en wordt er een fout gegenereerd.
Werken met isolatie van momentopnamen in ADO.NET
Als u een transactie wilt starten met isolatie van momentopnamen, schakelt u ALLOW_SNAPSHOT_ISOLATION in voor de database en geeft u Snapshot door aan BeginTransaction. Met de databaseoptie READ_COMMITTED_SNAPSHOT worden rijversie-gebaseerde leesbewerkingen op het isolatieniveau Read Committed geregeld; hiermee wordt geen snapshotisolatie voor een transactie geselecteerd. In het volgende fragment wordt ervan uitgegaan dat dit connection een open exemplaar van de SqlConnection klasse is.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Voorbeeld
In het volgende voorbeeld ziet u hoe de verschillende isolatieniveaus zich gedragen door toegang te krijgen tot vergrendelde gegevens en het is niet bedoeld om te worden gebruikt in productiecode.
De code maakt verbinding met de AdventureWorks voorbeelddatabase in SQL Server en maakt een tabel met de naam TestSnapshot en voegt één rij met gegevens in. De code maakt gebruik van de ALTER DATABASE Transact-SQL instructie om snapshot-isolatie voor de database in te schakelen, maar stelt de READ_COMMITTED_SNAPSHOT optie niet in, waardoor het standaardgedrag van isolatieniveau READ COMMITTED van kracht blijft. De code voert vervolgens de volgende acties uit:
Het begint, maar voltooit
sqlTransaction1niet, waarbij hetSERIALIZABLE-isolatieniveau wordt gebruikt om een updatetransactie te starten. Dit heeft het effect van het vergrendelen van de tabel.Er wordt een tweede verbinding geopend en er wordt een tweede transactie gestart met behulp van het
SNAPSHOTisolatieniveau om de gegevens in deTestSnapshottabel te lezen. Omdat snapshot-isolatie is ingeschakeld, kan deze transactie de gegevens lezen die bestonden voordatsqlTransaction1werd gestart.Er wordt een derde verbinding geopend en er wordt een transactie gestart met behulp van het
READ COMMITTEDisolatieniveau om te proberen de gegevens in de tabel te lezen. In dit geval kan de code de gegevens niet lezen omdat deze niet voorbij de vergrendelingen kan lezen die in de eerste transactie op de tabel zijn geplaatst en er een time-out optreedt. Hetzelfde resultaat zou optreden als deREPEATABLE READ- enSERIALIZABLE-isolatieniveaus werden gebruikt, omdat deze isolatieniveaus ook niet voorbij de vergrendelingen heen kunnen lezen die in de eerste transactie zijn geplaatst.Er wordt een vierde verbinding geopend en er wordt een transactie gestart met het isolatieniveau
READ UNCOMMITTED, dat een dirty read uitvoert van de niet-vastgelegde waarde insqlTransaction1. Deze waarde bestaat mogelijk nooit in de database als de eerste transactie niet is doorgevoerd.De eerste transactie wordt teruggedraaid en opgeschoond door de
TestSnapshottabel te verwijderen en de isolatie van momentopnamen voor deAdventureWorksdatabase uit te schakelen.
Notitie
In de volgende voorbeelden wordt dezelfde verbindingsreeks gebruikt waarbij groepsgewijze verbindingen zijn uitgeschakeld. Als een verbinding is gegroepeerd, wordt bij het opnieuw instellen van het isolatieniveau het isolatieniveau op de server niet opnieuw ingesteld. Als gevolg hiervan beginnen volgende verbindingen die gebruikmaken van dezelfde gegroepeerde binnenverbinding met hun isolatieniveaus die zijn ingesteld op die van de gegroepeerde verbinding. Een alternatief voor het uitschakelen van groepsgewijze verbindingen is het expliciet instellen van het isolatieniveau voor elke verbinding.
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(connectionString))
{
// Drop the TestSnapshot table if it exists
connection1.Open();
SqlCommand command1 = connection1.CreateCommand();
command1.CommandText = "IF EXISTS "
+ "(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') "
+ "DROP TABLE TestSnapshot";
try
{
command1.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Enable Snapshot isolation
command1.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
command1.ExecuteNonQuery();
// Create a table named TestSnapshot and insert one row of data
command1.CommandText =
"CREATE TABLE TestSnapshot (ID int primary key, valueCol int)";
command1.ExecuteNonQuery();
command1.CommandText =
"INSERT INTO TestSnapshot VALUES (1,1)";
command1.ExecuteNonQuery();
// Begin, but do not complete, a transaction to update the data
// with the Serializable isolation level, which locks the table
// pending the commit or rollback of the update. The original
// value in valueCol was 1, the proposed new value is 22.
SqlTransaction transaction1 =
connection1.BeginTransaction(IsolationLevel.Serializable);
command1.Transaction = transaction1;
command1.CommandText =
"UPDATE TestSnapshot SET valueCol=22 WHERE ID=1";
command1.ExecuteNonQuery();
// Open a second connection to AdventureWorks
using (SqlConnection connection2 = new(connectionString))
{
connection2.Open();
// Initiate a second transaction to read from TestSnapshot
// using Snapshot isolation. This will read the original
// value of 1 since transaction1 has not yet committed.
SqlCommand command2 = connection2.CreateCommand();
SqlTransaction transaction2 =
connection2.BeginTransaction(IsolationLevel.Snapshot);
command2.Transaction = transaction2;
command2.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
using (SqlDataReader reader2 = command2.ExecuteReader())
{
while (reader2.Read())
{
Console.WriteLine("Expected 1,1 Actual "
+ reader2.GetValue(0)
+ "," + reader2.GetValue(1));
}
}
transaction2.Commit();
}
// Open a third connection to AdventureWorks and
// initiate a third transaction to read from TestSnapshot
// using ReadCommitted isolation level. This transaction
// will not be able to view the data because of
// the locks placed on the table in transaction1
// and will time out after 4 seconds.
// You would see the same behavior with the
// RepeatableRead or Serializable isolation levels.
using (SqlConnection connection3 = new(connectionString))
{
connection3.Open();
SqlCommand command3 = connection3.CreateCommand();
SqlTransaction transaction3 =
connection3.BeginTransaction(IsolationLevel.ReadCommitted);
command3.Transaction = transaction3;
command3.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
command3.CommandTimeout = 4;
try
{
using (SqlDataReader sqldatareader3 = command3.ExecuteReader())
{
while (sqldatareader3.Read())
{
Console.WriteLine("You should never hit this.");
}
}
transaction3.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Expected timeout expired exception: "
+ ex.Message);
transaction3.Rollback();
}
}
// Open a fourth connection to AdventureWorks and
// initiate a fourth transaction to read from TestSnapshot
// using the ReadUncommitted isolation level. ReadUncommitted
// will not hit the table lock, and will allow a dirty read
// of the proposed new value 22 for valueCol. If the first
// transaction rolls back, this value will never actually have
// existed in the database.
using (SqlConnection connection4 = new(connectionString))
{
connection4.Open();
SqlCommand command4 = connection4.CreateCommand();
SqlTransaction transaction4 =
connection4.BeginTransaction(IsolationLevel.ReadUncommitted);
command4.Transaction = transaction4;
command4.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
using (SqlDataReader reader4 = command4.ExecuteReader())
{
while (reader4.Read())
{
Console.WriteLine("Expected 1,22 Actual "
+ reader4.GetValue(0)
+ "," + reader4.GetValue(1));
}
}
transaction4.Commit();
}
// Roll back the first transaction
transaction1.Rollback();
}
// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new(connectionString))
{
connection5.Open();
SqlCommand command5 = connection5.CreateCommand();
command5.CommandText = "DROP TABLE TestSnapshot";
SqlCommand command6 = connection5.CreateCommand();
command6.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
try
{
command5.ExecuteNonQuery();
command6.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Console.WriteLine("Done!");
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()
Using connection1 As New SqlConnection(connectionString)
' Drop the TestSnapshot table if it exists
connection1.Open()
Dim command1 As SqlCommand = connection1.CreateCommand
command1.CommandText = "IF EXISTS " & _
"(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') " _
& "DROP TABLE TestSnapshot"
Try
command1.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
' Enable SNAPSHOT isolation
command1.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON"
command1.ExecuteNonQuery()
' Create a table named TestSnapshot and insert one row of data
command1.CommandText = _
"CREATE TABLE TestSnapshot (ID int primary key, valueCol int)"
command1.ExecuteNonQuery()
command1.CommandText = _
"INSERT INTO TestSnapshot VALUES (1,1)"
command1.ExecuteNonQuery()
' Begin, but do not complete, a transaction to update the data
' with the Serializable isolation level, which locks the table
' pending the commit or rollback of the update. The original
' value in valueCol was 1, the proposed new value is 22.
Dim transaction1 As SqlTransaction = _
connection1.BeginTransaction(IsolationLevel.Serializable)
command1.Transaction = transaction1
command1.CommandText = _
"UPDATE TestSnapshot SET valueCol=22 WHERE ID=1"
command1.ExecuteNonQuery()
' Open a second connection to AdventureWorks
Dim connection2 As SqlConnection = New SqlConnection(connectionString)
Using connection2
connection2.Open()
' Initiate a second transaction to read from TestSnapshot
' using Snapshot isolation. This will read the original
' value of 1 since transaction1 has not yet committed.
Dim command2 As SqlCommand = connection2.CreateCommand()
Dim transaction2 As SqlTransaction = _
connection2.BeginTransaction(IsolationLevel.Snapshot)
command2.Transaction = transaction2
command2.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
Using reader2 As SqlDataReader = command2.ExecuteReader()
While reader2.Read()
Console.WriteLine("Expected 1,1 Actual " _
& reader2.GetValue(0).ToString() + "," _
& reader2.GetValue(1).ToString())
End While
End Using
transaction2.Commit()
End Using
' Open a third connection to AdventureWorks and
' initiate a third transaction to read from TestSnapshot
' using the ReadCommitted isolation level. This transaction
' will not be able to view the data because of
' the locks placed on the table in transaction1
' and will time out after 4 seconds.
' You would see the same behavior with the
' RepeatableRead or Serializable isolation levels.
Dim connection3 As SqlConnection = New SqlConnection(connectionString)
Using connection3
connection3.Open()
Dim command3 As SqlCommand = connection3.CreateCommand()
Dim transaction3 As SqlTransaction = _
connection3.BeginTransaction(IsolationLevel.ReadCommitted)
command3.Transaction = transaction3
command3.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
command3.CommandTimeout = 4
Try
Using reader3 As SqlDataReader = command3.ExecuteReader()
While reader3.Read()
Console.WriteLine("You should never hit this.")
End While
End Using
transaction3.Commit()
Catch ex As Exception
Console.WriteLine("Expected timeout expired exception: " _
& ex.Message)
transaction3.Rollback()
End Try
End Using
' Open a fourth connection to AdventureWorks and
' initiate a fourth transaction to read from TestSnapshot
' using the ReadUncommitted isolation level. ReadUncommitted
' will not hit the table lock, and will allow a dirty read
' of the proposed new value 22. If the first transaction
' transaction rolls back, this value will never actually have
' existed in the database.
Dim connection4 As SqlConnection = New SqlConnection(connectionString)
Using connection4
connection4.Open()
Dim command4 As SqlCommand = connection4.CreateCommand()
Dim transaction4 As SqlTransaction = _
connection4.BeginTransaction(IsolationLevel.ReadUncommitted)
command4.Transaction = transaction4
command4.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
Using reader4 As SqlDataReader = command4.ExecuteReader()
While reader4.Read()
Console.WriteLine("Expected 1,22 Actual " _
& reader4.GetValue(0).ToString() _
& "," + reader4.GetValue(1).ToString())
End While
End Using
transaction4.Commit()
' Rollback transaction1
transaction1.Rollback()
End Using
End Using
' CLEANUP
' Drop TestSnapshot table and set
' ALLOW_SNAPSHOT_ISOLATION OFF for AdventureWorks
Dim connection5 As New SqlConnection(connectionString)
Using connection5
connection5.Open()
Dim command5 As SqlCommand = connection5.CreateCommand()
command5.CommandText = "DROP TABLE TestSnapshot"
Dim command6 As SqlCommand = connection5.CreateCommand()
command6.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
Try
command5.ExecuteNonQuery()
command6.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
Voorbeeld
In het volgende voorbeeld ziet u het gedrag van isolatie van momentopnamen wanneer gegevens worden gewijzigd. De code voert de volgende acties uit:
Maakt verbinding met de
AdventureWorksvoorbeelddatabase en maaktSNAPSHOTisolatie mogelijk.Hiermee maakt u een tabel met de naam
TestSnapshotUpdateen voegt u drie rijen met voorbeeldgegevens in.Begint, maar voltooit
sqlTransaction1niet met behulp vanSNAPSHOT-isolatie. Er worden drie rijen met gegevens geselecteerd in de transactie.Hiermee maakt u een tweede
SqlConnectionaanAdventureWorksen maakt u een tweede transactie met behulp van hetREAD COMMITTEDisolatieniveau waarmee een waarde wordt bijgewerkt in een van de rijen die zijn geselecteerd insqlTransaction1.Commits
sqlTransaction2.Keert terug naar
sqlTransaction1en probeert dezelfde rij bij te werken diesqlTransaction1al heeft vastgelegd. Fout 3960 wordt veroorzaakt ensqlTransaction1wordt automatisch teruggedraaid. DeSqlException.NumberenSqlException.Messageworden weergegeven in het consolevenster.De opschoningscode wordt uitgevoerd om momentopname-isolatie in
AdventureWorksuit te schakelen en de tabelTestSnapshotUpdatete verwijderen.
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(connectionString))
{
connection1.Open();
SqlCommand command1 = connection1.CreateCommand();
// Enable Snapshot isolation in AdventureWorks
command1.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
try
{
command1.ExecuteNonQuery();
Console.WriteLine(
"Snapshot Isolation turned on in AdventureWorks.");
}
catch (Exception ex)
{
Console.WriteLine($"ALLOW_SNAPSHOT_ISOLATION ON failed: {ex.Message}");
}
// Create a table
command1.CommandText =
"IF EXISTS "
+ "(SELECT * FROM sys.tables "
+ "WHERE name=N'TestSnapshotUpdate')"
+ " DROP TABLE TestSnapshotUpdate";
command1.ExecuteNonQuery();
command1.CommandText =
"CREATE TABLE TestSnapshotUpdate "
+ "(ID int primary key, CharCol nvarchar(100));";
try
{
command1.ExecuteNonQuery();
Console.WriteLine("TestSnapshotUpdate table created.");
}
catch (Exception ex)
{
Console.WriteLine($"CREATE TABLE failed: {ex.Message}");
}
// Insert some data
command1.CommandText =
"INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');"
+ "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');"
+ "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');";
try
{
command1.ExecuteNonQuery();
Console.WriteLine("Data inserted TestSnapshotUpdate table.");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Begin, but do not complete, a transaction
// using the Snapshot isolation level.
SqlTransaction transaction1 = default!;
try
{
transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot);
command1.CommandText =
"SELECT * FROM TestSnapshotUpdate WHERE ID BETWEEN 1 AND 3";
command1.Transaction = transaction1;
command1.ExecuteNonQuery();
Console.WriteLine("Snapshot transaction1 started.");
// Open a second Connection/Transaction to update data
// using ReadCommitted. This transaction should succeed.
using (SqlConnection connection2 = new(connectionString))
{
connection2.Open();
SqlCommand command2 = connection2.CreateCommand();
command2.CommandText = "UPDATE TestSnapshotUpdate SET CharCol="
+ "N'New value from Connection2' WHERE ID=1";
SqlTransaction transaction2 =
connection2.BeginTransaction(IsolationLevel.ReadCommitted);
command2.Transaction = transaction2;
try
{
command2.ExecuteNonQuery();
transaction2.Commit();
Console.WriteLine(
"transaction2 has modified data and committed.");
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
transaction2.Rollback();
}
finally
{
transaction2.Dispose();
}
}
// Now try to update a row in Connection1/Transaction1.
// This transaction should fail because Transaction2
// succeeded in modifying the data.
command1.CommandText =
"UPDATE TestSnapshotUpdate SET CharCol="
+ "N'New value from Connection1' WHERE ID=1";
command1.Transaction = transaction1;
command1.ExecuteNonQuery();
transaction1.Commit();
Console.WriteLine("You should never see this.");
}
catch (SqlException ex)
{
Console.WriteLine("Expected failure for transaction1:");
Console.WriteLine($" {ex.Number}: {ex.Message}");
}
finally
{
transaction1.Dispose();
}
}
// CLEANUP:
// Turn off Snapshot isolation and delete the table
using (SqlConnection connection3 = new(connectionString))
{
connection3.Open();
SqlCommand command3 = connection3.CreateCommand();
command3.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
try
{
command3.ExecuteNonQuery();
Console.WriteLine(
"CLEANUP: Snapshot isolation turned off in AdventureWorks.");
}
catch (Exception ex)
{
Console.WriteLine($"CLEANUP FAILED: {ex.Message}");
}
command3.CommandText = "DROP TABLE TestSnapshotUpdate";
try
{
command3.ExecuteNonQuery();
Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
}
catch (Exception ex)
{
Console.WriteLine($"CLEANUP FAILED: {ex.Message}");
}
}
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()
Using connection1 As New SqlConnection(connectionString)
' Enable Snapshot isolation in AdventureWorks
connection1.Open()
Dim command1 As SqlCommand = connection1.CreateCommand
command1.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;"
Try
command1.ExecuteNonQuery()
Console.WriteLine( _
"Snapshot Isolation turned on in AdventureWorks.")
Catch ex As Exception
Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION failed: {0}", ex.Message)
End Try
' Create a table
command1.CommandText = _
"IF EXISTS (SELECT * FROM sys.databases " _
& "WHERE name=N'TestSnapshotUpdate') " _
& "DROP TABLE TestSnapshotUpdate"
command1.ExecuteNonQuery()
command1.CommandText = _
"CREATE TABLE TestSnapshotUpdate (ID int primary key, " _
& "CharCol nvarchar(100));"
Try
command1.ExecuteNonQuery()
Console.WriteLine("TestSnapshotUpdate table created.")
Catch ex As Exception
Console.WriteLine("CREATE TABLE failed: {0}", ex.Message)
End Try
' Insert some data
command1.CommandText = _
"INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');" _
& "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');" _
& "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');"
Try
command1.ExecuteNonQuery()
Console.WriteLine("Data inserted TestSnapshotUpdate table.")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
' Begin, but do not complete, a transaction
' using the Snapshot isolation level
Dim transaction1 As SqlTransaction = Nothing
Try
transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot)
command1.CommandText = _
"SELECT * FROM TestSnapshotUpdate WHERE ID " _
& "BETWEEN 1 AND 3"
command1.Transaction = transaction1
command1.ExecuteNonQuery()
Console.WriteLine("Snapshot transaction1 started.")
' Open a second Connection/Transaction to update data
' using ReadCommitted. This transaction should succeed.
Dim connection2 As SqlConnection = New SqlConnection(connectionString)
Using connection2
connection2.Open()
Dim command2 As SqlCommand = connection2.CreateCommand()
command2.CommandText = "UPDATE TestSnapshotUpdate SET " _
& "CharCol=N'New value from Connection2' WHERE ID=1"
Dim transaction2 As SqlTransaction = _
connection2.BeginTransaction(IsolationLevel.ReadCommitted)
command2.Transaction = transaction2
Try
command2.ExecuteNonQuery()
transaction2.Commit()
Console.WriteLine( _
"transaction2 has modified data and committed.")
Catch ex As SqlException
Console.WriteLine(ex.Message)
transaction2.Rollback()
Finally
transaction2.Dispose()
End Try
End Using
' Now try to update a row in Connection1/Transaction1.
' This transaction should fail because Transaction2
' succeeded in modifying the data.
command1.CommandText = _
"UPDATE TestSnapshotUpdate SET CharCol=" _
& "N'New value from Connection1' WHERE ID=1"
command1.Transaction = transaction1
command1.ExecuteNonQuery()
transaction1.Commit()
Console.WriteLine("You should never see this.")
Catch ex As SqlException
Console.WriteLine("Expected failure for transaction1:")
Console.WriteLine(" {0}: {1}", ex.Number, ex.Message)
Finally
transaction1.Dispose()
End Try
End Using
' CLEANUP:
' Turn off Snapshot isolation and delete the table
Dim connection3 As New SqlConnection(connectionString)
Using connection3
connection3.Open()
Dim command3 As SqlCommand = connection3.CreateCommand()
command3.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
Try
command3.ExecuteNonQuery()
Console.WriteLine( _
"Snapshot isolation turned off in AdventureWorks.")
Catch ex As Exception
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
End Try
command3.CommandText = "DROP TABLE TestSnapshotUpdate"
Try
command3.ExecuteNonQuery()
Console.WriteLine("TestSnapshotUpdate table deleted.")
Catch ex As Exception
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
End Try
End Using
Gebruik vergrendelingsaanwijzingen met snapshot-isolatie
In het vorige voorbeeld selecteert de eerste transactie gegevens en een tweede transactie werkt de gegevens bij voordat de eerste transactie kan worden voltooid, waardoor een updateconflict ontstaat wanneer de eerste transactie probeert dezelfde rij bij te werken. U kunt de kans op updateconflicten in langlopende momentopnametransacties verminderen door vergrendelingshints aan het begin van de transactie op te geven. In de volgende SELECT instructie wordt de UPDLOCK hint gebruikt om de geselecteerde rijen te vergrendelen:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Als u de UPDLOCK vergrendelingshint gebruikt, worden de rijen geblokkeerd die proberen de rijen bij te werken voordat de eerste transactie is voltooid. Dit garandeert dat de geselecteerde rijen geen conflicten hebben wanneer ze later in de transactie worden bijgewerkt. Zie hints (Transact-SQL)voor meer informatie.
Als uw toepassing veel conflicten heeft, is isolatie van momentopnamen mogelijk niet de beste keuze. Hints moeten alleen worden gebruikt wanneer ze echt nodig zijn. Uw toepassing moet niet zo worden ontworpen dat deze voortdurend afhankelijk is van vergrendelingshints voor de werking ervan.