Panduan penguncian transaksi dan penerapan versi baris

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

Dalam database apa pun, kesalahan kelola transaksi sering menyebabkan ketidakcocokan dan masalah performa dalam sistem yang memiliki banyak pengguna. Ketika jumlah pengguna yang mengakses data meningkat, menjadi penting untuk memiliki aplikasi yang menggunakan transaksi secara efisien. Panduan ini menjelaskan mekanisme penguncian dan penerapan versi baris yang digunakan Mesin Database SQL Server untuk memastikan integritas fisik setiap transaksi dan memberikan informasi tentang bagaimana aplikasi dapat mengontrol transaksi secara efisien.

Catatan

Penguncian yang dioptimalkan adalah fitur Mesin Database yang diperkenalkan pada tahun 2023 yang secara drastis mengurangi memori kunci, dan jumlah kunci yang diperlukan untuk penulisan bersamaan. Artikel ini telah diperbarui untuk menjelaskan Mesin Database SQL Server dengan dan tanpa penguncian yang dioptimalkan. Saat ini, penguncian yang dioptimalkan hanya tersedia di Azure SQL Database.

Penguncian yang dioptimalkan telah memperbarui beberapa bagian artikel ini secara signifikan, termasuk:

Dasar-dasar transaksi

Transaksi adalah urutan operasi yang dilakukan sebagai satu unit kerja logis. Unit kerja logis harus menunjukkan empat properti, yang disebut properti atomitas, konsistensi, isolasi, dan durabilitas (ACID), untuk memenuhi syarat sebagai transaksi.

Atomitas
Transaksi harus merupakan unit kerja atom; baik semua modifikasi datanya dilakukan, atau tidak ada yang dilakukan.

Konsistensi
Setelah selesai, transaksi harus meninggalkan semua data dalam keadaan konsisten. Dalam database relasional, semua aturan harus diterapkan pada modifikasi transaksi untuk mempertahankan semua integritas data. Semua struktur data internal, seperti indeks pohon B atau daftar tertaut doubly, harus benar di akhir transaksi.

Catatan

Dokumentasi SQL Server menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, SQL Server mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau penyimpanan data dalam memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

Isolasi
Modifikasi yang dilakukan oleh transaksi bersamaan harus diisolasi dari modifikasi yang dilakukan oleh transaksi bersamaan lainnya. Transaksi mengenali data dalam status sebelum transaksi bersamaan lain memodifikasinya, atau mengenali data setelah transaksi kedua selesai, tetapi tidak mengenali status menengah. Ini disebut sebagai serializability karena menghasilkan kemampuan untuk memuat ulang data awal dan memutar ulang serangkaian transaksi untuk berakhir dengan data dalam keadaan yang sama dengan yang ada setelah transaksi asli dilakukan.

Daya tahan
Setelah transaksi yang sepenuhnya tahan lama selesai, efeknya secara permanen diberlakukan dalam sistem. Modifikasi akan bertahan meskipun terjadi kegagalan sistem. SQL Server 2014 (12.x) dan yang lebih baru mengaktifkan transaksi tahan lama yang tertunda. Transaksi tahan lama tertunda dilakukan sebelum catatan log transaksi dipertahankan ke disk. Untuk informasi selengkapnya tentang durabilitas transaksi yang tertunda, lihat artikel Mengontrol Durabilitas Transaksi.

Pemrogram SQL bertanggung jawab untuk memulai dan mengakhiri transaksi pada titik-titik yang menegakkan konsistensi logis data. Programmer harus menentukan urutan modifikasi data yang meninggalkan data dalam keadaan konsisten relatif terhadap aturan bisnis organisasi. Programmer menyertakan pernyataan modifikasi ini dalam satu transaksi sehingga Mesin Database SQL Server dapat memberlakukan integritas fisik transaksi.

Ini adalah tanggung jawab sistem database perusahaan, seperti instans Mesin Database SQL Server, untuk menyediakan mekanisme yang memastikan integritas fisik setiap transaksi. Mesin Database SQL Server menyediakan:

  • Fasilitas penguncian yang mempertahankan isolasi transaksi.

  • Fasilitas pengelogan memastikan durabilitas transaksi. Untuk transaksi yang sepenuhnya tahan lama, catatan log diperkuat ke disk sebelum transaksi dilakukan. Dengan demikian, bahkan jika perangkat keras server, sistem operasi, atau instans Mesin Database SQL Server itu sendiri gagal, instans menggunakan log transaksi setelah restart untuk secara otomatis mengembalikan transaksi yang tidak lengkap ke titik kegagalan sistem. Transaksi tahan lama tertunda dilakukan sebelum catatan log transaksi diperkuat ke disk. Transaksi tersebut dapat hilang jika ada kegagalan sistem sebelum catatan log diperkuat ke disk. Untuk informasi selengkapnya tentang durabilitas transaksi yang tertunda, lihat artikel Mengontrol Durabilitas Transaksi.

  • Fitur manajemen transaksi yang memberlakukan atomitas dan konsistensi transaksi. Setelah transaksi dimulai, transaksi harus berhasil diselesaikan (dilakukan), atau Mesin Database SQL Server membatalkan semua modifikasi data yang dilakukan sejak transaksi dimulai. Operasi ini disebut sebagai mengembalikan transaksi karena mengembalikan data ke status sebelum perubahan tersebut.

Mengontrol transaksi

Aplikasi mengontrol transaksi terutama dengan menentukan kapan transaksi dimulai dan berakhir. Ini dapat ditentukan dengan menggunakan pernyataan Transact-SQL atau fungsi antarmuka pemrograman aplikasi database (API). Sistem juga harus dapat menangani kesalahan dengan benar yang mengakhiri transaksi sebelum selesai. Untuk informasi selengkapnya, lihat Transaksi, Melakukan Transaksi di ODBC dan Transaksi di SQL Server Native Client.

Secara default, transaksi dikelola pada tingkat koneksi. Ketika transaksi dimulai pada koneksi, semua pernyataan Transact-SQL yang dijalankan pada koneksi tersebut adalah bagian dari transaksi hingga transaksi berakhir. Namun, di bawah sesi beberapa kumpulan hasil aktif (MARS), transaksi eksplisit atau implisit Transact-SQL menjadi transaksi cakupan batch yang dikelola pada tingkat batch. Ketika batch selesai, jika transaksi cakupan batch tidak diterapkan atau digulung balik, itu secara otomatis digulung balik oleh SQL Server. Untuk informasi selengkapnya, lihat Menggunakan Beberapa Kumpulan Hasil Aktif (MARS).

Memulai transaksi

Dengan menggunakan fungsi API dan pernyataan Transact-SQL, Anda dapat memulai transaksi dalam instans Mesin Database SQL Server sebagai transaksi eksplisit, autocommit, atau implisit.

Transaksi eksplisit
Transaksi eksplisit adalah transaksi di mana Anda secara eksplisit menentukan awal dan akhir transaksi melalui fungsi API atau dengan mengeluarkan pernyataan Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, atau ROLLBACK WORK Transact-SQL. Ketika transaksi berakhir, koneksi kembali ke mode transaksi sebelum transaksi eksplisit dimulai, baik mode implisit atau autocommit.

Anda dapat menggunakan semua pernyataan Transact-SQL dalam transaksi eksplisit, kecuali untuk pernyataan berikut:

  • BUAT DATABASE
  • ALTER DATABASE
  • JATUHKAN DATABASE
  • BUAT KATALOG FULLTEXT
  • UBAH KATALOG FULLTEXT
  • JATUHKAN KATALOG FULLTEXT
  • HILANGKAN INDEKS TEKS PENUH
  • ALTER FULLTEXT INDEX
  • MEMBUAT INDEKS FULLTEXT
  • CADANGAN
  • MEMULIHKAN
  • KONFIGURASI ULANG
  • Prosedur tersimpan sistem teks lengkap
  • sp_dboption untuk mengatur opsi database atau prosedur sistem apa pun yang memodifikasi master database di dalam transaksi eksplisit atau implisit.

Catatan

STATISTIK PEMBARUAN dapat digunakan di dalam transaksi eksplisit. Namun, UPDATE STATISTICS menerapkan secara independen dari transaksi penutup dan tidak dapat digulung balik.

Transaksi Autocommit
Mode autocommit adalah mode manajemen transaksi default dari SQL Server Database Engine. Setiap pernyataan Transact-SQL diterapkan atau digulung balik ketika selesai. Jika pernyataan berhasil diselesaikan, pernyataan tersebut diterapkan; jika mengalami kesalahan, kesalahan akan digulung balik. Koneksi ke instans Mesin Database SQL Server beroperasi dalam mode autocommit setiap kali mode default ini belum ditimpa oleh transaksi eksplisit atau implisit. Mode autocommit juga merupakan mode default untuk ADO, OLE DB, ODBC, dan DB-Library.

Transaksi Implisit
Ketika koneksi beroperasi dalam mode transaksi implisit, instans Mesin Database SQL Server secara otomatis memulai transaksi baru setelah transaksi saat ini diterapkan atau digulung balik. Anda tidak melakukan apa pun untuk menguraikan awal transaksi; Anda hanya menerapkan atau membatalkan setiap transaksi. Mode transaksi implisit menghasilkan rantai transaksi berkelanjutan. Atur mode transaksi implisit pada melalui fungsi API atau pernyataan SET Transact-SQL IMPLICIT_TRANSACTIONS ON. Mode ini juga dikenal sebagai Autocommit OFF, lihat setAutoCommit Method (SQLServer Koneksi ion).

Setelah mode transaksi implisit diatur untuk koneksi, instans Mesin Database SQL Server secara otomatis memulai transaksi ketika pertama kali menjalankan salah satu pernyataan ini:

  • ALTER TABLE

  • CREATE

  • DELETE

  • DROP

  • FETCH

  • GRANT

  • INSERT

  • BUKA

  • REVOKE

  • SELECT

  • POTONG TABEL

  • UPDATE

  • Transaksi yang dilingkup batch hanya berlaku untuk beberapa tataan hasil aktif (MARS), transaksi eksplisit atau implisit Transact-SQL yang dimulai di bawah sesi MARS menjadi transaksi cakupan batch. Transaksi cakupan batch yang tidak diterapkan atau digulung balik ketika batch selesai secara otomatis digulung balik oleh SQL Server.

  • Transaksi terdistribusi Transaksi terdistribusi mencakup dua server atau lebih yang dikenal sebagai manajer sumber daya. Manajemen transaksi harus dikoordinasikan antara manajer sumber daya oleh komponen server yang disebut manajer transaksi. Setiap instans Mesin Database SQL Server dapat beroperasi sebagai manajer sumber daya dalam transaksi terdistribusi yang dikoordinasikan oleh manajer transaksi, seperti Koordinator Transaksi Terdistribusi Microsoft (MS DTC), atau manajer transaksi lain yang mendukung spesifikasi Open Group XA untuk pemrosesan transaksi terdistribusi. Untuk informasi selengkapnya, lihat dokumentasi MS DTC.

    Transaksi dalam satu instans Mesin Database SQL Server yang mencakup dua database atau lebih sebenarnya adalah transaksi terdistribusi. Instans mengelola transaksi terdistribusi secara internal; kepada pengguna, ia beroperasi sebagai transaksi lokal.

    Pada aplikasi, transaksi terdistribusi dikelola jauh sama dengan transaksi lokal. Pada akhir transaksi, aplikasi meminta transaksi untuk diterapkan atau digulung balik. Penerapan terdistribusi harus dikelola secara berbeda oleh manajer transaksi untuk meminimalkan risiko bahwa kegagalan jaringan dapat mengakibatkan beberapa manajer sumber daya berhasil melakukan sementara yang lain membatalkan transaksi. Hal ini dicapai dengan mengelola proses penerapan dalam dua fase (fase persiapan dan fase penerapan), yang dikenal sebagai penerapan dua fase (2PC).

    • Fase Persiapan Ketika manajer transaksi menerima permintaan penerapan, manajer transaksi mengirimkan perintah persiapan ke semua manajer sumber daya yang terlibat dalam transaksi. Setiap manajer sumber daya kemudian melakukan semua yang diperlukan untuk membuat transaksi tahan lama, dan semua buffer yang memegang gambar log untuk transaksi disiram ke disk. Ketika setiap manajer sumber daya menyelesaikan fase persiapan, ia mengembalikan keberhasilan atau kegagalan persiapan ke manajer transaksi. SQL Server 2014 (12.x) memperkenalkan durabilitas transaksi yang tertunda. Transaksi tahan lama tertunda dilakukan sebelum gambar log untuk transaksi dihapus ke disk. Untuk informasi selengkapnya tentang durabilitas transaksi yang tertunda, lihat artikel Mengontrol Durabilitas Transaksi.

    • Fase Penerapan Jika manajer transaksi menerima persiapan yang berhasil dari semua manajer sumber daya, manajer sumber daya mengirimkan perintah penerapan ke setiap manajer sumber daya. Manajer sumber daya kemudian dapat menyelesaikan penerapan. Jika semua manajer sumber daya melaporkan penerapan yang berhasil, manajer transaksi kemudian mengirim pemberitahuan keberhasilan ke aplikasi. Jika ada manajer sumber daya yang melaporkan kegagalan persiapan, manajer transaksi mengirimkan perintah putar kembali ke setiap manajer sumber daya dan menunjukkan kegagalan penerapan ke aplikasi.

      Aplikasi Mesin Database SQL Server dapat mengelola transaksi terdistribusi baik melalui Transact-SQL atau API database. Untuk informasi selengkapnya, lihat MULAI TRANSAKSI TERDISTRIBUSI (Transact-SQL).

Akhiri transaksi

Anda dapat mengakhiri transaksi dengan pernyataan COMMIT atau ROLLBACK, atau melalui fungsi API yang sesuai.

  • COMMIT Jika transaksi berhasil, terapkan. Pernyataan COMMIT menjamin semua modifikasi transaksi dibuat sebagai bagian permanen dari database. COMMIT juga membebaskan sumber daya, seperti kunci, yang digunakan oleh transaksi.

  • ROLLBACK Jika terjadi kesalahan dalam transaksi, atau jika pengguna memutuskan untuk membatalkan transaksi, maka gulung balik transaksi. Pernyataan ROLLBACK mencadangkan semua modifikasi yang dilakukan dalam transaksi dengan mengembalikan data ke status berada di awal transaksi. ROLLBACK juga membebaskan sumber daya yang disimpan oleh transaksi.

Catatan

Di bawah koneksi yang diaktifkan untuk mendukung beberapa kumpulan hasil aktif (MARS), transaksi eksplisit yang dimulai melalui fungsi API tidak dapat dilakukan saat ada permintaan yang tertunda untuk eksekusi. Setiap upaya untuk melakukan jenis transaksi ini saat ada operasi terutang yang berjalan akan mengakibatkan kesalahan.

Kesalahan selama pemrosesan transaksi

Jika kesalahan mencegah keberhasilan penyelesaian transaksi, SQL Server secara otomatis mengembalikan transaksi dan membebaskan semua sumber daya yang disimpan oleh transaksi. Jika koneksi jaringan klien ke instans Mesin Database SQL Server rusak, setiap transaksi terutang untuk koneksi digulung balik ketika jaringan memberi tahu instans jeda. Jika aplikasi klien gagal atau jika komputer klien tidak berfungsi atau dimulai ulang, ini juga memecah koneksi, dan instans Mesin Database SQL Server mengembalikan koneksi yang luar biasa ketika jaringan memberi tahunya tentang jeda. Jika klien keluar dari aplikasi, setiap transaksi yang terutang akan digulung balik.

Jika kesalahan pernyataan run-time (seperti pelanggaran batasan) terjadi dalam batch, perilaku default di Mesin Database SQL Server adalah mengembalikan hanya pernyataan yang menghasilkan kesalahan. Anda dapat mengubah perilaku ini menggunakan SET XACT_ABORT pernyataan . Setelah SET XACT_ABORT ON dijalankan, kesalahan pernyataan run-time menyebabkan pembatalan otomatis transaksi saat ini. Mengkompilasi kesalahan, seperti kesalahan sintaksis, tidak dipengaruhi oleh SET XACT_ABORT. Untuk informasi selengkapnya, lihat SET XACT_ABORT (Transact-SQL).

Ketika kesalahan terjadi, tindakan korektif (COMMIT atau ROLLBACK) harus disertakan dalam kode aplikasi. Salah satu alat yang efektif untuk menangani kesalahan, termasuk yang dalam transaksi, adalah konstruksi Transact-SQL TRY...CATCH . Untuk informasi selengkapnya tentang contoh yang menyertakan transaksi, lihat TRY... CATCH (Transact-SQL). Dimulai dengan SQL Server 2012 (11.x), Anda dapat menggunakan THROW pernyataan untuk menaikkan pengecualian dan mentransfer eksekusi ke CATCH blok TRY...CATCH konstruksi. Untuk informasi selengkapnya, lihat THROW (Transact-SQL).

Mengompilasi dan menjalankan kesalahan dalam mode autocommit

Dalam mode autocommit, terkadang muncul seolah-olah instans Mesin Database SQL Server telah mengembalikan seluruh batch alih-alih hanya satu pernyataan SQL. Ini terjadi jika kesalahan yang ditemui adalah kesalahan kompilasi, bukan kesalahan run-time. Kesalahan kompilasi mencegah Mesin Database SQL Server membangun rencana eksekusi, sehingga tidak ada dalam batch yang dijalankan. Meskipun tampaknya semua pernyataan sebelum yang menghasilkan kesalahan digulung balik, kesalahan mencegah apa pun dalam batch dijalankan. Dalam contoh berikut, tidak ada INSERT pernyataan dalam batch ketiga yang dijalankan karena kesalahan kompilasi. Tampaknya dua INSERT pernyataan pertama digulung balik ketika tidak pernah dijalankan.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

Dalam contoh berikut, pernyataan ketiga INSERT menghasilkan kesalahan kunci primer duplikat run-time. Dua INSERT pernyataan pertama berhasil dan diterapkan, sehingga tetap ada setelah kesalahan run-time.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Mesin Database SQL Server menggunakan resolusi nama yang ditangguhkan, di mana nama objek tidak diselesaikan hingga waktu eksekusi. Dalam contoh berikut, dua INSERT pernyataan pertama dijalankan dan diterapkan, dan kedua baris tersebut TestBatch tetap dalam tabel setelah pernyataan ketiga INSERT menghasilkan kesalahan run-time dengan merujuk ke tabel yang tidak ada.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Dasar-dasar penguncian dan penerapan versi baris

Mesin Database SQL Server menggunakan mekanisme berikut untuk memastikan integritas transaksi dan mempertahankan konsistensi database ketika beberapa pengguna mengakses data secara bersamaan:

  • Penguncian

    Setiap transaksi meminta kunci dari berbagai jenis pada sumber daya, seperti baris, halaman, atau tabel, di mana transaksi bergantung. Kunci memblokir transaksi lain agar tidak memodifikasi sumber daya dengan cara yang akan menyebabkan masalah untuk transaksi yang meminta kunci. Setiap transaksi membebaskan kuncinya ketika tidak lagi memiliki dependensi pada sumber daya yang terkunci.

  • Penerapan versi baris

    Saat tingkat isolasi berbasis penerapan versi baris diaktifkan, Mesin Database SQL Server mempertahankan versi setiap baris yang dimodifikasi. Aplikasi dapat menentukan bahwa transaksi menggunakan versi baris untuk melihat data seperti yang ada di awal transaksi atau kueri alih-alih melindungi semua bacaan dengan kunci. Dengan menggunakan penerapan versi baris, kemungkinan operasi baca akan memblokir transaksi lain sangat berkurang.

Penguncian dan penerapan versi baris mencegah pengguna membaca data yang tidak dilakukan dan mencegah beberapa pengguna mencoba mengubah data yang sama secara bersamaan. Tanpa penguncian atau penerapan versi baris, kueri yang dijalankan terhadap data tersebut dapat menghasilkan hasil yang tidak terduga dengan mengembalikan data yang belum dilakukan dalam database.

Aplikasi dapat memilih tingkat isolasi transaksi, yang menentukan tingkat perlindungan untuk transaksi dari modifikasi yang dilakukan oleh transaksi lain. Petunjuk tingkat tabel dapat ditentukan untuk pernyataan Transact-SQL individual untuk menyesuaikan perilaku lebih lanjut agar sesuai dengan persyaratan aplikasi.

Mengelola akses data bersamaan

Pengguna yang mengakses sumber daya secara bersamaan dikatakan mengakses sumber daya secara bersamaan. Akses data bersamaan memerlukan mekanisme untuk mencegah efek buruk ketika beberapa pengguna mencoba memodifikasi sumber daya yang digunakan pengguna lain secara aktif.

Efek konkurensi

Pengguna yang memodifikasi data dapat memengaruhi pengguna lain yang membaca atau memodifikasi data yang sama secara bersamaan. Pengguna ini dikatakan mengakses data secara bersamaan. Jika sistem penyimpanan data tidak memiliki kontrol konkurensi, pengguna dapat melihat efek samping berikut:

  • Pembaruan hilang

    Pembaruan yang hilang terjadi ketika dua transaksi atau lebih memilih baris yang sama lalu memperbarui baris berdasarkan nilai yang awalnya dipilih. Setiap transaksi tidak menyadari transaksi lainnya. Pembaruan terakhir menimpa pembaruan yang dibuat oleh transaksi lain, yang menghasilkan data yang hilang.

    Misalnya, dua editor membuat salinan elektronik dari dokumen yang sama. Setiap editor mengubah salinan secara independen lalu menyimpan salinan yang diubah sehingga menimpa dokumen asli. Editor yang menyimpan salinan yang diubah terakhir kali menimpa perubahan yang dibuat oleh editor lain. Masalah ini dapat dihindari jika satu editor tidak dapat mengakses file sampai editor lain selesai dan melakukan transaksi.

  • Dependensi yang tidak dikomit (baca kotor)

    Dependensi yang tidak dilakukan terjadi ketika transaksi kedua memilih baris yang sedang diperbarui oleh transaksi lain. Transaksi kedua adalah membaca data yang belum dilakukan dan dapat diubah oleh transaksi yang memperbarui baris.

    Misalnya, editor membuat perubahan pada dokumen elektronik. Selama perubahan, editor kedua mengambil salinan dokumen yang mencakup semua perubahan yang dibuat sejauh ini, dan mendistribusikan dokumen ke audiens yang dimaksud. Editor pertama kemudian memutuskan perubahan yang dibuat sejauh ini salah dan menghapus pengeditan dan menyimpan dokumen. Dokumen terdistribusi berisi pengeditan yang tidak lagi ada dan harus diperlakukan seolah-olah dokumen tersebut tidak pernah ada. Masalah ini dapat dihindari jika tidak ada yang dapat membaca dokumen yang diubah sampai editor pertama melakukan penyimpanan akhir modifikasi dan melakukan transaksi.

  • Analisis yang tidak konsisten (bacaan yang tidak dapat dipetik)

    Analisis yang tidak konsisten terjadi ketika transaksi kedua mengakses baris yang sama beberapa kali dan membaca data yang berbeda setiap kali. Analisis yang tidak konsisten mirip dengan dependensi yang tidak dilakukan karena transaksi lain mengubah data yang dibaca transaksi kedua. Namun, dalam analisis yang tidak konsisten, data yang dibaca oleh transaksi kedua dilakukan oleh transaksi yang melakukan perubahan. Selain itu, analisis yang tidak konsisten melibatkan beberapa bacaan (dua atau lebih) dari baris yang sama, dan setiap kali informasi diubah oleh transaksi lain; dengan demikian, istilah baca yang tidak dapat dipesan.

    Misalnya, editor membaca dokumen yang sama dua kali, tetapi di antara setiap pembaca menulis ulang dokumen. Ketika editor membaca dokumen untuk kedua kalinya, dokumen telah berubah. Bacaan asli tidak dapat diulang. Masalah ini dapat dihindari jika penulis tidak dapat mengubah dokumen sampai editor selesai membacanya untuk terakhir kalinya.

  • Bacaan phantom

    Bacaan phantom adalah situasi yang terjadi ketika dua kueri identik dijalankan dan kumpulan baris yang dikembalikan oleh kueri kedua berbeda. Contoh di bawah ini menunjukkan bagaimana hal ini dapat terjadi. Asumsikan kedua transaksi di bawah ini dijalankan secara bersamaan. Dua SELECT pernyataan dalam transaksi pertama dapat mengembalikan hasil yang berbeda karena INSERT pernyataan dalam transaksi kedua mengubah data yang digunakan oleh keduanya.

    --Transaction 1
BEGIN TRAN;
SELECT ID FROM dbo.employee
WHERE ID > 5 and ID < 10;
--The INSERT statement from the second transaction occurs here.
SELECT ID FROM dbo.employee
WHERE ID > 5 and ID < 10;
COMMIT;

    --Transaction 2
BEGIN TRAN;
INSERT INTO dbo.employee
  (Id, Name) VALUES(6 ,'New');
COMMIT;

  • Pembacaan hilang dan ganda yang disebabkan oleh pembaruan baris

    • Kehilangan baris yang diperbarui atau melihat baris yang diperbarui beberapa kali

      Transaksi yang berjalan pada READ UNCOMMITTED tingkat tidak mengeluarkan kunci bersama untuk mencegah transaksi lain memodifikasi data yang dibaca oleh transaksi saat ini. Transaksi yang berjalan di tingkat READ COMMITTED mengeluarkan kunci bersama, tetapi kunci baris atau halaman dirilis setelah baris dibaca. Dalam kedua kasus, saat Anda memindai indeks, jika pengguna lain mengubah kolom kunci indeks baris selama baca Anda, baris mungkin muncul lagi jika perubahan kunci memindahkan baris ke posisi di depan pemindaian Anda. Demikian pula, baris mungkin tidak muncul jika perubahan kunci memindahkan baris ke posisi dalam indeks yang telah Anda baca. Untuk menghindari hal ini, gunakan SERIALIZABLE atau HOLDLOCK petunjuk, atau penerapan versi baris. Untuk informasi selengkapnya, lihat Petunjuk Tabel (Transact-SQL).

    • Kehilangan satu atau beberapa baris yang bukan target pembaruan

      Saat Anda menggunakan READ UNCOMMITTED, jika kueri Anda membaca baris menggunakan pemindaian urutan alokasi (menggunakan halaman IAM), Anda mungkin melewatkan baris jika transaksi lain menyebabkan pemisahan halaman. Ini tidak dapat terjadi ketika Anda menggunakan baca yang diterapkan karena kunci tabel ditahan selama pemisahan halaman dan tidak terjadi jika tabel tidak memiliki indeks berkluster, karena pembaruan tidak menyebabkan pemisahan halaman.

Jenis konkurensi

Ketika banyak orang mencoba memodifikasi data dalam database secara bersamaan, sistem kontrol harus diimplementasikan sehingga modifikasi yang dilakukan oleh satu orang tidak berdampak buruk pada orang lain. Ini disebut kontrol konkurensi.

Teori kontrol konkurensi memiliki dua klasifikasi untuk metode penerapan kontrol konkurensi:

  • Kontrol konkurensi pesimis

    Sistem kunci mencegah pengguna memodifikasi data dengan cara yang memengaruhi pengguna lain. Setelah pengguna melakukan tindakan yang menyebabkan kunci diterapkan, pengguna lain tidak dapat melakukan tindakan yang akan bertentangan dengan kunci hingga pemilik melepaskannya. Ini disebut kontrol pesimis karena terutama digunakan di lingkungan di mana ada ketidakcocokan tinggi untuk data, di mana biaya melindungi data dengan kunci kurang dari biaya transaksi rollback jika konflik konkurensi terjadi.

  • Kontrol konkurensi optimis

    Dalam kontrol konkurensi optimis, pengguna tidak mengunci data saat mereka membacanya. Saat pengguna memperbarui data, sistem memeriksa untuk melihat apakah pengguna lain mengubah data setelah dibaca. Jika pengguna lain memperbarui data, kesalahan akan muncul. Biasanya, pengguna yang menerima kesalahan mengembalikan transaksi dan memulai kembali. Ini disebut optimis karena terutama digunakan di lingkungan di mana ada ketidakcocokan rendah untuk data, dan di mana biaya sesekali menggulung balik transaksi lebih rendah daripada biaya penguncian data saat dibaca.

SQL Server mendukung berbagai kontrol konkurensi. Pengguna menentukan jenis kontrol konkurensi dengan memilih tingkat isolasi transaksi untuk koneksi atau opsi konkurensi pada kursor. Atribut ini dapat didefinisikan menggunakan pernyataan Transact-SQL, atau melalui properti dan atribut antarmuka pemrograman aplikasi database (API) seperti ADO, ADO.NET, OLE DB, dan ODBC.

Tingkat isolasi di Mesin Database SQL Server

Transaksi menentukan tingkat isolasi yang menentukan tingkat di mana satu transaksi harus diisolasi dari modifikasi sumber daya atau data yang dilakukan oleh transaksi lain. Tingkat isolasi dijelaskan dalam hal efek samping konkurensi mana, seperti bacaan kotor atau bacaan phantom, diizinkan.

Kontrol tingkat isolasi transaksi:

  • Apakah kunci diperoleh saat data dibaca, dan jenis kunci apa yang diminta.
  • Berapa lama kunci baca ditahan.
  • Apakah operasi baca mereferensikan baris yang dimodifikasi oleh transaksi lain:
    • Memblokir hingga kunci eksklusif pada baris dibebesarkan.
    • Mengambil versi baris yang diterapkan yang ada pada saat pernyataan atau transaksi dimulai.
    • Membaca modifikasi data yang tidak dikomit.

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. Untuk operasi baca, tingkat isolasi transaksi terutama menentukan tingkat perlindungan dari efek modifikasi yang dilakukan oleh transaksi lain.

Tingkat isolasi yang lebih rendah meningkatkan kemampuan banyak pengguna untuk mengakses data secara bersamaan, tetapi meningkatkan jumlah efek konkurensi (seperti bacaan kotor atau pembaruan yang hilang) yang mungkin dihadapi pengguna. Sebaliknya, tingkat isolasi yang lebih tinggi mengurangi jenis efek konkurensi yang mungkin dihadapi pengguna, tetapi membutuhkan lebih banyak sumber daya sistem dan meningkatkan kemungkinan satu transaksi akan memblokir transaksi lain. Memilih tingkat isolasi yang sesuai tergantung pada penyeimbangan persyaratan integritas data aplikasi terhadap overhead setiap tingkat isolasi. Tingkat isolasi tertinggi, dapat diserialisasikan, menjamin bahwa transaksi akan mengambil data yang sama persis setiap kali mengulangi operasi baca, tetapi melakukan ini dengan melakukan tingkat penguncian yang kemungkinan berdampak pada pengguna lain dalam sistem multi-pengguna. Tingkat isolasi terendah, baca tidak dikomit, dapat mengambil data yang telah dimodifikasi tetapi tidak dilakukan oleh transaksi lain. Semua efek samping konkurensi dapat terjadi dalam bacaan yang tidak dikomit, tetapi tidak ada penguncian baca atau penerapan versi, sehingga overhead diminimalkan.

Tingkat isolasi Mesin Database

Standar ISO mendefinisikan tingkat isolasi berikut, yang semuanya didukung oleh Mesin Database SQL Server:

Tingkat Isolasi Definisi
Baca tidak dikomit Tingkat isolasi terendah di mana transaksi hanya cukup terisolasi untuk memastikan bahwa data yang rusak secara fisik tidak dibaca. Dalam tingkat ini, bacaan kotor diizinkan, sehingga satu transaksi mungkin melihat perubahan yang belum diterapkan yang dilakukan oleh transaksi lain.
Baca berkomitmen Memungkinkan transaksi membaca data yang sebelumnya dibaca (tidak dimodifikasi) oleh transaksi lain tanpa menunggu transaksi pertama selesai. Mesin Database SQL Server menyimpan kunci tulis (diperoleh pada data yang dipilih) hingga akhir transaksi, tetapi kunci baca dirilis segera setelah operasi SELECT dilakukan. Ini adalah tingkat default Mesin Database SQL Server.
Bacaan yang dapat diulang Mesin Database SQL Server terus membaca dan menulis kunci yang diperoleh pada data yang dipilih hingga akhir transaksi. Namun, karena penguncian rentang tidak dikelola, pembacaan phantom dapat terjadi.
Dapat diserialisasikan Tingkat tertinggi di mana transaksi sepenuhnya terisolasi satu sama lain. Mesin Database SQL Server terus membaca dan menulis kunci yang diperoleh pada data yang dipilih untuk dirilis pada akhir transaksi. Kunci rentang diperoleh ketika operasi SELECT menggunakan klausa WHERE berkisar, terutama untuk menghindari pembacaan phantom.

Catatan: Operasi dan transaksi DDL pada tabel yang direplikasi mungkin gagal ketika tingkat isolasi yang dapat diserialisasikan diminta. Ini karena kueri replikasi menggunakan petunjuk yang mungkin tidak kompatibel dengan tingkat isolasi yang dapat diserialisasikan.

SQL Server juga mendukung dua tingkat isolasi transaksi tambahan yang menggunakan penerapan versi baris. Salah satunya adalah implementasi isolasi READ COMMITTED, dan satu adalah tingkat isolasi transaksi, rekam jepret.

Tingkat Isolasi Penerapan Versi Baris Definisi
Baca Rekam Jepret yang Diterapkan (RCSI) Saat opsi database READ_COMMITTED_SNAPSHOT diatur AKTIF, isolasi READ COMMITTED menggunakan penerapan versi baris untuk memberikan konsistensi baca tingkat pernyataan. Operasi baca hanya memerlukan kunci tingkat tabel SCH-S dan tidak ada kunci halaman atau baris. Artinya, Mesin Database SQL Server 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. Fungsi yang ditentukan pengguna dapat mengembalikan data yang diterapkan setelah waktu pernyataan yang berisi UDF dimulai.

READ_COMMITTED_SNAPSHOT Saat opsi database diatur NONAKTIF, yang merupakan pengaturan default, isolasi READ COMMITTED 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. Kedua implementasi memenuhi definisi ISO dari isolasi READ COMMITTED.
Snapshot Tingkat isolasi rekam jepret menggunakan penerapan versi baris untuk memberikan konsistensi baca tingkat transaksi. Operasi baca tidak memperoleh kunci halaman atau baris; hanya kunci tabel SCH-S yang diperoleh. Saat membaca baris yang dimodifikasi oleh transaksi lain, baris tersebut mengambil versi baris yang ada saat transaksi dimulai. Anda hanya dapat menggunakan isolasi Rekam Jepret terhadap database saat ALLOW_SNAPSHOT_ISOLATION opsi database diatur AKTIF. Secara default, opsi ini diatur NONAKTIF untuk database pengguna.

Catatan: SQL Server tidak mendukung penerapan versi metadata. Untuk alasan ini, ada batasan pada operasi DDL apa yang dapat dilakukan dalam transaksi eksplisit yang berjalan di bawah isolasi rekam jepret. Pernyataan DDL berikut tidak diizinkan di bawah isolasi rekam jepret setelah pernyataan BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, atau pernyataan DDL common language runtime (CLR). Pernyataan ini diizinkan ketika Anda menggunakan isolasi snapshot dalam transaksi implisit. Transaksi implisit, menurut definisi, adalah satu pernyataan yang memungkinkan untuk memberlakukan semantik isolasi rekam jepret, bahkan dengan pernyataan DDL. Pelanggaran prinsip ini dapat menyebabkan kesalahan 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

Tabel berikut menunjukkan efek samping konkurensi yang diaktifkan oleh tingkat isolasi yang berbeda.

Tingkat isolasi Pembacaan kotor Bacaan yang tidak dapat dipesan Phantom
Baca tidak dikomit Ya Ya Ya
Baca berkomitmen Tidak Ya Ya
Bacaan yang dapat diulang Tidak No Ya
Snapshot Tidak No Tidak
Dapat diserialisasikan Tidak No Tidak

Untuk informasi selengkapnya tentang jenis penguncian atau penerapan versi baris tertentu yang dikontrol oleh setiap tingkat isolasi transaksi, lihat MENGATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL).

Tingkat isolasi transaksi dapat diatur menggunakan Transact-SQL atau melalui API database.

T-SQL
Skrip Transact-SQL menggunakan pernyataan .SET TRANSACTION ISOLATION LEVEL

ADO
Aplikasi ADO mengatur properti objek ke adXactReadUncommitted, , adXactReadCommittedadXactRepeatableRead, atau adXactReadSerializable.ConnectionIsolationLevel

ADO.NET
ADO.NET aplikasi yang menggunakan System.Data.SqlClient namespace layanan terkelola dapat memanggil SqlConnection.BeginTransaction metode dan mengatur IsolationLevel opsi ke Unspecified, , Chaos, ReadUncommittedReadCommitted, RepeatableRead, Serializable, atau Snapshot.

OLE DB
Saat memulai transaksi, aplikasi yang menggunakan panggilan ITransactionLocal::StartTransaction OLE DB dengan isoLevel diatur ke ISOLATIONLEVEL_READUNCOMMITTED, , ISOLATIONLEVEL_READCOMMITTEDISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT, atau ISOLATIONLEVEL_SERIALIZABLE.

Saat menentukan tingkat isolasi transaksi dalam mode autocommit, aplikasi OLE DB dapat mengatur properti ke , , DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSEDBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, , DBPROPVAL_TI_ISOLATED, atau DBPROPVAL_TI_SNAPSHOT.DBPROPVAL_TI_CHAOSDBPROP_SESS_AUTOCOMMITISOLEVELSDBPROPSET_SESSION

ODBC
Aplikasi ODBC memanggil SQLSetConnectAttr dengan Attribute diatur ke SQL_ATTR_TXN_ISOLATION dan ValuePtr diatur ke SQL_TXN_READ_UNCOMMITTED, , SQL_TXN_REPEATABLE_READSQL_TXN_READ_COMMITTED, atau SQL_TXN_SERIALIZABLE.

Untuk transaksi rekam jepret, aplikasi memanggil SQLSetConnectAttr dengan Atribut diatur ke SQL_COPT_SS_TXN_ISOLATION dan ValuePtr diatur ke SQL_TXN_SS_SNAPSHOT. Transaksi rekam jepret dapat diambil menggunakan SQL_COPT_SS_TXN_ISOLATION atau SQL_ATTR_TXN_ISOLATION.

Mengunci Mesin Database

Penguncian adalah mekanisme yang digunakan oleh Mesin Database SQL Server untuk menyinkronkan akses oleh beberapa pengguna ke bagian data yang sama secara bersamaan.

Sebelum transaksi memperoleh dependensi pada status data saat ini, seperti dengan membaca atau memodifikasi data, transaksi harus melindungi dirinya dari efek transaksi lain yang memodifikasi data yang sama. Transaksi melakukan ini dengan meminta kunci pada bagian data. Kunci memiliki mode yang berbeda, seperti bersama atau eksklusif. Mode kunci menentukan tingkat dependensi yang dimiliki transaksi pada data. Tidak ada transaksi yang dapat diberikan kunci yang akan bertentangan dengan mode kunci yang sudah diberikan pada data tersebut ke transaksi lain. Jika transaksi meminta mode kunci yang bertentangan dengan kunci yang telah diberikan pada data yang sama, instans Mesin Database SQL Server akan menjeda transaksi yang meminta hingga kunci pertama dirilis.

Ketika transaksi memodifikasi sepotong data, transaksi menyimpan kunci tertentu yang melindungi modifikasi hingga akhir transaksi. Berapa lama transaksi memegang kunci yang diperoleh untuk melindungi operasi baca tergantung pada pengaturan tingkat isolasi transaksi dan apakah penguncian yang dioptimalkan diaktifkan atau tidak.

  • Saat penguncian yang dioptimalkan tidak diaktifkan, kunci baris dan halaman yang diperlukan untuk penulisan ditahan hingga akhir transaksi.

  • Saat penguncian yang dioptimalkan diaktifkan, hanya kunci ID Transaksi (TID) yang ditahan selama durasi transaksi. Di bawah tingkat isolasi default, transaksi tidak akan menahan kunci baris dan halaman yang diperlukan untuk penulisan hingga akhir transaksi. Ini mengurangi memori kunci yang diperlukan dan mengurangi kebutuhan akan eskalasi kunci. Selanjutnya, ketika penguncian yang dioptimalkan diaktifkan, pengoptimalan kunci setelah kualifikasi (LAQ) mengevaluasi predikat kueri pada versi baris terbaru yang diterapkan tanpa memperoleh kunci, meningkatkan konkurensi.

Semua kunci yang dipegang oleh transaksi dirilis ketika transaksi selesai (baik penerapan atau gulung balik).

Aplikasi biasanya tidak meminta kunci secara langsung. Kunci dikelola secara internal oleh bagian dari Mesin Database SQL Server yang disebut manajer kunci. Ketika instans Mesin Database SQL Server memproses pernyataan Transact-SQL, prosesor kueri Mesin Database SQL Server menentukan sumber daya mana yang akan diakses. Prosesor kueri menentukan jenis kunci apa yang diperlukan untuk melindungi setiap sumber daya berdasarkan jenis akses dan pengaturan tingkat isolasi transaksi. Prosesor kueri kemudian meminta kunci yang sesuai dari manajer kunci. Manajer kunci memberikan kunci jika tidak ada kunci yang bertentangan yang dipegang oleh transaksi lain.

Mengunci granularitas dan hierarki

Mesin Database SQL Server memiliki penguncian multigranular yang memungkinkan berbagai jenis sumber daya dikunci oleh transaksi. Untuk meminimalkan biaya penguncian, Mesin Database SQL Server mengunci sumber daya secara otomatis pada tingkat yang sesuai dengan tugas. Penguncian pada granularitas yang lebih kecil, seperti baris, meningkatkan konkurensi tetapi memiliki overhead yang lebih tinggi karena lebih banyak kunci harus ditahan jika banyak baris dikunci. Penguncian pada granularitas yang lebih besar, seperti tabel, mahal dalam hal konkurensi karena mengunci seluruh tabel membatasi akses ke bagian mana pun dari tabel oleh transaksi lain. Namun, ia memiliki overhead yang lebih rendah karena lebih sedikit kunci yang dipertahankan.

Mesin Database SQL Server sering kali harus memperoleh kunci pada beberapa tingkat granularitas untuk sepenuhnya melindungi sumber daya. Kelompok kunci ini pada beberapa tingkat granularitas disebut hierarki kunci. Misalnya, untuk sepenuhnya melindungi pembacaan indeks, instans Mesin Database SQL Server mungkin harus memperoleh kunci berbagi pada baris dan kunci berbagi niat pada halaman dan tabel.

Tabel berikut ini memperlihatkan sumber daya yang bisa dikunci oleh Mesin Database SQL Server.

Sumber daya Deskripsi
RID Pengidentifikasi baris yang digunakan untuk mengunci satu baris dalam timbunan.
KEY Kunci baris dalam indeks yang digunakan untuk melindungi rentang kunci dalam transaksi yang dapat diserialisasikan.
PAGE Halaman 8 kilobyte (KB) dalam database, seperti data atau halaman indeks.
SEJAUH Grup yang berdekatan dengan delapan halaman, seperti data atau halaman indeks.
HoBT 1 Tumpuk atau pohon B. Kunci yang melindungi pohon B (indeks) atau halaman data timbunan dalam tabel yang tidak memiliki indeks berkluster.
TABEL 1 Seluruh tabel, termasuk semua data dan indeks.
FILE File database.
APLIKASI Sumber daya yang ditentukan aplikasi.
METADATA Kunci metadata.
ALLOCATION_UNIT Unit alokasi.
DATABASE Seluruh database.
XACT 2 Kunci ID Transaksi (TID) yang digunakan dalam Penguncian yang dioptimalkan. Lihat Penguncian ID Transaksi (TID).

1 Kunci HoBT dan TABLE dapat dipengaruhi oleh opsi LOCK_ESCALATION ALTER TABLE.

2 Sumber daya penguncian tambahan tersedia untuk sumber daya kunci XACT, lihat Penambahan diagnostik untuk penguncian yang dioptimalkan.

Mode kunci

Mesin Database SQL Server mengunci sumber daya menggunakan mode kunci yang berbeda yang menentukan bagaimana sumber daya dapat diakses oleh transaksi bersamaan.

Tabel berikut ini memperlihatkan mode kunci sumber daya yang digunakan Mesin Database SQL Server.

Mode kunci Deskripsi
Bersama (S) Digunakan untuk operasi baca yang tidak mengubah atau memperbarui data, seperti pernyataan SELECT.
Pembaruan (U) Digunakan pada sumber daya yang dapat diperbarui. Mencegah bentuk kebuntuan umum yang terjadi ketika beberapa sesi membaca, mengunci, dan berpotensi memperbarui sumber daya nanti.
Eksklusif (X) Digunakan untuk operasi modifikasi data, seperti INSERT, UPDATE, atau DELETE. Memastikan bahwa beberapa pembaruan tidak dapat dilakukan ke sumber daya yang sama secara bersamaan.
Niat Digunakan untuk membuat hierarki kunci. Jenis kunci niat adalah: niat bersama (IS), niat eksklusif (IX), dan dibagikan dengan niat eksklusif (SIX).
Skema Digunakan saat operasi bergantung pada skema tabel yang dijalankan. Jenis kunci skema adalah: modifikasi skema (Sch-M) dan stabilitas skema (Sch-S).
Pembaruan Massal (BU) Digunakan saat menyalin data secara massal ke dalam tabel dan petunjuk TABLOCK ditentukan.
Rentang kunci Melindungi rentang baris yang dibaca oleh kueri saat menggunakan tingkat isolasi transaksi yang dapat diserialisasikan. Memastikan bahwa transaksi lain tidak dapat menyisipkan baris yang akan memenuhi syarat untuk kueri transaksi yang dapat diserialisasikan jika kueri dijalankan lagi.

Kunci bersama

Kunci bersama (S) memungkinkan transaksi bersamaan untuk membaca (SELECT) sumber daya di bawah kontrol konkurensi pesimis. Tidak ada transaksi lain yang dapat memodifikasi data saat kunci bersama (S) ada di sumber daya. Kunci bersama (S) pada sumber daya dirilis segera setelah operasi baca selesai, kecuali tingkat isolasi transaksi diatur ke baca yang dapat diulang atau lebih tinggi, atau petunjuk penguncian digunakan untuk mempertahankan kunci bersama (S) selama durasi transaksi.

Memperbarui kunci

Mesin Database menempatkan kunci pembaruan (U) saat bersiap untuk menjalankan pembaruan. Kunci U kompatibel dengan kunci S, tetapi hanya satu transaksi yang dapat menahan kunci U pada satu waktu pada sumber daya tertentu. Ini adalah kunci - banyak transaksi bersamaan dapat menahan kunci S, tetapi hanya satu transaksi yang dapat menahan kunci U pada sumber daya. Kunci pembaruan (U) akhirnya ditingkatkan ke kunci eksklusif (X) untuk memperbarui baris.

Kunci pembaruan (U) juga dapat diambil oleh kueri yang tidak melakukan PEMBARUAN, ketika petunjuk tabel UPDLOCK ditentukan dalam kueri. Umum bagi aplikasi untuk menggunakan pola "pilih baris, lalu perbarui baris", di mana baca dan tulis dipisahkan secara eksplisit dalam transaksi. Dalam hal ini, jika tingkat isolasi dapat dibaca berulang atau dapat diserialisasikan, pembaruan bersamaan kemungkinan mungkin kebuntuan. Sebagai gantinya, aplikasi dapat mengikuti pola "pilih baris dengan petunjuk UPDLOCK, lalu perbarui baris".

  • Dalam transaksi baca atau serialisasi yang dapat diulang, transaksi membaca data, memperoleh kunci bersama (S) pada sumber daya, lalu memodifikasi data, yang memerlukan konversi kunci ke kunci eksklusif (X). Jika dua transaksi memperoleh kunci bersama (S) pada sumber daya dan kemudian mencoba memperbarui data secara bersamaan, satu transaksi mencoba konversi kunci ke kunci eksklusif (X). Konversi kunci bersama-ke-eksklusif harus menunggu karena kunci eksklusif untuk satu transaksi tidak kompatibel dengan kunci bersama (S) dari transaksi lain; kunci menunggu terjadi. Transaksi kedua mencoba memperoleh kunci eksklusif (X) untuk pembaruannya. Karena kedua transaksi mengonversi ke kunci eksklusif (X), dan masing-masing menunggu transaksi lain untuk melepaskan kunci bersama (S), kebuntuan terjadi.

  • Dalam tingkat isolasi penerapan baca default, kunci S ber durasi pendek, dirilis segera setelah digunakan. Kunci durasi pendek tidak mungkin menyebabkan kebuntuan.

  • Jika petunjuk UPDLOCK digunakan dalam tulis, transaksi harus memiliki akses ke versi terbaru baris. Jika versi terbaru tidak lagi terlihat, diharapkan dapat diterima Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict saat isolasi SNAPSHOT sedang digunakan. Misalnya, lihat Bekerja dengan isolasi rekam jepret.

Kunci eksklusif

Kunci eksklusif (X) mencegah akses ke sumber daya dengan transaksi bersamaan. Dengan kunci eksklusif (X), tidak ada transaksi lain yang dapat memodifikasi data; operasi baca hanya dapat berlangsung dengan penggunaan petunjuk NOLOCK atau membaca tingkat isolasi yang tidak dilakukan.

Pernyataan modifikasi data, seperti INSERT, UPDATE, dan DELETE menggabungkan operasi modifikasi dan baca. Pernyataan pertama-tama melakukan operasi baca untuk memperoleh data sebelum melakukan operasi modifikasi yang diperlukan. Oleh karena itu, pernyataan modifikasi data biasanya meminta kunci bersama dan kunci eksklusif. Misalnya, pernyataan UPDATE mungkin mengubah baris dalam satu tabel berdasarkan gabungan dengan tabel lain. Dalam hal ini, pernyataan UPDATE meminta kunci bersama pada baris yang dibaca dalam tabel gabungan selain meminta kunci eksklusif pada baris yang diperbarui.

Kunci niat

Mesin Database SQL Server menggunakan kunci niat untuk melindungi penempatan kunci bersama (S) atau kunci eksklusif (X) pada sumber daya yang lebih rendah dalam hierarki kunci. Kunci niat diberi nama "kunci niat" karena diperoleh sebelum kunci di tingkat yang lebih rendah dan, oleh karena itu, niat sinyal untuk menempatkan kunci pada tingkat yang lebih rendah.

Kunci niat melayani dua tujuan:

  • Untuk mencegah transaksi lain memodifikasi sumber daya tingkat lebih tinggi dengan cara yang akan membatalkan kunci di tingkat yang lebih rendah.
  • Untuk meningkatkan efisiensi Mesin Database SQL Server dalam mendeteksi konflik kunci pada tingkat granularitas yang lebih tinggi.

Misalnya, kunci niat bersama diminta pada tingkat tabel sebelum kunci bersama (S) diminta pada halaman atau baris dalam tabel tersebut. Mengatur kunci niat di tingkat tabel mencegah transaksi lain untuk kemudian memperoleh kunci eksklusif (X) pada tabel yang berisi halaman tersebut. Kunci niat meningkatkan performa karena Mesin Database SQL Server memeriksa kunci niat hanya pada tingkat tabel untuk menentukan apakah transaksi dapat memperoleh kunci dengan aman pada tabel tersebut. Ini menghapus persyaratan untuk memeriksa setiap baris atau kunci halaman pada tabel untuk menentukan apakah transaksi dapat mengunci seluruh tabel.

Kunci niat termasuk niat bersama (IS), niat eksklusif (IX), dan dibagikan dengan niat eksklusif (SIX).

Mode kunci Deskripsi
Berbagi niat (IS) Melindungi kunci bersama yang diminta atau diperoleh pada beberapa (tetapi tidak semua) sumber daya yang lebih rendah dalam hierarki.
Niat eksklusif (IX) Melindungi kunci eksklusif yang diminta atau diperoleh pada beberapa (tetapi tidak semua) sumber daya yang lebih rendah dalam hierarki. IX adalah superset IS, dan juga melindungi permintaan kunci bersama pada sumber daya tingkat yang lebih rendah.
Dibagikan dengan niat eksklusif (SIX) Melindungi kunci bersama yang diminta atau diperoleh pada semua sumber daya yang lebih rendah dalam hierarki dan niat kunci eksklusif pada beberapa (tetapi tidak semua) sumber daya tingkat bawah. Kunci IS bersamaan di sumber daya tingkat atas diizinkan. Misalnya, memperoleh kunci SIX pada tabel juga memperoleh kunci eksklusif niat pada halaman yang dimodifikasi dan kunci eksklusif pada baris yang dimodifikasi. Hanya ada satu kunci SIX per sumber daya pada satu waktu, mencegah pembaruan pada sumber daya yang dibuat oleh transaksi lain, meskipun transaksi lain dapat membaca sumber daya yang lebih rendah dalam hierarki dengan mendapatkan kunci IS di tingkat tabel.
Pembaruan niat (IU) Melindungi kunci pembaruan yang diminta atau diperoleh pada semua sumber daya yang lebih rendah dalam hierarki. Kunci IU hanya digunakan pada sumber daya halaman. Kunci IU dikonversi ke kunci IX jika operasi pembaruan terjadi.
Pembaruan niat bersama (SIU) Kombinasi kunci S dan IU, sebagai hasil dari memperoleh kunci ini secara terpisah dan bersamaan memegang kedua kunci. Misalnya, transaksi menjalankan kueri dengan petunjuk PAGLOCK lalu menjalankan operasi pembaruan. Kueri dengan petunjuk PAGLOCK memperoleh kunci S, dan operasi pembaruan memperoleh kunci IU.
Memperbarui niat eksklusif (UIX) Kombinasi dari Anda dan kunci IX, sebagai hasil dari memperoleh kunci ini secara terpisah dan bersamaan memegang kedua kunci.

Kunci skema

Mesin Database SQL Server menggunakan kunci modifikasi skema (Sch-M) selama operasi bahasa definisi data tabel (DDL), seperti menambahkan kolom atau menjatuhkan tabel. Selama waktu dipegang, kunci Sch-M mencegah akses bersamaan ke tabel. Ini berarti kunci Sch-M memblokir semua operasi luar hingga kunci dilepaskan.

Beberapa operasi bahasa manipulasi data (DML), seperti pemotongan tabel, menggunakan kunci Sch-M untuk mencegah akses ke tabel yang terpengaruh oleh operasi bersamaan.

Mesin Database SQL Server menggunakan kunci stabilitas skema (Sch-S) saat mengkompilasi dan menjalankan kueri. Kunci Sch-S tidak memblokir kunci transaksional apa pun, termasuk kunci eksklusif (X). Oleh karena itu, transaksi lain, termasuk yang memiliki kunci X pada tabel, terus berjalan saat kueri sedang dikompilasi. Namun, operasi DDL bersamaan, dan operasi DML bersamaan yang memperoleh kunci Sch-M, tidak dapat dilakukan pada tabel.

Kunci pembaruan massal

Kunci pembaruan massal (BU) memungkinkan beberapa utas memuat data secara massal secara bersamaan ke dalam tabel yang sama sambil mencegah proses lain yang tidak memuat data secara massal mengakses tabel. Mesin Database SQL Server menggunakan kunci pembaruan massal (BU) ketika kedua kondisi berikut ini benar.

  • Anda menggunakan pernyataan TRANSACT-SQL BULK INSERT, atau fungsi OPENROWSET(BULK), atau Anda menggunakan salah satu perintah Insert API Massal seperti .NET SqlBulkCopy, OLEDB Fast Load API, atau API Salinan Massal ODBC untuk menyalin data secara massal ke dalam tabel.
  • Petunjuk TABLOCK ditentukan atau opsi kunci tabel pada tabel beban massal diatur menggunakan sp_tableoption.

Tip

Tidak seperti pernyataan BULK INSERT, yang menyimpan kunci Pembaruan Massal (BU) yang kurang ketat, INSERT INTO... SELECT dengan petunjuk TABLOCK memegang kunci eksklusif niat (IX) pada tabel. Ini berarti Anda tidak dapat menyisipkan baris menggunakan operasi sisipkan paralel.

Kunci rentang kunci

Kunci rentang kunci melindungi rentang baris yang secara implisit disertakan dalam kumpulan catatan yang dibaca oleh pernyataan Transact-SQL saat menggunakan tingkat isolasi transaksi yang dapat diserialisasikan. Penguncian rentang kunci mencegah pembacaan phantom. Dengan melindungi rentang kunci antar baris, itu juga mencegah penyisipan atau penghapusan phantom ke dalam kumpulan catatan yang diakses oleh transaksi.

Kompatibilitas kunci

Kompatibilitas kunci mengontrol apakah beberapa transaksi dapat memperoleh kunci pada sumber daya yang sama secara bersamaan. Jika sumber daya sudah dikunci oleh transaksi lain, permintaan kunci baru hanya dapat diberikan jika mode kunci yang diminta kompatibel dengan mode kunci yang ada. Jika mode kunci yang diminta tidak kompatibel dengan kunci yang ada, transaksi yang meminta kunci baru menunggu kunci yang ada dilepaskan atau interval batas waktu penguncian kedaluwarsa. Misalnya, tidak ada mode kunci yang kompatibel dengan kunci eksklusif. Saat kunci eksklusif (X) ditahan, tidak ada transaksi lain yang dapat memperoleh kunci apa pun (bersama, memperbarui, atau eksklusif) pada sumber daya tersebut sampai kunci eksklusif (X) dirilis. Atau, jika kunci bersama (S) telah diterapkan ke sumber daya, transaksi lain juga dapat memperoleh kunci bersama atau kunci pembaruan (U) pada item tersebut meskipun transaksi pertama belum selesai. Namun, transaksi lain tidak dapat memperoleh kunci eksklusif hingga kunci bersama dirilis.

Tabel berikut menunjukkan kompatibilitas mode kunci yang paling umum ditemui.

Mode yang diberikan yang ada IS S U IX ENAM X
Mode yang diminta
Berbagi niat (IS) Ya Ya Ya Ya Ya Tidak
Bersama (S) Ya Ya Ya No No Tidak
Pembaruan (U) Ya Ya No No No Tidak
Niat eksklusif (IX) Ya No No Ya No Tidak
Dibagikan dengan niat eksklusif (SIX) Ya No No No No Tidak
Eksklusif (X) Tidak No No No No No

Catatan

Kunci eksklusif niat (IX) kompatibel dengan mode kunci IX karena IX berarti niatnya adalah memperbarui hanya beberapa baris daripada semuanya. Transaksi lain yang mencoba membaca atau memperbarui beberapa baris juga diizinkan selama baris tersebut bukan baris yang sama yang diperbarui oleh transaksi lain. Selanjutnya, jika dua transaksi mencoba memperbarui baris yang sama, kedua transaksi akan diberikan kunci IX pada tingkat tabel dan halaman. Namun, satu transaksi akan diberikan kunci X pada tingkat baris. Transaksi lain harus menunggu hingga kunci tingkat baris dihapus.

Gunakan tabel berikut untuk menentukan kompatibilitas semua mode kunci yang tersedia di SQL Server.

A table showing a matrix of lock conflicts and compatibility.

Penguncian rentang kunci

Kunci rentang kunci melindungi rentang baris yang secara implisit disertakan dalam kumpulan catatan yang dibaca oleh pernyataan Transact-SQL saat menggunakan tingkat isolasi transaksi yang dapat diserialisasikan. Tingkat isolasi yang dapat diserialisasikan mengharuskan setiap kueri yang dijalankan selama transaksi harus mendapatkan set baris yang sama setiap kali dijalankan selama transaksi. Kunci rentang kunci melindungi persyaratan ini dengan mencegah transaksi lain menyisipkan baris baru yang kuncinya akan berada dalam rentang kunci yang dibaca oleh transaksi yang dapat diserialisasikan.

Penguncian rentang kunci mencegah pembacaan phantom. Dengan melindungi rentang kunci antar baris, itu juga mencegah penyisipan phantom ke dalam satu set rekaman yang diakses oleh transaksi.

Kunci rentang kunci ditempatkan pada indeks, menentukan nilai kunci awal dan akhir. Kunci ini memblokir setiap upaya untuk menyisipkan, memperbarui, atau menghapus baris apa pun dengan nilai kunci yang berada dalam rentang karena operasi tersebut pertama-tama harus memperoleh kunci pada indeks. Misalnya, transaksi yang dapat diserialisasikan dapat mengeluarkan SELECT pernyataan yang membaca semua baris yang nilai kuncinya cocok dengan kondisi BETWEEN 'AAA' AND 'CZZ'. Kunci rentang kunci pada nilai kunci dalam rentang dari 'AAA' hingga 'CZZ' mencegah transaksi lain menyisipkan baris dengan nilai kunci di mana saja dalam rentang tersebut, seperti 'ADG', 'BBD', atau 'CAL'.

Mode kunci rentang kunci

Kunci rentang kunci mencakup rentang dan komponen baris yang ditentukan dalam format baris rentang:

  • Rentang mewakili mode kunci yang melindungi rentang antara dua entri indeks berturut-turut.
  • Baris mewakili mode kunci yang melindungi entri indeks.
  • Mode mewakili mode kunci gabungan yang digunakan. Mode kunci rentang kunci terdiri dari dua bagian. Yang pertama mewakili jenis kunci yang digunakan untuk mengunci rentang indeks (RentangT) dan yang kedua mewakili jenis kunci yang digunakan untuk mengunci kunci tertentu (K). Dua bagian terhubung dengan tanda hubung (-), seperti RentangT-K.
Rentang Baris Mode Deskripsi
Rentang S Rentang-S Rentang bersama, kunci sumber daya bersama; pemindaian rentang yang dapat diserialisasikan.
Rentang U Rentang-U Rentang bersama, perbarui kunci sumber daya; pemindaian pembaruan yang dapat diserialisasikan.
RangeI Null RangeI-N Sisipkan rentang, kunci sumber daya null; digunakan untuk menguji rentang sebelum memasukkan kunci baru ke dalam indeks.
RangeX X RangeX-X Rentang eksklusif, kunci sumber daya eksklusif; digunakan saat memperbarui kunci dalam rentang.

Catatan

Mode kunci Null internal kompatibel dengan semua mode kunci lainnya.

Mode kunci rentang kunci memiliki matriks kompatibilitas yang menunjukkan kunci mana yang kompatibel dengan kunci lain yang diperoleh pada kunci dan rentang yang tumpang tindih.

Mode yang diberikan yang ada S U X Rentang-S Rentang-U RangeI-N RangeX-X
Mode yang diminta
Bersama (S) Ya Ya No Ya Ya Ya Tidak
Pembaruan (U) Ya No No Ya No Ya Tidak
Eksklusif (X) Tidak No No No No Ya Tidak
Rentang-S Ya Ya No Ya Ya No Tidak
Rentang-U Ya No No Ya No No Tidak
RangeI-N Ya Ya Ya No No Ya Tidak
RangeX-X Tidak No No No No No Tidak

Penguncian konversi

Kunci konversi dibuat saat kunci rentang kunci tumpang tindih dengan kunci lain.

Kunci 1 Kunci 2 Kunci konversi
S RangeI-N RangeI-S
U RangeI-N RangeI-U
X RangeI-N RangeI-X
RangeI-N Rentang-S RangeX-S
RangeI-N Rentang-U RangeX-U

Penguncian konversi dapat diamati untuk waktu yang singkat dalam keadaan kompleks yang berbeda, kadang-kadang saat menjalankan proses bersamaan.

Pemindaian rentang yang dapat diserialisasikan, pengambilan singleton, hapus, dan sisipkan

Penguncian rentang kunci memastikan bahwa operasi berikut dapat diserialisasikan:

  • Kueri pemindaian rentang
  • Pengambilan singleton dari baris yang tidak ada
  • Operasi hapus
  • Operasi Sisipkan

Sebelum penguncian rentang kunci dapat terjadi, kondisi berikut harus dipenuhi:

  • Tingkat isolasi transaksi harus diatur ke SERIALIZABLE.
  • Prosesor kueri harus menggunakan indeks untuk mengimplementasikan predikat filter rentang. Misalnya, klausa WHERE dalam pernyataan SELECT dapat menetapkan kondisi rentang dengan predikat ini: ColumnX BETWEEN N**'AAA'** DAN N**'CZZ'**. Kunci rentang kunci hanya dapat diperoleh jika ColumnX dicakup oleh kunci indeks.

Contoh

Tabel dan indeks berikut digunakan sebagai dasar untuk contoh penguncian rentang kunci yang mengikutinya.

A diagram of a sample of a Btree.

Kueri pemindaian rentang

Untuk memastikan kueri pemindaian rentang dapat diserialisasikan, kueri yang sama harus mengembalikan hasil yang sama setiap kali dijalankan dalam transaksi yang sama. Baris baru tidak boleh disisipkan dalam kueri pemindaian rentang oleh transaksi lain; jika tidak, ini menjadi sisipan phantom. Misalnya, kueri berikut menggunakan tabel dan indeks dalam ilustrasi sebelumnya:

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

Kunci rentang kunci ditempatkan pada entri indeks yang sesuai dengan rentang baris data di mana nama berada di antara nilai Adam dan Dale, mencegah baris baru yang memenuhi syarat dalam kueri sebelumnya ditambahkan atau dihapus. Meskipun nama depan dalam rentang ini adalah Adam, kunci rentang kunci mode RangeS-S pada entri indeks ini memastikan bahwa tidak ada nama baru yang dimulai dengan huruf A dapat ditambahkan sebelum Adam, seperti Abigail. Demikian pula, kunci rentang kunci RangeS-S pada entri indeks untuk Dale memastikan bahwa tidak ada nama baru yang dimulai dengan huruf C dapat ditambahkan setelah Carlos, seperti Clive.

Catatan

Jumlah kunci RangeS-S yang disimpan adalah n+1, di mana n adalah jumlah baris yang memenuhi kueri.

Pengambilan singleton dari data yang tidak ada

Jika kueri dalam transaksi mencoba memilih baris yang tidak ada, mengeluarkan kueri di titik selanjutnya dalam transaksi yang sama harus mengembalikan hasil yang sama. Tidak ada transaksi lain yang dapat diizinkan untuk menyisipkan baris yang tidak ada. Misalnya, mengingat kueri ini:

SELECT name
FROM mytable
WHERE name = 'Bill';

Kunci rentang kunci ditempatkan pada entri indeks yang sesuai dengan rentang nama dari Ben ke Bing karena nama Bill akan dimasukkan di antara dua entri indeks yang berdekatan ini. Kunci rentang kunci mode RangeS-S ditempatkan pada entri Bingindeks . Ini mencegah transaksi lain menyisipkan nilai, seperti Bill, antara entri Ben indeks dan Bing.

Hapus operasi, tanpa penguncian yang dioptimalkan

Saat menghapus nilai dalam transaksi, rentang nilai tidak harus dikunci selama durasi transaksi yang melakukan operasi penghapusan. Mengunci nilai kunci yang dihapus hingga akhir transaksi cukup untuk mempertahankan serialisasi. Misalnya, mengingat pernyataan DELETE ini:

DELETE mytable
WHERE name = 'Bob';

Kunci eksklusif (X) ditempatkan pada entri indeks yang sesuai dengan nama Bob. Transaksi lain dapat menyisipkan atau menghapus nilai sebelum atau sesudah nilai Bobyang dihapus . Namun, setiap transaksi yang mencoba membaca, menyisipkan, atau menghapus nilai Bob akan diblokir hingga transaksi penghapusan dilakukan atau digulung balik. (Opsi database READ_COMMITTED_SNAPSHOT dan tingkat isolasi SNAPSHOT juga memungkinkan pembacaan dari versi baris dari status yang diterapkan sebelumnya.)

Penghapusan rentang dapat dijalankan menggunakan tiga mode kunci dasar: baris, halaman, atau kunci tabel. Strategi penguncian baris, halaman, atau tabel diputuskan oleh Pengoptimal Kueri atau dapat ditentukan oleh pengguna melalui petunjuk Pengoptimal Kueri seperti ROWLOCK, PAGLOCK, atau TABLOCK. Ketika PAGLOCK atau TABLOCK digunakan, Mesin Database SQL Server segera membatalkan alokasi halaman indeks jika semua baris dihapus dari halaman ini. Sebaliknya, ketika ROWLOCK digunakan, semua baris yang dihapus hanya ditandai sebagai dihapus; mereka dihapus dari halaman indeks nanti menggunakan tugas latar belakang.

Hapus operasi, dengan penguncian yang dioptimalkan

Saat menghapus nilai dalam transaksi, kunci baris dan halaman diperoleh dan dirilis secara bertahap, dan tidak disimpan selama transaksi. Misalnya, mengingat pernyataan DELETE ini:

DELETE mytable
WHERE name = 'Bob';

Kunci TID ditempatkan pada semua baris yang dimodifikasi selama durasi transaksi. Kunci diperoleh pada TID entri indeks yang sesuai dengan nama Bob. Dengan penguncian yang dioptimalkan, kunci halaman dan baris terus diperoleh untuk pembaruan, tetapi setiap kunci halaman dan baris dirilis segera setelah setiap baris diperbarui. Kunci TID melindungi baris agar tidak diperbarui hingga transaksi selesai. Setiap transaksi yang mencoba membaca, menyisipkan, atau menghapus nilai Bob akan diblokir hingga transaksi penghapusan dilakukan atau digulung balik. (Opsi database READ_COMMITTED_SNAPSHOT dan tingkat isolasi SNAPSHOT juga memungkinkan pembacaan dari versi baris dari status yang diterapkan sebelumnya.)

Jika tidak, mekanisme penguncian operasi penghapusan sama dengan tanpa penguncian yang dioptimalkan.

Sisipkan operasi tanpa penguncian yang dioptimalkan

Saat menyisipkan nilai dalam transaksi, rentang yang termasuk dalam nilai tidak harus dikunci selama durasi transaksi yang melakukan operasi penyisipan. Mengunci nilai kunci yang disisipkan hingga akhir transaksi cukup untuk mempertahankan serialisasi. Misalnya, mengingat pernyataan INSERT ini:

INSERT mytable VALUES ('Dan');

Kunci rentang kunci mode RangeI-N ditempatkan pada entri indeks yang sesuai dengan nama David untuk menguji rentang. Jika kunci diberikan, Dan dimasukkan dan kunci eksklusif (X) ditempatkan pada nilai Dan. Kunci rentang kunci mode RangeI-N hanya diperlukan untuk menguji rentang dan tidak ditahan selama transaksi yang melakukan operasi penyisipan. Transaksi lain dapat menyisipkan atau menghapus nilai sebelum atau sesudah nilai Danyang disisipkan . Namun, setiap transaksi yang mencoba membaca, menyisipkan, atau menghapus nilai Dan akan dikunci hingga transaksi penyisipan dilakukan atau digulung balik.

Sisipkan operasi dengan penguncian yang dioptimalkan

Saat menyisipkan nilai dalam transaksi, rentang yang termasuk dalam nilai tidak harus dikunci selama durasi transaksi yang melakukan operasi penyisipan. Kunci baris dan halaman jarang diperoleh, hanya ketika ada pembangunan ulang indeks online yang sedang berlangsung, atau ketika ada transaksi yang dapat diserialisasikan dalam instans. Jika kunci baris dan halaman diperoleh, kunci tersebut dirilis dengan cepat dan tidak ditahan selama durasi transaksi. Menempatkan kunci TID eksklusif pada nilai kunci yang dimasukkan hingga akhir transaksi cukup untuk mempertahankan serialisasi. Misalnya, mengingat pernyataan INSERT ini:

INSERT mytable VALUES ('Dan');

Dengan penguncian yang dioptimalkan, kunci RangeI-N hanya diperoleh jika setidaknya ada satu transaksi yang menggunakan tingkat isolasi SERIALIZABLE dalam instans. Kunci rentang kunci mode RangeI-N ditempatkan pada entri indeks yang sesuai dengan nama David untuk menguji rentang. Jika kunci diberikan, Dan dimasukkan dan kunci eksklusif (X) ditempatkan pada nilai Dan. Kunci rentang kunci mode RangeI-N hanya diperlukan untuk menguji rentang dan tidak ditahan selama transaksi yang melakukan operasi penyisipan. Transaksi lain dapat menyisipkan atau menghapus nilai sebelum atau sesudah nilai Danyang disisipkan . Namun, setiap transaksi yang mencoba membaca, menyisipkan, atau menghapus nilai Dan akan dikunci hingga transaksi penyisipan dilakukan atau digulung balik.

Eskalasi kunci

Eskalasi kunci adalah proses mengonversi banyak kunci berbutir halus menjadi lebih sedikit kunci butir kasar, mengurangi overhead sistem sambil meningkatkan probabilitas ketidakcocokan konkurensi.

Eskalasi kunci bersifat berbeda tergantung pada apakah penguncian yang dioptimalkan diaktifkan.

Eskalasi penguncian tanpa penguncian yang dioptimalkan

Karena Mesin Database SQL Server memperoleh kunci tingkat rendah, mesin ini juga menempatkan kunci niat pada objek yang berisi objek tingkat bawah:

  • Saat mengunci baris atau rentang kunci indeks, Mesin Database menempatkan kunci niat pada halaman yang berisi baris atau kunci.
  • Saat mengunci halaman, Mesin Database menempatkan kunci niat pada objek tingkat lebih tinggi yang berisi halaman. Selain kunci niat pada objek, kunci halaman niat diminta pada objek berikut:
    • Halaman tingkat daun dari indeks nonclustered
    • Halaman data indeks berkluster
    • Halaman data tumpuk

Mesin Database mungkin melakukan penguncian baris dan halaman untuk pernyataan yang sama untuk meminimalkan jumlah kunci dan mengurangi kemungkinan eskalasi kunci akan diperlukan. Misalnya, Mesin Database dapat menempatkan kunci halaman pada indeks non-kluster (jika kunci yang cukup berdekatan dalam simpul indeks dipilih untuk memenuhi kueri) dan kunci baris pada data.

Untuk meningkatkan kunci, Mesin Database mencoba mengubah kunci niat pada tabel ke kunci penuh yang sesuai, misalnya, mengubah kunci eksklusif niat (IX) ke kunci eksklusif (X), atau kunci bersama niat (IS) ke kunci bersama (S). Jika upaya eskalasi kunci berhasil dan kunci tabel penuh diperoleh, maka semua kunci tumpukan atau pohon B, halaman (HALAMAN), atau tingkat baris (RID) yang dipegang oleh transaksi pada tumpukan atau indeks dilepaskan. Jika kunci penuh tidak dapat diperoleh, tidak ada eskalasi kunci yang terjadi pada saat itu dan Mesin Database akan terus memperoleh baris, kunci, atau kunci halaman.

Mesin Database tidak meningkatkan kunci baris atau rentang kunci ke kunci halaman, tetapi meningkatkannya langsung ke kunci tabel. Demikian pula, kunci halaman selalu dinaikkan ke kunci tabel. Penguncian tabel yang dipartisi dapat meningkat ke tingkat HoBT untuk partisi terkait alih-alih ke kunci tabel. Kunci tingkat HoBT tidak selalu mengunci HoBTs yang selaras untuk partisi.

Catatan

Kunci tingkat HoBT biasanya meningkatkan konkurensi, tetapi memperkenalkan potensi kebuntuan ketika transaksi yang mengunci partisi yang berbeda masing-masing ingin memperluas kunci eksklusif mereka ke partisi lain. Dalam kasus yang jarang terjadi, granularitas penguncian TABLE mungkin berkinerja lebih baik.

Jika upaya eskalasi kunci gagal karena kunci yang bertentangan yang dipegang oleh transaksi bersamaan, Mesin Database akan mencoba kembali eskalasi kunci untuk setiap 1.250 kunci tambahan yang diperoleh oleh transaksi.

Setiap peristiwa eskalasi beroperasi terutama pada tingkat satu pernyataan Transact-SQL. Ketika peristiwa dimulai, Mesin Database mencoba untuk meningkatkan semua kunci yang dimiliki oleh transaksi saat ini dalam salah satu tabel yang telah direferensikan oleh pernyataan aktif asalkan memenuhi persyaratan ambang eskalasi. Jika peristiwa eskalasi dimulai sebelum pernyataan mengakses tabel, tidak ada upaya yang dilakukan untuk meningkatkan kunci pada tabel tersebut. Jika eskalasi kunci berhasil, setiap kunci yang diperoleh oleh transaksi dalam pernyataan sebelumnya dan masih dipegang pada saat peristiwa dimulai akan dieskalasikan jika tabel dirujuk oleh pernyataan saat ini dan disertakan dalam peristiwa eskalasi.

Misalnya, asumsikan bahwa sesi melakukan operasi ini:

  • Memulai transaksi.
  • Pembaruan TableA. Ini menghasilkan kunci baris eksklusif di TableA yang ditahan hingga transaksi selesai.
  • Pembaruan TableB. Ini menghasilkan kunci baris eksklusif di TableB yang ditahan hingga transaksi selesai.
  • Melakukan SELECT yang bergabung TableA dengan TableC. Rencana eksekusi kueri memanggil baris yang akan diambil dari TableA sebelum baris diambil dari TableC.
  • Pernyataan SELECT memicu eskalasi kunci saat mengambil baris dari TableA dan sebelum diakses TableC.

Jika eskalasi kunci berhasil, hanya kunci yang dipegang oleh sesi yang dieskalasikan TableA . Ini termasuk kunci bersama dari pernyataan SELECT dan kunci eksklusif dari pernyataan UPDATE sebelumnya. Meskipun hanya kunci yang diperoleh TableA sesi untuk pernyataan SELECT yang dihitung untuk menentukan apakah eskalasi kunci harus dilakukan, setelah eskalasi berhasil semua kunci yang dipegang oleh sesi di TableA dieskalasikan ke kunci eksklusif pada tabel, dan semua kunci granularitas bawah lainnya, termasuk kunci niat, dilepaskan TableA .

Tidak ada upaya yang dilakukan untuk meningkatkan kunci karena TableB tidak ada referensi aktif ke TableB dalam pernyataan SELECT. Demikian pula tidak ada upaya yang dilakukan untuk meningkatkan kunci pada , yang tidak dieskalasikan TableCkarena belum diakses ketika eskalasi terjadi.

Eskalasi kunci dengan penguncian yang dioptimalkan

Penguncian yang dioptimalkan membantu mengurangi memori kunci karena sangat sedikit kunci yang ditahan selama transaksi. Karena Mesin Database SQL Server memperoleh kunci baris dan halaman, eskalasi kunci dapat terjadi serupa, tetapi jauh lebih jarang. Penguncian yang dioptimalkan biasanya berhasil menghindari eskalasi kunci, menurunkan jumlah kunci dan jumlah memori kunci yang diperlukan.

Saat penguncian yang dioptimalkan diaktifkan, dan di tingkat isolasi READ COMMITTED default, mesin database merilis baris dan kunci halaman segera setelah penulisan selesai. Tidak ada kunci baris dan halaman yang disimpan selama durasi transaksi, kecuali untuk satu kunci ID Transaksi (TID). Ini mengurangi kemungkinan eskalasi kunci.

Kunci ambang eskalasi

Eskalasi kunci dipicu saat eskalasi kunci tidak dinonaktifkan pada tabel dengan menggunakan ALTER TABLE SET LOCK_ESCALATION opsi , dan ketika salah satu kondisi berikut ada:

  • Satu pernyataan Transact-SQL memperoleh setidaknya 5.000 kunci pada satu tabel atau indeks yang tidak dipartisi.
  • Satu pernyataan Transact-SQL memperoleh setidaknya 5.000 kunci pada satu partisi tabel yang dipartisi dan ALTER TABLE SET LOCK_ESCALATION opsi diatur ke AUTO.
  • Jumlah kunci dalam instans Mesin Database melebihi memori atau ambang konfigurasi.

Jika kunci tidak dapat dinaikkan karena konflik kunci, Mesin Database secara berkala memicu eskalasi kunci pada setiap 1.250 kunci baru yang diperoleh.

Ambang eskalasi untuk pernyataan Transact-SQL

Ketika Mesin Database memeriksa kemungkinan eskalasi pada setiap 1.250 kunci yang baru diperoleh, eskalasi kunci akan terjadi jika dan hanya jika pernyataan Transact-SQL telah memperoleh setidaknya 5.000 kunci pada satu referensi tabel. Eskalasi kunci dipicu ketika pernyataan Transact-SQL memperoleh setidaknya 5.000 kunci pada satu referensi tabel. Misalnya, eskalasi kunci tidak dipicu jika pernyataan memperoleh 3.000 kunci dalam satu indeks dan 3.000 kunci di indeks lain dari tabel yang sama. Demikian pula, eskalasi kunci tidak dipicu jika pernyataan memiliki gabungan mandiri pada tabel, dan setiap referensi ke tabel hanya memperoleh 3.000 kunci dalam tabel.

Eskalasi kunci hanya terjadi untuk tabel yang telah diakses pada saat eskalasi dipicu. Asumsikan bahwa satu pernyataan SELECT adalah gabungan yang mengakses tiga tabel dalam urutan ini: TableA, , TableBdan TableC. Pernyataan ini memperoleh 3.000 kunci baris dalam indeks berkluster untuk TableA dan setidaknya 5.000 kunci baris dalam indeks berkluster untuk TableB, tetapi belum diakses TableC. Ketika Mesin Database mendeteksi bahwa pernyataan telah memperoleh setidaknya 5.000 kunci baris di TableB, mesin database mencoba untuk meningkatkan semua kunci yang dipegang oleh transaksi saat ini pada TableB. Ini juga mencoba untuk meningkatkan semua kunci yang disimpan oleh transaksi saat ini pada TableA, tetapi karena jumlah kunci pada TableA kurang dari 5.000, eskalasi tidak akan berhasil. Tidak ada eskalasi kunci yang dicoba TableC karena belum diakses ketika eskalasi terjadi.

Ambang eskalasi untuk instans Mesin Database

Setiap kali jumlah kunci lebih besar dari ambang memori untuk eskalasi kunci, Mesin Database memicu eskalasi kunci. Ambang memori tergantung pada pengaturan opsi konfigurasi kunci:

  • Jika opsi kunci diatur ke pengaturan default 0, maka ambang eskalasi kunci tercapai ketika memori yang digunakan oleh objek kunci adalah 24 persen dari memori yang digunakan oleh Mesin Database, tidak termasuk memori AWE. Struktur data yang digunakan untuk mewakili kunci panjangnya sekitar 100 byte. Ambang batas ini dinamis karena Mesin Database secara dinamis memperoleh dan membebaskan memori untuk menyesuaikan beban kerja yang bervariasi.

  • Jika opsi kunci adalah nilai selain 0, maka ambang eskalasi kunci adalah 40 persen (atau kurang jika ada tekanan memori) dari nilai opsi kunci.

Mesin Database dapat memilih pernyataan aktif apa pun dari sesi apa pun untuk eskalasi, dan untuk setiap 1.250 kunci baru, mesin database akan memilih pernyataan untuk eskalasi selama memori kunci yang digunakan dalam instans tetap berada di atas ambang batas.

Meningkatkan jenis kunci campuran

Ketika eskalasi kunci terjadi, kunci yang dipilih untuk tumpukan atau indeks cukup kuat untuk memenuhi persyaratan kunci tingkat bawah yang paling ketat.

Misalnya, asumsikan sesi:

  • Memulai transaksi.
  • Memperbarui tabel yang berisi indeks berkluster.
  • Mengeluarkan pernyataan SELECT yang mereferensikan tabel yang sama.

Pernyataan UPDATE memperoleh kunci ini:

  • Kunci eksklusif (X) pada baris data yang diperbarui.
  • Kunci intent exclusive (IX) pada halaman indeks berkluster yang berisi baris tersebut.
  • Kunci IX pada indeks berkluster dan yang lain pada tabel.

Pernyataan SELECT memperoleh kunci ini:

  • Kunci bersama (S) pada semua baris data yang dibacanya, kecuali baris sudah dilindungi oleh kunci X dari pernyataan UPDATE.
  • Kunci Berbagi Niat pada semua halaman indeks berkluster yang berisi baris tersebut, kecuali halaman sudah dilindungi oleh kunci IX.
  • Tidak ada kunci pada indeks atau tabel berkluster karena sudah dilindungi oleh kunci IX.

Jika pernyataan SELECT memperoleh kunci yang cukup untuk memicu eskalasi kunci dan eskalasi berhasil, kunci IX pada tabel dikonversi ke kunci X, dan semua baris, halaman, dan kunci indeks dibeberkan. Pembaruan dan bacaan dilindungi oleh kunci X pada tabel.

Mengurangi penguncian dan eskalasi

Dalam kebanyakan kasus, Mesin Database memberikan performa terbaik saat beroperasi dengan pengaturan defaultnya untuk mengunci dan mengunci eskalasi.

Jika instans Mesin Database menghasilkan banyak kunci dan sering melihat eskalasi kunci, pertimbangkan untuk mengurangi jumlah penguncian dengan strategi berikut:

  • Gunakan tingkat isolasi yang tidak menghasilkan kunci bersama untuk operasi baca:

    • TINGKAT isolasi READ COMMITTED saat opsi database READ_COMMITTED_SNAPSHOT AKTIF.

    • Tingkat isolasi SNAPSHOT.

    • BACA tingkat isolasi YANG TIDAK DIKOMIT. Ini hanya dapat digunakan untuk sistem yang dapat beroperasi dengan bacaan kotor.

      Catatan

      Mengubah tingkat isolasi memengaruhi semua tabel pada instans Mesin Database.

  • Gunakan petunjuk tabel PAGLOCK atau TABLOCK agar halaman penggunaan Mesin Database, tumpukan, atau kunci indeks alih-alih kunci tingkat rendah. Namun, menggunakan opsi ini, meningkatkan masalah pengguna yang memblokir pengguna lain yang mencoba mengakses data yang sama dan tidak boleh digunakan dalam sistem dengan lebih dari beberapa pengguna bersamaan.

  • Saat penguncian yang dioptimalkan tidak diaktifkan, untuk tabel yang dipartisi, gunakan opsi LOCK_ESCALATION ALTER TABLE untuk meningkatkan kunci ke tingkat HoBT alih-alih tabel atau untuk menonaktifkan eskalasi kunci.

  • Memecah operasi batch besar menjadi beberapa operasi yang lebih kecil. Misalnya, Anda menjalankan kueri berikut untuk menghapus beberapa ratus ribu rekaman lama dari tabel audit, lalu Anda menemukan bahwa itu menyebabkan eskalasi kunci yang memblokir pengguna lain:

    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'

    Dengan menghapus rekaman ini beberapa ratus sekaligus, Anda dapat secara dramatis mengurangi jumlah kunci yang terakumulasi per transaksi dan mencegah eskalasi kunci. Contohnya:

    SET ROWCOUNT 500
delete_more:
  DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

  • Kurangi jejak kunci kueri dengan membuat kueri seefisien mungkin. Pemindaian besar atau sejumlah besar Pencarian Bookmark dapat meningkatkan kemungkinan eskalasi kunci; selain itu, ini meningkatkan kemungkinan kebuntuan, dan umumnya berdampak buruk pada konkurensi dan performa. Setelah Anda menemukan kueri yang menyebabkan eskalasi kunci, cari peluang untuk membuat indeks baru atau menambahkan kolom ke indeks yang ada untuk menghapus pemindaian indeks atau tabel dan untuk memaksimalkan efisiensi pencarian indeks. Pertimbangkan untuk menggunakan Konsultan Penyetelan Mesin Database untuk melakukan analisis indeks otomatis pada kueri. Untuk informasi selengkapnya, lihat Tutorial: Konsultan Penyetelan Mesin Database. Salah satu tujuan dari pengoptimalan ini adalah untuk membuat pencarian indeks mengembalikan baris sesedikempat mungkin untuk meminimalkan biaya Pencarian Bookmark (memaksimalkan selektivitas indeks untuk kueri tertentu). Jika Mesin Database memperkirakan bahwa operator logis Pencarian Bookmark dapat mengembalikan banyak baris, ia dapat menggunakan PREFETCH untuk melakukan pencarian bookmark. Jika Mesin Database memang menggunakan PREFETCH untuk pencarian marka buku, mesin tersebut harus meningkatkan tingkat isolasi transaksi dari sebagian kueri ke pembacaan yang dapat diulang untuk sebagian kueri. Ini berarti bahwa apa yang mungkin terlihat mirip dengan pernyataan SELECT pada tingkat isolasi yang diterapkan baca dapat memperoleh ribuan kunci kunci (pada indeks berkluster dan satu indeks non-kluster), yang dapat menyebabkan kueri tersebut melebihi ambang eskalasi kunci. Ini sangat penting jika Anda menemukan bahwa kunci yang ditingkatkan adalah kunci tabel bersama, yang, bagaimanapun, tidak biasanya terlihat pada tingkat isolasi berkomitmen baca default.

    Jika klausa Bookmark Lookup WITH PREFETCH menyebabkan eskalasi, pertimbangkan untuk menambahkan kolom tambahan ke indeks non-kluster yang muncul di Pencarian Indeks atau operator logis Pemindaian Indeks di bawah operator logis Pencarian Bookmark dalam rencana kueri. Mungkin untuk membuat indeks penutup (indeks yang menyertakan semua kolom dalam tabel yang digunakan dalam kueri), atau setidaknya indeks yang mencakup kolom yang digunakan untuk kriteria gabungan atau dalam klausa WHERE jika menyertakan semuanya dalam daftar kolom pilih tidak praktis. Gabungan Perulangan Berlapis juga dapat menggunakan PREFETCH, dan ini menyebabkan perilaku penguncian yang sama.

  • Eskalasi kunci tidak dapat terjadi jika SPID yang berbeda saat ini memegang kunci tabel yang tidak kompatibel. Eskalasi kunci selalu meningkat ke kunci tabel, dan tidak pernah ke kunci halaman. Selain itu, jika upaya eskalasi kunci gagal karena SPID lain memegang kunci TAB yang tidak kompatibel, kueri yang mencoba eskalasi tidak memblokir saat menunggu kunci TAB. Sebaliknya, ia terus memperoleh kunci pada tingkat aslinya yang lebih terperinci (baris, kunci, atau halaman), secara berkala melakukan upaya eskalasi tambahan. Oleh karena itu, salah satu metode untuk mencegah eskalasi kunci pada tabel tertentu adalah memperoleh dan menahan kunci pada koneksi lain yang tidak kompatibel dengan jenis kunci yang diluaskan. Kunci IX (eksklusif niat) di tingkat tabel tidak mengunci baris atau halaman apa pun, tetapi masih tidak kompatibel dengan kunci TAB S (bersama) atau X (eksklusif) yang dinaikkan. Misalnya, asumsikan bahwa Anda harus menjalankan pekerjaan batch yang memodifikasi sejumlah besar baris dalam tabel mytable dan itu telah menyebabkan pemblokiran yang terjadi karena eskalasi kunci. Jika pekerjaan ini selalu selesai dalam waktu kurang dari satu jam, Anda dapat membuat pekerjaan Transact-SQL yang berisi kode berikut, dan menjadwalkan pekerjaan baru untuk memulai beberapa menit sebelum waktu mulai pekerjaan batch:

    BEGIN TRAN
SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN

    Kueri ini memperoleh dan menahan kunci IX pada mytable selama satu jam, yang mencegah eskalasi kunci pada tabel selama waktu tersebut. Batch ini tidak mengubah data apa pun atau memblokir kueri lain (kecuali kueri lain memaksa kunci tabel dengan petunjuk TABLOCK atau jika administrator telah menonaktifkan kunci halaman atau baris dengan menggunakan prosedur tersimpan sp_indexoption ).

  • Anda juga dapat menggunakan bendera pelacakan 1211 dan 1224 untuk menonaktifkan semua atau beberapa eskalasi kunci. Namun, bendera pelacakan ini menonaktifkan semua eskalasi kunci secara global untuk seluruh Mesin Database. Eskalasi kunci melayani tujuan yang sangat berguna di Mesin Database dengan memaksimalkan efisiensi kueri yang diperlambat oleh overhead untuk memperoleh dan melepaskan beberapa ribu kunci. Eskalasi kunci juga membantu meminimalkan memori yang diperlukan untuk melacak kunci. Memori yang dapat dialokasikan secara dinamis oleh Mesin Database untuk struktur kunci terbatas, jadi jika Anda menonaktifkan eskalasi kunci dan memori kunci tumbuh cukup besar, upaya untuk mengalokasikan kunci tambahan untuk kueri apa pun mungkin gagal dan kesalahan berikut terjadi: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

    Catatan

    Ketika kesalahan MSSQLSERVER_1204 terjadi, ia menghentikan pemrosesan pernyataan saat ini dan menyebabkan pembatalan transaksi aktif. Pembatalan itu sendiri dapat memblokir pengguna atau menyebabkan waktu pemulihan database yang panjang jika Anda memulai ulang layanan database.

    Catatan

    Menggunakan petunjuk kunci seperti ROWLOCK hanya mengubah rencana kunci awal. Petunjuk kunci tidak mencegah eskalasi kunci.

Memantau eskalasi kunci

Pantau eskalasi kunci dengan menggunakan lock_escalation Extended Event (xEvent), seperti dalam contoh berikut:

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation(SET collect_database_name=(1),collect_statement=(1)
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.    username))
ADD TARGET package0.histogram(SET source=N'sqlserver.database_id')
GO

Penting

Extended lock_escalation Event (xEvent) harus digunakan alih-alih kelas peristiwa Lock:Escalation di SQL Trace atau SQL Profiler.

Penguncian dinamis

Menggunakan kunci tingkat rendah, seperti kunci baris, meningkatkan konkurensi dengan mengurangi probabilitas bahwa dua transaksi akan meminta kunci pada bagian data yang sama pada saat yang sama. Menggunakan kunci tingkat rendah juga meningkatkan jumlah kunci dan sumber daya yang diperlukan untuk mengelolanya. Menggunakan tabel tingkat tinggi atau kunci halaman menurunkan overhead, tetapi dengan mengorbankan konkurensi.

A graph of locking cost vs. concurrency cost.

Mesin Database SQL Server menggunakan strategi penguncian dinamis untuk menentukan kunci yang paling hemat biaya. Mesin Database SQL Server secara otomatis menentukan kunci apa yang paling tepat saat kueri dijalankan, berdasarkan karakteristik skema dan kueri. Misalnya, untuk mengurangi overhead penguncian, pengoptimal dapat memilih kunci tingkat halaman dalam indeks saat melakukan pemindaian indeks.

Penguncian dinamis memiliki keuntungan berikut:

  • Administrasi database yang disederhanakan. Administrator database tidak perlu menyesuaikan ambang eskalasi kunci.
  • Peningkatan performa. Mesin Database SQL Server meminimalkan overhead sistem dengan menggunakan kunci yang sesuai dengan tugas.
  • Pengembang aplikasi dapat berkonsentrasi pada pengembangan. Mesin Database SQL Server menyesuaikan penguncian secara otomatis.

Dimulai dengan SQL Server 2008 (10.0.x), perilaku eskalasi kunci telah berubah dengan pengenalan LOCK_ESCALATION opsi. Untuk informasi selengkapnya, lihat LOCK_ESCALATION opsi ALTER TABLE.

Mengunci partisi

Untuk sistem komputer besar, kunci pada objek yang sering direferensikan dapat menjadi hambatan performa karena memperoleh dan melepaskan kunci menempatkan ketidakcocokan pada sumber daya penguncian internal. Partisi kunci meningkatkan performa penguncian dengan membagi sumber daya kunci tunggal menjadi beberapa sumber daya kunci. Fitur ini hanya tersedia untuk sistem dengan 16 CPU atau lebih, dan diaktifkan secara otomatis dan tidak dapat dinonaktifkan. Hanya kunci objek yang dapat dipartisi. Kunci objek yang memiliki subjenis tidak dipartisi. Untuk informasi selengkapnya, lihat sys.dm_tran_locks (Transact-SQL).

Memahami partisi kunci

Mengunci tugas mengakses beberapa sumber daya bersama, dua di antaranya dioptimalkan dengan mengunci partisi:

  • Spinlock. Ini mengontrol akses ke sumber daya kunci, seperti baris atau tabel.

    Tanpa partisi kunci, satu spinlock mengelola semua permintaan kunci untuk satu sumber daya kunci. Pada sistem yang mengalami aktivitas dalam volume besar, pertikaian dapat terjadi karena permintaan kunci menunggu spinlock tersedia. Dalam situasi ini, memperoleh kunci dapat menjadi hambatan dan dapat berdampak negatif pada performa.

    Untuk mengurangi ketidakcocokan pada sumber daya kunci tunggal, partisi kunci membagi satu sumber daya kunci menjadi beberapa sumber daya kunci untuk mendistribusikan beban di beberapa spinlock.

  • Memori. Ini digunakan untuk menyimpan struktur sumber daya kunci.

    Setelah spinlock diperoleh, struktur kunci disimpan dalam memori dan kemudian diakses dan mungkin dimodifikasi. Mendistribusikan akses kunci di beberapa sumber daya membantu menghilangkan kebutuhan untuk mentransfer blok memori antara CPU, yang akan membantu meningkatkan performa.

Menerapkan dan memantau partisi kunci

Partisi kunci diaktifkan secara default untuk sistem dengan 16 CPU atau lebih. Saat partisi kunci diaktifkan, pesan informasi direkam di log kesalahan SQL Server.

Saat memperoleh kunci pada sumber daya yang dipartisi:

  • Hanya mode kunci NL, SCH-S, IS, IU, dan IX yang diperoleh pada satu partisi.

  • Kunci bersama (S), eksklusif (X), dan kunci lainnya dalam mode selain NL, SCH-S, IS, IU, dan IX harus diperoleh pada semua partisi yang dimulai dengan ID partisi 0 dan mengikuti urutan ID partisi. Kunci ini pada sumber daya yang dipartisi akan menggunakan lebih banyak memori daripada kunci dalam mode yang sama pada sumber daya yang tidak dipartisi karena setiap partisi secara efektif adalah kunci terpisah. Peningkatan memori ditentukan oleh jumlah partisi. Penghitung kunci SQL Server di Monitor Performa Windows akan menampilkan informasi tentang memori yang digunakan oleh kunci yang dipartisi dan tidak dipartisi.

Transaksi ditetapkan ke partisi saat transaksi dimulai. Untuk transaksi, semua permintaan kunci yang dapat dipartisi menggunakan partisi yang ditetapkan untuk transaksi tersebut. Dengan metode ini, akses ke sumber daya kunci objek yang sama oleh transaksi yang berbeda didistribusikan di berbagai partisi.

Kolom resource_lock_partition dalam sys.dm_tran_locks Tampilan Manajemen Dinamis menyediakan ID partisi kunci untuk sumber daya yang dipartisi kunci. Untuk informasi selengkapnya, lihat sys.dm_tran_locks (Transact-SQL).

Bekerja dengan partisi kunci

Contoh kode berikut mengilustrasikan partisi kunci. Dalam contoh, dua transaksi dijalankan dalam dua sesi berbeda untuk menunjukkan perilaku partisi kunci pada sistem komputer dengan 16 CPU.

Pernyataan Transact-SQL ini membuat objek pengujian yang digunakan dalam contoh berikut.

-- Create a test table.
CREATE TABLE TestTable  (col1 int);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable
    ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable VALUES (1);
GO

Contoh A

Sesi 1:

Pernyataan SELECT dijalankan di bawah transaksi. HOLDLOCK Karena petunjuk kunci, pernyataan ini akan memperoleh dan mempertahankan kunci Bersama Niat (IS) pada tabel (untuk ilustrasi ini, baris dan kunci halaman diabaikan). Kunci IS hanya akan diperoleh pada partisi yang ditetapkan untuk transaksi. Untuk contoh ini, diasumsikan bahwa kunci IS diperoleh pada ID partisi 7.

-- Start a transaction.
BEGIN TRANSACTION
    -- This SELECT statement will acquire an IS lock on the table.
    SELECT col1
    FROM TestTable
    WITH (HOLDLOCK);

Sesi 2:

Transaksi dimulai, dan pernyataan yang SELECT berjalan di bawah transaksi ini akan memperoleh dan mempertahankan kunci bersama (S) pada tabel. Kunci S akan diperoleh pada semua partisi, yang menghasilkan beberapa kunci tabel, satu untuk setiap partisi. Misalnya, pada sistem 16-cpu, kunci 16 S akan dikeluarkan di seluruh ID partisi kunci 0-15. Karena kunci S kompatibel dengan kunci IS yang ditahan pada ID partisi 7 oleh transaksi di sesi 1, tidak ada pemblokiran antar transaksi.

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCK, HOLDLOCK);

Sesi 1:

Pernyataan berikut SELECT dijalankan di bawah transaksi yang masih aktif di bawah sesi 1. Karena petunjuk kunci tabel eksklusif (X), transaksi akan mencoba memperoleh kunci X pada tabel. Namun, kunci S yang sedang ditahan oleh transaksi di sesi 2 akan memblokir kunci X pada ID partisi 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Contoh B

Sesi 1:

Pernyataan SELECT dijalankan di bawah transaksi. HOLDLOCK Karena petunjuk kunci, pernyataan ini akan memperoleh dan mempertahankan kunci Bersama Niat (IS) pada tabel (untuk ilustrasi ini, baris dan kunci halaman diabaikan). Kunci IS hanya akan diperoleh pada partisi yang ditetapkan untuk transaksi. Untuk contoh ini, diasumsikan bahwa kunci IS diperoleh pada ID partisi 6.

-- Start a transaction.
BEGIN TRANSACTION
    -- This SELECT statement will acquire an IS lock on the table.
    SELECT col1
    FROM TestTable
    WITH (HOLDLOCK);

Sesi 2:

Pernyataan SELECT dijalankan di bawah transaksi. TABLOCKX Karena petunjuk kunci, transaksi mencoba memperoleh kunci eksklusif (X) pada tabel. Ingatlah bahwa kunci X harus diperoleh pada semua partisi yang dimulai dengan ID partisi 0. Kunci X akan diperoleh pada semua ID partisi 0-5 tetapi akan diblokir oleh kunci IS yang diperoleh pada ID partisi 6.

Pada ID partisi 7-15 yang belum dicapai kunci X, transaksi lain dapat terus memperoleh kunci.

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCKX, HOLDLOCK);

Tingkat isolasi berbasis penerapan versi baris di Mesin Database SQL Server

Dimulai dengan SQL Server 2005 (9.x), Mesin Database SQL Server menawarkan implementasi tingkat isolasi transaksi yang ada, baca berkomitmen, yang menyediakan rekam jepret tingkat pernyataan menggunakan penerapan versi baris. Mesin Database SQL Server juga menawarkan tingkat isolasi transaksi, rekam jepret, yang menyediakan rekam jepret tingkat transaksi juga menggunakan penerapan versi baris.

Penerapan versi baris adalah kerangka kerja umum di SQL Server yang memanggil mekanisme salin saat baris dimodifikasi atau dihapus. Ini mengharuskan bahwa saat transaksi berjalan, versi lama baris harus tersedia untuk transaksi yang memerlukan status konsisten transaksi sebelumnya. Penerapan versi baris digunakan untuk melakukan hal berikut:

  • Buat tabel yang disisipkan dan dihapus dalam pemicu. Setiap baris yang dimodifikasi oleh pemicu diberi versi. Ini termasuk baris yang dimodifikasi oleh pernyataan yang meluncurkan pemicu, serta modifikasi data apa pun yang dibuat oleh pemicu.
  • Mendukung Beberapa Set Hasil Aktif (MARS). Jika sesi MARS mengeluarkan pernyataan modifikasi data (seperti INSERT, , UPDATEatau DELETE) pada saat ada tataan hasil aktif, baris yang terpengaruh oleh pernyataan modifikasi diversi versinya.
  • Mendukung operasi indeks yang menentukan opsi ONLINE.
  • Mendukung tingkat isolasi transaksi berbasis penerapan versi baris:
    • Implementasi baru tingkat isolasi READ COMMITTED yang menggunakan penerapan versi baris untuk memberikan konsistensi baca tingkat pernyataan.
    • Tingkat isolasi baru, rekam jepret, untuk memberikan konsistensi baca tingkat transaksi.

Database tempdb harus memiliki cukup ruang untuk penyimpanan versi. Ketika tempdb penuh, operasi pembaruan akan berhenti menghasilkan versi dan terus berhasil, tetapi operasi baca mungkin gagal karena versi baris tertentu yang tidak diperlukan lagi. Ini memengaruhi operasi seperti pemicu, MARS, dan pengindeksan online.

Menggunakan penerapan versi baris untuk transaksi berkomitmen baca dan rekam jepret adalah proses dua langkah:

  1. Atur READ_COMMITTED_SNAPSHOT opsi database dan ALLOW_SNAPSHOT_ISOLATION AKTIF.

  2. Atur tingkat isolasi transaksi yang sesuai dalam aplikasi:

    • READ_COMMITTED_SNAPSHOT Saat opsi database AKTIF, transaksi yang mengatur tingkat isolasi READ COMMITTED menggunakan penerapan versi baris.
    • ALLOW_SNAPSHOT_ISOLATION Saat opsi database AKTIF, transaksi dapat mengatur tingkat isolasi rekam jepret.

Saat opsi READ_COMMITTED_SNAPSHOT atau ALLOW_SNAPSHOT_ISOLATION database diatur AKTIF, Mesin Database SQL Server menetapkan nomor urutan transaksi (XSN) ke setiap transaksi yang memanipulasi data menggunakan penerapan versi baris. Transaksi dimulai pada saat BEGIN TRANSACTION pernyataan dijalankan. Namun, nomor urutan transaksi dimulai dengan operasi baca atau tulis pertama setelah pernyataan BEGIN TRANSACTION. Nomor urutan transaksi bertambah satu kali setiap kali ditetapkan.

READ_COMMITTED_SNAPSHOT Saat opsi atau ALLOW_SNAPSHOT_ISOLATION database AKTIF, salinan logis (versi) dipertahankan untuk semua modifikasi data yang dilakukan dalam database. Setiap kali baris dimodifikasi oleh transaksi tertentu, instans Mesin Database SQL Server menyimpan versi gambar baris yang diterapkan sebelumnya di tempdb. Setiap versi ditandai dengan nomor urutan transaksi transaksi yang melakukan perubahan. Versi baris yang dimodifikasi ditautkan menggunakan daftar tautan. Nilai baris terbaru selalu disimpan dalam database saat ini dan ditautkan ke baris versi yang disimpan di tempdb.

Catatan

Untuk modifikasi objek besar (LOB), hanya fragmen yang diubah yang disalin ke penyimpanan versi di tempdb.

Versi baris ditahan cukup lama untuk memenuhi persyaratan transaksi yang berjalan di bawah tingkat isolasi berbasis penerapan versi baris. Mesin Database SQL Server melacak nomor urutan transaksi paling awal yang berguna dan secara berkala menghapus semua versi baris yang dicap dengan nomor urutan transaksi yang lebih rendah dari nomor urutan paling awal yang berguna.

Saat kedua opsi database diatur ke NONAKTIF, hanya baris yang dimodifikasi oleh pemicu atau sesi MARS, atau dibaca oleh operasi indeks ONLINE, yang diberi versi. Versi baris tersebut dirilis saat tidak lagi diperlukan. Utas latar belakang secara berkala dijalankan untuk menghapus versi baris kedaluarsa.

Catatan

Untuk transaksi yang berjalan singkat, versi baris yang dimodifikasi mungkin di-cache di kumpulan buffer tanpa ditulis ke dalam file tempdb disk database. Jika kebutuhan untuk baris versi berumur pendek, itu hanya akan dihilangkan dari kumpulan buffer dan mungkin belum tentu dikenakan overhead I/O.

Perilaku saat membaca data

Saat transaksi yang berjalan di bawah data baca isolasi berbasis penerapan versi baris, operasi baca tidak memperoleh kunci bersama (S) pada data yang sedang dibaca, dan oleh karena itu jangan memblokir transaksi yang memodifikasi data. Selain itu, overhead sumber daya penguncian diminimalkan karena jumlah kunci yang diperoleh berkurang. Isolasi berkomitmen baca menggunakan penerapan versi baris dan isolasi rekam jepret dirancang untuk memberikan konsistensi baca tingkat pernyataan atau tingkat transaksi dari data versi.

Semua kueri, termasuk transaksi yang berjalan di bawah tingkat isolasi berbasis penerapan versi baris, memperoleh kunci Sch-S (stabilitas skema) selama kompilasi dan eksekusi. Karena itu, kueri diblokir ketika transaksi bersamaan memegang kunci Sch-M (modifikasi skema) pada tabel. Misalnya, operasi bahasa definisi data (DDL) memperoleh kunci Sch-M sebelum memodifikasi informasi skema tabel. Transaksi kueri, termasuk yang berjalan di bawah tingkat isolasi berbasis penerapan versi baris, diblokir saat mencoba memperoleh kunci Sch-S. Sebaliknya, kueri yang memegang kunci Sch-S memblokir transaksi bersamaan yang mencoba memperoleh kunci Sch-M.

Saat transaksi menggunakan tingkat isolasi rekam jepret dimulai, instans Mesin Database SQL Server merekam semua transaksi yang saat ini aktif. Ketika transaksi rekam jepret membaca baris yang memiliki rantai versi, Mesin Database SQL Server mengikuti rantai dan mengambil baris di mana nomor urutan transaksi adalah:

  • Terdekat tetapi lebih rendah dari jumlah urutan transaksi rekam jepret yang membaca baris.

  • Tidak dalam daftar transaksi aktif saat transaksi rekam jepret dimulai.

Operasi baca yang dilakukan oleh transaksi rekam jepret mengambil versi terakhir dari setiap baris yang telah dilakukan pada saat transaksi rekam jepret dimulai. Ini menyediakan rekam jepret data yang konsisten secara transaksional seperti yang ada di awal transaksi.

Transaksi berkomitmen baca menggunakan penerapan versi baris beroperasi dengan cara yang sama. Perbedaannya adalah bahwa transaksi berkomitmen baca tidak menggunakan nomor urutan transaksinya sendiri saat memilih versi baris. Setiap kali pernyataan dimulai, transaksi berkomitmen baca membaca nomor urutan transaksi terbaru yang dikeluarkan untuk instans Mesin Database SQL Server tersebut. Ini adalah nomor urutan transaksi yang digunakan untuk memilih versi baris yang benar untuk pernyataan tersebut. Ini memungkinkan transaksi berkomitmen baca untuk melihat rekam jepret data seperti yang ada di awal setiap pernyataan.

Catatan

Meskipun transaksi berkomitmen baca menggunakan penerapan versi baris memberikan tampilan data yang konsisten secara transaksional pada tingkat pernyataan, versi baris yang dihasilkan atau diakses oleh jenis transaksi ini dipertahankan sampai transaksi selesai.

Perilaku saat memodifikasi data

Perilaku penulisan data secara signifikan berbeda dengan dan tanpa penguncian yang dioptimalkan.

Mengubah data tanpa penguncian yang dioptimalkan

Dalam transaksi berkomitmen baca menggunakan penerapan versi baris, pemilihan baris yang akan diperbarui dilakukan menggunakan pemindaian pemblokiran di mana kunci pembaruan (U) diperoleh pada baris data saat nilai data dibaca. Ini sama dengan transaksi berkomitmen baca yang tidak menggunakan penerapan versi baris. Jika baris data tidak memenuhi kriteria pembaruan, kunci pembaruan dirilis pada baris tersebut dan baris berikutnya dikunci dan dipindai.

Transaksi yang berjalan di bawah isolasi rekam jepret mengambil pendekatan optimis untuk modifikasi data dengan memperoleh kunci pada data sebelum melakukan modifikasi hanya untuk memberlakukan batasan. Jika tidak, kunci tidak diperoleh pada data hingga data akan dimodifikasi. Saat baris data memenuhi kriteria pembaruan, transaksi rekam jepret memverifikasi bahwa baris data belum dimodifikasi oleh transaksi bersamaan yang dilakukan setelah transaksi rekam jepret dimulai. Jika baris data telah dimodifikasi di luar transaksi rekam jepret, konflik pembaruan terjadi dan transaksi rekam jepret dihentikan. Konflik pembaruan ditangani oleh Mesin Database SQL Server dan tidak ada cara untuk menonaktifkan deteksi konflik pembaruan.

Catatan

Perbarui operasi yang berjalan di bawah isolasi rekam jepret yang dijalankan secara internal di bawah isolasi READ COMMITTED saat transaksi rekam jepret mengakses salah satu hal berikut:

Tabel dengan batasan KUNCI ASING.

Tabel yang dirujuk dalam batasan KUNCI ASING dari tabel lain.

Tampilan terindeks yang mereferensikan lebih dari satu tabel.

Namun, bahkan dalam kondisi ini operasi pembaruan akan terus memverifikasi bahwa data belum dimodifikasi oleh transaksi lain. Jika data telah dimodifikasi oleh transaksi lain, transaksi rekam jepret mengalami konflik pembaruan dan dihentikan. Konflik pembaruan harus ditangani dan dicoba kembali secara manual oleh aplikasi.

Mengubah data dengan penguncian yang dioptimalkan

Dengan penguncian yang dioptimalkan diaktifkan dan dengan opsi database READ_COMMITTED_SNAPSHOT (RCSI) diaktifkan, dan menggunakan tingkat isolasi READ COMMITTED default, pembaca tidak memperoleh kunci apa pun, dan penulis memperoleh kunci tingkat rendah durasi pendek, alih-alih kunci yang kedaluwarsa di akhir transaksi.

Mengaktifkan RCSI direkomendasikan untuk sebagian besar efisiensi dengan penguncian yang dioptimalkan. Saat menggunakan tingkat isolasi yang lebih ketat seperti bacaan yang dapat diulang atau dapat diserialisasikan, Mesin Database dipaksa untuk menahan kunci baris dan halaman hingga akhir transaksi, untuk pembaca dan penulis, menghasilkan peningkatan pemblokiran dan kunci memori.

Dengan RCSI diaktifkan, dan saat menggunakan tingkat isolasi READ COMMITTED default, penulis memenuhi syarat baris per predikat berdasarkan versi baris terbaru yang diterapkan, tanpa memperoleh kunci U. Kueri hanya akan menunggu jika baris memenuhi syarat dan ada transaksi tulis aktif pada baris atau halaman tersebut. Memenuhi syarat berdasarkan versi berkomitmen terbaru dan hanya mengunci baris yang memenuhi syarat yang mengurangi pemblokiran dan meningkatkan konkurensi.

Jika konflik pembaruan terdeteksi dengan RCSI dan dalam tingkat isolasi READ COMMITTED default, konflik tersebut ditangani dan dicoba kembali secara otomatis tanpa berdampak pada beban kerja pelanggan.

Dengan penguncian yang dioptimalkan diaktifkan, menggunakan tingkat isolasi SNAPSHOT, perilaku konflik pembaruan sama. Konflik pembaruan harus ditangani dan dicoba kembali secara manual oleh aplikasi.

Catatan

Untuk informasi selengkapnya tentang perubahan perilaku dengan fitur kunci setelah kualifikasi (LAQ) penguncian yang dioptimalkan, lihat Perubahan perilaku kueri dengan penguncian yang dioptimalkan dan RCSI.

Perilaku dalam ringkasan

Tabel berikut ini meringkas perbedaan antara isolasi rekam jepret dan isolasi READ COMMITTED menggunakan penerapan versi baris.

Properti Tingkat isolasi yang diterapkan baca menggunakan penerapan versi baris Tingkat isolasi rekam jepret
Opsi database yang harus diatur ke AKTIF untuk mengaktifkan dukungan yang diperlukan. READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION
Bagaimana sesi meminta jenis penerapan versi baris tertentu. Gunakan tingkat isolasi berkomitmen baca default, atau jalankan pernyataan SET TRANSACTION ISOLATION LEVEL untuk menentukan tingkat isolasi READ COMMITTED. Ini dapat dilakukan setelah transaksi dimulai. Memerlukan eksekusi SET TRANSACTION ISOLATION LEVEL untuk menentukan tingkat isolasi SNAPSHOT sebelum awal transaksi.
Versi data yang dibaca oleh pernyataan. Semua data yang diterapkan sebelum dimulainya setiap pernyataan. Semua data yang diterapkan sebelum dimulainya setiap transaksi.
Bagaimana pembaruan ditangani. Tanpa penguncian yang dioptimalkan: Mengembalikan dari versi baris ke data aktual untuk memilih baris untuk diperbarui dan menggunakan kunci pembaruan pada baris data yang dipilih. Memperoleh kunci eksklusif pada baris data aktual yang akan dimodifikasi. Tidak ada deteksi konflik pembaruan.

Dengan penguncian yang dioptimalkan: Baris dipilih berdasarkan versi terakhir yang diterapkan tanpa kunci apa pun yang diperoleh. Jika baris memenuhi syarat untuk pembaruan, baris eksklusif atau kunci halaman diperoleh. Jika konflik pembaruan terdeteksi, konflik tersebut ditangani dan dicoba kembali secara otomatis.		 Menggunakan versi baris untuk memilih baris yang akan diperbarui. Mencoba memperoleh kunci eksklusif pada baris data aktual yang akan dimodifikasi, dan jika data telah dimodifikasi oleh transaksi lain, konflik pembaruan terjadi dan transaksi rekam jepret dihentikan.
Memperbarui deteksi konflik Tanpa penguncian yang dioptimalkan: Tidak ada.

Dengan penguncian yang dioptimalkan: Jika konflik pembaruan terdeteksi, konflik tersebut ditangani dan dicoba kembali secara otomatis.		 Dukungan terintegrasi. Tidak dapat dinonaktifkan.

Penggunaan sumber daya penerapan versi baris

Kerangka kerja penerapan versi baris mendukung fitur berikut yang tersedia di SQL Server:

  • Pemicu
  • Beberapa Set Hasil Aktif (MARS)
  • Pengindeksan online

Kerangka kerja penerapan versi baris juga mendukung tingkat isolasi transaksi berbasis penerapan versi baris berikut, yang tidak diaktifkan secara default:

  • READ_COMMITTED_SNAPSHOT Saat opsi database AKTIF, READ_COMMITTED transaksi menyediakan konsistensi baca tingkat pernyataan menggunakan penerapan versi baris.
  • ALLOW_SNAPSHOT_ISOLATION Saat opsi database AKTIF, SNAPSHOT transaksi menyediakan konsistensi baca tingkat transaksi menggunakan penerapan versi baris.

Tingkat isolasi berbasis penerapan versi baris mengurangi jumlah kunci yang diperoleh oleh transaksi dengan menghilangkan penggunaan kunci bersama pada operasi baca. Ini meningkatkan performa sistem dengan mengurangi sumber daya yang digunakan untuk mengelola kunci. Performa juga ditingkatkan dengan mengurangi berapa kali transaksi diblokir oleh kunci yang diperoleh oleh transaksi lain.

Tingkat isolasi berbasis penerapan versi baris meningkatkan sumber daya yang diperlukan oleh modifikasi data. Mengaktifkan opsi ini menyebabkan semua modifikasi data untuk database diberi versi. Salinan data sebelum modifikasi disimpan tempdb bahkan ketika tidak ada transaksi aktif menggunakan isolasi berbasis penerapan versi baris. Data setelah modifikasi menyertakan penunjuk ke data versi yang disimpan di tempdb. Untuk objek besar, hanya bagian dari objek yang berubah yang disalin ke tempdb.

Ruang yang digunakan dalam tempdb

Untuk setiap instans Mesin Database SQL Server, tempdb harus memiliki ruang yang cukup untuk menyimpan versi baris yang dihasilkan untuk setiap database dalam instans. Administrator database harus memastikan bahwa tempdb memiliki cukup ruang untuk mendukung penyimpanan versi. Ada dua penyimpanan versi di tempdb:

  • Penyimpanan versi build indeks online digunakan untuk build indeks online di semua database.
  • Penyimpanan versi umum digunakan untuk semua operasi modifikasi data lainnya di semua database.

Versi baris harus disimpan selama transaksi aktif perlu mengaksesnya. Setelah setiap menit, utas latar belakang menghapus versi baris yang tidak lagi diperlukan dan membebaskan ruang versi di tempdb. Transaksi jangka panjang mencegah ruang di penyimpanan versi dirilis jika memenuhi salah satu kondisi berikut:

  • Ini menggunakan isolasi berbasis penerapan versi baris.
  • Ini menggunakan pemicu, MARS, atau operasi build indeks online.
  • Ini menghasilkan versi baris.

Catatan

Saat pemicu dipanggil di dalam transaksi, versi baris yang dibuat oleh pemicu dipertahankan hingga akhir transaksi, meskipun versi baris tidak lagi diperlukan setelah pemicu selesai. Ini juga berlaku untuk transaksi berkomitmen baca yang menggunakan penerapan versi baris. Dengan jenis transaksi ini, tampilan database yang konsisten secara transaksional hanya diperlukan untuk setiap pernyataan dalam transaksi. Ini berarti bahwa versi baris yang dibuat untuk pernyataan dalam transaksi tidak lagi diperlukan setelah pernyataan selesai. Namun, versi baris yang dibuat oleh setiap pernyataan dalam transaksi dipertahankan hingga transaksi selesai.

Saat tempdb kehabisan ruang, Mesin Database SQL Server memaksa penyimpanan versi menyusut. Selama proses penyusutan, transaksi terlama yang belum menghasilkan versi baris ditandai sebagai korban. Pesan 3967 dihasilkan dalam log kesalahan untuk setiap transaksi korban. Jika transaksi ditandai sebagai korban, transaksi tidak dapat lagi membaca versi baris di penyimpanan versi. Ketika mencoba membaca versi baris, pesan 3966 dihasilkan dan transaksi digulung balik. Jika proses penyusutan berhasil, ruang menjadi tersedia di tempdb. Jika tidak, tempdb kehabisan ruang dan hal berikut ini terjadi:

  • Operasi tulis terus dijalankan tetapi tidak menghasilkan versi. Pesan informasi (3959) muncul di log kesalahan, tetapi transaksi yang menulis data tidak terpengaruh.

  • Transaksi yang mencoba mengakses versi baris yang tidak dihasilkan karena pemutaran tempdb kembali penuh berakhir dengan kesalahan 3958.

Ruang yang digunakan dalam baris data

Setiap baris database dapat menggunakan hingga 14 byte di akhir baris untuk informasi penerapan versi baris. Informasi penerapan versi baris berisi nomor urutan transaksi dari transaksi yang menerapkan versi dan penunjuk ke baris versi. 14 byte ini ditambahkan saat pertama kali baris dimodifikasi, atau ketika baris baru disisipkan, dalam salah satu kondisi ini:

  • READ_COMMITTED_SNAPSHOT atau ALLOW_SNAPSHOT_ISOLATION opsinya adalah AKTIF.
  • Tabel memiliki pemicu.
  • Beberapa Set Hasil Aktif (MARS) sedang digunakan.
  • Operasi build indeks online saat ini berjalan pada tabel.

14 byte ini dihapus dari baris database saat pertama kali baris dimodifikasi dalam semua kondisi ini:

  • READ_COMMITTED_SNAPSHOT dan ALLOW_SNAPSHOT_ISOLATION opsi nonaktif.
  • Pemicu tidak lagi ada di tabel.
  • MARS tidak digunakan.
  • Operasi build indeks online saat ini tidak berjalan.

Jika Anda menggunakan salah satu fitur penerapan versi baris, Anda mungkin perlu mengalokasikan ruang disk tambahan untuk database guna mengakomodasi 14 byte per baris database. Menambahkan informasi penerapan versi baris dapat menyebabkan pemisahan halaman indeks atau alokasi halaman data baru jika tidak ada cukup ruang yang tersedia di halaman saat ini. Misalnya, jika panjang baris rata-rata adalah 100 byte, 14 byte tambahan menyebabkan tabel yang ada tumbuh hingga 14 persen.

Mengurangi faktor pengisian mungkin membantu mencegah atau mengurangi fragmentasi halaman indeks. Untuk melihat informasi fragmentasi untuk data dan indeks tabel atau tampilan, Anda bisa menggunakan sys.dm_db_index_physical_stats.

Ruang yang digunakan dalam objek besar

Mesin Database SQL Server mendukung enam jenis data yang dapat menyimpan string besar hingga 2 gigabyte (GB) panjangnya: nvarchar(max), , varchar(max), varbinary(max)ntext, , textdan image. String besar yang disimpan menggunakan jenis data ini disimpan dalam serangkaian fragmen data yang ditautkan ke baris data. Informasi penerapan versi baris disimpan di setiap fragmen yang digunakan untuk menyimpan string besar ini. Fragmen data adalah kumpulan halaman yang didedikasikan untuk objek besar dalam tabel.

Karena nilai besar baru ditambahkan ke database, nilai tersebut dialokasikan menggunakan maksimum 8040 byte data per fragmen. Versi Mesin Database SQL Server yang lebih lama disimpan hingga 8080 byte ntext, text, atau image data per fragmen.

ntextData objek , text, dan image besar (LOB) yang ada tidak diperbarui untuk membuat ruang untuk informasi penerapan versi baris saat database ditingkatkan ke SQL Server dari versi SQL Server yang lebih lama. Namun, pertama kali data LOB dimodifikasi, data LOB ditingkatkan secara dinamis untuk mengaktifkan penyimpanan informasi penerapan versi. Ini akan terjadi bahkan jika versi baris tidak dihasilkan. Setelah data LOB ditingkatkan, jumlah maksimum byte yang disimpan per fragmen berkurang dari 8080 byte menjadi 8040 byte. Proses peningkatan setara dengan menghapus nilai LOB dan memasukkan kembali nilai yang sama. Data LOB ditingkatkan bahkan jika hanya 1 byte yang dimodifikasi. Ini adalah operasi satu kali untuk setiap ntextkolom , , textatau image , tetapi setiap operasi dapat menghasilkan sejumlah besar alokasi halaman dan aktivitas I/O tergantung pada ukuran data LOB. Ini juga dapat menghasilkan sejumlah besar aktivitas pengelogan jika modifikasi sepenuhnya dicatat. Operasi WRITETEXT dan UPDATETEXT dicatat secara minimal jika model pemulihan database tidak diatur ke PENUH.

Jenis nvarchar(max)data , varchar(max), dan varbinary(max) tidak tersedia di versi SQL Server yang lebih lama. Oleh karena itu, mereka tidak memiliki masalah peningkatan.

Ruang disk yang cukup harus dialokasikan untuk mengakomodasi persyaratan ini.

Memantau penerapan versi baris dan penyimpanan versi

Untuk memantau proses penerapan versi baris, penyimpanan versi, dan isolasi rekam jepret untuk performa dan masalah, SQL Server menyediakan alat dalam bentuk Tampilan Manajemen Dinamis (DMV) dan penghitung kinerja di Windows System Monitor.

DMV

DMV berikut memberikan informasi tentang status tempdb sistem saat ini dan penyimpanan versi, serta transaksi menggunakan penerapan versi baris.

  • sys.dm_db_file_space_usage. Mengembalikan informasi penggunaan ruang untuk setiap file dalam database. Untuk informasi selengkapnya, lihat sys.dm_db_file_space_usage (Transact-SQL).

  • sys.dm_db_session_space_usage. Mengembalikan alokasi halaman dan aktivitas alokasi berdasarkan sesi untuk database. Untuk informasi selengkapnya, lihat sys.dm_db_session_space_usage (Transact-SQL).

  • sys.dm_db_task_space_usage. Mengembalikan alokasi halaman dan aktivitas alokasi berdasarkan tugas untuk database. Untuk informasi selengkapnya, lihat sys.dm_db_task_space_usage (Transact-SQL).

  • sys.dm_tran_top_version_generators. Mengembalikan tabel virtual untuk objek yang menghasilkan versi terbanyak di penyimpanan versi. Ini mengelompokkan 256 panjang rekaman agregat teratas berdasarkan database_id dan rowset_id. Gunakan fungsi ini untuk menemukan konsumen terbesar dari penyimpanan versi. Untuk informasi selengkapnya, lihat sys.dm_tran_top_version_generators (Transact-SQL).

  • sys.dm_tran_version_store. Mengembalikan tabel virtual yang menampilkan semua rekaman versi di penyimpanan versi umum. Untuk informasi selengkapnya, lihat sys.dm_tran_version_store (Transact-SQL).

  • sys.dm_tran_version_store_space_usage. Mengembalikan tabel virtual yang menampilkan total ruang dalam tempdb yang digunakan oleh rekaman penyimpanan versi untuk setiap database. Untuk informasi selengkapnya, lihat sys.dm_tran_version_store_space_usage (Transact-SQL).

    Catatan

    Objek sys.dm_tran_top_version_generators sistem dan sys.dm_tran_version_store berpotensi sangat mahal untuk dijalankan, karena keduanya mengkueri seluruh penyimpanan versi, yang bisa sangat besar. Meskipun sys.dm_tran_version_store_space_usage efisien dan tidak mahal untuk dijalankan karena tidak menavigasi melalui rekaman penyimpanan versi individual, dan sebaliknya mengembalikan ruang penyimpanan versi agregat yang digunakan dalam tempdb per database.

  • sys.dm_tran_active_snapshot_database_transactions. Mengembalikan tabel virtual untuk semua transaksi aktif di semua database dalam instans SQL Server yang menggunakan penerapan versi baris. Transaksi sistem tidak muncul di DMV ini. Untuk informasi selengkapnya, lihat sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

  • sys.dm_tran_transactions_snapshot. Mengembalikan tabel virtual yang menampilkan rekam jepret yang diambil oleh setiap transaksi. Rekam jepret berisi nomor urut transaksi aktif yang menggunakan penerapan versi baris. Untuk informasi selengkapnya, lihat sys.dm_tran_transactions_snapshot (Transact-SQL).

  • sys.dm_tran_current_transaction. Mengembalikan satu baris yang menampilkan informasi status terkait penerapan versi baris dari transaksi dalam sesi saat ini. Untuk informasi selengkapnya, lihat sys.dm_tran_current_transaction (Transact-SQL).

  • sys.dm_tran_current_snapshot. Mengembalikan tabel virtual yang menampilkan semua transaksi aktif pada saat transaksi isolasi rekam jepret saat ini dimulai. Jika transaksi saat ini menggunakan isolasi rekam jepret, fungsi ini tidak mengembalikan baris. DMV sys.dm_tran_current_snapshot mirip sys.dm_tran_transactions_snapshotdengan , kecuali hanya mengembalikan transaksi aktif untuk rekam jepret saat ini. Untuk informasi selengkapnya, lihat sys.dm_tran_current_snapshot (Transact-SQL).

Penghitung kinerja

Penghitung kinerja SQL Server memberikan informasi tentang performa sistem yang terpengaruh oleh proses SQL Server. Penghitung kinerja berikut memantau tempdb dan penyimpanan versi, serta transaksi menggunakan penerapan versi baris. Penghitung kinerja terkandung dalam objek performa SQLServer:Transactions .

  • Ruang Kosong dalam tempdb (KB). Memantau jumlah, dalam kilobyte (KB), ruang kosong dalam tempdb database. Harus ada cukup ruang kosong untuk tempdb menangani penyimpanan versi yang mendukung isolasi rekam jepret.

    Rumus berikut memberikan perkiraan kasar tentang ukuran penyimpanan versi. Untuk transaksi jangka panjang, mungkin berguna untuk memantau pembuatan dan tingkat pembersihan untuk memperkirakan ukuran maksimum penyimpanan versi.

    [ukuran penyimpanan versi umum] = 2 * [data penyimpanan versi yang dihasilkan per menit] * [waktu berjalan terpanjang (menit) transaksi]

    Waktu transaksi terlama tidak boleh menyertakan build indeks online. Karena operasi ini mungkin memakan waktu lama pada tabel yang sangat besar, build indeks online menggunakan penyimpanan versi terpisah. Perkiraan ukuran penyimpanan versi build indeks online sama dengan jumlah data yang dimodifikasi dalam tabel, termasuk semua indeks, sementara build indeks online aktif.

  • Ukuran Penyimpanan Versi (KB). Memantau ukuran dalam KB semua penyimpanan versi. Informasi ini membantu menentukan jumlah ruang yang tempdb diperlukan dalam database untuk penyimpanan versi. Memantau penghitung ini selama jangka waktu tertentu memberikan perkiraan yang berguna tentang ruang tambahan yang diperlukan untuk tempdb.

  • Laju Pembuatan Versi (KB/dtk). Memantau tingkat pembuatan versi dalam KB per detik di semua penyimpanan versi.

  • Laju Pembersihan Versi (KB/dtk). Memantau tingkat pembersihan versi dalam KB per detik di semua penyimpanan versi.

    Catatan

    Informasi dari tingkat Pembuatan Versi (KB/dtk) dan laju Pembersihan Versi (KB/dtk) dapat digunakan untuk memprediksi tempdb persyaratan ruang.

  • Jumlah unit Penyimpanan Versi. Memantau jumlah unit penyimpanan versi.

  • Pembuatan unit Penyimpanan Versi. Memantau jumlah total unit penyimpanan versi yang dibuat untuk menyimpan versi baris sejak instans dimulai.

  • Pemotongan unit Penyimpanan Versi. Memantau jumlah total unit penyimpanan versi yang dipotong sejak instans dimulai. Unit penyimpanan versi dipotong ketika SQL Server menentukan bahwa tidak ada baris versi yang disimpan di unit penyimpanan versi yang diperlukan untuk menjalankan transaksi aktif.

  • Perbarui rasio konflik. Memantau rasio transaksi rekam jepret pembaruan yang memiliki konflik pembaruan dengan jumlah total transaksi rekam jepret pembaruan.

  • Durasi Transaksi Terpanjang. Memantau waktu berjalan terlama dalam detik dari setiap transaksi menggunakan penerapan versi baris. Ini dapat digunakan untuk menentukan apakah ada transaksi yang berjalan untuk jumlah waktu yang tidak masuk akal.

  • Transaksi. Memantau jumlah total transaksi aktif. Ini tidak termasuk transaksi sistem.

  • Transaksi Rekam Jepret. Memantau jumlah total transaksi rekam jepret aktif.

  • Perbarui Transaksi Rekam Jepret. Memantau jumlah total transaksi rekam jepret aktif yang melakukan operasi pembaruan.

  • Transaksi Versi NonSnapshot. Memantau jumlah total transaksi non-rekam jepret aktif yang menghasilkan rekaman versi.

    Catatan

    Jumlah Transaksi Rekam Jepret Pembaruan dan Transaksi Versi NonSnapshot mewakili jumlah total transaksi yang berpartisipasi dalam pembuatan versi. Perbedaan Transaksi Rekam Jepret dan Perbarui Transaksi Rekam Jepret melaporkan jumlah transaksi rekam jepret baca-saja.

Contoh tingkat isolasi berbasis penerapan versi baris

Contoh berikut menunjukkan perbedaan perilaku antara transaksi isolasi rekam jepret dan transaksi berkomitmen baca yang menggunakan penerapan versi baris.

J. Bekerja dengan isolasi rekam jepret

Dalam contoh ini, transaksi yang berjalan di bawah isolasi rekam jepret membaca data yang kemudian dimodifikasi oleh transaksi lain. Transaksi rekam jepret tidak memblokir operasi pembaruan yang dijalankan oleh transaksi lain, dan terus membaca data dari baris versi, mengabaikan modifikasi data. Namun, ketika transaksi rekam jepret mencoba memodifikasi data yang telah dimodifikasi oleh transaksi lain, transaksi rekam jepret menghasilkan kesalahan dan dihentikan.

Pada sesi 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;
    -- This SELECT statement will return
    -- 48 vacation hours for the employee.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Pada sesi 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;
    -- Subtract a vacation day from employee 4.
    -- Update is not blocked by session 1 since
    -- under snapshot isolation shared locks are
    -- not requested.
    UPDATE HumanResources.Employee
        SET VacationHours = VacationHours - 8
        WHERE BusinessEntityID = 4;

    -- Verify that the employee now has 40 vacation hours.
    SELECT VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Pada sesi 1:

    -- Reissue the SELECT statement - this shows
    -- the employee having 48 vacation hours. The
    -- snapshot transaction is still reading data from
    -- the versioned row.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Pada sesi 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

Pada sesi 1:

    -- Reissue the SELECT statement - this still
    -- shows the employee having 48 vacation hours
    -- even after the other transaction has committed
    -- the data modification.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

    -- Because the data has been modified outside of the
    -- snapshot transaction, any further data changes to
    -- that data by the snapshot transaction will cause
    -- the snapshot transaction to fail. This statement
    -- will generate a 3960 error and the transaction will
    -- terminate.
    UPDATE HumanResources.Employee
        SET SickLeaveHours = SickLeaveHours - 8
        WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION
GO

B. Bekerja dengan penerapan baca menggunakan penerapan versi baris

Dalam contoh ini, transaksi berkomitmen baca menggunakan penerapan versi baris berjalan bersamaan dengan transaksi lain. Transaksi berkomitmen baca bersifat berbeda dari transaksi rekam jepret. Seperti transaksi rekam jepret, transaksi berkomitmen baca akan membaca baris berversi bahkan setelah transaksi lain memodifikasi data. Namun, tidak seperti transaksi rekam jepret, transaksi yang dilakukan baca akan:

  • Baca data yang dimodifikasi setelah transaksi lain melakukan perubahan data.
  • Dapat memperbarui data yang dimodifikasi oleh transaksi lain di mana transaksi rekam jepret tidak dapat.

Pada sesi 1:

USE AdventureWorks2022;  -- Or any earlier version of the AdventureWorks database.
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;
    -- This SELECT statement will return
    -- 48 vacation hours for the employee.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Pada sesi 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;
    -- Subtract a vacation day from employee 4.
    -- Update is not blocked by session 1 since
    -- under read-committed using row versioning shared locks are
    -- not requested.
    UPDATE HumanResources.Employee
        SET VacationHours = VacationHours - 8
        WHERE BusinessEntityID = 4;

    -- Verify that the employee now has 40 vacation hours.
    SELECT VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Pada sesi 1:

    -- Reissue the SELECT statement - this still shows
    -- the employee having 48 vacation hours. The
    -- read-committed transaction is still reading data
    -- from the versioned row and the other transaction
    -- has not committed the data changes yet.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

Pada sesi 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

Pada sesi 1:

    -- Reissue the SELECT statement which now shows the
    -- employee having 40 vacation hours. Being
    -- read-committed, this transaction is reading the
    -- committed data. This is different from snapshot
    -- isolation which reads from the versioned row.
    SELECT BusinessEntityID, VacationHours
        FROM HumanResources.Employee
        WHERE BusinessEntityID = 4;

    -- This statement, which caused the snapshot transaction
    -- to fail, will succeed with read-committed using row versioning.
    UPDATE HumanResources.Employee
        SET SickLeaveHours = SickLeaveHours - 8
        WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Mengaktifkan tingkat isolasi berbasis penerapan versi baris

Administrator database mengontrol pengaturan tingkat database untuk penerapan versi baris dengan menggunakan READ_COMMITTED_SNAPSHOT opsi database dan ALLOW_SNAPSHOT_ISOLATION dalam pernyataan ALTER DATABASE.

READ_COMMITTED_SNAPSHOT Ketika opsi database diatur AKTIF, mekanisme yang digunakan untuk mendukung opsi segera diaktifkan. Saat mengatur opsi READ_COMMITTED_SNAPSHOT, hanya koneksi yang menjalankan perintah yang ALTER DATABASE diizinkan dalam database. Tidak boleh ada koneksi terbuka lain dalam database sampai ALTER DATABASE selesai. Database tidak harus dalam mode pengguna tunggal.

Pernyataan Transact-SQL berikut memungkinkan READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

ALLOW_SNAPSHOT_ISOLATION Saat opsi database diatur AKTIF, instans Mesin Database SQL Server tidak menghasilkan versi baris untuk data yang dimodifikasi hingga semua transaksi aktif yang telah memodifikasi data dalam database selesai. Jika ada transaksi modifikasi aktif, SQL Server mengatur status opsi ke PENDING_ON. Setelah semua transaksi modifikasi selesai, status opsi diubah menjadi AKTIF. Pengguna tidak dapat memulai transaksi rekam jepret dalam database tersebut hingga opsi aktif sepenuhnya. Database melewati status PENDING_OFF saat administrator database mengatur ALLOW_SNAPSHOT_ISOLATION opsi ke NONAKTIF.

Pernyataan Transact-SQL berikut akan mengaktifkan ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

Tabel berikut ini mencantumkan dan menjelaskan status opsi ALLOW_SNAPSHOT_ISOLATION. Menggunakan ALTER DATABASE dengan opsi ALLOW_SNAPSHOT_ISOLATION tidak memblokir pengguna yang saat ini mengakses data database.

Status kerangka kerja isolasi rekam jepret untuk database saat ini Deskripsi
TIDAK AKTIF Dukungan untuk transaksi isolasi rekam jepret tidak diaktifkan. Tidak ada transaksi isolasi rekam jepret yang diizinkan.
PENDING_ON Dukungan untuk transaksi isolasi rekam jepret dalam status transisi (dari NONAKTIF ke AKTIF). Transaksi terbuka harus selesai.

Tidak ada transaksi isolasi rekam jepret yang diizinkan.
AKTIF Dukungan untuk transaksi isolasi rekam jepret diaktifkan.

Transaksi rekam jepret diizinkan.
PENDING_OFF Dukungan untuk transaksi isolasi rekam jepret dalam status transisi (dari AKTIF ke NONAKTIF).

Transaksi rekam jepret yang dimulai setelah waktu ini tidak dapat mengakses database ini. Memperbarui transaksi masih membayar biaya penerapan versi dalam database ini. Transaksi rekam jepret yang ada masih dapat mengakses database ini tanpa masalah. Status PENDING_OFF tidak menjadi NONAKTIF sampai semua transaksi rekam jepret yang aktif ketika status isolasi rekam jepret database selesai.

sys.databases Gunakan tampilan katalog untuk menentukan status kedua opsi database penerapan versi baris.

Semua pembaruan untuk tabel pengguna dan beberapa tabel sistem yang disimpan di master dan msdb menghasilkan versi baris.

Opsi ALLOW_SNAPSHOT_ISOLATION ini secara otomatis diatur AKTIF dalam master database dan msdb , dan tidak dapat dinonaktifkan.

Pengguna tidak dapat mengatur READ_COMMITTED_SNAPSHOT opsi AKTIF di master, , tempdbatau msdb.

Menggunakan tingkat isolasi berbasis penerapan versi baris

Kerangka kerja penerapan versi baris selalu diaktifkan di SQL Server, dan digunakan oleh beberapa fitur. Selain menyediakan tingkat isolasi berbasis penerapan versi baris, ini digunakan untuk mendukung modifikasi yang dilakukan dalam pemicu dan beberapa sesi kumpulan hasil aktif (MARS), dan untuk mendukung pembacaan data untuk operasi indeks ONLINE.

Tingkat isolasi berbasis penerapan versi baris diaktifkan di tingkat database. Setiap aplikasi yang mengakses objek dari database yang diaktifkan dapat menjalankan kueri menggunakan tingkat isolasi berikut:

  • Penerapan baca yang menggunakan penerapan versi baris dengan mengatur READ_COMMITTED_SNAPSHOT opsi database ke ON seperti yang diperlihatkan dalam contoh kode berikut:

    ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

    Ketika database diaktifkan untuk READ_COMMITTED_SNAPSHOT, semua kueri yang berjalan di bawah tingkat isolasi READ COMMITTED menggunakan penerapan versi baris, yang berarti bahwa operasi baca tidak memblokir operasi pembaruan.

  • Isolasi rekam jepret dengan mengatur ALLOW_SNAPSHOT_ISOLATION opsi database ke ON seperti yang ditunjukkan dalam contoh kode berikut:

    ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

    Transaksi yang berjalan di bawah isolasi rekam jepret dapat mengakses tabel dalam database yang telah diaktifkan untuk rekam jepret. Untuk mengakses tabel yang belum diaktifkan untuk rekam jepret, tingkat isolasi harus diubah. Misalnya, contoh kode berikut menunjukkan SELECT pernyataan yang menggabungkan dua tabel saat berjalan di bawah transaksi rekam jepret. Satu tabel milik database tempat isolasi rekam jepret tidak diaktifkan. SELECT Ketika pernyataan berjalan di bawah isolasi rekam jepret, pernyataan gagal dijalankan dengan sukses.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
    SELECT t1.col5, t2.col5
        FROM Table1 as t1
        INNER JOIN SecondDB.dbo.Table2 as t2
            ON t1.col1 = t2.col2;

    Contoh kode berikut menunjukkan pernyataan yang sama SELECT yang telah dimodifikasi untuk mengubah tingkat isolasi transaksi menjadi berkomitmen baca. Karena perubahan ini, SELECT pernyataan berhasil dijalankan.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
    SELECT t1.col5, t2.col5
        FROM Table1 as t1
        WITH (READCOMMITTED)
        INNER JOIN SecondDB.dbo.Table2 as t2
            ON t1.col1 = t2.col2;

Batasan transaksi menggunakan tingkat isolasi berbasis penerapan versi baris

Pertimbangkan batasan berikut saat bekerja dengan tingkat isolasi berbasis penerapan versi baris:

  • READ_COMMITTED_SNAPSHOT tidak dapat diaktifkan dalam tempdb, msdb, atau master.

  • Tabel sementara global disimpan di tempdb. Saat mengakses tabel sementara global di dalam transaksi rekam jepret, salah satu hal berikut harus terjadi:

    • Atur ALLOW_SNAPSHOT_ISOLATION opsi database AKTIF di tempdb.
    • Gunakan petunjuk isolasi untuk mengubah tingkat isolasi untuk pernyataan tersebut.

  • Transaksi rekam jepret gagal ketika:

    • Database dibuat baca-saja setelah transaksi rekam jepret dimulai, tetapi sebelum transaksi rekam jepret mengakses database.
    • Jika mengakses objek dari beberapa database, status database diubah sedih sehingga pemulihan database terjadi setelah transaksi rekam jepret dimulai, tetapi sebelum transaksi rekam jepret mengakses database. Misalnya: database diatur ke OFFLINE lalu ke ONLINE, database otomatisclose dan buka, atau database lepaskan dan lampirkan.

  • Transaksi terdistribusi, termasuk kueri dalam database yang dipartisi terdistribusi, tidak didukung di bawah isolasi rekam jepret.

  • SQL Server tidak menyimpan beberapa versi metadata sistem. Pernyataan bahasa definisi data (DDL) pada tabel dan objek database lainnya (indeks, tampilan, jenis data, prosedur tersimpan, dan fungsi runtime bahasa umum) mengubah metadata. Jika pernyataan DDL memodifikasi objek, referensi bersamaan apa pun ke objek di bawah isolasi rekam jepret menyebabkan transaksi rekam jepret gagal. Transaksi berkomitmen baca tidak memiliki batasan ini ketika opsi database READ_COMMITTED_SNAPSHOT AKTIF.

    Misalnya, administrator database menjalankan pernyataan berikut ALTER INDEX .

    USE AdventureWorks2022;
GO
ALTER INDEX AK_Employee_LoginID
    ON HumanResources.Employee REBUILD;
GO

    Setiap transaksi rekam jepret yang aktif saat ALTER INDEX pernyataan dijalankan menerima kesalahan jika mencoba mereferensikan HumanResources.Employee tabel setelah ALTER INDEX pernyataan dijalankan. Transaksi yang diterapkan baca menggunakan penerapan versi baris tidak terpengaruh.

    Catatan

    Operasi INSERT MASSAL dapat menyebabkan perubahan pada metadata tabel target (misalnya, saat menonaktifkan pemeriksaan batasan). Ketika ini terjadi, transaksi isolasi rekam jepret bersamaan yang mengakses tabel yang disisipkan secara massal gagal.

Menyesuaikan penguncian dan penerapan versi baris

Menyesuaikan batas waktu penguncian

Ketika instans Mesin Database Microsoft SQL Server tidak dapat memberikan kunci ke transaksi karena transaksi lain sudah memiliki kunci yang bertentangan pada sumber daya, transaksi pertama menjadi diblokir menunggu kunci yang ada dirilis. Secara default, tidak ada periode waktu habis wajib dan tidak ada cara untuk menguji apakah sumber daya terkunci sebelum menguncinya, kecuali untuk mencoba mengakses data (dan berpotensi diblokir tanpa batas waktu).

Catatan

Di SQL Server, gunakan sys.dm_os_waiting_tasks tampilan manajemen dinamis untuk menentukan apakah proses sedang diblokir dan siapa yang memblokirnya. Di versi SQL Server yang lebih lama, gunakan prosedur tersimpan sp_who sistem. Untuk informasi dan contoh selengkapnya, lihat Memahami dan mengatasi masalah pemblokiran SQL Server.

Pengaturan memungkinkan LOCK_TIMEOUT aplikasi untuk mengatur waktu maksimum pernyataan menunggu pada sumber daya yang diblokir. Ketika pernyataan telah menunggu lebih lama dari pengaturan LOCK_TIMEOUT, pernyataan yang diblokir dibatalkan secara otomatis, dan pesan kesalahan 1222 (Lock request time-out period exceeded) dikembalikan ke aplikasi. Namun, setiap transaksi yang berisi pernyataan tidak digulung balik atau dibatalkan oleh SQL Server. Oleh karena itu, aplikasi harus memiliki handler kesalahan yang dapat menjebak pesan kesalahan 1222. Jika aplikasi tidak menjebak kesalahan, aplikasi dapat melanjutkan tidak menyadari bahwa pernyataan individu dalam transaksi telah dibatalkan, dan kesalahan dapat terjadi karena pernyataan nanti dalam transaksi mungkin tergantung pada pernyataan yang tidak pernah dijalankan.

Menerapkan handler kesalahan yang menjebak pesan kesalahan 1222 memungkinkan aplikasi untuk menangani situasi waktu habis dan mengambil tindakan perbaikan, seperti: secara otomatis mengirim ulang pernyataan yang diblokir atau menggulung balik seluruh transaksi.

Untuk menentukan pengaturan saat ini LOCK_TIMEOUT , jalankan @@LOCK_TIMEOUT fungsi :

SELECT @@lock_timeout;
GO

Menyesuaikan tingkat isolasi transaksi

READ COMMITTED adalah tingkat isolasi default untuk Microsoft SQL Server Database Engine. Jika aplikasi harus beroperasi pada tingkat isolasi yang berbeda, aplikasi dapat menggunakan metode berikut untuk mengatur tingkat isolasi:

  • Jalankan pernyataan SET TRANSACTION ISOLATION LEVEL .
  • ADO.NET aplikasi yang menggunakan System.Data.SqlClient namespace terkelola dapat menentukan IsolationLevel opsi dengan menggunakan SqlConnection.BeginTransaction metode .
  • Aplikasi yang menggunakan ADO dapat mengatur Autocommit Isolation Levels properti .
  • Saat memulai transaksi, aplikasi yang menggunakan OLE DB dapat memanggil ITransactionLocal::StartTransaction dengan isoLevel diatur ke tingkat isolasi transaksi yang diinginkan. Saat menentukan tingkat isolasi dalam mode autocommit, aplikasi yang menggunakan OLE DB dapat mengatur DBPROPSET_SESSION properti DBPROP_SESS_AUTOCOMMITISOLEVELS ke tingkat isolasi transaksi yang diinginkan.
  • Aplikasi yang menggunakan ODBC dapat mengatur SQL_COPT_SS_TXN_ISOLATION atribut dengan menggunakan SQLSetConnectAttr.

Ketika tingkat isolasi ditentukan, perilaku penguncian untuk semua kueri dan pernyataan bahasa manipulasi data (DML) dalam sesi SQL Server beroperasi pada tingkat isolasi tersebut. Tingkat isolasi tetap berlaku sampai sesi berakhir atau sampai tingkat isolasi diatur ke tingkat lain.

Contoh berikut mengatur SERIALIZABLE tingkat isolasi:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID
    FROM HumanResources.Employee;
GO

Tingkat isolasi dapat ditimpa untuk kueri individual atau pernyataan DML, jika perlu, dengan menentukan petunjuk tingkat tabel. Menentukan petunjuk tingkat tabel tidak memengaruhi pernyataan lain dalam sesi. Kami menyarankan agar petunjuk tingkat tabel digunakan untuk mengubah perilaku default hanya jika benar-benar diperlukan.

Mesin Database SQL Server mungkin harus memperoleh kunci saat membaca metadata bahkan ketika tingkat isolasi diatur ke tingkat di mana kunci berbagi tidak diminta saat membaca data. Misalnya, transaksi yang berjalan pada tingkat isolasi baca-yang tidak diterapkan tidak memperoleh kunci berbagi saat membaca data, tetapi mungkin terkadang meminta kunci saat membaca tampilan katalog sistem. Ini berarti dimungkinkan bagi transaksi baca yang tidak dilakukan untuk menyebabkan pemblokiran saat mengkueri tabel saat transaksi bersamaan memodifikasi metadata tabel tersebut.

Untuk menentukan tingkat isolasi transaksi yang saat ini ditetapkan, gunakan pernyataan seperti yang DBCC USEROPTIONS ditunjukkan dalam contoh berikut. Kumpulan hasil dapat bervariasi dari hasil yang ditetapkan pada sistem Anda.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Berikut adalah hasil yang ditetapkan.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Petunjuk penguncian

Petunjuk penguncian dapat ditentukan untuk referensi tabel individual dalam pernyataan SELECT, INSERT, UPDATE, dan DELETE. Petunjuk menentukan jenis penguncian atau versi baris yang digunakan instans Mesin Database SQL Server untuk data tabel. Petunjuk penguncian tingkat tabel dapat digunakan ketika kontrol yang lebih baik dari jenis kunci yang diperoleh pada objek diperlukan. Petunjuk penguncian ini mengambil alih tingkat isolasi transaksi saat ini untuk sesi.

Catatan

Petunjuk penguncian tidak disarankan untuk digunakan saat penguncian yang dioptimalkan diaktifkan. Meskipun petunjuk tabel dan kueri dihormati, petunjuk tersebut mengurangi manfaat penguncian yang dioptimalkan. Untuk informasi selengkapnya, lihat Menghindari petunjuk penguncian dengan penguncian yang dioptimalkan.

Untuk informasi selengkapnya tentang petunjuk penguncian tertentu dan perilakunya, lihat Petunjuk Tabel (Transact-SQL).

Catatan

Mesin Database SQL Server hampir selalu memilih tingkat penguncian yang benar. Sebaiknya petunjuk penguncian tingkat tabel digunakan untuk mengubah perilaku penguncian default hanya jika diperlukan. Melarang tingkat penguncian dapat berdampak buruk pada konkurensi.

Mesin Database SQL Server mungkin harus memperoleh kunci saat membaca metadata, bahkan saat memproses pilih dengan petunjuk penguncian yang mencegah permintaan kunci berbagi saat membaca data. Misalnya, SELECT menggunakan NOLOCK petunjuk tidak memperoleh kunci berbagi saat membaca data, tetapi mungkin terkadang meminta kunci saat membaca tampilan katalog sistem. Ini berarti dimungkinkan untuk pernyataan yang SELECT menggunakan NOLOCK untuk diblokir.

Seperti yang ditunjukkan dalam contoh berikut, jika tingkat isolasi transaksi diatur ke SERIALIZABLE, dan petunjuk NOLOCK penguncian tingkat tabel digunakan dengan SELECT pernyataan, kunci rentang kunci biasanya digunakan untuk mempertahankan transaksi yang dapat diserialisasikan tidak diperoleh.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
    FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT
        resource_type,
        resource_subtype,
        request_mode
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@spid;

-- End the transaction.
ROLLBACK;
GO

Satu-satunya kunci yang diperoleh yang dirujuk HumanResources.Employee adalah kunci stabilitas skema (Sch-S). Dalam hal ini, serialisasi tidak lagi dijamin.

Di SQL Server, LOCK_ESCALATION opsi ALTER TABLE untuk dapat mencela kunci tabel, dan mengaktifkan kunci HoBT pada tabel yang dipartisi. Opsi ini bukan petunjuk penguncian, tetapi dapat digunakan untuk mengurangi eskalasi kunci. Untuk informasi selengkapnya, lihat ALTER TABLE (Transact-SQL).

Menyesuaikan penguncian untuk indeks

Mesin Database SQL Server menggunakan strategi penguncian dinamis yang secara otomatis memilih granularitas penguncian terbaik untuk kueri dalam banyak kasus. Kami menyarankan agar Anda tidak mengambil alih tingkat penguncian default, yang memiliki penguncian halaman dan baris, kecuali tabel atau pola akses indeks dipahami dengan baik dan konsisten, dan ada masalah ketidakcocokan sumber daya untuk diselesaikan. Mengambil alih tingkat penguncian dapat secara signifikan menghambat akses bersamaan ke tabel atau indeks. Misalnya, menentukan hanya kunci tingkat tabel pada tabel besar yang sangat dapat diakses pengguna dapat menyebabkan hambatan karena pengguna harus menunggu kunci tingkat tabel dirilis sebelum mengakses tabel.

Ada beberapa kasus di mana melarang penguncian halaman atau baris dapat bermanfaat, jika pola akses dipahami dengan baik dan konsisten. Misalnya, aplikasi database menggunakan tabel pencarian yang diperbarui setiap minggu dalam proses batch. Pembaca bersamaan mengakses tabel dengan kunci bersama (S) dan pembaruan batch mingguan mengakses tabel dengan kunci eksklusif (X). Menonaktifkan penguncian halaman dan baris pada tabel mengurangi overhead penguncian sepanjang minggu dengan memungkinkan pembaca mengakses tabel secara bersamaan melalui kunci tabel bersama. Ketika pekerjaan batch berjalan, itu dapat menyelesaikan pembaruan secara efisien karena mendapatkan kunci tabel eksklusif.

Menonaktifkan penguncian halaman dan baris mungkin atau mungkin tidak dapat diterima karena pembaruan batch mingguan akan memblokir pembaca bersamaan agar tidak mengakses tabel saat pembaruan berjalan. Jika pekerjaan batch hanya mengubah beberapa baris atau halaman, Anda dapat mengubah tingkat penguncian untuk mengizinkan penguncian tingkat baris atau halaman, yang akan memungkinkan sesi lain membaca dari tabel tanpa memblokir. Jika pekerjaan batch memiliki sejumlah besar pembaruan, mendapatkan kunci eksklusif pada tabel mungkin merupakan cara terbaik untuk memastikan pekerjaan batch selesai secara efisien.

Terkadang kebuntuan terjadi ketika dua operasi bersamaan memperoleh kunci baris pada tabel yang sama dan kemudian memblokir karena keduanya perlu mengunci halaman. Melarang kunci baris memaksa salah satu operasi untuk menunggu, menghindari kebuntuan. Untuk informasi selengkapnya tentang kebuntuan, lihat panduan Kebuntuan.

Granularitas penguncian yang digunakan pada indeks dapat diatur menggunakan CREATE INDEX pernyataan dan ALTER INDEX . Pengaturan kunci berlaku untuk halaman indeks dan halaman tabel. Selain itu, CREATE TABLE pernyataan dan ALTER TABLE dapat digunakan untuk mengatur granularitas penguncian pada PRIMARY KEY dan UNIQUE batasan. Untuk kompatibilitas mundur, prosedur tersimpan sp_indexoption sistem juga dapat mengatur granularitas. Untuk menampilkan opsi penguncian saat ini untuk indeks tertentu, gunakan INDEXPROPERTY fungsi . Kunci tingkat halaman, kunci tingkat baris, atau kombinasi kunci tingkat halaman dan tingkat baris dapat dilarang untuk indeks tertentu.

Kunci yang tidak diizinkan Indeks diakses oleh
Tingkat halaman Kunci tingkat baris dan tingkat tabel
Tingkat baris Kunci tingkat halaman dan tingkat tabel
Tingkat halaman dan tingkat baris Kunci tingkat tabel

Informasi transaksi tingkat lanjut

Transaksi nest

Transaksi eksplisit dapat ditumpuk. Ini terutama dimaksudkan untuk mendukung transaksi dalam prosedur tersimpan yang dapat dipanggil baik dari proses yang sudah dalam transaksi atau dari proses yang tidak memiliki transaksi aktif.

Contoh berikut menunjukkan penggunaan transaksi berlapis yang dimaksudkan. Prosedur TransProc memberlakukan transaksinya terlepas dari mode transaksi dari setiap proses yang menjalankannya. Jika TransProc dipanggil ketika transaksi aktif, transaksi berlapis di TransProc sebagian besar diabaikan, dan pernyataannya INSERT diterapkan atau digulung balik berdasarkan tindakan akhir yang diperoleh untuk transaksi luar. Jika TransProc dijalankan oleh proses yang tidak memiliki transaksi terutang, COMMIT TRANSACTION di akhir prosedur secara efektif menerapkan INSERT pernyataan.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
               Colb CHAR(3) NOT NULL);
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc;
GO
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
EXECUTE TransProc 3,'bbb';
GO
/* The following SELECT statement shows only rows 3 and 4 are
   still in the table. This indicates that the commit
   of the inner transaction from the first EXECUTE statement of
   TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans;
GO

Melakukan transaksi dalam diabaikan oleh Mesin Database SQL Server. Transaksi dilakukan atau digulirkan kembali berdasarkan tindakan yang diperoleh pada akhir transaksi terluar. Jika transaksi luar dilakukan, transaksi berlapis dalam juga dilakukan. Jika transaksi luar digulung balik, maka semua transaksi dalam juga digulung balik, terlepas dari apakah transaksi dalam dilakukan secara individual atau tidak.

Setiap panggilan ke COMMIT TRANSACTION atau COMMIT WORK berlaku untuk yang terakhir dijalankan BEGIN TRANSACTION. BEGIN TRANSACTION Jika pernyataan ditumpuk, maka COMMIT pernyataan hanya berlaku untuk transaksi berlapis terakhir, yang merupakan transaksi terdalu. Bahkan jika COMMIT TRANSACTION transaction_name pernyataan dalam transaksi berlapis mengacu pada nama transaksi transaksi luar, penerapan hanya berlaku untuk transaksi terdahulu.

Tidak legal untuk parameter ROLLBACK TRANSACTION transaction_name pernyataan untuk merujuk ke transaksi dalam dari sekumpulan transaksi berlapis bernama. transaction_name hanya dapat merujuk ke nama transaksi transaksi terluar. Jika pernyataan ROLLBACK TRANSACTION transaction_name menggunakan nama transaksi luar dijalankan pada tingkat apa pun dari sekumpulan transaksi berlapis, semua transaksi berlapis digulung balik. ROLLBACK WORK Jika pernyataan atau ROLLBACK TRANSACTION tanpa parameter transaction_name dijalankan pada tingkat apa pun dari sekumpulan transaksi berlapis, itu mengembalikan semua transaksi berlapis, termasuk transaksi terluar.

Fungsi ini @@TRANCOUNT merekam tingkat berlapis transaksi saat ini. Setiap BEGIN TRANSACTION pernyataan bertambah @@TRANCOUNT satu per satu. Masing-masing COMMIT TRANSACTION atau COMMIT WORK pernyataan dekremen @@TRANCOUNT satu per satu. Atau ROLLBACK WORKROLLBACK TRANSACTION pernyataan yang tidak memiliki nama transaksi mengembalikan semua transaksi berlapis dan penurunan @@TRANCOUNT menjadi 0. ROLLBACK TRANSACTION Yang menggunakan nama transaksi transaksi terluar dalam sekumpulan transaksi berlapis mengembalikan semua transaksi dan penurunan @@TRANCOUNT berlapis menjadi 0. Ketika Anda tidak yakin apakah Anda sudah dalam transaksi, SELECT @@TRANCOUNT untuk menentukan apakah itu 1 atau lebih. Jika @@TRANCOUNT adalah 0, Anda tidak dalam transaksi.

Menggunakan sesi terikat

Sesi terikat memudahkan koordinasi tindakan di beberapa sesi di server yang sama. Sesi terikat memungkinkan dua sesi atau lebih untuk berbagi transaksi dan kunci yang sama, dan dapat mengerjakan data yang sama tanpa konflik kunci. Sesi terikat dapat dibuat dari beberapa sesi dalam aplikasi yang sama atau dari beberapa aplikasi dengan sesi terpisah.

Untuk berpartisipasi dalam sesi terikat, sesi memanggil sp_getbindtoken atau srv_getbindtoken (melalui Open Data Services) untuk mendapatkan token ikatan. Token ikat adalah string karakter yang secara unik mengidentifikasi setiap transaksi terikat. Token ikatan kemudian dikirim ke sesi lain untuk terikat dengan sesi saat ini. Sesi lain mengikat transaksi dengan memanggil sp_bindsession, menggunakan token ikatan yang diterima dari sesi pertama.

Catatan

Sesi harus memiliki transaksi pengguna aktif agar sp_getbindtoken atau srv_getbindtoken berhasil.

Token ikat harus ditransmisikan dari kode aplikasi yang membuat sesi pertama ke kode aplikasi yang kemudian mengikat sesi mereka ke sesi pertama. Tidak ada pernyataan Transact-SQL atau fungsi API yang dapat digunakan aplikasi untuk mendapatkan token ikatan untuk transaksi yang dimulai oleh proses lain. Beberapa metode yang dapat digunakan untuk mengirimkan token ikatan meliputi yang berikut:

  • Jika semua sesi dimulai dari proses aplikasi yang sama, token ikatan dapat disimpan dalam memori global atau diteruskan ke fungsi sebagai parameter.

  • Jika sesi dibuat dari proses aplikasi terpisah, token ikat dapat ditransmisikan menggunakan komunikasi antarproses (IPC), seperti panggilan prosedur jarak jauh (RPC) atau pertukaran data dinamis (DDE).

  • Token ikatan dapat disimpan dalam tabel dalam instans Mesin Database SQL Server yang dapat dibaca oleh proses yang ingin mengikat sesi pertama.

Hanya satu sesi dalam satu set sesi terikat yang dapat aktif kapan saja. Jika satu sesi menjalankan pernyataan pada instans atau memiliki hasil yang tertunda dari instans, tidak ada sesi lain yang terikat ke dalamnya yang dapat mengakses instans sampai sesi saat ini selesai memproses atau membatalkan pernyataan saat ini. Jika instans sibuk memproses pernyataan dari sesi terikat lainnya, terjadi kesalahan yang menunjukkan bahwa ruang transaksi sedang digunakan dan sesi harus mencoba kembali nanti.

Saat Anda mengikat 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.

Jenis sesi terikat

Dua jenis sesi terikat bersifat lokal dan terdistribusi.

  • Sesi terikat lokal Memungkinkan sesi terikat untuk berbagi ruang transaksi dari satu transaksi dalam satu instans Mesin Database SQL Server.

  • Sesi terikat terdistribusi Memungkinkan sesi terikat untuk berbagi transaksi yang sama di dua instans atau lebih sampai seluruh transaksi diterapkan atau digulung balik dengan menggunakan Koordinator Transaksi Terdistribusi Microsoft (MS DTC).

Sesi terikat terdistribusi tidak diidentifikasi oleh token ikatan string karakter; mereka diidentifikasi dengan nomor identifikasi transaksi terdistribusi. Jika sesi terikat terlibat dalam transaksi lokal dan menjalankan RPC di server jarak jauh dengan SET REMOTE_PROC_TRANSACTIONS ON, transaksi terikat lokal secara otomatis dipromosikan ke transaksi terikat terdistribusi oleh MS DTC dan sesi MS DTC dimulai.

Kapan menggunakan sesi terikat

Dalam versi SQL Server sebelumnya, sesi terikat terutama digunakan dalam mengembangkan prosedur tersimpan yang diperluas yang harus menjalankan pernyataan Transact-SQL atas nama proses yang memanggilnya. Memiliki proses panggilan lulus dalam token ikatan sebagai salah satu parameter dari prosedur tersimpan yang diperluas memungkinkan prosedur untuk bergabung dengan ruang transaksi proses panggilan, sehingga mengintegrasikan prosedur tersimpan yang diperluas dengan proses panggilan.

Di Mesin Database SQL Server, prosedur tersimpan yang ditulis menggunakan CLR lebih aman, dapat diskalakan, dan stabil daripada prosedur tersimpan yang diperluas. Prosedur yang disimpan CLR menggunakan objek SqlContext untuk menggabungkan konteks sesi panggilan, bukan sp_bindsession.

Sesi terikat dapat digunakan untuk mengembangkan aplikasi tiga tingkat di mana logika bisnis dimasukkan ke dalam program terpisah yang bekerja secara kooperatif pada satu transaksi bisnis. Program-program ini harus dikodekan untuk mengoordinasikan akses mereka ke database dengan hati-hati. Karena kedua sesi memiliki kunci yang sama, kedua program tidak boleh mencoba mengubah data yang sama secara bersamaan. Kapan saja, hanya satu sesi yang dapat melakukan pekerjaan sebagai bagian dari transaksi; tidak mungkin ada eksekusi paralel. Transaksi hanya dapat dialihkan antar sesi pada titik hasil yang terdefinisi dengan baik, seperti ketika semua pernyataan DML telah selesai dan hasilnya telah diambil.

Transaksi efisien kode

Penting untuk menjaga transaksi sesingkat mungkin. Ketika transaksi dimulai, sistem manajemen database (DBMS) harus menyimpan banyak sumber daya hingga akhir transaksi untuk melindungi properti atomitas, konsistensi, isolasi, dan durabilitas (ACID) transaksi. Jika data dimodifikasi, baris yang dimodifikasi harus dilindungi dengan kunci eksklusif yang mencegah transaksi lain membaca baris, dan kunci eksklusif harus ditahan sampai transaksi dilakukan atau digulung balik. Tergantung pada pengaturan tingkat isolasi transaksi, SELECT pernyataan dapat memperoleh kunci yang harus ditahan hingga transaksi dilakukan atau digulung balik. Terutama dalam sistem dengan banyak pengguna, transaksi harus dijaga sesingkat mungkin untuk mengurangi ketidakcocokan penguncian untuk sumber daya antara koneksi bersamaan. Transaksi yang berjalan lama dan tidak efisien mungkin tidak menjadi masalah dengan sejumlah kecil pengguna, tetapi mereka tidak dapat ditoleransi dalam sistem dengan ribuan pengguna. Dimulai dengan SQL Server 2014 (12.x), SQL Server mendukung transaksi tahan lama yang tertunda. Transaksi tahan lama yang tertunda tidak menjamin durabilitas. Untuk informasi selengkapnya, lihat Mengontrol Durabilitas Transaksi.

Panduan kode

Ini adalah panduan untuk mengkoding transaksi yang efisien:

  • Tidak memerlukan input dari pengguna selama transaksi. Dapatkan semua input yang diperlukan dari pengguna sebelum transaksi dimulai. Jika input pengguna tambahan diperlukan selama transaksi, gulung balik transaksi saat ini dan mulai ulang transaksi setelah input pengguna disediakan. Bahkan jika pengguna segera merespons, waktu reaksi manusia jauh lebih lambat daripada kecepatan komputer. Semua sumber daya yang disimpan oleh transaksi ditahan untuk waktu yang sangat lama, yang berpotensi menyebabkan masalah pemblokiran. Jika pengguna tidak merespons, transaksi tetap aktif, mengunci sumber daya penting sampai mereka merespons, yang mungkin tidak terjadi selama beberapa menit atau bahkan berjam-jam.

  • Jangan membuka transaksi saat menelusuri data, jika memungkinkan. Transaksi tidak boleh dimulai sampai semua analisis data awal selesai.

  • Jaga transaksi sesingkat mungkin. Setelah Anda mengetahui modifikasi yang harus dilakukan, mulai transaksi, jalankan pernyataan modifikasi, lalu segera terapkan atau gulung balik. Jangan buka transaksi sebelum diperlukan.

  • Untuk mengurangi pemblokiran, pertimbangkan untuk menggunakan tingkat isolasi berbasis penerapan versi baris untuk kueri baca-saja.

  • Gunakan tingkat isolasi transaksi yang lebih rendah dengan cerdas. Banyak aplikasi dapat dengan mudah dikodekan untuk menggunakan tingkat isolasi transaksi berkomitmen baca. Tidak semua transaksi memerlukan tingkat isolasi transaksi yang dapat diserialisasikan.

  • Gunakan opsi konkurensi kursor yang lebih rendah secara cerdas, seperti opsi konkurensi optimis. Dalam sistem dengan probabilitas rendah pembaruan bersamaan, overhead berurusan dengan kesalahan "orang lain mengubah data Anda setelah Anda membacanya" bisa jauh lebih rendah daripada overhead baris penguncian selalu saat dibaca.

  • Akses jumlah data sekecil mungkin saat dalam transaksi. Ini mengurangi jumlah baris terkunci, sehingga mengurangi ketidakcocokan antar transaksi.

  • Hindari petunjuk penguncian pesimis seperti holdlock jika memungkinkan. Petunjuk seperti TINGKAT isolasi HOLDLOCK atau SERIALIZABLE dapat menyebabkan proses menunggu bahkan pada kunci bersama dan mengurangi konkurensi

  • Hindari menggunakan transaksi Implisit ketika kemungkinan transaksi Implisit dapat memperkenalkan perilaku yang tidak dapat diprediksi karena sifatnya. Lihat Masalah Transaksi implisit dan konkurensi

  • Indeks desain dengan faktor pengisian yang berkurang Mengurangi faktor pengisian dapat membantu Anda mencegah atau mengurangi fragmentasi halaman indeks dan dengan demikian mengurangi waktu pencarian indeks terutama ketika diambil dari disk. Untuk melihat informasi fragmentasi untuk data dan indeks tabel atau tampilan, Anda bisa menggunakan sys.dm_db_index_physical_stats.

Transaksi implisit dan menghindari konkurensi dan masalah sumber daya

Untuk mencegah konkurensi dan masalah sumber daya, kelola transaksi implisit dengan hati-hati. Saat menggunakan transaksi implisit, pernyataan Transact-SQL berikutnya setelah COMMIT atau ROLLBACK secara otomatis memulai transaksi baru. Ini dapat menyebabkan transaksi baru dibuka saat aplikasi menelusuri data, atau bahkan ketika memerlukan input dari pengguna. Setelah menyelesaikan transaksi terakhir yang diperlukan untuk melindungi modifikasi data, nonaktifkan transaksi implisit hingga transaksi sekali lagi diperlukan untuk melindungi modifikasi data. Proses ini memungkinkan Mesin Database SQL Server menggunakan mode autocommit saat aplikasi menelusuri data dan mendapatkan input dari pengguna.

Selain itu, ketika tingkat isolasi rekam jepret diaktifkan, meskipun transaksi baru tidak akan menahan kunci, transaksi yang berjalan lama akan mencegah versi lama dihapus dari tempdb.

Mengelola transaksi yang berjalan lama

Transaksi jangka panjang adalah transaksi aktif yang belum dilakukan atau digulung balik transaksi tepat waktu. Misalnya, jika awal dan akhir transaksi dikendalikan oleh pengguna, penyebab umum transaksi yang berjalan lama adalah pengguna yang memulai transaksi dan kemudian pergi saat transaksi menunggu respons dari pengguna.

Transaksi yang berjalan lama dapat menyebabkan masalah serius untuk database, sebagai berikut:

  • Jika instans server dimatikan setelah transaksi aktif melakukan banyak modifikasi yang tidak dilakukan, fase pemulihan mulai ulang berikutnya dapat memakan waktu lebih lama dari waktu yang ditentukan oleh opsi konfigurasi server interval pemulihan atau oleh ALTER DATABASE ... SET TARGET_RECOVERY_TIME opsi . Opsi ini mengontrol frekuensi titik pemeriksaan aktif dan tidak langsung, masing-masing. Untuk informasi selengkapnya tentang jenis titik pemeriksaan, lihat Titik pemeriksaan database (SQL Server).

  • Lebih penting lagi, meskipun transaksi tunggu mungkin menghasilkan log yang sangat sedikit, transaksi menyimpan pemotongan log tanpa batas waktu, menyebabkan log transaksi tumbuh dan mungkin terisi. Jika log transaksi terisi, database tidak dapat melakukan pembaruan lagi. Untuk informasi selengkapnya, lihat Arsitektur log transaksi SQL Server dan panduan manajemen, Memecahkan masalah log transaksi lengkap (Kesalahan SQL Server 9002), dan Log transaksi.

Penting

Di Azure SQL Database, transaksi diam (transaksi yang belum ditulis ke log transaksi selama enam jam) secara otomatis dihentikan, untuk membebaskan sumber daya.

Temukan transaksi jangka panjang

Untuk mencari transaksi jangka panjang, gunakan salah satu hal berikut:

  • sys.dm_tran_database_transactions

    Tampilan manajemen dinamis ini mengembalikan informasi terkait transaksi di tingkat database. Untuk transaksi jangka panjang, kolom kepentingan tertentu termasuk waktu rekaman log pertama (database_transaction_begin_time), status transaksi terkini (database_transaction_state), dan nomor urutan log (LSN) dari rekaman awal di log transaksi (database_transaction_begin_lsn).

    Untuk informasi selengkapnya, lihat sys.dm_tran_database_transactions (Transact-SQL).

  • DBCC OPENTRAN

    Pernyataan ini memungkinkan Anda mengidentifikasi ID pengguna pemilik transaksi, sehingga Anda dapat berpotensi melacak sumber transaksi untuk penghentian yang lebih terarah (menerapkan dan bukannya mengambil kembali). Untuk informasi selengkapnya, lihat DBCC OPENTRAN (Transact-SQL).

Menghentikan transaksi

Anda mungkin harus menggunakan pernyataan KILL. Gunakan pernyataan ini dengan sangat hati-hati, namun, terutama ketika proses kritis berjalan. Untuk informasi selengkapnya, lihat Pernyataan KILL (T-SQL).

Kebuntuan

Kebuntuan adalah topik kompleks yang terkait dengan penguncian, tetapi berbeda dari pemblokiran.

Konten terkait