Bahasa

Isolasi Rekam Jepret di 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-versi ini disimpan di tempdb. SQL Server 2019 memperkenalkan fitur baru, Accelerated Database Recovery (ADR) yang memerlukan set versi barisnya sendiri. Jadi, pada SQL Server 2019, jika ADR tidak diaktifkan, versi baris disimpan seperti tempdb biasa. Jika ADR diaktifkan, maka semua versi baris, baik yang terkait dengan isolasi rekam jepret maupun ADR, disimpan di Penyimpanan Versi Persisten (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, yang dibuat setelah transaksi dimulai, diabaikan oleh transaksi.

Istilah "rekam jepret" mencerminkan fakta bahwa semua kueri dalam transaksi melihat versi yang sama, atau rekam jepret, dari 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 diblokir 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, seperti biasanya di bawah tingkat isolasi default READ COMMITTED dalam SQL Server. Perilaku non-pemblokiran ini juga secara signifikan mengurangi kemungkinan kebuntuan untuk transaksi yang kompleks.

Isolasi rekam jepret menggunakan model konkurensi optimis. Jika transaksi snapshot mencoba melakukan modifikasi pada data yang telah berubah sejak transaksi dimulai, transaksi akan dilakukan rollback dan akan muncul kesalahan. Anda dapat menghindari hal ini dengan menggunakan hint UPDLOCK pada statement SELECT yang mengakses data yang akan dimodifikasi. Untuk informasi selengkapnya, lihat petunjuk (Transact-SQL).

Isolasi snapshot 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, snapshot isolation berbeda dari tingkat isolasi tradisional READ COMMITTED, REPEATABLE READ, SERIALIZABLE, dan READ UNCOMMITTED, yang tidak memerlukan konfigurasi. Pernyataan berikut memungkinkan transaksi SNAPSHOT dan operasi baca READ COMMITTED berversi baris:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

Mengatur opsi READ_COMMITTED_SNAPSHOT ON memungkinkan akses ke baris berversi di bawah tingkat isolasi default READ COMMITTED. Jika opsi READ_COMMITTED_SNAPSHOT disetel ke OFF, 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 berlaku untuk seluruh koneksi, dan setelah ditetapkan untuk suatu koneksi dengan pernyataan SET TRANSACTION ISOLATION LEVEL, tingkat tersebut tetap berlaku hingga koneksi ditutup atau tingkat isolasi lain ditetapkan. Ketika koneksi ditutup dan dikembalikan ke kumpulan, tingkat isolasi dari pernyataan terakhir SET TRANSACTION ISOLATION LEVEL 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 mengubah 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 tidak ketat karena mengabaikan kunci yang ditempatkan oleh transaksi lain. Transaksi yang dijalankan di bawah READ UNCOMMITTED dapat membaca nilai data yang dimodifikasi yang belum dikomitkan oleh transaksi lain; ini disebut bacaan "kotor".

  • READ COMMITTEDadalah 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 di antara eksekusi perintah individual dalam transaksi saat ini, mengakibatkan pembacaan yang tidak berulang, atau data "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 dikomit. Konkurensi lebih rendah daripada untuk READ COMMITTED karena kunci bersama pada data yang dibaca 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 pembatasan bahwa transaksi lain tidak dapat memasukkan 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 diperkenalkannya tingkat isolasi SNAPSHOT dan implementasi tambahan READ COMMITTED.

  • SNAPSHOT tingkat isolasi menentukan bahwa data yang dibaca dalam transaksi tidak akan pernah mencerminkan perubahan yang dilakukan oleh transaksi lain yang berjalan secara bersamaan. 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 snapshot untuk membaca data. Anda perlu mengaktifkan snapshot isolation dengan mengatur opsi database ALLOW_SNAPSHOT_ISOLATION untuk menggunakannya.

  • Opsi READ_COMMITTED_SNAPSHOT database mengontrol cara READ COMMITTED membaca data. Dengan opsi OFFini , default dalam SQL Server, Database Engine menggunakan kunci bersama untuk melindungi bacaan. Dengan opsi ON, READ COMMITTED menggunakan versi baris pada tingkat pernyataan alih-alih kunci bersama untuk melindungi operasi baca.

Cara Kerja Isolasi Snapshot dan Pembuatan Versi Baris

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

  • Transaksi baru dimulai, dan diberi nomor urutan transaksi.

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

  • Mesin Database memeriksa untuk memastikan apakah nomor urutan transaksi tidak ada dalam daftar nomor urutan transaksi dari transaksi yang belum selesai dan aktif ketika transaksi snapshot dimulai.

  • Transaksi membaca versi baris dari tempdb yang berlaku pada awal transaksi. Ini tidak akan melihat baris baru dimasukkan setelah transaksi dimulai karena nilai nomor urutan tersebut akan lebih tinggi dari nilai nomor urutan 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 menghormati atau menempatkan kunci apa pun pada tabel yang mendasarinya. Hal ini dapat mengakibatkan peningkatan kinerja dalam situasi di mana ada persaingan.

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 digulung balik, dan kesalahan dimunculkan.

Bekerja dengan Isolasi Potret di ADO.NET

Untuk memulai transaksi dengan isolasi snapshot, aktifkan ALLOW_SNAPSHOT_ISOLATION untuk database dan teruskan Snapshot ke BeginTransaction. Opsi READ_COMMITTED_SNAPSHOT database mengontrol bacaan berversi baris pada tingkat isolasi read-committed; opsi tersebut tidak memilih snapshot isolation untuk transaksi. Fragmen berikut mengasumsikan bahwa connection adalah instans yang terbuka dari kelas SqlConnection.

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

  • Ini memulai, tetapi tidak menyelesaikan, sqlTransaction1, yang menggunakan tingkat isolasi SERIALIZABLE untuk memulai transaksi pembaruan. Ini memiliki efek mengunci tabel.

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

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

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

  • Ini mengembalikan transaksi pertama dan membersihkannya dengan menghapus tabel TestSnapshot dan menonaktifkan isolasi rekam jepret untuk basis data AdventureWorks.

Nota

Contoh berikut menggunakan string koneksi yang sama dengan pengumpulan koneksi dinonaktifkan. Jika koneksi dikumpulkan, mengatur ulang tingkat isolasinya tidak mengatur ulang tingkat isolasi di server. Akibatnya, koneksi berikutnya yang menggunakan koneksi internal pada kumpulan yang sama akan memulai dengan tingkat isolasi yang sama seperti yang ditetapkan untuk koneksi terkumpul tersebut. 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;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(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(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";
        using (SqlDataReader reader2 = command2.ExecuteReader())
        {
            while (reader2.Read())
            {
                Console.WriteLine("Expected 1,1 Actual "
                    + reader2.GetValue(0)
                    + "," + reader2.GetValue(1));
            }
        }
        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(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
        {
            using (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(connectionString))
    {
        connection4.Open();
        SqlCommand command4 = connection4.CreateCommand();
        SqlTransaction transaction4 =
            connection4.BeginTransaction(IsolationLevel.ReadUncommitted);
        command4.Transaction = transaction4;
        command4.CommandText =
            "SELECT ID, valueCol FROM TestSnapshot";
        using (SqlDataReader reader4 = command4.ExecuteReader())
        {
            while (reader4.Read())
            {
                Console.WriteLine("Expected 1,22 Actual "
                    + reader4.GetValue(0)
                    + "," + reader4.GetValue(1));
            }
        }

        transaction4.Commit();
    }

    // Roll back the first transaction
    transaction1.Rollback();
}

// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new(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"
        Using reader2 As SqlDataReader = command2.ExecuteReader()
            While reader2.Read()
                Console.WriteLine("Expected 1,1 Actual " _
                  & reader2.GetValue(0).ToString() + "," _
                  & reader2.GetValue(1).ToString())
            End While
        End Using
        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
            Using reader3 As SqlDataReader = command3.ExecuteReader()
                While reader3.Read()
                    Console.WriteLine("You should never hit this.")
                End While
            End Using
            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"
        Using reader4 As SqlDataReader = command4.ExecuteReader()
            While reader4.Read()
                Console.WriteLine("Expected 1,22 Actual " _
                  & reader4.GetValue(0).ToString() _
                  & "," + reader4.GetValue(1).ToString())
            End While
        End Using
        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 AdventureWorks database sampel dan mengaktifkan SNAPSHOT isolasi.

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

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

  • 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.

  • Komit sqlTransaction2.

  • Kembali ke sqlTransaction1 dan mencoba memperbarui baris yang sama yang sudah di-commit oleh sqlTransaction1. Kesalahan 3960 muncul, dan sqlTransaction1 dibatalkan secara otomatis. SqlException.Number dan SqlException.Message ditampilkan di jendela Konsol.

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

// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(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: {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: {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 = default!;
    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(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($"  {ex.Number}: {ex.Message}");
    }
    finally
    {
        transaction1.Dispose();
    }
}

// CLEANUP:
// Turn off Snapshot isolation and delete the table
using (SqlConnection connection3 = new(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: {ex.Message}");
    }
    command3.CommandText = "DROP TABLE TestSnapshotUpdate";
    try
    {
        command3.ExecuteNonQuery();
        Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"CLEANUP FAILED: {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 Cuplikan

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 memberikan petunjuk kunci di awal transaksi. Pernyataan berikut SELECT menggunakan UPDLOCK petunjuk 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 tersebut hingga transaksi pertama selesai. Ini menjamin bahwa baris yang dipilih tidak memiliki konflik ketika diperbarui nanti dalam transaksi. 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 sehingga terus-menerus bergantung pada petunjuk kunci untuk operasinya.

Lihat juga

  • Terakhir diperbarui pada