Poznámka:
Přístup k této stránce vyžaduje autorizaci. Můžete se zkusit přihlásit nebo změnit adresáře.
Přístup k této stránce vyžaduje autorizaci. Můžete zkusit změnit adresáře.
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 verze SQL Server 2019, pokud není povoleno ADR, se verze řádků uchovávají v tempdb stejně jako vždy. 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 pracuje 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á. Při transakci snímkování nejsou na podkladových datových řádcích ani stránkách získány žádné zámky, což umožňuje 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í.
Model optimistické souběžnosti využívá izolaci snímků. Pokud se snímek transakce pokusí potvrdit změny v datech, která se od zahájení transakce změnila, transakce se vrátí zpět a bude vyvolána 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 naleznete v tématu Tipy (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ů je nutné 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í výroky aktivují izolaci SNAPSHOT a nahrazují výchozí chování READ COMMITTED za SNAPSHOT:
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 verzovaným řádkům pod výchozí úrovní izolace READ COMMITTED. Pokud je možnost READ_COMMITTED_SNAPSHOT nastavena na VYPNUTO, je nutné explicitně nastavit úroveň izolace snímku pro každou relaci, aby bylo možné přistoupit k verzovaným řádkům.
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 není připojení ukončeno nebo není nastavena jiná úroveň izolace. Při zavření připojení a jeho vrácení do fondu se zachová izolační úroveň, která byla nastavena posledním příkazem SET TRANSACTION ISOLATION LEVEL. Následná připojení znovu využívající připojení ve fondu používají úroveň izolace, která platila v době, kdy bylo připojení vloženo do 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:
READ UNCOMMITTED je nejméně 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. Zabraňuje špinavému čtení specifikováním, že příkazy nemohou číst hodnoty dat, které byly změněny, ale dosud nebyly 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 COMMITTED a navíc určuje, že žádné jiné transakce nemohou upravovat nebo odstraňovat data, která byla přečtena aktuální transakcí, dokud aktuální transakce není potvrzena. 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, které pracují se snímky, 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 databáze READ_COMMITTED_SNAPSHOT určuje chování výchozí úrovně izolace READ COMMITTED, když je v databázi povolena izolace snímku. 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ý modul 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 verzování řá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 čte řádek v rámci transakce a vyhledá verzi řádku z
tempdb, jejíž pořadové číslo je nejblíže a současně nižší než pořadové číslo transakce.Databázový stroj zkontroluje, zda sekvenční číslo transakce není v seznamu sekvenčních čísel neuzavřených transakcí, které byly aktivní, když byla zahájena transakce snímku.
Transakce čte verzi řádku z
tempdb, která byla aktuální na 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 zobrazí řádky, které byly odstraněny po zahájení transakce, protože existuje verze řádku
tempdbs 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 respektovala nebo umístila 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á optimistické řízení paralelismu, nepoužívá žádné zámky, které by zabránily jiným transakcím v aktualizaci řádků. Pokud se snímek transakce pokusí potvrdit aktualizaci řádku, který byl změněn po začátku transakce, transakce se vrátí zpět a objeví se chyba.
Práce s izolací snímků v ADO.NET
Izolace snímků v ADO.NET je podporována třídou SqlTransaction. Pokud je databáze povolena pro izolaci snímků, ale není nakonfigurována pro READ_COMMITTED_SNAPSHOT ON, musíte při volání metody SqlTransaction použít výčtovou hodnotu 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 AdventureWorks ukázkové databázi 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á sqlTransaction1, ale nedokončí, která používá úroveň izolace SERIALIZABLE k zahájení aktualizační transakce. To má za následek uzamčení tabulky.
Otevře druhé připojení a zahájí druhou transakci pomocí úrovně izolace SNAPSHOT pro čtení dat z tabulky
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 za zámky umístěné na tabulce v první transakci a vyprší časový limit. Stejný výsledek by nastal, pokud by 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
TestSnapshottabulky a vypnutím izolace snímků proAdventureWorksdatabázi.
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í součástí 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řipojí se k
AdventureWorksukázkové databázi a povolí izolaci SNAPSHOT.Vytvoří tabulku s názvem
TestSnapshotUpdatea 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ý
SqlConnectiondoAdventureWorksa vytvoří druhou transakci, která pomocí úrovně izolace READ COMMITTED aktualizuje hodnotu v jednom z řádků vybraných v sqlTransaction1.Potvrdí transakci sqlTransaction2.
Vrátí se k sqlTransaction1 a pokusí se aktualizovat stejný řádek, který sqlTransaction1 již potvrdil. Vyvolá se chyba 3960 a sqlTransaction1 se automaticky vrátí zpět. SqlException.Number a SqlException.Message se zobrazí v okně konzoly.
Provede vyčištění kódu, aby se vypnula izolace snímků v
AdventureWorksa odstranila se tabulkaTestSnapshotUpdate.
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(connectionString))
{
connection1.Open();
SqlCommand command1 = connection1.CreateCommand();
// Enable Snapshot isolation in AdventureWorks
command1.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
try
{
command1.ExecuteNonQuery();
Console.WriteLine(
"Snapshot Isolation turned on in AdventureWorks.");
}
catch (Exception ex)
{
Console.WriteLine($"ALLOW_SNAPSHOT_ISOLATION ON failed: {ex.Message}");
}
// Create a table
command1.CommandText =
"IF EXISTS "
+ "(SELECT * FROM sys.tables "
+ "WHERE name=N'TestSnapshotUpdate')"
+ " DROP TABLE TestSnapshotUpdate";
command1.ExecuteNonQuery();
command1.CommandText =
"CREATE TABLE TestSnapshotUpdate "
+ "(ID int primary key, CharCol nvarchar(100));";
try
{
command1.ExecuteNonQuery();
Console.WriteLine("TestSnapshotUpdate table created.");
}
catch (Exception ex)
{
Console.WriteLine($"CREATE TABLE failed: {ex.Message}");
}
// Insert some data
command1.CommandText =
"INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');"
+ "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');"
+ "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');";
try
{
command1.ExecuteNonQuery();
Console.WriteLine("Data inserted TestSnapshotUpdate table.");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Begin, but do not complete, a transaction
// using the Snapshot isolation level.
SqlTransaction transaction1 = default!;
try
{
transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot);
command1.CommandText =
"SELECT * FROM TestSnapshotUpdate WHERE ID BETWEEN 1 AND 3";
command1.Transaction = transaction1;
command1.ExecuteNonQuery();
Console.WriteLine("Snapshot transaction1 started.");
// Open a second Connection/Transaction to update data
// using ReadCommitted. This transaction should succeed.
using (SqlConnection connection2 = new(connectionString))
{
connection2.Open();
SqlCommand command2 = connection2.CreateCommand();
command2.CommandText = "UPDATE TestSnapshotUpdate SET CharCol="
+ "N'New value from Connection2' WHERE ID=1";
SqlTransaction transaction2 =
connection2.BeginTransaction(IsolationLevel.ReadCommitted);
command2.Transaction = transaction2;
try
{
command2.ExecuteNonQuery();
transaction2.Commit();
Console.WriteLine(
"transaction2 has modified data and committed.");
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
transaction2.Rollback();
}
finally
{
transaction2.Dispose();
}
}
// Now try to update a row in Connection1/Transaction1.
// This transaction should fail because Transaction2
// succeeded in modifying the data.
command1.CommandText =
"UPDATE TestSnapshotUpdate SET CharCol="
+ "N'New value from Connection1' WHERE ID=1";
command1.Transaction = transaction1;
command1.ExecuteNonQuery();
transaction1.Commit();
Console.WriteLine("You should never see this.");
}
catch (SqlException ex)
{
Console.WriteLine("Expected failure for transaction1:");
Console.WriteLine($" {ex.Number}: {ex.Message}");
}
finally
{
transaction1.Dispose();
}
}
// CLEANUP:
// Turn off Snapshot isolation and delete the table
using (SqlConnection connection3 = new(connectionString))
{
connection3.Open();
SqlCommand command3 = connection3.CreateCommand();
command3.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
try
{
command3.ExecuteNonQuery();
Console.WriteLine(
"CLEANUP: Snapshot isolation turned off in AdventureWorks.");
}
catch (Exception ex)
{
Console.WriteLine($"CLEANUP FAILED: {ex.Message}");
}
command3.CommandText = "DROP TABLE TestSnapshotUpdate";
try
{
command3.ExecuteNonQuery();
Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
}
catch (Exception ex)
{
Console.WriteLine($"CLEANUP FAILED: {ex.Message}");
}
}
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()
Using connection1 As New SqlConnection(connectionString)
' Enable Snapshot isolation in AdventureWorks
connection1.Open()
Dim command1 As SqlCommand = connection1.CreateCommand
command1.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;"
Try
command1.ExecuteNonQuery()
Console.WriteLine( _
"Snapshot Isolation turned on in AdventureWorks.")
Catch ex As Exception
Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION failed: {0}", ex.Message)
End Try
' Create a table
command1.CommandText = _
"IF EXISTS (SELECT * FROM sys.databases " _
& "WHERE name=N'TestSnapshotUpdate') " _
& "DROP TABLE TestSnapshotUpdate"
command1.ExecuteNonQuery()
command1.CommandText = _
"CREATE TABLE TestSnapshotUpdate (ID int primary key, " _
& "CharCol nvarchar(100));"
Try
command1.ExecuteNonQuery()
Console.WriteLine("TestSnapshotUpdate table created.")
Catch ex As Exception
Console.WriteLine("CREATE TABLE failed: {0}", ex.Message)
End Try
' Insert some data
command1.CommandText = _
"INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');" _
& "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');" _
& "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');"
Try
command1.ExecuteNonQuery()
Console.WriteLine("Data inserted TestSnapshotUpdate table.")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
' Begin, but do not complete, a transaction
' using the Snapshot isolation level
Dim transaction1 As SqlTransaction = Nothing
Try
transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot)
command1.CommandText = _
"SELECT * FROM TestSnapshotUpdate WHERE ID " _
& "BETWEEN 1 AND 3"
command1.Transaction = transaction1
command1.ExecuteNonQuery()
Console.WriteLine("Snapshot transaction1 started.")
' Open a second Connection/Transaction to update data
' using ReadCommitted. This transaction should succeed.
Dim connection2 As SqlConnection = New SqlConnection(connectionString)
Using connection2
connection2.Open()
Dim command2 As SqlCommand = connection2.CreateCommand()
command2.CommandText = "UPDATE TestSnapshotUpdate SET " _
& "CharCol=N'New value from Connection2' WHERE ID=1"
Dim transaction2 As SqlTransaction = _
connection2.BeginTransaction(IsolationLevel.ReadCommitted)
command2.Transaction = transaction2
Try
command2.ExecuteNonQuery()
transaction2.Commit()
Console.WriteLine( _
"transaction2 has modified data and committed.")
Catch ex As SqlException
Console.WriteLine(ex.Message)
transaction2.Rollback()
Finally
transaction2.Dispose()
End Try
End Using
' Now try to update a row in Connection1/Transaction1.
' This transaction should fail because Transaction2
' succeeded in modifying the data.
command1.CommandText = _
"UPDATE TestSnapshotUpdate SET CharCol=" _
& "N'New value from Connection1' WHERE ID=1"
command1.Transaction = transaction1
command1.ExecuteNonQuery()
transaction1.Commit()
Console.WriteLine("You should never see this.")
Catch ex As SqlException
Console.WriteLine("Expected failure for transaction1:")
Console.WriteLine(" {0}: {1}", ex.Number, ex.Message)
Finally
transaction1.Dispose()
End Try
End Using
' CLEANUP:
' Turn off Snapshot isolation and delete the table
Dim connection3 As New SqlConnection(connectionString)
Using connection3
connection3.Open()
Dim command3 As SqlCommand = connection3.CreateCommand()
command3.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
Try
command3.ExecuteNonQuery()
Console.WriteLine( _
"Snapshot isolation turned off in AdventureWorks.")
Catch ex As Exception
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
End Try
command3.CommandText = "DROP TABLE TestSnapshotUpdate"
Try
command3.ExecuteNonQuery()
Console.WriteLine("TestSnapshotUpdate table deleted.")
Catch ex As Exception
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
End Try
End Using
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ředtím, než je první transakce schopna dokončit, což způsobí konflikt aktualizace, když se první transakce pokusí aktualizovat stejný řádek. Pravděpodobnost konfliktů aktualizací v dlouhotrvajících transakcích snímků můžete snížit použitím zámkových náznaků 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 naleznete v tématu Tipy (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 zamykacích tipech pro svůj provoz.