Panduan penguncian transaksi dan penerapan versi baris

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

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

Catatan

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

• Untuk informasi selengkapnya dan untuk mempelajari tempat penguncian yang dioptimalkan tersedia, lihat Penguncian yang dioptimalkan.
• Untuk menentukan apakah penguncian yang dioptimalkan diaktifkan pada database Anda, lihat Apakah penguncian yang dioptimalkan diaktifkan?

Penguncian yang dioptimalkan telah memperkenalkan perubahan signifikan pada beberapa bagian artikel ini, termasuk:

• Mengunci Mesin Database
• Hapus operasi
• Operasi sisipkan
• Eskalasi kunci
• Mengurangi penguncian dan eskalasi
• Perilaku saat memodifikasi data
• Perilaku dalam ringkasan
• Petunjuk penguncian

Dasar-dasar transaksi

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

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

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

Catatan

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

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

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

Aplikasi bertanggung jawab untuk memulai dan mengakhiri transaksi di titik-titik yang memberlakukan konsistensi logis data. Aplikasi harus menentukan urutan modifikasi data yang meninggalkan data dalam keadaan konsisten relatif terhadap aturan bisnis organisasi. Aplikasi melakukan modifikasi ini dalam satu transaksi sehingga Mesin Database dapat menegakkan integritas transaksi.

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

• Fasilitas penguncian yang mempertahankan isolasi transaksi.

• Fasilitas pengelogan untuk 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 itu sendiri gagal, instans menggunakan log transaksi saat memulai ulang untuk secara otomatis mengembalikan transaksi yang tidak lengkap ke titik kegagalan sistem. Transaksi tahan lama tertunda dilakukan sebelum catatan log transaksi diperkuat ke disk. Transaksi tersebut dapat hilang jika ada kegagalan sistem sebelum catatan log diperkuat ke disk. Untuk informasi selengkapnya tentang durabilitas transaksi yang tertunda, lihat artikel Mengontrol Durabilitas Transaksi.

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

Mengontrol transaksi

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

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

Memulai transaksi

Dengan menggunakan fungsi API dan pernyataan Transact-SQL, Anda dapat memulai transaksi 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, yang mungkin merupakan mode implisit atau autocommit.

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

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• 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

UPDATE STATISTICS 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. 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 beroperasi dalam mode autocommit setiap kali mode default ini belum ditimpa oleh transaksi eksplisit atau implisit. Mode autocommit juga merupakan mode default untuk SqlClient, ADO, OLE DB, dan ODBC.

Transaksi Implisit

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

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

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

Transaksi cakupan batch

Hanya berlaku untuk beberapa set hasil aktif (MARS), transaksi eksplisit atau implisit Transact-SQL yang dimulai di bawah sesi MARS menjadi transaksi cakupan batch. Transaksi cakupan batch yang tidak diterapkan atau digulung balik ketika batch selesai secara otomatis digulung balik oleh Mesin Database.

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 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 yang mencakup dua database atau lebih adalah transaksi terdistribusi. Instans mengelola transaksi terdistribusi secara internal; kepada pengguna, ia beroperasi sebagai transaksi lokal.

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

• 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 log transaksi untuk transaksi disiram ke disk. Ketika setiap manajer sumber daya menyelesaikan fase persiapan, ia mengembalikan keberhasilan atau kegagalan persiapan ke manajer transaksi. SQL Server 2014 (12.x) memperkenalkan durabilitas transaksi yang tertunda. Transaksi tahan lama yang tertunda dilakukan sebelum buffer log transaksi pada setiap manajer sumber daya dibersihkan ke disk. Untuk informasi selengkapnya tentang durabilitas transaksi yang tertunda, lihat artikel Mengontrol Durabilitas Transaksi.

• Fase Penerapan

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

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

Akhiri transaksi

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

• Melakukan

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

• Gulung balik

  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. Roll back juga membebaskan sumber daya yang dipegang oleh transaksi.

Catatan

Pada beberapa sesi kumpulan hasil aktif (MARS), transaksi eksplisit yang dimulai melalui fungsi API tidak dapat dilakukan saat ada permintaan eksekusi yang tertunda. Setiap upaya untuk melakukan jenis transaksi ini saat ada permintaan yang dijalankan akan mengakibatkan kesalahan.

Kesalahan selama pemrosesan transaksi

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

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

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

Mengompilasi dan menjalankan kesalahan dalam mode autocommit

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

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

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

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

Mesin Database menggunakan resolusi nama yang ditangguhkan, di mana nama objek diselesaikan pada waktu eksekusi, bukan pada waktu kompilasi. 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 menggunakan mekanisme berikut untuk memastikan integritas transaksi dan mempertahankan konsistensi database ketika beberapa pengguna mengakses data secara bersamaan:

• Penguncian

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

• Penerapan versi baris

  Saat tingkat isolasi berbasis penerapan versi baris digunakan, Mesin Database mempertahankan versi setiap baris yang dimodifikasi. Aplikasi dapat menentukan bahwa transaksi menggunakan versi baris untuk melihat data seperti yang ada di awal transaksi atau pernyataan, 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 lebih menyesuaikan perilaku agar sesuai dengan persyaratan aplikasi.

Mengelola akses data bersamaan

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

Efek konkurensi

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

• Pembaruan hilang

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

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

• Dependensi yang tidak dikomit (baca kotor)

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

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

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

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

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

• Bacaan phantom

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

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

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

• Pembacaan hilang dan ganda yang disebabkan oleh pembaruan baris

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

    Transaksi yang berjalan pada READ UNCOMMITTED tingkat (atau pernyataan menggunakan NOLOCK petunjuk tabel) tidak mengeluarkan kunci bersama untuk mencegah transaksi lain memodifikasi data yang dibaca oleh transaksi saat ini. Transaksi yang berjalan di READ COMMITTED tingkat mengeluarkan kunci bersama, tetapi kunci baris atau halaman dilepaskan 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 dibaca sama sekali 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 terjadi ketika Anda menggunakan READ COMMITTED tingkat isolasi.

Jenis konkurensi

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

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

• Kontrol konkurensi pesimis

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

• Kontrol konkurensi optimis

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

Mesin Database mendukung kedua metode 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

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

Kontrol tingkat isolasi transaksi:

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

Penting

Memilih tingkat isolasi transaksi tidak memengaruhi kunci yang diperoleh untuk melindungi modifikasi data. Transaksi selalu memegang kunci eksklusif untuk melakukan modifikasi data, dan menahan kunci itu hingga 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 transaksi untuk mengakses data pada saat yang sama, tetapi juga meningkatkan jumlah efek konkurensi (seperti pembacaan kotor atau pembaruan yang hilang) yang mungkin dihadapi transaksi. Sebaliknya, tingkat isolasi yang lebih tinggi mengurangi jenis efek konkurensi yang mungkin dihadapi transaksi, tetapi membutuhkan lebih banyak sumber daya sistem dan meningkatkan kemungkinan satu transaksi akan memblokir transaksi lain. Memilih tingkat isolasi yang sesuai tergantung pada penyeimbangan persyaratan integritas data aplikasi terhadap overhead setiap tingkat isolasi. Tingkat isolasi tertinggi, SERIALIZABLE, 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 transaksi lain dalam sistem multi-pengguna. Tingkat isolasi terendah, READ UNCOMMITTED, dapat mengambil data yang telah dimodifikasi tetapi tidak dilakukan oleh transaksi lain. Semua efek samping konkurensi dapat terjadi di READ UNCOMMITTED, 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:

Tingkat Isolasi	Definisi
READ UNCOMMITTED	Tingkat isolasi terendah di mana transaksi hanya cukup terisolasi untuk memastikan bahwa data yang tidak konsisten secara fisik tidak dibaca. Dalam tingkat ini, bacaan kotor diizinkan, sehingga satu transaksi mungkin melihat perubahan yang belum diterapkan yang dilakukan oleh transaksi lain.
READ COMMITTED	Memungkinkan transaksi membaca data yang sebelumnya dibaca (tidak dimodifikasi) oleh transaksi lain tanpa menunggu transaksi pertama selesai. Mesin Database menyimpan kunci tulis (diperoleh pada data yang dipilih) hingga akhir transaksi, tetapi kunci baca dirilis segera setelah operasi baca dilakukan. Ini adalah tingkat default Mesin Database.
REPEATABLE READ	Mesin Database terus membaca dan menulis kunci yang diperoleh pada data yang dipilih hingga akhir transaksi. Namun, karena penguncian rentang tidak dikelola, pembacaan phantom dapat terjadi.
SERIALIZABLE	Tingkat tertinggi di mana transaksi sepenuhnya terisolasi satu sama lain. Mesin Database terus membaca dan menulis kunci yang diperoleh pada data yang dipilih hingga akhir transaksi. Kunci rentang diperoleh saat operasi SELECT menggunakan klausa WHERE rentang untuk menghindari pembacaan phantom. Catatan: Operasi dan transaksi DDL pada tabel yang direplikasi mungkin gagal ketika SERIALIZABLE tingkat isolasi diminta. Ini karena kueri replikasi menggunakan petunjuk yang mungkin tidak kompatibel dengan SERIALIZABLE tingkat isolasi.

Mesin Database juga mendukung dua tingkat isolasi transaksi tambahan yang menggunakan penerapan versi baris. Salah satunya adalah implementasi READ COMMITTED tingkat isolasi, dan satu adalah SNAPSHOT tingkat isolasi transaksi.

Tingkat Isolasi Penerapan Versi Baris	Definisi
Read Committed Snapshot (RCSI)	READ_COMMITTED_SNAPSHOT Saat opsi database diatur ON, yang merupakan pengaturan default di Azure SQL Database, READ COMMITTED tingkat isolasi menggunakan penerapan versi baris untuk memberikan konsistensi baca tingkat pernyataan. Operasi baca hanya memerlukan stabilitas skema (Sch-S) kunci tingkat tabel dan tidak ada kunci halaman atau baris. Artinya, Mesin Database menggunakan penerapan versi baris untuk menyajikan setiap pernyataan dengan rekam jepret data yang konsisten secara transaksional seperti yang ada di awal pernyataan. Kunci tidak digunakan untuk melindungi data dari pembaruan oleh transaksi lain. Fungsi yang ditentukan pengguna dapat mengembalikan data yang diterapkan setelah waktu pernyataan yang berisi UDF dimulai. READ_COMMITTED_SNAPSHOT Ketika opsi database diatur OFF, yang merupakan pengaturan default di SQL Server dan Azure SQL Managed Instance, READ COMMITTED isolasi menggunakan kunci bersama untuk mencegah transaksi lain memodifikasi baris saat transaksi saat ini menjalankan operasi baca. Kunci bersama juga memblokir pernyataan dari baris baca yang dimodifikasi oleh transaksi lain hingga transaksi lain selesai. Kedua implementasi memenuhi definisi ISO isolasi READ COMMITTED .
SNAPSHOT	Tingkat isolasi rekam jepret menggunakan penerapan versi baris untuk memberikan konsistensi baca tingkat transaksi. Operasi baca tidak memperoleh kunci halaman atau baris; hanya kunci tabel stabilitas skema (Sch-S) yang diperoleh. Saat membaca baris yang dimodifikasi oleh transaksi lain, operasi baca mengambil versi baris yang ada saat transaksi dimulai. Anda hanya dapat menggunakan SNAPSHOT isolasi saat ALLOW_SNAPSHOT_ISOLATION opsi database diatur ke ON. Secara default, opsi ini diatur ke OFF untuk database pengguna di SQL Server dan Azure SQL Managed Instance, dan diatur ke ON untuk database di Azure SQL Database. Catatan: Mesin Database 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: , , , CREATE INDEXDROP INDEXDBCC REINDEXCREATE XML INDEXALTER INDEX, ALTER PARTITION FUNCTION, , ALTER PARTITION SCHEME, atau pernyataan DDL runtime bahasa umum (CLR). ALTER TABLEBEGIN TRANSACTION Pernyataan ini diizinkan ketika Anda menggunakan isolasi snapshot dalam transaksi implisit. Transaksi implisit, menurut definisi, adalah satu pernyataan yang memungkinkan untuk memberlakukan semantik isolasi rekam jepret, bahkan dengan pernyataan DDL. Pelanggaran prinsip ini dapat menyebabkan kesalahan 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

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

Tingkat isolasi	Pembacaan kotor	Bacaan yang tidak dapat dipesan	Hantu
READ UNCOMMITTED	Ya	Ya	Ya
READ COMMITTED	No	Ya	Ya
REPEATABLE READ	No	No	Ya
SNAPSHOT	No	No	No
SERIALIZABLE	No	No	Tidak

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

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

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

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

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

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

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

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

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

Mengunci Mesin Database

Penguncian adalah mekanisme yang digunakan oleh Mesin Database 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 (S) atau eksklusif (X). 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, Mesin Database akan menjeda transaksi yang meminta hingga kunci pertama dirilis.

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

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

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

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

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

Mengunci granularitas dan hierarki

Mesin Database memiliki penguncian multigranular yang memungkinkan berbagai jenis sumber daya dikunci oleh transaksi. Untuk meminimalkan biaya penguncian, Mesin Database 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 sering kali harus memperoleh kunci pada beberapa tingkat granularitas untuk sepenuhnya melindungi sumber daya. Kelompok kunci ini pada beberapa tingkat granularitas disebut hierarki kunci. Misalnya, untuk sepenuhnya melindungi pembacaan indeks, instans Mesin Database mungkin harus memperoleh kunci bersama pada baris dan kunci bersama niat pada halaman dan tabel.

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

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

¹ HoBT dan TABLE kunci dapat dipengaruhi oleh LOCK_ESCALATION opsi ALTER TABLE.

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

Mode kunci

Mesin Database 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.

Mode kunci	Deskripsi
Bersama (S)	Digunakan untuk operasi baca yang tidak mengubah atau memperbarui data, seperti SELECT pernyataan.
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, , UPDATEatau DELETE. Memastikan bahwa beberapa pembaruan tidak dapat dilakukan ke sumber daya yang sama secara bersamaan.
Niat	Digunakan untuk membuat hierarki kunci. Jenis kunci niat adalah: niat bersama (IS), niat eksklusif (IX), dan dibagikan dengan niat eksklusif (SIX).
Skema	Digunakan saat operasi bergantung pada skema tabel yang dijalankan. Jenis kunci skema adalah: modifikasi skema (Sch-M) dan stabilitas skema (Sch-S).
Pembaruan Massal (BU)	Digunakan saat menyalin data secara massal ke dalam tabel dengan TABLOCK petunjuk.
Rentang kunci	Melindungi rentang baris yang dibaca oleh kueri saat menggunakan SERIALIZABLE tingkat isolasi transaksi. Memastikan bahwa transaksi lain tidak dapat menyisipkan baris yang akan memenuhi syarat untuk kueri SERIALIZABLE transaksi jika kueri dijalankan lagi.

Kunci bersama

Kunci bersama (S) memungkinkan transaksi bersamaan untuk membaca 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 REPEATABLE READ atau lebih tinggi, atau petunjuk penguncian digunakan untuk mempertahankan kunci bersama (S) selama durasi transaksi.

Memperbarui kunci

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

Kunci pembaruan (U) juga dapat diambil oleh pernyataan selain UPDATE, ketika petunjuk tabel UPDLOCK ditentukan dalam pernyataan.

• Beberapa aplikasi menggunakan pola "pilih baris, lalu perbarui baris", di mana baca dan tulis dipisahkan secara eksplisit dalam transaksi. Dalam hal ini, jika tingkat isolasi adalah REPEATABLE READ atau SERIALIZABLE, pembaruan bersamaan dapat menyebabkan kebuntuan, sebagai berikut:

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

  Dalam tingkat isolasi default READ COMMITTED , S kunci ber durasi pendek, dirilis segera setelah digunakan. Meskipun kebuntuan yang dijelaskan di atas masih mungkin terjadi, kemungkinannya jauh lebih kecil dengan kunci durasi pendek.

  Untuk menghindari kebuntuan jenis ini, aplikasi dapat mengikuti pola "pilih baris dengan UPDLOCK petunjuk, lalu perbarui baris".

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

Kunci eksklusif

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

Pernyataan modifikasi data, seperti INSERT, UPDATE, dan DELETE menggabungkan operasi baca dan modifikasi. 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, UPDATE pernyataan mungkin mengubah baris dalam satu tabel berdasarkan gabungan dengan tabel lain. Dalam hal ini, UPDATE pernyataan meminta kunci bersama pada baris yang dibaca dalam tabel gabungan selain meminta kunci eksklusif pada baris yang diperbarui.

Kunci niat

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

Kunci niat melayani dua tujuan:

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

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

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

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

Kunci skema

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

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

Mesin Database menggunakan kunci stabilitas skema (Sch-S) saat mengkompilasi dan mengeksekusi kueri. Sch-S kunci tidak memblokir kunci transaksi, termasuk kunci eksklusif (X). Oleh karena itu, transaksi lain, termasuk yang memiliki X kunci pada tabel, terus berjalan saat kueri sedang dikompilasi. Namun, operasi DDL bersamaan, dan operasi DML bersamaan yang memperoleh Sch-M kunci, diblokir oleh Sch-S kunci.

Kunci pembaruan massal

Penguncian 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 menggunakan kunci pembaruan massal (BU) saat kedua kondisi berikut ini benar.

• Anda menggunakan pernyataan Transact-SQL BULK INSERT , atau OPENROWSET(BULK) fungsi , atau Anda menggunakan salah satu perintah INSERT API Massal seperti .NET SqlBulkCopy, OLEDB Fast Load API, atau ODBC Bulk Copy API untuk menyalin data secara massal ke dalam tabel.
• Petunjuk TABLOCK ditentukan atau table lock on bulk load opsi tabel diatur menggunakan sp_tableoption.

Tip

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

Kunci rentang kunci

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

Kompatibilitas kunci

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

Tabel berikut menunjukkan kompatibilitas mode kunci yang paling umum ditemui.

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

Catatan

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

Gunakan tabel berikut untuk menentukan kompatibilitas semua mode kunci yang tersedia di Mesin Database.

Kunci	Deskripsi
N	Tidak ada konflik
I	Ilegal
C	Konflik
NL	Tidak ada kunci
SCH-S	Kunci stabilitas skema
SCH-M	Kunci modifikasi skema
S	Bersama
U	Pembaruan
X	Eksklusif
IS	Niat dibagikan
IU	Pembaruan niat
IX	Niat eksklusif
SIU	Berbagi dengan pembaruan niat
ENAM	Berbagi dengan niat eksklusif
UIX	Perbarui dengan niat eksklusif
BU	Pembaruan massal
RS-S	Berbagi rentang bersama
RS-U	Pembaruan rentang bersama
RI-N	Sisipkan rentang-null
RI-S	Sisipkan berbagi rentang
RI-U	Sisipkan pembaruan rentang
RI-X	Sisipkan rentang-eksklusif
RX-S	Berbagi rentang eksklusif
RX-U	Pembaruan rentang eksklusif
RX-X	Eksklusif-rentang eksklusif

Penguncian rentang kunci

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

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

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

Mode kunci rentang kunci

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

• Rentang mewakili mode kunci yang melindungi rentang antara dua entri indeks berturut-turut.
• Baris mewakili mode kunci yang melindungi entri indeks.
• Mode mewakili mode kunci gabungan yang digunakan. Mode kunci rentang kunci terdiri dari dua bagian. Yang pertama mewakili jenis kunci yang digunakan untuk mengunci rentang indeks (RentangT) dan yang kedua mewakili jenis kunci yang digunakan untuk mengunci kunci tertentu (K). Dua bagian terhubung dengan tanda hubung (-), seperti RentangT-K.

Rentang	Baris	Mode	Deskripsi
RangeS	S	RangeS-S	Rentang bersama, kunci sumber daya bersama; SERIALIZABLE pemindaian rentang.
RangeS	U	RangeS-U	Rentang bersama, perbarui kunci sumber daya; SERIALIZABLE perbarui pemindaian.
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 internal Null 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	RangeS-S	RangeS-U	RangeI-N	RangeX-X
Mode yang diminta
Bersama (S)	Ya	Ya	No	Ya	Ya	Ya	Tidak
Pembaruan (U)	Ya	No	No	Ya	No	Ya	Tidak
Eksklusif (X)	Tidak	No	No	No	No	Ya	No
RangeS-S	Ya	Ya	No	Ya	Ya	No	No
RangeS-U	Ya	No	No	Ya	No	No	No
RangeI-N	Ya	Ya	Ya	No	No	Ya	No
RangeX-X	No	No	No	No	No	No	Tidak

Penguncian konversi

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

Kunci 1	Kunci 2	Kunci konversi
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-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, WHERE klausul dalam SELECT pernyataan dapat menetapkan kondisi rentang dengan predikat ini: ColumnX BETWEEN N'AAA' AND N'CZZ'. Kunci rentang kunci hanya dapat diperoleh jika ColumnX dicakup oleh kunci indeks.

Contoh

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

Kueri pemindaian rentang

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

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

Kunci rentang kunci ditempatkan pada entri indeks yang sesuai dengan rentang baris 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 pada entri indeks ini memastikan bahwa tidak ada nama baru yang dimulai dengan huruf A dapat ditambahkan sebelum Adam, seperti AbigailRangeS-S . Demikian pula, RangeS-S kunci rentang kunci pada entri indeks untuk Dale memastikan bahwa tidak ada nama baru yang dimulai dengan huruf C dapat ditambahkan setelah Carlos, seperti Clive.

Catatan

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

Pengambilan singleton dari data yang tidak ada

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

SELECT name
FROM mytable
WHERE name = 'Bill';

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

Hapus operasi tanpa penguncian yang dioptimalkan

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

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 baris dengan nilai Bob yang sedang dihapus. Namun, setiap transaksi yang mencoba membaca, menyisipkan, atau menghapus baris yang cocok dengan nilai Bob akan diblokir hingga transaksi penghapusan dilakukan atau digulung balik. (Opsi READ_COMMITTED_SNAPSHOT database dan SNAPSHOT tingkat isolasi juga memungkinkan pembacaan dari versi baris dari status yang diterapkan sebelumnya.)

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

Hapus operasi dengan penguncian yang dioptimalkan

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

DELETE mytable
WHERE name = 'Bob';

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

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

Sisipkan operasi tanpa penguncian yang dioptimalkan

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

INSERT mytable VALUES ('Dan');

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

Sisipkan operasi dengan penguncian yang dioptimalkan

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

INSERT mytable VALUES ('Dan');

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

Eskalasi kunci

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

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

Eskalasi penguncian tanpa penguncian yang dioptimalkan

Saat Mesin Database memperoleh kunci tingkat rendah, Mesin Database juga menempatkan kunci niat pada objek yang berisi objek tingkat bawah:

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

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

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 (IS) niat ke kunci bersama (S). Jika upaya eskalasi kunci berhasil dan kunci tabel penuh diperoleh, maka semua kunci HoBT, halaman (PAGE), atau tingkat baris (RID, KEY) yang dipegang oleh transaksi pada tumpukan atau indeks dilepaskan. Jika kunci penuh tidak dapat diperoleh, tidak ada eskalasi kunci yang terjadi pada saat itu dan Mesin Database akan terus memperoleh baris, kunci, atau kunci halaman.

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

Catatan

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

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

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

Misalnya, asumsikan bahwa sesi melakukan operasi ini:

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

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

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

Eskalasi kunci dengan penguncian yang dioptimalkan

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

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

Kunci ambang eskalasi

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

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

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

Ambang eskalasi untuk pernyataan Transact-SQL

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

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

Ambang eskalasi untuk instans Mesin Database

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

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

• locks Jika opsi 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.

Eskalasi kunci dengan jenis kunci campuran

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

Misalnya, asumsikan sesi:

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

Pernyataan memperoleh UPDATE 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 memperoleh SELECT kunci ini:

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

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

Mengurangi penguncian dan eskalasi kunci

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

• Manfaatkan penguncian yang dioptimalkan.

  • Penguncian yang dioptimalkan menawarkan mekanisme penguncian transaksi yang ditingkatkan yang mengurangi konsumsi memori kunci dan pemblokiran untuk transaksi bersamaan. Eskalasi kunci jauh lebih kecil kemungkinannya terjadi ketika penguncian yang dioptimalkan diaktifkan.
  • Hindari menggunakan petunjuk tabel dengan penguncian yang dioptimalkan. Petunjuk tabel dapat mengurangi efektivitas penguncian yang dioptimalkan.
  • Aktifkan opsi READ_COMMITTED_SNAPSHOT untuk database untuk mendapatkan manfaat paling besar dari penguncian yang dioptimalkan. Ini adalah default di Azure SQL Database.
  • Penguncian yang dioptimalkan memerlukan pemulihan database terakselerasi (ADR) untuk diaktifkan pada database.

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

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

  • READ COMMITTED tingkat isolasi saat READ_COMMITTED_SNAPSHOT opsi database adalah ON.
  • SNAPSHOT tingkat isolasi.
  • READ UNCOMMITTED tingkat isolasi. Ini hanya dapat digunakan untuk sistem yang dapat beroperasi dengan bacaan kotor.

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

• Jika penguncian yang dioptimalkan tidak tersedia, untuk tabel yang dipartisi, gunakan LOCK_ESCALATION opsi ALTER TABLE untuk meningkatkan kunci ke partisi alih-alih tabel, atau untuk menonaktifkan eskalasi kunci untuk tabel.

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

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

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

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• Kurangi jejak kunci kueri dengan membuat kueri seefisien mungkin. Pemindaian besar atau sejumlah besar pencarian kunci dapat meningkatkan kemungkinan eskalasi kunci; selain itu, yang 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 indeks penuh atau pemindaian tabel dan untuk memaksimalkan efisiensi pencarian indeks. Pertimbangkan untuk menggunakan Konsultan Penyetelan Mesin Database untuk melakukan analisis indeks otomatis pada kueri. Untuk informasi selengkapnya, lihat Tutorial: Konsultan Penyetelan Mesin Database. Salah satu tujuan dari pengoptimalan ini adalah untuk membuat pencarian indeks mengembalikan baris sesedikempat mungkin untuk meminimalkan biaya pencarian kunci (memaksimalkan selektivitas indeks untuk kueri tertentu). Jika Mesin Database memperkirakan bahwa operator logis pencarian kunci dapat mengembalikan banyak baris, ia dapat menggunakan pengoptimalan prefetch untuk melakukan pencarian. Jika Mesin Database memang menggunakan prefetch untuk pencarian, mesin database harus meningkatkan tingkat isolasi transaksi dari sebagian kueri ke REPEATABLE READ. Ini berarti bahwa apa yang mungkin terlihat mirip dengan SELECT pernyataan pada READ COMMITTED tingkat isolasi 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, biasanya tidak terlihat pada tingkat isolasi default READ COMMITTED .

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

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

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

  Kueri ini memperoleh dan menahan IX kunci 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 TABLOCK petunjuk atau jika administrator telah menonaktifkan kunci halaman atau baris pada indeks pada mytable).

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

  Catatan

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

  Catatan

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

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

Memantau eskalasi kunci

Pantau eskalasi kunci dengan menggunakan peristiwa yang lock_escalation diperluas, 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

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.

Mesin Database menggunakan strategi penguncian dinamis untuk menentukan kunci yang paling efektif. Mesin Database 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 halaman dalam indeks saat melakukan pemindaian indeks.

Mengunci partisi

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

Memahami partisi kunci

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

• Spinlock

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

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

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

• Memori

  Ini digunakan untuk menyimpan struktur sumber daya kunci.

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

Menerapkan dan memantau partisi kunci

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

Saat memperoleh kunci pada sumber daya yang dipartisi:

• Hanya NLmode , Sch-S, IS, IU, dan IX kunci yang diperoleh pada satu partisi.

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

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

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

Bekerja dengan partisi kunci

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

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

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

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

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

Contoh A

Sesi 1:

Pernyataan SELECT dijalankan di bawah transaksi. HOLDLOCK Karena petunjuk kunci, pernyataan ini akan memperoleh dan mempertahankan kunci bersama Niat (IS) pada tabel (untuk ilustrasi ini, baris dan kunci halaman diabaikan). Kunci IS hanya akan diperoleh pada partisi yang ditetapkan untuk transaksi. Untuk contoh ini, diasumsikan bahwa IS kunci 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, 16 S kunci akan dikeluarkan di seluruh ID partisi kunci 0-15. S Karena kunci kompatibel dengan kunci yang IS 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 X kunci pada tabel. Namun, S kunci yang sedang dipegang oleh transaksi di sesi 2 akan memblokir X kunci pada ID partisi 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Contoh B

Sesi 1:

Pernyataan SELECT dijalankan di bawah transaksi. HOLDLOCK Karena petunjuk kunci, pernyataan ini akan memperoleh dan mempertahankan kunci bersama Niat (IS) pada tabel (untuk ilustrasi ini, baris dan kunci halaman diabaikan). Kunci IS hanya akan diperoleh pada partisi yang ditetapkan untuk transaksi. Untuk contoh ini, diasumsikan bahwa IS kunci 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 X kunci 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 IS kunci yang diperoleh pada ID partisi 6.

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

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Tingkat isolasi berbasis penerapan versi baris di Mesin Database

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

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

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

Versi baris disimpan di penyimpanan versi. Jika Pemulihan Database Dipercepat diaktifkan pada database, penyimpanan versi dibuat dalam database tersebut. Jika tidak, penyimpanan versi dibuat dalam tempdb database.

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

Ketika Pemulihan Database Dipercepat digunakan dan penyimpanan versi penuh, operasi baca terus berhasil tetapi operasi tulis yang menghasilkan versi, seperti UPDATE dan DELETE gagal. INSERT operasi terus berhasil jika database memiliki ruang yang cukup.

Menggunakan penerapan versi baris untuk READ COMMITTED transaksi dan SNAPSHOT adalah proses dua langkah:

1. Atur READ_COMMITTED_SNAPSHOT opsi database dan ALLOW_SNAPSHOT_ISOLATION ke ON.

2. Atur tingkat isolasi transaksi yang sesuai dalam aplikasi:

   • READ_COMMITTED_SNAPSHOT Ketika opsi database adalah ON, transaksi yang mengatur READ COMMITTED tingkat isolasi gunakan penerapan versi baris.
   • ALLOW_SNAPSHOT_ISOLATION Ketika opsi database adalah ON, transaksi dapat mengatur SNAPSHOT tingkat isolasi.

Saat opsi atau READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION database diatur ke ON, Mesin Database 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 BEGIN TRANSACTION pernyataan. Nomor urutan transaksi bertambah satu kali setiap kali ditetapkan.

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

Catatan

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

Versi baris ditahan cukup lama untuk memenuhi persyaratan transaksi yang berjalan di bawah tingkat isolasi berbasis penerapan versi baris. Mesin 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.

Ketika kedua opsi database diatur ke OFF, 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. Proses latar belakang menghapus versi baris kedaluarsa.

Catatan

Untuk transaksi yang berjalan singkat, versi baris yang dimodifikasi mungkin di-cache di kumpulan buffer tanpa ditulis ke penyimpanan versi. 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

Ketika transaksi yang berjalan di bawah data baca isolasi berbasis penerapan versi baris, operasi baca tidak memperoleh kunci bersama (S) pada data yang sedang dibaca, dan oleh karena itu jangan memblokir transaksi yang memodifikasi data. Selain itu, overhead sumber daya penguncian diminimalkan karena jumlah kunci yang diperoleh berkurang. READ COMMITTED isolasi menggunakan penerapan versi baris dan SNAPSHOT isolasi 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 stabilitas skema (Sch-S) selama kompilasi dan eksekusi. Karena itu, kueri diblokir ketika transaksi bersamaan memegang kunci modifikasi skema (Sch-M) pada tabel. Misalnya, operasi bahasa definisi data (DDL) memperoleh Sch-M kunci sebelum memodifikasi informasi skema tabel. Transaksi, termasuk yang berjalan di bawah tingkat isolasi berbasis penerapan versi baris, diblokir saat mencoba memperoleh Sch-S kunci. Sebaliknya, kueri yang memegang Sch-S kunci memblokir transaksi bersamaan yang mencoba memperoleh Sch-M kunci.

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

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

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

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

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

Catatan

Meskipun READ COMMITTED transaksi yang 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

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

Mengubah data tanpa penguncian yang dioptimalkan

Dalam transaksi menggunakan READ COMMITTED penerapan versi baris, pemilihan baris yang akan diperbarui dilakukan menggunakan pemindaian pemblokiran tempat kunci pembaruan (U) diperoleh pada baris data saat nilai data dibaca. Ini sama READ COMMITTED dengan transaksi 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 SNAPSHOT isolasi mengambil pendekatan optimis untuk modifikasi data dengan memperoleh kunci pada data sebelum melakukan modifikasi hanya untuk memberlakukan batasan. Jika tidak, kunci tidak diperoleh pada data hingga data akan dimodifikasi. Ketika baris data memenuhi kriteria pembaruan, SNAPSHOT transaksi memverifikasi bahwa baris data belum dimodifikasi oleh transaksi bersamaan yang dilakukan setelah SNAPSHOT transaksi dimulai. Jika baris data telah dimodifikasi di luar SNAPSHOT transaksi, konflik pembaruan terjadi dan SNAPSHOT transaksi dihentikan. Konflik pembaruan ditangani oleh Mesin Database dan tidak ada cara untuk menonaktifkan deteksi konflik pembaruan.

Catatan

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

Tabel dengan batasan kunci asing.

Tabel yang dirujuk dalam batasan kunci asing 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, SNAPSHOT transaksi mengalami konflik pembaruan dan dihentikan. Konflik pembaruan harus ditangani dan dicoba kembali oleh aplikasi.

Mengubah data dengan penguncian yang dioptimalkan

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

Mengaktifkan RCSI direkomendasikan untuk sebagian besar efisiensi dengan penguncian yang dioptimalkan. Saat menggunakan tingkat isolasi yang lebih ketat seperti REPEATABLE READ atau SERIALIZABLE, Mesin Database menyimpan baris dan kunci halaman hingga akhir transaksi, untuk pembaca dan penulis, menghasilkan peningkatan pemblokiran dan kunci memori.

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

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

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

Catatan

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

Perilaku dalam ringkasan

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

Properti	READ COMMITTED tingkat isolasi menggunakan penerapan versi baris	SNAPSHOT tingkat isolasi
Opsi database yang harus diatur ke ON untuk mengaktifkan dukungan yang diperlukan.	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
Bagaimana sesi meminta jenis penerapan versi baris tertentu.	Gunakan tingkat isolasi default READ COMMITTED , atau jalankan SET TRANSACTION ISOLATION LEVEL pernyataan untuk menentukan READ COMMITTED tingkat isolasi. Ini dapat dilakukan setelah transaksi dimulai.	Memerlukan eksekusi SET TRANSACTION ISOLATION LEVEL untuk menentukan SNAPSHOT tingkat isolasi sebelum awal transaksi.
Versi data yang dibaca oleh pernyataan.	Semua data yang diterapkan sebelum dimulainya setiap pernyataan.	Semua data yang diterapkan sebelum dimulainya setiap transaksi.
Bagaimana pembaruan ditangani.	Tanpa penguncian yang dioptimalkan: Mengembalikan dari versi baris ke data aktual untuk memilih baris untuk diperbarui dan menggunakan kunci pembaruan pada baris data yang dipilih. Memperoleh kunci eksklusif pada baris data aktual yang akan dimodifikasi. Tidak ada deteksi konflik pembaruan. Dengan penguncian yang dioptimalkan: Baris dipilih berdasarkan versi terakhir yang diterapkan tanpa kunci apa pun yang diperoleh. Jika baris memenuhi syarat untuk pembaruan, baris eksklusif atau kunci halaman diperoleh. Jika konflik pembaruan terdeteksi, konflik tersebut ditangani dan dicoba kembali secara otomatis.	Menggunakan versi baris untuk memilih baris yang akan diperbarui. Mencoba memperoleh kunci eksklusif pada baris data aktual yang akan dimodifikasi, dan jika data telah dimodifikasi oleh transaksi lain, konflik pembaruan terjadi dan transaksi rekam jepret dihentikan.
Memperbarui deteksi konflik	Tanpa penguncian yang dioptimalkan: Tidak ada. Dengan penguncian yang dioptimalkan: Jika konflik pembaruan terdeteksi, konflik tersebut ditangani dan dicoba kembali secara otomatis.	Dukungan terintegrasi. Tidak dapat dinonaktifkan.

Penggunaan sumber daya penerapan versi baris

Kerangka kerja penerapan versi baris mendukung fitur Mesin Database berikut:

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

Kerangka kerja penerapan versi baris juga mendukung tingkat isolasi transaksi berbasis penerapan versi baris berikut:

• READ_COMMITTED_SNAPSHOT Saat opsi database diatur ke ON, READ_COMMITTED transaksi menyediakan konsistensi baca tingkat pernyataan menggunakan penerapan versi baris.
• ALLOW_SNAPSHOT_ISOLATION Saat opsi database diatur ke ON, 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 di penyimpanan versi bahkan ketika tidak ada transaksi aktif menggunakan isolasi berbasis penerapan versi baris. Data setelah modifikasi menyertakan penunjuk ke data versi di penyimpanan versi. Untuk objek besar, hanya bagian dari objek yang diubah yang disimpan di penyimpanan versi.

Ruang yang digunakan dalam tempdb

Untuk setiap instans Mesin Database, penyimpanan versi harus memiliki ruang yang cukup untuk menyimpan versi baris. Administrator database harus memastikan bahwa tempdb dan database lain (jika Pemulihan Database Dipercepat diaktifkan) memiliki cukup ruang untuk mendukung penyimpanan versi. Ada dua jenis penyimpanan versi:

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

Versi baris harus disimpan selama transaksi aktif perlu mengaksesnya. Secara berkala, utas latar belakang menghapus versi baris yang tidak lagi diperlukan dan mengosongkan ruang di penyimpanan versi. 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 READ COMMITTED transaksi yang menggunakan penerapan versi baris. Dengan jenis transaksi ini, tampilan database yang konsisten secara transaksional hanya diperlukan untuk setiap pernyataan dalam transaksi. Ini berarti bahwa versi baris yang dibuat untuk pernyataan dalam transaksi tidak lagi diperlukan setelah pernyataan selesai. Namun, versi baris yang dibuat oleh setiap pernyataan dalam transaksi dipertahankan hingga transaksi selesai.

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

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

• Transaksi yang mencoba mengakses versi baris yang tidak dihasilkan karena tempdb roll back penuh dan 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 opsi diatur ke ON.
• Tabel memiliki pemicu.
• Beberapa Set Hasil Aktif (MARS) sedang digunakan.
• Operasi build indeks online saat ini berjalan pada tabel.

Jika penyimpanan versi berada di tempdb, 14 byte ini dihapus dari baris database saat pertama kali baris dimodifikasi di bawah semua kondisi ini:

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

14 byte juga dihapus ketika baris dimodifikasi jika Pemulihan Database Dipercepat tidak lagi diaktifkan dan kondisi di atas terpenuhi.

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

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

Ruang yang digunakan dalam objek besar

Mesin Database mendukung beberapa jenis data yang dapat menampung string besar hingga 2 gigabyte (GB) panjangnya, seperti: nvarchar(max), , varchar(max), varbinary(max)ntext, , textdan image. Data 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 disimpan dalam sekumpulan halaman yang didedikasikan untuk objek besar dalam tabel.

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

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

Ruang disk yang cukup harus dialokasikan untuk mengakomodasi persyaratan ini.

Memantau penerapan versi baris dan penyimpanan versi

Untuk memantau proses penerapan versi baris, penyimpanan versi, dan isolasi rekam jepret untuk performa dan masalah, Mesin Database menyediakan alat dalam bentuk Tampilan Manajemen Dinamis (DMV) dan penghitung kinerja.

DMV

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

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

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

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

• sys.dm_tran_top_version_generators. Mengembalikan tabel virtual untuk objek yang menghasilkan versi terbanyak di penyimpanan versi. Ini mengelompokkan 256 panjang rekaman agregat teratas berdasarkan database_id dan rowset_id. Gunakan fungsi ini untuk menemukan konsumen terbesar dari penyimpanan versi. Hanya berlaku untuk penyimpanan tempdb 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. Hanya berlaku untuk penyimpanan tempdb versi. 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. Hanya berlaku untuk penyimpanan tempdb versi. Untuk informasi selengkapnya, lihat sys.dm_tran_version_store_space_usage (Transact-SQL).

  Catatan

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

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

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

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

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

• sys.dm_tran_persistent_version_store_stats. Mengembalikan statistik untuk penyimpanan versi persisten di setiap database yang digunakan saat Pemulihan Database Dipercepat diaktifkan. Untuk informasi selengkapnya, lihat sys.dm_tran_persistent_version_store_stats (Transact-SQL).

Penghitung kinerja

Penghitung kinerja berikut memantau penyimpanan versi di tempdb, serta transaksi menggunakan penerapan versi baris. Penghitung kinerja terkandung dalam SQLServer:Transactions objek performa.

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

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

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

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

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

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

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

  Catatan

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

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

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

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

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

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

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

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

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

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

  Catatan

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

Contoh tingkat isolasi berbasis penerapan versi baris

Contoh berikut menunjukkan perbedaan perilaku antara SNAPSHOT transaksi isolasi dan READ COMMITTED transaksi yang menggunakan penerapan versi baris.

J. Bekerja dengan isolasi SNAPSHOT

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

Pada sesi 1:

USE AdventureWorks2022;
GO

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

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

BEGIN TRANSACTION;

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

Pada sesi 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

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

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

Pada sesi 1:

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, 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 isolasi READ COMMITTED menggunakan penerapan versi baris

Dalam contoh ini, transaksi yang READ COMMITTED menggunakan penerapan versi baris berjalan bersamaan dengan transaksi lain. Transaksi READ COMMITTED berakibat berbeda dari SNAPSHOT transaksi. SNAPSHOT Seperti transaksi, READ COMMITTED transaksi akan membaca baris versi bahkan setelah transaksi lain memodifikasi data. Namun, tidak seperti SNAPSHOT transaksi, READ COMMITTED transaksi:

• Membaca data yang dimodifikasi setelah transaksi lain melakukan perubahan data.
• Mampu memperbarui data yang dimodifikasi oleh transaksi lain di mana SNAPSHOT transaksi tidak dapat dilakukan.

Pada sesi 1:

USE AdventureWorks2022;
GO

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

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

BEGIN TRANSACTION;

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

Pada sesi 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

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

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

Pada sesi 1:

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

Pada sesi 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

Pada sesi 1:

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

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

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

Mengaktifkan tingkat isolasi berbasis penerapan versi baris

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

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

Pernyataan Transact-SQL berikut memungkinkan READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

ALLOW_SNAPSHOT_ISOLATION Saat opsi database diatur ke ON, instans Mesin Database tidak mulai menghasilkan versi baris untuk data yang dimodifikasi hingga semua transaksi aktif yang telah memodifikasi data dalam database selesai. Jika ada transaksi modifikasi aktif, Mesin Database mengatur status opsi ke PENDING_ON. Setelah semua transaksi modifikasi selesai, status opsi diubah menjadi ON. Pengguna tidak dapat memulai SNAPSHOT transaksi dalam database hingga opsinya adalah ON. Demikian pula, database melewati status PENDING_OFF saat administrator database mengatur ALLOW_SNAPSHOT_ISOLATION opsi ke OFF.

Pernyataan Transact-SQL berikut akan mengaktifkan ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

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

Status SNAPSHOT isolasi untuk database saat ini	Deskripsi
OFF	Dukungan untuk SNAPSHOT transaksi isolasi tidak diaktifkan. Tidak ada SNAPSHOT transaksi isolasi yang diizinkan.
PENDING_ON	Dukungan untuk SNAPSHOT transaksi isolasi dalam status transisi (dari OFF ke ON). Transaksi terbuka harus selesai. Tidak ada SNAPSHOT transaksi isolasi yang diizinkan.
ON	Dukungan untuk SNAPSHOT transaksi isolasi diaktifkan. SNAPSHOT transaksi diperbolehkan.
PENDING_OFF	Dukungan untuk SNAPSHOT transaksi isolasi dalam status transisi (dari ON ke OFF). SNAPSHOT transaksi yang dimulai setelah waktu ini tidak dapat mengakses database ini. Transaksi yang SNAPSHOT ada masih dapat mengakses database ini. Transaksi tulis yang ada masih menggunakan penerapan versi dalam database ini. PENDING_OFF Status tidak menjadi OFF sampai semua SNAPSHOT transaksi yang dimulai ketika status isolasi database SNAPSHOT selesaiON.

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 ke ON master dalam database dan msdb , dan tidak dapat dinonaktifkan.

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

Menggunakan tingkat isolasi berbasis penerapan versi baris

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

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

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

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

• SNAPSHOTisolasi dengan mengatur opsi database ke ON seperti yang ALLOW_SNAPSHOT_ISOLATION diperlihatkan dalam contoh kode berikut:

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  Saat menggunakan kueri lintas database, transaksi yang berjalan di bawah SNAPSHOT isolasi dapat mengakses tabel dalam database yang memiliki ALLOW_SNAPSHOT_ISOLATION opsi database yang diatur ke ON. Untuk mengakses tabel dalam database yang tidak memiliki opsi database yang ALLOW_SNAPSHOT_ISOLATION diatur ke ON, tingkat isolasi harus diubah. Misalnya, contoh kode berikut menunjukkan SELECT pernyataan yang menggabungkan dua tabel saat berjalan di bawah SNAPSHOT transaksi. Satu tabel milik database di mana SNAPSHOT isolasi tidak diaktifkan. SELECT Ketika pernyataan berjalan di bawah SNAPSHOT isolasi, pernyataan gagal dijalankan dengan sukses.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  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 saat mengakses tabel tertentu. Karena perubahan ini, SELECT pernyataan berhasil dijalankan.

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

Batasan transaksi menggunakan tingkat isolasi berbasis penerapan versi baris

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

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

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

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

• SNAPSHOT transaksi gagal ketika:

  • Database dibuat baca-saja setelah SNAPSHOT transaksi dimulai, tetapi sebelum SNAPSHOT transaksi mengakses database.
  • Jika mengakses objek dari beberapa database, status database diubah sedih sehingga pemulihan database terjadi setelah SNAPSHOT transaksi dimulai, tetapi sebelum SNAPSHOT transaksi mengakses database. Misalnya: database diatur ke OFFLINE lalu ke ONLINE, database secara otomatis ditutup dan dibuka kembali karena AUTO_CLOSE opsi yang diatur ke ON, atau database dilepas dan dipasang kembali.

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

• Mesin Database 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 SNAPSHOT isolasi menyebabkan SNAPSHOT transaksi gagal. READ COMMITTED transaksi tidak memiliki batasan ini ketika READ_COMMITTED_SNAPSHOT opsi database diatur ke ON.

  Misalnya, administrator database menjalankan pernyataan berikut ALTER INDEX .

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

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

  Catatan

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

Menyesuaikan penguncian dan penerapan versi baris

Menyesuaikan batas waktu penguncian

Ketika instans Mesin Database 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 untuk menunggu kunci, oleh karena itu transaksi berpotensi diblokir tanpa batas waktu.

Catatan

sys.dm_os_waiting_tasks Gunakan tampilan manajemen dinamis untuk menentukan apakah tugas diblokir dan apa yang memblokirnya. Untuk informasi dan contoh selengkapnya, lihat Memahami dan mengatasi masalah pemblokiran SQL Server.

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

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

Penting

Aplikasi yang menggunakan transaksi eksplisit dan mengharuskan transaksi dihentikan setelah menerima kesalahan 1222 harus secara eksplisit mengembalikan transaksi sebagai bagian dari penanganan kesalahan. Tanpa ini, pernyataan lain tidak sengaja dapat dijalankan pada sesi yang sama sementara transaksi tetap aktif, yang menyebabkan pertumbuhan log transaksi yang tidak terbatas dan kehilangan data jika transaksi digulung balik nanti.

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 Mesin Database. Jika aplikasi harus beroperasi pada tingkat isolasi yang berbeda, aplikasi dapat menggunakan metode berikut untuk mengatur tingkat isolasi:

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

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

Contoh berikut mengatur SERIALIZABLE tingkat isolasi:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
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.

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

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

Berikut set hasilnya.

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 SELECTpernyataan , , INSERTUPDATE, DELETE dan MERGE . Petunjuk menentukan jenis penguncian atau penerapan versi baris instans yang digunakan Mesin Database untuk data tabel. Petunjuk penguncian tingkat tabel dapat digunakan ketika kontrol yang lebih baik dari jenis kunci yang diperoleh pada objek diperlukan. Petunjuk penguncian ini mengambil alih tingkat isolasi transaksi saat ini untuk sesi.

Catatan

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

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

Catatan

Sebaiknya petunjuk penguncian tingkat tabel digunakan untuk mengubah perilaku penguncian default hanya jika diperlukan. Memaksa tingkat penguncian dapat berdampak buruk pada konkurensi.

Mesin Database mungkin harus memperoleh kunci saat membaca metadata, bahkan saat memproses pernyataan dengan petunjuk penguncian yang mencegah permintaan kunci bersama saat membaca data. Misalnya, pernyataan yang SELECT berjalan di bawah READ UNCOMMITTED tingkat isolasi atau menggunakan NOLOCK petunjuk tidak memperoleh kunci berbagi saat membaca data, tetapi mungkin terkadang meminta kunci saat membaca tampilan katalog sistem. Ini berarti dimungkinkan bagi pernyataan seperti SELECT itu untuk diblokir ketika transaksi bersamaan memodifikasi metadata tabel.

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

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

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

-- End the transaction.
ROLLBACK;
GO

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

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

Menyesuaikan penguncian untuk indeks

Mesin Database 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, kecuali jika pola akses tabel atau indeks dipahami dengan baik dan konsisten, dan ada masalah ketidakcocokan sumber daya untuk dipecahkan. Mengambil alih tingkat penguncian dapat secara signifikan menghambat akses bersamaan ke tabel atau indeks. Misalnya, menentukan hanya kunci tingkat tabel pada tabel besar yang sangat dapat diakses pengguna dapat menyebabkan hambatan karena pengguna harus menunggu kunci tingkat tabel dirilis sebelum mengakses tabel.

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

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

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

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

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

Informasi transaksi tingkat lanjut

Transaksi nest

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

Contoh berikut menunjukkan penggunaan transaksi berlapis. Jika TransProc dipanggil ketika transaksi aktif, hasil transaksi berlapis dikendalikan TransProc oleh transaksi luar, dan pernyataannya INSERT diterapkan atau digulung balik berdasarkan penerapan atau pengembalian transaksi luar. Jika TransProc dijalankan oleh proses yang tidak memiliki transaksi terutang, COMMIT TRANSACTION di akhir prosedur akan menerapkan INSERT pernyataan.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

Melakukan transaksi dalam diabaikan oleh Mesin Database saat transaksi luar aktif. Transaksi dilakukan atau digulung balik berdasarkan penerapan atau gulung balik di akhir transaksi terluar. Jika transaksi luar dilakukan, transaksi berlapis dalam juga dilakukan. Jika transaksi luar digulung balik, maka semua transaksi dalam juga digulung balik, terlepas dari apakah transaksi dalam dilakukan secara individual atau tidak.

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

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

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

Menggunakan sesi terikat

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

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

Catatan

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

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

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

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

• Token ikatan dapat disimpan dalam tabel dalam instans Mesin Database yang dapat dibaca oleh proses yang ingin diikat ke 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 token yang sama yang dapat mengakses instans sampai sesi saat ini selesai memproses atau membatalkan pernyataan saat ini. Jika instans sibuk memproses pernyataan dari sesi terikat lainnya, terjadi kesalahan yang menunjukkan bahwa ruang transaksi sedang digunakan dan sesi harus mencoba kembali nanti.

Saat Anda mengikat sesi, setiap sesi mempertahankan pengaturan tingkat isolasinya. Menggunakan SET TRANSACTION ISOLATION LEVEL untuk mengubah pengaturan tingkat isolasi satu sesi tidak memengaruhi pengaturan sesi lain yang terikat ke token yang sama.

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.

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

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

Kapan menggunakan sesi terikat

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

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

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

Transaksi efisien kode

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

Panduan kode

Ini adalah panduan untuk mengkoding transaksi yang efisien:

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

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

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

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

• Gunakan tingkat isolasi transaksi yang lebih rendah dengan cerdas. Banyak aplikasi dapat dikodekan untuk menggunakan READ COMMITTED tingkat isolasi transaksi. Beberapa transaksi memerlukan SERIALIZABLE tingkat isolasi transaksi.

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

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

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

• Hindari menggunakan transaksi implisit jika memungkinkan. Transaksi implisit dapat memperkenalkan perilaku yang tidak dapat diprediksi karena sifatnya. Lihat Transaksi Implisit dan masalah konkurensi.

Transaksi implisit dan menghindari konkurensi dan masalah sumber daya

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

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

Mengelola transaksi yang berjalan lama

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

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

• Jika instans server dimatikan setelah transaksi aktif melakukan banyak modifikasi yang tidak dilakukan, fase pemulihan mulai ulang berikutnya dapat memakan waktu lebih lama dari waktu yang ditentukan oleh recovery interval opsi konfigurasi server atau dengan ALTER DATABASE ... SET TARGET_RECOVERY_TIME opsi . Opsi ini mengontrol 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 menyimpan pemotongan log tanpa batas waktu, menyebabkan log transaksi tumbuh dan mungkin terisi. Jika log transaksi terisi, database tidak dapat melakukan penulisan lagi. Untuk informasi selengkapnya, lihat Arsitektur log transaksi SQL Server dan panduan manajemen, Memecahkan masalah log transaksi lengkap (Kesalahan SQL Server 9002), dan Log transaksi.

Penting

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

Temukan transaksi jangka panjang

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

• sys.dm_tran_database_transactions

  Tampilan manajemen dinamis ini mengembalikan informasi terkait transaksi di tingkat database. Untuk transaksi 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 rekaman 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 berpotensi melacak sumber transaksi untuk penghentian yang sesuai (penerapan atau putar kembali). Untuk informasi selengkapnya, lihat DBCC OPENTRAN (Transact-SQL).

Mengakhiri transaksi

Untuk mengakhiri transaksi pada sesi tertentu, gunakan KILL pernyataan . Gunakan pernyataan ini dengan sangat hati-hati, namun, terutama ketika proses kritis berjalan. Untuk informasi selengkapnya, lihat Pernyataan KILL (T-SQL).

Kebuntuan

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

• Untuk informasi selengkapnya tentang kebuntuan, termasuk pemantauan, diagnosis, dan sampel, lihat panduan Kebuntuan.
• Untuk informasi selengkapnya tentang kebuntuan khusus untuk Azure SQL Database, lihat Menganalisis dan mencegah kebuntuan di Azure SQL Database.

Konten terkait

• Memahami dan menyelesaikan masalah pemblokiran SQL Server
• Memahami dan mengatasi masalah pemblokiran Azure SQL Database
• Tampilan dan Fungsi Manajemen Dinamis Terkait Transaksi (Transact-SQL)
• Overhead Penerapan Versi Baris
• sys.dm_tran_locks (T-SQL)