Panduan penguncian transaksi dan penerapan versi baris

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

Dalam database apa pun, kesalahan manajemen 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.

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 membiarkan 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 yang ditautkan bergantian, harus benar di akhir transaksi.

Catatan

SQL Server dokumentasi 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. Tinjau Panduan Arsitektur dan Desain Indeks SQL Server untuk detailnya.

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 serialisasi karena menghasilkan kemampuan untuk memuat ulang data awal dan memutar ulang serangkaian transaksi untuk berakhir dengan data dalam keadaan yang sama dengan setelah transaksi asli dilakukan.

Daya tahan
Setelah transaksi yang sepenuhnya tahan lama selesai, efeknya secara permanen berlaku 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 yang tertunda dilakukan sebelum catatan log transaksi dipertahankan ke disk. Untuk informasi selengkapnya tentang durabilitas transaksi yang tertunda, lihat artikel 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 mencakup pernyataan modifikasi ini dalam satu transaksi sehingga mesin database SQL Server dapat menegakkan 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 saat dimulai ulang untuk secara otomatis mengembalikan transaksi yang tidak lengkap ke titik kegagalan sistem. Transaksi tahan lama tertunda dilakukan sebelum catatan log transaksi diperkeras ke disk. Transaksi tersebut dapat hilang jika ada kegagalan sistem sebelum rekaman log diperkeras ke disk. Untuk informasi selengkapnya tentang durabilitas transaksi yang tertunda, lihat artikel 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 yang mengakhiri transaksi dengan benar sebelum selesai. Untuk informasi selengkapnya, lihat Transaksi, Transaksi dalam ODBC dan Transaksi dalam SQL Server Native Client (OLEDB).

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, 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
  • MENGUBAH DATABASE
  • JATUHKAN DATABASE
  • BUAT KATALOG TEKS LENGKAP
  • UBAH KATALOG FULLTEXT
  • JATUHKAN KATALOG TEKS LENGKAP
  • HILANGKAN INDEKS TEKS PENUH
  • ALTER FULLTEXT INDEX
  • BUAT 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 berkomitmen secara independen dari transaksi penutup dan tidak dapat digulung balik.

Transaksi Autocommit
Mode autocommit adalah mode manajemen transaksi default dari mesin database SQL Server. 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 mengembalikan setiap transaksi. Mode transaksi implisit menghasilkan rantai transaksi berkelanjutan. Atur mode transaksi implisit pada melalui fungsi API atau pernyataan TRANSACT-SQL SET IMPLICIT_TRANSACTIONS ON. Mode ini juga dikenal sebagai Autocommit OFF, lihat setAutoCommit Method di JDBC

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 dalam cakupan batch
    Hanya berlaku untuk beberapa tataan hasil aktif (MARS), transaksi eksplisit atau implisit Transact-SQL yang dimulai di bawah sesi MARS menjadi transaksi dalam 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 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 berkomitmen sementara yang lain mengembalikan 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 dibersihkan ke disk. Saat 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 yang tertunda dilakukan sebelum gambar log untuk transaksi dibersihkan ke disk. Untuk informasi selengkapnya tentang durabilitas transaksi yang tertunda, lihat artikel 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.

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

Mengakhiri transaksi

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

  • MELAKUKAN
    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, gulung balik transaksi. Pernyataan ROLLBACK mencadangkan semua modifikasi yang dilakukan dalam transaksi dengan mengembalikan data ke status 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 sementara ada operasi luar biasa 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 yang luar biasa 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 merusak 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 apa pun menyebabkan pembatalan otomatis transaksi saat ini. Kompilasi kesalahan, seperti kesalahan sintaksis, tidak terpengaruh 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 ada dalam transaksi, adalah konstruksi Transact-SQL TRY...CATCH . Untuk informasi selengkapnya dengan 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).

Kesalahan kompilasi dan run-time dalam mode autocommit

Dalam mode autocommit, terkadang muncul seolah-olah instans mesin database SQL Server telah menggulung balik 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 dieksekusi. 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 dieksekusi.

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 SQL Server Database 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 menjaga 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 samping 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 yang 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 (bacaan kotor)

    Dependensi yang tidak dikomit 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 dilakukan sejauh ini salah dan menghapus pengeditan dan menyimpan dokumen. Dokumen terdistribusi berisi pengeditan yang sudah tidak ada lagi 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 pembacaan penulis menulis ulang dokumen. Ketika editor membaca dokumen untuk kedua kalinya, itu telah berubah. Bacaan asli tidak dapat diulang. Masalah ini dapat dihindari jika penulis tidak dapat mengubah dokumen sampai editor selesai membacanya untuk terakhir kalinya.

  • Pembacaan 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 pada 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 pada saat yang sama, 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 melembagakan 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 berkonflik dengan kunci hingga pemilik melepaskannya. Ini disebut kontrol pesimis karena terutama digunakan di lingkungan di mana ada ketidakcocokan data yang tinggi, 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 data yang rendah, dan di mana biaya sesekali menggulung balik transaksi lebih rendah dari 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 diambil 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 dibebaskan.
    • 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 pada saat yang sama, 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 lainnya. Memilih tingkat isolasi yang sesuai tergantung pada penyeimbangan persyaratan integritas data aplikasi terhadap overhead setiap tingkat isolasi. Tingkat isolasi tertinggi, yang 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, dibaca tidak dilakukan, dapat mengambil data yang telah dimodifikasi tetapi tidak dilakukan oleh transaksi lain. Semua efek samping konkurensi dapat terjadi dalam baca 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 diisolasi cukup untuk memastikan bahwa data yang rusak secara fisik tidak dibaca. Dalam tingkat ini, bacaan kotor diizinkan, sehingga satu transaksi mungkin melihat perubahan yang belum dilakukan oleh transaksi lain.
Baca berkomitmen Memungkinkan transaksi membaca data yang sebelumnya dibaca (tidak dimodifikasi) oleh transaksi lain tanpa menunggu transaksi pertama selesai. Mesin SQL Server Database terus menulis kunci (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 kunci rentang tidak dikelola, bacaan phantom dapat terjadi.
Serializable Tingkat tertinggi di mana transaksi sepenuhnya terisolasi satu sama lain. Mesin SQL Server Database terus membaca dan menulis kunci yang diperoleh pada data yang dipilih untuk dirilis pada akhir transaksi. Kunci rentang diperoleh ketika operasi SELECT menggunakan klausul 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 yang diterapkan baca, 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 yang diterapkan baca 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 dilakukan setelah pernyataan yang berisi UDF dimulai.

READ_COMMITTED_SNAPSHOT Ketika opsi database diatur NONAKTIF, yang merupakan pengaturan default, isolasi yang diterapkan baca 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 sampai transaksi lain selesai. Kedua implementasi memenuhi definisi ISO dari isolasi berkomitmen baca.
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 runtime bahasa umum (CLR). Pernyataan ini diizinkan ketika Anda menggunakan isolasi snapshot dalam transaksi implisit. Transaksi implisit, menurut definisi, adalah satu pernyataan yang memungkinkan untuk menegakkan 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 dipelajari Phantom
Baca tidak dikomit Ya Ya Ya
Baca berkomitmen Tidak Ya Ya
Bacaan yang dapat diulang Tidak Tidak Ya
Snapshot Tidak Tidak Tidak
Serializable Tidak Tidak 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.

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

ADO
Aplikasi ADO mengatur IsolationLevel properti objek Koneksi ke adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead, atau adXactReadSerializable.

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

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

Saat menentukan tingkat isolasi transaksi dalam mode autocommit, aplikasi OLE DB dapat mengatur DBPROP_SESS_AUTOCOMMITISOLEVELS properti DBPROPSET_SESSION ke DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED, atau DBPROPVAL_TI_SNAPSHOT.

ODBC
Aplikasi ODBC memanggil SQLSetConnectAttr dengan Atribut yang diatur ke SQL_ATTR_TXN_ISOLATION dan ValuePtr diatur ke SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, 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.

Saat transaksi memodifikasi sepotong data, transaksi menyimpan kunci yang melindungi modifikasi hingga akhir transaksi. Berapa lama transaksi memegang kunci yang diperoleh untuk melindungi operasi baca tergantung pada pengaturan tingkat isolasi transaksi. Semua kunci yang dipegang oleh transaksi dilepaskan 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.

Granularitas dan hierarki penguncian

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 melindungi sumber daya sepenuhnya. 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 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 Tumpuk atau pohon B. Kunci yang melindungi pohon B (indeks) atau halaman data tumpuk dalam tabel yang tidak memiliki indeks berkluster.
TABEL 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.

Catatan

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

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: intent shared (IS), intent exclusive (IX), dan shared with intent exclusive (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 transaksi.

Perbarui kunci

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

Untuk menghindari potensi masalah kebuntuan ini, kunci pembaruan (U) digunakan. Hanya satu transaksi yang dapat memperoleh kunci pembaruan (U) ke sumber daya pada satu waktu. Jika transaksi memodifikasi sumber daya, kunci pembaruan (U) dikonversi ke kunci eksklusif (X).

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

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 pada 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 yang 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 pada tingkat tabel mencegah transaksi lain 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), intent exclusive (IX), dan dibagikan dengan intent exclusive (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) dari 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 dilakukan oleh transaksi lain, meskipun transaksi lain dapat membaca sumber daya 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 kunci Anda dan 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 Bulk Insert API seperti .NET SqlBulkCopy, OLEDB Fast Load API, atau ODBC Bulk Copy API untuk menyalin data secara massal ke dalam tabel.
  • Petunjuk TABLOCK ditentukan atau opsi kunci tabel pada tabel muat massal diatur menggunakan sp_tableoption.

Tip

Tidak seperti pernyataan BULK INSERT, yang memegang kunci Bulk Update (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 penyisipan 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.

Mengunci kompatibilitas

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, pembaruan, atau eksklusif) pada sumber daya tersebut hingga kunci eksklusif (X) dilepaskan. 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 sampai kunci bersama telah dilepaskan.

Tabel berikut ini memperlihatkan 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 Tidak Tidak Tidak
Pembaruan (U) Ya Ya Tidak Tidak Tidak Tidak
Niat eksklusif (IX) Ya Tidak Tidak Ya Tidak Tidak
Dibagikan dengan niat eksklusif (SIX) Ya Tidak Tidak Tidak Tidak Tidak
Eksklusif (X) Tidak Tidak Tidak Tidak Tidak Tidak

Catatan

Kunci eksklusif niat (IX) kompatibel dengan mode kunci IX karena IX berarti niatnya adalah untuk 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.

lock_conflicts

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 sekumpulan 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 tersebut 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 RangeS-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 RangeS-U RangeI-N RangeX-X
Mode yang diminta
Bersama (S) Ya Ya Tidak Ya Ya Ya Tidak
Pembaruan (U) Ya Tidak Tidak Ya Tidak Ya Tidak
Eksklusif (X) Tidak Tidak Tidak Tidak Tidak Ya Tidak
Rentang-S Ya Ya Tidak Ya Ya Tidak Tidak
RangeS-U Ya Tidak Tidak Ya Tidak Tidak Tidak
RangeI-N Ya Ya Ya Tidak Tidak Ya Tidak
RangeX-X Tidak Tidak Tidak Tidak Tidak Tidak 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 RangeS-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 mengikuti.

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 dimasukkan 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 yang 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 ditahan 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 disisipkan di antara kedua 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.

Operasi hapus

Saat menghapus nilai dalam transaksi, rentang nilai tidak harus dikunci selama 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.

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.

Operasi Sisipkan

Saat memasukkan nilai dalam transaksi, rentang nilai tidak harus dikunci selama 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 melakukan atau menggulung balik.

Eskalasi kunci

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

Saat 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 yang lebih tinggi yang berisi halaman. Selain kunci niat pada objek, kunci halaman niat diminta pada objek berikut:
    • Halaman tingkat daun dari indeks nonkluster
    • 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 kunci baris, kunci, atau halaman.

Mesin Database tidak meningkatkan kunci baris atau rentang kunci ke kunci halaman, tetapi meningkatkannya langsung ke kunci tabel. Demikian pula, kunci halaman selalu dilaporkan 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, kunci apa pun yang diperoleh oleh transaksi dalam pernyataan sebelumnya dan masih diadakan 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.
  • TableAUpdates . Ini menghasilkan kunci baris eksklusif di TableA yang ditahan hingga transaksi selesai.
  • TableBUpdates . 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 sebelum TableA 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 pada TableA yang dieskalasikan. Ini termasuk kunci bersama dari pernyataan SELECT dan kunci eksklusif dari pernyataan UPDATE sebelumnya. Meskipun hanya kunci sesi yang diperoleh untuk TableA pernyataan SELECT yang dihitung untuk menentukan apakah eskalasi kunci harus dilakukan, setelah eskalasi berhasil semua kunci yang ditahan oleh sesi di TableA diekskalasikan 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 dalam TableB pernyataan SELECT. Demikian pula tidak ada upaya yang dilakukan untuk meningkatkan kunci pada TableC, yang tidak dieskalasikan karena belum diakses ketika eskalasi terjadi.

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 OTOMATIS.
  • Jumlah kunci dalam instans Mesin Database melebihi memori atau ambang konfigurasi.

Jika kunci tidak dapat di eskalasi 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, TableB, dan 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 mengakses TableC. Ketika Mesin Database mendeteksi bahwa pernyataan telah memperoleh setidaknya 5.000 kunci baris di TableB, ia 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 memiliki panjang sekitar 100 byte. Ambang batas ini dinamis karena Mesin Database secara dinamis memperoleh dan membebaskan memori untuk menyesuaikan berbagai beban kerja.

  • 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.
  • Updates 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 eksklusif niat (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 dibebaskan. 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:

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

  • Menggunakan petunjuk tabel PAGLOCK atau TABLOCK agar halaman penggunaan Mesin Database, tumpukan, atau kunci indeks alih-alih kunci baris. 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.

  • 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 catatan 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 Marka Buku 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 sesedikim mungkin untuk meminimalkan biaya Pencarian Bookmark (memaksimalkan selektivitas indeks untuk kueri tertentu). Jika Mesin Database memperkirakan bahwa operator logis Bookmark Lookup dapat mengembalikan banyak baris, operator mungkin menggunakan PREFETCH untuk melakukan pencarian marka buku. Jika Mesin Database 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, namun, 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 Bookmark Lookup dalam rencana kueri. Dimungkinkan 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 mengunci 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 berbeda yang tidak kompatibel dengan jenis kunci yang di eskalasi. Kunci IX (niat eksklusif) di tingkat tabel tidak mengunci baris atau halaman apa pun, tetapi masih tidak kompatibel dengan kunci TAB S (bersama) atau X (eksklusif) yang di eskalasi. 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 menyimpan kunci IX di 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 ribuan 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:

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 1204 terjadi, itu menghentikan pemrosesan pernyataan saat ini dan menyebabkan pembatalan transaksi aktif. Pembatalan itu sendiri dapat memblokir pengguna atau menyebabkan waktu pemulihan database yang lama jika Anda memulai ulang layanan database.

Catatan

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

Selain itu, 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 penurunan.

Biaya Penguncian vs. Biaya Konkurensi

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, perilaku eskalasi kunci telah berubah dengan pengenalan LOCK_ESCALATION opsi. Untuk informasi selengkapnya, lihat LOCK_ESCALATION opsi UBAH TABEL.

Kebuntuan

Kebuntuan terjadi ketika dua atau beberapa tugas secara permanen memblokir satu sama lain oleh setiap tugas yang memiliki kunci pada sumber daya yang coba dikunci oleh tugas lain. Contohnya:

  • Transaksi A memperoleh kunci bersama pada baris 1.
  • Transaksi B memperoleh kunci bersama pada baris 2.
  • Transaksi A sekarang meminta kunci eksklusif pada baris 2, dan diblokir hingga transaksi B selesai dan melepaskan kunci bersama yang dimilikinya pada baris 2.
  • Transaksi B sekarang meminta kunci eksklusif pada baris 1, dan diblokir hingga transaksi A selesai dan melepaskan kunci bersama yang dimilikinya pada baris 1.

Transaksi A tidak dapat diselesaikan sampai transaksi B selesai, tetapi transaksi B diblokir oleh transaksi A. Kondisi ini juga disebut dependensi siklik: Transaksi A memiliki dependensi pada transaksi B, dan transaksi B menutup lingkaran dengan memiliki dependensi pada transaksi A.

Kedua transaksi dalam kebuntuan akan menunggu selamanya kecuali kebuntuan rusak oleh proses eksternal. monitor kebuntuan mesin database SQL Server secara berkala memeriksa tugas yang mengalami kebuntuan. Jika monitor mendeteksi dependensi siklik, monitor memilih salah satu tugas sebagai korban dan mengakhiri transaksinya dengan kesalahan. Ini memungkinkan tugas lain untuk menyelesaikan transaksinya. Aplikasi dengan transaksi yang dihentikan dengan kesalahan dapat mencoba kembali transaksi, yang biasanya selesai setelah transaksi lain yang mengalami kebuntuan selesai.

Kebuntuan sering dikacaukan dengan pemblokiran normal. Ketika transaksi meminta kunci pada sumber daya yang dikunci oleh transaksi lain, transaksi yang meminta menunggu hingga kunci dilepaskan. Secara default, SQL Server transaksi tidak kehabisan waktu, kecuali LOCK_TIMEOUT diatur. Transaksi yang meminta diblokir, tidak mengalami kebuntuan, karena transaksi yang meminta belum melakukan apa pun untuk memblokir transaksi yang memiliki kunci. Akhirnya, transaksi pemilik akan selesai dan melepaskan kunci, dan kemudian transaksi yang meminta akan diberikan kunci dan melanjutkan.

Catatan

Kebuntuan terkadang disebut pelukan mematikan.

Kebuntuan adalah kondisi yang dapat terjadi pada sistem apa pun dengan beberapa utas, bukan hanya pada sistem manajemen database relasional, dan dapat terjadi untuk sumber daya selain kunci pada objek database. Misalnya, utas dalam sistem operasi multithreaded mungkin memperoleh satu atau beberapa sumber daya, seperti blok memori. Jika sumber daya yang diperoleh saat ini dimiliki oleh utas lain, utas pertama mungkin harus menunggu utas pemilik untuk merilis sumber daya target. Utas tunggu dikatakan memiliki dependensi pada utas pemilik untuk sumber daya tertentu. Dalam instans mesin database SQL Server, sesi dapat mengalami kebuntuan saat memperoleh sumber daya nondatabase, seperti memori atau utas.

Diagram memperlihatkan kebuntuan transaksi

Dalam ilustrasi, transaksi T1 memiliki dependensi pada transaksi T2 untuk Part sumber daya kunci tabel. Demikian pula, transaksi T2 memiliki dependensi pada transaksi T1 untuk Supplier sumber daya kunci tabel. Karena dependensi ini membentuk siklus, ada kebuntuan antara transaksi T1 dan T2.

Kebuntuan juga dapat terjadi ketika tabel dipartisi dan LOCK_ESCALATION pengaturan ALTER TABLE diatur ke OTOMATIS. Ketika LOCK_ESCALATION diatur ke OTOMATIS, konkurensi meningkat dengan memungkinkan mesin database SQL Server mengunci partisi tabel di tingkat HoBT alih-alih pada tingkat tabel. Namun, ketika transaksi terpisah memegang kunci partisi dalam tabel dan menginginkan kunci di suatu tempat pada partisi transaksi lain, ini menyebabkan kebuntuan. Jenis kebuntuan ini dapat dihindari dengan mengatur LOCK_ESCALATION ke TABLE; meskipun pengaturan ini akan mengurangi konkurensi dengan memaksa pembaruan besar ke partisi untuk menunggu kunci tabel.

Mendeteksi dan mengakhiri kebuntuan

Kebuntuan terjadi ketika dua atau beberapa tugas secara permanen memblokir satu sama lain oleh setiap tugas yang memiliki kunci pada sumber daya yang coba dikunci oleh tugas lain. Grafik berikut menyajikan tampilan tingkat tinggi dari status kebuntuan di mana:

  • Tugas T1 memiliki kunci pada sumber daya R1 (ditunjukkan oleh panah dari R1 ke T1) dan telah meminta kunci pada sumber daya R2 (ditunjukkan oleh panah dari T1 ke R2).
  • Tugas T2 memiliki kunci pada sumber daya R2 (ditunjukkan oleh panah dari R2 ke T2) dan telah meminta kunci pada sumber daya R1 (ditunjukkan oleh panah dari T2 ke R1).
  • Karena tidak ada tugas yang dapat dilanjutkan sampai sumber daya tersedia dan tidak ada sumber daya yang dapat dirilis sampai tugas berlanjut, status kebuntuan ada.
    Diagram memperlihatkan tugas dalam status kebuntuan

Mesin Database SQL Server secara otomatis mendeteksi siklus kebuntuan dalam SQL Server. Mesin Database SQL Server memilih salah satu sesi sebagai korban kebuntuan dan transaksi saat ini dihentikan dengan kesalahan untuk memecah kebuntuan.

Sumber daya yang dapat kebuntuan

Setiap sesi pengguna mungkin memiliki satu atau beberapa tugas yang berjalan atas namanya di mana setiap tugas mungkin memperoleh atau menunggu untuk memperoleh berbagai sumber daya. Jenis sumber daya berikut dapat menyebabkan pemblokiran yang dapat mengakibatkan kebuntuan.

  • Kunci. Menunggu untuk memperoleh kunci pada sumber daya, seperti objek, halaman, baris, metadata, dan aplikasi dapat menyebabkan kebuntuan. Misalnya, transaksi T1 memiliki kunci bersama (S) pada baris r1 dan menunggu untuk mendapatkan kunci eksklusif (X) pada r2. Transaksi T2 memiliki kunci bersama (S) pada r2 dan menunggu untuk mendapatkan kunci eksklusif (X) pada baris r1. Ini menghasilkan siklus kunci di mana T1 dan T2 saling menunggu untuk melepaskan sumber daya yang terkunci.

  • Utas pekerja. Tugas antrean yang menunggu utas pekerja yang tersedia dapat menyebabkan kebuntuan. Jika tugas yang diantrekan memiliki sumber daya yang memblokir semua utas pekerja, kebuntuan akan mengakibatkan. Misalnya, sesi S1 memulai transaksi dan memperoleh kunci bersama (S) pada baris r1 lalu tidur. Sesi aktif yang berjalan pada semua utas pekerja yang tersedia mencoba memperoleh kunci eksklusif (X) pada baris r1. Karena sesi S1 tidak dapat memperoleh utas pekerja, sesi tidak dapat melakukan transaksi dan melepaskan kunci pada baris r1. Ini menghasilkan kebuntuan.

  • Memori. Ketika permintaan bersamaan menunggu peruntukan memori yang tidak dapat dipenuhi dengan memori yang tersedia, kebuntuan dapat terjadi. Misalnya, dua kueri bersamaan, Q1 dan Q2, dijalankan sebagai fungsi yang ditentukan pengguna yang masing-masing memperoleh memori 10 MB dan 20 MB. Jika setiap kueri membutuhkan 30 MB dan total memori yang tersedia adalah 20 MB, maka Q1 dan Q2 harus menunggu satu sama lain untuk melepaskan memori, dan ini menghasilkan kebuntuan.

  • Sumber daya terkait eksekusi kueri paralel. Rangkaian koordinator, produsen, atau konsumen yang terkait dengan port pertukaran dapat saling memblokir satu sama lain yang menyebabkan kebuntuan biasanya ketika menyertakan setidaknya satu proses lain yang bukan bagian dari kueri paralel. Selain itu, ketika kueri paralel memulai eksekusi, SQL Server menentukan tingkat paralelisme, atau jumlah utas pekerja, berdasarkan beban kerja saat ini. Jika beban kerja sistem tiba-tiba berubah, misalnya, di mana kueri baru mulai berjalan di server atau sistem kehabisan utas pekerja, maka kebuntuan dapat terjadi.

  • Beberapa sumber daya Active Result Sets (MARS). Sumber daya ini digunakan untuk mengontrol interleaving beberapa permintaan aktif di bawah MARS. Untuk informasi selengkapnya, lihat Menggunakan Beberapa Kumpulan Hasil Aktif (MARS).

    • Sumber daya pengguna. Ketika utas menunggu sumber daya yang berpotensi dikontrol oleh aplikasi pengguna, sumber daya dianggap sebagai sumber daya eksternal atau pengguna dan diperlakukan seperti kunci.

    • Mutex sesi. Tugas yang berjalan dalam satu sesi diselingi, yang berarti bahwa hanya satu tugas yang dapat berjalan di bawah sesi pada waktu tertentu. Sebelum tugas dapat berjalan, tugas harus memiliki akses eksklusif ke mutex sesi.

    • Mutex transaksi. Semua tugas yang berjalan dalam satu transaksi saling terkait, yang berarti bahwa hanya satu tugas yang dapat berjalan di bawah transaksi pada waktu tertentu. Sebelum tugas dapat berjalan, tugas harus memiliki akses eksklusif ke mutex transaksi.

    Agar tugas berjalan di bawah MARS, tugas harus memperoleh mutex sesi. Jika tugas berjalan di bawah transaksi, tugas tersebut kemudian harus memperoleh mutex transaksi. Ini menjamin bahwa hanya satu tugas yang aktif pada satu waktu dalam sesi tertentu dan transaksi tertentu. Setelah mutex yang diperlukan diperoleh, tugas dapat dijalankan. Ketika tugas selesai, atau menghasilkan di tengah permintaan, pertama-tama akan merilis mutex transaksi diikuti oleh mutex sesi dalam urutan akuisisi terbalik. Namun, kebuntuan dapat terjadi dengan sumber daya ini. Dalam contoh kode berikut, dua tugas, permintaan pengguna U1 dan U2 permintaan pengguna, berjalan dalam sesi yang sama.

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");  
    U2:    Rs2=Command2.Execute("select colA from sometable");  
    

    Prosedur tersimpan yang dijalankan dari permintaan pengguna U1 telah memperoleh mutex sesi. Jika prosedur tersimpan membutuhkan waktu lama untuk dijalankan, diasumsikan oleh SQL Server Database Engine bahwa prosedur tersimpan menunggu input dari pengguna. Permintaan pengguna U2 sedang menunggu mutex sesi saat pengguna sedang menunggu hasil yang ditetapkan dari U2, dan U1 sedang menunggu sumber daya pengguna. Ini adalah status kebuntuan yang diilustrasikan secara logis sebagai:

LogicFlowExamplec

Deteksi kebuntuan

Semua sumber daya yang tercantum di bagian di atas berpartisipasi dalam skema deteksi kebuntuan Mesin Database SQL Server. Deteksi kebuntuan dilakukan oleh utas monitor kunci yang secara berkala memulai pencarian melalui semua tugas dalam instans mesin database SQL Server. Poin-poin berikut menjelaskan proses pencarian:

  • Interval default adalah 5 detik.
  • Jika utas monitor kunci menemukan kebuntuan, interval deteksi kebuntuan akan turun dari 5 detik menjadi serendah 100 milidetik tergantung pada frekuensi kebuntuan.
  • Jika utas monitor kunci berhenti menemukan kebuntuan, mesin database SQL Server meningkatkan interval antara pencarian menjadi 5 detik.
  • Jika kebuntuan baru saja terdeteksi, diasumsikan bahwa utas berikutnya yang harus menunggu kunci memasuki siklus kebuntuan. Beberapa kunci pertama menunggu setelah kebuntuan terdeteksi akan segera memicu pencarian kebuntuan daripada menunggu interval deteksi kebuntuan berikutnya. Misalnya, jika interval saat ini adalah 5 detik, dan kebuntuan baru saja terdeteksi, penantian kunci berikutnya akan segera memulai detektor kebuntuan. Jika tunggu kunci ini adalah bagian dari kebuntuan, kunci akan segera terdeteksi daripada selama pencarian kebuntuan berikutnya.

Mesin database SQL Server biasanya hanya melakukan deteksi kebuntuan berkala. Karena jumlah kebuntuan yang ditemui dalam sistem biasanya kecil, deteksi kebuntuan berkala membantu mengurangi overhead deteksi kebuntuan dalam sistem.

Ketika monitor kunci memulai pencarian kebuntuan untuk utas tertentu, monitor tersebut mengidentifikasi sumber daya tempat utas menunggu. Monitor kunci kemudian menemukan pemilik untuk sumber daya tertentu dan secara rekursif melanjutkan pencarian kebuntuan untuk utas tersebut sampai menemukan siklus. Siklus yang diidentifikasi dengan cara ini membentuk kebuntuan.

Setelah kebuntuan terdeteksi, Mesin Database SQL Server mengakhiri kebuntuan dengan memilih salah satu utas sebagai korban kebuntuan. Mesin database SQL Server mengakhiri batch saat ini yang dijalankan untuk utas, mengembalikan transaksi korban kebuntuan, dan mengembalikan kesalahan 1205 ke aplikasi. Menggulung balik transaksi untuk korban kebuntuan melepaskan semua kunci yang dipegang oleh transaksi. Ini memungkinkan transaksi utas lain menjadi tidak diblokir dan dilanjutkan. Kesalahan korban kebuntuan 1205 mencatat informasi tentang utas dan sumber daya yang terlibat dalam kebuntuan dalam log kesalahan.

Secara default, mesin database SQL Server memilih sebagai korban kebuntuan sesi yang menjalankan transaksi yang paling murah untuk digulung balik. Atau, pengguna dapat menentukan prioritas sesi dalam situasi kebuntuan menggunakan SET DEADLOCK_PRIORITY pernyataan . DEADLOCK_PRIORITY dapat diatur ke RENDAH, NORMAL, atau TINGGI, atau dapat diatur ke nilai bilangan bulat apa pun dalam rentang (-10 hingga 10). Prioritas kebuntuan default ke NORMAL. Jika dua sesi memiliki prioritas kebuntuan yang berbeda, sesi dengan prioritas lebih rendah dipilih sebagai korban kebuntuan. Jika kedua sesi memiliki prioritas kebuntuan yang sama, sesi dengan transaksi yang paling murah untuk digulung balik dipilih. Jika sesi yang terlibat dalam siklus kebuntuan memiliki prioritas kebuntuan yang sama dan biaya yang sama, korban dipilih secara acak.

Saat bekerja dengan CLR, monitor kebuntuan secara otomatis mendeteksi kebuntuan untuk sumber daya sinkronisasi (monitor, kunci pembaca/penulis, dan gabungan utas) yang diakses di dalam prosedur terkelola. Namun, kebuntuan diselesaikan dengan melemparkan pengecualian dalam prosedur yang dipilih untuk menjadi korban kebuntuan. Penting untuk dipahami bahwa pengecualian tidak secara otomatis melepaskan sumber daya yang saat ini dimiliki oleh korban; sumber daya harus dirilis secara eksplisit. Konsisten dengan perilaku pengecualian, pengecualian yang digunakan untuk mengidentifikasi korban kebuntuan dapat ditangkap dan diberhentikan.

Alat informasi kebuntuan

Untuk melihat informasi kebuntuan, mesin SQL Server Database menyediakan alat pemantauan dalam bentuk sesi system_health xEvent, dua bendera pelacakan, dan peristiwa grafik kebuntuan di SQL Profiler.

Peristiwa yang diperluas kebuntuan

Dimulai dengan SQL Server 2012 (11.x), xml_deadlock_report Extended Event (xEvent) harus digunakan alih-alih kelas peristiwa grafik Kebuntuan di SQL Trace atau SQL Profiler.

Juga dimulai dengan SQL Server 2012 (11.x), ketika kebuntuan terjadi, sesi system_health sudah menangkap semua xml_deadlock_report xEvent yang berisi grafik kebuntuan. Karena sesi system_health diaktifkan secara default, tidak diwajibkan bahwa sesi xEvent terpisah dikonfigurasi untuk menangkap informasi kebuntuan.

Grafik kebuntuan yang diambil biasanya memiliki tiga simpul yang berbeda:

  • daftar korban. Pengidentifikasi proses korban kebuntuan.
  • daftar proses. Informasi tentang semua proses yang terlibat dalam kebuntuan.
  • daftar sumber daya. Informasi tentang sumber daya yang terlibat dalam kebuntuan.

Membuka file sesi system_health atau buffer cincin, jika xml_deadlock_report xEvent direkam, Management Studio menyajikan penggambaran grafis tugas dan sumber daya yang terlibat dalam kebuntuan, seperti yang terlihat dalam contoh berikut:

Grafik Kebuntuan xEvent

Kueri berikut ini bisa menampilkan semua peristiwa kebuntuan yang diambil oleh buffer cincin sesi system_health :

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
	xdr.query('.') AS [Event_Data]
FROM (SELECT CAST([target_data] AS XML) AS Target_Data
			FROM sys.dm_xe_session_targets AS xt
			INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
			WHERE xs.name = N'system_health'
			  AND xt.target_name = N'ring_buffer'
	) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;

Berikut adalah hasil yang ditetapkan.

system_health_xevent_query_result

Contoh berikut menunjukkan output, setelah mengklik tautan pertama hasil di atas:

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2019.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1) 
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2019.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1) 
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2019.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2019.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

Untuk informasi selengkapnya, lihat Menggunakan Sesi system_health

Bendera pelacakan 1204 dan bendera pelacakan 1222

Ketika kebuntuan terjadi, bendera pelacakan 1204 dan bendera pelacakan 1222 mengembalikan informasi yang diambil dalam log kesalahan SQL Server. Bendera pelacakan 1204 melaporkan informasi kebuntuan yang diformat oleh setiap simpul yang terlibat dalam kebuntuan. Bendera pelacakan 1222 memformat informasi kebuntuan, pertama menurut proses lalu berdasarkan sumber daya. Dimungkinkan untuk mengaktifkan kedua bendera pelacakan untuk mendapatkan dua representasi dari peristiwa kebuntuan yang sama.

Penting

Hindari menggunakan bendera pelacakan 1204 dan 1222 pada sistem intensif beban kerja yang menyebabkan kebuntuan. Menggunakan bendera pelacakan ini dapat menimbulkan masalah performa. Sebagai gantinya, gunakan Peristiwa Yang Diperluas Kebuntuan.

Selain menentukan properti bendera pelacakan 1204 dan 1222, tabel berikut ini juga menunjukkan kesamaan dan perbedaan.

Properti Bendera Pelacakan 1204 dan Bendera Pelacakan 1222 Bendera Pelacakan 1204 saja Bendera Pelacakan hanya 1222
Format output Output diambil dalam log kesalahan SQL Server. Berfokus pada simpul yang terlibat dalam kebuntuan. Setiap simpul memiliki bagian khusus, dan bagian akhir menjelaskan korban kebuntuan. Mengembalikan informasi dalam format seperti XML yang tidak sesuai dengan skema Definisi Skema XML (XSD). Format ini memiliki tiga bagian utama. Bagian pertama menyatakan korban kebuntuan. Bagian kedua menjelaskan setiap proses yang terlibat dalam kebuntuan. Bagian ketiga menjelaskan sumber daya yang identik dengan simpul dalam bendera pelacakan 1204.
Mengidentifikasi atribut SPID:<x> ECID:<x>. Mengidentifikasi utas ID proses sistem dalam kasus proses paralel. Entri SPID:<x> ECID:0, di mana <x> digantikan oleh nilai SPID, mewakili utas utama. Entri SPID:<x> ECID:<y>, di mana <x> digantikan oleh nilai SPID dan <y> lebih besar dari 0, mewakili subthread untuk SPID yang sama.

BatchID (sbid untuk bendera pelacakan 1222). Mengidentifikasi batch dari mana eksekusi kode meminta atau menahan kunci. Saat Beberapa Kumpulan Hasil Aktif (MARS) dinonaktifkan, nilai BatchID adalah 0. Ketika MARS diaktifkan, nilai untuk batch aktif adalah 1 hingga n. Jika tidak ada batch aktif dalam sesi, BatchID adalah 0.

Mode. Menentukan jenis kunci untuk sumber daya tertentu yang diminta, diberikan, atau ditunggu oleh utas. Mode dapat berupa IS (Intent Shared), S (Shared), U (Update), IX (Intent Exclusive), SIX (Shared with Intent Exclusive), dan X (Exclusive).

Garis # (garis untuk bendera pelacakan 1222). Mencantumkan nomor baris dalam batch pernyataan saat ini yang sedang dijalankan ketika kebuntuan terjadi.

Input Buf (inputbuf untuk bendera pelacakan 1222). Mencantumkan semua pernyataan dalam batch saat ini.
Node. Mewakili nomor entri dalam rantai kebuntuan.

Daftar. Pemilik kunci dapat menjadi bagian dari daftar ini:

Daftar Pemberian. Menghitung pemilik sumber daya saat ini.

Konversi Daftar. Menghitung pemilik saat ini yang mencoba mengonversi kunci mereka ke tingkat yang lebih tinggi.

Daftar Tunggu. Menghitung permintaan kunci baru saat ini untuk sumber daya.

Jenis Pernyataan. Menjelaskan jenis pernyataan DML (SELECT, INSERT, UPDATE, atau DELETE) tempat utas memiliki izin.

Pemilik Sumber Daya Korban. Menentukan utas yang berpartisipasi yang SQL Server pilih sebagai korban untuk memutus siklus kebuntuan. Utas yang dipilih dan semua subthread yang ada dihentikan.

Cabang Berikutnya. Mewakili dua subthread atau lebih dari SPID yang sama yang terlibat dalam siklus kebuntuan.
korban kebuntuan. Mewakili alamat memori fisik tugas (lihat sys.dm_os_tasks (Transact-SQL)) yang dipilih sebagai korban kebuntuan. Mungkin 0 (nol) dalam kasus kebuntuan yang belum terselesaikan. Tugas yang digulung balik tidak dapat dipilih sebagai korban kebuntuan.

tumpukan eksekusi. Mewakili kode Transact-SQL yang sedang dijalankan pada saat kebuntuan terjadi.

prioritas. Mewakili prioritas kebuntuan. Dalam kasus tertentu, mesin database SQL Server dapat memilih untuk mengubah prioritas kebuntuan untuk durasi singkat untuk mencapai konkurensi yang lebih baik.

logused. Ruang log yang digunakan oleh tugas.

id pemilik. ID transaksi yang memiliki kontrol atas permintaan.

status. Status tugas. Ini adalah salah satu nilai berikut:

>>tertunda. Menunggu utas pekerja.

>>dapat dijalankan. Siap untuk menjalankan tetapi menunggu kuantum.

>>berjalan. Saat ini berjalan pada penjadwal.

>>ditangguhkan. Eksekusi ditangguhkan.

>>selesai. Tugas telah selesai.

>>spinloop. Menunggu spinlock menjadi bebas.

sumber daya waitresource. Sumber daya yang diperlukan oleh tugas.

waktu tunggu. Waktu dalam milidetik menunggu sumber daya.

schedulerid. Penjadwal yang terkait dengan tugas ini. Lihat sys.dm_os_schedulers (Transact-SQL).

nama host. Nama stasiun kerja.

isolationlevel. Tingkat isolasi transaksi saat ini.

Xactid. ID transaksi yang memiliki kontrol atas permintaan.

currentdb. ID database.

lastbatchstarted. Terakhir kali proses klien memulai eksekusi batch.

lastbatchcompleted. Terakhir kali proses klien menyelesaikan eksekusi batch.

clientoption1 dan clientoption2. Atur opsi pada koneksi klien ini. Ini adalah bitmask yang mencakup informasi tentang opsi yang biasanya dikontrol oleh pernyataan SET seperti SET NOCOUNT dan SET XACTABORT.

associatedObjectId. Mewakili ID HoBT (heap atau B-tree).
Atribut sumber daya BERKUDA. Mengidentifikasi satu baris dalam tabel tempat kunci ditahan atau diminta. RID diwakili sebagai RID: db_id:file_id:page_no:row_no. Contohnya:RID: 6:1:20789:0

OBJEK . Mengidentifikasi tabel tempat kunci ditahan atau diminta. OBJECT direpresentasikan sebagai OBJECT: db_id:object_id. Contohnya:TAB: 6:2009058193

KUNCI. Mengidentifikasi rentang kunci dalam indeks tempat kunci ditahan atau diminta. KEY direpresentasikan sebagai KEY: db_id:hobt_id (nilai hash kunci indeks). Contohnya:KEY: 6:72057594057457664 (350007a4d329)

Ini pag. Mengidentifikasi sumber daya halaman tempat kunci ditahan atau diminta. PAG direpresentasikan sebagai PAG: db_id:file_id:page_no. Contohnya:PAG: 6:1:20789

EXT. Mengidentifikasi struktur jangkauan. EXT direpresentasikan sebagai EXT: db_id:file_id:extent_no. Contohnya:EXT: 6:1:9

DB. Mengidentifikasi kunci database. DB diwakili dengan salah satu cara berikut:

DB: db_id

DB: db_id[BULK-OP-DB], yang mengidentifikasi kunci database yang diambil oleh database cadangan.

DB: db_id[BULK-OP-LOG], yang mengidentifikasi kunci yang diambil oleh log cadangan untuk database tertentu.

APP. Mengidentifikasi kunci yang diambil oleh sumber daya aplikasi. APP direpresentasikan sebagai APP: lock_resource. Contohnya:APP: Formf370f478

METADATA. Mewakili sumber daya metadata yang terlibat dalam kebuntuan. Karena METADATA memiliki banyak sub sumber daya, nilai yang dikembalikan tergantung pada subsumber daya yang mengalami kebuntuan. Misalnya, METADATA. USER_TYPE mengembalikan user_type_id =<integer_value>. Untuk informasi selengkapnya tentang sumber daya dan subsumber daya METADATA, lihat sys.dm_tran_locks (Transact-SQL).

INI HOBT. Mewakili timbunan atau pohon B yang terlibat dalam kebuntuan.
Tidak ada yang eksklusif untuk bendera pelacakan ini. Tidak ada yang eksklusif untuk bendera pelacakan ini.
Contoh bendera pelacakan 1204

Contoh berikut menunjukkan output saat bendera pelacakan 1204 diaktifkan. Dalam hal ini, tabel di Node 1 adalah tumpukan tanpa indeks, dan tabel di Node 2 adalah tumpukan dengan indeks non-klusster. Kunci indeks di Node 2 sedang diperbarui ketika kebuntuan terjadi.

Deadlock encountered .... Printing deadlock information  
Wait-for graph  
  
Node:1  
  
RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2  
 Grant List 0:  
   Owner:0x0315D6A0 Mode: X          
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C  
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6  
   Input Buf: Language Event:   
BEGIN TRANSACTION  
   EXEC usp_p2  
 Requested By:   
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0   
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)  
  
Node:2  
  
KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0  
 Grant List 0:  
   Owner:0x0315D140 Mode: X          
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4  
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6  
   Input Buf: Language Event:   
     BEGIN TRANSACTION  
       EXEC usp_p1  
 Requested By:   
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258   
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)  
  
Victim Resource Owner:  
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258   
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)  
Contoh Bendera Pelacakan 1222

Contoh berikut menunjukkan output saat bendera pelacakan 1222 diaktifkan. Dalam hal ini, satu tabel adalah tumpukan tanpa indeks, dan tabel lainnya adalah tumpukan dengan indeks non-klusster. Dalam tabel kedua, kunci indeks sedang diperbarui ketika kebuntuan terjadi.

deadlock-list  
 deadlock victim=process689978  
  process-list  
   process id=process6891f8 taskpriority=0 logused=868   
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444   
   transactionname=user_transaction   
   lasttranstarted=2022-02-05T11:22:42.733 XDES=0x3a3dad0   
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54   
   sbid=0 ecid=0 priority=0 transcount=2   
   lastbatchstarted=2022-02-05T11:22:42.733   
   lastbatchcompleted=2022-02-05T11:22:42.733   
   clientapp=Microsoft SQL Server Management Studio - Query   
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user   
   isolationlevel=read committed (2) xactid=310444 currentdb=6   
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200  
    executionStack  
     frame procname=AdventureWorks2019.dbo.usp_p1 line=6 stmtstart=202   
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000  
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;       
     frame procname=adhoc line=3 stmtstart=44   
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000  
     EXEC usp_p1       
    inputbuf  
      BEGIN TRANSACTION  
       EXEC usp_p1  
   process id=process689978 taskpriority=0 logused=380   
   waitresource=KEY: 6:72057594057457664 (350007a4d329)     
   waittime=5015 ownerId=310462 transactionname=user_transaction   
   lasttranstarted=2022-02-05T11:22:44.077 XDES=0x4d9e258 lockMode=U   
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0   
   priority=0 transcount=2 lastbatchstarted=2022-02-05T11:22:44.077   
   lastbatchcompleted=2022-02-05T11:22:44.077   
   clientapp=Microsoft SQL Server Management Studio - Query   
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user   
   isolationlevel=read committed (2) xactid=310462 currentdb=6   
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200  
    executionStack  
     frame procname=AdventureWorks2019.dbo.usp_p2 line=6 stmtstart=200   
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000  
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;       
     frame procname=adhoc line=3 stmtstart=44   
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000  
     EXEC usp_p2       
    inputbuf  
      BEGIN TRANSACTION  
        EXEC usp_p2      
  resource-list  
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2019.dbo.T2   
   id=lock3136940 mode=X associatedObjectId=72057594057392128  
    owner-list  
     owner id=process689978 mode=X  
    waiter-list  
     waiter id=process6891f8 mode=U requestType=wait  
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2019.dbo.T1   
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X   
   associatedObjectId=72057594057457664  
    owner-list  
     owner id=process6891f8 mode=X  
    waiter-list  
     waiter id=process689978 mode=U requestType=wait  

Peristiwa grafik kebuntuan profiler

Ini adalah peristiwa di SQL Profiler yang menyajikan penggambutan grafis tugas dan sumber daya yang terlibat dalam kebuntuan. Contoh berikut menunjukkan output dari SQL Profiler saat peristiwa grafik kebuntuan diaktifkan.

ProfilerDeadlockGraphc

Untuk informasi selengkapnya tentang peristiwa kebuntuan, lihat Kunci:Kelas Peristiwa Kebuntuan.

Untuk informasi selengkapnya tentang menjalankan grafik kebuntuan SQL Profiler, lihat Menyimpan Grafik Kebuntuan (SQL Server Profiler).

Menangani kebuntuan

Ketika instans mesin database SQL Server memilih transaksi sebagai korban kebuntuan, ia mengakhiri batch saat ini, mengembalikan transaksi, dan mengembalikan pesan kesalahan 1205 ke aplikasi.

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Karena aplikasi apa pun yang mengirimkan kueri Transact-SQL dapat dipilih sebagai korban kebuntuan, aplikasi harus memiliki penangan kesalahan yang dapat menjebak pesan kesalahan 1205. Jika aplikasi tidak menjebak kesalahan, aplikasi dapat melanjutkan tidak menyadari bahwa transaksinya telah digulung balik dan kesalahan dapat terjadi.

Menerapkan penangan kesalahan yang menjebak pesan kesalahan 1205 memungkinkan aplikasi untuk menangani situasi kebuntuan dan mengambil tindakan perbaikan (misalnya, secara otomatis mengirim ulang kueri yang terlibat dalam kebuntuan). Dengan mengirim ulang kueri secara otomatis, pengguna tidak perlu mengetahui bahwa kebuntuan terjadi.

Aplikasi harus dijeda sebentar sebelum mengirimkan ulang kuerinya. Ini memberi kesempatan kepada transaksi lain yang mengalami kebuntuan untuk menyelesaikan dan melepaskan kuncinya yang membentuk bagian dari siklus kebuntuan. Ini meminimalkan kemungkinan kebuntuan terjadi kembali saat kueri yang dikirim ulang meminta kuncinya.

Meminimalkan kebuntuan

Meskipun kebuntuan tidak dapat sepenuhnya dihindari, mengikuti konvensi pengkodian tertentu dapat meminimalkan kemungkinan menghasilkan kebuntuan. Meminimalkan kebuntuan dapat meningkatkan throughput transaksi dan mengurangi overhead sistem, karena lebih sedikit transaksi:

  • Digulung balik, membatalkan semua pekerjaan yang dilakukan oleh transaksi.
  • Dikirim ulang oleh aplikasi karena digulung balik saat mengalami kebuntuan.

Untuk membantu meminimalkan kebuntuan:

  • Akses objek dalam urutan yang sama.
  • Hindari interaksi pengguna dalam transaksi. - Menjaga transaksi tetap pendek dan dalam satu batch.
  • Gunakan tingkat isolasi yang lebih rendah.
  • Gunakan tingkat isolasi berbasis penerapan versi baris.
    • Atur READ_COMMITTED_SNAPSHOT opsi database pada untuk mengaktifkan transaksi berkomitmen baca untuk menggunakan penerapan versi baris.
    • Gunakan isolasi salinan bayangan.
  • Gunakan koneksi terikat.

Mengakses objek dalam urutan yang sama

Jika semua transaksi bersamaan mengakses objek dalam urutan yang sama, kebuntuan cenderung tidak terjadi. Misalnya, jika dua transaksi bersamaan mendapatkan kunci pada tabel Pemasok dan kemudian pada tabel Bagian , satu transaksi diblokir pada tabel Pemasok sampai transaksi lainnya selesai. Setelah transaksi pertama diterapkan atau digulung balik, transaksi kedua berlanjut, dan kebuntuan tidak terjadi. Menggunakan prosedur tersimpan untuk semua modifikasi data dapat menstandarkan urutan mengakses objek.

kebuntuan2

Hindari interaksi pengguna dalam transaksi

Hindari menulis transaksi yang mencakup interaksi pengguna, karena kecepatan batch yang berjalan tanpa intervensi pengguna jauh lebih cepat daripada kecepatan yang melibatkan pengguna untuk merespons kueri secara manual, seperti membalas permintaan parameter yang diminta oleh aplikasi. Misalnya, jika transaksi menunggu input pengguna dan pengguna pergi ke makan siang atau bahkan di rumah untuk akhir pekan, pengguna menunda transaksi selesai. Hal ini akan menurunkan throughput sistem karena kunci apa pun yang dipegang oleh transaksi hanya dilepaskan ketika transaksi dilakukan atau digulung balik. Bahkan jika situasi kebuntuan tidak muncul, transaksi lain yang mengakses sumber daya yang sama diblokir sambil menunggu transaksi selesai.

Menjaga transaksi tetap pendek dan dalam satu batch

Kebuntuan biasanya terjadi ketika beberapa transaksi jangka panjang dijalankan secara bersamaan dalam database yang sama. Semakin lama transaksi, semakin lama kunci eksklusif atau pembaruan ditahan, memblokir aktivitas lain dan menyebabkan kemungkinan situasi kebuntuan.

Melakukan transaksi dalam satu batch meminimalkan bolak-balik jaringan selama transaksi, mengurangi kemungkinan penundaan dalam menyelesaikan transaksi dan melepaskan kunci.

Menggunakan tingkat isolasi yang lebih rendah

Tentukan apakah transaksi dapat berjalan pada tingkat isolasi yang lebih rendah. Menerapkan penerapan baca memungkinkan transaksi untuk membaca data yang sebelumnya dibaca (tidak dimodifikasi) oleh transaksi lain tanpa menunggu transaksi pertama selesai. Menggunakan tingkat isolasi yang lebih rendah, seperti penerapan pembacaan, menyimpan kunci bersama dengan durasi yang lebih singkat daripada tingkat isolasi yang lebih tinggi, seperti serializeable. Ini mengurangi ketidakcocokan penguncian.

Menggunakan tingkat isolasi berbasis penerapan versi baris

READ_COMMITTED_SNAPSHOT Saat opsi database diatur AKTIF, transaksi yang berjalan di bawah tingkat isolasi berkomitmen baca menggunakan penerapan versi baris daripada kunci bersama selama operasi baca.

Catatan

Beberapa aplikasi mengandalkan perilaku penguncian dan pemblokiran isolasi yang diterapkan baca. Untuk aplikasi ini, beberapa perubahan diperlukan sebelum opsi ini dapat diaktifkan.

Isolasi rekam jepret juga menggunakan penerapan versi baris, yang tidak menggunakan kunci bersama selama operasi baca. Sebelum transaksi dapat berjalan di bawah isolasi rekam jepret, ALLOW_SNAPSHOT_ISOLATION opsi database harus diatur AKTIF.

Terapkan tingkat isolasi ini untuk meminimalkan kebuntuan yang dapat terjadi antara operasi baca dan tulis.

Gunakan koneksi terikat

Dengan menggunakan koneksi terikat, dua koneksi atau lebih yang dibuka oleh aplikasi yang sama dapat bekerja sama satu sama lain. Setiap kunci yang diperoleh oleh koneksi sekunder ditahan seolah-olah mereka diperoleh oleh koneksi utama, dan sebaliknya. Oleh karena itu mereka tidak saling memblokir.

Partisi kunci

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 partisi kunci:

  • 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, ketidakcocokan 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 dicatat dalam 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 dalam urutan ID partisi. Kunci pada sumber daya yang dipartisi ini 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 dengan 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 Niat bersama (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 Niat bersama (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 diterapkan, yang menyediakan rekam jepret tingkat pernyataan menggunakan penerapan versi baris. SQL Server Database Engine 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 dalam 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 Kumpulan 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 diberi versi.
  • Mendukung operasi indeks yang menentukan opsi ONLINE.
  • Mendukung tingkat isolasi transaksi berbasis penerapan versi baris:
    • Implementasi baru tingkat isolasi berkomitmen baca 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 diperlukan tidak lagi ada. Ini mempengaruhi operasi seperti pemicu, MARS, dan pengindeksan online.

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

  1. Atur READ_COMMITTED_SNAPSHOT salah satu atau 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 yang diterapkan baca menggunakan penerapan versi baris.
    • ALLOW_SNAPSHOT_ISOLATION Saat opsi database AKTIF, transaksi dapat mengatur tingkat isolasi rekam jepret.

Saat salah satu READ_COMMITTED_SNAPSHOT atau ALLOW_SNAPSHOT_ISOLATION opsi 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 per satu setiap kali ditetapkan.

READ_COMMITTED_SNAPSHOT Saat opsi database atau ALLOW_SNAPSHOT_ISOLATION AKTIF, salinan logis (versi) dipertahankan untuk semua modifikasi data yang dilakukan dalam database. Setiap kali baris dimodifikasi oleh transaksi tertentu, instans SQL Server Database Engine menyimpan versi gambar baris yang diterapkan sebelumnya di tempdb. Setiap versi ditandai dengan nomor urutan transaksi transaksi yang membuat perubahan. Versi baris yang dimodifikasi dirangkai menggunakan daftar tautan. Nilai baris terbaru selalu disimpan dalam database saat ini dan dirangkai 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 SQL Server Database 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 jangka pendek, 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 dibaca, dan oleh karena itu tidak 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:

  • Paling dekat 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. 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 hingga transaksi selesai.

Perilaku saat memodifikasi data

Dalam transaksi berkomitmen baca menggunakan penerapan versi baris, pemilihan baris yang akan diperbarui dilakukan menggunakan pemindaian pemblokiran di mana kunci pembaruan (U) diambil 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 sampai data akan dimodifikasi. Ketika 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 berkomitmen baca 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.

Perilaku dalam ringkasan

Tabel berikut ini meringkas perbedaan antara isolasi rekam jepret dan isolasi yang diterapkan baca menggunakan penerapan versi baris.

Properti Tingkat isolasi berkomitmen 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. Kembali 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. 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.
Perbarui deteksi konflik. Tidak ada. 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 dalam tempdb bahkan ketika tidak ada transaksi aktif menggunakan isolasi berbasis penerapan versi baris. Data setelah modifikasi menyertakan penunjuk ke data versi yang disimpan dalam tempdb. Untuk objek besar, hanya sebagian 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 ruang yang cukup untuk mendukung penyimpanan versi. Ada dua penyimpanan versi dalam 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 dalam 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 sampai transaksi selesai.

Ketika tempdb kehabisan ruang, mesin database SQL Server memaksa penyimpanan versi untuk 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 dalam tempdb. Jika tidak, tempdb kehabisan ruang dan hal berikut 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 pembatalan penuh tempdb 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 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 opsinya NONAKTIF.
  • Pemicu tidak ada lagi 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 baris 14 byte per 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, text, dan 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 SQL Server Database Engine yang lebih lama menyimpan hingga 8080 byte ntext, text, atau image data per fragmen.

ntextData objek besar (LOB) , text, dan image yang ada tidak diperbarui untuk membuat ruang untuk informasi penerapan versi baris saat database ditingkatkan ke SQL Server dari versi SQL Server sebelumnya. 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 meskipun hanya satu byte yang dimodifikasi. Ini adalah operasi satu kali untuk setiap ntextkolom , text, atau 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 dicatat sepenuhnya. Operasi WRITETEXT dan UPDATETEXT dicatat minimal jika mode pemulihan database tidak diatur ke PENUH.

Jenis nvarchar(max)data , varchar(max), dan varbinary(max) tidak tersedia di versi SQL Server sebelumnya. 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 versi baris, penyimpanan versi, dan proses 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 sistem tempdb 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 dealokasi menurut 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 dealokasi 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 dengan 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

    sys.dm_tran_top_version_generators dan sys.dm_tran_version_store adalah fungsi yang berpotensi sangat mahal untuk dijalankan, karena keduanya mengkueri seluruh penyimpanan versi, yang bisa sangat besar.
    sys.dm_tran_version_store_space_usage efisien dan tidak mahal untuk dijalankan, karena tidak menavigasi melalui rekaman penyimpanan versi individual dan 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. sys.dm_tran_current_snapshot mirip dengan sys.dm_tran_transactions_snapshot, kecuali hanya mengembalikan transaksi aktif untuk rekam jepret saat ini. Untuk informasi selengkapnya, lihat sys.dm_tran_current_snapshot (Transact-SQL).

Penghitung kinerja

SQL Server penghitung kinerja memberikan informasi tentang performa sistem yang terkena dampak 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 di tempdb (KB). Memantau jumlah, dalam kilobyte (KB), ruang kosong dalam database tempdb. Harus ada cukup ruang kosong dalam tempdb untuk 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 dari semua penyimpanan versi. Informasi ini membantu menentukan jumlah ruang yang diperlukan dalam database tempdb untuk penyimpanan versi. Memantau penghitung ini selama periode waktu tertentu memberikan perkiraan yang berguna tentang ruang tambahan yang diperlukan untuk tempdb.

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

  • Tingkat 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 tingkat Pembersihan Versi (KB/dtk) dapat digunakan untuk memprediksi persyaratan ruang tempdb.

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

  • Pemotokan unit Penyimpanan Versi. Memantau jumlah total unit penyimpanan versi yang terpotok sejak instans dimulai. Unit penyimpanan versi dipotok saat 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 hitungan 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.

  • Memperbarui 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 Perbarui Transaksi Rekam Jepret dan Transaksi Versi NonSnapshot menunjukkan 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 AdventureWorks2019;  
GO  
  
-- Enable snapshot isolation on the database.  
ALTER DATABASE AdventureWorks2019  
    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 AdventureWorks2019;  
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 versi 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 AdventureWorks2019;  -- 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 AdventureWorks2019  
-- database.  
ALTER DATABASE AdventureWorks2019  
    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 AdventureWorks2019;  
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 AdventureWorks2019  
    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 sampai 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 sepenuhnya AKTIF. Database melewati status PENDING_OFF saat administrator database mengatur opsi ke ALLOW_SNAPSHOT_ISOLATION NONAKTIF.

Pernyataan Transact-SQL berikut akan mengaktifkan ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2019  
    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 diselesaikan.

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 dimulai setelah waktu ini tidak dapat mengakses database ini. Transaksi pembaruan 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 ON 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 database master dan msdb, dan tidak dapat dinonaktifkan.

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

Menggunakan tingkat isolasi berbasis penerapan versi baris

Kerangka kerja penerapan versi baris selalu diaktifkan dalam 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:

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

    ALTER DATABASE AdventureWorks2019  
        SET READ_COMMITTED_SNAPSHOT ON;  
    

    Ketika database diaktifkan untuk READ_COMMITTED_SNAPSHOT, semua kueri yang berjalan di bawah tingkat isolasi yang diterapkan baca 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 diperlihatkan dalam contoh kode berikut:

    ALTER DATABASE AdventureWorks2019  
        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 di mana 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 read-committed. 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 dalam tempdb. Saat mengakses tabel sementara global di dalam transaksi rekam jepret, salah satu hal berikut ini 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 sed 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 otomatis terpasang dan terbuka, atau database melepaskan dan melampirkan.
  • Transaksi terdistribusi, termasuk kueri dalam database terdistribusi yang dipartisi, 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 AdventureWorks2019;  
    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 berkomitmen 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 Microsoft SQL Server Database Engine 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

Dalam SQL Server, gunakan tampilan manajemen dinamis sys.dm_os_waiting_tasks untuk menentukan apakah proses sedang diblokir dan siapa yang memblokirnya. Dalam versi SQL Server yang lebih lama, gunakan prosedur tersimpan sistem sp_who.

Pengaturan memungkinkan LOCK_TIMEOUT aplikasi untuk mengatur waktu maksimum pernyataan menunggu 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 penangan 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 bergantung pada pernyataan yang tidak pernah dijalankan.

Menerapkan penangan 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 mengembalikan 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 namespace layanan terkelola System.Data.SqlClient dapat menentukan opsi IsolationLevel dengan menggunakan metode SqlConnection.BeginTransaction.
  • 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 properti DBPROPSET_SESSION DBPROP_SESS_AUTOCOMMITISOLEVELS ke tingkat isolasi transaksi yang diinginkan.
  • Aplikasi yang menggunakan ODBC dapat mengatur atribut SQL_COPT_SS_TXN_ISOLATION 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 menetapkan SERIALIZABLE tingkat isolasi:

USE AdventureWorks2019;  
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 yang tidak diterapkan baca 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. Tataan hasil dapat bervariasi dari hasil yang ditetapkan pada sistem Anda.

USE AdventureWorks2019;  
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 instans yang digunakan 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 tersebut.

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. Kami menyarankan agar petunjuk penguncian tingkat tabel digunakan untuk mengubah perilaku penguncian default hanya jika perlu. 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 permintaan terkunci 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 yang biasanya digunakan untuk mempertahankan transaksi yang dapat diserialisasi tidak diambil.

USE AdventureWorks2019;  
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 diambil yang mereferensikan HumanResources.Employee adalah kunci stabilitas skema (Sch-S). Dalam hal ini, serialisasi tidak lagi dijamin.

Dalam SQL Server, LOCK_ESCALATION opsi ALTER TABLE untuk dapat membantah 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 jika tabel atau pola akses indeks dipahami dengan baik dan konsisten, dan ada masalah ketidakcocokan sumber daya untuk diselesaikan. Mengesampingkan 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 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 bersamaan. 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 tugas 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.

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 dan PRIMARY KEYUNIQUE 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 bersarang

Transaksi eksplisit dapat disarangkan. 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 proses apa pun 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 diambil untuk transaksi luar. Jika TransProc dijalankan oleh proses yang tidak memiliki transaksi yang luar biasa, COMMIT TRANSACTION di akhir prosedur secara efektif melakukan 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 digulung balik berdasarkan tindakan yang diambil pada akhir transaksi terluar. Jika transaksi luar dilakukan, transaksi berlapis dalam juga dilakukan. Jika transaksi luar digulung balik, 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, pernyataan COMMIT hanya berlaku untuk transaksi berlapis terakhir, yang merupakan transaksi terdalu. Bahkan jika COMMIT TRANSACTION pernyataan transaction_name dalam transaksi berlapis mengacu pada nama transaksi transaksi luar, penerapan hanya berlaku untuk transaksi terdahulu.

Tidak sah bagi parameter ROLLBACK TRANSACTIONtransaction_name pernyataan untuk merujuk pada 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 mana pun dari serangkaian transaksi berlapis, semua transaksi berlapis akan digulung balik. ROLLBACK WORK Jika pernyataan atau ROLLBACK TRANSACTION tanpa parameter transaction_name dijalankan pada tingkat mana pun dari serangkaian transaksi berlapis, itu mengembalikan semua transaksi berlapis, termasuk transaksi terluar.

Fungsi ini @@TRANCOUNT merekam tingkat bersarang transaksi saat ini. Setiap BEGIN TRANSACTION pernyataan bertambah @@TRANCOUNT satu per satu. Masing-masing COMMIT TRANSACTION atau COMMIT WORK pernyataan turun @@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 serangkaian transaksi berlapis mengembalikan semua transaksi berlapis dan penurunan @@TRANCOUNT 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 berada 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 bekerja pada 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 ikatan 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 ikatan 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 lagi 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 hingga seluruh transaksi dilakukan atau digulung balik dengan menggunakan Koordinator Transaksi Terdistribusi Microsoft (MS DTC).

Sesi terikat terdistribusi tidak diidentifikasi oleh token ikatan string karakter; 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.

Dalam 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 bergabung dengan 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 dengan hati-hati ke database. Karena kedua sesi memiliki kunci yang sama, kedua program tidak boleh mencoba memodifikasi data yang sama secara bersamaan. Kapan saja, hanya satu sesi yang dapat melakukan pekerjaan sebagai bagian dari transaksi; tidak boleh 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.

Mengoding transaksi yang efisien

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 hingga 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. Lihat artikel Durabilitas Transaksi untuk informasi selengkapnya.

Panduan pengkodian

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 dipegang 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 jam.

  • Jangan buka transaksi saat menelusuri data, jika memungkinkan.
    Transaksi tidak boleh dimulai sampai semua analisis data awal telah 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.

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

  • Manfaatkan opsi konkurensi kursor yang lebih rendah, seperti opsi konkurensi optimis.
    Dalam sistem dengan probabilitas pembaruan bersamaan yang rendah, overhead berurusan dengan kesalahan "orang lain mengubah data Anda setelah Anda membacanya" bisa jauh lebih rendah daripada overhead selalu mengunci baris 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 HOLDLOCK atau tingkat isolasi SERIALIZABLE dapat menyebabkan proses menunggu bahkan pada kunci bersama dan mengurangi konkurensi

  • Hindari menggunakan transaksi implisit ketika kemungkinan transaksi Implisit dapat menimbulkan perilaku yang tidak dapat diprediksi karena sifatnya. Lihat Transaksi Implisit dan masalah 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 usesys.dm_db_index_physical_stats.

Transaksi implisit dan menghindari masalah konkurensi dan sumber daya

Untuk mencegah masalah konkurensi dan 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 SQL Server Database 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 jangka panjang

Transaksi yang berjalan lama adalah transaksi aktif yang belum dilakukan atau digulung balik transaksi secara 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 sementara 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. 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 tersebut menahan 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 SQL Server Arsitektur dan Panduan Manajemen Log Transaksi, Memecahkan Masalah Log Transaksi Penuh (SQL Server Kesalahan 9002), dan Log Transaksi (SQL Server).

Menemukan transaksi jangka panjang

Untuk mencari transaksi yang berjalan lama, gunakan salah satu hal berikut ini:

  • sys.dm_tran_database_transactions

    Tampilan manajemen dinamis ini mengembalikan informasi terkait transaksi di tingkat database. Untuk transaksi yang berjalan lama, kolom minat tertentu mencakup waktu rekaman log pertama (database_transaction_begin_time), status transaksi saat ini (database_transaction_state), dan nomor urutan log (LSN) dari catatan awal dalam 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. Namun, gunakan pernyataan ini dengan sangat hati-hati, terutama ketika proses penting berjalan. Untuk informasi selengkapnya, lihat Pernyataan KILL (T-SQL).

Langkah berikutnya

Overhead Penerapan Versi Baris
Aktivitas yang Diperluas
sys.dm_tran_locks (T-SQL)
Tampilan dan Fungsi Manajemen Dinamis (Transact-SQL)
Tampilan dan Fungsi Manajemen Dinamis Terkait Transaksi (Transact-SQL)