Idioma
Observação
O acesso a essa página exige autorização. Você pode tentar entrar ou alterar diretórios.
O acesso a essa página exige autorização. Você pode tentar alterar os diretórios.
O isolamento de instantâneo aprimora a simultaneidade para aplicativos OLTP.
Entendendo o isolamento de instantâneo e o controle de versão de linha
Depois que o isolamento do instantâneo for habilitado, as versões de linha atualizadas para cada transação deverão ser mantidas. Antes do SQL Server 2019, essas versões eram armazenadas em tempdb. O SQL Server 2019 apresenta um novo recurso, ADR (Recuperação Acelerada de Banco de Dados), que exige seu próprio conjunto de versões de linha. Portanto, a partir do SQL Server 2019, se o ADR não estiver habilitado, as versões de linha são mantidas em tempdb como sempre. Se o ADR estiver habilitado, todas as versões de linha, tanto relacionadas ao isolamento por snapshot quanto ao próprio ADR, são mantidas no Repositório de Versões Persistentes (PVS) da ADR, que está localizado no banco de dados do usuário em um grupo de arquivos especificado pelo usuário. Um número de sequência de transação exclusivo identifica cada transação. Esses números exclusivos são registrados para cada versão de linha. A transação funciona com as versões de linha mais recentes que têm um número de sequência anterior ao número de sequência da transação. As versões de linha mais recentes criadas depois que a transação foi iniciada são ignoradas pela transação.
O termo "instantâneo" reflete o fato de que todas as consultas na transação veem a mesma versão (ou instantâneo) do banco de dados, com base no estado do banco de dados no momento em que a transação é iniciada. Nenhum bloqueio é adquirido nas linhas de dados subjacentes ou páginas de dados em uma transação de instantâneo, o que permite que outras transações sejam executadas sem serem bloqueadas por uma transação anterior não concluída. Transações que modificam dados não bloqueiam transações que leem dados, e transações que leem dados não bloqueiam transações que gravam dados, como normalmente ocorreria no nível de isolamento padrão READ COMMITTED no SQL Server. Esse comportamento de não bloqueio também reduz consideravelmente a probabilidade de deadlocks para transações complexas.
O isolamento de instantâneo usa um modelo de simultaneidade otimista. Se uma transação de instantâneo tentar confirmar modificações a dados alterados desde o início da transação, a transação será revertida e um erro será gerado. Você pode evitar isso usando UPDLOCK hints para SELECT comandos SQL que acessam dados a serem modificados. Para obter mais informações, veja Dicas (Transact-SQL).
O isolamento de instantâneo deve ser ativado definindo a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION ON antes de ser usado em transações. Isso ativa o mecanismo para armazenar versões de linha no banco de dados temporário (tempdb). Você deve habilitar o isolamento de instantâneos em cada banco de dados que o utiliza com a instrução Transact-SQL ALTER DATABASE. Nesse aspecto, o isolamento do instantâneo difere dos níveis de isolamento tradicionais de READ COMMITTED, REPEATABLE READ, SERIALIZABLE e READ UNCOMMITTED, que não exigem nenhuma configuração. As instruções a seguir ativam transações SNAPSHOT e leituras READ COMMITTED com controle de versão por linha:
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
Definir a opção READ_COMMITTED_SNAPSHOT ON permite o acesso a linhas com controle de versão no nível de isolamento padrão READ COMMITTED . Se a opção READ_COMMITTED_SNAPSHOT estiver definida como OFF, você deverá definir explicitamente o nível de isolamento Snapshot para cada sessão para acessar linhas com versão.
Gerenciando simultaneidade com níveis de isolamento
O nível de isolamento sob o qual uma instrução Transact-SQL é executada determina o comportamento de bloqueio e de controle de versão de linha dessa instrução. Um nível de isolamento tem escopo em toda a conexão e, uma vez definido para uma conexão com a SET TRANSACTION ISOLATION LEVEL instrução, ele permanece em vigor até que a conexão seja fechada ou outro nível de isolamento seja definido. Quando uma conexão é fechada e devolvida ao pool, o nível de isolamento da última instrução SET TRANSACTION ISOLATION LEVEL é preservado. As conexões subsequentes que reutilizam uma conexão em pool usam o nível de isolamento que estava em vigor no momento em que a conexão foi disponibilizada no pool.
As consultas individuais emitidas em uma conexão podem conter sugestões de bloqueio que modificam o isolamento para essa instrução ou transação específica, mas não afetam o nível de isolamento da conexão. Os níveis de isolamento ou as dicas de bloqueio definidas em procedimentos armazenados ou funções não alteram o nível de isolamento da conexão que os chama e estão em vigor apenas pela duração do procedimento armazenado ou da chamada de função.
Quatro níveis de isolamento definidos no padrão SQL-92 são compatíveis com versões anteriores do SQL Server:
READ UNCOMMITTEDé o nível de isolamento menos restritivo porque ignora os bloqueios colocados por outras transações. As transações em execução emREAD UNCOMMITTEDpodem ler valores de dados modificados que ainda não foram confirmados por outras transações; isso é chamado de leituras "sujas".READ COMMITTEDé o nível de isolamento padrão para SQL Server. Ele impede a realização de leituras sujas especificando que as instruções não podem ler valores de dados que foram modificados, mas ainda não confirmados por outras transações. Outras transações ainda podem modificar, inserir ou excluir dados entre execuções de instruções individuais dentro da transação atual, resultando em leituras não repetíveis ou dados fantasmas.REPEATABLE READé um nível de isolamento mais restritivo do queREAD COMMITTED. Ele abrangeREAD COMMITTEDe especifica adicionalmente que nenhuma outra transação pode modificar ou excluir dados que foram lidos pela transação atual até que a transação atual seja confirmada. A simultaneidade é menor do que paraREAD COMMITTEDporque os bloqueios compartilhados nos dados de leitura são mantidos durante a transação em vez de serem liberados no final de cada instrução.SERIALIZABLEé o nível de isolamento mais restritivo, pois bloqueia intervalos inteiros de chaves e mantém os bloqueios até que a transação seja concluída. Ele abrangeREPEATABLE READe adiciona a restrição de que outras transações não podem inserir novas linhas em faixas que foram lidas pela transação até que a transação seja concluída.
Para obter mais informações, confira o Guia de controle de versão de linha e bloqueio de transação.
Extensões de nível de isolamento de instantâneo
SQL Server introduziu extensões aos níveis de isolamento do SQL-92 com a introdução do nível de SNAPSHOT isolamento e uma implementação adicional de READ COMMITTED.
SNAPSHOTO isolamento especifica que os dados lidos em uma transação nunca refletirão as alterações feitas por outras transações simultâneas. A transação usa as versões de linhas de dados que existem quando a transação é iniciada. Nenhum bloqueio é colocado nos dados quando eles são lidos, portanto, as transaçõesSNAPSHOTnão impedem que outras transações gravem dados. Transações que escrevem dados não impedem que transações de snapshot leiam os dados. Você precisa habilitar o isolamento de instantâneo definindo a opção de banco de dadosALLOW_SNAPSHOT_ISOLATIONpara usá-lo.A opção
READ_COMMITTED_SNAPSHOTde banco de dados controla comoREAD COMMITTEDlê os dados. Com essa opçãoOFF, o padrão em SQL Server, o Mecanismo de Banco de Dados usa bloqueios compartilhados para proteger as leituras. Com essa opçãoON,READ COMMITTEDusa versões de linha no nível da instrução em vez de bloqueios compartilhados para proteger leituras.
Como funcionam o isolamento de instantâneo e o controle de versão de linha
Quando o nível de isolamento SNAPSHOT está habilitado, sempre que uma linha é atualizada, o Mecanismo de Banco de Dados do SQL Server armazena uma cópia da linha original em tempdb e adiciona um número de sequência de transações à linha. A seguinte sequência de eventos ocorre:
Uma nova transação é iniciada e recebe um número de sequência de transação.
O Mecanismo de Banco de Dados lê uma linha dentro da transação e recupera a versão da linha de
tempdbcujo número de sequência é o mais próximo e menor que o número de sequência da transação.O mecanismo de banco de dados verifica se o número de sequência da transação não está presente na lista de números de sequência das transações não confirmadas que estavam ativas quando a transação instantânea foi iniciada.
A transação lê a versão da linha de
tempdbque estava atualizada no início da transação. Ela não verá novas linhas inseridas depois que a transação foi iniciada, porque esses valores de número de sequência serão maiores que o valor do número de sequência da transação.A transação atual verá linhas que foram excluídas após o início da transação, porque haverá uma versão da linha em
tempdbcom um valor de número de sequência menor.
O resultado efetivo do isolamento de instantâneo é que a transação vê todos os dados como eles existiam no início da transação, sem respeitar nem colocar nenhum bloqueio nas tabelas subjacentes. Isso pode resultar em aprimoramentos de desempenho em situações em que há contenção.
Uma transação de instantâneo sempre usa o controle de simultaneidade otimista, transacionando todos os bloqueios que impediriam que outras transações atualizassem as linhas. Se uma transação de instantâneo tentar confirmar uma atualização para uma linha que foi alterada após o início da transação, a transação será revertida e um erro será gerado.
Trabalhando com isolamento de instantâneo no ADO.NET
Para iniciar uma transação com isolamento de instantâneo, habilite ALLOW_SNAPSHOT_ISOLATION para o banco de dados e passe Snapshot para BeginTransaction. A READ_COMMITTED_SNAPSHOT opção de banco de dados controla as leituras com versão de linha no nível de isolamento leitura confirmada; ela não seleciona o isolamento de instantâneo/snapshot para uma transação. O fragmento a seguir pressupõe que connection seja uma instância aberta da SqlConnection classe.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Exemplo
O exemplo a seguir demonstra como os diferentes níveis de isolamento se comportam ao tentar acessar dados bloqueados e não se destina a ser usado em código de produção.
O código se conecta ao AdventureWorks banco de dados de exemplo no SQL Server e cria uma tabela nomeada TestSnapshot e insere uma linha de dados. O código usa a ALTER DATABASE instrução Transact-SQL para ativar o isolamento de instantâneo para o banco de dados, mas não define a opção READ_COMMITTED_SNAPSHOT , deixando o comportamento padrão READ COMMITTED no nível de isolamento em vigor. Em seguida, o código executa as seguintes ações:
Ele começa, mas não conclui,
sqlTransaction1, que usa o nível de isolamentoSERIALIZABLEpara iniciar uma transação de atualização. Isso tem o efeito de bloquear a tabela.Ele abre uma segunda conexão e inicia uma segunda transação usando o
SNAPSHOTnível de isolamento para ler os dados naTestSnapshottabela. Como o isolamento de instantâneo está habilitado, essa transação pode ler os dados que existiam antes desqlTransaction1ser iniciada.Ela abre uma terceira conexão e inicia uma transação usando o nível de isolamento
READ COMMITTEDpara tentar ler os dados na tabela. Nesse caso, o código não pode ler os dados porque não pode ler além dos bloqueios colocados na tabela na primeira transação e atinge o tempo limite. O mesmo resultado ocorreria se os níveis de isolamentoSERIALIZABLEeREPEATABLE READfossem usados porque esses níveis de isolamento também não podem ler além dos bloqueios colocados na primeira transação.Ele abre uma quarta conexão e inicia uma transação usando o
READ UNCOMMITTEDnível de isolamento, que executa uma leitura suja do valor não confirmado emsqlTransaction1. Esse valor poderá nunca realmente existir no banco de dados se a primeira transação não for confirmada.Ela reverte a primeira transação e limpa excluindo a tabela
TestSnapshote desativando o isolamento de instantâneo para o banco de dadosAdventureWorks.
Observação
Os exemplos a seguir usam a mesma cadeia de conexão com o pool de conexão desativado. Se uma conexão for colocada em pool, redefinir o nível de isolamento dela não redefinirá o nível de isolamento no servidor. Como resultado, as conexões subsequentes que usam a mesma conexão interna em pool começam com seus respectivos níveis de isolamento definidos para o da conexão em pool. Uma alternativa para desativar o pool de conexões é definir o nível de isolamento explicitamente para cada conexão.
// 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
Exemplo
O exemplo a seguir demonstra o comportamento do isolamento de instantâneos quando os dados estão sendo modificados. O código executa as seguintes ações:
Conecta-se ao
SNAPSHOTbanco de dados de exemplo e habilita oAdventureWorksisolamento.Cria uma tabela nomeada
TestSnapshotUpdatee insere três linhas de dados de exemplo.Inicia, mas não conclui,
sqlTransaction1usando o isolamentoSNAPSHOT. Três linhas de dados são selecionadas na transação.Cria um segundo
SqlConnectionparaAdventureWorkse cria uma segunda transação usando o nível de isolamentoREAD COMMITTEDque atualiza um valor em uma das linhas selecionadas emsqlTransaction1.Confirma
sqlTransaction2.Retorna para
sqlTransaction1e tenta atualizar a mesma linha quesqlTransaction1já confirmou. O erro 3960 é gerado esqlTransaction1é revertido automaticamente. OsSqlException.NumbereSqlException.Messagesão exibidos na janela Console.Executa o código de limpeza para desativar o isolamento de snapshot em
AdventureWorkse excluir a tabelaTestSnapshotUpdate.
// 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
Usando dicas de bloqueio com isolamento de instantâneo
No exemplo anterior, a primeira transação seleciona dados e uma segunda transação atualiza os dados antes que a primeira transação possa ser concluída, causando um conflito de atualização quando a primeira transação tenta atualizar a mesma linha. Você pode reduzir a chance de conflitos de atualização em transações de instantâneo de execução longa pelo fornecimento de dicas de bloqueio no início da transação. A instrução a seguir SELECT usa a UPDLOCK dica para bloquear as linhas selecionadas:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
O uso da dica de bloqueio UPDLOCK bloqueia quaisquer linhas que tentem atualizar as linhas antes da conclusão da primeira transação. Isso garante que as linhas selecionadas não tenham conflitos quando forem atualizadas posteriormente na transação. Para obter mais informações, veja Dicas (Transact-SQL).
Se o aplicativo tem muitos conflitos, o isolamento de instantâneo pode não ser a melhor opção. As dicas só devem ser usadas quando realmente necessário. Seu aplicativo não deve ser projetado de forma que dependa constantemente de sugestões de bloqueio para sua operação.