Nota
O acesso a esta página requer autorização. Podes tentar iniciar sessão ou mudar de diretório.
O acesso a esta página requer autorização. Podes tentar mudar de diretório.
O isolamento de instantâneo melhora a simultaneidade para aplicativos OLTP.
Compreender o isolamento de instantâneos e o versionamento de linhas
Uma vez ativado o isolamento de snapshots, as versões atualizadas das linhas para cada transação são mantidas em tempdb. Um número exclusivo de sequência de transação identifica cada transação, e estes números exclusivos são registados em cada versão de linha. A transação funciona com as versões de linha mais recentes cujo número de sequência é anterior ao da transação. As versões de linha mais recentes criadas após o início da transação são ignoradas pela transação.
O termo "snapshot" reflete o fato de que todas as consultas na transação veem a mesma versão, ou snapshot, do banco de dados, com base no estado do banco de dados no momento em que a transação começa. Não são adquiridos bloqueios nas linhas ou páginas de dados subjacentes de uma transação de instantâneo, o que permite que outras transações sejam executadas sem serem bloqueadas por uma transação incompleta anterior. As transações que modificam dados não bloqueiam transações que leem dados, e as transações que leem dados não bloqueiam transações que gravam dados, como normalmente fariam no nível de isolamento padrão READ COMMITTED no SQL Server. Esse comportamento de não bloqueio também reduz significativamente a probabilidade de bloqueios para transações complexas.
O isolamento por instantâneo usa um modelo de simultaneidade otimista. Se uma transação de instantâneo tentar confirmar modificações em dados que foram alterados desde o início da transação, a transação será revertida e um erro será gerado. Você pode evitar isso usando dicas UPDLOCK para instruções SELECT que acessam dados a serem modificados. Consulte "Dicas de Bloqueio" nos Livros do SQL Server Online para mais informações.
O isolamento de instantâneo deve ser ativado configurando a opção de base de dados ALLOW_SNAPSHOT_ISOLATION como 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âneo em cada banco de dados que o usa com a instrução Transact-SQL ALTER DATABASE. A este respeito, o isolamento por instantâneo difere dos níveis de isolamento tradicionais de READ COMMITTED, REPEATABLE READ, SERIALIZABLE e READ UNCOMMITTED, que não exigem configuração. As instruções a seguir ativam o isolamento de snapshot e substituem o comportamento padrão READ COMMITTED pelo modo SNAPSHOT.
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
Ao ativar a opção READ_COMMITTED_SNAPSHOT ON, permite-se o acesso a linhas versionadas sob o 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 de instantâneo para cada sessão para acessar linhas versionadas.
Gerir a concorrência com níveis de isolamento
O nível de isolamento sob o qual uma instrução Transact-SQL é executada determina o seu comportamento de bloqueios e versionamento de linhas. Um nível de isolamento tem escopo em toda a conexão e, uma vez definido para uma conexão com a instrução SET TRANSACTION ISOLATION LEVEL, permanece em vigor até que a conexão seja fechada ou outro nível de isolamento seja definido. Quando uma conexão é fechada e retornada ao pool, o nível de isolamento da última instrução SET TRANSACTION ISOLATION LEVEL é mantido. 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 é agrupada.
As consultas individuais emitidas numa conexão podem conter pistas de bloqueio que modificam o isolamento de uma única instrução ou transação, mas não afetam o nível de isolamento da conexão. Os níveis de isolamento ou dicas de bloqueio definidos 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 durante a duração do procedimento armazenado ou chamada de função.
Quatro níveis de isolamento definidos no padrão SQL-92 eram suportados nas versões anteriores do SQL Server:
READ UNCOMMITTED é o nível de isolamento menos restritivo porque ignora bloqueios colocados por outras transações. As transações executadas sob READ UNCOMMITTED podem ler valores de dados modificados que ainda não foram confirmados por outras transações; estas são chamadas de leituras "sujas".
READ COMMITTED é o nível de isolamento padrão para o SQL Server. Ele evita leituras sujas, especificando que as declarações não podem ler valores de dados que foram modificados, mas ainda não validados 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 que READ COMMITTED. Ele engloba READ COMMITTED e, adicionalmente, especifica 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 para READ COMMITTED porque os bloqueios compartilhados nos dados lidos são mantidos durante toda a transação, em vez de serem liberados no final de cada instrução.
SERIALIZABLE é o nível de isolamento mais restritivo, porque bloqueia intervalos inteiros de chaves e mantém os bloqueios até que a transação seja concluída. Engloba LEITURA REPETÍVEL e adiciona a restrição de que outras transações não podem inserir novas linhas nas faixas lidas pela transação até que esta esteja concluída.
Para obter mais informações, consulte o Transaction Locking and Row Versioning Guide.
Extensões do nível de isolamento de snapshots
O SQL Server introduziu extensões para os níveis de isolamento do SQL-92 com a introdução do nível de isolamento SNAPSHOT e uma implementação adicional de READ COMMITTED. O nível de isolamento READ_COMMITTED_SNAPSHOT pode substituir transparentemente o READ COMMITTED em todas as transações.
O isolamento SNAPSHOT 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 linha de dados que existem quando a transação começa. Nenhum bloqueio é colocado nos dados quando eles são lidos, portanto, as transações SNAPSHOT não impedem que outras transações gravem dados. As transações que gravam dados não impedem que as transações de instantâneo leiam dados. Você precisa habilitar o isolamento de instantâneo definindo a opção ALLOW_SNAPSHOT_ISOLATION banco de dados para usá-lo.
A opção do banco de dados READ_COMMITTED_SNAPSHOT determina o comportamento do nível de isolamento padrão READ COMMITTED quando o isolamento de snapshot está habilitado em um banco de dados. Se não especificar explicitamente READ_COMMITTED_SNAPSHOT ON, READ COMMITTED será aplicado a todas as transações implícitas. Isso produz o mesmo comportamento que a configuração READ_COMMITTED_SNAPSHOT OFF (o padrão). Quando READ_COMMITTED_SNAPSHOT OFF está em vigor, o motor de base de dados usa bloqueios partilhados para impor o nível de isolamento padrão. Se definir a opção de base de dados READ_COMMITTED_SNAPSHOT como ON, o motor de base de dados usará o versionamento de linhas e o isolamento por instantâneos como padrão, em vez de usar bloqueios para proteger os dados.
Como funcionam o isolamento de snapshots e o versionamento de linhas
Quando o nível de isolamento SNAPSHOT é habilitado, sempre que uma linha é atualizada, o Mecanismo de Banco de Dados do SQL Server armazena uma cópia da linha original em tempdbe adiciona um número de sequência de transação à linha. A seguir está a sequência de eventos que ocorre:
Uma nova transação é iniciada e é atribuído um número de sequência de transação.
O Motor de Base de Dados lê uma linha dentro da transação e recupera a versão da linha do tempdb cujo número de sequência é o mais próximo e inferior ao 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á na lista de números de sequência de transações das transações não confirmadas ativas quando a transação de instantâneo foi iniciada.
A transação lê a versão da linha do tempdb que estava atualizada no início da transação. Ele não verá novas linhas inseridas após o início da transação porque esses valores de número de sequência serão maiores do que o valor do número de sequência da transação.
A transação atual verá linhas que foram eliminadas após o início da transação, porque haverá uma versão de linha no tempdb com um valor de número de sequência mais baixo.
O efeito final do isolamento de instantâneos é que a transação vê todos os dados como existiam no início da transação, sem respeitar ou aplicar bloqueios nas tabelas subjacentes. Isso pode resultar em melhorias de desempenho em situações em que há contenção.
Uma transação de instantâneo sempre usa um controle de simultaneidade otimista, evitando quaisquer bloqueios que impediriam outras transações de atualizar 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.
Trabalhar com isolamento de snapshots em ADO.NET
O isolamento de instantâneo é suportado em ADO.NET pela classe SqlTransaction. Se um banco de dados tiver sido habilitado para isolamento de instantâneo, mas não estiver configurado para READ_COMMITTED_SNAPSHOT ON, você deverá iniciar uma SqlTransaction usando o valor de enumeração IsolationLevel.Snapshot ao chamar o método BeginTransaction. Este fragmento de código assume que a conexão é um objeto SqlConnection aberto.
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Example
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 no código de produção.
O código liga-se à base de dados de exemplo AdventureWorks no SQL Server e cria uma tabela chamada TestSnapshot , inserindo uma linha de dados. O código usa a instrução ALTER DATABASE Transact-SQL para ativar o isolamento de instantâneos para o banco de dados, mas não define a opção READ_COMMITTED_SNAPSHOT, deixando o comportamento padrão no nível de isolamento READ COMMITTED 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 isolamento SERIALIZABLE para iniciar uma transação de atualização. Isso tem o efeito de bloquear a tabela.
Abre uma segunda ligação e inicia uma segunda transação usando o nível de isolamento SNAPSHOT para ler os dados na tabela TestSnapshot . Como o isolamento de instantâneo está ativado, esta transação pode ler os dados que existiam antes do sqlTransaction1 começar.
Ele abre uma terceira conexão e inicia uma transação usando o nível de isolamento READ COMMITTED para 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 expira. O mesmo resultado ocorreria se os níveis de isolamento REPEATABLE READ e SERIALIZABLE fossem 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 nível de isolamento READ UNCOMMITTED que executa uma leitura suja do valor não confirmado em sqlTransaction1. Esse valor pode nunca existir no banco de dados se a primeira transação não for confirmada.
Reverte a primeira transação e limpa eliminando a tabela TestSnapshot e desativando o isolamento de snapshots na base de dados AdventureWorks .
Observação
Os exemplos a seguir usam a mesma cadeia de conexão com o pool de conexões desativado. Se uma conexão estiver agrupada, a redefinição de seu nível de isolamento não redefinirá o nível de isolamento no servidor. Como resultado, as conexões subsequentes que usam a mesma conexão interna agrupada começam com seus níveis de isolamento definidos como os 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.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
// 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!");
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file, using the
// System.Configuration.ConfigurationSettings.AppSettings property
return "Data Source=localhost;Initial Catalog=AdventureWorks;"
+ "Integrated Security=SSPI";
}
}
Example
O exemplo a seguir demonstra o comportamento do isolamento de instantâneo quando os dados estão sendo modificados. O código executa as seguintes ações:
Liga-se à base de dados de exemplo AdventureWorks e ativa o isolamento SNAPSHOT.
Cria uma tabela chamada TestSnapshotUpdate e insere três linhas de dados de exemplo.
Inicia, mas não conclui, sqlTransaction1 usando o isolamento SNAPSHOT. Três linhas de dados são selecionadas na transação.
Cria um segundo SqlConnection para AdventureWorks e cria uma segunda transação usando o nível de isolamento READ COMMITTED que atualiza um valor numa das linhas selecionadas em sqlTransaction1.
Confirma sqlTransaction2.
Retorna a sqlTransaction1 e tenta atualizar a mesma linha que sqlTransaction1 já confirmou. O erro 3960 é gerado e sqlTransaction1 é revertido automaticamente. Os SqlException.Number e SqlException.Message são exibidos na janela Console.
Executa código de limpeza para desativar o isolamento de snapshots no AdventureWorks e eliminar a tabela TestSnapshotUpdate .
using Microsoft.Data.SqlClient; using System.Data.Common; class Program { static void Main() { // 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); } } Console.WriteLine("Done"); Console.ReadLine(); } static private string GetConnectionString() { // To avoid storing the connection string in your code, // you can retrieve it from a configuration file, using the // System.Configuration.ConfigurationSettings.AppSettings property return "Data Source=(local);Initial Catalog=AdventureWorks;" + "Integrated Security=SSPI;Pooling=false"; } }
Utilização de dicas de bloqueio com isolamento de snapshots
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âneos de longa duração fornecendo sugestões de bloqueio no início da transação. A instrução SELECT a seguir usa a dica UPDLOCK para bloquear as linhas selecionadas:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
O uso da dica de bloqueio UPDLOCK bloqueia todas as linhas que tentam atualizar as linhas antes que a primeira transação seja concluída. Isso garante que as linhas selecionadas não tenham conflitos quando forem atualizadas posteriormente na transação. Veja "Dicas de Bloqueio" nos livros do SQL Server Online.
Se a sua aplicação tiver muitos conflitos, o isolamento por instantâneo pode não ser a melhor escolha. As dicas só devem ser usadas quando realmente necessário. O seu aplicativo não deve ser projetado de forma a depender constantemente de sugestões de bloqueio para funcionar.