IsolationLevel IsolationLevel IsolationLevel IsolationLevel Enum

定义

指定连接的事务锁定行为。Specifies the transaction locking behavior for the connection.

此枚举有一个 FlagsAttribute 属性,允许按位组合成员值。

public enum class IsolationLevel
[System.Flags]
public enum IsolationLevel
type IsolationLevel = 
Public Enum IsolationLevel
继承
IsolationLevelIsolationLevelIsolationLevelIsolationLevel
属性

字段

Chaos Chaos Chaos Chaos 16

无法覆盖隔离级别更高的事务中的挂起的更改。The pending changes from more highly isolated transactions cannot be overwritten.

ReadCommitted ReadCommitted ReadCommitted ReadCommitted 4096

在正在读取数据时保持共享锁,以避免脏读,但是在事务结束之前可以更改数据,从而导致不可重复的读取或幻像数据。Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.

ReadUncommitted ReadUncommitted ReadUncommitted ReadUncommitted 256

可以进行脏读,意思是说,不发布共享锁,也不接受独占锁。A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.

RepeatableRead RepeatableRead RepeatableRead RepeatableRead 65536

在查询中使用的所有数据上放置锁,以防止其他用户更新这些数据。Locks are placed on all data that is used in a query, preventing other users from updating the data. 防止不可重复的读取,但是仍可以有幻像行。Prevents non-repeatable reads but phantom rows are still possible.

Serializable Serializable Serializable Serializable 1048576

DataSet 上放置范围锁,以防止在事务完成之前由其他用户更新行或向数据集中插入行。A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.

Snapshot Snapshot Snapshot Snapshot 16777216

通过在一个应用程序正在修改数据时存储另一个应用程序可以读取的相同数据版本来减少阻止。Reduces blocking by storing a version of data that one application can read while another is modifying the same data. 表示您无法从一个事务中看到在其他事务中进行的更改,即便重新查询也是如此。Indicates that from one transaction you cannot see changes made in other transactions, even if you requery.

Unspecified Unspecified Unspecified Unspecified -1

正在使用与指定隔离级别不同的隔离级别,但是无法确定该级别。A different isolation level than the one specified is being used, but the level cannot be determined.

示例

此应用程序演示了如何使用IsolationLevelDbTransactionThis application demonstrates how to use IsolationLevel in DbTransaction. 该示例将演示其中的以下行为允许在不同的隔离级别中:The sample will demonstrate which of the following behaviors are allowed in the different isolation levels:

  • 脏读。Dirty reads.

  • 非重复的读取。Non-repeatable reads.

  • 幻影。Phantoms.

此应用程序将执行以下隔离级别中:This application will execute in the following isolation levels:

  • ReadUncommittedReadUncommitted

  • ReadCommittedReadCommitted

  • RepeatableReadRepeatableRead

  • 可序列化Serializable

  • 快照Snapshot

PhantomReadThreads 类演示了是否特定事务允许接触的虚拟读取行为。The PhantomReadThreads class demonstrates if the specific transaction allows the Phantom Read behavior. 如果事务允许行为,线程将按以下顺序操作:If the transaction allows the behavior, the threads will operate in the following order:

  • 在第一个线程中,选择 products(All)。In first thread, select the products(All).

  • 在第二个线程中,插入一个新的产品。In the second thread, insert a new product.

  • 提交事务在第二个线程中。Commit the transaction in second thread.

  • 再次选择的产品。Select the products again.

  • 提交事务在第一个线程中。Commit the transaction in first thread.

如果事务允许行为,两个选择操作将获取不同的结果。If the transaction allows the behavior, the two Select operations will get the different results.

NonrepeatableReadThreads 类演示了特定的事务是否允许不可重复读行为。The NonrepeatableReadThreads class demonstrates if the specific transaction allows the Nonrepeatable Read behavior. 如果事务允许行为,线程将按以下顺序操作:If the transaction allows the behavior, the threads will operate in the following order:

  • 在第一个线程中,选择 product(ProductId=1)。In first thread, select the product(ProductId=1).

  • 在第二个线程中更新数量 value(ProductId=1)。In the second thread, update the Quantity value(ProductId=1).

  • 提交事务在第二个线程中。Commit the transaction in second thread.

  • 再次选择该产品。Select the product again.

  • 提交事务在第一个线程中。Commit the transaction in first thread.

如果事务允许行为,两个选择操作将获取不同的结果。If the transaction allows the behavior, the two Select operations will get the different results.

ExchangeValuesThreads 类演示 Serializable 和 Snapshot 事务之间的差异。The ExchangeValuesThreads class demonstrates the difference between the Serializable and Snapshot transaction. 对于可序列化事务中,线程将按以下顺序操作:For the Serializable transaction, threads will operate in the following order:

  • 在第一个线程中,在变量中获取 product(ProductId=2) 和存储的价格。In first thread, get the Price of product(ProductId=2) and store in the variable.

  • 在第一个线程中更新的 product(ProductId=2) 价格 product(ProductId=1) 的价格。In first thread, update the Price of product(ProductId=1) with the price of product(ProductId=2).

  • 提交事务在第一个线程中。Commit the transaction in first thread.

  • 在第二个线程中,在变量中获取 product(ProductId=1) 和存储的价格。In second thread, get the Price of product(ProductId=1) and store in the variable.

  • 在第二个线程中更新的 product(ProductId=1) 价格 product(ProductId=2) 的价格。In second thread, update the Price of product(ProductId=2) with the price of product(ProductId=1).

  • 提交事务在第二个线程中。Commit the transaction in second thread.

现在的价格值 (ProductId = 1 且 ProductId = 2) 是为与原始 Product(ProductId=2) 价格相同。Now the values of the Price(ProductId=1 and ProductId=2) are as same as the original Price of Product(ProductId=2).

对于快照事务中,线程将按以下顺序操作:For the Snapshot transaction, threads will operate in the following order:

  • 在第一个线程中变量,例如: 获得 product(ProductId=2) 和存储的价格In first thread, get the Price of product(ProductId=2) and store in the variable;

  • 在第一个线程中更新的 product(ProductId=2) 价格 product(ProductId=1) 的价格。In first thread, update the Price of product(ProductId=1) with the price of product(ProductId=2).

  • 在第二个线程中 product(ProductId=1) 的价格获得快照并存储在变量中。In second thread, get the Price of product(ProductId=1) from the snapshot and store in the variable.

  • 在第二个线程中更新的 product(ProductId=1) 价格 product(ProductId=2) 的价格。In second thread, update the Price of product(ProductId=2) with the price of product(ProductId=1).

  • 提交事务在第二个线程中。Commit the transaction in second thread.

  • 提交事务在第一个线程中。Commit the transaction in first thread.

现在交换产品的价格 (ProductId = 1 且 ProductId = 2)。Now exchange the Price of products(ProductId=1 and ProductId=2).

DirtyReadThreads 类演示了是否特定事务允许脏读行为。The DirtyReadThreads class demonstrates if the specific transaction allows the Dirty Read behavior. 如果事务允许行为,线程将按以下顺序操作:If the transaction allows the behavior, the threads will operate in the following order:

  • 在第一个线程开始一个事务并添加数量 value(ProductId=1)。In first thread, begin a transaction and add the Quantity value(ProductId=1).

  • 中的第二个线程读取数量值并再次添加值。In the second thread, read the Quantity value and add the value again.

  • 提交事务在第二个线程中。Commit the transaction in second thread.

  • 回滚该事务中的第一个线程。Roll back the transaction in first thread.

如果事务允许行为,将两次添加数量值。If the transaction allows the behavior, the Quantity value will be added twice.

可以上找到具有此代码示例的 C# 和 Visual Basic 项目开发人员代码示例C# and Visual Basic projects with this code sample can be found on Developer Code Samples.

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

注解

IsolationLevel值由.NET 数据提供程序执行事务时。The IsolationLevel values are used by a .NET data provider when performing a transaction.

IsolationLevel保持生效,直到显式更改,但可以随时更改。The IsolationLevel remains in effect until explicitly changed, but it can be changed at any time. 使用新值是在执行时,不分析时。The new value is used at execution time, not parse time. 如果在事务期间发生更改,服务器的预期的行为是将新的锁定级别应用到所有剩余的语句。If changed during a transaction, the expected behavior of the server is to apply the new locking level to all statements remaining.

使用时OdbcTransaction,如果未设置OdbcTransaction.IsolationLevel或将其设置为Unspecified,根据由中使用的驱动程序的隔离级别执行该事务。When using OdbcTransaction, if you do not set OdbcTransaction.IsolationLevel or you set it to Unspecified, the transaction executes according to the isolation level determined by the driver in use.

适用于