Bagikan melalui

ATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL)

  • Artikel

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

Konvensi sintaks transact-SQL

Sintaksis

-- Syntax for SQL Server and Azure SQL Database
  
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Catatan

Azure Synapse Analytics menerapkan transaksi ACID. Tingkat isolasi dukungan transaksional diatur menjadi default ke READ UNCOMMITTED. Anda dapat mengubahnya menjadi READ COMMITTED SNAPSHOT ISOLATION dengan mengaktifkan opsi database READ_COMMITTED_SNAPSHOT untuk database pengguna saat tersambung ke database master. Setelah diaktifkan, semua transaksi dalam database ini dijalankan di bawah READ COMMITTED SNAPSHOT ISOLATION dan pengaturan READ UNCOMMITTED pada tingkat sesi tidak akan dihormati. Centang opsi ALTER DATABASE SET (T-SQL) untuk mengetahui detailnya.

Argumen

BACA TIDAK DIKOMIT
Menentukan bahwa pernyataan dapat membaca baris yang telah dimodifikasi oleh transaksi lain tetapi belum diterapkan.

Transaksi yang berjalan pada tingkat READ UNCOMMITTED tidak mengeluarkan kunci bersama untuk mencegah transaksi lain memodifikasi data yang dibaca oleh transaksi saat ini. TRANSAKSI READ UNCOMMITTED juga tidak diblokir oleh kunci eksklusif yang akan mencegah transaksi saat ini membaca baris yang telah 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 mengatur NOLOCK pada semua tabel di semua pernyataan SELECT 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 isolasi READ COMMITTED dengan opsi database READ_COMMITTED_SNAPSHOT diatur ke AKTIF.

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

READ COMMITTED
Menentukan bahwa pernyataan tidak dapat membaca data yang telah 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 bergantung pada pengaturan opsi database READ_COMMITTED_SNAPSHOT:

  • Jika READ_COMMITTED_SNAPSHOT diatur ke NONAKTIF (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 akan dirilis. 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 AKTIF (default di Azure SQL Database), 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.

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 tingkat isolasi READ COMMITTED menggunakan kunci pembaruan pada baris data yang dipilih, sedangkan pembaruan yang dibuat di tingkat isolasi SNAPSHOT menggunakan versi baris untuk memilih baris yang akan diperbarui. Untuk operasi baca, tingkat isolasi transaksi terutama menentukan tingkat perlindungan dari efek modifikasi yang dilakukan oleh transaksi lain. Lihat Panduan Penguncian Transaksi dan Penerapan Versi Baris untuk informasi selengkapnya.

Catatan

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

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

Catatan

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

REPEATABLE READ
Menentukan bahwa pernyataan tidak dapat membaca data yang telah dimodifikasi tetapi belum dilakukan oleh transaksi lain dan bahwa tidak ada transaksi lain yang dapat memodifikasi data yang telah 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 telah 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 akan 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 READ COMMITTED default. Gunakan opsi ini hanya jika perlu.

SNAPSHOT
Menentukan bahwa data yang dibaca oleh pernyataan apa pun dalam transaksi akan menjadi 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 berjalan tidak terlihat oleh pernyataan yang dieksekusi 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, transaksi SNAPSHOT tidak meminta kunci saat membaca data. Transaksi SNAPSHOT membaca data tidak memblokir transaksi lain untuk menulis data. Transaksi yang menulis data tidak memblokir transaksi SNAPSHOT dari membaca data.

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

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

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

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

Catatan

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

SERIALIZABLE
Menentukan hal berikut:

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

  • Tidak ada transaksi lain yang dapat memodifikasi data yang telah 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 akan membaca sekumpulan 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 mengatur HOLDLOCK pada semua tabel di semua pernyataan SELECT 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 klausul FROM dari 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 dinaikkan ke kunci halaman atau tabel jika sejumlah besar baris dalam halaman atau tabel dirujuk oleh bacaan. Jika baris dimodifikasi oleh transaksi setelah dibaca, transaksi memperoleh kunci eksklusif untuk melindungi baris tersebut, dan kunci eksklusif dipertahankan hingga transaksi selesai. Misalnya, jika transaksi READ REPEATABLE 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 saat mengubah dari tingkat isolasi apa pun ke isolasi SNAPSHOT. Melakukan ini menyebabkan transaksi gagal dan digulung balik. Namun, Anda dapat mengubah transaksi yang dimulai dalam isolasi SNAPSHOT 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 menjadi SERIALIZABLE, kunci bersama yang diperoleh setelah perubahan sekarang ditahan hingga akhir transaksi.

Jika Anda mengeluarkan SET TRANSACTION ISOLATION LEVEL dalam prosedur atau pemicu tersimpan, ketika objek mengembalikan kontrol 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 saat 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 bukan pada waktu penguraian.

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

  • SNAPSHOT

  • BACA TIDAK DIKOMIT

  • 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 Bertransaksi akses SQL Akses sistem berkas
Baca tidak dikomit SQL Server Tidak didukung
Pembacaan yang diterapkan SQL Server SQL Server
Pembacaan berulang SQL Server Tidak didukung
Serializable 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

Lihat Juga

MENGUBAH DATABASE (T-SQL)
USEROPTIONS DBCC (Transact-SQL)
SELECT (Transact-SQL)
Pernyataan SET (Transact-SQL)
Petunjuk Tabel (Transact-SQL)