Izolace snímků na SQL Serveru
Izolace snímků vylepšuje souběžnost pro aplikace OLTP.
Principy izolace snímků a správy verzí řádků
Jakmile je povolená izolace snímků, musí být zachovány aktualizované verze řádků pro každou transakci. Před SQL Serverem 2019 byly tyto verze uloženy v databázi tempdb. SQL Server 2019 zavádí novou funkci Zrychlené obnovení databáze (ADR), která vyžaduje vlastní sadu verzí řádků. Takže od SQL Serveru 2019 se verze řádků uchovávají v databázi tempdb stejně jako vždy, pokud není povolená služba ADR. Pokud je povolené ADR, všechny verze řádků, které souvisejí s izolací snímků a ADR, se uchovávají v trvalém úložišti verzí ADR (PVS), který se nachází v uživatelské databázi ve skupině souborů, kterou určuje uživatel. Jedinečné pořadové číslo transakce identifikuje každou transakci a tato jedinečná čísla jsou zaznamenána pro každou verzi řádku. Transakce funguje s nejnovějšími verzemi řádků, které mají pořadové číslo před pořadovým číslem transakce. Novější verze řádků vytvořené po zahájení transakce jsou ignorovány transakcí.
Termín "snímek" odráží skutečnost, že všechny dotazy v transakci vidí stejnou verzi nebo snímek databáze na základě stavu databáze v okamžiku, kdy transakce začíná. Na podkladových datových řádcích nebo datových stránkách v transakci snímku nejsou získány žádné zámky, které umožňují provádění jiných transakcí bez blokování předchozí nedokončené transakce. Transakce, které upravují data, neblokují transakce, které čtou data, a transakce, které čtou data neblokují transakce, které zapisují data, protože obvykle by byly pod výchozí úrovní izolace READ COMMITTED v SQL Serveru. Toto neblokující chování také výrazně snižuje pravděpodobnost zablokování u složitých transakcí.
Izolace snímků používá model optimistické souběžnosti. Pokud se transakce snímku pokusí potvrdit změny dat, která se od zahájení transakce změnila, transakce se vrátí zpět a vyvolá se chyba. Můžete tomu zabránit použitím tipů UPDLOCK pro příkazy SELECT, které přistupují k datům, které se mají upravit. Další informace najdete v tématu Rady (Transact-SQL).
Izolace snímků musí být povolena nastavením možnosti ALLOW_SNAPSHOT_ISOLATION ON databáze, než se použije v transakcích. Tím se aktivuje mechanismus pro ukládání verzí řádků do dočasné databáze (tempdb). Izolaci snímků musíte povolit v každé databázi, která ji používá s příkazem Transact-SQL ALTER DATABASE. V tomto ohledu se izolace snímků liší od tradičních úrovní izolace READ COMMITTED, REPEATABLE READ, SERIALIZABLE a READ UNCOMMITTED, které nevyžadují žádnou konfiguraci. Následující příkazy aktivují izolaci snímků a nahrazují výchozí chování READ COMMITTED snímkem:
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
Nastavení možnosti READ_COMMITTED_SNAPSHOT ZAPNUTO umožňuje přístup k řádkům s verzí pod výchozí úrovní izolace READ COMMITTED. Pokud je možnost READ_COMMITTED_SNAPSHOT nastavená na VYPNUTO, je nutné explicitně nastavit úroveň izolace snímku pro každou relaci, aby bylo možné získat přístup k řádkům s verzí.
Správa souběžnosti s úrovněmi izolace
Úroveň izolace, pod kterou se provádí příkaz Transact-SQL, určuje chování při zamykání a správu verzí řádků. Úroveň izolace má rozsah pro celé připojení a po nastavení pro připojení s příkazem SET TRANSACTION ISOLATION LEVEL zůstane v platnosti, dokud se připojení neskončí nebo nenastaví jinou úroveň izolace. Při zavření a vrácení připojení do fondu se zachová úroveň izolace z posledního příkazu SET TRANSACTION ISOLATION LEVEL. Další připojení, která znovu používají sdružování připojení, používají úroveň izolace, která se projevila v době, kdy je připojení ve fondu.
Jednotlivé dotazy vydané v rámci připojení mohou obsahovat pokyny k uzamčení, které upravují izolaci pro jeden příkaz nebo transakci, ale nemají vliv na úroveň izolace připojení. Úrovně izolace nebo nápovědy k uzamčení nastavené v uložených procedurách nebo funkcích nemění úroveň izolace připojení, které je volá, a jsou účinné pouze po dobu trvání uložené procedury nebo volání funkce.
V dřívějších verzích SQL Serveru byly podporovány čtyři úrovně izolace definované ve standardu SQL-92:
FUNKCE READ UNCOMMITTED je nejnižší omezující úroveň izolace, protože ignoruje zámky umístěné jinými transakcemi. Transakce spuštěné v rámci READ UNCOMMITTED mohou číst upravené datové hodnoty, které dosud nebyly potvrzeny jinými transakcemi; označují se jako "špinavé" čtení.
READ COMMITTED je výchozí úroveň izolace pro SQL Server. Zabrání nezačitelným čtením zadáním toho, že příkazy nemohou číst hodnoty dat, které byly změněny, ale dosud potvrzeny jinými transakcemi. Jiné transakce mohou stále upravovat, vkládat nebo odstraňovat data mezi spuštěními jednotlivých příkazů v rámci aktuální transakce, což vede k neopakovatelným čtením nebo "fantomovým" datům.
REPEATABLE READ je více omezující úroveň izolace než READ COMMITTED. Zahrnuje READ COMMIT a dále určuje, že žádné jiné transakce nemohou upravovat nebo odstraňovat data, která byla přečtena aktuální transakcí, dokud aktuální transakce potvrzení. Souběžnost je nižší než u funkce READ COMMITTED, protože sdílené zámky na datech čtení se uchovávají po dobu trvání transakce místo uvolnění na konci každého příkazu.
SERIALIZABLE je nejvíce omezující úroveň izolace, protože uzamkne celé rozsahy klíčů a uchovává zámky, dokud transakce nebude dokončena. Zahrnuje REPEATABLE READ a přidává omezení, že ostatní transakce nemohou vložit nové řádky do oblastí, které byly načteny transakcí, dokud transakce nebude dokončena.
Další informace najdete v průvodci uzamčením transakcí a verzí řádků.
Rozšíření na úrovni izolace snímků
SQL Server zavedl rozšíření úrovní izolace SQL-92 se zavedením úrovně izolace SNAPSHOT a další implementací READ COMMITTED. Úroveň izolace READ_COMMITTED_SNAPSHOT může transparentně nahradit READ COMMITTED pro všechny transakce.
Izolace SNAPSHOT určuje, že čtení dat v rámci transakce nikdy neodráží změny provedené jinými souběžnými transakcemi. Transakce používá verze datového řádku, které existují při zahájení transakce. Při čtení nejsou na datech umístěné žádné zámky, takže transakce SNAPSHOT nezablokují zápis dat jinými transakcemi. Transakce, které zapisují data, neblokují transakce snímků při čtení dat. Izolaci snímků musíte povolit nastavením možnosti ALLOW_SNAPSHOT_ISOLATION databáze, aby ji bylo možné použít.
Možnost READ_COMMITTED_SNAPSHOT databáze určuje chování výchozí úrovně izolace READ COMMITTED při povolení izolace snímku v databázi. Pokud explicitně nezadáte READ_COMMITTED_SNAPSHOT ZAPNUTO, funkce READ COMMITTED se použije pro všechny implicitní transakce. Výsledkem je stejné chování jako nastavení READ_COMMITTED_SNAPSHOT VYPNUTO (výchozí nastavení). Pokud je READ_COMMITTED_SNAPSHOT VYPNUTO, databázový stroj používá sdílené zámky k vynucení výchozí úrovně izolace. Pokud nastavíte možnost READ_COMMITTED_SNAPSHOT databáze na zapnuto, použije databázový stroj jako výchozí nastavení správu verzí řádků a izolaci snímků, místo aby k ochraně dat používal zámky.
Jak funguje izolace snímků a správa verzí řádků
Pokud je povolena úroveň izolace SNAPSHOT, při každé aktualizaci řádku uloží databázový stroj SQL Serveru kopii původního řádku v databázi tempdb a přidá do řádku pořadové číslo transakce. Následuje posloupnost událostí, ke kterým dochází:
Zahájí se nová transakce a přiřadí se jí pořadové číslo transakce.
Databázový stroj načte řádek v rámci transakce a načte verzi řádku z databáze tempdb , jejíž pořadové číslo je nejblíže a nižší než pořadové číslo transakce.
Databázový stroj zkontroluje, zda pořadové číslo transakce není v seznamu čísel pořadí transakcí nepotvrzených transakcí aktivních při spuštění transakce snímku.
Transakce čte verzi řádku z databáze tempdb , která byla aktuální od začátku transakce. Nezobrazí se nové řádky vložené po zahájení transakce, protože tyto pořadové číselné hodnoty budou vyšší než hodnota pořadového čísla transakce.
Aktuální transakce uvidí řádky, které byly odstraněny po zahájení transakce, protože v databázi tempdb bude verze řádku s nižší hodnotou pořadového čísla.
Čistým účinkem izolace snímku je, že transakce vidí všechna data, která existovala na začátku transakce, aniž by respektovaly nebo umístily jakékoli zámky na podkladové tabulky. To může vést k vylepšení výkonu v situacích, kdy dochází k kolizím.
Transakce snímku vždy používá optimistickou souběžnost řízení, přičemž zadržuje všechny zámky, které by zabránily jiným transakcím v aktualizaci řádků. Pokud se transakce snímku pokusí potvrdit aktualizaci na řádek, který byl změněn po zahájení transakce, transakce se vrátí zpět a dojde k chybě.
Práce s izolací snímků v ADO.NET
Třída podporuje izolaci snímků v ADO.NET SqlTransaction . Pokud byla databáze povolena pro izolaci snímků, ale není nakonfigurována pro READ_COMMITTED_SNAPSHOT ON, musíte při volání metody zahájit SqlTransaction použití hodnoty IsolationLevel.Snapshot výčtu BeginTransaction . Tento fragment kódu předpokládá, že připojení je otevřený SqlConnection objekt.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Příklad
Následující příklad ukazuje, jak se různé úrovně izolace chovají pokusem o přístup k uzamčeným datům a nejsou určeny k použití v produkčním kódu.
Kód se připojí k ukázkové databázi AdventureWorks na SQL Serveru a vytvoří tabulku s názvem TestSnapshot a vloží jeden řádek dat. Kód používá příkaz ALTER DATABASE Transact-SQL k zapnutí izolace snímku pro databázi, ale nenastaví možnost READ_COMMITTED_SNAPSHOT a ponechá výchozí chování na úrovni izolace READ COMMITTED. Kód pak provede následující akce:
Začíná, ale nedokončí, sqlTransaction1, který používá SERIALIZABLE úroveň izolace zahájit aktualizační transakce. To má vliv na uzamčení tabulky.
Otevře druhé připojení a zahájí druhou transakci pomocí úrovně izolace SNAPSHOT ke čtení dat v tabulce TestSnapshot . Vzhledem k tomu, že je povolena izolace snímků, může tato transakce číst data, která existovaly před spuštěním sqlTransaction1.
Otevře třetí připojení a zahájí transakci pomocí úrovně izolace READ COMMITTED k pokusu o čtení dat v tabulce. V tomto případě kód nemůže číst data, protože nemůže číst zámky umístěné v tabulce v první transakci a vyprší časový limit. Stejný výsledek by nastal, pokud byly použity úrovně izolace REPEATABLE read a SERIALIZABLE, protože tyto úrovně izolace také nemohou číst za zámky umístěné v první transakci.
Otevře čtvrté připojení a zahájí transakci pomocí úrovně izolace READ UNCOMMITTED, která provádí špinavé čtení nepotvrzené hodnoty v sqlTransaction1. Tato hodnota nemusí ve skutečnosti existovat v databázi, pokud první transakce není potvrzena.
Vrátí zpět první transakci a vyčistí odstraněním tabulky TestSnapshot a vypnutím izolace snímků pro databázi AdventureWorks .
Poznámka:
Následující příklady používají stejný připojovací řetězec s vypnutým sdružováním připojení. Pokud je připojení ve fondu, resetováním jeho úrovně izolace nedojde k resetování úrovně izolace na serveru. V důsledku toho následná připojení, která používají stejné vnitřní připojení ve fondu, začínají s úrovněmi izolace nastavenou na připojení ve fondu. Alternativou k vypnutí sdružování připojení je explicitně nastavit úroveň izolace pro každé připojení.
// 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
Příklad
Následující příklad ukazuje chování izolace snímků při úpravě dat. Kód provede následující akce:
Připojení na Ukázková databáze AdventureWorks a umožňuje izolaci SNÍMKŮ.
Vytvoří tabulku s názvem TestSnapshotUpdate a vloží tři řádky ukázkových dat.
Začíná, ale nedokončí sqlTransaction1 pomocí izolace SNAPSHOT. V transakci jsou vybrány tři řádky dat.
Vytvoří druhý sql Připojení ion do AdventureWorks a vytvoří druhou transakci pomocí úrovně izolace READ COMMITTED, která aktualizuje hodnotu v jednom z řádků vybraných v sqlTransaction1.
Potvrdí sqlTransaction2.
Vrátí do sqlTransaction1 a pokusí se aktualizovat stejný řádek, který sqlTransaction1 již potvrzen. Vyvolá se chyba 3960 a sqlTransaction1 se automaticky vrátí zpět. SqlException.Number a SqlException.Message se zobrazí v okně konzoly.
Spustí vyčištění kódu, který vypne izolaci snímků v AdventureWorks a odstraní tabulku 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
Použití tipů k uzamčení s izolací snímků
V předchozím příkladu první transakce vybere data a druhá transakce aktualizuje data před první transakcí je schopen dokončit, což způsobí konflikt aktualizace při prvním pokusu o aktualizaci stejného řádku. Pravděpodobnost konfliktů aktualizací v dlouhotrvajících transakcích snímků můžete snížit zadáním tipů zámků na začátku transakce. Následující příkaz SELECT používá nápovědu UPDLOCK k uzamčení vybraných řádků:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Pomocí nápovědy zámku UPDLOCK blokuje všechny řádky, které se pokoušejí aktualizovat řádky před dokončením první transakce. To zaručuje, že vybrané řádky nemají žádné konflikty, když se aktualizují později v transakci. Další informace najdete v tématu Rady (Transact-SQL).
Pokud má vaše aplikace mnoho konfliktů, izolace snímků nemusí být nejlepší volbou. Rady by měly být použity pouze v případě, že jsou skutečně potřeba. Vaše aplikace by neměla být navržena tak, aby neustále závisela na nápovědě k uzamčení pro svou operaci.