Panduan penguncian transaksi dan penerapan versi baris

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

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.

Note

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

• Untuk informasi selengkapnya dan untuk mengetahui di mana 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 memperkenalkan perubahan signifikan pada beberapa bagian artikel ini, termasuk:

• Mengunci Mesin Database
• Hapus operasi
• Operasi penyisipan
• 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.

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

Consistency
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 B-tree atau daftar tertaut ganda, harus benar di akhir transaksi.

Note

Dokumentasi biasanya menggunakan istilah pohon B ketika merujuk pada 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.

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

Durability
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 versi yang lebih baru mengaktifkan transaksi tertunda yang tahan lama. 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 mungkin hilang jika ada kegagalan sistem sebelum rekaman 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 yang mencakup batch tidak dikomitmen atau dibatalkan, itu secara otomatis dibatalkan 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.

Note

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 akan diterapkan; jika terjadi kesalahan, pernyataan akan dibatalkan. Koneksi ke instance Mesin Database beroperasi dalam mode autocommit setiap kali mode default ini belum digantikan 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 menandai memulai transaksi; Anda hanya mengonfirmasi 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 diaktifkan 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 dalam Cakupan Batch

Hanya berlaku untuk multiple active result sets (MARS), transaksi eksplisit atau implisit Transact-SQL yang dimulai di bawah sesi MARS menjadi transaksi dengan cakupan batch. Transaksi dalam cakupan batch yang tidak dikonfirmasi atau dibatalkan ketika batch selesai akan secara otomatis dibatalkan 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 di-komit atau dibatalkan. Komit terdistribusi harus dikelola secara berbeda oleh manajer transaksi untuk meminimalkan risiko bahwa kegagalan jaringan dapat mengakibatkan beberapa manajer sumber daya dalam transaksi berhasil mengkomit 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. Saat setiap manajer sumber daya menyelesaikan fase persiapan, ia mengembalikan keberhasilan atau kegagalan fase 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.

• Commit

  Jika transaksi berhasil, terapkan. Pernyataan COMMIT menjamin semua modifikasi transaksi dibuat sebagai bagian permanen dari database. ** Komitmen 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 membatalkan semua modifikasi yang dilakukan dalam transaksi dengan mengembalikan data ke kondisi seperti di awal transaksi. Roll back juga membebaskan sumber daya yang digunakan oleh transaksi tersebut.

Note

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 menghasilkan 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 instance Mesin Basis Data terputus, setiap transaksi yang tertunda pada koneksi tersebut akan dibatalkan ketika jaringan menginformasikan instance tentang pemutusan koneksi. Jika aplikasi klien gagal atau jika komputer klien tidak berfungsi atau dinyalakan ulang, ini juga memutuskan koneksi, dan instans Mesin Database membatalkan transaksi yang belum selesai ketika menerima pemberitahuan dari jaringan 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. Kesalahan kompilasi, seperti kesalahan sintaksis, tidak terpengaruh 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 pernyataan THROW untuk menghasilkan pengecualian dan memindahkan eksekusi ke blok CATCH dari konstruksi TRY...CATCH. Untuk informasi selengkapnya, lihat THROW (Transact-SQL).

Kesalahan waktu kompilasi dan waktu run 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 menyebabkan kesalahan dibatalkan, kesalahan mencegah eksekusi apapun dalam batch. Dalam contoh berikut, pernyataan-pernyataan INSERT dalam batch ketiga tidak dijalankan karena adanya 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 waktu proses. Dua INSERT pernyataan pertama berhasil dan dilakukan, sehingga tetap ada setelah kesalahan runtime.

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 dieksekusi dan diterapkan, dan kedua baris tersebut tetap ada dalam tabel TestBatch 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 pembuatan versi baris

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

• Locking

  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 pengunciannya ketika tidak lagi memiliki ketergantungan 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 memblokir transaksi lain sangat berkurang.

Penguncian dan versi baris mencegah pengguna membaca data yang belum diselesaikan dan mencegah banyak pengguna mencoba mengubah data yang sama secara bersamaan. Tanpa penguncian atau versi baris, kueri yang dijalankan terhadap data tersebut dapat menghasilkan hasil yang tidak terduga dengan mengembalikan data yang belum dikomit 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 yang hilang

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

  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 paling akhir akan menimpa hasil 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 belum dikomit (bacaan kotor)

  Dependensi yang belum selesai terjadi ketika transaksi kedua membaca baris yang sedang dalam proses diperbarui oleh transaksi lain. Transaksi kedua adalah membaca data yang belum dilakukan commit dan mungkin diubah oleh transaksi yang memperbarui baris tersebut.

  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 (baca yang tidak dapat diulang)

  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 serupa dengan dependensi yang belum diselesaikan karena transaksi lain sedang mengubah data yang sedang dibaca oleh transaksi kedua. Namun, dalam analisis yang tidak konsisten, data yang dibaca oleh transaksi kedua telah dikomit oleh transaksi yang melakukan perubahan. Selain itu, analisis yang tidak konsisten melibatkan beberapa pembacaan (dua atau lebih) dari baris yang sama, dan setiap kali informasi diubah oleh transaksi lain; dengan demikian, istilah baca tidak dapat diulang.

  Misalnya, editor membaca dokumen yang sama dua kali, tetapi di antara setiap pembacaan, penulis menulis ulang dokumen tersebut. 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 berikut menunjukkan bagaimana hal ini mungkin terjadi. Asumsikan kedua transaksi 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

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

    Transaksi yang berjalan di tingkat READ UNCOMMITTED (atau pernyataan yang menggunakan petunjuk tabel NOLOCK) tidak menerbitkan kunci berbagi untuk mencegah transaksi lain memodifikasi data yang dibaca oleh transaksi saat ini. Transaksi yang berjalan pada tingkat READ COMMITTED memang 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 versi baris. Untuk informasi selengkapnya, lihat Petunjuk Tabel (Transact-SQL).

  • Menghilangkan satu atau beberapa baris yang tidak menjadi 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 saat 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 biasanya digunakan dalam sistem di mana ada kontensi tinggi untuk data, di mana biaya melindungi data dengan penguncian lebih kecil daripada biaya menggulung kembali transaksi jika terjadi konflik konkurensi.

• Kontrol konkurensi optimis

  Dalam kontrol konkurensi optimis, transaksi tidak mengunci data saat melakukan pembacaan. 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 mengalami rollback dan mulai lagi. Ini disebut optimis karena biasanya digunakan dalam sistem di mana ada ketidakcocokan rendah untuk data, dan di mana biaya sesekali menggulung balik transaksi lebih rendah dari 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.
• Seberapa lama kunci baca dipertahankan.
• Apakah operasi pembacaan mengacu pada baris yang dimodifikasi oleh transaksi lain:
  • Menunggu hingga kunci eksklusif pada baris dibebaskan.
  • Mengambil versi baris yang telah dikomit seperti pada saat pernyataan atau transaksi dimulai.
  • Membaca modifikasi data yang belum dikonfirmasi.

Important

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 memblokir transaksi lainnya. Memilih tingkat isolasi yang sesuai tergantung pada penyeimbangan persyaratan integritas data aplikasi terhadap overhead setiap tingkat isolasi. Tingkat isolasi tertinggi, SERIALIZABLE, menjamin bahwa transaksi 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, mungkin mengambil data yang telah dimodifikasi tetapi belum di-commit oleh transaksi lain. Semua efek samping ketidaksamaan mungkin terjadi di READ UNCOMMITTED, tetapi tidak ada penguncian baca atau pembuatan versi, dengan demikian overhead diminimalkan.

Tingkat isolasi Mesin Database

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

Tingkat Isolasi	Definition
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 Versi Baris	Definition
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 untuk membaca baris yang dimodifikasi oleh transaksi lain hingga transaksi lain tersebut selesai. Kedua implementasi memenuhi definisi ISO isolasi READ COMMITTED .
SNAPSHOT	Tingkat isolasi snapshot menggunakan versi baris untuk memberikan konsistensi baca pada tingkat transaksi. Operasi baca tidak memperoleh kunci halaman atau baris; hanya kunci stabilitas skema tabel (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, terdapat batasan terhadap operasi DDL yang dapat dilakukan dalam transaksi eksplisit yang berjalan di bawah isolasi snapshot. Pernyataan DDL berikut tidak diizinkan di bawah isolasi snapshot setelah pernyataan BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, atau pernyataan DDL common language runtime (CLR). Pernyataan ini diizinkan saat Anda menggunakan isolasi rekam jepret dalam transaksi implisit. Transaksi implisit, menurut definisi, adalah satu pernyataan yang memungkinkan untuk menerapkan semantik isolasi snapshot, bahkan ketika menggunakan 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	Bacaan kotor	Baca Tidak Terulang	Phantom
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Yes
SNAPSHOT	No	No	No
SERIALIZABLE	No	No	No

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

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

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

ADO
Aplikasi ADO mengatur properti objek ke IsolationLevel, , ConnectionadXactReadUncommitted, atau adXactReadCommitted.adXactRepeatableReadadXactReadSerializable

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

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

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

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

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

Mengunci Mesin Basis Data

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 terhadap data tersebut untuk 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 dioptimasi tidak diaktifkan, kunci baris dan halaman yang diperlukan untuk penulisan dipertahankan hingga akhir transaksi.

• Saat penguncian yang dioptimalkan diaktifkan, hanya kunci ID Transaksi (TID) yang ditahan hingga akhir transaksi. Di tingkat isolasi default READ COMMITTED, transaksi tidak akan mempertahankan kunci baris dan halaman yang diperlukan untuk penulisan sampai 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 yang terakhir dikomit tanpa memperoleh kunci, sehingga meningkatkan konkurensi.

Semua kunci yang dipegang oleh transaksi dirilis ketika transaksi selesai (baik di-commit atau di-rollback).

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 kebersamaan tetapi memiliki overhead yang lebih tinggi karena lebih banyak kunci yang harus dipegang jika banyak baris dikunci. Penguncian pada granularitas yang lebih besar, seperti tabel, memiliki biaya tinggi dalam hal konkurensi. Hal ini karena penguncian 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, sebuah instans Mesin Basis Data mungkin harus mendapatkan kunci bersama pada baris serta kunci bersama niat pada halaman dan tabel.

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

Resource	Description
RID	Pengidentifikasi baris yang digunakan untuk mengunci satu baris dalam kumpulan data.
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 terdiri dari delapan halaman yang bersebelahan, seperti halaman data atau 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 oleh aplikasi.
METADATA	Kunci metadata.
ALLOCATION_UNIT	Unit pengalokasian.
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. Untuk informasi selengkapnya, lihat Penambahan diagnostik untuk penguncian yang dioptimalkan.

Modus 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	Description
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 pengguna membaca, melakukan penguncian, dan berpotensi memperbarui sumber daya kemudian.
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.
Intent	Digunakan untuk membuat hierarki kunci. Jenis kunci niat adalah: niat berbagi (IS), niat eksklusif (IX), dan berbagi dengan niat eksklusif (SIX).
Schema	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 memindahkan data dalam jumlah besar ke dalam tabel dengan petunjuk TABLOCK.
Key-range	Melindungi jangkauan baris yang dibaca oleh kueri saat menggunakan tingkat isolasi transaksi SERIALIZABLE. Memastikan bahwa transaksi lain tidak dapat menyisipkan baris yang dapat memenuhi syarat bagi 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 Basis Data menempatkan kunci pembaruan (U) saat mempersiapkan eksekusi pembaruan. U kunci kompatibel dengan S kunci, tetapi hanya satu transaksi yang dapat menahan sebuah U kunci pada satu waktu pada sumber daya tertentu. Ini adalah kunci - banyak transaksi bersamaan dapat memegang S kunci, tetapi hanya satu transaksi yang dapat memegang U kunci pada sumber daya. Kunci pembaruan (U) pada akhirnya ditingkatkan menjadi kunci eksklusif (X) untuk memperbarui baris data.

Kunci pembaruan (U) juga dapat digunakan oleh pernyataan selain UPDATE, ketika indikasi penggunaan tabel UPDLOCK ditentukan dalam pernyataan tersebut.

• 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, kunci S berdurasi pendek, dirilis segera setelah digunakan. Meskipun kebuntuan yang dijelaskan di atas masih mungkin terjadi, kemungkinannya jauh lebih kecil dengan kunci dengan durasi yang singkat.

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

• Jika petunjuk UPDLOCK digunakan selama penulisan ketika isolasi SNAPSHOT diterapkan, transaksi harus memiliki akses ke versi terbaru dari baris tersebut. 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 snapshot.

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 kasus ini, pernyataan UPDATE meminta kunci berbagi pada baris yang dibaca dalam tabel penggabungan, 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 dengan demikian menunjukkan niat 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 dapat membatalkan kunci pada 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	Description
Niat bersama (IS)	Melindungi kunci bersama yang diminta atau diperoleh pada beberapa (tetapi tidak semua) sumber daya yang lebih rendah dalam hierarki.
Tujuan 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 di tingkat bawah hierarki dan kunci niat 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 niat eksklusif 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 kedua kunci ini secara terpisah dan memegangnya secara bersamaan. Misalnya, sebuah transaksi menjalankan kueri dengan petunjuk PAGLOCK dan kemudian melaksanakan operasi pembaruan. Kueri dengan petunjuk PAGLOCK memperoleh kunci S, dan operasi pembaruan memperoleh kunci IU.
Perbarui tujuan eksklusif (UIX)	Kombinasi kunci U dan IX, sebagai hasil dari memperoleh kedua kunci ini secara terpisah, dan pada saat yang sama memegang kedua kunci tersebut.

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 dari 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 apa pun, 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

Kunci pembaruan massal (BU) memungkinkan beberapa utas memuat massal data secara bersamaan ke dalam tabel yang sama, sambil mencegah proses lain yang tidak melakukan pemuatan massal data mengakses tabel. Mesin Basis Data menggunakan kunci pembaruan massal (BU) saat kedua kondisi berikut 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 opsi tabel table lock on bulk load diatur menggunakan sp_tableoption.

Tip

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

Kunci rentang

Kunci rentang 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. Melindungi rentang kunci antar baris 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 sampai kunci bersama telah dirilis.

Tabel berikut menunjukkan kompatibilitas mode kunci yang paling umum ditemui.

Mode yang telah diberikan	IS	S	U	IX	SIX	X
Mode yang diminta
Niat bersama (IS)	Yes	Yes	Yes	Yes	Yes	No
Bersama (S)	Yes	Yes	Yes	No	No	No
Pembaruan (U)	Yes	Yes	No	No	No	No
Tujuan eksklusif (IX)	Yes	No	No	Yes	No	No
Dibagikan dengan niat eksklusif (SIX)	Yes	No	No	No	No	No
Eksklusif (X)	No	No	No	No	No	No

Note

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 diberikan kunci IX pada tingkat tabel dan halaman. Namun, satu transaksi diberikan kunci X pada tingkat baris. Transaksi lain harus menunggu hingga kunci tingkat baris dihapus.

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

Key	Description
N	Tidak ada konflik
I	Illegal
C	Conflict
NL	Tidak ada kunci
SCH-S	Kunci stabilitas skema
SCH-M	Kunci modifikasi skema
S	Shared
U	Update
X	Exclusive
IS	Niat dibagikan
IU	Pembaruan niat
IX	Maksud eksklusif
SIU	Berbagi dengan pembaruan niat
SIX	Berbagi dengan niat eksklusif
UIX	Perbarui dengan tujuan khusus
BU	Pembaruan massal
RS-S	Jangkauan yang Dibagi
RS-U	Pembaruan rentang yang dibagikan
RI-N	Sisipkan rentang-null
RI-S	Sisipkan rentang berbagi
RI-U	Sisipkan pembaruan rentang
RI-X	Sisipkan rentang khusus
RX-S	Rentang berbagi khusus
RX-U	Pembaruan rentang eksklusif
RX-X	Rentang istimewa-eksklusif

Penguncian rentang kunci

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

Penguncian rentang kunci mencegah bacaan hantu. Dengan melindungi rentang kunci antar baris, ini juga mencegah penyisipan phantom pada sekumpulan rekaman yang diakses oleh transaksi.

Kunci rentang ditempatkan pada indeks, yang 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 mencakup baik komponen rentang maupun baris yang ditentukan dalam format rentang-baris.

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

Range	Row	Mode	Description
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.

Note

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 telah diberikan	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
Mode yang diminta
Bersama (S)	Yes	Yes	No	Yes	Yes	Yes	No
Pembaruan (U)	Yes	No	No	Yes	No	Yes	No
Eksklusif (X)	No	No	No	No	No	Yes	No
RangeS-S	Yes	Yes	No	Yes	Yes	No	No
RangeS-U	Yes	No	No	Yes	No	No	No
RangeI-N	Yes	Yes	Yes	No	No	Yes	No
RangeX-X	No	No	No	No	No	No	No

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 serial, pengambilan data tunggal, hapus, dan sisipkan

Penguncian rentang kunci memastikan bahwa operasi berikut dapat diserialisasikan:

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

Sebelum penguncian kunci rentang dapat dilakukan, 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.

Examples

Tabel dan indeks berikut digunakan sebagai dasar untuk contoh penguncian rentang kunci yang akan dibahas berikutnya.

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 mode pada entri indeks ini memastikan bahwa tidak ada nama baru yang dimulai dengan huruf A dapat ditambahkan sebelum Adam, seperti Abigail. 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.

Note

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, dengan kueri ini:

SELECT name
FROM mytable
WHERE name = 'Bill';

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

Hapus operasi tanpa penguncian yang dioptimalkan

Saat menghapus baris dalam transaksi, rentang di mana baris tersebut berada tidak perlu 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 diblokir hingga transaksi penghapusan dilakukan atau digulung balik. (Opsi READ_COMMITTED_SNAPSHOT database dan SNAPSHOT level isolasi juga memungkinkan membaca dari versi baris dari status yang telah dikomit sebelumnya.)

Penghapusan rentang dapat dijalankan menggunakan tiga mode kunci dasar: kunci baris, kunci 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 mengalokasikan kembali halaman indeks segera setelah 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 proses latar belakang.

Hapus operasi dengan penguncian yang dioptimalkan

Saat menghapus baris dalam transaksi, kunci baris dan halaman diperoleh dan dilepaskan secara bertahap, dan tidak ditahan 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 diblokir hingga transaksi penghapusan dilakukan atau digulung balik. (Opsi READ_COMMITTED_SNAPSHOT database dan level SNAPSHOT isolasi juga memungkinkan pembacaan dari versi baris dari status yang telah dikomit sebelumnya.)

Jika tidak, mekanisme penguncian pada operasi penghapusan adalah sama seperti saat tidak menggunakan penguncian yang telah 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 mode RangeI-N untuk rentang kunci 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 rentang mode RangeI-N hanya diperlukan untuk menguji rentang dan tidak ditahan selama transaksi 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 diblokir hingga transaksi penyisipan dilakukan atau digulung balik.

Sisipkan operasi dengan penguncian yang dioptimalkan

Saat memasukkan baris dalam sebuah transaksi, rentang data tempat baris tersebut masuk tidak harus dikunci selama 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 dilepas dengan cepat dan tidak dipertahankan 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 dioptimalkan, sebuah kunci hanya diperoleh jika setidaknya ada satu transaksi yang menggunakan tingkat isolasi SERIALIZABLE pada instans. Kunci rentang mode RangeI-N 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 jangkauan kunci mode RangeI-N hanya diperlukan untuk menguji rentang dan tidak ditahan selama durasi transaksi yang sedang 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 diblokir hingga transaksi penyisipan dilakukan atau digulung balik.

Eskalasi kunci

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

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

Eskalasi penguncian tanpa penguncian yang telah 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 berniat diminta pada objek berikut:
  • Halaman pada tingkat daun dari indeks tidak berkumpul
  • Halaman data indeks berkelompok
  • Halaman data tumpuk

Mesin Database mungkin melakukan penguncian pada baris dan halaman untuk pernyataan yang sama guna meminimalkan jumlah kunci dan mengurangi kemungkinan bahwa eskalasi kunci 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 maksud pada tabel menjadi kunci penuh yang sesuai, misalnya, mengubah kunci maksud eksklusif (IX) menjadi kunci eksklusif (X), atau kunci maksud berbagi (IS) menjadi kunci berbagi (S). Jika upaya eskalasi penguncian berhasil dan kunci tabel penuh berhasil diperoleh, maka semua kunci HoBT, halaman (PAGE), atau tingkat baris (RID, KEY) yang dipegang oleh transaksi pada tumpukan atau indeks akan dilepaskan. Jika kunci penuh tidak dapat diperoleh, eskalasi kunci tidak terjadi saat itu, dan Mesin Database terus memperoleh kunci baris, kunci 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 pada partisi yang bersangkutan alih-alih ke kunci tabel. Kunci tingkat HoBT tidak selalu mengunci HoBTs yang selaras untuk partisi.

Note

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 beberapa kasus yang jarang terjadi, granularitas penguncian TABLE dapat memberikan kinerja yang lebih baik.

Jika upaya eskalasi kunci gagal karena adanya konflik kunci yang dipegang oleh transaksi bersamaan, Mesin Database akan mengulangi eskalasi kunci setiap kali 1.250 kunci tambahan diperoleh oleh transaksi tersebut.

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

Misalnya, asumsikan bahwa sesi melakukan operasi ini:

• Memulai transaksi.
• Pembaruan TableA. Ini menghasilkan kunci baris eksklusif di TableA yang akan tetap ditahan hingga transaksi selesai.
• Pembaruan TableB. Ini menghasilkan kunci baris eksklusif di TableB yang ditahan hingga transaksi selesai.
• Melaksanakan SELECT yang menggabungkan TableA dengan TableC. Rencana eksekusi kueri menentukan urutan pengambilan baris yang akan diambil dari TableA sebelum baris diambil dari TableC.
• Pernyataan SELECT memicu eskalasi kunci saat mengambil baris dari TableA dan sebelum mengakses TableC.

Jika eskalasi kunci berhasil, hanya kunci yang dipegang oleh sesi pada TableA yang dieskalasikan. Ini termasuk baik kunci bersama dari pernyataan SELECT maupun kunci eksklusif dari pernyataan sebelumnya UPDATE. Meskipun hanya kunci yang diperoleh sesi dalam pernyataan SELECT dihitung untuk menentukan apakah eskalasi kunci harus dilakukan, jika eskalasi berhasil, semua kunci yang dipegang oleh sesi di TableA dieskalasikan menjadi kunci eksklusif pada tabel, dan semua kunci dengan granularitas lebih rendah lainnya, termasuk kunci niat di TableA, dilepaskan.

Tidak ada upaya yang dilakukan untuk menaikkan level kunci pada TableB karena tidak ada referensi aktif ke TableB pada pernyataan SELECT. Demikian pula, tidak ada upaya yang dilakukan untuk mengeskalasi kunci pada TableC, yang tidak dieskalasi karena belum diakses ketika eskalasi terjadi.

Eskalasi penguncian dengan sistem penguncian yang dioptimalkan

Penguncian yang dioptimalkan membantu mengurangi memori kunci karena sangat sedikit kunci yang ditahan selama transaksi. Saat Mesin Basis Data memperoleh kunci baris dan halaman, eskalasi kunci dapat terjadi dalam cara yang 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 tingkat isolasi default READ COMMITTED digunakan, Mesin Database akan melepaskan kunci 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 opsi ALTER TABLE SET LOCK_ESCALATION dan ketika salah satu kondisi berikut ada:

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

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

Ambang eskalasi untuk pernyataan Transact-SQL

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

Eskalasi kunci hanya terjadi pada tabel yang telah diakses saat itu juga ketika 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 mengakses 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 melakukan eskalasi semua kunci yang dipegang oleh transaksi saat ini di TableA, tetapi karena jumlah kunci di TableA kurang dari 5.000, eskalasi tidak akan berhasil. Eskalasi kunci tidak dilakukan untuk TableC karena belum sempat diakses saat 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 locks:

• 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 UPDATE ini memperoleh penguncian berikut:

• Kunci eksklusif (X) terhadap baris data yang diperbarui.
• Kunci eksklusif niat (IX) pada halaman indeks berkluster yang berisi baris tersebut.
• Kunci IX pada indeks berkelompok dan satu lagi pada tabel.

Pernyataan ini memperoleh SELECT kunci-kunci ini:

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

Jika pernyataan memperoleh kunci yang cukup untuk memicu eskalasi kunci dan eskalasi berhasil, kunci pada tabel dikonversi menjadi kunci lain, dan semua kunci baris, halaman, dan indeks dilepaskan. Baik pembaruan maupun pembacaan dilindungi oleh kunci X pada tabel.

Mengurangi penguncian dan eskalasi kunci

Dalam kebanyakan kasus, Mesin Database memberikan performa terbaik saat beroperasi dengan setelan default untuk penguncian dan eskalasi kunci.

• Manfaatkan penguncian yang dioptimalkan.

  • Penguncian yang dioptimalkan menawarkan mekanisme penguncian transaksi yang ditingkatkan yang mengurangi konsumsi memori kunci dan pemblokiran untuk transaksi bersamaan. Kemungkinan eskalasi kunci terjadi jauh lebih kecil 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 basis data yang dipercepat (ADR) diaktifkan pada basis data.

Jika contoh Mesin Basis Data menghasilkan banyak kunci dan sering mengalami eskalasi kunci, pertimbangkan strategi berikut untuk mengurangi penguncian:

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

• Gunakan petunjuk tabel PAGLOCK atau TABLOCK agar Mesin Database menggunakan kunci halaman, timbunan, atau indeks sebagai ganti 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 opsi ALTER TABLE untuk mengeskalasi kunci ke tingkat partisi alih-alih tingkat tabel, atau untuk mematikan 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 penguncian; selain itu, hal ini meningkatkan risiko kebuntuan, dan umumnya berdampak buruk pada kepaduan 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 mungkin mengembalikan banyak baris, mungkin 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 tingkat isolasi READ COMMITTED mungkin memperoleh ribuan kunci (pada indeks berkluster dan satu indeks non-kluster), yang dapat menyebabkan kueri tersebut melebihi batas 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. Join Nested Loop mungkin juga menggunakan optimasi 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 melakukan eskalasi tidak akan terhambat 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 melalui koneksi lain yang tidak kompatibel dengan jenis kunci yang dinaikkan. Kunci niat eksklusif (IX) di tingkat tabel tidak mengunci baris atau halaman apa pun, namun tetap tidak kompatibel dengan kunci tabel bersama (S) atau eksklusif (X) yang ditingkatkan. Misalnya, asumsikan bahwa Anda harus menjalankan pekerjaan batch yang memodifikasi sejumlah besar baris di tabel mytable 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 di mytable).

• Anda juga dapat menggunakan bendera pelacakan 1211 dan 1224 untuk menonaktifkan semua atau beberapa eskalasi kunci. Namun, trace flags ini menonaktifkan semua eskalasi kunci secara global untuk seluruh instance Mesin Database. Eskalasi kunci melayani tujuan yang 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.

  Note

  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.

  Note

  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 diperkenalkannya opsi tabel LOCK_ESCALATION. 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 meminta kunci pada bagian data yang sama secara bersamaan. 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 mungkin memilih kunci halaman dalam indeks saat melakukan pemindaian indeks.

Mengunci partisi

Untuk sistem komputer besar, kunci pada objek yang sering diakses dapat menjadi bottleneck kinerja karena memperoleh dan melepaskan kunci menimbulkan persaingan pada sumber daya penguncian internal. Pembagian kunci meningkatkan performa penguncian dengan memecah sumber penguncian tunggal menjadi beberapa sumber penguncian. 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 dengan volume besar, kontensi dapat terjadi karena permintaan kunci harus menunggu hingga 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.

• Memory

  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 antar CPU, yang membantu meningkatkan performa.

Menerapkan dan memantau partisi kunci

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

Saat memperoleh kunci pada sumber daya yang dipartisi:

• Hanya mode NL, 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, IS, IU, dan IX harus diperoleh untuk semua partisi dimulai dengan ID partisi 0 kemudian dalam urutan ID partisi. Kunci ini pada sumber daya yang dipartisi menggunakan lebih banyak memori daripada kunci dalam mode yang sama pada sumber daya yang tidak dipartisi karena setiap partisi secara efektif merupakan kunci terpisah. Peningkatan memori ditentukan oleh jumlah partisi. Penghitung kinerja kunci SQL Server menampilkan informasi tentang memori yang digunakan oleh kunci yang dipartisi dan tidak dipartisi.

Transaksi dialokasikan 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 ini mengilustrasikan pemartisian 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 dalam konteks transaksi. Karena petunjuk kunci HOLDLOCK, pernyataan ini memperoleh dan mempertahankan kunci niat berbagi (IS) pada tabel (untuk ilustrasi ini, kunci baris dan halaman diabaikan). Kunci IS diperoleh hanya 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 SELECT yang berjalan di bawah transaksi ini memperoleh dan mempertahankan kunci bersama (S) pada tabel. Kunci S diperoleh pada semua partisi, yang menghasilkan beberapa kunci tabel, satu untuk setiap partisi. Misalnya, pada sistem 16-CPU, 16 S kunci akan dialokasikan di seluruh ID partisi kunci 0-15. Karena kunci S kompatibel dengan kunci IS yang ditahan pada ID partisi 7 oleh transaksi di sesi 1, tidak ada pemblokiran antara 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 mencoba memperoleh kunci X pada tabel. Namun, kunci S yang dipegang oleh transaksi di sesi 2 memblokir kunci X pada ID partisi 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Contoh B

Sesi 1:

Pernyataan SELECT dijalankan di dalam transaksi. Karena petunjuk kunci HOLDLOCK, pernyataan ini memperoleh dan mempertahankan kunci niat berbagi (IS) pada tabel (untuk ilustrasi ini, kunci baris dan halaman diabaikan). Kunci IS diperoleh hanya 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 dalam sebuah transaksi. Karena TABLOCKX 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 diperoleh pada semua ID partisi 0-5 tetapi diblokir oleh kunci IS yang diperoleh pada ID partisi 6.

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

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Tingkat isolasi berbasis versi baris pada Mesin Basis Data

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

Versi baris adalah kerangka kerja umum di SQL Server yang memanggil mekanisme salin-saat-menulis ketika sebuah 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:

• Bangun tabel inserted dan deleted dalam pemicu. Setiap baris yang dimodifikasi oleh pemicu diberi versi. Ini termasuk baris yang dimodifikasi oleh pernyataan yang menjalankan trigger, serta modifikasi data apa pun yang dibuat oleh trigger.
• Mendukung Beberapa Set Hasil Aktif (MARS). Jika sesi MARS mengeluarkan pernyataan modifikasi data (seperti INSERT, UPDATE, atau DELETE) sementara ada kumpulan hasil aktif, baris yang terpengaruh oleh pernyataan modifikasi akan dipindahkan.
• Mendukung operasi indeks yang menentukan opsi ONLINE.
• Mendukung tingkat isolasi transaksi berbasis penerapan versi baris:
  • Implementasi baru tingkat isolasi READ COMMITTED yang menggunakan versi baris untuk memastikan konsistensi baca pada tingkat pernyataan.
  • Tingkat isolasi baru, SNAPSHOT, untuk memberikan pembacaan konsisten pada tingkat transaksi.

Versi baris disimpan di penyimpanan versi. Jika pemulihan database dipercepat (ADR) diaktifkan pada sebuah database, maka penyimpanan versi akan dibuat di database tersebut. Jika tidak, penyimpanan versi akan dibuat di dalam database tempdb.

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

Ketika ADR 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 dan SNAPSHOT transaksi adalah proses dua langkah:

1. Atur salah satu atau kedua opsi database READ_COMMITTED_SNAPSHOT dan ALLOW_SNAPSHOT_ISOLATION ke ON.

2. Atur tingkat isolasi transaksi yang sesuai dalam aplikasi:

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

Salah satu dari opsi READ_COMMITTED_SNAPSHOT atau ALLOW_SNAPSHOT_ISOLATION diatur ke ON, Mesin Database menetapkan nomor urutan transaksi (XSN) ke setiap transaksi yang memanipulasi data menggunakan 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.

Ketika opsi READ_COMMITTED_SNAPSHOT atau ALLOW_SNAPSHOT_ISOLATION diatur ke ON, salinan logis (versi) dipertahankan untuk semua modifikasi data yang dilakukan di dalam database. Setiap kali baris dimodifikasi oleh transaksi tertentu, instance Mesin Basis Data menyimpan versi rekaman baris yang telah dikomit 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.

Note

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

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

Note

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 hanya bersifat sementara, maka baris tersebut akan dihilangkan dari kumpulan buffer dan tidak mengakibatkan beban I/O.

Perilaku saat membaca data

Saat transaksi yang berjalan di bawah isolasi berbasis versi baris membaca data, operasi baca tidak memperoleh kunci berbagi (S) pada data yang dibaca dan karena itu, tidak memblokir transaksi yang sedang memodifikasi data. Selain itu, overhead sumber daya penguncian diminimalkan karena jumlah kunci yang diperoleh berkurang. READ COMMITTED isolasi menggunakan versi baris dan SNAPSHOT isolasi dirancang untuk memberikan konsistensi baca pada tingkat pernyataan atau transaksi dari data yang sudah diberi 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 alasan ini, kueri diblokir ketika terdapat transaksi bersamaan yang 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 level isolasi berbasis versi baris, diblokir saat mencoba memperoleh Sch-S kunci. Sebaliknya, sebuah kueri yang memegang kunci Sch-S akan memblokir transaksi bersamaan yang mencoba untuk memperoleh kunci Sch-M.

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 dikomit pada saat transaksi SNAPSHOT dimulai. Ini menyediakan rekam jepret data yang konsisten secara transaksional seperti yang ada di awal transaksi.

READ COMMITTED transaksi menggunakan versi baris beroperasi dengan cara serupa. 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.

Note

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 berbeda ketika penguncian yang dioptimalkan diaktifkan dan tidak diaktifkan.

Mengubah data tanpa lock yang dioptimalkan

Dalam transaksi menggunakan versi baris READ COMMITTED, pemilihan baris yang akan diperbarui dilakukan dengan menggunakan pemindaian blokir di mana penguncian pembaruan (U) diperoleh pada baris data saat nilai data dibaca. Ini sama READ COMMITTED dengan transaksi yang tidak menggunakan versioning 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 dengan tujuan 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.

Note

Operasi pemutakhiran yang beroperasi dalam isolasi SNAPSHOT secara internal beroperasi dalam isolasi READ COMMITTED saat transaksi SNAPSHOT mengakses salah satu aspek berikut ini:

Tabel dengan batasan kunci asing.

Tabel yang menjadi rujukan dalam pengaturan kunci asing di tabel lain.

Tampilan terindeks yang mereferensikan lebih dari satu tabel.

Namun, bahkan dalam kondisi ini operasi pembaruan 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.

Modifikasi data dengan penguncian yang dioptimalkan

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

Direkomendasikan untuk mengaktifkan RCSI guna meningkatkan efisiensi terbaik dengan penguncian yang dioptimalkan. Saat menggunakan tingkat isolasi yang lebih ketat seperti REPEATABLE READ atau SERIALIZABLE, Mesin Basis Data menyimpan kunci baris dan halaman hingga akhir transaksi, baik untuk pembaca maupun penulis, mengakibatkan peningkatan pemblokiran dan kunci memori.

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

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

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

Note

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

Perilaku dalam ringkasan

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

Property	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 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 telah di-commit sebelum dimulainya eksekusi setiap pernyataan.	Semua data yang dilakukan sebelum dimulainya setiap transaksi.
Bagaimana pembaruan ditangani.	Tanpa penguncian yang dioptimalkan: Beralih kembali dari versi baris data ke data aktual untuk memilih baris yang akan diperbarui dan menggunakan kunci pembaruan pada baris data tersebut. Memperoleh kunci eksklusif pada baris data aktual yang akan dimodifikasi. Tidak ada deteksi konflik pembaruan sistem. Dengan penguncian yang dioptimalkan: Baris dipilih berdasarkan versi terakhir yang diterapkan tanpa kunci apa pun yang diperoleh. Jika baris memenuhi syarat untuk pembaruan, kunci baris atau halaman eksklusif akan diperoleh. Jika konflik pembaruan terdeteksi, konflik tersebut akan ditangani dan dicoba ulang 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 akan ditangani dan dicoba ulang secara otomatis.	Dukungan terintegrasi. Tidak dapat dinonaktifkan.

Penggunaan sumber daya versi baris

Kerangka kerja pengaturan versi baris mendukung fitur Mesin Basis Data berikut:

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

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

• Saat opsi database diatur ke ON, transaksi READ_COMMITTED menyediakan konsistensi baca tingkat pernyataan menggunakan versi baris.
• ALLOW_SNAPSHOT_ISOLATION Ketika opsi database diatur ke ON, transaksi SNAPSHOT menyediakan konsistensi baca tingkat transaksi dengan menggunakan versi baris.

Tingkat isolasi berbasis versi baris mengurangi jumlah kunci yang diperoleh oleh transaksi dengan cara 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 versi baris meningkatkan sumber daya yang diperlukan oleh modifikasi data. Mengaktifkan opsi ini menyebabkan semua modifikasi data pada database memiliki versi. Salinan data sebelum modifikasi disimpan di penyimpanan versi bahkan ketika tidak ada transaksi aktif yang menggunakan isolasi berbasis 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 Basis Data, ruang penyimpanan versi harus memiliki ruang yang cukup untuk menyimpan versi baris. Administrator database harus memastikan bahwa tempdb dan database lain (jika ADR diaktifkan) memiliki cukup ruang untuk mendukung penyimpanan versi. Ada dua jenis penyimpanan versi:

• Penyimpanan versi untuk pembuatan indeks online digunakan selama proses pembangunan indeks online.
• Penyimpanan versi umum digunakan untuk semua operasi modifikasi data lainnya.

Versi baris data harus disimpan selama transaksi aktif masih membutuhkan akses terhadapnya. Secara berkala, utas latar belakang membersihkan versi baris yang tidak lagi diperlukan dan mengosongkan ruang di ruang penyimpanan versi. Transaksi jangka panjang menghalangi pelepasan ruang di penyimpanan versi jika memenuhi salah satu kondisi berikut:

• Ini menggunakan pengisolasian berbasis versi baris.
• Ini menggunakan pemicu, MARS, atau operasi penyusunan indeks online.
• Ini menghasilkan versi baris.

Note

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 sebuah transaksi ditandai sebagai korban, transaksi tersebut tidak dapat lagi membaca versi baris di dalam penyimpanan versi. Saat mencoba membaca versi baris, pesan 3966 dihasilkan dan transaksi dibatalkan. 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 putar kembali penuh dan berakhir dengan kesalahan 3958.

Ruang yang digunakan dalam baris data

Setiap baris database mungkin menggunakan hingga 14 byte di akhir baris untuk informasi versi baris. Informasi versi baris berisi nomor urutan transaksi dari transaksi yang mengkomit versi dan penunjuk ke baris yang sudah diberi 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 pembangunan indeks secara daring saat ini sedang berjalan di tabel.
• Pemulihan database yang dipercepat (ADR) diaktifkan.

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

• READ_COMMITTED_SNAPSHOT dan ALLOW_SNAPSHOT_ISOLATION opsi diatur ke OFF.
• Pemicu tidak lagi ada di meja.
• MARS tidak digunakan.
• Operasi pembangunan indeks online sedang tidak berjalan.
• Pemulihan database yang dipercepat (ADR) dinonaktifkan.

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 oleh penyimpanan versi persisten (PVS)

Saat ADR diaktifkan, versi baris dapat disimpan di penyimpanan versi persisten (PVS) dengan salah satu cara berikut, tergantung pada ukuran baris sebelum modifikasi:

• Jika ukurannya kecil, seluruh versi baris lama disimpan sebagai bagian dari baris yang dimodifikasi.
• Jika ukurannya menengah, perbedaan antara versi baris lama dan baris yang dimodifikasi disimpan sebagai bagian dari baris yang dimodifikasi. Perbedaannya dibangun dengan cara yang memungkinkan mesin database merekonstruksi seluruh versi baris lama jika diperlukan.
• Jika ukurannya besar, seluruh versi baris lama disimpan dalam tabel internal terpisah.

Dua metode pertama disebut penyimpanan versi dalam baris. Metode terakhir disebut penyimpanan versi off-row. Saat versi dalam baris tidak lagi diperlukan, versi tersebut dihapus untuk mengosongkan ruang pada halaman. Demikian pula, halaman dalam tabel internal yang berisi versi off-row yang tidak lagi diperlukan dihapus oleh pembersih versi.

Menyimpan versi baris dalam satu baris mengoptimalkan transaksi dalam pengambilan data yang membutuhkan pembacaan versi baris. Jika versi disimpan dalam baris, baca terpisah halaman PVS di luar baris tidak perlu.

DMV sys.dm_db_index_physical_stats menyediakan informasi tentang jumlah dan jenis versi yang disimpan baik dalam baris maupun di luar baris untuk partisi dari sebuah indeks. Ukuran total data versi yang disimpan dalam baris dilaporkan di kolom total_inrow_version_payload_size_in_bytes.

Ukuran penyimpanan versi di luar baris dilaporkan di kolom persistent_version_store_size_kb di sys.dm_tran_persistent_version_store_stats DMV.

Penggunaan ruang 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 versi baris disimpan di setiap fragmen yang dipakai untuk menyimpan string besar ini. Fragmen data disimpan dalam sekumpulan halaman yang didedikasikan untuk objek besar dalam tabel.

Saat nilai besar baru ditambahkan ke database, nilai tersebut dialokasikan menggunakan maksimum 8040 byte data per fragmen. Versi-versi lama dari Mesin Database menyimpan hingga 8.080 byte data ntext, text, atau image per fragmen.

ntext, text, dan image data objek besar (LOB) yang sudah ada tidak diperbarui untuk menyediakan ruang bagi informasi 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 versi. Ini terjadi bahkan jika versi baris tidak dihasilkan. Setelah data LOB ditingkatkan, jumlah maksimum byte yang disimpan per fragmen berkurang dari 8.080 byte menjadi 8.040 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 mungkin menghasilkan sejumlah besar alokasi halaman dan aktivitas I/O tergantung pada ukuran data LOB. Ini mungkin juga menghasilkan sejumlah besar aktivitas pengelogan jika modifikasi dicatat sepenuhnya. "Operasi WRITETEXT dan UPDATETEXT dicatat secara minimal jika model pemulihan database tidak diatur ke FULL."

Ruang disk yang cukup harus dialokasikan untuk mengakomodasi persyaratan ini.

Mengawasi pengelolaan 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.

DMVs

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 aktivitas alokasi dan dealokasi halaman per sesi untuk database. Untuk informasi selengkapnya, lihat sys.dm_db_session_space_usage (Transact-SQL).

• sys.dm_db_task_space_usage. Menunjukkan aktivitas alokasi dan dealokasi halaman 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 pengguna terbesar dari penyimpanan versi. Hanya berlaku untuk penyimpanan versi di tempdb. 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 versi tempdb. Untuk informasi selengkapnya, lihat sys.dm_tran_version_store (Transact-SQL).

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

  Note

  Menjalankan kueri sys.dm_tran_top_version_generators dan sys.dm_tran_version_store bisa mahal, karena keduanya harus memindai seluruh penyimpanan versi, yang ukurannya bisa sangat besar. sys.dm_tran_version_store_space_usage efisien dan tidak mahal untuk dijalankan karena tidak menavigasi melalui rekaman penyimpanan versi individual, dan 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. Cuplikan berisi nomor urut transaksi aktif yang menggunakan 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 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 dengan sys.dm_tran_transactions_snapshot, kecuali hanya mengembalikan transaksi aktif untuk cuplikan 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 yang 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 ruang kosong, dalam kilobyte (KB), di dalam basis data tempdb. Harus ada cukup ruang kosong di tempdb untuk menangani penyimpanan versi yang mendukung isolasi snapshot.

  Rumus berikut memberikan perkiraan kasar tentang ukuran penyimpanan versi. Untuk transaksi jangka panjang, mungkin bermanfaat untuk memantau tingkat pembuatan dan pembersihan untuk memperkirakan ukuran maksimum dari 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 pembangunan indeks online. Karena operasi ini mungkin memakan waktu lama pada tabel yang sangat besar, pembangunan indeks secara daring menggunakan penyimpanan versi terpisah. Perkiraan ukuran penyimpanan versi build indeks online sebanding dengan jumlah data yang dimodifikasi dalam tabel, termasuk semua indeks, selama build indeks daring 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/detik). Memantau tingkat pembersihan versi dalam KB per detik di semua penyimpanan versi di tempdb.

  Note

  Informasi dari kecepatan Pembuatan Versi (KB/dtk) dan kecepatan Pembersihan Versi (KB/dtk) dapat digunakan untuk memprediksi tempdb kebutuhan ruang.

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

• Pembuatan Unit Penyimpanan Versi. Memantau jumlah total unit penyimpanan versi yang telah dibuat untuk penyimpanan versi baris sejak instance dimulai.

• Pemusnahan Unit Penyimpanan Versi. Memantau jumlah total unit penyimpanan versi yang dipangkas 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.

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

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

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

• Transaksi Versi NonSnapshot Memantau jumlah total transaksi aktif yang berupa transaksi nonsnapshot yang menghasilkan catatan versi.

  Note

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

Contoh tingkat isolasi berbasis versi baris

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

A. Menggunakan 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 memperbarui yang dijalankan oleh transaksi lain, dan terus membaca data dari baris versi, tanpa memperhatikan 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 pemversian baris

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

• Membaca data yang dimodifikasi setelah transaksi lain melakukan perubahan data.
• Dapat memperbarui data yang diubah oleh transaksi lain di mana transaksi SNAPSHOT tidak bisa melakukannya.

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 versi baris

Administrator basis data mengontrol pengaturan di tingkat basis data untuk versi baris dengan menggunakan opsi basis data READ_COMMITTED_SNAPSHOT dan ALLOW_SNAPSHOT_ISOLATION dalam pernyataan ALTER DATABASE.

READ_COMMITTED_SNAPSHOT Ketika opsi database diatur ke ON, mekanisme yang digunakan untuk mendukung opsi segera diaktifkan. Saat mengatur opsi READ_COMMITTED_SNAPSHOT, hanya koneksi yang menjalankan perintah ALTER DATABASE yang diizinkan untuk mengakses 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 memasuki status PENDING_OFF saat administrator database mengatur opsi ALLOW_SNAPSHOT_ISOLATION ke OFF.

Pernyataan Transact-SQL berikut memungkinkan 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	Description
OFF	Dukungan untuk transaksi isolasi SNAPSHOT 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 diselesaikan. Tidak ada SNAPSHOT transaksi isolasi yang diizinkan.
ON	Dukungan diaktifkan untuk transaksi isolasi SNAPSHOT. 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 akan dapat mengakses database ini. Transaksi yang sudah ada masih dapat mengakses database ini. Transaksi tulis yang ada masih menggunakan penerapan versi dalam database ini. PENDING_OFF Status tidak berubah menjadi OFF sampai semua SNAPSHOT transaksi yang dimulai ketika status isolasi database SNAPSHOT selesai ON.

Gunakan tampilan katalog sys.databases untuk menentukan status kedua opsi database versioning 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 dalam database master dan msdb, dan tidak dapat dinonaktifkan.

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

Menggunakan tingkat isolasi berbasis versi baris

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

Tingkat isolasi berbasis 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 versi baris dengan mengatur opsi database READ_COMMITTED_SNAPSHOT ke ON, seperti yang diperlihatkan dalam contoh kode berikut:

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

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

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

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  Saat menggunakan kueri lintas basis data, transaksi yang berjalan di bawah isolasi SNAPSHOT dapat mengakses tabel dalam basis data yang memiliki opsi basis data ALLOW_SNAPSHOT_ISOLATION yang diatur ke ON. Untuk mengakses tabel dalam database yang opsinya ALLOW_SNAPSHOT_ISOLATION tidak 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. Ketika pernyataan SELECT dijalankan di bawah isolasi SNAPSHOT, eksekusinya tidak berhasil.

  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 versi baris

Pertimbangkan batasan berikut saat bekerja dengan tingkat isolasi berbasis versi baris.

• READ_COMMITTED_SNAPSHOT tidak dapat diaktifkan di tempdb, , msdbatau 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:

  • Sebuah database dibuat baca saja setelah transaksi SNAPSHOT dimulai tetapi sebelum transaksi SNAPSHOT mengaksesnya.
  • Jika mengakses objek dari beberapa database, status database diubah sedemikian rupa sehingga pemulihan database terjadi setelah transaksi SNAPSHOT dimulai, tetapi sebelum transaksi SNAPSHOT mengakses database. Misalnya: database diatur ke OFFLINE lalu ke ONLINE, database secara otomatis ditutup dan dibuka kembali karena opsi AUTO_CLOSE 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 suatu pernyataan DDL memodifikasi objek, referensi apa pun yang bersamaan ke objek di bawah SNAPSHOT isolasi menyebabkan SNAPSHOT transaksi gagal. READ COMMITTED transaksi tidak memiliki batasan ini ketika READ_COMMITTED_SNAPSHOT opsi basis data 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.

  Note

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

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.

Note

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 LOCK_TIMEOUT memungkinkan aplikasi untuk mengatur waktu maksimum suatu perintah 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 menangani kesalahan, aplikasi dapat melanjutkan, tanpa menyadari bahwa instruksi 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 batas waktu dan mengambil tindakan perbaikan, seperti: secara otomatis mengirim ulang pernyataan yang diblokir atau mengembalikan seluruh transaksi.

Important

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 .
• Aplikasi ADO.NET yang menggunakan namespace Microsoft.Data.SqlClient atau System.Data.SqlClient, dapat menentukan opsi IsolationLevel dengan menggunakan metode SqlConnection.BeginTransaction.
• Aplikasi yang menggunakan ADO dapat mengatur Autocommit Isolation Levels properti .
• Saat memulai transaksi, aplikasi yang menggunakan OLE DB dapat memanggil ITransactionLocal::StartTransaction dengan isoLevel diatur ke tingkat isolasi transaksi yang diinginkan. Saat menentukan tingkat isolasi dalam mode autocommit, aplikasi yang menggunakan OLE DB dapat mengatur 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 menetapkan SERIALIZABLE tingkat isolasi:

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

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

Level isolasi dapat diubah untuk kueri individual atau statement DML, jika perlu, dengan menentukan hint 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 mungkin bervariasi dari hasil yang ditetapkan pada sistem Anda.

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

Berikut ini adalah 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 pernyataan SELECT, INSERT, UPDATE, DELETE, dan MERGE. Petunjuk menentukan jenis penguncian atau versi baris yang digunakan oleh instans Mesin Database untuk data tabel. Petunjuk penguncian tingkat tabel dapat digunakan ketika diperlukan kontrol yang lebih rinci terhadap jenis kunci yang didapatkan pada objek. Petunjuk penguncian ini menggantikan tingkat isolasi transaksi saat ini untuk sesi tersebut.

Note

Petunjuk penguncian tidak disarankan untuk digunakan saat penguncian yang dioptimalkan diaktifkan. Meskipun petunjuk tabel dan kueri dihormati, hal tersebut dapat mengurangi efektivitas penguncian yang telah dioptimalkan. Untuk informasi lebih lanjut, lihat Hindari petunjuk penguncian saat menggunakan penguncian yang dioptimalkan.

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

Note

Kami merekomendasikan agar 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 SELECT yang berjalan pada tingkat isolasi READ UNCOMMITTED atau menggunakan petunjuk NOLOCK tidak memperoleh kunci berbagi saat membaca data, tetapi mungkin terkadang meminta kunci saat membaca tampilan katalog sistem. Ini berarti sebuah pernyataan SELECT dapat diblokir ketika terdapat transaksi bersamaan yang memodifikasi metadata tabel.

Seperti yang ditunjukkan dalam contoh berikut, jika tingkat isolasi transaksi diatur ke SERIALIZABLE, dan petunjuk penguncian tingkat tabel NOLOCK digunakan dengan perintah SELECT, penguncian rentang kunci yang biasanya digunakan untuk mempertahankan transaksi SERIALIZABLE tidak akan 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 didapatkan dan merujuk HumanResources.Employee adalah kunci stabilitas skema Sch-S. Dalam hal ini, serialisasi tidak lagi dijamin.

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

Sesuaikan 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 mengubah tingkat penguncian default, kecuali jika pola akses tabel atau indeks dipahami dengan baik dan konsisten, dan ada masalah persaingan 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 beban 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 memblokir pembaca bersamaan agar tidak mengakses tabel saat pembaruan berjalan. Jika tugas batch hanya mengubah beberapa baris atau halaman, Anda dapat mengubah level penguncian untuk mengizinkan penguncian pada 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 Pedoman Kebuntuan.

Granularitas penguncian yang digunakan pada indeks dapat diatur menggunakan pernyataan CREATE INDEX dan ALTER INDEX. Selain itu, pernyataan CREATE TABLE dan ALTER TABLE dapat digunakan untuk mengatur granularitas penguncian pada batasan PRIMARY KEY dan UNIQUE. Untuk kompatibilitas mundur, prosedur tersimpan sp_indexoption sistem juga dapat mengatur granularitas. Untuk menampilkan opsi penguncian saat ini dari suatu indeks tertentu, gunakan fungsi INDEXPROPERTY. Kunci tingkat halaman, kunci tingkat baris, atau kedua jenis 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 luar dan dalam

Transaksi internal eksplisit dapat dimulai dalam transaksi eksternal eksplisit. 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 luar dan dalam. Jika TransProc dipanggil ketika transaksi aktif, hasil transaksi dalam TransProc dikendalikan oleh transaksi luar, dan pernyataannya INSERT diterapkan atau dibatalkan berdasarkan penerapan atau pembatalan transaksi luar. Jika TransProc dijalankan oleh proses yang tidak memiliki transaksi terbuka, COMMIT TRANSACTION di akhir prosedur akan mengkomit pernyataan INSERT.

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 inner 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 dikonfirmasi atau dibatalkan berdasarkan komitmen atau pembatalan di akhir transaksi terluar. Jika transaksi luar dilakukan, transaksi 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 BEGIN TRANSACTION yang terakhir dijalankan. Jika ada beberapa BEGIN TRANSACTION pernyataan, maka COMMIT pernyataan hanya berlaku untuk pernyataan terakhir, dengan kata lain untuk transaksi terdalu. Bahkan jika COMMIT TRANSACTION transaction_name pernyataan dalam transaksi batin mengacu pada nama transaksi dari transaksi luar, komitmen hanya berlaku untuk transaksi terdalam.

Tidak diperbolehkan untuk transaction_name parameter pernyataan ROLLBACK TRANSACTION merujuk ke transaksi dalam serangkaian transaksi yang diberi nama. transaction_name hanya dapat merujuk ke nama transaksi terluar.

Fungsi @@TRANCOUNT mencatat tingkat bersarang transaksi saat ini. Setiap BEGIN TRANSACTION pernyataan menambahkan @@TRANCOUNT dengan satu. Masing-masing pernyataan COMMIT TRANSACTION atau COMMIT WORK mengurangi @@TRANCOUNT dengan satu. Pernyataan ROLLBACK WORK atau ROLLBACK TRANSACTION yang tidak memiliki nama transaksi akan membatalkan outer dan semua transaksi inner dan mengurangi @@TRANCOUNT menjadi 0. Demikian pula, ROLLBACK TRANSACTION yang menggunakan nama transaksi terluar mengembalikan transaksi terluar dan semua transaksi dalam dan mengurangi @@TRANCOUNT ke 0. Untuk menentukan apakah Anda sudah dalam transaksi, lihat nilai SELECT @@TRANCOUNT untuk memastikan apakah itu 1 atau lebih. Jika @@TRANCOUNT 0, Anda tidak dalam transaksi.

Note

Mesin Database tidak mendukung transaksi berlapis yang dapat dikelola secara independen. Komitmen dari transaksi internal mengurangi @@TRANCOUNT, tetapi tidak memiliki efek lainnya. Pemutaran kembali transaksi dalam selalu mengembalikan transaksi luar, kecuali ada titik penyimpanan dan ditentukan dalam ROLLBACK pernyataan.

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.

Note

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-jenis sesi terikat

Dua jenis sesi terikat bersifat lokal dan terdistribusi.

• Sesi Terikat Lokal memungkinkan sesi-sesi terikat untuk berbagi ruang transaksi dari satu transaksi dalam satu instance Mesin Database.

• Sesi Terikat Terdistribusi memungkinkan sesi-sesi untuk berbagi transaksi yang sama di dua atau lebih instansi hingga seluruh transaksi diterapkan atau dibatalkan, menggunakan Koordinator Transaksi Terdistribusi Microsoft (MS DTC).

Sesi terikat terdistribusi tidak diidentifikasi oleh token ikat 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. Ketika proses pemanggilan melewatkan token pengikat sebagai salah satu parameter dari prosedur tersimpan yang diperluas, prosedur tersebut dapat bergabung ke dalam ruang transaksi dari proses pemanggilan, dengan demikian mengintegrasikan prosedur tersimpan yang diperluas dengan proses tersebut.

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. Pada suatu waktu tertentu, hanya satu sesi yang dapat melaksanakan tugas sebagai bagian dari transaksi; tidak 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 dengan 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 kontensi penguncian sumber daya di 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 hingga mereka merespons, yang mungkin tidak terjadi selama beberapa menit atau bahkan berjam-jam.

• Jangan buka 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. Hanya sedikit transaksi yang memerlukan SERIALIZABLE tingkat isolasi untuk transaksi.

• Gunakan opsi konkurensi optimis secara cerdas. Dalam sistem dengan probabilitas rendah terjadinya pembaruan bersamaan, biaya tambahan untuk menangani kesalahan "orang lain mengubah data Anda setelah Anda membacanya" bisa jauh lebih rendah daripada biaya tambahan untuk selalu mengunci baris saat baris tersebut dibaca.

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

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

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

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 level isolasi SNAPSHOT dinyalakan, meskipun transaksi baru tidak akan menahan lock, transaksi yang berjalan lama akan menghalangi penghapusan versi-versi lama 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 belum diselesaikan, fase pemulihan mulai ulang berikutnya dapat memakan waktu lebih lama dari waktu yang ditentukan oleh recovery interval opsi konfigurasi server atau oleh opsi ALTER DATABASE ... SET TARGET_RECOVERY_TIME. 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 yang menunggu mungkin menghasilkan log yang sangat sedikit, transaksi tersebut menahan pemotongan log tanpa batas waktu, menyebabkan log transaksi tumbuh dan mungkin terisi penuh. 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.

Important

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 lebih lanjut, lihat KILL (Transact-SQL).

Deadlocks

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 Versi Baris
• sys.dm_tran_locks (Transact-SQL)