Isolement de capture instantanée dans SQL Server (ADO.NET)
SQL Server 2005 introduit un nouveau niveau d'isolation d'instantané afin d'améliorer l'accès simultané pour des applications OLTP. Dans les versions antérieures de SQL Server, l'accès simultané était basé uniquement sur le verrouillage, qui entraînait le blocage de problèmes pour certaines applications. En revanche, l'isolation d'instantané dépend des améliorations apportées à la fonction de gestion des versions de ligne et est destinée à améliorer les performances en évitant les scénarios de blocage de lecteur-writer.
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 sont maintenues dans tempdb. 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 opère avec les versions de ligne les plus récentes dont le numéro de séquence est antérieur au numéro de séquence de la transaction. Les versions de ligne plus récentes, créées après que la transaction a commencé 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 celles qui lisent des données ne bloquent pas celles qui en écrivent, comme c'est normalement le cas avec le niveau d'isolation READ COMMITTED par défaut dans SQL Server. Ce comportement non bloquant réduit également sensiblement la probabilité d'interblocages pour les transactions complexes.
L'isolation d'instantané utilise un modèle d'accès simultané optimiste. Si une transaction d'instantané tente de valider des modifications apportées à des données ayant changé depuis le début de la transaction, la transaction est annulée et une erreur est déclenchée. Vous pouvez éviter cela en utilisant les indicateurs UPDLOCK pour les instructions SELECT qui accèdent à des données à modifier. Pour plus d'informations, voir « Locking Hints » dans la documentation en ligne de SQL Server.
L'isolation d'instantané doit être activée en définissant l'option de base de données ALLOW_SNAPSHOT_ISOLATION ON avant de l'utiliser 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 de niveaux d'isolation traditionnels des instructions READ COMMITTED, REPEATABLE READ, SERIALIZABLE et READ UNCOMMITTED qui ne requièrent 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 à des lignes dont les versions sont gérées au niveau d'isolation READ COMMITTED par défaut. Si l'option READ_COMMITTED_SNAPSHOT a la valeur OFF, vous devez explicitement définir le niveau d'isolation d'instantané pour chaque session afin d'accéder aux lignes dont les versions sont gérées.
Gestion de l'accès simultané avec des niveaux d'isolation
Le niveau d'isolation auquel une instruction Transact-SQL s'exécute détermine son comportement de verrouillage et de gestion des versions de ligne. Un niveau d'isolation a une portée qui s'étend à une connexion et, une fois qu'il est 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'isolement de la dernière instruction SET TRANSACTION ISOLATION LEVEL est conservé. Des connexions ultérieures réutilisant une connexion regroupée utilisent le niveau d'isolement qui s'appliquait au moment où la connexion a été mise en pool.
Des requêtes individuelles émises au sein d'une connexion peuvent contenir des indicateurs de verrou qui modifient l'isolement pour une instruction ou une transaction unique mais n'affectent pas le niveau d'isolement de la connexion. Les niveaux d'isolation ou indicateurs de verrou définis dans les procédures ou les fonctions stockées 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.
Il existe quatre niveaux d'isolation définis dans la norme SQL-92, qui étaient pris en charge dans les versions précédentes 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 au niveau d'isolation READ UNCOMMITTED peuvent lire des valeurs de données modifiées non encore validées par d'autres transactions ; ces dernières sont appelées lectures modifiées (« dirty »).
READ COMMITTED est le niveau d'isolation par défaut pour SQL Server. Il empêche les lectures modifiées en spécifiant que les instructions ne peuvent pas lire de valeurs de données qui ont été modifiées mais pas encore validées par d'autres transactions. D'autres transactions peuvent encore modifier, insérer ou supprimer des données entre les exécutions d'instructions individuelles à l'intérieur de la transaction en cours, ce qui entraîne des lectures qui ne peuvent pas être répétées ou données fantômes.
REPEATABLE READ 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 lues par la transaction en cours tant que celle-ci n'est pas validée. L'accès simultané est inférieur à celui obtenu avec READ COMMITTED parce que les blocs 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 parce qu'il verrouille des plages entières de touches et maintient les verrous jusqu'à ce que la transaction soit achevée. Il englobe REPEATABLE READ et ajoute la restriction en vertu de laquelle d'autres transactions ne peuvent pas insérer de nouvelles lignes dans les plages qui ont été lues par la transaction tant que celle-ci n'est pas achevée.
Pour plus d'informations, voir « Isolation Levels » dans la documentation en ligne de SQL Server.
Extensions de niveau d'isolation d'instantané
SQL Server 2005 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 nouveau niveau d'isolation READ_COMMITTED_SNAPSHOT peut remplacer de façon transparente READ COMMITTED pour toutes les transactions.
L'isolation SNAPSHOT spécifie que les données lues dans une transaction ne refléteront jamais les modifications apportées par d'autres transactions simultanées. Au démarrage, la transaction utilise les versions de ligne de données existantes. Aucun verrou n'est appliqué aux données lors de leur lecture, de sorte que les transactions SNAPSHOT n'empêchent pas d'autres transactions d'écrire des données. Les transactions qui écrivent des données n'empêchent pas les transactions d'instantané de lire des données. Vous devez activer une isolation d'instantané en définissant l'option de base de données ALLOW_SNAPSHOT_ISOLATION de façon à ce qu'elle l'utilise.
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 définir READ_COMMITTED_SNAPSHOT OFF (par défaut). Lorsque READ_COMMITTED_SNAPSHOT OFF est effectif, le moteur de base de données utilise des verrous partagés pour appliquer le niveau d'isolation par défaut. Si vous définissez l'option de base de données READ_COMMITTED_SNAPSHOT sur ON, le moteur de base de données utilise par défaut la gestion de versions de ligne et l'isolation d'instantané, 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
Lorsque 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. La section suivante présente la séquence des événements qui se produit :
Une nouvelle transaction est initiée, qui reçoit un numéro de séquence de transaction.
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émarrage de la transaction. Elle ne voit pas de nouvelles lignes insérées après le démarrage de la transaction parce que ces valeurs de numéro de séquence sont supérieures à la valeur du numéro de séquence de la transaction.
La transaction en cours voit les lignes qui ont été supprimées après le début de la transaction parce qu'il y aura une version de ligne dans tempdb dont la valeur de numéro de séquence est inférieure.
L'effet net d'une 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 aux tables sous-jacentes. Cela peut améliorer les performances dans des situations de conflit.
Une transaction d'instantané utilise toujours un contrôle d'accès simultané optimiste, rejetant tous les verrous qui empêcheraient d'autres transactions de mettre à jour les lignes. Si une transaction d'instantané tente de valider la mise à jour d'une ligne modifiée après le début de la transaction, la transaction est annulée et une erreur est déclenchée.
Utilisation de l'isolation d'instantané dans ADO.NET
L'isolation d'instantané est prise en charge dans ADO.NET par 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 initier un SqlTransaction en utilisant la valeur d'énumération IsolationLevel.Snapshot lors de l'appel de la méthode BeginTransaction. Ce fragment de code est basé sur l'hypothèse 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 à des données verrouillées et n'est pas destiné à être utilisé dans un 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 ALTER DATABASE Transact-SQL pour activer l'isolation d'instantané pour la base de données mais ne définit pas l'option READ_COMMITTED_SNAPSHOT, en conservant le comportement de niveau d'isolation READ COMMITTED par défaut. Le code effectue ensuite les actions suivantes :
Il commence à exécuter, sans l'achever, 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 initie une seconde transaction en utilisant le niveau d'isolation SNAPSHOT pour lire les données dans la table TestSnapshot. Comme 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 parce qu'il ne peut pas lire au-delà des verrous appliqués à la table dans la première transaction et le délai de temporisation expire. Le même résultat est obtenu si les niveaux d'isolation REPEATABLE READ et SERIALIZABLE ont été utilisés parce que ces niveaux ne peuvent pas non plus lire au-delà des verrous appliqués à la première transaction.
Il ouvre une quatrième connexion et initie une transaction à l'aide du niveau d'isolation READ UNCOMMITTED qui effectue une lecture modifiée de la valeur non validée dans sqlTransaction1. Il se peut que cette valeur n'existe en réalité jamais 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.
.gif "Remarque") Remarque |
|---|
Les exemples ci-dessous utilisent la même chaîne de connexion avec la mise en pool des connexions désactivée.Si une connexion est mise en pool, la réinitialisation de son niveau d'isolement ne réinitialise pas le niveau d'isolement sur le serveur.Par conséquent, les connexions suivantes qui utilisent la même connexion interne regroupée démarrent avec leurs niveaux d'isolement définis sur celui de la connexion regroupée.Une alternative à la désactivation de la mise en pool des connexions consiste à définir le niveau d'isolement de manière explicite pour chaque connexion. |
' 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
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
string connectionString = GetConnectionString();
using (SqlConnection connection1 = new SqlConnection(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 SqlConnection(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).ToString()
+ "," + reader2.GetValue(1).ToString());
}
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 SqlConnection(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 SqlConnection(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).ToString()
+ "," + reader4.GetValue(1).ToString());
}
transaction4.Commit();
}
// Roll back the first transaction
transaction1.Rollback();
}
// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new SqlConnection(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!");
Exemple
L'exemple suivant illustre le comportement d'une isolation d'instantané en cas de modification de données. Le code exécute les actions suivantes :
Établit une connexion avec l'exemple de base de données AdventureWorks et active l'isolation SNAPSHOT.
Crée une table nommée TestSnapshotUpdate et insère trois lignes d'exemples de données.
Démarre, sans l'achever, sqlTransaction1 en utilisant une isolation SNAPSHOT. Trois lignes de données sont sélectionnées dans la transaction.
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 ligne déjà validée par sqlTransaction1. Une erreur 3960 est déclenchée et sqlTransaction1 est automatiquement annulé. SqlException.Number et SqlException.Message s'affichent dans la fenêtre de console.
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;.
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
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
string connectionString = GetConnectionString();
using (SqlConnection connection1 = new SqlConnection(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 = null;
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 SqlConnection(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 SqlConnection(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);
}
}
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 seconde transaction met à jour les données avant que la première transaction puisse se terminer, entraînant un conflit de mise à jour lorsque la première transaction tente de mettre à jour la même ligne. Vous pouvez réduire la probabilité de conflits de mise à jour dans les transactions d'instantané 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 sont exemptes de conflits lors de leur mise à jour ultérieurement dans la transaction. Voir « Locking Hints » dans la documentation en ligne de SQL Server.
Si votre application rencontre de nombreux conflits, une isolation d'instantané n'est peut-être pas le meilleur choix. N'utilisez des indicateurs que si c'est réellement nécessaire. Votre application ne doit pas être conçue de façon à ce que son fonctionnement dépende constamment des indicateurs de verrou.