IsolationLevel Enumeração

  • Referência

Definição

Namespace:
System.Data
Assembly:
System.Data.Common.dll
Assembly:
System.Data.dll
Assembly:
netstandard.dll

Especifica o comportamento de bloqueio de transação para a conexão.

Essa enumeração dá suporte a uma combinação bit a bit dos valores de membro.

public enum class IsolationLevel
public enum IsolationLevel
[System.Flags]
public enum IsolationLevel
type IsolationLevel = 
[<System.Flags>]
type IsolationLevel = 
Public Enum IsolationLevel
Herança
Object
ValueType
Enum
IsolationLevel
Atributos
FlagsAttribute

Campos

Chaos 16

As alterações pendentes de transações com maior isolamento não podem ser substituídas.
ReadCommitted 4096

Bloqueios compartilhados são mantidos durante a leitura dos dados para impedir leituras sujas, mas os dados podem ser alterados antes do término da transação, resultando em leituras não repetíveis ou em dados fantasmas.
ReadUncommitted 256

Uma leitura suja é possível, o que significa que nenhum bloqueio compartilhado é emitido e nenhum bloqueio exclusivo é respeitado.
RepeatableRead 65536

Os bloqueios são colocados em todos os dados usados em uma consulta, impedindo que outros usuários atualizem os dados. Evita leituras não repetíveis, mas linhas fantasma ainda são possíveis.
Serializable 1048576

Um bloqueio de intervalos é colocado no DataSet, impedindo que outros usuários atualizem ou insiram linhas no conjunto de dados até que a transação seja concluída.
Snapshot 16777216

Reduz o bloqueio armazenando uma versão dos dados que um aplicativo pode ler enquanto outro está modificando os mesmos dados. Indica que em uma transação não é possível ver as alterações feitas em outras transações, mesmo que a consulta seja repetida.
Unspecified -1

Um nível de isolamento diferente daquele especificado está sendo usado, mas não é possível determinar o nível.

Exemplos

Este aplicativo demonstra como usar IsolationLevel no DbTransaction. O exemplo demonstrará quais dos seguintes comportamentos são permitidos nos diferentes níveis de isolamento:

  • Leituras sujas.

  • Leituras não repetíveis.

  • Fantasmas.

Esse aplicativo será executado nos seguintes níveis de isolamento:

  • ReadUncommitted

  • ReadCommitted

  • RepeatableRead

  • Serializável

  • Instantâneo

A classe PhantomReadThreads demonstra se a transação específica permite o comportamento de Leitura Fantasma. Se a transação permitir o comportamento, os threads funcionarão na seguinte ordem:

  • No primeiro thread, selecione os produtos (Todos).

  • No segundo thread, insira um novo produto.

  • Confirme a transação no segundo thread.

  • Selecione os produtos novamente.

  • Confirme a transação no primeiro thread.

Se a transação permitir o comportamento, as duas operações Selecionar obterão os resultados diferentes.

A classe NonrepeatableReadThreads demonstra se a transação específica permite o comportamento de leitura não executável. Se a transação permitir o comportamento, os threads funcionarão na seguinte ordem:

  • No primeiro thread, selecione o produto(ProductId=1).

  • No segundo thread, atualize o valor quantity(ProductId=1).

  • Confirme a transação no segundo thread.

  • Selecione o produto novamente.

  • Confirme a transação no primeiro thread.

Se a transação permitir o comportamento, as duas operações Selecionar obterão os resultados diferentes.

A classe ExchangeValuesThreads demonstra a diferença entre a transação Serializable e Snapshot. Para a transação serializável, os threads funcionarão na seguinte ordem:

  • No primeiro thread, obtenha o Preço do produto (ProductId=2) e armazene na variável .

  • No primeiro thread, atualize o Preço do produto(ProductId=1) com o preço do produto(ProductId=2).

  • Confirme a transação no primeiro thread.

  • No segundo thread, obtenha o Preço do produto (ProductId=1) e armazene na variável .

  • No segundo thread, atualize o Preço do produto(ProductId=2) com o preço do produto(ProductId=1).

  • Confirme a transação no segundo thread.

Agora, os valores de Price(ProductId=1 e ProductId=2) são iguais ao preço original do produto(ProductId=2).

Para a transação de instantâneo, os threads funcionarão na seguinte ordem:

  • No primeiro thread, obtenha o Preço do produto (ProductId=2) e armazene na variável ;

  • No primeiro thread, atualize o Preço do produto(ProductId=1) com o preço do produto(ProductId=2).

  • No segundo thread, obtenha o Preço do produto (ProductId=1) do instantâneo e armazene na variável .

  • No segundo thread, atualize o Preço do produto(ProductId=2) com o preço do produto(ProductId=1).

  • Confirme a transação no segundo thread.

  • Confirme a transação no primeiro thread.

Agora, troque o Preço dos produtos(ProductId=1 e ProductId=2).

A classe DirtyReadThreads demonstra se a transação específica permite o comportamento de Leitura Suja. Se a transação permitir o comportamento, os threads funcionarão na seguinte ordem:

  • No primeiro thread, inicie uma transação e adicione o valor quantity(ProductId=1).

  • No segundo thread, leia o valor Quantity e adicione o valor novamente.

  • Confirme a transação no segundo thread.

  • Reverta a transação no primeiro thread.

Se a transação permitir o comportamento, o valor Quantity será adicionado duas vezes.

using System;
using System.Data.SqlClient;
using System.Data;
using System.Threading;
using System.Threading.Tasks;

namespace CSDataIsolationLevel {

   // Use the delegate to call the different threads.
   public delegate void AsyncAccessDatabase(String connString, IsolationLevel level);

   static class DirtyReadThreads {
      public static void DirtyReadFirstThread(String connStrig, IsolationLevel level) {
         Console.WriteLine("Begin the DirtyReadFirstThread.....");

         using (SqlConnection conn = new SqlConnection(connStrig)) {
            String cmdText = @"Use DbDataIsolationLevel;

                    Update dbo.Products set Quantity=Quantity+100 where ProductId=1;
                    WaitFor Delay '00:00:06';";

            conn.Open();

            using (SqlTransaction tran = conn.BeginTransaction(level, "DirtyReadFirst")) {
               using (SqlCommand command = new SqlCommand(cmdText, conn)) {
                  command.Transaction = tran;
                  command.ExecuteNonQuery();
               }

               if (tran != null)
                  tran.Rollback();
            }
         }

         Console.WriteLine("Exit from the DirtyReadFirstThread.....");
      }

      public static void DirtyReadSecondThread(String connStrig, IsolationLevel level) {
         Console.WriteLine("Begin the DirtyReadSecondThread.....");

         using (SqlConnection conn = new SqlConnection(connStrig)) {
            String cmdText = @"Use DbDataIsolationLevel;

                    WaitFor Delay '00:00:03';

                    Declare @qty int;
                    select @qty=Quantity from dbo.Products where ProductId=1;

                    Update dbo.Products set Quantity=@qty+100 where ProductId=1;";

            conn.Open();

            using (SqlTransaction tran = conn.BeginTransaction(level, "DirtyReadSecond")) {
               using (SqlCommand command = new SqlCommand(cmdText, conn)) {
                  command.Transaction = tran;
                  command.ExecuteNonQuery();
               }
               tran.Commit();
            }
         }

         Console.WriteLine("Exit from the DirtyReadSecondThread.....");
      }
   }

   static class NonrepeatableReadThreads {
      public static void NonrepeatableReadFirstThread(String connStrig, IsolationLevel level) {
         Console.WriteLine("Begin the NonrepeatableReadFirstThread.....");

         using (SqlConnection conn = new SqlConnection(connStrig)) {
            String cmdText = @"Use DbDataIsolationLevel;

                    Select ProductId,ProductName,Quantity,Price
                    from dbo.Products
                    where ProductId=1

                    WaitFor Delay '00:00:06';

                    Select ProductId,ProductName,Quantity,Price
                    from dbo.Products
                    where ProductId=1";

            conn.Open();

            using (SqlTransaction tran = conn.BeginTransaction(level, "NonrepeatableReadFirst")) {
               using (SqlCommand command = new SqlCommand(cmdText, conn)) {
                  command.Transaction = tran;

                  using (SqlDataReader reader = command.ExecuteReader()) {
                     Boolean isFirstReader = true;
                     do {
                        Console.WriteLine("It's the result of {0} read:", isFirstReader ? "first" : "second");
                        TransactionIsolationLevels.DisplayData(reader);
                        isFirstReader = !isFirstReader;
                     } while (reader.NextResult() && !isFirstReader);
                  }
               }

               tran.Commit();
            }
         }

         Console.WriteLine("Exit from the NonrepeatableReadFirstThread.....");
      }

      public static void NonrepeatableReadSecondThread(String connStrig, IsolationLevel level) {
         Console.WriteLine("Begin the NonrepeatableReadSecondThread.....");

         using (SqlConnection conn = new SqlConnection(connStrig)) {
            String cmdText = @"Use DbDataIsolationLevel;

                    WaitFor Delay '00:00:03';

                    Update dbo.Products set Quantity=Quantity+100 where ProductId=1;";

            conn.Open();

            using (SqlTransaction tran = conn.BeginTransaction(level, "NonrepeatableReadSecond")) {
               using (SqlCommand command = new SqlCommand(cmdText, conn)) {
                  command.Transaction = tran;
                  command.ExecuteNonQuery();
               }
               tran.Commit();
            }
         }

         Console.WriteLine("Exit from the NonrepeatableReadSecondThread.....");
      }
   }

   static class PhantomReadThreads {
      public static void PhantomReadFirstThread(String connStrig, IsolationLevel level) {
         Console.WriteLine("Begin the PhantomReadFirstThread.....");

         using (SqlConnection conn = new SqlConnection(connStrig)) {
            String cmdText = @"Use DbDataIsolationLevel;

                    Select ProductId,ProductName,Quantity,Price
                    from dbo.Products

                    WaitFor Delay '00:00:06';

                    Select ProductId,ProductName,Quantity,Price
                    from dbo.Products";

            conn.Open();

            using (SqlTransaction tran = conn.BeginTransaction(level, "PhantomReadFirst")) {
               using (SqlCommand command = new SqlCommand(cmdText, conn)) {
                  command.Transaction = tran;

                  using (SqlDataReader reader = command.ExecuteReader()) {
                     Boolean isFirstReader = true;
                     do {
                        Console.WriteLine("It's the result of {0} read:", isFirstReader ? "first" : "second");

                        TransactionIsolationLevels.DisplayData(reader);

                        isFirstReader = !isFirstReader;
                     } while (reader.NextResult() && !isFirstReader);
                  }
               }

               tran.Commit();
            }
         }
         Console.WriteLine("Exit from the PhantomReadFirstThread.....");
      }

      public static void PhantomReadSecondThread(String connStrig, IsolationLevel level) {
         Console.WriteLine("Begin the PhantomReadSecondThread.....");

         using (SqlConnection conn = new SqlConnection(connStrig)) {
            String cmdText = @"Use DbDataIsolationLevel;

                    WaitFor Delay '00:00:03';

                    INSERT [dbo].[Products] ([ProductName], [Quantity], [Price])
                    VALUES (N'White Bike', 843, 1349.00)";

            conn.Open();

            using (SqlTransaction tran = conn.BeginTransaction(level, "PhantomReadSecond")) {
               using (SqlCommand command = new SqlCommand(cmdText, conn)) {
                  command.Transaction = tran;
                  command.ExecuteNonQuery();
               }
               tran.Commit();
            }
         }

         Console.WriteLine("Exit from the PhantomReadSecondThread.....");
      }
   }

   // Demonstrates if the specific transaction allows the following behaviors:
   // 1. Dirty reads;
   // 2. Non-repeatable reads;
   // 3. Phantoms.
   static class TransactionIsolationLevels {
      public static void DemonstrateIsolationLevel(String connString, IsolationLevel level) {
         // Before connect the database, recreate the table.
         OperateDatabase.CreateTable(connString);
         DemonstrateIsolationLevel(connString, level, DirtyReadThreads.DirtyReadFirstThread, DirtyReadThreads.DirtyReadSecondThread);
         DisplayData(connString);
         Console.WriteLine();

         OperateDatabase.CreateTable(connString);
         DemonstrateIsolationLevel(connString, level, NonrepeatableReadThreads.NonrepeatableReadFirstThread, NonrepeatableReadThreads.NonrepeatableReadSecondThread);
         Console.WriteLine();

         OperateDatabase.CreateTable(connString);
         DemonstrateIsolationLevel(connString, level, PhantomReadThreads.PhantomReadFirstThread, PhantomReadThreads.PhantomReadSecondThread);
         Console.WriteLine();
      }

      // Demonstrates if the specific transaction allows the specific behaviors.
      public static void DemonstrateIsolationLevel(String connString, IsolationLevel level,
          AsyncAccessDatabase firstThread, AsyncAccessDatabase secondThread) {
         Task[] tasks ={
                            Task.Factory.StartNew(()=>firstThread(connString, level)),
                            Task.Factory.StartNew(()=>secondThread(connString, level))
                        };

         Task.WaitAll(tasks);
      }

      static class ExchangeValuesThreads {
         public static void ExchangeValuesFirstThread(String connStrig, IsolationLevel level) {
            Console.WriteLine("Begin the ExchangeValuesFirstThread.....");

            using (SqlConnection conn = new SqlConnection(connStrig)) {
               String cmdText = @"Use DbDataIsolationLevel;

                    Declare @price money;
                    select @price=Price from dbo.Products where ProductId=2;

                    Update dbo.Products set Price=@price where ProductId=1;

                    WaitFor Delay '00:00:06'; ";

               conn.Open();
               using (SqlTransaction tran = conn.BeginTransaction(level, "ExchangeValuesFirst")) {

                  using (SqlCommand command = new SqlCommand(cmdText, conn)) {
                     command.Transaction = tran;
                     command.ExecuteNonQuery();
                  }

                  tran.Commit();
               }
            }

            Console.WriteLine("Exit from the ExchangeValuesFirstThread.....");
         }

         public static void ExchangeValuesSecondThread(String connStrig, IsolationLevel level) {
            Console.WriteLine("Begin the ExchangeValuesSecondThread.....");

            using (SqlConnection conn = new SqlConnection(connStrig)) {
               String cmdText = @"Use DbDataIsolationLevel;

                    WaitFor Delay '00:00:03';

                    Declare @price money;
                    select @price=Price from dbo.Products where ProductId=1;

                    Update dbo.Products set Price=@price where ProductId=2;";

               conn.Open();

               using (SqlTransaction tran = conn.BeginTransaction(level, "ExchangeValuesSecond")) {
                  using (SqlCommand command = new SqlCommand(cmdText, conn)) {
                     command.Transaction = tran;
                     command.ExecuteNonQuery();
                  }
                  tran.Commit();
               }
            }

            Console.WriteLine("Exit from the ExchangeValuesSecondThread.....");
         }
      }

      // Demonstrates the difference between the Serializable and Snapshot transaction
      public static void DemonstrateBetweenSnapshotAndSerializable(String connString) {
         OperateDatabase.CreateTable(connString);

         Console.WriteLine("Exchange Vaules in the Snapshot transaction:");
         DemonstrateIsolationLevel(connString, IsolationLevel.Snapshot,
             ExchangeValuesThreads.ExchangeValuesFirstThread,
             ExchangeValuesThreads.ExchangeValuesSecondThread);
         DisplayData(connString);
         Console.WriteLine();

         Console.WriteLine("Cannot Exchange Vaules in the Serializable transaction:");
         OperateDatabase.CreateTable(connString);
         DemonstrateIsolationLevel(connString, IsolationLevel.Serializable,
             ExchangeValuesThreads.ExchangeValuesFirstThread,
             ExchangeValuesThreads.ExchangeValuesSecondThread);
         DisplayData(connString);
      }

      public static void DisplayData(String connString) {
         using (SqlConnection conn = new SqlConnection(connString)) {
            String cmdText = @"Use DbDataIsolationLevel;

                    Select ProductId,ProductName,Quantity,Price
                    from dbo.Products";

            conn.Open();

            using (SqlCommand command = new SqlCommand(cmdText, conn)) {
               using (SqlDataReader reader = command.ExecuteReader()) {
                  DisplayData(reader);
               }
            }
         }
      }

      public static void DisplayData(SqlDataReader reader) {
         Boolean isFirst = true;

         while (reader.Read()) {
            if (isFirst) {
               isFirst = false;

               for (int i = 0; i < reader.FieldCount; i++)
                  Console.Write("{0,-12}   ", reader.GetName(i));
               Console.WriteLine();
            }

            for (int i = 0; i < reader.FieldCount; i++)
               Console.Write("{0,-12}   ", reader[i]);
            Console.WriteLine();
         }
      }
   }

   // This class includes database operations. If there's no database 'DbDataIsolationLevel', create the database.
   static class OperateDatabase {
      public static Boolean CreateDatabase(String connString) {
         using (SqlConnection conn = new SqlConnection(connString)) {
            String cmdText = @"Use Master;

                                     if Db_Id('DbDataIsolationLevel') is null
                                      create Database [DbDataIsolationLevel];";

            using (SqlCommand command = new SqlCommand(cmdText, conn)) {
               conn.Open();
               command.ExecuteNonQuery();
            }

            Console.WriteLine("Create the Database 'DbDataIsolationLevel'");
         }

         return true;
      }

      // If there's no table [dbo].[Products] in DbDataIsolationLevel, create the table; or recreate it.
      public static Boolean CreateTable(String connString) {
         using (SqlConnection conn = new SqlConnection(connString)) {
            String cmdText = @"Use DbDataIsolationLevel

                                    if Object_ID('[dbo].[Products]') is not null
                                    drop table [dbo].[Products]

                                    Create Table [dbo].[Products]
                                    (
                                    [ProductId] int IDENTITY(1,1) primary key,
                                    [ProductName] NVarchar(100) not null,
                                    [Quantity] int null,
                                    [Price] money null
                                    )";

            using (SqlCommand command = new SqlCommand(cmdText, conn)) {
               conn.Open();
               command.ExecuteNonQuery();
            }
         }

         return InsertRows(connString);
      }

      // Insert some rows into [dbo].[Products] table.
      public static Boolean InsertRows(String connString) {
         using (SqlConnection conn = new SqlConnection(connString)) {
            String cmdText = @"Use DbDataIsolationLevel

                    INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Blue Bike', 365,1075.00)
                    INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Red Bike', 159, 1299.00)
                    INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Black Bike', 638, 1159.00)";

            using (SqlCommand command = new SqlCommand(cmdText, conn)) {
               conn.Open();
               command.ExecuteNonQuery();
            }
         }
         return true;
      }

      // Turn on or off 'ALLOW_SNAPSHOT_ISOLATION'
      public static Boolean SetSnapshot(String connString, Boolean isOpen) {
         using (SqlConnection conn = new SqlConnection(connString)) {
            String cmdText = null;

            if (isOpen)
               cmdText = @"ALTER DATABASE DbDataIsolationLevel SET ALLOW_SNAPSHOT_ISOLATION ON";
            else
               cmdText = @"ALTER DATABASE DbDataIsolationLevel SET ALLOW_SNAPSHOT_ISOLATION OFF";

            using (SqlCommand command = new SqlCommand(cmdText, conn)) {
               conn.Open();
               command.ExecuteNonQuery();
            }
         }

         return true;
      }
   }
   class Program {
      static void Main(string[] args) {
         String connString = "Data Source=(local);Initial Catalog=master;Integrated Security=True;Asynchronous Processing=true;";

         OperateDatabase.CreateDatabase(connString);
         Console.WriteLine();

         Console.WriteLine("Demonstrate the ReadUncommitted transaction: ");
         TransactionIsolationLevels.DemonstrateIsolationLevel(connString,
             System.Data.IsolationLevel.ReadUncommitted);
         Console.WriteLine("-----------------------------------------------");

         Console.WriteLine("Demonstrate the ReadCommitted transaction: ");
         TransactionIsolationLevels.DemonstrateIsolationLevel(connString,
             System.Data.IsolationLevel.ReadCommitted);
         Console.WriteLine("-----------------------------------------------");

         Console.WriteLine("Demonstrate the RepeatableRead transaction: ");
         TransactionIsolationLevels.DemonstrateIsolationLevel(connString,
             System.Data.IsolationLevel.RepeatableRead);
         Console.WriteLine("-----------------------------------------------");

         Console.WriteLine("Demonstrate the Serializable transaction: ");
         TransactionIsolationLevels.DemonstrateIsolationLevel(connString,
             System.Data.IsolationLevel.Serializable);
         Console.WriteLine("-----------------------------------------------");

         Console.WriteLine("Demonstrate the Snapshot transaction: ");
         OperateDatabase.SetSnapshot(connString, true);
         TransactionIsolationLevels.DemonstrateIsolationLevel(connString,
             System.Data.IsolationLevel.Snapshot);
         Console.WriteLine("-----------------------------------------------");

         Console.WriteLine("Demonstrate the difference between the Snapshot and Serializable transactions:");
         TransactionIsolationLevels.DemonstrateBetweenSnapshotAndSerializable(connString);
         OperateDatabase.SetSnapshot(connString, false);
         Console.WriteLine();
      }
   }
}

Imports System.Data.SqlClient
Imports System.Data
Imports System.Threading
Imports System.Threading.Tasks

Namespace CSDataIsolationLevel

   ' Use the delegate to call the different threads.
   Public Delegate Sub AsyncAccessDatabase(connString As [String], level As IsolationLevel)

   NotInheritable Class DirtyReadThreads
      Private Sub New()
      End Sub
      Public Shared Sub DirtyReadFirstThread(connStrig As [String], level As IsolationLevel)
         Console.WriteLine("Begin the DirtyReadFirstThread.....")

         Using conn As New SqlConnection(connStrig)
            Dim cmdText As [String] = "Use DbDataIsolationLevel; " & vbCr & vbLf & vbCr & vbLf &
                "Update dbo.Products set Quantity=Quantity+100 where ProductId=1;" & vbCr & vbLf &
                "WaitFor Delay '00:00:06';"

            conn.Open()

            Using tran As SqlTransaction = conn.BeginTransaction(level, "DirtyReadFirst")
               Using command As New SqlCommand(cmdText, conn)
                  command.Transaction = tran
                  command.ExecuteNonQuery()
               End Using

               If tran IsNot Nothing Then
                  tran.Rollback()
               End If
            End Using
         End Using

         Console.WriteLine("Exit from the DirtyReadFirstThread.....")
      End Sub

      Public Shared Sub DirtyReadSecondThread(connStrig As [String], level As IsolationLevel)
         Console.WriteLine("Begin the DirtyReadSecondThread.....")

         Using conn As New SqlConnection(connStrig)
            Dim cmdText As [String] = "Use DbDataIsolationLevel;" & vbCr & vbLf & vbCr & vbLf &
                "WaitFor Delay '00:00:03'; " & vbCr & vbLf & vbCr & vbLf &
                "Declare @qty int;" & vbCr & vbLf &
                "select @qty=Quantity from dbo.Products where ProductId=1;" & vbCr & vbLf & vbCr & vbLf &
                "Update dbo.Products set Quantity=@qty+100 where ProductId=1;"

            conn.Open()

            Using tran As SqlTransaction = conn.BeginTransaction(level, "DirtyReadSecond")
               Using command As New SqlCommand(cmdText, conn)
                  command.Transaction = tran
                  command.ExecuteNonQuery()
               End Using
               tran.Commit()
            End Using
         End Using

         Console.WriteLine("Exit from the DirtyReadSecondThread.....")
      End Sub
   End Class


   NotInheritable Class NonrepeatableReadThreads
      Private Sub New()
      End Sub
      Public Shared Sub NonrepeatableReadFirstThread(connStrig As [String], level As IsolationLevel)
         Console.WriteLine("Begin the NonrepeatableReadFirstThread.....")

         Using conn As New SqlConnection(connStrig)
            Dim cmdText As [String] = "Use DbDataIsolationLevel; " & vbCr & vbLf & vbCr & vbLf &
                "Select ProductId,ProductName,Quantity,Price" & vbCr & vbLf &
                "from dbo.Products" & vbCr & vbLf &
                "where ProductId=1" & vbCr & vbLf & vbCr & vbLf &
                "WaitFor Delay '00:00:06';" & vbCr & vbLf & vbCr & vbLf &
                "Select ProductId,ProductName,Quantity,Price" & vbCr & vbLf &
                "from dbo.Products" & vbCr & vbLf &
                "where ProductId=1"

            conn.Open()

            Using tran As SqlTransaction = conn.BeginTransaction(level, "NonrepeatableReadFirst")
               Using command As New SqlCommand(cmdText, conn)
                  command.Transaction = tran

                  Using reader As SqlDataReader = command.ExecuteReader()
                     Dim isFirstReader As [Boolean] = True
                     Do
                        Console.WriteLine("It's the result of {0} read:", If(isFirstReader, "first", "second"))
                        TransactionIsolationLevels.DisplayData(reader)
                        isFirstReader = Not isFirstReader
                     Loop While reader.NextResult() AndAlso Not isFirstReader
                  End Using
               End Using

               tran.Commit()
            End Using
         End Using

         Console.WriteLine("Exit from the NonrepeatableReadFirstThread.....")
      End Sub

      Public Shared Sub NonrepeatableReadSecondThread(connStrig As [String], level As IsolationLevel)
         Console.WriteLine("Begin the NonrepeatableReadSecondThread.....")

         Using conn As New SqlConnection(connStrig)
            Dim cmdText As [String] = "Use DbDataIsolationLevel;" & vbCr & vbLf & vbCr & vbLf &
                "WaitFor Delay '00:00:03'; " & vbCr & vbLf & vbCr & vbLf &
                "Update dbo.Products set Quantity=Quantity+100 where ProductId=1;"

            conn.Open()

            Using tran As SqlTransaction = conn.BeginTransaction(level, "NonrepeatableReadSecond")
               Using command As New SqlCommand(cmdText, conn)
                  command.Transaction = tran
                  command.ExecuteNonQuery()
               End Using
               tran.Commit()
            End Using
         End Using

         Console.WriteLine("Exit from the NonrepeatableReadSecondThread.....")
      End Sub
   End Class


   NotInheritable Class PhantomReadThreads
      Private Sub New()
      End Sub
      Public Shared Sub PhantomReadFirstThread(connStrig As [String], level As IsolationLevel)
         Console.WriteLine("Begin the PhantomReadFirstThread.....")

         Using conn As New SqlConnection(connStrig)
            Dim cmdText As [String] = "Use DbDataIsolationLevel; " & vbCr & vbLf & vbCr & vbLf &
                "Select ProductId,ProductName,Quantity,Price" & vbCr & vbLf &
                "from dbo.Products" & vbCr & vbLf & vbCr & vbLf &
                "WaitFor Delay '00:00:06';" & vbCr & vbLf & vbCr & vbLf &
                "Select ProductId,ProductName,Quantity,Price" & vbCr & vbLf &
                "from dbo.Products"

            conn.Open()

            Using tran As SqlTransaction = conn.BeginTransaction(level, "PhantomReadFirst")
               Using command As New SqlCommand(cmdText, conn)
                  command.Transaction = tran

                  Using reader As SqlDataReader = command.ExecuteReader()
                     Dim isFirstReader As [Boolean] = True
                     Do
                        Console.WriteLine("It's the result of {0} read:", If(isFirstReader, "first", "second"))

                        TransactionIsolationLevels.DisplayData(reader)

                        isFirstReader = Not isFirstReader
                     Loop While reader.NextResult() AndAlso Not isFirstReader
                  End Using
               End Using

               tran.Commit()
            End Using
         End Using
         Console.WriteLine("Exit from the PhantomReadFirstThread.....")
      End Sub

      Public Shared Sub PhantomReadSecondThread(connStrig As [String], level As IsolationLevel)
         Console.WriteLine("Begin the PhantomReadSecondThread.....")

         Using conn As New SqlConnection(connStrig)
            Dim cmdText As [String] = "Use DbDataIsolationLevel;" & vbCr & vbLf & vbCr & vbLf &
                "WaitFor Delay '00:00:03'; " & vbCr & vbLf & vbCr & vbLf &
                "INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) " & vbCr & vbLf &
                "VALUES (N'White Bike', 843, 1349.00)"

            conn.Open()

            Using tran As SqlTransaction = conn.BeginTransaction(level, "PhantomReadSecond")
               Using command As New SqlCommand(cmdText, conn)
                  command.Transaction = tran
                  command.ExecuteNonQuery()
               End Using
               tran.Commit()
            End Using
         End Using

         Console.WriteLine("Exit from the PhantomReadSecondThread.....")
      End Sub
   End Class


   ' Demonstrates if the specific transaction allows the following behaviors:
   ' 1. Dirty reads;
   ' 2. Non-repeatable reads;
   ' 3. Phantoms.
   NotInheritable Class TransactionIsolationLevels
      Private Sub New()
      End Sub
      Public Shared Sub DemonstrateIsolationLevel(connString As [String], level As IsolationLevel)
         ' Before connect the database, recreate the table.
         OperateDatabase.CreateTable(connString)
         DemonstrateIsolationLevel(connString, level, AddressOf DirtyReadThreads.DirtyReadFirstThread, AddressOf DirtyReadThreads.DirtyReadSecondThread)
         DisplayData(connString)
         Console.WriteLine()

         OperateDatabase.CreateTable(connString)
         DemonstrateIsolationLevel(connString, level, AddressOf NonrepeatableReadThreads.NonrepeatableReadFirstThread, AddressOf NonrepeatableReadThreads.NonrepeatableReadSecondThread)
         Console.WriteLine()

         OperateDatabase.CreateTable(connString)
         DemonstrateIsolationLevel(connString, level, AddressOf PhantomReadThreads.PhantomReadFirstThread, AddressOf PhantomReadThreads.PhantomReadSecondThread)
         Console.WriteLine()
      End Sub

      ' Demonstrates if the specific transaction allows the specific behaviors.
      Public Shared Sub DemonstrateIsolationLevel(connString As [String], level As IsolationLevel, firstThread As AsyncAccessDatabase, secondThread As AsyncAccessDatabase)
         ' Dim tasks As Task() = {Task.Factory.StartNew(Function() firstThread(connString, level)), Task.Factory.StartNew(Function() secondThread(connString, level))}

         Dim tasks() As Task = {
                         Task.Factory.StartNew(Sub() firstThread(connString, level)),
                         Task.Factory.StartNew(Sub() secondThread(connString, level))
                                        }

         Task.WaitAll(tasks)
      End Sub

      Private NotInheritable Class ExchangeValuesThreads
         Private Sub New()
         End Sub
         Public Shared Sub ExchangeValuesFirstThread(connStrig As [String], level As IsolationLevel)
            Console.WriteLine("Begin the ExchangeValuesFirstThread.....")

            Using conn As New SqlConnection(connStrig)
               Dim cmdText As [String] = "Use DbDataIsolationLevel;" & vbCr & vbLf & vbCr & vbLf &
                   "Declare @price money;" & vbCr & vbLf &
                   "select @price=Price from dbo.Products where ProductId=2;" & vbCr & vbLf & vbCr & vbLf &
                   "Update dbo.Products set Price=@price where ProductId=1;" & vbCr & vbLf & vbCr & vbLf &
                   "WaitFor Delay '00:00:06'; "

               conn.Open()
               Using tran As SqlTransaction = conn.BeginTransaction(level, "ExchangeValuesFirst")

                  Using command As New SqlCommand(cmdText, conn)
                     command.Transaction = tran
                     command.ExecuteNonQuery()
                  End Using

                  tran.Commit()
               End Using
            End Using

            Console.WriteLine("Exit from the ExchangeValuesFirstThread.....")
         End Sub

         Public Shared Sub ExchangeValuesSecondThread(connStrig As [String], level As IsolationLevel)
            Console.WriteLine("Begin the ExchangeValuesSecondThread.....")

            Using conn As New SqlConnection(connStrig)
               Dim cmdText As [String] = "Use DbDataIsolationLevel;" & vbCr & vbLf & vbCr & vbLf &
                   "WaitFor Delay '00:00:03'; " & vbCr & vbLf & vbCr & vbLf &
                   "Declare @price money;" & vbCr & vbLf &
                   "select @price=Price from dbo.Products where ProductId=1;" & vbCr & vbLf & vbCr & vbLf &
                   "Update dbo.Products set Price=@price where ProductId=2;"

               conn.Open()

               Using tran As SqlTransaction = conn.BeginTransaction(level, "ExchangeValuesSecond")
                  Using command As New SqlCommand(cmdText, conn)
                     command.Transaction = tran
                     command.ExecuteNonQuery()
                  End Using
                  tran.Commit()
               End Using
            End Using

            Console.WriteLine("Exit from the ExchangeValuesSecondThread.....")
         End Sub
      End Class

      ' Demonstrates the difference between the Serializable and Snapshot transaction
      Public Shared Sub DemonstrateBetweenSnapshotAndSerializable(connString As [String])
         OperateDatabase.CreateTable(connString)

         Console.WriteLine("Exchange Vaules in the Snapshot transaction:")
         DemonstrateIsolationLevel(connString, IsolationLevel.Snapshot, AddressOf ExchangeValuesThreads.ExchangeValuesFirstThread, AddressOf ExchangeValuesThreads.ExchangeValuesSecondThread)
         DisplayData(connString)
         Console.WriteLine()

         Console.WriteLine("Cannot Exchange Vaules in the Serializable transaction:")
         OperateDatabase.CreateTable(connString)
         DemonstrateIsolationLevel(connString, IsolationLevel.Serializable, AddressOf ExchangeValuesThreads.ExchangeValuesFirstThread, AddressOf ExchangeValuesThreads.ExchangeValuesSecondThread)
         DisplayData(connString)
      End Sub

      Public Shared Sub DisplayData(connString As [String])
         Using conn As New SqlConnection(connString)
            Dim cmdText As [String] = "Use DbDataIsolationLevel; " & vbCr & vbLf & vbCr & vbLf &
                "Select ProductId,ProductName,Quantity,Price" & vbCr & vbLf &
                "from dbo.Products"

            conn.Open()

            Using command As New SqlCommand(cmdText, conn)
               Using reader As SqlDataReader = command.ExecuteReader()
                  DisplayData(reader)
               End Using
            End Using
         End Using
      End Sub

      Public Shared Sub DisplayData(reader As SqlDataReader)
         Dim isFirst As [Boolean] = True

         While reader.Read()
            If isFirst Then
               isFirst = False

               For i As Integer = 0 To reader.FieldCount - 1
                  Console.Write("{0,-12}   ", reader.GetName(i))
               Next
               Console.WriteLine()
            End If

            For i As Integer = 0 To reader.FieldCount - 1
               Console.Write("{0,-12}   ", reader(i))
            Next
            Console.WriteLine()
         End While
      End Sub
   End Class

   ' This class includes database operations. If there's no database 'DbDataIsolationLevel', create the database.
   NotInheritable Class OperateDatabase
      Private Sub New()
      End Sub
      Public Shared Function CreateDatabase(connString As [String]) As [Boolean]
         Using conn As New SqlConnection(connString)
            Dim cmdText As [String] = "Use Master;" & vbCr & vbLf & vbCr & vbLf &
                "if Db_Id('DbDataIsolationLevel') is null" & vbCr & vbLf &
                "create Database [DbDataIsolationLevel];"

            Using command As New SqlCommand(cmdText, conn)
               conn.Open()
               command.ExecuteNonQuery()
            End Using

            Console.WriteLine("Create the Database 'DbDataIsolationLevel'")
         End Using

         Return True
      End Function


      ' If there's no table [dbo].[Products] in DbDataIsolationLevel, create the table; or recreate it.
      Public Shared Function CreateTable(connString As [String]) As [Boolean]
         Using conn As New SqlConnection(connString)
            Dim cmdText As [String] = "Use DbDataIsolationLevel" & vbCr & vbLf & vbCr & vbLf &
                "if Object_ID('[dbo].[Products]') is not null" & vbCr & vbLf &
                "drop table [dbo].[Products]" & vbCr & vbLf & vbCr & vbLf &
                "Create Table [dbo].[Products]" & vbCr & vbLf &
                "(" & vbCr & vbLf &
                "[ProductId] int IDENTITY(1,1) primary key," & vbCr & vbLf &
                "[ProductName] NVarchar(100) not null," & vbCr & vbLf &
                "[Quantity] int null," & vbCr & vbLf &
                "[Price] money null" & vbCr & vbLf & "                                    )"

            Using command As New SqlCommand(cmdText, conn)
               conn.Open()
               command.ExecuteNonQuery()
            End Using
         End Using

         Return InsertRows(connString)
      End Function

      ' Insert some rows into [dbo].[Products] table.
      Public Shared Function InsertRows(connString As [String]) As [Boolean]
         Using conn As New SqlConnection(connString)
            Dim cmdText As [String] = "Use DbDataIsolationLevel" & vbCr & vbLf & vbCr & vbLf &
                "INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Blue Bike', 365,1075.00)" & vbCr & vbLf &
                "INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Red Bike', 159, 1299.00)" & vbCr & vbLf &
                "INSERT [dbo].[Products] ([ProductName], [Quantity], [Price]) VALUES (N'Black Bike', 638, 1159.00)"

            Using command As New SqlCommand(cmdText, conn)
               conn.Open()
               command.ExecuteNonQuery()
            End Using
         End Using
         Return True
      End Function

      ' Turn on or off 'ALLOW_SNAPSHOT_ISOLATION'
      Public Shared Function SetSnapshot(connString As [String], isOpen As [Boolean]) As [Boolean]
         Using conn As New SqlConnection(connString)
            Dim cmdText As [String] = Nothing

            If isOpen Then
               cmdText = "ALTER DATABASE DbDataIsolationLevel SET ALLOW_SNAPSHOT_ISOLATION ON"
            Else
               cmdText = "ALTER DATABASE DbDataIsolationLevel SET ALLOW_SNAPSHOT_ISOLATION OFF"
            End If

            Using command As New SqlCommand(cmdText, conn)
               conn.Open()
               command.ExecuteNonQuery()
            End Using
         End Using

         Return True
      End Function
   End Class
   Class Program
      Public Shared Sub Main(args As String())
         Dim connString As [String] = "Data Source=(local);Initial Catalog=master;Integrated Security=True;Asynchronous Processing=true;"

         OperateDatabase.CreateDatabase(connString)
         Console.WriteLine()

         Console.WriteLine("Demonstrate the ReadUncommitted transaction: ")
         TransactionIsolationLevels.DemonstrateIsolationLevel(connString, System.Data.IsolationLevel.ReadUncommitted)
         Console.WriteLine("-----------------------------------------------")

         Console.WriteLine("Demonstrate the ReadCommitted transaction: ")
         TransactionIsolationLevels.DemonstrateIsolationLevel(connString, System.Data.IsolationLevel.ReadCommitted)
         Console.WriteLine("-----------------------------------------------")

         Console.WriteLine("Demonstrate the RepeatableRead transaction: ")
         TransactionIsolationLevels.DemonstrateIsolationLevel(connString, System.Data.IsolationLevel.RepeatableRead)
         Console.WriteLine("-----------------------------------------------")

         Console.WriteLine("Demonstrate the Serializable transaction: ")
         TransactionIsolationLevels.DemonstrateIsolationLevel(connString, System.Data.IsolationLevel.Serializable)
         Console.WriteLine("-----------------------------------------------")

         Console.WriteLine("Demonstrate the Snapshot transaction: ")
         OperateDatabase.SetSnapshot(connString, True)
         TransactionIsolationLevels.DemonstrateIsolationLevel(connString, System.Data.IsolationLevel.Snapshot)
         Console.WriteLine("-----------------------------------------------")

         Console.WriteLine("Demonstrate the difference between the Snapshot and Serializable transactions:")
         TransactionIsolationLevels.DemonstrateBetweenSnapshotAndSerializable(connString)
         OperateDatabase.SetSnapshot(connString, False)
         Console.WriteLine()
      End Sub
   End Class
End Namespace

Comentários

Os IsolationLevel valores são usados por um provedor de dados .NET ao executar uma transação.

O IsolationLevel permanece em vigor até ser explicitamente alterado, mas pode ser alterado a qualquer momento. O novo valor é usado no tempo de execução, não no tempo de análise. Se for alterado durante uma transação, o comportamento esperado do servidor será aplicar o novo nível de bloqueio a todas as instruções restantes.

Ao usar OdbcTransaction, se você não definir OdbcTransaction.IsolationLevel ou defini-lo Unspecifiedcomo , a transação será executada de acordo com o nível de isolamento determinado pelo driver em uso.

Aplica-se a