ATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistem Platform Analitik (PDW)Database SQL di Microsoft Fabric

Mengontrol perilaku penguncian dan penerapan versi baris dari pernyataan Transact-SQL yang dikeluarkan oleh koneksi ke SQL Server.

Konvensi sintaks transact-SQL

Sintaksis

Sintaks untuk SQL Server, Azure SQL Database, dan database SQL di Microsoft Fabric.

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Sintaks untuk Azure Synapse Analytics dan Gudang Data Paralel.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Catatan

Azure Synapse Analytics menerapkan transaksi ACID. Tingkat isolasi default adalah READ UNCOMMITTED. Anda bisa mengubahnya menjadi READ COMMITTED SNAPSHOT ISOLATION dengan mengubah ONREAD_COMMITTED_SNAPSHOT opsi database untuk database pengguna saat tersambung ke master database. Setelah diaktifkan, semua transaksi dalam database ini dijalankan di bawah READ COMMITTED SNAPSHOT ISOLATION dan pengaturan READ UNCOMMITTED pada tingkat sesi tidak dihormati. Untuk informasi selengkapnya, lihat OPSI ALTER DATABASE SET (Transact-SQL).

Argumen

BACA TIDAK DIKOMIT

Menentukan bahwa pernyataan dapat membaca baris yang dimodifikasi oleh transaksi lain tetapi belum diterapkan.

Transaksi yang berjalan pada READ UNCOMMITTED tingkat tidak mengeluarkan kunci bersama untuk mencegah transaksi lain memodifikasi data yang dibaca oleh transaksi saat ini. READ UNCOMMITTED transaksi juga tidak diblokir oleh kunci eksklusif yang akan mencegah transaksi saat ini membaca baris yang dimodifikasi tetapi tidak dilakukan oleh transaksi lain. Ketika opsi ini diatur, dimungkinkan untuk membaca modifikasi yang tidak dilakukan, yang disebut bacaan kotor. Nilai dalam data dapat diubah dan baris dapat muncul atau menghilang dalam himpunan data sebelum akhir transaksi. Opsi ini memiliki efek yang sama dengan pengaturan NOLOCK pada semua tabel dalam semua SELECT pernyataan dalam transaksi. Ini adalah yang paling tidak ketat dari tingkat isolasi.

Di SQL Server, Anda juga dapat meminimalkan pertikaian penguncian sambil melindungi transaksi dari pembacaan kotor modifikasi data yang tidak dilakukan menggunakan:

• Tingkat READ COMMITTED isolasi dengan READ_COMMITTED_SNAPSHOT opsi database diatur ke ON.

• Tingkat SNAPSHOT isolasi. Untuk informasi selengkapnya tentang isolasi rekam jepret, lihat Isolasi Rekam Jepret di SQL Server.

BACA BERKOMITMEN

Menentukan bahwa pernyataan tidak dapat membaca data yang dimodifikasi tetapi tidak dilakukan oleh transaksi lain. Ini mencegah pembacaan kotor. Data dapat diubah oleh transaksi lain antara pernyataan individu dalam transaksi saat ini, yang mengakibatkan bacaan atau data phantom yang tidak dapat dipetik. Opsi ini adalah default SQL Server.

Perilaku READ COMMITTED tergantung pada pengaturan READ_COMMITTED_SNAPSHOT opsi database:

• Jika READ_COMMITTED_SNAPSHOT diatur ke OFF (default di SQL Server), Mesin Database menggunakan kunci bersama untuk mencegah transaksi lain memodifikasi baris saat transaksi saat ini menjalankan operasi baca. Kunci bersama juga memblokir pernyataan dari baris baca yang dimodifikasi oleh transaksi lain hingga transaksi lain selesai. Jenis kunci bersama menentukan kapan dilepaskan. Kunci baris dilepaskan sebelum baris berikutnya diproses. Kunci halaman dilepaskan saat halaman berikutnya dibaca, dan kunci tabel dilepaskan saat pernyataan selesai.

• Jika READ_COMMITTED_SNAPSHOT diatur ke ON, Mesin Database menggunakan penerapan versi baris untuk menyajikan setiap pernyataan dengan rekam jepret data yang konsisten secara transaksional seperti yang ada di awal pernyataan. Kunci tidak digunakan untuk melindungi data dari pembaruan oleh transaksi lain.

  • READ_COMMITTED_SNAPSHOT ON adalah default pada Azure SQL Database dan SQL database di Microsoft Fabric.

Penting

Memilih tingkat isolasi transaksi tidak memengaruhi kunci yang diperoleh untuk melindungi modifikasi data. Transaksi selalu mendapatkan kunci eksklusif pada data apa pun yang dimodifikasinya, dan menahan kunci itu sampai transaksi selesai, terlepas dari tingkat isolasi yang ditetapkan untuk transaksi tersebut. Selain itu, pembaruan yang dibuat pada READ COMMITTED tingkat isolasi menggunakan kunci pembaruan pada baris data yang dipilih, sedangkan pembaruan yang dibuat di tingkat isolasi menggunakan versi baris untuk memilih baris yang akan diperbarui SNAPSHOT . Untuk operasi baca, tingkat isolasi transaksi terutama menentukan tingkat perlindungan dari efek modifikasi yang dilakukan oleh transaksi lain. Untuk informasi selengkapnya, lihat Panduan Penguncian Transaksi dan Penerapan Versi Baris.

Isolasi rekam jepret mendukung data FILESTREAM. Dalam mode isolasi rekam jepret, data FILESTREAM yang dibaca oleh pernyataan apa pun dalam transaksi adalah versi data yang konsisten secara transaksional yang ada di awal transaksi.

READ_COMMITTED_SNAPSHOT Saat opsi database adalah ON, Anda dapat menggunakan READCOMMITTEDLOCK petunjuk tabel untuk meminta penguncian bersama alih-alih penerapan versi baris untuk pernyataan individual dalam transaksi yang berjalan di READ COMMITTED tingkat isolasi.

Catatan

Saat Anda mengatur READ_COMMITTED_SNAPSHOT opsi , hanya koneksi yang menjalankan perintah yang ALTER DATABASE diizinkan dalam database. Tidak boleh ada koneksi terbuka lain dalam database hingga ALTER DATABASE selesai. Database tidak harus dalam mode pengguna tunggal.

BACAAN YANG DAPAT DIULANG

Menentukan bahwa pernyataan tidak dapat membaca data yang dimodifikasi tetapi belum dilakukan oleh transaksi lain, dan bahwa tidak ada transaksi lain yang dapat memodifikasi data yang dibaca oleh transaksi saat ini sampai transaksi saat ini selesai.

Kunci bersama ditempatkan pada semua data yang dibaca oleh setiap pernyataan dalam transaksi dan ditahan hingga transaksi selesai. Ini mencegah transaksi lain memodifikasi baris apa pun yang dibaca oleh transaksi saat ini. Transaksi lain dapat menyisipkan baris baru yang cocok dengan kondisi pencarian pernyataan yang dikeluarkan oleh transaksi saat ini. Jika transaksi saat ini kemudian mencoba kembali pernyataan, itu mengambil baris baru, yang menghasilkan bacaan phantom. Karena kunci bersama ditahan hingga akhir transaksi alih-alih dirilis di akhir setiap pernyataan, konkurensi lebih rendah dari tingkat isolasi default READ COMMITTED . Gunakan opsi ini hanya jika perlu.

SNAPSHOT

Menentukan bahwa data yang dibaca oleh pernyataan apa pun dalam transaksi adalah versi data yang konsisten secara transaksional yang ada di awal transaksi. Transaksi hanya dapat mengenali modifikasi data yang dilakukan sebelum awal transaksi. Modifikasi data yang dilakukan oleh transaksi lain setelah dimulainya transaksi saat ini tidak terlihat oleh pernyataan yang dijalankan dalam transaksi saat ini. Efeknya seolah-olah pernyataan dalam transaksi mendapatkan snapshot dari data yang berkomitmen seperti yang ada di awal transaksi.

Kecuali ketika database sedang dipulihkan, SNAPSHOT transaksi tidak meminta kunci saat membaca data. SNAPSHOT transaksi membaca data tidak memblokir transaksi lain untuk menulis data. Transaksi yang menulis data tidak memblokir SNAPSHOT transaksi dari membaca data.

Selama fase roll-back pemulihan database, SNAPSHOT transaksi meminta kunci jika upaya dilakukan untuk membaca data yang dikunci oleh transaksi lain yang sedang digulung balik. Transaksi SNAPSHOT diblokir sampai transaksi tersebut digulung balik. Kunci dilepaskan segera setelah diberikan.

Opsi ALLOW_SNAPSHOT_ISOLATION database harus diatur ke ON sebelum Anda dapat memulai transaksi yang menggunakan SNAPSHOT tingkat isolasi. Jika transaksi yang menggunakan SNAPSHOT tingkat isolasi mengakses data dalam beberapa database, ALLOW_SNAPSHOT_ISOLATION harus diatur ke ON di setiap database.

Transaksi tidak dapat diatur ke SNAPSHOT tingkat isolasi yang dimulai dengan tingkat isolasi lain; melakukannya menyebabkan transaksi dibatalkan. Jika transaksi dimulai di SNAPSHOT tingkat isolasi, Anda dapat mengubahnya ke tingkat isolasi lain dan kemudian kembali ke SNAPSHOT. Transaksi dimulai pertama kali mengakses data.

Transaksi yang berjalan di bawah SNAPSHOT tingkat isolasi dapat melihat perubahan yang dilakukan oleh transaksi tersebut. Misalnya, jika transaksi melakukan UPDATE pada tabel lalu mengeluarkan SELECT pernyataan terhadap tabel yang sama, data yang dimodifikasi disertakan dalam tataan hasil.

Catatan

Dalam mode isolasi rekam jepret, data FILESTREAM yang dibaca oleh pernyataan apa pun dalam transaksi adalah versi data yang konsisten secara transaksional yang ada di awal transaksi, bukan di awal pernyataan.

SERIALIZABLE

Menentukan kondisi berikut:

• Pernyataan tidak dapat membaca data yang dimodifikasi tetapi belum dilakukan oleh transaksi lain.

• Tidak ada transaksi lain yang dapat memodifikasi data yang dibaca oleh transaksi saat ini hingga transaksi saat ini selesai.

• Transaksi lain tidak dapat menyisipkan baris baru dengan nilai kunci yang akan berada dalam rentang kunci yang dibaca oleh pernyataan apa pun dalam transaksi saat ini hingga transaksi saat ini selesai.

Kunci rentang ditempatkan dalam rentang nilai kunci yang cocok dengan kondisi pencarian setiap pernyataan yang dijalankan dalam transaksi. Ini memblokir transaksi lain agar tidak memperbarui atau menyisipkan baris apa pun yang akan memenuhi syarat untuk salah satu pernyataan yang dijalankan oleh transaksi saat ini. Ini berarti bahwa jika salah satu pernyataan dalam transaksi dijalankan untuk kedua kalinya, mereka membaca kumpulan baris yang sama. Kunci rentang ditahan hingga transaksi selesai. Ini adalah yang paling ketat dari tingkat isolasi karena mengunci seluruh rentang kunci dan menahan kunci sampai transaksi selesai. Karena konkurensi lebih rendah, gunakan opsi ini hanya jika diperlukan. Opsi ini memiliki efek yang sama dengan pengaturan HOLDLOCK pada semua tabel dalam semua SELECT pernyataan dalam transaksi.

Keterangan

Hanya salah satu opsi tingkat isolasi yang dapat diatur pada satu waktu, dan tetap diatur untuk koneksi tersebut hingga diubah secara eksplisit. Semua operasi baca yang dilakukan dalam transaksi beroperasi di bawah aturan untuk tingkat isolasi yang ditentukan kecuali petunjuk tabel dalam FROM klausul pernyataan menentukan perilaku penguncian atau penerapan versi yang berbeda untuk tabel.

Tingkat isolasi transaksi menentukan jenis kunci yang diperoleh pada operasi baca. Kunci bersama yang diperoleh untuk READ COMMITTED atau REPEATABLE READ umumnya adalah kunci baris, meskipun kunci baris dapat diekskalasikan ke kunci halaman atau tabel jika sejumlah besar baris dalam halaman atau tabel dirujuk oleh bacaan. Jika transaksi memodifikasi baris setelah dibaca, transaksi memperoleh kunci eksklusif untuk melindungi baris tersebut, dan kunci eksklusif dipertahankan hingga transaksi selesai. Misalnya, jika REPEATABLE READ transaksi memiliki kunci bersama pada baris, dan transaksi kemudian memodifikasi baris, kunci baris bersama dikonversi ke kunci baris eksklusif.

Dengan satu pengecualian, Anda dapat beralih dari satu tingkat isolasi ke tingkat isolasi lainnya kapan saja selama transaksi. Pengecualian terjadi ketika berubah dari tingkat isolasi apa pun ke SNAPSHOT isolasi. Melakukan ini menyebabkan transaksi gagal dan digulung balik. Namun, Anda dapat mengubah transaksi yang dimulai dalam SNAPSHOT isolasi ke tingkat isolasi lainnya.

Saat Anda mengubah transaksi dari satu tingkat isolasi ke tingkat isolasi lainnya, sumber daya yang dibaca setelah perubahan dilindungi sesuai dengan aturan tingkat baru. Sumber daya yang dibaca sebelum perubahan terus dilindungi sesuai dengan aturan tingkat sebelumnya. Misalnya, jika transaksi berubah dari READ COMMITTED ke SERIALIZABLE, kunci bersama yang diperoleh setelah perubahan sekarang ditahan hingga akhir transaksi.

Jika Anda mengeluarkan SET TRANSACTION ISOLATION LEVEL prosedur atau pemicu tersimpan, saat objek kembali mengontrol tingkat isolasi diatur ulang ke tingkat yang berlaku saat objek dipanggil. Misalnya, jika Anda mengatur REPEATABLE READ dalam batch, dan batch kemudian memanggil prosedur tersimpan yang mengatur tingkat isolasi ke SERIALIZABLE, pengaturan tingkat isolasi kembali ke REPEATABLE READ ketika prosedur tersimpan mengembalikan kontrol ke batch.

Catatan

Fungsi yang ditentukan pengguna dan jenis yang ditentukan pengguna common language runtime (CLR) tidak dapat menjalankan SET TRANSACTION ISOLATION LEVEL. Namun, Anda dapat mengambil alih tingkat isolasi dengan menggunakan petunjuk tabel. Untuk informasi selengkapnya, lihat Petunjuk Tabel (Transact-SQL).

Saat Anda menggunakan sp_bindsession untuk mengikat dua sesi, setiap sesi mempertahankan pengaturan tingkat isolasinya. Menggunakan SET TRANSACTION ISOLATION LEVEL untuk mengubah pengaturan tingkat isolasi dari satu sesi tidak memengaruhi pengaturan sesi lain yang terikat padanya.

SET TRANSACTION ISOLATION LEVEL berlaku pada waktu eksekusi atau run time, dan tidak pada waktu penguraian.

Operasi pemuatan massal yang dioptimalkan pada kueri blok tumpukan yang berjalan di bawah tingkat isolasi berikut:

• SNAPSHOT
• READ UNCOMMITTED
• READ COMMITTED menggunakan penerapan versi baris

Sebaliknya, kueri yang berjalan di bawah tingkat isolasi ini memblokir operasi beban massal yang dioptimalkan pada tumpukan. Untuk informasi selengkapnya tentang operasi pemuatan massal, lihat Impor dan Ekspor Data Massal (SQL Server).

Database berkemampuan FILESTREAM mendukung tingkat isolasi transaksi berikut.

Tingkat isolasi	akses Transact-SQL	Akses sistem berkas
Baca tidak dikomit	SQL Server	Tidak didukung
Baca berkomitmen	SQL Server	SQL Server
Bacaan yang dapat diulang	SQL Server	Tidak didukung
Dapat diserialisasikan	SQL Server	Tidak didukung
Membaca rekam jepret yang diterapkan	SQL Server	SQL Server
Snapshot	SQL Server	SQL Server

Contoh

Contoh berikut mengatur TRANSACTION ISOLATION LEVEL untuk sesi. Untuk setiap pernyataan Transact-SQL yang mengikuti, SQL Server menyimpan semua kunci bersama hingga akhir transaksi.

USE AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

Konten terkait

• MENGUBAH DATABASE (T-SQL)
• USEROPTIONS DBCC (Transact-SQL)
• PILIH (Transact-SQL)
• Pernyataan SET (Transact-SQL)
• Petunjuk tabel (Transact-SQL)