Isolasi Snapshot dalam SQL Server

Isolasi rekam jepret meningkatkan konkurensi untuk aplikasi OLTP.

Memahami Isolasi Rekam Jepret dan Penerapan Versi Baris

Setelah isolasi rekam jepret diaktifkan, versi baris yang diperbarui untuk setiap transaksi harus dipertahankan. Sebelum SQL Server 2019, versi ini disimpan dalam tempdb. SQL Server 2019 memperkenalkan fitur baru, Accelerated Database Recovery (ADR) yang memerlukan serangkaian versi barisnya sendiri. Jadi, pada SQL Server 2019, jika ADR tidak diaktifkan, versi baris disimpan dalam tempdb seperti biasa. Jika ADR diaktifkan, maka semua versi baris, baik yang terkait dengan isolasi rekam jepret maupun ADR, disimpan di Penyimpanan Versi Persisten (Persistent Version Store atau PVS) ADR, yang terletak di database pengguna dalam grup file yang ditentukan pengguna. Nomor urutan transaksi unik mengidentifikasi setiap transaksi, dan angka unik ini dicatat untuk setiap versi baris. Transaksi bekerja dengan versi baris terbaru yang memiliki nomor urut sebelum nomor urut transaksi. Versi baris yang lebih baru dibuat setelah transaksi dimulai diabaikan oleh transaksi tersebut.

Istilah "rekam jepret" mencerminkan fakta bahwa semua kueri dalam transaksi melihat versi yang sama, atau rekam jepret, database, berdasarkan status database pada saat transaksi dimulai. Tidak ada kunci yang diperoleh pada baris data atau halaman data yang mendasar dalam transaksi rekam jepret, yang memungkinkan transaksi lain dijalankan tanpa terblokir oleh transaksi sebelumnya yang belum selesai. Transaksi yang memodifikasi data tidak memblokir transaksi yang membaca data, dan transaksi yang membaca data tidak memblokir transaksi yang menulis data, tidak sebagaimana akan mereka blokir ketika menggunakan tingkat isolasi READ COMMITTED default di SQL Server. Perilaku non-blokir ini juga secara signifikan mengurangi kemungkinan kebuntuan untuk transaksi yang kompleks.

Isolasi rekam jepret menggunakan model konkurensi optimis. Jika transaksi rekam jepret mencoba melakukan modifikasi pada data yang telah berubah sejak transaksi dimulai, maka transaksi tersebut akan mengulang balik dan kesalahan akan muncul. Anda dapat menghindari hal ini dengan menggunakan petunjuk UPDLOCK untuk pernyataan SELECT yang mengakses data yang akan dimodifikasi. Untuk informasi selengkapnya, lihat Petunjuk (Transact-SQL).

Isolasi rekam jepret harus diaktifkan dengan mengatur opsi database ALLOW_SNAPSHOT_ISOLATION ON sebelum digunakan dalam transaksi. Ini mengaktifkan mekanisme untuk menyimpan versi baris dalam database sementara (tempdb). Anda harus mengaktifkan isolasi rekam jepret di setiap database yang menggunakannya dengan pernyataan TRANSACT-SQL ALTER DATABASE. Dalam hal ini, isolasi rekam jepret berbeda dari tingkat isolasi tradisional READ COMMITTED, REPEATABLE READ, SERIALIZABLE, dan READ UNCOMMITTED, yang tidak memerlukan konfigurasi. Pernyataan berikut mengaktifkan isolasi rekam jepret dan mengganti perilaku READ COMMITTED default dengan SNAPSHOT:

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

Mengatur opsi READ_COMMITTED_SNAPSHOT AKTIF memungkinkan akses ke baris berversi di bawah tingkat isolasi READ COMMITTED default. Jika opsi READ_COMMITTED_SNAPSHOT diatur ke NONAKTIF, Anda harus secara eksplisit mengatur tingkat isolasi Snapshot untuk setiap sesi untuk mengakses baris versi.

Mengelola Konkurensi dengan Tingkat Isolasi

Tingkat isolasi di mana pernyataan Transact-SQL dijalankan menentukan perilaku penguncian dan penerapan versi barisnya. Tingkat isolasi memiliki cakupan di seluruh koneksi, dan setelah diatur untuk koneksi dengan pernyataan SET TRANSACTION ISOLATION LEVEL, akan tetap berlaku sampai koneksi ditutup atau ditetapkan tingkat isolasi lain. Ketika koneksi ditutup dan dikembalikan ke kumpulan, tingkat isolasi dari pernyataan SET TRANSACTION ISOLATION LEVEL terakhir dipertahankan. Koneksi berikutnya yang menggunakan kembali koneksi terkumpul menggunakan tingkat isolasi yang berlaku pada saat koneksi dikumpulkan.

Kueri individual yang dikeluarkan dalam koneksi dapat berisi petunjuk kunci yang memodifikasi isolasi untuk satu pernyataan atau transaksi tetapi tidak memengaruhi tingkat isolasi koneksi. Tingkat isolasi atau petunjuk kunci yang diatur dalam prosedur atau fungsi tersimpan tidak mengubah tingkat isolasi koneksi yang memanggilnya dan hanya berlaku selama prosedur tersimpan atau panggilan fungsi.

Empat tingkat isolasi yang ditentukan dalam standar SQL-92 didukung dalam versi awal SQL Server:

  • READ UNCOMMITTED adalah tingkat isolasi yang paling longgar karena mengabaikan kunci yang ditempatkan oleh transaksi lain. Transaksi yang dijalankan di bawah READ UNCOMMITTED dapat membaca nilai data yang dimodifikasi yang belum dilakukan oleh transaksi lain; ini disebut pembacaan "kotor".

  • READ COMMITTED merupakan tingkat isolasi default untuk SQL Server. Ini mencegah pembacaan kotor dengan menentukan bahwa pernyataan tidak dapat membaca nilai data yang telah dimodifikasi tetapi belum dilakukan oleh transaksi lain. Transaksi lain masih dapat memodifikasi, menyisipkan, atau menghapus data antara eksekusi pernyataan individual dalam transaksi saat ini, menghasilkan data baca yang tidak dapat diulang, atau "phantom".

  • REPEATABLE READ adalah tingkat isolasi yang lebih ketat daripada READ COMMITTED. Ini mencakup READ COMMITTED dan juga menentukan bahwa tidak ada transaksi lain yang dapat memodifikasi atau menghapus data yang telah dibaca oleh transaksi saat ini hingga transaksi saat ini menerapkannya. Konkurensi lebih rendah daripada untuk READ COMMITTED karena kunci bersama pada data baca disimpan selama durasi transaksi alih-alih dirilis di akhir setiap pernyataan.

  • SERIALIZABLE adalah tingkat isolasi yang paling ketat, karena mengunci seluruh rentang kunci dan menahan kunci hingga transaksi selesai. Ini mencakup REPEATABLE READ dan menambahkan batasan bahwa transaksi lain tidak dapat menyisipkan baris baru ke dalam rentang yang telah dibaca oleh transaksi hingga transaksi selesai.

Untuk informasi selengkapnya, lihat Panduan Penguncian Transaksi dan Penerapan Versi Baris.

Ekstensi Tingkat Isolasi Rekam Jepret

SQL Server memperkenalkan ekstensi ke tingkat isolasi SQL-92 dengan pengenalan tingkat isolasi SNAPSHOT dan implementasi tambahan dari READ COMMITTED. Tingkat isolasi READ_COMMITTED_SNAPSHOT dapat secara transparan menggantikan READ COMMITTED untuk semua transaksi.

  • Isolasi SNAPSHOT menentukan bahwa data yang dibaca dalam transaksi tidak akan pernah mencerminkan perubahan yang dilakukan oleh transaksi simultan lainnya. Transaksi menggunakan versi baris data yang ada saat transaksi dimulai. Tidak ada kunci yang ditempatkan pada data saat dibaca, sehingga transaksi SNAPSHOT tidak memblokir transaksi lain untuk menulis data. Transaksi yang menulis data tidak memblokir transaksi rekam jepret agar tidak membaca data. Anda perlu mengaktifkan isolasi rekam jepret dengan mengatur opsi database ALLOW_SNAPSHOT_ISOLATION untuk menggunakannya.

  • Opsi database READ_COMMITTED_SNAPSHOT menentukan perilaku tingkat isolasi READ COMMITTED default saat isolasi rekam jepret diaktifkan dalam database. Jika Anda tidak secara eksplisit menentukan READ_COMMITTED_SNAPSHOT AKTIF, READ COMMITTED diterapkan ke semua transaksi implisit. Maka akan menghasilkan perilaku yang sama dengan pengaturan READ_COMMITTED_SNAPSHOT NONAKTIF (default). Saat READ_COMMITTED_SNAPSHOT NONAKTIF berlaku, Mesin Database menggunakan kunci bersama untuk memberlakukan tingkat isolasi default. Jika Anda mengatur opsi database READ_COMMITTED_SNAPSHOT ke AKTIF, mesin database menggunakan penerapan versi baris dan isolasi rekam jepret sebagai default, alih-alih menggunakan kunci untuk melindungi data.

Cara Kerja Isolasi Rekam Jepret dan Penerapan Versi Baris

Ketika tingkat isolasi SNAPSHOT diaktifkan, setiap kali baris diperbarui, mesin database SQL Server menyimpan salinan baris asli dalam tempdb, dan menambahkan nomor urutan transaksi ke baris tersebut. Berikut ini adalah urutan peristiwa yang terjadi:

  • Transaksi baru dimulai, dan diberi nomor urut transaksi.

  • Mesin Database membaca baris dalam transaksi dan mengambil versi baris dari tempdb yang nomor urutannya paling dekat dengan, dan lebih rendah dari, nomor urutan transaksi.

  • Mesin Database memeriksa untuk melihat apakah nomor urutan transaksi tidak ada dalam daftar nomor urutan transaksi dari transaksi yang tidak dilakukan aktif saat transaksi rekam jepret dimulai.

  • Transaksi membaca versi baris dari tempdb yang saat ini berada pada awal transaksi. Transaksi ini tidak akan melihat baris baru disisipkan setelah transaksi dimulai karena nilai nomor urut tersebut akan lebih tinggi dari nilai nomor urut transaksi.

  • Transaksi saat ini akan melihat baris yang dihapus setelah transaksi dimulai, karena akan ada versi baris di tempdb dengan nilai angka urutan yang lebih rendah.

Efek bersih dari isolasi rekam jepret adalah bahwa transaksi melihat semua data seperti yang ada di awal transaksi, tanpa menghiraukan atau menempatkan kunci apa pun pada tabel yang mendasarinya. Ini dapat menghasilkan peningkatan performa dalam situasi ketika terjadi ketidakcocokan.

Transaksi rekam jepret selalu menggunakan kontrol konkurensi optimis, menahan kunci apa pun yang akan mencegah transaksi lain memperbarui baris. Jika transaksi rekam jepret mencoba melakukan pembaruan ke baris yang diubah setelah transaksi dimulai, transaksi akan mengulang balik, dan kesalahan muncul.

Bekerja dengan Isolasi Rekam Jepret di ADO.NET

Isolasi rekam jepret didukung di ADO.NET oleh kelas SqlTransaction. Jika database telah diaktifkan untuk isolasi rekam jepret tetapi tidak dikonfigurasi untuk READ_COMMITTED_SNAPSHOT AKTIF, Anda harus memulai SqlTransaction menggunakan nilai enumerasi IsolationLevel.Snapshot saat memanggil metode BeginTransaction. Fragmen kode ini mengasumsikan bahwa koneksi adalah objek SqlConnection terbuka.

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

Contoh

Contoh berikut menunjukkan bagaimana tingkat isolasi yang berbeda bereaksi dengan mencoba mengakses data yang terkunci, dan tidak dimaksudkan untuk digunakan dalam kode produksi.

Kode ini tersambung ke database sampel AdventureWorks di SQL Server dan membuat tabel bernama TestSnapshot dan menyisipkan satu baris data. Kode ini menggunakan pernyataan ALTER DATABASE Transact-SQL untuk mengaktifkan isolasi rekam jepret untuk database, tetapi tidak mengatur opsi READ_COMMITTED_SNAPSHOT, membiarkan perilaku tingkat isolasi READ COMMITTED default berlaku. Kode kemudian melakukan tindakan berikut:

  • Kode memulai, tetapi tidak selesai, sqlTransaction1, yang menggunakan tingkat isolasi SERIALIZABLE untuk memulai transaksi pembaruan. Hal ini berefek pada penguncian tabel.

  • Kode ini membuka koneksi kedua dan memulai transaksi kedua menggunakan tingkat isolasi SNAPSHOT untuk membaca data dalam tabel TestSnapshot. Karena isolasi rekam jepret diaktifkan, transaksi ini dapat membaca data yang ada sebelum sqlTransaction1 dimulai.

  • Kode ini membuka koneksi ketiga dan memulai transaksi menggunakan tingkat isolasi READ COMMITTED untuk mencoba membaca data dalam tabel. Dalam hal ini, kode tidak dapat membaca data karena tidak dapat membaca melewati kunci yang ditempatkan pada tabel dalam transaksi pertama dan waktu habis. Hasil yang sama akan terjadi jika tingkat isolasi REPEATABLE READ dan SERIALIZABLE digunakan karena tingkat isolasi ini juga tidak dapat membaca melewati kunci yang ditempatkan dalam transaksi pertama.

  • Kode ini membuka koneksi keempat dan memulai transaksi menggunakan tingkat isolasi READ UNCOMMITTED, yang melakukan pembacaan kotor dari nilai yang tidak di-komit pada sqlTransaction1. Nilai ini mungkin tidak pernah benar-benar ada dalam database jika transaksi pertama tidak dilakukan.

  • Kode ini mengembalikan transaksi pertama dan membersihkan dengan menghapus tabel TestSnapshot dan menonaktifkan isolasi rekam jepret untuk database AdventureWorks.

Catatan

Contoh berikut menggunakan string koneksi yang sama dengan pengumpulan koneksi dinonaktifkan. Jika koneksi dikumpulkan, mengatur ulang tingkat isolasinya tidak mereset tingkat isolasi di server. Akibatnya, koneksi berikutnya yang menggunakan koneksi dalam terkumpul yang sama dimulai dengan tingkat isolasi mereka yang diatur ke koneksi terkumpul. Alternatif untuk menonaktifkan pengumpulan koneksi adalah mengatur tingkat isolasi secara eksplisit untuk setiap koneksi.

// 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

Contoh

Contoh berikut menunjukkan perilaku isolasi rekam jepret saat data sedang dimodifikasi. Kode melakukan tindakan berikut:

  • Menyambungkan ke database sampel AdventureWorks dan mengaktifkan isolasi SNAPSHOT.

  • Membuat tabel bernama TestSnapshotUpdate dan menyisipkan tiga baris data sampel.

  • Memulai, tetapi tidak selesai, sqlTransaction1 menggunakan isolasi SNAPSHOT. Tiga baris data dipilih dalam transaksi itu.

  • Membuat SqlConnection kedua ke AdventureWorks dan membuat transaksi kedua menggunakan tingkat isolasi READ COMMITTED yang memperbarui nilai di salah satu baris yang dipilih di sqlTransaction1.

  • Menerapkan sqlTransaction2.

  • Kembali ke sqlTransaction1 dan mencoba memperbarui baris yang sama dengan yang sudah dilakukan sqlTransaction1. Kesalahan 3960 muncul, dan sqlTransaction1 digulung balik secara otomatis. SqlException.Number dan SqlException.Message ditampilkan di jendela Konsol.

  • Menjalankan kode pembersihan untuk menonaktifkan isolasi rekam jepret di AdventureWorks dan menghapus tabel TestSnapshotUpdate.

// 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

Menggunakan Petunjuk Kunci dengan Isolasi Rekam Jepret

Dalam contoh sebelumnya, transaksi pertama memilih data, dan transaksi kedua memperbarui data sebelum transaksi pertama dapat diselesaikan, menyebabkan konflik pembaruan ketika transaksi pertama mencoba memperbarui baris yang sama. Anda dapat mengurangi kemungkinan konflik pembaruan dalam transaksi rekam jepret yang berjalan lama dengan menyediakan petunjuk kunci di awal transaksi. Pernyataan SELECT berikut menggunakan petunjuk UPDLOCK untuk mengunci baris yang dipilih:

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

Menggunakan petunjuk kunci UPDLOCK memblokir baris apa pun yang mencoba memperbarui baris sebelum transaksi pertama selesai. Ini menjamin bahwa baris yang dipilih tidak memiliki konflik ketika diperbarui nanti dalam transaksi itu. Untuk informasi selengkapnya, lihat Petunjuk (Transact-SQL).

Jika aplikasi Anda memiliki banyak konflik, isolasi rekam jepret mungkin bukan pilihan terbaik. Petunjuk hanya boleh digunakan ketika benar-benar diperlukan. Aplikasi Anda tidak boleh dirancang semacam ini, yang bisa berakibat aplikasinya terus-menerus bergantung pada petunjuk kunci untuk operasinya.

Lihat juga