SQL Server 中的快照隔離Snapshot Isolation in SQL Server

快照隔離可強化 OLTP 應用程式的並行功能。Snapshot isolation enhances concurrency for OLTP applications.

瞭解快照集隔離及資料列版本控制Understanding Snapshot Isolation and Row Versioning

一旦啟用快照集隔離,就必須維護每個交易的更新資料列版本。Once snapshot isolation is enabled, updated row versions for each transaction must be maintained. 在 SQL Server 2019 之前,這些版本會儲存在tempdb中。Prior to SQL Server 2019, these versions were stored in tempdb. SQL Server 2019 引進了一項新功能:加速資料庫復原(ADR),它需要自己的一組資料列版本。SQL Server 2019 introduces a new feature, Accelerated Database Recovery (ADR) which requires its own set of row versions. 因此,從 SQL Server 2019,如果未啟用 ADR,則資料列版本會一律保留在tempdb中。So, as of SQL Server 2019, if ADR is not enabled, row versions are kept in tempdb as always. 如果已啟用 ADR,則與 snapshot 隔離和 ADR 相關的所有資料列版本都會保留在 ADR 的持續版本存放區(PV)中,該資料庫位於使用者指定的檔案群組中。If ADR is enabled, then all row versions, both related to snapshot isolation and ADR, are kept in ADR's Persistent Version Store (PVS), which is located in the user database in a filegroup which the user specifies. 唯一交易序號識別每筆交易,並會針對每個資料列版本記錄這些唯一的號碼。A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. 交易使用其序號在該交易序號之前的最新資料列版本。The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. 該異動會忽略在異動開始之後建立的較新資料列版本。Newer row versions created after the transaction has begun are ignored by the transaction.

「快照集」這個詞彙反映了根據異動開始時資料庫的狀態,異動中所有查詢都看到資料庫的同一版本 (或快照集) 這一事實。The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. 在快照集交易中的基礎資料列或資料頁面上沒有鎖定,這允許其他交易執行,而不會被之前未完成的交易封鎖。No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. 修改資料的異動不會封鎖讀取資料的異動,而讀取資料的異動也不會封鎖寫入資料的異動,因為它們通常處於 SQL Server 中預設的 READ COMMITTED 隔離等級中。Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. 這種無封鎖的行為也會大幅降低複雜異動發生死結的可能性。This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.

快照集隔離使用開放式同步存取模型。Snapshot isolation uses an optimistic concurrency model. 如果快照集異動嘗試認可對異動開始後發生變更之資料的修改,則該異動將復原並將發生錯誤。If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised. 對存取要修改之資料的 SELECT 陳述式使用 UPDLOCK 提示,可避免發生上述情況。You can avoid this by using UPDLOCK hints for SELECT statements that access data to be modified. 如需詳細資訊,請參閱《SQL Server 線上叢書》的<鎖定提示>。See "Locking Hints" in SQL Server Books Online for more information.

必須先設定 ALLOW_SNAPSHOT_ISOLATION ON 資料庫選項以啟用快照集隔離,才能在交易中使用快照集隔離。Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. 這會啟用在暫存資料庫(tempdb)中儲存資料列版本的機制。This activates the mechanism for storing row versions in the temporary database (tempdb). 必須在搭配使用快照集隔離與 Transact-SQL ALTER DATABASE 陳述式的每個資料庫中啟用快照集隔離。You must enable snapshot isolation in each database that uses it with the Transact-SQL ALTER DATABASE statement. 在這一方面,快照集隔離不同於無需設定的傳統隔離等級 READ COMMITTED、REPEATABLE READ、SERIALIZABLE 及 READ UNCOMMITTED。In this respect, snapshot isolation differs from the traditional isolation levels of READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and READ UNCOMMITTED, which require no configuration. 下列陳述式會啟動快照集隔離,並以 SNAPSHOT 取代預設的 READ COMMITTED 行為:The following statements activate snapshot isolation and replace the default READ COMMITTED behavior with SNAPSHOT:

ALTER DATABASE MyDatabase  
SET ALLOW_SNAPSHOT_ISOLATION ON  
  
ALTER DATABASE MyDatabase  
SET READ_COMMITTED_SNAPSHOT ON  

藉由設定 READ_COMMITTED_SNAPSHOT ON 選項,可存取預設 READ COMMITTED 隔離等級下已指定版本的資料列。Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level. 如果將 READ_COMMITTED_SNAPSHOT 選項設為 OFF,則必須明確設定每個工作階段的快照隔離等級,以便存取已指定版本的資料列。If the READ_COMMITTED_SNAPSHOT option is set to OFF, you must explicitly set the Snapshot isolation level for each session in order to access versioned rows.

使用隔離等級管理並行存取Managing Concurrency with Isolation Levels

Transact-SQL 陳述式執行所在的隔離等級決定了其鎖定及資料列版本控制行為。The isolation level under which a Transact-SQL statement executes determines its locking and row versioning behavior. 隔離等級的範圍涵蓋整個連接,一旦以 SET TRANSACTION ISOLATION LEVEL 陳述式設定某連接的隔離等級後,它會持續有效,直到連接關閉或設定了另一隔離等級為止。An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. 當連接關閉且傳回至集區時,會保留最後一個 SET TRANSACTION ISOLATION LEVEL 陳述式的隔離等級 (Isolation Level)。When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. 重複使用共用連接的後續連接會使用在共用連接時有效的隔離等級。Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled.

在連接內發出的個別查詢可包含修改單一陳述式或異動之隔離的鎖定提示,但不會影響連接的隔離等級。Individual queries issued within a connection can contain lock hints that modify the isolation for a single statement or transaction but do not affect the isolation level of the connection. 預存程序或函式中設定的隔離等級或鎖定提示,不會變更呼叫它們之連接的隔離等級,並僅在預存程序或函式呼叫期間有效。Isolation levels or lock hints set in stored procedures or functions do not change the isolation level of the connection that calls them and are in effect only for the duration of the stored procedure or function call.

舊版 SQL Server 支援 SQL-92 標準中定義的四種隔離等級:Four isolation levels defined in the SQL-92 standard were supported in early versions of SQL Server:

  • READ UNCOMMITTED 是最為寬鬆的隔離等級,因為它會忽略其他異動設定的鎖定。READ UNCOMMITTED is the least restrictive isolation level because it ignores locks placed by other transactions. 在 READ UNCOMMITTED 之下執行的交易可讀取其他交易尚未認可的已修改資料值;這種讀取稱為 Dirty 讀取。Transactions executing under READ UNCOMMITTED can read modified data values that have not yet been committed by other transactions; these are called "dirty" reads.

  • READ COMMITTED 是 SQL Server 的預設隔離等級。READ COMMITTED is the default isolation level for SQL Server. 它藉由指定陳述式不可讀取已修改但未獲得其他異動認可的資料值,來防止 Dirty 讀取。It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. 在目前交易內個別陳述式執行之間,其他交易仍可修改、插入或刪除資料,這會導致不可重複的讀取,即「虛設」資料。Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or "phantom" data.

  • REPEATABLE READ 隔離等級比 READ COMMITTED 嚴格。REPEATABLE READ is a more restrictive isolation level than READ COMMITTED. 它包含 READ COMMITTED,並額外指定任何其他交易都不可修改或刪除目前交易已讀取的資料,直到目前交易認可資料為止。It encompasses READ COMMITTED and additionally specifies that no other transactions can modify or delete data that has been read by the current transaction until the current transaction commits. 此時的並行存取較 READ COMMITTED 封閉,因為在異動期間會保持對讀取資料的共用鎖定,而不是在每個陳述式結束時釋放鎖定。Concurrency is lower than for READ COMMITTED because shared locks on read data are held for the duration of the transaction instead of being released at the end of each statement.

  • SERIALIZABLE 是最嚴格的隔離等級,因為它會鎖定整個範圍的索引鍵,並保持鎖定直到交易完成。SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. 它包含 REPEATABLE READ,並增加了在異動完成之前,其他異動不可在該異動已讀取的範圍內插入新資料列的限制。It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete.

如需詳細資訊,請參閱交易鎖定和資料列版本設定指南For more information, refer to the Transaction Locking and Row Versioning Guide.

快照隔離等級擴充Snapshot Isolation Level Extensions

SQL Server 在引進 SNAPSHOT 隔離等級的同時,還引進了 SQL-92 隔離等級的擴充及 READ COMMITTED 的其他實作。SQL Server introduced extensions to the SQL-92 isolation levels with the introduction of the SNAPSHOT isolation level and an additional implementation of READ COMMITTED. READ_COMMITTED_SNAPSHOT 隔離等級可透明化取代所有異動的 READ COMMITTED。The READ_COMMITTED_SNAPSHOT isolation level can transparently replace READ COMMITTED for all transactions.

  • SNAPSHOT 隔離指定交易內的資料讀取永遠不會反映其他同時進行之交易所進行的變更。SNAPSHOT isolation specifies that data read within a transaction will never reflect changes made by other simultaneous transactions. 異動使用異動開始時存在的資料列版本。The transaction uses the data row versions that exist when the transaction begins. 讀取資料時不會對資料設定鎖定,因此 SNAPSHOT 交易不會阻止其他交易寫入資料。No locks are placed on the data when it is read, so SNAPSHOT transactions do not block other transactions from writing data. 寫入資料的交易不會阻止快照集交易讀取資料。Transactions that write data do not block snapshot transactions from reading data. 您需要藉由設定 ALLOW_SNAPSHOT_ISOLATION 資料庫選項來啟用快照集隔離,以便使用該隔離。You need to enable snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option in order to use it.

  • READ_COMMITTED_SNAPSHOT 資料庫選項可判斷在資料庫中啟用快照集隔離時,預設 READ COMMITTED 隔離等級的行為。The READ_COMMITTED_SNAPSHOT database option determines the behavior of the default READ COMMITTED isolation level when snapshot isolation is enabled in a database. 如果您未明確指定 READ_COMMITTED_SNAPSHOT ON,則 READ COMMITTED 會套用至所有隱含交易。If you do not explicitly specify READ_COMMITTED_SNAPSHOT ON, READ COMMITTED is applied to all implicit transactions. 如此會產生與設定 READ_COMMITTED_SNAPSHOT OFF (預設值) 相同的行為。This produces the same behavior as setting READ_COMMITTED_SNAPSHOT OFF (the default). READ_COMMITTED_SNAPSHOT OFF 生效後,資料庫引擎會使用共用鎖定來強制執行預設的隔離等級。When READ_COMMITTED_SNAPSHOT OFF is in effect, the Database Engine uses shared locks to enforce the default isolation level. 如果將 READ_COMMITTED_SNAPSHOT 資料庫選項設為 ON,則資料庫引擎會使用資料列版本控制及快照集隔離做為預設值,而不是使用鎖定保護資料。If you set the READ_COMMITTED_SNAPSHOT database option to ON, the database engine uses row versioning and snapshot isolation as the default, instead of using locks to protect the data.

快照集隔離及資料列版本控制的運作方式How Snapshot Isolation and Row Versioning Work

啟用快照隔離等級時,每次更新資料列時,SQL Server 資料庫引擎會將原始資料列的複本儲存在tempdb中,並將交易序號加入資料列。When the SNAPSHOT isolation level is enabled, each time a row is updated, the SQL Server Database Engine stores a copy of the original row in tempdb, and adds a transaction sequence number to the row. 以下是事件的發生順序:The following is the sequence of events that occurs:

  • 初始新交易,並為其指派交易序號。A new transaction is initiated, and it is assigned a transaction sequence number.

  • 資料庫引擎會讀取交易中的資料列,並從其序號最接近且小於交易序號的tempdb抓取資料列版本。The Database Engine reads a row within the transaction and retrieves the row version from tempdb whose sequence number is closest to, and lower than, the transaction sequence number.

  • 資料庫引擎檢查該交易序號是否不在快照集交易開始時啟用之未認可交易的序號清單中。The Database Engine checks to see if the transaction sequence number is not in the list of transaction sequence numbers of the uncommitted transactions active when the snapshot transaction started.

  • 交易會從交易開始時的最新tempdb讀取資料列的版本。The transaction reads the version of the row from tempdb that was current as of the start of the transaction. 它不會看到異動開始後插入的新資料列,因為那些序號值會高於該異動序號值。It will not see new rows inserted after the transaction was started because those sequence number values will be higher than the value of the transaction sequence number.

  • 目前的交易將會看到在交易開始後刪除的資料列,因為tempdb中會有一個資料列版本,其序號值較低。The current transaction will see rows that were deleted after the transaction began, because there will be a row version in tempdb with a lower sequence number value.

快照集隔離的唯一影響是異動可看到異動開始時就存在的所有資料,而不會在基底資料表上接受或設定鎖定。The net effect of snapshot isolation is that the transaction sees all of the data as it existed at the start of the transaction, without honoring or placing any locks on the underlying tables. 在發生爭用時,這可提升效能。This can result in performance improvements in situations where there is contention.

快照集異動通常使用開放式同步存取控制,抑制任何可防止其他異動更新資料列的鎖定。A snapshot transaction always uses optimistic concurrency control, withholding any locks that would prevent other transactions from updating rows. 如果快照集異動嘗試認可對異動開始後發生變更之資料列的更新,則該異動會復原並將發生錯誤。If a snapshot transaction attempts to commit an update to a row that was changed after the transaction began, the transaction is rolled back, and an error is raised.

在 ADO.NET 中使用快照集隔離Working with Snapshot Isolation in ADO.NET

ADO.NET 中的 SqlTransaction 類別支援快照集隔離。Snapshot isolation is supported in ADO.NET by the SqlTransaction class. 如果資料庫已啟用快照集隔離,但未設定為在上進行 READ_COMMITTED_SNAPSHOT,您必須在呼叫 BeginTransaction 方法時,使用IsolationLevel來起始 SqlTransactionIf a database has been enabled for snapshot isolation but is not configured for READ_COMMITTED_SNAPSHOT ON, you must initiate a SqlTransaction using the IsolationLevel.Snapshot enumeration value when calling the BeginTransaction method. 此程式碼片段假設連接是開啟的 SqlConnection 物件。This code fragment assumes that connection is an open SqlConnection object.

Dim sqlTran As SqlTransaction = _  
  connection.BeginTransaction(IsolationLevel.Snapshot)  
SqlTransaction sqlTran =   
  connection.BeginTransaction(IsolationLevel.Snapshot);  

範例Example

下列範例說明不同隔離等級嘗試存取鎖定資料的行為方式,該範例不適用於實際執行的程式碼。The following example demonstrates how the different isolation levels behave by attempting to access locked data, and it is not intended to be used in production code.

程式碼會連接到 SQL Server 中的AdventureWorks範例資料庫,並建立名為TestSnapshot的資料表,並插入一個資料列。The code connects to the AdventureWorks sample database in SQL Server and creates a table named TestSnapshot and inserts one row of data. 該程式碼使用 ALTER DATABASE Transact-SQL 陳述式來啟用資料庫的快照集隔離,但不會設定 READ_COMMITTED_SNAPSHOT 選項,而是讓預設的 READ COMMITTED 隔離等級行為生效。The code uses the ALTER DATABASE Transact-SQL statement to turn on snapshot isolation for the database, but it does not set the READ_COMMITTED_SNAPSHOT option, leaving the default READ COMMITTED isolation-level behavior in effect. 然後,程式碼執行下列動作:The code then performs the following actions:

  • 它會開始但不完成 sqlTransaction1,其使用 SERIALIZABLE 隔離等級來啟動更新異動。It begins, but does not complete, sqlTransaction1, which uses the SERIALIZABLE isolation level to start an update transaction. 它的作用是鎖定資料表。This has the effect of locking the table.

  • 它會開啟第二個連接,並使用快照隔離等級來起始第二筆交易,以讀取TestSnapshot資料表中的資料。It opens a second connection and initiates a second transaction using the SNAPSHOT isolation level to read the data in the TestSnapshot table. 因為已啟用快照集隔離,所以此異動可讀取 sqlTransaction1 啟動之前存在的資料。Because snapshot isolation is enabled, this transaction can read the data that existed before sqlTransaction1 started.

  • 它會開啟第三個連接並初始使用 READ COMMITTED 隔離等級的交易,以嘗試讀取資料表中的資料。It opens a third connection and initiates a transaction using the READ COMMITTED isolation level to attempt to read the data in the table. 在這種情況下,程式碼無法讀取資料,因為第一個交易在資料表上設定的鎖定阻止其讀取資料並發生逾時。如果使用 REPEATABLE READ 及 SERIALIZABLE 隔離等級也會導致相同的結果,因為第一個交易設定的鎖定也會阻止這些隔離等級進行讀取。In this case, the code cannot read the data because it cannot read past the locks placed on the table in the first transaction and times out. The same result would occur if the REPEATABLE READ and SERIALIZABLE isolation levels were used because these isolation levels also cannot read past the locks placed in the first transaction.

  • 它會開啟第四個連接並初始使用 READ UNCOMMITTED 隔離等級的異動,其會執行對 sqlTransaction1 中未認可值的 Dirty 讀取。It opens a fourth connection and initiates a transaction using the READ UNCOMMITTED isolation level, which performs a dirty read of the uncommitted value in sqlTransaction1. 若未認可第一個異動,則資料庫實際上可能從未擁有此值。This value may never actually exist in the database if the first transaction is not committed.

  • 它會復原第一個交易,並藉由刪除TestSnapshot資料表並關閉AdventureWorks資料庫的快照集隔離來進行清除。It rolls back the first transaction and cleans up by deleting the TestSnapshot table and turning off snapshot isolation for the AdventureWorks database.

注意

下列範例在連接共用 (Connection Pooling) 關閉時會使用相同的連接字串。The following examples use the same connection string with connection pooling turned off. 如果共用連接,則重設其隔離等級並不會重設伺服器的隔離等級。If a connection is pooled, resetting its isolation level does not reset the isolation level at the server. 因此,使用同一共用內部連接的後續連接在啟動時的隔離等級會設定為共用連接的隔離等級。As a result, subsequent connections that use the same pooled inner connection start with their isolation levels set to that of the pooled connection. 關閉連接共用的方法之一,是明確地設定每個連接的隔離等級。An alternative to turning off connection pooling is to set the isolation level explicitly for each connection.

// 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!");
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;. 
Dim connectionString As String = GetConnectionString()

Using connection1 As New SqlConnection(connectionString)
    ' Drop the TestSnapshot table if it exists
    connection1.Open()
    Dim command1 As SqlCommand = connection1.CreateCommand
    command1.CommandText = "IF EXISTS " & _
    "(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') " _
      & "DROP TABLE TestSnapshot"
    Try
        command1.ExecuteNonQuery()
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

    ' Enable SNAPSHOT isolation 
    command1.CommandText = _
    "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON"
    command1.ExecuteNonQuery()

    ' Create a table named TestSnapshot and insert one row of data
    command1.CommandText = _
    "CREATE TABLE TestSnapshot (ID int primary key, valueCol int)"
    command1.ExecuteNonQuery()
    command1.CommandText = _
      "INSERT INTO TestSnapshot VALUES (1,1)"
    command1.ExecuteNonQuery()

    ' Begin, but do not complete, a transaction to update the data 
    ' with the Serializable isolation level, which locks the table
    ' pending the commit or rollback of the update. The original 
    ' value in valueCol was 1, the proposed new value is 22.
    Dim transaction1 As SqlTransaction = _
      connection1.BeginTransaction(IsolationLevel.Serializable)
    command1.Transaction = transaction1
    command1.CommandText = _
     "UPDATE TestSnapshot SET valueCol=22 WHERE ID=1"
    command1.ExecuteNonQuery()

    ' Open a second connection to AdventureWorks
    Dim connection2 As SqlConnection = New SqlConnection(connectionString)
    Using connection2
        connection2.Open()

        ' Initiate a second transaction to read from TestSnapshot
        ' using Snapshot isolation. This will read the original 
        ' value of 1 since transaction1 has not yet committed.
        Dim command2 As SqlCommand = connection2.CreateCommand()
        Dim transaction2 As SqlTransaction = _
          connection2.BeginTransaction(IsolationLevel.Snapshot)
        command2.Transaction = transaction2
        command2.CommandText = _
            "SELECT ID, valueCol FROM TestSnapshot"
        Dim reader2 As SqlDataReader = _
            command2.ExecuteReader()
        While reader2.Read()
            Console.WriteLine("Expected 1,1 Actual " _
              & reader2.GetValue(0).ToString() + "," _
              & reader2.GetValue(1).ToString())
        End While
        transaction2.Commit()
    End Using

    ' Open a third connection to AdventureWorks and
    ' initiate a third transaction to read from TestSnapshot
    ' using the ReadCommitted isolation level. This transaction
    ' will not be able to view the data because of 
    ' the locks placed on the table in transaction1
    ' and will time out after 4 seconds.
    ' You would see the same behavior with the
    ' RepeatableRead or Serializable isolation levels.
    Dim connection3 As SqlConnection = New SqlConnection(connectionString)
    Using connection3
        connection3.Open()
        Dim command3 As SqlCommand = connection3.CreateCommand()
        Dim transaction3 As SqlTransaction = _
            connection3.BeginTransaction(IsolationLevel.ReadCommitted)
        command3.Transaction = transaction3
        command3.CommandText = _
            "SELECT ID, valueCol FROM TestSnapshot"
        command3.CommandTimeout = 4

        Try
            Dim reader3 As SqlDataReader = command3.ExecuteReader()
            While reader3.Read()
                Console.WriteLine("You should never hit this.")
            End While
            transaction3.Commit()
        Catch ex As Exception
            Console.WriteLine("Expected timeout expired exception: " _
              & ex.Message)
            transaction3.Rollback()
        End Try
    End Using

    ' Open a fourth connection to AdventureWorks and
    ' initiate a fourth transaction to read from TestSnapshot
    ' using the ReadUncommitted isolation level. ReadUncommitted
    ' will not hit the table lock, and will allow a dirty read  
    ' of the proposed new value 22. If the first transaction 
    ' transaction rolls back, this value will never actually have
    ' existed in the database.
    Dim connection4 As SqlConnection = New SqlConnection(connectionString)
    Using connection4
        connection4.Open()
        Dim command4 As SqlCommand = connection4.CreateCommand()
        Dim transaction4 As SqlTransaction = _
          connection4.BeginTransaction(IsolationLevel.ReadUncommitted)
        command4.Transaction = transaction4
        command4.CommandText = _
            "SELECT ID, valueCol FROM TestSnapshot"
        Dim reader4 As SqlDataReader = _
            command4.ExecuteReader()
        While reader4.Read()
            Console.WriteLine("Expected 1,22 Actual " _
              & reader4.GetValue(0).ToString() _
              & "," + reader4.GetValue(1).ToString())
        End While
        transaction4.Commit()

        ' Rollback transaction1
        transaction1.Rollback()
    End Using
End Using

' CLEANUP
' Drop TestSnapshot table and set
' ALLOW_SNAPSHOT_ISOLATION OFF for AdventureWorks
Dim connection5 As New SqlConnection(connectionString)
Using connection5
    connection5.Open()
    Dim command5 As SqlCommand = connection5.CreateCommand()
    command5.CommandText = "DROP TABLE TestSnapshot"
    Dim command6 As SqlCommand = connection5.CreateCommand()
    command6.CommandText = _
   "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
    Try
        command5.ExecuteNonQuery()
        command6.ExecuteNonQuery()
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try
End Using

範例Example

下列範例說明修改資料時快照集隔離的行為。The following example demonstrates the behavior of snapshot isolation when data is being modified. 該程式碼執行下列動作:The code performs the following actions:

  • 連接到AdventureWorks範例資料庫,並啟用快照集隔離。Connects to the AdventureWorks sample database and enables SNAPSHOT isolation.

  • 建立名為TestSnapshotUpdate的資料表,並插入三個數據列的範例資料。Creates a table named TestSnapshotUpdate and inserts three rows of sample data.

  • 開始使用 SNAPSHOT 隔離的 sqlTransaction1 但不完成它。Begins, but does not complete, sqlTransaction1 using SNAPSHOT isolation. 在交易中選取三個資料列。Three rows of data are selected in the transaction.

  • 建立第二個SqlConnectionAdventureWorks ,並使用讀取認可隔離等級建立第二筆交易,以更新 sqlTransaction1 中所選其中一個資料列的值。Creates a second SqlConnection to AdventureWorks and creates a second transaction using the READ COMMITTED isolation level that updates a value in one of the rows selected in sqlTransaction1.

  • 認可 sqlTransaction2。Commits sqlTransaction2.

  • 返回 sqlTransaction1 並嘗試更新 sqlTransaction1 已認可的同一資料列。Returns to sqlTransaction1 and attempts to update the same row that sqlTransaction1 already committed. 發生錯誤 3960,sqlTransaction1 自動復原。Error 3960 is raised, and sqlTransaction1 is rolled back automatically. [ SqlException ] 和 [ SqlException ] 會顯示在主控台視窗中。The SqlException.Number and SqlException.Message are displayed in the Console window.

  • 執行清除程式碼以關閉AdventureWorks中的快照集隔離,並刪除TestSnapshotUpdate資料表。Executes clean-up code to turn off snapshot isolation in AdventureWorks and delete the TestSnapshotUpdate table.

// 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);
    }
}
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;. 
Dim connectionString As String = GetConnectionString()

Using connection1 As New SqlConnection(connectionString)
    ' Enable Snapshot isolation in AdventureWorks
    connection1.Open()
    Dim command1 As SqlCommand = connection1.CreateCommand
    command1.CommandText = _
   "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;"
    Try
        command1.ExecuteNonQuery()
        Console.WriteLine( _
         "Snapshot Isolation turned on in AdventureWorks.")
    Catch ex As Exception
        Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION failed: {0}", ex.Message)
    End Try

    ' Create a table
    command1.CommandText = _
      "IF EXISTS (SELECT * FROM sys.databases " _
      & "WHERE name=N'TestSnapshotUpdate') " _
      & "DROP TABLE TestSnapshotUpdate"
    command1.ExecuteNonQuery()
    command1.CommandText = _
      "CREATE TABLE TestSnapshotUpdate (ID int primary key, " _
      & "CharCol nvarchar(100));"
    Try
        command1.ExecuteNonQuery()
        Console.WriteLine("TestSnapshotUpdate table created.")
    Catch ex As Exception
        Console.WriteLine("CREATE TABLE failed: {0}", ex.Message)
    End Try

    ' Insert some data
    command1.CommandText = _
      "INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');" _
      & "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');" _
      & "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');"
    Try
        command1.ExecuteNonQuery()
        Console.WriteLine("Data inserted TestSnapshotUpdate table.")
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

    ' Begin, but do not complete, a transaction
    ' using the Snapshot isolation level
    Dim transaction1 As SqlTransaction = Nothing
    Try
        transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot)
        command1.CommandText = _
          "SELECT * FROM TestSnapshotUpdate WHERE ID " _
          & "BETWEEN 1 AND 3"
        command1.Transaction = transaction1
        command1.ExecuteNonQuery()
        Console.WriteLine("Snapshot transaction1 started.")

        ' Open a second Connection/Transaction to update data
        ' using ReadCommitted. This transaction should succeed.
        Dim connection2 As SqlConnection = New SqlConnection(connectionString)
        Using connection2
            connection2.Open()
            Dim command2 As SqlCommand = connection2.CreateCommand()
            command2.CommandText = "UPDATE TestSnapshotUpdate SET " _
              & "CharCol=N'New value from Connection2' WHERE ID=1"
            Dim transaction2 As SqlTransaction = _
              connection2.BeginTransaction(IsolationLevel.ReadCommitted)
            command2.Transaction = transaction2
            Try
                command2.ExecuteNonQuery()
                transaction2.Commit()
                Console.WriteLine( _
                  "transaction2 has modified data and committed.")
            Catch ex As SqlException
                Console.WriteLine(ex.Message)
                transaction2.Rollback()
            Finally
                transaction2.Dispose()
            End Try
        End Using

        ' Now try to update a row in Connection1/Transaction1.
        ' This transaction should fail because Transaction2
        ' succeeded in modifying the data.
        command1.CommandText = _
          "UPDATE TestSnapshotUpdate SET CharCol=" _
            & "N'New value from Connection1' WHERE ID=1"
        command1.Transaction = transaction1
        command1.ExecuteNonQuery()
        transaction1.Commit()
        Console.WriteLine("You should never see this.")

    Catch ex As SqlException
        Console.WriteLine("Expected failure for transaction1:")
        Console.WriteLine("  {0}: {1}", ex.Number, ex.Message)
    Finally
        transaction1.Dispose()
    End Try
End Using

' CLEANUP:
' Turn off Snapshot isolation and delete the table
Dim connection3 As New SqlConnection(connectionString)
Using connection3
    connection3.Open()
    Dim command3 As SqlCommand = connection3.CreateCommand()
    command3.CommandText = _
  "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
    Try
        command3.ExecuteNonQuery()
        Console.WriteLine( _
         "Snapshot isolation turned off in AdventureWorks.")
    Catch ex As Exception
        Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
    End Try

    command3.CommandText = "DROP TABLE TestSnapshotUpdate"
    Try
        command3.ExecuteNonQuery()
        Console.WriteLine("TestSnapshotUpdate table deleted.")
    Catch ex As Exception
        Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
    End Try
End Using

搭配使用鎖定提示與快照集隔離Using Lock Hints with Snapshot Isolation

在前一範例中,第一筆異動選取了資料,而第二筆異動在第一筆異動尚未完成時就更新該資料,因而在第一筆異動嘗試更新同一資料列時發生更新衝突。In the previous example, the first transaction selects data, and a second transaction updates the data before the first transaction is able to complete, causing an update conflict when the first transaction tries to update the same row. 藉由在異動開始時提供鎖定提示,可減少長期執行之快照集異動發生更新衝突的機率。You can reduce the chance of update conflicts in long-running snapshot transactions by supplying lock hints at the beginning of the transaction. 下列 SELECT 陳述式使用 UPDLOCK 提示來鎖定已選取的資料列:The following SELECT statement uses the UPDLOCK hint to lock the selected rows:

SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)   
  WHERE PriKey BETWEEN 1 AND 3  

使用 UPDLOCK 鎖定提示可阻止任何資料列在第一筆交易完成之前嘗試更新資料列。Using the UPDLOCK lock hint blocks any rows attempting to update the rows before the first transaction completes. 如此可確保異動中在稍後更新選取的資料列時不會發生衝突。This guarantees that the selected rows have no conflicts when they are updated later in the transaction. 請參閱《SQL Server 線上叢書》的<鎖定提示>。See "Locking Hints" in SQL Server Books Online.

如果應用程式有許多衝突,快照集隔離也許並非是最佳選擇。If your application has many conflicts, snapshot isolation may not be the best choice. 僅在確實需要時才使用提示。Hints should only be used when really needed. 您的應用程式不應設計為必須依賴鎖定提示來執行作業。Your application should not be designed so that it constantly relies on lock hints for its operation.

另請參閱See also