Isolering av ögonblicksbild i SQL Server
Isolering av ögonblicksbilder förbättrar samtidigheten för OLTP-program.
Förstå ögonblicksbildisolering och radversioner
När ögonblicksbildisolering har aktiverats måste uppdaterade radversioner för varje transaktion underhållas. Före SQL Server 2019 lagrades dessa versioner i tempdb. SQL Server 2019 introducerar en ny funktion, Accelerated Database Recovery (ADR) som kräver en egen uppsättning radversioner. Så från och med SQL Server 2019, om ADR inte är aktiverat, sparas radversioner i tempdb som alltid. Om ADR är aktiverat sparas alla radversioner, båda relaterade till ögonblicksbildisolering och ADR, i ADR:s Persistent Version Store (PVS), som finns i användardatabasen i en filgrupp som användaren anger. Ett unikt transaktionssekvensnummer identifierar varje transaktion och dessa unika tal registreras för varje radversion. Transaktionen fungerar med de senaste radversionerna med ett sekvensnummer före transaktionens sekvensnummer. Nyare radversioner som skapats när transaktionen har påbörjats ignoreras av transaktionen.
Termen "ögonblicksbild" återspeglar det faktum att alla frågor i transaktionen ser samma version, eller ögonblicksbild, av databasen, baserat på databasens tillstånd vid den tidpunkt då transaktionen börjar. Inga lås hämtas på underliggande datarader eller datasidor i en ögonblicksbildtransaktion, vilket gör att andra transaktioner kan köras utan att blockeras av en tidigare oavslutad transaktion. Transaktioner som ändrar data blockerar inte transaktioner som läser data och transaktioner som läser data blockerar inte transaktioner som skriver data, som de normalt skulle göra under standardnivån READ COMMITTED-isolering i SQL Server. Det här icke-blockerande beteendet minskar också sannolikheten för dödlägen för komplexa transaktioner avsevärt.
Ögonblicksbildisolering använder en optimistisk samtidighetsmodell. Om en ögonblicksbildtransaktion försöker genomföra ändringar av data som har ändrats sedan transaktionen började, återställs transaktionen och ett fel utlöses. Du kan undvika detta genom att använda UPDLOCK-tips för SELECT-instruktioner som kommer åt data som ska ändras. Mer information finns i Tips (Transact-SQL).
Isolering av ögonblicksbilder måste aktiveras genom att alternativet ALLOW_SNAPSHOT_ISOLATION PÅ-databas anges innan det används i transaktioner. Detta aktiverar mekanismen för att lagra radversioner i den tillfälliga databasen (tempdb). Du måste aktivera ögonblicksbildisolering i varje databas som använder den med Transact-SQL ALTER DATABASE-instruktionen. I det här avseendet skiljer sig ögonblicksbildisolering från de traditionella isoleringsnivåerna för READ COMMITTED, REPEATABLE READ, SERIALIZABLE och READ UNCOMMITTED, som inte kräver någon konfiguration. Följande instruktioner aktiverar ögonblicksbildisolering och ersätter standardbeteendet READ COMMITTED med SNAPSHOT:
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
Om du ställer in alternativet READ_COMMITTED_SNAPSHOT PÅ får du åtkomst till versionsrader under standardnivån READ COMMITTED-isolering. Om alternativet READ_COMMITTED_SNAPSHOT är inställt på AV måste du uttryckligen ange isoleringsnivån för ögonblicksbilder för varje session för att få åtkomst till versionsrader.
Hantera samtidighet med isoleringsnivåer
Isoleringsnivån under vilken en Transact-SQL-instruktion körs avgör dess beteende för låsning och radversion. En isoleringsnivå har ett anslutningsomfattande omfång, och när den har angetts för en anslutning med instruktionen SET TRANSACTION ISOLATION LEVEL gäller den tills anslutningen har stängts eller en annan isoleringsnivå har angetts. När en anslutning stängs och returneras till poolen behålls isoleringsnivån från den senaste instruktionen SET TRANSACTION ISOLATION LEVEL . Efterföljande anslutningar som återanvänder en poolanslutning använder isoleringsnivån som var i kraft vid den tidpunkt då anslutningen poolades.
Enskilda frågor som utfärdas inom en anslutning kan innehålla låstips som ändrar isoleringen för en enda instruktion eller transaktion, men som inte påverkar anslutningens isoleringsnivå. Isoleringsnivåer eller låstips som anges i lagrade procedurer eller funktioner ändrar inte isoleringsnivån för anslutningen som anropar dem och gäller endast under den lagrade proceduren eller funktionsanropet.
Fyra isoleringsnivåer som definierats i SQL-92-standarden stöds i tidiga versioner av SQL Server:
READ UNCOMMITTED är den minst restriktiva isoleringsnivån eftersom den ignorerar lås som placeras av andra transaktioner. Transaktioner som körs under READ UNCOMMITTED kan läsa ändrade datavärden som ännu inte har utförts av andra transaktioner. dessa kallas "smutsiga" läsningar.
READ COMMITTED är standardisoleringsnivån för SQL Server. Det förhindrar felaktiga läsningar genom att ange att instruktioner inte kan läsa datavärden som har ändrats men som ännu inte har utförts av andra transaktioner. Andra transaktioner kan fortfarande ändra, infoga eller ta bort data mellan körningar av enskilda instruktioner i den aktuella transaktionen, vilket resulterar i icke-repeterbara läsningar eller "fantomdata".
REPEATABLE READ är en mer restriktiv isoleringsnivå än READ COMMITTED. Den omfattar READ COMMITTED och anger dessutom att inga andra transaktioner kan ändra eller ta bort data som har lästs av den aktuella transaktionen förrän den aktuella transaktionen checkas in. Samtidighet är lägre än för READ COMMITTED eftersom delade lås på läsdata lagras under transaktionens varaktighet i stället för att släppas i slutet av varje instruktion.
SERIALIZABLE är den mest restriktiva isoleringsnivån eftersom den låser hela intervall med nycklar och håller låsen tills transaktionen är klar. Den omfattar REPEATABLE READ och lägger till begränsningen att andra transaktioner inte kan infoga nya rader i intervall som har lästs av transaktionen förrän transaktionen har slutförts.
Mer information finns i versionsguiden för transaktionslåsning och rad.
Tillägg på isoleringsnivå för ögonblicksbilder
SQL Server introducerade tillägg till SQL-92-isoleringsnivåer med införandet av isoleringsnivån ögonblicksbild och en ytterligare implementering av READ COMMITTED. Den READ_COMMITTED_SNAPSHOT isoleringsnivån kan transparent ersätta READ COMMITTED för alla transaktioner.
Ögonblicksbildisolering anger att data som läss i en transaktion aldrig återspeglar ändringar som gjorts av andra samtidiga transaktioner. Transaktionen använder de dataradsversioner som finns när transaktionen börjar. Inga lås placeras på data när de läss, så SNAPSHOT-transaktioner blockerar inte andra transaktioner från att skriva data. Transaktioner som skriver data blockerar inte transaktioner med ögonblicksbilder från att läsa data. Du måste aktivera ögonblicksbildisolering genom att ange alternativet ALLOW_SNAPSHOT_ISOLATION databas för att kunna använda den.
Alternativet READ_COMMITTED_SNAPSHOT databas avgör beteendet för standardnivån READ COMMITTED-isolering när ögonblicksbildisolering är aktiverat i en databas. Om du inte uttryckligen anger READ_COMMITTED_SNAPSHOT PÅ tillämpas READ COMMITTED på alla implicita transaktioner. Detta ger samma beteende som inställningen READ_COMMITTED_SNAPSHOT OFF (standard). När READ_COMMITTED_SNAPSHOT OFF är i kraft använder databasmotorn delade lås för att framtvinga standardisoleringsnivån. Om du anger alternativet READ_COMMITTED_SNAPSHOT databas till PÅ använder databasmotorn radversions- och ögonblicksbildisolering som standard, i stället för att använda lås för att skydda data.
Så här fungerar ögonblicksbildisolering och radversioner
När isoleringsnivån ögonblicksbild är aktiverad, varje gång en rad uppdateras, lagrar SQL Server Database Engine en kopia av den ursprungliga raden i tempdb och lägger till ett transaktionssekvensnummer på raden. Följande är sekvensen med händelser som inträffar:
En ny transaktion initieras och tilldelas ett transaktionssekvensnummer.
Databasmotorn läser en rad i transaktionen och hämtar radversionen från tempdb vars sekvensnummer är närmast och lägre än transaktionssekvensnumret.
Databasmotorn kontrollerar om transaktionssekvensnumret inte finns med i listan över transaktionssekvensnummer för de ogenomförda transaktioner som är aktiva när ögonblicksbildstransaktionen startade.
Transaktionen läser den version av raden från tempdb som var aktuell i början av transaktionen. Nya rader infogas inte efter att transaktionen startades eftersom dessa sekvensnummervärden kommer att vara högre än värdet för transaktionssekvensnumret.
Den aktuella transaktionen ser rader som togs bort efter att transaktionen började, eftersom det kommer att finnas en radversion i tempdb med ett lägre sekvensnummervärde.
Nettoeffekten av ögonblicksbildisolering är att transaktionen ser alla data som de fanns i början av transaktionen, utan att respektera eller placera några lås på de underliggande tabellerna. Detta kan resultera i prestandaförbättringar i situationer där det finns konkurrens.
En ögonblicksbildstransaktion använder alltid optimistisk samtidighetskontroll och undanhåller alla lås som hindrar andra transaktioner från att uppdatera rader. Om en ögonblicksbildtransaktion försöker checka in en uppdatering till en rad som ändrades efter att transaktionen påbörjades återställs transaktionen och ett fel uppstår.
Arbeta med ögonblicksbildisolering i ADO.NET
Ögonblicksbildisolering stöds i ADO.NET av SqlTransaction klassen. Om en databas har aktiverats för ögonblicksbildisolering men inte har konfigurerats för READ_COMMITTED_SNAPSHOT PÅ måste du initiera en SqlTransaction med uppräkningsvärdet IsolationLevel.Snapshot när du anropar BeginTransaction metoden. Det här kodfragmentet förutsätter att anslutningen är ett öppet SqlConnection objekt.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Exempel
I följande exempel visas hur de olika isoleringsnivåerna beter sig genom att försöka komma åt låsta data och att de inte är avsedda att användas i produktionskoden.
Koden ansluter till AdventureWorks-exempeldatabasen i SQL Server och skapar en tabell med namnet TestSnapshot och infogar en rad med data. Koden använder ALTER DATABASE Transact-SQL-instruktionen för att aktivera ögonblicksbildisolering för databasen, men den anger inte alternativet READ_COMMITTED_SNAPSHOT, vilket gör att standardbeteendet read committed isolation-level tillämpas. Koden utför sedan följande åtgärder:
Den börjar, men slutförs inte, sqlTransaction1, som använder isoleringsnivån SERIALIZABLE för att starta en uppdateringstransaktion. Detta innebär att tabellen låses.
Den öppnar en andra anslutning och initierar en andra transaktion med isoleringsnivån ÖGONBLICKSBILD för att läsa data i tabellen TestSnapshot . Eftersom ögonblicksbildisolering är aktiverat kan den här transaktionen läsa de data som fanns innan sqlTransaction1 startades.
Den öppnar en tredje anslutning och initierar en transaktion med hjälp av READ COMMITTED-isoleringsnivån för att försöka läsa data i tabellen. I det här fallet kan koden inte läsa data eftersom den inte kan läsa förbi låsen som placerats i tabellen i den första transaktionen och överskrider tidsgränsen. Samma resultat skulle inträffa om isoleringsnivåerna REPEATABLE READ och SERIALIZABLE användes eftersom dessa isoleringsnivåer inte heller kan läsas förbi låsen som placerades i den första transaktionen.
Den öppnar en fjärde anslutning och initierar en transaktion med hjälp av READ UNCOMMITTED-isoleringsnivån, som utför en felaktig läsning av det obekräftade värdet i sqlTransaction1. Det här värdet kanske aldrig finns i databasen om den första transaktionen inte har checkats in.
Den återställer den första transaktionen och rensar genom att ta bort tabellen TestSnapshot och stänga av ögonblicksbildisolering för AdventureWorks-databasen .
Kommentar
I följande exempel används samma anslutningssträng med inaktiverad anslutningspool. Om en anslutning är poolad återställs inte isoleringsnivån på servern när isoleringsnivån återställs. Därför börjar efterföljande anslutningar som använder samma inre poolanslutning med sina isoleringsnivåer inställda på den poolkopplade anslutningen. Ett alternativ till att stänga av anslutningspooler är att uttryckligen ange isoleringsnivån för varje anslutning.
// 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
Exempel
I följande exempel visas hur ögonblicksbildisolering fungerar när data ändras. Koden utför följande åtgärder:
Anslut till AdventureWorks exempeldatabas och aktiverar ögonblicksbildisolering.
Skapar en tabell med namnet TestSnapshotUpdate och infogar tre rader med exempeldata.
Börjar, men slutförs inte, sqlTransaction1 med hjälp av ögonblicksbildisolering. Tre rader med data väljs i transaktionen.
Skapar en andra Sql Anslut ion till AdventureWorks och skapar en andra transaktion med hjälp av READ COMMITTED-isoleringsnivån som uppdaterar ett värde i en av de rader som valts i sqlTransaction1.
Genomför sqlTransaction2.
Återgår till sqlTransaction1 och försöker uppdatera samma rad som sqlTransaction1 redan har checkats in. Fel 3960 utlöses och sqlTransaction1 återställs automatiskt. SqlException.Number och SqlException.Message visas i konsolfönstret.
Kör rensningskod för att inaktivera ögonblicksbildisolering i AdventureWorks och ta bort tabellen TestSnapshotUpdate .
// 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
Använda låstips med ögonblicksbildisolering
I föregående exempel väljer den första transaktionen data och en andra transaktion uppdaterar data innan den första transaktionen kan slutföras, vilket orsakar en uppdateringskonflikt när den första transaktionen försöker uppdatera samma rad. Du kan minska risken för uppdateringskonflikter i långvariga ögonblicksbildtransaktioner genom att ange låstips i början av transaktionen. Följande SELECT-instruktion använder UPDLOCK-tipset för att låsa de markerade raderna:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Med hjälp av UPDLOCK-låstipset blockeras alla rader som försöker uppdatera raderna innan den första transaktionen slutförs. Detta garanterar att de valda raderna inte har några konflikter när de uppdateras senare i transaktionen. Mer information finns i Tips (Transact-SQL).
Om ditt program har många konflikter är ögonblicksbildisolering kanske inte det bästa valet. Tips bör endast användas när det verkligen behövs. Programmet bör inte utformas så att det ständigt förlitar sig på låstips för dess drift.