Langue
Remarque
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
Isolation par instantané améliore la concurrence pour les applications OLTP.
Présentation de l'isolation d'instantané et gestion des versions de ligne
Une fois l’isolation des instantanés activée, les versions de lignes mises à jour pour chaque transaction doivent être conservées. Avant SQL Server 2019, ces versions ont été stockées dans tempdb. SQL Server 2019 introduit une nouvelle fonctionnalité de récupération de base de données accélérée (ADR) qui nécessite son propre ensemble de versions de lignes. Par conséquent, à partir de SQL Server 2019, si adr n’est pas activé, les versions de ligne sont conservées tempdb comme toujours. Si ADR est activé, toutes les versions de ligne, liées à l’isolation des instantanés et à l’ADR, sont conservées dans le magasin de versions persistantes (PVS) de l’ADR, qui se trouve dans la base de données utilisateur dans un groupe de fichiers spécifié par l’utilisateur. Un numéro de séquence de transaction unique identifie chaque transaction, et ces numéros uniques sont enregistrés pour chaque version de ligne. La transaction fonctionne avec les versions de ligne les plus récentes ayant un numéro de séquence antérieur au numéro de séquence de la transaction. Les versions de ligne plus récentes créées après le début de la transaction sont ignorées par la transaction.
Le terme « instantané » reflète le fait que toutes les requêtes de la transaction voient la même version, ou instantané, de la base de données, en fonction de l’état de la base de données au moment où la transaction commence. Aucun verrou n’est acquis sur les lignes de données ou les pages de données sous-jacentes dans une transaction d’instantané, ce qui permet à d’autres transactions de s’exécuter sans être bloquées par une transaction inachevée antérieure. Les transactions qui modifient les données ne bloquent pas les transactions qui lisent les données, et les transactions qui lisent les données ne bloquent pas les transactions qui écrivent des données, comme ce serait normalement le cas sous le niveau d’isolation par défaut READ COMMITTED dans SQL Server. Ce comportement non bloquant réduit également sensiblement la probabilité de blocages pour les transactions complexes.
L’isolation d’instantané utilise un modèle de concurrence optimiste. Si une transaction d’instantané tente de valider des modifications apportées à des données qui ont été modifiées après le début de la transaction, la transaction est annulée et une erreur est générée. Vous pouvez éviter cela à l’aide de UPDLOCK indicateurs pour SELECT les instructions qui accèdent à des données devant être modifiées. Pour plus d’informations, consultez Indicateurs (Transact-SQL).
L’isolation d’instantané doit être activée en définissant l’option de base de données ALLOW_SNAPSHOT_ISOLATION ON avant d’être utilisée dans une transaction. Cela active le mécanisme de stockage des versions de lignes dans la base de données temporaire (tempdb). Vous devez activer l’isolation des instantanés dans chaque base de données qui l’utilise avec l’instruction ALTER DATABASE Transact-SQL. À cet égard, l’isolation de capture instantanée diffère des niveaux d’isolation traditionnels de READ COMMITTED, REPEATABLE READ, SERIALIZABLE et READ UNCOMMITTED, qui ne nécessitent aucune configuration. Les instructions suivantes activent les transactions SNAPSHOT et les lectures de lignes avec gestion de versions READ COMMITTED :
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
La définition de l’option READ_COMMITTED_SNAPSHOT ON autorise l’accès aux lignes avec version sous le niveau d’isolation par défaut READ COMMITTED . Si l’option READ_COMMITTED_SNAPSHOT est définie sur OFF, vous devez définir explicitement le niveau d’isolation d’instantané pour chaque session afin d’accéder aux lignes avec version.
Gestion de la concurrence avec des niveaux d’isolation
Le niveau d’isolation sous lequel une instruction Transact-SQL s’exécute détermine son comportement de verrouillage et de contrôle de version de ligne. Un niveau d’isolation a une étendue à l’échelle de la connexion et, une fois défini pour une connexion avec l’instruction SET TRANSACTION ISOLATION LEVEL , il reste en vigueur jusqu’à ce que la connexion soit fermée ou qu’un autre niveau d’isolation soit défini. Lorsqu’une connexion est fermée et retournée au pool, le niveau d’isolation de la dernière SET TRANSACTION ISOLATION LEVEL instruction est conservé. Les connexions suivantes réutilisant une connexion regroupée utilisent le niveau d’isolation en vigueur au moment où la connexion est regroupée.
Les requêtes individuelles émises au sein d’une connexion peuvent contenir des indicateurs de verrou qui modifient l’isolation pour une seule instruction ou transaction, mais n’affectent pas le niveau d’isolation de la connexion. Les niveaux d’isolation ou les indicateurs de verrou définis dans les procédures stockées ou les fonctions ne modifient pas le niveau d’isolation de la connexion qui les appelle et ne sont effectifs que pendant la durée de la procédure stockée ou de l’appel de fonction.
Quatre niveaux d’isolation définis dans la norme SQL-92 étaient pris en charge dans les premières versions de SQL Server :
READ UNCOMMITTEDest le niveau d’isolation le moins restrictif, car il ignore les verrous placés par d’autres transactions. Les transactions exécutées sousREAD UNCOMMITTEDpeuvent lire les valeurs de données modifiées qui n’ont pas encore été validées par d’autres transactions ; elles sont appelées lectures non validées.READ COMMITTEDest le niveau d’isolation par défaut pour SQL Server. Il empêche les lectures erronées en spécifiant que les instructions ne peuvent pas lire des valeurs de données modifiées et non encore validées par d'autres transactions. D’autres transactions peuvent toujours modifier, insérer ou supprimer des données entre les exécutions d’instructions individuelles dans la transaction en cours, ce qui aboutit à des lectures non renouvelables ou des données « fantômes ».REPEATABLE READest un niveau d’isolation plus restrictif queREAD COMMITTED. Il englobeREAD COMMITTEDet spécifie également qu’aucune autre transaction ne peut modifier ou supprimer des données lues par la transaction actuelle jusqu’à ce que la transaction actuelle valide. La concurrence est inférieure à celle pourREAD COMMITTEDlaquelle les verrous partagés sur les données de lecture sont conservés pendant la durée de la transaction au lieu d’être libérés à la fin de chaque instruction.SERIALIZABLEest le niveau d’isolation le plus restrictif, car il verrouille des plages entières de clés et conserve les verrous jusqu’à ce que la transaction soit terminée. Elle englobeREPEATABLE READet ajoute la restriction que d’autres transactions ne peuvent pas insérer de nouvelles lignes dans des plages lues par la transaction jusqu’à ce que la transaction soit terminée.
Pour plus d’informations, consultez le Guide du verrouillage des transactions et de la gestion de versions de ligne.
Extensions de niveau d'isolation d'instantané
SQL Server introduit des extensions aux niveaux d’isolation SQL-92 avec l’introduction du SNAPSHOT niveau d’isolation et une implémentation supplémentaire de READ COMMITTED.
SNAPSHOTl’isolation spécifie que les données lues dans une transaction ne reflètent jamais les modifications apportées par d’autres transactions simultanées. La transaction utilise les versions de ligne de données qui existent au début de la transaction. Aucun verrou n’est placé sur les données lorsqu’elles sont lues.SNAPSHOTLes transactions n’empêchent par conséquent pas les autres transactions d’écrire des données. Les transactions qui écrivent des données n'empêchent pas les transactions instantanées de lire des données. Vous devez activer l’isolation des instantanés en définissant l’optionALLOW_SNAPSHOT_ISOLATIONde base de données pour l’utiliser.L’option
READ_COMMITTED_SNAPSHOTde la base de données contrôle la façon dontREAD COMMITTEDlit les données. Avec cette optionOFF, l’option par défaut dans SQL Server, le Moteur de base de données utilise des verrous partagés pour protéger les lectures. Avec cette optionON,READ COMMITTEDutilise des versions de lignes au niveau de l’instruction au lieu de verrous partagés pour protéger les lectures.
Fonctionnement de l'isolation d'instantané et de la gestion des versions de ligne
Lorsque le SNAPSHOT niveau d’isolation est activé, chaque fois qu’une ligne est mise à jour, l’Moteur de base de données SQL Server stocke une copie de la ligne d’origine dans tempdbet ajoute un numéro de séquence de transactions à la ligne. Voici la séquence des événements :
Une nouvelle transaction est lancée et un numéro de séquence de transaction lui est affecté.
Le moteur de base de données lit une ligne dans la transaction et récupère la version de ligne à partir de
tempdb, dont le numéro de séquence est le plus proche et inférieur au numéro de séquence de la transaction.Le moteur de base de données vérifie si le numéro de séquence de la transaction ne figure pas dans la liste des numéros de séquence de transaction des transactions non validées actives au démarrage de la transaction d’instantané.
La transaction lit la version de la ligne de
tempdbqui était en vigueur au début de la transaction. Les nouvelles lignes insérées après le début de la transaction ne sont pas visibles, car leurs valeurs de numéro de séquence sont supérieures au numéro de la transaction.La transaction actuelle détectera les lignes qui ont été supprimées après le début de la transaction, car il y aura une version de ligne dans
tempdbavec une valeur de numéro de séquence inférieure.
L’effet net de l'« isolation d'instantané » est que la transaction voit toutes les données telles qu’elles existaient au début de la transaction, sans respecter ni appliquer de verrous sur les tables sous-jacentes. Cela peut entraîner des améliorations des performances dans les situations où il existe une contention.
Une transaction d’instantané utilise toujours le contrôle d’accès concurrentiel optimiste, en retenant tous les verrous qui empêchent les autres transactions de mettre à jour des lignes. Si une transaction d’instantané tente de valider une mise à jour sur une ligne qui a été modifiée après le début de la transaction, la transaction est restaurée et une erreur est générée.
Utilisation de l'isolation d'instantané dans ADO.NET
Pour démarrer une transaction avec l’isolation d’instantané, activez ALLOW_SNAPSHOT_ISOLATION la base de données et passez Snapshot à BeginTransaction. L’option READ_COMMITTED_SNAPSHOT de base de données contrôle les lectures avec version de ligne au niveau d’isolation lecture validée ; elle ne sélectionne pas l’isolation d’instantané pour une transaction. Le fragment suivant suppose que connection est une instance ouverte de la classe SqlConnection.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Exemple :
L’exemple suivant montre comment les différents niveaux d’isolation se comportent en tentant d’accéder aux données verrouillées et n’est pas destiné à être utilisé dans du code de production.
Le code se connecte à l’exemple AdventureWorks de base de données dans SQL Server et crée une table nommée TestSnapshot et insère une ligne de données. Le code utilise l’instruction ALTER DATABASE Transact-SQL pour activer l’isolation d’instantané pour la base de données, mais le code ne définit pas l’option READ_COMMITTED_SNAPSHOT, laissant le comportement par défaut du niveau d’isolation READ COMMITTED en vigueur. Le code effectue ensuite les actions suivantes :
Elle commence, mais n’est pas terminée,
sqlTransaction1qui utilise leSERIALIZABLEniveau d’isolation pour démarrer une transaction de mise à jour. Cela a pour effet de verrouiller la table.Il ouvre une deuxième connexion et lance une deuxième transaction à l’aide du
SNAPSHOTniveau d’isolation pour lire les données de laTestSnapshottable. Étant donné que l’isolation des instantanés est activée, cette transaction peut lire les données qui existaient avantsqlTransaction1le démarrage.Il ouvre une troisième connexion et lance une transaction à l’aide du
READ COMMITTEDniveau d’isolation pour tenter de lire les données dans la table. Dans ce cas, le code ne peut pas lire les données, car il ne peut pas lire les verrous placés sur la table dans la première transaction et expire. Le même résultat se produit si les niveaux d’isolation etSERIALIZABLElesREPEATABLE READniveaux d’isolation ont été utilisés, car ces niveaux d’isolation ne peuvent pas également lire les verrous placés dans la première transaction.Il ouvre une quatrième connexion et lance une transaction à l’aide du
READ UNCOMMITTEDniveau d’isolation, qui effectue une lecture incorrecte de la valeur non validée danssqlTransaction1. Cette valeur peut ne jamais exister dans la base de données si la première transaction n’est pas validée.Il réinitialise la première transaction et effectue un nettoyage en supprimant la table
TestSnapshotet en désactivant l'isolation des instantanés pour la base de donnéesAdventureWorks.
Remarque
Les exemples suivants utilisent la même chaîne de connexion avec le regroupement de connexions désactivé. Si une connexion est regroupée, la réinitialisation de son niveau d’isolation ne rétablit pas le niveau d’isolation sur le serveur. Par conséquent, les connexions suivantes qui utilisent la même connexion interne regroupée démarrent avec leurs niveaux d’isolation définis sur ceux de la connexion regroupée. Une alternative à la désactivation du regroupement de connexions consiste à définir le niveau d’isolation de manière explicite pour chaque connexion.
// 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";
using (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
{
using (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";
using (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"
Using reader2 As SqlDataReader = command2.ExecuteReader()
While reader2.Read()
Console.WriteLine("Expected 1,1 Actual " _
& reader2.GetValue(0).ToString() + "," _
& reader2.GetValue(1).ToString())
End While
End Using
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
Using reader3 As SqlDataReader = command3.ExecuteReader()
While reader3.Read()
Console.WriteLine("You should never hit this.")
End While
End Using
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"
Using reader4 As SqlDataReader = command4.ExecuteReader()
While reader4.Read()
Console.WriteLine("Expected 1,22 Actual " _
& reader4.GetValue(0).ToString() _
& "," + reader4.GetValue(1).ToString())
End While
End Using
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
Exemple :
L’exemple suivant illustre le comportement de l’isolation d’instantané quand des données sont modifiées. Le code effectue les actions suivantes :
Se connecte à la base de données d’exemple
AdventureWorkset active l’isolationSNAPSHOT.Crée une table nommée
TestSnapshotUpdateet insère trois lignes d’exemples de données.Commence, mais ne se termine pas,
sqlTransaction1à l’aide de l’isolationSNAPSHOT. Trois lignes de données sont sélectionnées dans la transaction.Crée une seconde de
SqlConnectionàAdventureWorkset crée une deuxième transaction en utilisant le niveau d’isolationREAD COMMITTEDqui met à jour une valeur dans l’une des lignes sélectionnées danssqlTransaction1.Validations
sqlTransaction2.Revient à
sqlTransaction1et tente de mettre à jour la même ligne quesqlTransaction1a déjà validée. L’erreur 3960 est déclenchée etsqlTransaction1est annulée automatiquement. LesSqlException.NumberetSqlException.Messagesont affichés dans la fenêtre console.Exécute le code de nettoyage pour désactiver l’isolation par captures instantanées dans
AdventureWorkset supprimer la tableTestSnapshotUpdate.
// 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
Utilisation d'indicateurs de verrou avec une isolation d'instantané
Dans l’exemple précédent, la première transaction sélectionne des données et une deuxième transaction met à jour les données avant que la première transaction puisse se terminer, provoquant un conflit de mise à jour lorsque la première transaction tente de mettre à jour la même ligne. Vous pouvez réduire le risque de conflits de mise à jour dans les transactions par instantané de longue durée en fournissant des indications de verrouillage au début de la transaction. L’instruction suivante SELECT utilise l’indicateur UPDLOCK pour verrouiller les lignes sélectionnées :
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
L’utilisation de l’indicateur de verrouillage UPDLOCK bloque toute tentative de mise à jour de ces lignes avant la fin de la première transaction. Cela garantit que les lignes sélectionnées n’ont aucun conflit lorsqu’elles sont mises à jour ultérieurement dans la transaction. Pour plus d’informations, consultez Indicateurs (Transact-SQL).
Si votre application présente de nombreux conflits, l’isolation par instantané n’est peut-être pas le meilleur choix. Les indicateurs doivent être utilisés uniquement lorsque cela est vraiment nécessaire. Votre application ne doit pas être conçue de manière à ce qu’elle s’appuie constamment sur des indicateurs de verrou pour son fonctionnement.