Pillanatkép-elkülönítés az SQL Serveren
A pillanatkép-elkülönítés növeli az egyidejűséget az OLTP-alkalmazások esetében.
A pillanatképek elkülönítésének és a sorok verziószámozásának ismertetése
A pillanatkép-elkülönítés engedélyezése után az egyes tranzakciók frissített sorverzióit meg kell őrizni. Az SQL Server 2019 előtt ezeket a verziókat a tempdb tárolja. Az SQL Server 2019 bevezet egy új funkciót, a Gyorsított adatbázis-helyreállítást (ADR), amelyhez saját sorverziókra van szükség. Az SQL Server 2019-hez hasonlóan, ha az ADR nincs engedélyezve, a sorverziók a tempdb-ben maradnak, mint mindig. Ha az ADR engedélyezve van, akkor a pillanatkép-elkülönítéshez és az ADR-hez kapcsolódó összes sorverzió az ADR állandó verziótárában (PVS) van tárolva, amely a felhasználó által megadott fájlcsoportban található a felhasználói adatbázisban. Egy egyedi tranzakciósorozat-szám azonosítja az egyes tranzakciókat, és ezeket az egyedi számokat minden egyes sorverzióhoz rögzíti a rendszer. A tranzakció a legutóbbi sorverziókkal működik, amelyek sorszámmal rendelkeznek a tranzakció sorszáma előtt. A tranzakció megkezdése után létrehozott újabb sorverziókat a tranzakció figyelmen kívül hagyja.
A "pillanatkép" kifejezés azt a tényt tükrözi, hogy a tranzakció összes lekérdezése ugyanazt az adatbázis-verziót vagy pillanatképet látja az adatbázis állapotától függően, amikor a tranzakció megkezdődik. A pillanatkép-tranzakció mögöttes adatsorain vagy adatoldalain nem történik zárolás, ami lehetővé teszi más tranzakciók végrehajtását anélkül, hogy egy korábbi, nem befejezett tranzakció blokkolta őket. Az adatokat módosító tranzakciók nem tiltják le az adatokat olvasott tranzakciókat, és az adatokat olvasott tranzakciók nem tiltják le az adatokat író tranzakciókat, mivel általában az SQL Server alapértelmezett READ COMMITTED elkülönítési szintje alatt lennének. Ez a nem blokkoló viselkedés jelentősen csökkenti az összetett tranzakciók holtpontjainak valószínűségét is.
A pillanatkép-elkülönítés optimista egyidejűségi modellt használ. Ha egy pillanatkép-tranzakció megpróbál módosításokat véglegesíteni a tranzakció kezdete óta megváltozott adatokon, a tranzakció vissza fog gördülni, és hibaüzenet jelenik meg. Ezt elkerülheti, ha UPDLOCK-tippeket használ Standard kiadás LECT-utasításokhoz, amelyek hozzáférnek a módosítandó adatokhoz. További információ: Tippek (Transact-SQL).
A pillanatkép-elkülönítést engedélyezni kell a ALLOW_SNAPSHOT_ISOLATION ON adatbázis beállításának beállításával, mielőtt a tranzakciókban használva lenne. Ez aktiválja a sorverziók ideiglenes adatbázisban (tempdb) való tárolásának mechanizmusát. Engedélyeznie kell a pillanatkép-elkülönítést minden olyan adatbázisban, amely a Transact-SQL ALTER DATABA Standard kiadás utasítással használja. Ebben a tekintetben a pillanatképek elkülönítése eltér a READ COMMITTED, a REPEATABLE READ, a Standard kiadás RIALIZABLE és a READ UNCOMMITTED hagyományos elkülönítési szintjeitől, amelyek nem igényelnek konfigurációt. Az alábbi utasítások aktiválják a pillanatkép-elkülönítést, és lecserélik az alapértelmezett READ COMMITTED viselkedést a SNAPSHOT-ra:
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
A READ_COMMITTED_SNAPSHOT ON beállítás beállítása lehetővé teszi a verziószámozott sorok elérését az alapértelmezett READ COMMITTED elkülönítési szint alatt. Ha a READ_COMMITTED_SNAPSHOT beállítás KI értékre van állítva, a verziószámozott sorok eléréséhez explicit módon be kell állítania az egyes munkamenetek pillanatkép-elkülönítési szintjét.
Egyidejűség kezelése elkülönítési szintekkel
A Transact-SQL-utasítás végrehajtásának elkülönítési szintje határozza meg annak zárolási és sorverziós viselkedését. Az elkülönítési szint kapcsolatszintű hatókörrel rendelkezik, és a Standard kiadás T TRANSACTION ISOLATION LEVEL utasítással való kapcsolat beállítása után a kapcsolat lezárultáig vagy egy másik elkülönítési szint beállításáig érvényben marad. Ha egy kapcsolat lezárul, és visszakerül a készletbe, a legutóbbi Standard kiadás T TRANSACTION ISOLATION LEVEL utasítás elkülönítési szintje megmarad. A készletezett kapcsolatok későbbi újrafelhasználása a kapcsolat készletezésének időpontjában érvényben lévő elkülönítési szintet használja.
A kapcsolaton belül kiadott egyes lekérdezések tartalmazhatnak olyan zárolási tippeket, amelyek módosítják az elkülönítést egyetlen utasítás vagy tranzakció esetében, de nem befolyásolják a kapcsolat elkülönítési szintjét. A tárolt eljárásokban vagy függvényekben beállított elkülönítési szintek vagy zárolási tippek nem módosítják az őket meghívó kapcsolat elkülönítési szintjét, és csak a tárolt eljárás vagy függvényhívás időtartamára vannak érvényben.
Az SQL-92 szabványban meghatározott négy elkülönítési szint támogatott az SQL Server korai verzióiban:
A READ UNCOMMITTED a legkevésbé korlátozó elkülönítési szint, mivel figyelmen kívül hagyja a más tranzakciók által elhelyezett zárolásokat. A READ UNCOMMITTED alatt végrehajtott tranzakciók beolvashatják azokat a módosított adatértékeket, amelyeket más tranzakciók még nem véglegesítettek; ezeket "piszkos" olvasásoknak nevezzük.
A READ COMMITTED az SQL Server alapértelmezett elkülönítési szintje. Megakadályozza a piszkos olvasást, ha megadja, hogy az utasítások nem tudják beolvasni azokat az adatértékeket, amelyeket más tranzakciók módosítottak, de még nem véglegesítettek. Más tranzakciók továbbra is módosíthatják, beszúrhatják vagy törölhetik az adatokat az aktuális tranzakció egyes utasításainak végrehajtása között, ami nem megismételhető olvasásokat vagy "fantom" adatokat eredményez.
A REPEATABLE READ egy szigorúbb elkülönítési szint, mint a READ COMMITTED. Magában foglalja a READ COMMITTED-t, és azt is meghatározza, hogy más tranzakciók nem módosíthatják vagy törölhetik az aktuális tranzakció által beolvasott adatokat, amíg az aktuális tranzakció véglegesítésre nem került. Az egyidejűség alacsonyabb, mint a READ COMMITTED esetében, mivel az olvasási adatok megosztott zárolásai a tranzakció időtartamára vannak tárolva, nem pedig az egyes utasítások végén.
Standard kiadás RIALIZABLE a legkorlátozóbb elkülönítési szint, mivel a kulcsok teljes tartományát zárolja, és a zárolásokat a tranzakció befejezéséig tárolja. Ez magában foglalja az ISMÉTELHETŐ OLVASÁSt, és azt a korlátozást adja hozzá, hogy más tranzakciók nem szúrhatók be új sorokat a tranzakció által beolvasott tartományokba, amíg a tranzakció be nem fejeződik.
További információkért tekintse meg a tranzakciózárolási és a sorverzió-használati útmutatót.
Pillanatkép-elkülönítési szint bővítményei
Az SQL Server bővítményeket vezetett be az SQL-92 elkülönítési szintjeihez a SNAPSHOT elkülönítési szint bevezetésével és a READ COMMITTED további implementálásával. A READ_COMMITTED_SNAPSHOT elkülönítési szint transzparens módon helyettesítheti a READ COMMITTED elemet az összes tranzakció esetében.
A SNAPSHOT-elkülönítés azt határozza meg, hogy a tranzakción belül beolvasott adatok soha nem tükrözik a többi egyidejű tranzakció módosításait. A tranzakció a tranzakció kezdetekor létező adatsor-verziókat használja. Olvasáskor nem kerül zárolás az adatokra, így a PILLANATKÉP-tranzakciók nem tiltják le más tranzakciók adatírását. Az adatokat író tranzakciók nem tiltják le a pillanatkép-tranzakciókat az adatok olvasásában. A funkció használatához engedélyeznie kell a pillanatképek elkülönítését a ALLOW_SNAPSHOT_ISOLATION adatbázis beállításával.
A READ_COMMITTED_SNAPSHOT adatbázis-beállítás határozza meg az alapértelmezett READ COMMITTED elkülönítési szint viselkedését, ha a pillanatkép-elkülönítés engedélyezve van az adatbázisban. Ha nem ad meg explicit módon READ_COMMITTED_SNAPSHOT BE, a READ COMMITTED minden implicit tranzakcióra érvényes. Ez ugyanazt a viselkedést eredményezi, mint a READ_COMMITTED_SNAPSHOT KI (alapértelmezett) beállítás. Ha READ_COMMITTED_SNAPSHOT KI van kapcsolva, az adatbázismotor megosztott zárolásokkal kényszeríti ki az alapértelmezett elkülönítési szintet. Ha a READ_COMMITTED_SNAPSHOT adatbázis beállítását BE értékre állítja, az adatbázismotor alapértelmezés szerint sorverziót és pillanatkép-elkülönítést használ az adatok védelméhez használt zárolások helyett.
A pillanatkép elkülönítése és a sorok verziószámozásának működése
Ha a SNAPSHOT elkülönítési szint engedélyezve van, minden sor frissítésekor az SQL Server adatbázismotorja tárolja az eredeti sor másolatát a tempdb-ben, és hozzáad egy tranzakciósorozat-számot a sorhoz. A következő események sorozata következik be:
A rendszer új tranzakciót indít el, és hozzá van rendelve egy tranzakciósorozat-számhoz.
Az adatbázismotor beolvassa a tranzakció egy sorát, és lekéri a sorverziót a tempdb-ből, amelynek sorszáma a legközelebb van a tranzakcióütemezési számhoz, és annál alacsonyabb.
Az adatbázismotor ellenőrzi, hogy a tranzakcióütemezési szám nem szerepel-e a pillanatkép-tranzakció indításakor aktív, nem véglegesített tranzakciók tranzakcióütemezési számainak listájában.
A tranzakció beolvassa a sor azon verzióját a tempdb-ből, amely a tranzakció kezdetekor aktuális volt. A tranzakció elindítása után nem jelenik meg új sorok beszúrása, mert ezek a sorszámértékek magasabbak lesznek a tranzakcióütemezési szám értékénél.
Az aktuális tranzakció azokat a sorokat fogja látni, amelyeket a tranzakció elindítása után töröltek, mivel a tempdb egy sorverziója alacsonyabb sorszámértékkel fog rendelkezni.
A pillanatkép-elkülönítés nettó hatása az, hogy a tranzakció az összes adatot úgy látja, ahogyan az a tranzakció elején létezett, anélkül, hogy a mögöttes táblákat zárolta volna vagy zárolta volna. Ez teljesítménybeli javulást eredményezhet olyan helyzetekben, amikor versengés történik.
A pillanatkép-tranzakciók mindig optimista egyidejűség-vezérlést használnak, visszatartva azokat a zárolásokat, amelyek megakadályozzák, hogy más tranzakciók frissítsenek sorokat. Ha egy pillanatkép-tranzakció megpróbál véglegesíteni egy frissítést egy olyan sorra, amely a tranzakció elindítása után módosult, a rendszer visszaállítja a tranzakciót, és hibaüzenet jelenik meg.
Pillanatkép-elkülönítés használata a ADO.NET
A pillanatkép-elkülönítést az osztály támogatja ADO.NET SqlTransaction . Ha egy adatbázis engedélyezve van a pillanatképek elkülönítéséhez, de nincs konfigurálva READ_COMMITTED_SNAPSHOT ON-hoz, a metódus meghívásakor az IsolationLevel.Snapshot enumerálási érték használatával kell kezdeményeznieSqlTransaction.BeginTransaction Ez a kódtöredék feltételezi, hogy a kapcsolat egy nyitott SqlConnection objektum.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Példa
Az alábbi példa bemutatja, hogyan viselkednek a különböző elkülönítési szintek a zárolt adatok elérésének megkísérlésével, és nem az éles kódban való használatra szolgálnak.
A kód csatlakozik az AdventureWorks mintaadatbázishoz az SQL Serveren, és létrehoz egy TestSnapshot nevű táblát, és beszúr egy adatsort. A kód az ALTER DATABA Standard kiadás Transact-SQL utasítással kapcsolja be az adatbázis pillanatkép-elkülönítését, de nem állítja be a READ_COMMITTED_SNAPSHOT beállítást, így az alapértelmezett READ COMMITTED elkülönítési szintű viselkedés érvényben marad. A kód ezután a következő műveleteket hajtja végre:
Megkezdődik, de nem fejeződik be az sqlTransaction1, amely a Standard kiadás RIALIZABLE elkülönítési szintet használja a frissítési tranzakció elindításához. Ennek hatására zárolja a táblázatot.
Megnyitja a második kapcsolatot, és elindít egy második tranzakciót a SNAPSHOT elkülönítési szinttel a TestSnapshot táblában lévő adatok beolvasásához. Mivel a pillanatkép-elkülönítés engedélyezve van, ez a tranzakció beolvassa az sqlTransaction1 elindítása előtt létező adatokat.
Megnyitja a harmadik kapcsolatot, és elindít egy tranzakciót a READ COMMITTED elkülönítési szinttel, hogy megpróbálja beolvasni az adatokat a táblában. Ebben az esetben a kód nem tudja beolvasni az adatokat, mert nem tudja beolvasni az első tranzakcióban a táblára helyezett zárolásokat, és időtúllépést jelez. Ugyanez az eredmény akkor fordul elő, ha a REPEATABLE READ és Standard kiadás RIALIZABLE elkülönítési szinteket használják, mert ezek az elkülönítési szintek nem tudják beolvasni az első tranzakcióban elhelyezett zárolásokat is.
Megnyitja a negyedik kapcsolatot, és elindít egy tranzakciót a READ UNCOMMITTED elkülönítési szinttel, amely az sqlTransaction1 nem véglegesített értékének piszkos olvasását hajtja végre. Előfordulhat, hogy ez az érték valójában nem létezik az adatbázisban, ha az első tranzakció nincs véglegesítése.
Visszaállítja az első tranzakciót, és törli a TestSnapshot táblát, és kikapcsolja az AdventureWorks-adatbázis pillanatkép-elkülönítését.
Feljegyzés
Az alábbi példák ugyanazt a kapcsolati sztring használják, ha a kapcsolatkészletezés ki van kapcsolva. Ha egy kapcsolat össze van állítva, az elkülönítési szint alaphelyzetbe állítása nem állítja vissza az elkülönítési szintet a kiszolgálón. Ennek eredményeképpen az azonos készletezett belső kapcsolatot használó későbbi kapcsolatok a készletezett kapcsolat elkülönítési szintjeivel kezdődnek. A kapcsolatkészletezés kikapcsolásának másik lehetősége az elkülönítési szint kifejezetten az egyes kapcsolatokhoz való beállítása.
// 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élda
Az alábbi példa a pillanatképek elkülönítésének viselkedését mutatja be az adatok módosításakor. A kód a következő műveleteket hajtja végre:
Csatlakozás a AdventureWorks mintaadatbázis, és lehetővé teszi a PILLANATKÉP elkülönítését.
Létrehoz egy TestSnapshotUpdate nevű táblát , és három sornyi mintaadatot szúr be.
Az sqlTransaction1 a SNAPSHOT elkülönítéssel kezdődik, de nem fejeződik be. A tranzakció három adatsort jelöl ki.
Létrehoz egy második Sql Csatlakozás iont az AdventureWorksben, és létrehoz egy második tranzakciót a READ COMMITTED elkülönítési szinttel, amely frissíti az sqlTransaction1-ben kijelölt sorok egyikének értékét.
Véglegesíti az sqlTransaction2-t.
Visszatér az sqlTransaction1 fájlhoz, és megpróbálja frissíteni azt a sort, amelyet az sqlTransaction1 már véglegesített. A rendszer 3960-ás hibát jelez, és az sqlTransaction1 automatikusan vissza lesz állítva. Az SqlException.Number és az SqlException.Message a Konzol ablakban jelenik meg.
Eltávolító kódot hajt végre a pillanatképek elkülönítésének kikapcsolásához az AdventureWorksben, és törli a TestSnapshotUpdate táblát.
// 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
Tippek zárolása pillanatkép-elkülönítéssel
Az előző példában az első tranzakció kiválasztja az adatokat, egy második tranzakció pedig frissíti az adatokat, mielőtt az első tranzakció befejeződhet, ami frissítési ütközést okoz, amikor az első tranzakció megpróbálja frissíteni ugyanazt a sort. Csökkentheti a frissítési ütközések esélyét a hosszú ideig futó pillanatkép-tranzakciókban, ha a tranzakció elején zárolási tippeket ad meg. A következő Standard kiadás LECT utasítás az UPDLOCK-tipp használatával zárolja a kijelölt sorokat:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Az UPDLOCK zárolási tipp használatával letilthatja a sorokat az első tranzakció befejeződése előtt frissíteni próbáló sorokat. Ez garantálja, hogy a kijelölt sorok nem ütköznek a tranzakció későbbi frissítésekor. További információ: Tippek (Transact-SQL).
Ha az alkalmazásnak sok ütközése van, előfordulhat, hogy a pillanatképek elkülönítése nem a legjobb választás. Tippeket csak akkor szabad használni, ha valóban szükség van rá. Az alkalmazást nem úgy kell megtervezni, hogy a működéshez folyamatosan a zárolási tippekre támaszkodjon.