Isolation d'instantanés dans SQL Server
L’isolation d’instantané améliore l’accès concurrentiel pour les applications OLTP.
Présentation de l'isolation d'instantané et gestion des versions de ligne
Une fois l’isolation d’instantané activée, les versions de ligne mise à jour pour chaque transaction doivent être maintenues. Avant SQL Server 2019, ces versions étaient 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. Ainsi, à partir de SQL Server 2019, si ADR n’est pas activé, les versions de lignes sont conservées dans tempdb comme toujours. Si ADR est activé, toutes les versions de lignes, liées à l’isolation des instantanés et à ADR, sont conservées dans la banque des versions persistantes 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 des données ne bloquent pas les transactions qui lisent des données, et les transactions qui lisent des données ne bloquent pas les transactions qui écrivent des données, comme elles le feraient normalement avec le niveau d’isolation READ COMMITTED par défaut 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 d’accès concurrentiel 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 en utilisant des indicateurs UPDLOCK pour les instructions SELECT qui accèdent aux données à modifier. Pour plus d’informations, consultez Indicateurs (Transact-SQL).
L’isolation d’instantané doit être activée en définissant l’option ALLOW_SNAPSHOT_ISOLATION sur la base de données avant qu’elle soit utilisée dans des transactions. Cela active le mécanisme de stockage de versions de ligne dans la base de données temporaire (tempdb). Vous devez activer l’isolation d’instantané dans chaque base de données qui l’utilise avec l’instruction Transact-SQL ALTER DATABASE. À cet égard, l’isolation d’instantané diffère des niveaux d’isolation traditionnels READ COMMITTED, REPEATABLE READ, SERIALIZABLE et READ UNCOMMITTED, qui ne nécessitent aucune configuration. Les instructions suivantes activent l’isolation d’instantané et remplacent le comportement READ COMMITTED par défaut par SNAPSHOT :
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 permet d’accéder aux lignes avec version sous le niveau d’isolation READ COMMITTED par défaut. Si l’option READ_COMMITTED_SNAPSHOT est désactivée (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 l'accès simultané 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 portée à l’échelle de la connexion et, une fois défini pour une connexion avec l’instruction SET TRANSACTION ISOLATION LEVEL, il reste effectif 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 instruction SET TRANSACTION ISOLATION LEVEL 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 UNCOMMITTED est le niveau d’isolation le moins restrictif, car il ignore les verrous placés par d’autres transactions. Les transactions qui s’exécutent sous READ UNCOMMITTED peuvent lire des valeurs de données modifiées qui n’ont pas encore été validées par d’autres transactions ; celles-ci sont appelées lectures « erronées ».
READ COMMITTED est 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 ».
La lecture renouvelable est un niveau d’isolation plus restrictif que READ COMMITTED. Il englobe READ COMMITTED et spécifie qu’aucune autre transaction ne peut modifier ou supprimer des données qui ont été lues par la transaction actuelle jusqu’à ce que la transaction en cours soit validée. La concurrence est inférieure à celle de READ COMMITTED, car les verrous partagés sur les données lues sont conservés pendant la durée de la transaction au lieu d’être libérés à la fin de chaque instruction.
SERIALIZABLE est le niveau d’isolation le plus restrictif, car il verrouille des groupes de clés entiers et laisse les verrous en place jusqu’à la fin de la transaction. Il englobe la lecture renouvelable et ajoute la restriction selon laquelle les autres transactions ne peuvent pas insérer de nouvelles lignes dans les plages qui ont été 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 a introduit des extensions des niveaux d’isolation de SQL-92 avec le niveau d’isolation SNAPSHOT et l’implémentation supplémentaire de READ COMMITTED. Le niveau d’isolation READ_COMMITTED_SNAPSHOT peut remplacer de façon transparente READ COMMITTED pour toutes les transactions.
L’isolation d’instantané spécifie que les données lues dans une transaction ne refléteront 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 lors de leur lecture, de sorte que les transactions d’instantané n’empêchent pas d’autres transactions d’écrire des données. De même, les transactions qui écrivent des données n’empêchent pas des transactions d’instantanés de lire des données. Vous devez activer l’isolation d’instantané en définissant l’option ALLOW_SNAPSHOT_ISOLATION sur la base de données afin de l’utiliser.
L’option de base de données READ_COMMITTED_SNAPSHOT détermine le comportement du niveau d’isolation READ COMMITTED par défaut lorsque l’isolation d’instantané est activée dans une base de données. Si vous ne spécifiez pas explicitement READ_COMMITTED_SNAPSHOT ON, READ COMMITTED est appliqué à toutes les transactions implicites. Cela produit le même comportement que le paramètre READ_COMMITTED_SNAPSHOT OFF (valeur par défaut). Lorsque READ_COMMITTED_SNAPSHOT OFF s’applique, le moteur de base de données utilise des verrous partagés pour appliquer le niveau d’isolation par défaut. Si vous affectez la valeur ON à l’option de base de données READ_COMMITTED_SNAPSHOT, le moteur de base de données utilise le contrôle de version de ligne et l’isolation d’instantané comme valeur par défaut, au lieu d’utiliser des verrous pour protéger les données.
Fonctionnement de l'isolation d'instantané et de la gestion des versions de ligne
Quand le niveau d’isolation SNAPSHOT est activé, chaque fois qu’une ligne est mise à jour, le moteur de base de données SQL Server stocke une copie de la ligne originale dans tempdb et ajoute un numéro de séquence de transaction à 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 de la transaction et extrait la version de ligne 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 dans tempdb la version de la ligne telle qu’elle était 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 en cours voit les lignes qui ont été supprimées après le début de la transaction car il y aura une version de ligne dans tempdb dont la valeur de numéro de séquence est 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 placer 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
L’isolation d’instantané est prise en charge dans ADO.NET avec la classe SqlTransaction. Si une base de données a été activée pour l’isolation d’instantané mais n’est pas configurée pour READ_COMMITTED_SNAPSHOT ON, vous devez lancer un SqlTransaction en utilisant la valeur d’énumération IsolationLevel.Snapshot lors de l’appel de la méthode BeginTransaction. Ce fragment de code suppose que la connexion est un objet SqlConnection ouvert.
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 de base de données AdventureWorks dans SQL Server, crée une table nommée TestSnapshot et insère une ligne de données. Le code utilise l’instruction Transact-SQL ALTER DATABASE pour activer l’isolation d’instantané pour la base de données, mais il ne définit pas l’option READ_COMMITTED_SNAPSHOT, ce qui laisse le comportement de niveau d’isolation READ COMMITTED par défaut en vigueur. Le code effectue ensuite les actions suivantes :
Il commence, mais ne termine pas, sqlTransaction1, qui utilise le niveau d’isolation SERIALIZABLE pour démarrer une transaction de mise à jour. Cela a pour effet de verrouiller la table.
Il ouvre une seconde connexion et lance une seconde transaction en utilisant le niveau d’isolation SNAPSHOT pour lire les données dans la table TestSnapshot. Étant donné que l’isolation d’instantané est activée, cette transaction peut lire les données qui existaient avant le démarrage de sqlTransaction1.
Il ouvre une troisième connexion et initie une transaction en utilisant le niveau d’isolation READ COMMITTED pour tenter de lire les données de la table. Dans ce cas, le code ne peut pas lire les données, car il n’est pas en mesure de lire au-delà des 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 REPEATABLE READ et SERIALIZABLE sont utilisés. En effet, ils ne peuvent pas non plus lire au-delà des verrous placés dans la première transaction.
Il ouvre une quatrième connexion et initie une transaction en utilisant le niveau d’isolation READ UNCOMMITTED, qui effectue une lecture erronée de la valeur non validée dans sqlTransaction1. Cette valeur peut ne jamais exister dans la base de données si la première transaction n’est pas validée.
Elle annule la première transaction et nettoie en supprimant la table TestSnapshot et en désactivant l’isolation d’instantané pour la base de données AdventureWorks.
Notes
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";
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
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 :
Il établit une connexion avec l’exemple de base de données AdventureWorks et active l’isolation SNAPSHOT.
Il crée une table nommée TestSnapshotUpdate et insère trois lignes d’exemples de données.
Commence, mais ne se termine pas, car sqlTransaction1 utilise l’isolation d’instantané. Trois lignes de données sont sélectionnées dans la transaction.
Il crée une seconde SqlConnection à AdventureWorks et une seconde transaction utilisant le niveau d’isolation READ COMMITTED, qui met à jour une valeur dans l’une des lignes sélectionnées dans sqlTransaction1.
Valide sqlTransaction2.
Retourne à sqlTransaction1 et tente de mettre à jour la même ligne que celle que sqlTransaction1 a déjà validée. L’erreur 3960 est déclenchée et sqlTransaction1 est restaurée automatiquement. SqlException.Number et SqlException.Message s’affichent dans la fenêtre de console.
Il exécute un code de nettoyage pour désactiver l’isolation d’instantané dans AdventureWorks et supprimer la table TestSnapshotUpdate.
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(connectionString))
{
connection1.Open();
SqlCommand command1 = connection1.CreateCommand();
// Enable Snapshot isolation in AdventureWorks
command1.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
try
{
command1.ExecuteNonQuery();
Console.WriteLine(
"Snapshot Isolation turned on in AdventureWorks.");
}
catch (Exception ex)
{
Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION ON failed: {0}", ex.Message);
}
// Create a table
command1.CommandText =
"IF EXISTS "
+ "(SELECT * FROM sys.tables "
+ "WHERE name=N'TestSnapshotUpdate')"
+ " DROP TABLE TestSnapshotUpdate";
command1.ExecuteNonQuery();
command1.CommandText =
"CREATE TABLE TestSnapshotUpdate "
+ "(ID int primary key, CharCol nvarchar(100));";
try
{
command1.ExecuteNonQuery();
Console.WriteLine("TestSnapshotUpdate table created.");
}
catch (Exception ex)
{
Console.WriteLine("CREATE TABLE failed: {0}", ex.Message);
}
// Insert some data
command1.CommandText =
"INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');"
+ "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');"
+ "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');";
try
{
command1.ExecuteNonQuery();
Console.WriteLine("Data inserted TestSnapshotUpdate table.");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Begin, but do not complete, a transaction
// using the Snapshot isolation level.
SqlTransaction transaction1 = default!;
try
{
transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot);
command1.CommandText =
"SELECT * FROM TestSnapshotUpdate WHERE ID BETWEEN 1 AND 3";
command1.Transaction = transaction1;
command1.ExecuteNonQuery();
Console.WriteLine("Snapshot transaction1 started.");
// Open a second Connection/Transaction to update data
// using ReadCommitted. This transaction should succeed.
using (SqlConnection connection2 = new(connectionString))
{
connection2.Open();
SqlCommand command2 = connection2.CreateCommand();
command2.CommandText = "UPDATE TestSnapshotUpdate SET CharCol="
+ "N'New value from Connection2' WHERE ID=1";
SqlTransaction transaction2 =
connection2.BeginTransaction(IsolationLevel.ReadCommitted);
command2.Transaction = transaction2;
try
{
command2.ExecuteNonQuery();
transaction2.Commit();
Console.WriteLine(
"transaction2 has modified data and committed.");
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
transaction2.Rollback();
}
finally
{
transaction2.Dispose();
}
}
// Now try to update a row in Connection1/Transaction1.
// This transaction should fail because Transaction2
// succeeded in modifying the data.
command1.CommandText =
"UPDATE TestSnapshotUpdate SET CharCol="
+ "N'New value from Connection1' WHERE ID=1";
command1.Transaction = transaction1;
command1.ExecuteNonQuery();
transaction1.Commit();
Console.WriteLine("You should never see this.");
}
catch (SqlException ex)
{
Console.WriteLine("Expected failure for transaction1:");
Console.WriteLine(" {0}: {1}", ex.Number, ex.Message);
}
finally
{
transaction1.Dispose();
}
}
// CLEANUP:
// Turn off Snapshot isolation and delete the table
using (SqlConnection connection3 = new(connectionString))
{
connection3.Open();
SqlCommand command3 = connection3.CreateCommand();
command3.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
try
{
command3.ExecuteNonQuery();
Console.WriteLine(
"CLEANUP: Snapshot isolation turned off in AdventureWorks.");
}
catch (Exception ex)
{
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message);
}
command3.CommandText = "DROP TABLE TestSnapshotUpdate";
try
{
command3.ExecuteNonQuery();
Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
}
catch (Exception ex)
{
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message);
}
}
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()
Using connection1 As New SqlConnection(connectionString)
' Enable Snapshot isolation in AdventureWorks
connection1.Open()
Dim command1 As SqlCommand = connection1.CreateCommand
command1.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;"
Try
command1.ExecuteNonQuery()
Console.WriteLine( _
"Snapshot Isolation turned on in AdventureWorks.")
Catch ex As Exception
Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION failed: {0}", ex.Message)
End Try
' Create a table
command1.CommandText = _
"IF EXISTS (SELECT * FROM sys.databases " _
& "WHERE name=N'TestSnapshotUpdate') " _
& "DROP TABLE TestSnapshotUpdate"
command1.ExecuteNonQuery()
command1.CommandText = _
"CREATE TABLE TestSnapshotUpdate (ID int primary key, " _
& "CharCol nvarchar(100));"
Try
command1.ExecuteNonQuery()
Console.WriteLine("TestSnapshotUpdate table created.")
Catch ex As Exception
Console.WriteLine("CREATE TABLE failed: {0}", ex.Message)
End Try
' Insert some data
command1.CommandText = _
"INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');" _
& "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');" _
& "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');"
Try
command1.ExecuteNonQuery()
Console.WriteLine("Data inserted TestSnapshotUpdate table.")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
' Begin, but do not complete, a transaction
' using the Snapshot isolation level
Dim transaction1 As SqlTransaction = Nothing
Try
transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot)
command1.CommandText = _
"SELECT * FROM TestSnapshotUpdate WHERE ID " _
& "BETWEEN 1 AND 3"
command1.Transaction = transaction1
command1.ExecuteNonQuery()
Console.WriteLine("Snapshot transaction1 started.")
' Open a second Connection/Transaction to update data
' using ReadCommitted. This transaction should succeed.
Dim connection2 As SqlConnection = New SqlConnection(connectionString)
Using connection2
connection2.Open()
Dim command2 As SqlCommand = connection2.CreateCommand()
command2.CommandText = "UPDATE TestSnapshotUpdate SET " _
& "CharCol=N'New value from Connection2' WHERE ID=1"
Dim transaction2 As SqlTransaction = _
connection2.BeginTransaction(IsolationLevel.ReadCommitted)
command2.Transaction = transaction2
Try
command2.ExecuteNonQuery()
transaction2.Commit()
Console.WriteLine( _
"transaction2 has modified data and committed.")
Catch ex As SqlException
Console.WriteLine(ex.Message)
transaction2.Rollback()
Finally
transaction2.Dispose()
End Try
End Using
' Now try to update a row in Connection1/Transaction1.
' This transaction should fail because Transaction2
' succeeded in modifying the data.
command1.CommandText = _
"UPDATE TestSnapshotUpdate SET CharCol=" _
& "N'New value from Connection1' WHERE ID=1"
command1.Transaction = transaction1
command1.ExecuteNonQuery()
transaction1.Commit()
Console.WriteLine("You should never see this.")
Catch ex As SqlException
Console.WriteLine("Expected failure for transaction1:")
Console.WriteLine(" {0}: {1}", ex.Number, ex.Message)
Finally
transaction1.Dispose()
End Try
End Using
' CLEANUP:
' Turn off Snapshot isolation and delete the table
Dim connection3 As New SqlConnection(connectionString)
Using connection3
connection3.Open()
Dim command3 As SqlCommand = connection3.CreateCommand()
command3.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
Try
command3.ExecuteNonQuery()
Console.WriteLine( _
"Snapshot isolation turned off in AdventureWorks.")
Catch ex As Exception
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
End Try
command3.CommandText = "DROP TABLE TestSnapshotUpdate"
Try
command3.ExecuteNonQuery()
Console.WriteLine("TestSnapshotUpdate table deleted.")
Catch ex As Exception
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
End Try
End Using
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 d’instantanés de longue durée en fournissant des indicateurs de verrou au début de la transaction. L’instruction SELECT suivante 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 verrou UPDLOCK bloque toutes les lignes tentant de mettre à jour les 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 d’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.