Isolatie van momentopnamen in SQL Server
Isolatie van momentopnamen verbetert gelijktijdigheid voor OLTP-toepassingen.
Informatie over isolatie van momentopnamen en rijversiebeheer
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 in tempdb bewaard zoals altijd. 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 werkt met de meest recente rijversies met een volgnummer vóór 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, omdat ze normaal gesproken onder het standaard isolementatieniveau READ COMMITTED in SQL Server zouden staan. 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 toegang hebben tot gegevens die moeten worden gewijzigd. Zie Hints (Transact-SQL) voor meer informatie.
Isolatie van momentopnamen moet worden ingeschakeld door de optie ALLOW_SNAPSHOT_ISOLATION ON-database 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 Transact-SQL ALTER DATABASE-instructie. In dit opzicht verschilt de isolatie van momentopnamen van de traditionele isolatieniveaus van READ COMMIT, REPEATABLE READ, SERIALIZABLE en READ UNCOMMITTED, waarvoor geen configuratie is vereist. Met de volgende instructies wordt isolatie van momentopnamen geactiveerd en wordt het standaardgedrag READ COMMIT vervangen door SNAPSHOT:
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
Als u de optie READ_COMMITTED_SNAPSHOT AAN instelt, hebt u toegang tot geversiede rijen onder het standaardniveau READ COMMIT-isolatie. Als de optie READ_COMMITTED_SNAPSHOT is ingesteld op UIT, moet u het isolatieniveau voor momentopnamen voor elke sessie expliciet instellen om toegang te krijgen tot versierijen.
Gelijktijdigheid beheren met isolatieniveaus
Het isolatieniveau waaronder een Transact-SQL-instructie wordt uitgevoerd, bepaalt het vergrendelings- en rijversiebeheergedrag. Een isolatieniveau heeft een verbindingsbreed bereik en zodra deze is ingesteld voor een verbinding met de instructie SET TRANSACTION ISOLATION LEVEL, blijft dit van kracht totdat de verbinding is gesloten of een ander isolatieniveau is ingesteld. Wanneer een verbinding wordt gesloten en aan de groep wordt geretourneerd, blijft het isolatieniveau van de laatste instructie TRANSACTION ISOLATION LEVEL 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 UNCOMMITTED is het minst beperkende isolatieniveau omdat hiermee vergrendelingen worden genegeerd die door andere transacties worden geplaatst. Transacties die worden uitgevoerd onder READ UNCOMMITTED kunnen gewijzigde gegevenswaarden lezen die nog niet zijn vastgelegd door andere transacties; deze worden 'vuile' leesbewerkingen genoemd.
READ COMMIT is 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.
HERHAALBARE LEESBEWERKING is een meer beperkend isolatieniveau dan READ COMMITTED. Het omvat READ COMMITTED en geeft bovendien aan dat er geen andere transacties gegevens kunnen wijzigen of verwijderen die door de huidige transactie zijn gelezen totdat de huidige transactie doorvoert. Gelijktijdigheid is lager dan voor READ COMMIT omdat gedeelde vergrendelingen voor leesgegevens worden bewaard voor de duur van de transactie in plaats van aan het einde van elke instructie te worden vrijgegeven.
SERIALIZABLE is het meest beperkende isolatieniveau, omdat het hele reeks sleutels vergrendelt en de vergrendelingen vasthoudt totdat de transactie is voltooid. Het omvat HERHAALBARE LEESBEWERKING en 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.
Raadpleeg de handleiding voor transactievergrendeling en rijversiebeheer voor meer informatie.
Extensies op isolatieniveau voor momentopnamen
SQL Server heeft uitbreidingen geïntroduceerd voor de SQL-92-isolatieniveaus met de introductie van het isolatieniveau SNAPSHOT en een extra implementatie van READ COMMITTED. Het READ_COMMITTED_SNAPSHOT isolatieniveau kan LEES VASTGELEGD voor alle transacties transparant vervangen.
MET MOMENTOPNAME-isolatie wordt aangegeven dat gegevens die in een transactie worden gelezen, nooit wijzigingen weerspiegelen die zijn aangebracht door andere gelijktijdige transacties. 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, dus MOMENTOPNAME-transacties blokkeren niet dat andere transacties gegevens schrijven. Transacties die gegevens schrijven, blokkeren niet dat momentopnametransacties gegevens lezen. U moet isolatie van momentopnamen inschakelen door de optie ALLOW_SNAPSHOT_ISOLATION database in te stellen om deze te kunnen gebruiken.
De READ_COMMITTED_SNAPSHOT-databaseoptie bepaalt het gedrag van het standaardniveau READ COMMITTED-isolatie wanneer momentopname-isolatie is ingeschakeld in een database. Als u niet expliciet READ_COMMITTED_SNAPSHOT AAN opgeeft, wordt READ COMMIT toegepast op alle impliciete transacties. Dit produceert hetzelfde gedrag als het instellen van READ_COMMITTED_SNAPSHOT UIT (de standaardinstelling). Wanneer READ_COMMITTED_SNAPSHOT UIT van kracht is, gebruikt de database-engine gedeelde vergrendelingen om het standaardisolatieniveau af te dwingen. Als u de optie READ_COMMITTED_SNAPSHOT database instelt op AAN, gebruikt de database-engine rijversiebeheer en isolatie van momentopnamen als standaard, in plaats van vergrendelingen te gebruiken om de gegevens te beveiligen.
Hoe isolatie van momentopnamen en rijversiebeheer werken
Wanneer het isolatieniveau SNAPSHOT is ingeschakeld, slaat de SQL Server Database Engine telkens wanneer een rij wordt bijgewerkt een kopie van de oorspronkelijke rij op in tempdb en voegt een transactiereeksnummer toe aan de rij. 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 uit tempdb waarvan het 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 uit tempdb die vanaf het begin van de transactie actueel was. 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 begonnen, omdat er een rijversie in tempdb is 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 optimistisch gelijktijdigheidsbeheer, waarbij eventuele vergrendelingen worden bijgehouden 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
Isolatie van momentopnamen wordt ondersteund in ADO.NET door de SqlTransaction klasse. Als een database is ingeschakeld voor isolatie van momentopnamen, maar niet is geconfigureerd voor READ_COMMITTED_SNAPSHOT AAN, moet u een SqlTransaction opsommingswaarde IsolationLevel.Snapshot initiëren bij het aanroepen van de BeginTransaction methode. In dit codefragment wordt ervan uitgegaan dat de verbinding een geopend SqlConnection object is.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Opmerking
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 transact-SQL-instructie ALTER DATABASE om isolatie van momentopnamen voor de database in te schakelen, maar stelt de optie READ_COMMITTED_SNAPSHOT niet in, waardoor het standaardgedrag op isolatieniveau READ COMMITTED van kracht blijft. De code voert vervolgens de volgende acties uit:
Het begint, maar is niet voltooid, sqlTransaction1, dat gebruikmaakt van het isolatieniveau SERIALIZABLE 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 isolatieniveau SNAPSHOT om de gegevens in de TestSnapshot-tabel te lezen. Omdat isolatie van momentopnamen is ingeschakeld, kan deze transactie de gegevens lezen die bestonden voordat sqlTransaction1 werd gestart.
Er wordt een derde verbinding geopend en er wordt een transactie gestart met behulp van het isolatieniveau READ COMMITTED om de gegevens in de tabel te lezen. In dit geval kan de code de gegevens niet lezen omdat deze niet kan worden gelezen na de vergrendelingen die in de tabel in de eerste transactie zijn geplaatst en er een time-out optreedt. Hetzelfde resultaat zou optreden als de HERHAALBARE ISOLATIE- en SERIALIZABLE-isolatieniveaus werden gebruikt, omdat deze isolatieniveaus ook niet kunnen worden gelezen na de vergrendelingen die in de eerste transactie zijn geplaatst.
Er wordt een vierde verbinding geopend en er wordt een transactie gestart met behulp van het isolatieniveau READ UNCOMMITTED, dat een vuile leesbewerking uitvoert van de niet-doorgevoerde waarde in sqlTransaction1. Deze waarde bestaat mogelijk nooit in de database als de eerste transactie niet is doorgevoerd.
De eerste transactie wordt teruggedraaid en opgeschoond door de TestSnapshot-tabel te verwijderen en de isolatie van momentopnamen voor de AdventureWorks-database 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";
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
{
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";
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"
Dim reader2 As SqlDataReader = _
command2.ExecuteReader()
While reader2.Read()
Console.WriteLine("Expected 1,1 Actual " _
& reader2.GetValue(0).ToString() + "," _
& reader2.GetValue(1).ToString())
End While
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
Dim reader3 As SqlDataReader = command3.ExecuteReader()
While reader3.Read()
Console.WriteLine("You should never hit this.")
End While
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"
Dim reader4 As SqlDataReader = _
command4.ExecuteReader()
While reader4.Read()
Console.WriteLine("Expected 1,22 Actual " _
& reader4.GetValue(0).ToString() _
& "," + reader4.GetValue(1).ToString())
End While
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
Opmerking
In het volgende voorbeeld ziet u het gedrag van isolatie van momentopnamen wanneer gegevens worden gewijzigd. De code voert de volgende acties uit:
Verbinding maken s naar de AdventureWorks-voorbeelddatabase en maakt isolatie van MOMENTOPNAMEn mogelijk.
Hiermee maakt u een tabel met de naam TestSnapshotUpdate en voegt u drie rijen met voorbeeldgegevens in.
Begint, maar is niet voltooid, sqlTransaction1 met behulp van SNAPSHOT-isolatie. Er worden drie rijen met gegevens geselecteerd in de transactie.
Hiermee maakt u een tweede Sql Verbinding maken ion naar AdventureWorks en maakt u een tweede transactie met behulp van het isolatieniveau READ COMMITTED waarmee een waarde wordt bijgewerkt in een van de rijen die zijn geselecteerd in sqlTransaction1.
Hiermee wordt sqlTransaction2 doorgevoerd.
Keert terug naar sqlTransaction1 en probeert dezelfde rij bij te werken die sqlTransaction1 al heeft doorgevoerd. Fout 3960 wordt gegenereerd en sqlTransaction1 wordt automatisch teruggedraaid. SqlException.Number en SqlException.Message worden weergegeven in het consolevenster.
Hiermee wordt opschooncode uitgevoerd om isolatie van momentopnamen in AdventureWorks uit te schakelen en de tabel TestSnapshotUpdate te 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: {0}", 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: {0}", 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(" {0}: {1}", 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: {0}", ex.Message);
}
command3.CommandText = "DROP TABLE TestSnapshotUpdate";
try
{
command3.ExecuteNonQuery();
Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
}
catch (Exception ex)
{
Console.WriteLine("CLEANUP FAILED: {0}", 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
Hints vergrendelen gebruiken met isolatie van momentopnamen
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 HINT VOOR UPDLOCK-vergrendeling gebruikt, worden alle 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.