Catatan
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba masuk atau mengubah direktori.
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba mengubah direktori.
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 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 di tempdb seperti 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 READ COMMITTED default di 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 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 pada setiap sesi agar dapat 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 diatur untuk koneksi dengan perintah SET TRANSACTION ISOLATION LEVEL, tetap aktif sampai koneksi ditutup atau tingkat isolasi lain diatur. Ketika sebuah koneksi ditutup dan dikembalikan ke kumpulan, tingkat isolasi dari pernyataan SET TRANSACTION ISOLATION LEVEL yang terakhir tetap 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 penguncian yang dilakukan oleh transaksi lain. Transaksi yang dijalankan di bawah READ UNCOMMITTED dapat membaca nilai data yang dimodifikasi yang belum dilakukan oleh transaksi lain; ini disebut bacaan "kotor".
READ COMMITTED adalah 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 di-commit. Konkurensi lebih rendah daripada READ COMMITTED karena kunci bersama pada data baca dipertahankan selama durasi transaksi, bukan dilepaskan 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 pada tingkat isolasi SQL-92 dengan pengenalan tingkat isolasi SNAPSHOT dan implementasi tambahan untuk 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 snapshot untuk 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. Ini menghasilkan perilaku yang sama dengan pengaturan READ_COMMITTED_SNAPSHOT NONAKTIF (default). Saat READ_COMMITTED_SNAPSHOT OFF diaktifkan, Mesin Database menggunakan kunci bersama untuk menerapkan 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 Snapshot dan Pembuatan Versi Baris
Ketika tingkat isolasi SNAPSHOT diaktifkan, setiap kali baris diperbarui, Mesin Database SQL Server menyimpan salinan baris asli di tempdb, dan menambahkan nomor urutan transaksi ke baris. 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 dalam 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
Isolasi rekam jepret didukung di ADO.NET oleh kelas SqlTransaction. Jika sebuah database telah diaktifkan untuk isolasi snapshot tetapi tidak dikonfigurasi untuk READ_COMMITTED_SNAPSHOT ON, 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 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:
Ini dimulai, tetapi tidak selesai, 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 rekam jepret diaktifkan, transaksi ini dapat membaca data yang ada sebelum sqlTransaction1 dimulai.
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 batas kunci yang ditempatkan pada tabel dalam transaksi pertama dan mengalami timeout. Hasil yang sama akan terjadi jika tingkat isolasi REPEATABLE READ dan SERIALIZABLE digunakan karena tingkat isolasi ini juga tidak dapat membaca melewati batas kunci yang ditempatkan dalam transaksi pertama.
Ini membuka koneksi keempat dan memulai transaksi menggunakan tingkat isolasi READ UNCOMMITTED, yang melakukan pembacaan kotor dari nilai yang tidak dilakukan di sqlTransaction1. Nilai ini mungkin tidak pernah benar-benar ada dalam database jika transaksi pertama tidak dilakukan.
Ini menggulung balik transaksi pertama dan melakukan pembersihan dengan cara menghapus tabel TestSnapshot dan menonaktifkan isolasi snapshot untuk database 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";
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
{
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";
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"
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 menyelesaikan, sqlTransaction1 dengan menggunakan isolasi SNAPSHOT. Tiga baris data dipilih dalam transaksi.
Membuat SqlConnection kedua untuk AdventureWorks dan membuat transaksi kedua menggunakan tingkat isolasi READ COMMITTED yang memperbarui nilai di salah satu baris yang dipilih dari sqlTransaction1.
Menyelesaikan sqlTransaction2.
Mengembalikan ke sqlTransaction1 dan mencoba memperbarui baris yang sama yang sudah dilakukan oleh 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 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 SELECT berikut menggunakan petunjuk UPDLOCK untuk mengunci baris yang dipilih:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Dengan menggunakan petunjuk kunci UPDLOCK, setiap percobaan untuk memperbarui baris akan diblokir 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.