Penguncian yang dioptimalkan

  • Artikel

Berlaku untuk:Azure SQL Database

Artikel ini memperkenalkan fitur penguncian yang dioptimalkan, kemampuan Mesin Database SQL Server baru yang menawarkan mekanisme penguncian transaksi yang ditingkatkan yang mengurangi konsumsi memori kunci dan pemblokiran untuk transaksi bersamaan.

Apa itu penguncian yang dioptimalkan?

Penguncian yang dioptimalkan membantu mengurangi memori kunci karena sangat sedikit kunci yang ditahan untuk transaksi besar. Selain itu, penguncian yang dioptimalkan juga menghindari eskalasi kunci. Ini memungkinkan akses yang lebih bersamaan ke tabel.

Penguncian yang dioptimalkan terdiri dari dua komponen utama: Penguncian dan penguncian ID Transaksi (TID) setelah kualifikasi (LAQ).

  • ID transaksi (TID) adalah pengidentifikasi unik transaksi. Setiap baris diberi label dengan TID terakhir yang memodifikasinya. Alih-alih berpotensi banyak kunci pengidentifikasi kunci atau baris, kunci tunggal pada TID digunakan. Untuk informasi selengkapnya, tinjau bagian tentang penguncian ID Transaksi (TID).
  • Kunci setelah kualifikasi (LAQ) adalah pengoptimalan yang mengevaluasi predikat kueri pada versi baris terbaru yang diterapkan tanpa memperoleh kunci, sehingga meningkatkan konkurensi. Untuk informasi selengkapnya, tinjau bagian di Kunci setelah kualifikasi (LAQ).

Contohnya:

  • Tanpa penguncian yang dioptimalkan, memperbarui 1 juta baris dalam tabel mungkin memerlukan 1 juta kunci baris eksklusif (X) yang ditahan hingga akhir transaksi.
  • Dengan penguncian yang dioptimalkan, memperbarui 1 juta baris dalam tabel mungkin memerlukan 1 juta kunci baris X tetapi setiap kunci dirilis segera setelah setiap baris diperbarui, dan hanya satu kunci TID yang akan ditahan hingga akhir transaksi.

Artikel ini membahas dua konsep inti penguncian yang dioptimalkan ini secara rinci.

Ketersediaan

Saat ini, penguncian yang dioptimalkan hanya tersedia di Azure SQL Database. Untuk informasi selengkapnya, lihat Di mana penguncian yang dioptimalkan saat ini tersedia?

Apakah penguncian yang dioptimalkan diaktifkan?

Penguncian yang dioptimalkan diaktifkan per database pengguna. Koneksi ke database Anda, lalu gunakan kueri berikut untuk memeriksa apakah penguncian yang dioptimalkan diaktifkan pada database Anda:

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX('testdb', 'IsOptimizedLockingOn');

Jika Anda tidak tersambung ke database yang ditentukan dalam DATABASEPROPERTYEX, hasilnya adalah NULL. Anda harus menerima 0 (penguncian yang dioptimalkan dinonaktifkan) atau 1 (diaktifkan).

Build penguncian yang dioptimalkan pada fitur database lainnya:

ADR dan RCSI diaktifkan secara default di Azure SQL Database. Untuk memverifikasi bahwa opsi ini diaktifkan untuk database Anda saat ini, gunakan kueri T-SQL berikut:

SELECT name
, is_read_committed_snapshot_on
, is_accelerated_database_recovery_on
FROM  sys.databases
WHERE name = db_name();

Gambaran umum penguncian

Ini adalah ringkasan singkat perilaku ketika penguncian yang dioptimalkan tidak diaktifkan. Untuk informasi selengkapnya, tinjau panduan Penguncian transaksi dan penerapan versi baris.

Di Mesin Database, penguncian adalah mekanisme yang mencegah beberapa transaksi memperbarui data yang sama secara bersamaan, untuk melindungi integritas dan konsistensi data.

Saat transaksi perlu memodifikasi data, transaksi dapat meminta kunci pada data. Kunci diberikan jika tidak ada kunci lain yang bertentangan yang disimpan pada data, dan transaksi dapat dilanjutkan dengan modifikasi. Jika kunci lain yang bertentangan ditahan pada data, transaksi harus menunggu kunci dirilis sebelum dapat dilanjutkan.

Ketika beberapa transaksi diizinkan untuk mengakses data yang sama secara bersamaan, Mesin Database harus menyelesaikan konflik yang berpotensi kompleks dengan baca dan tulis bersamaan. Penguncian adalah salah satu mekanisme di mana mesin database dapat menyediakan semantik untuk tingkat isolasi transaksi ANSI SQL. Meskipun penguncian dalam database sangat penting, berkurangnya konkurensi, kebuntuan, kompleksitas, dan overhead kunci dapat memengaruhi performa dan skalabilitas.

Penguncian yang dioptimalkan dan penguncian ID transaksi (TID)

Setiap baris di Mesin Database secara internal berisi ID transaksi (TID) saat penerapan versi baris sedang digunakan. TID ini bertahan pada disk. Setiap transaksi yang memodifikasi baris akan memberi stempel baris tersebut dengan TID-nya.

Dengan penguncian TID, alih-alih mengambil kunci pada kunci baris, kunci diambil pada TID baris. Transaksi yang memodifikasi akan menahan kunci X pada TID-nya. Transaksi lain akan memperoleh kunci S pada TID untuk memeriksa apakah transaksi pertama masih aktif. Dengan penguncian TID, kunci halaman dan baris terus diambil untuk pembaruan, tetapi setiap halaman dan kunci baris dirilis segera setelah setiap baris diperbarui. Satu-satunya kunci yang ditahan hingga akhir transaksi adalah kunci X pada sumber daya TID, mengganti kunci halaman dan baris (kunci) seperti yang ditunjukkan dalam demo berikutnya. (Database standar dan kunci objek lainnya tidak terpengaruh oleh penguncian yang dioptimalkan.)

Penguncian yang dioptimalkan membantu mengurangi memori kunci karena sangat sedikit kunci yang ditahan untuk transaksi besar. Selain itu, penguncian yang dioptimalkan juga menghindari eskalasi kunci. Ini memungkinkan transaksi bersamaan lainnya untuk mengakses tabel.

Pertimbangkan skenario sampel T-SQL berikut yang mencari kunci pada sesi pengguna saat ini:

CREATE TABLE t0
(a int PRIMARY KEY not null
,b int null);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRAN
UPDATE t0
SET b=b+10;

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
AND resource_type in ('PAGE','RID','KEY','XACT');

COMMIT TRAN
GO
DROP TABLE IF EXISTS t0;

A screenshot of the result set of a query on sys.dm_tran_locks for a single session shows only one lock when optimized locking is enabled.

Kueri yang sama tanpa manfaat penguncian yang dioptimalkan membuat empat kunci:

A screenshot of the result set of a query on sys.dm_tran_locks for a single session shows three locks when optimized locking is not enabled.

Tampilan manajemen dinamis (DMV) sys.dm_tran_locks dapat berguna dalam memeriksa atau memecahkan masalah penguncian, termasuk mengamati penguncian yang dioptimalkan dalam tindakan.

Penguncian dan penguncian yang dioptimalkan setelah kualifikasi (LAQ)

Membangun infrastruktur TID, penguncian yang dioptimalkan mengubah cara kueri menandakan kunci aman.

Tanpa penguncian yang dioptimalkan, predikat dari kueri dicentang baris demi baris dalam pemindaian dengan terlebih dahulu mengambil kunci baris pembaruan (U). Jika predikat terpenuhi, kunci baris X diambil sebelum memperbarui baris.

Dengan penguncian yang dioptimalkan, dan ketika tingkat isolasi rekam jepret yang diterapkan baca (RCSI) diaktifkan, predikat diterapkan pada versi berkomitmen terbaru tanpa mengambil kunci baris apa pun. Jika predikat tidak terpenuhi, kueri akan berpindah ke baris berikutnya dalam pemindaian. Jika predikat terpenuhi, kunci baris X diambil untuk benar-benar memperbarui baris. Kunci baris X dirilis segera setelah pembaruan baris selesai, sebelum akhir transaksi.

Karena evaluasi predikat dilakukan tanpa memperoleh kunci apa pun, kueri bersamaan yang memodifikasi baris yang berbeda tidak akan memblokir satu sama lain.

Contoh:

CREATE TABLE t1
(a int not null
,b int null);

INSERT INTO t1 VALUES (1,10),(2,20),(3,30);
GO
Sesi 1 Sesi 2
BEGIN TRAN
UPDATE t1
SET b=b+10
WHERE a=1;
BEGIN TRAN
UPDATE t1
SET b=b+10
WHERE a=2;
COMMIT TRAN
COMMIT TRAN

Perilaku pemblokiran berubah dengan penguncian yang dioptimalkan dalam contoh sebelumnya. Tanpa penguncian yang dioptimalkan, Sesi 2 akan diblokir.

Namun, dengan penguncian yang dioptimalkan, Sesi 2 tidak akan diblokir karena versi terbaru yang diterapkan dari baris 1 berisi a=1, yang tidak memenuhi predikat Sesi 2.

Jika predikat terpenuhi, kami menunggu transaksi aktif pada baris selesai. Jika kita harus menunggu kunci S TID, baris mungkin telah berubah, dan versi terbaru yang diterapkan mungkin telah berubah. Dalam hal ini, alih-alih membatalkan transaksi karena konflik pembaruan, Mesin Database akan mencoba kembali evaluasi predikat pada baris yang sama. Jika predikat memenuhi syarat saat mencoba kembali, baris akan diperbarui.

Pertimbangkan contoh berikut ketika perubahan predikat dicoba kembali secara otomatis:

CREATE TABLE t2
(a int not null
,b int null);

INSERT INTO t2 VALUES (1,10),(2,20),(3,30);
GO
Sesi 1 Sesi 2
BEGIN TRAN
UPDATE t2
SET b=b+10
WHERE a=1;
BEGIN TRAN
UPDATE t2
SET b=b+10
WHERE a=1;
COMMIT TRAN
COMMIT TRAN

Perubahan perilaku kueri dengan penguncian yang dioptimalkan dan RCSI

Sistem bersamaan di bawah tingkat isolasi rekam jepret yang diterapkan baca (RCSI) dengan beban kerja yang mengandalkan urutan eksekusi transaksi yang ketat, mungkin mengalami perilaku kueri yang berbeda saat penguncian yang dioptimalkan diaktifkan.

Pertimbangkan contoh berikut di mana transaksi T2 memperbarui tabel t1 berdasarkan kolom b yang diperbarui selama transaksi T1.

CREATE TABLE t1 (a int not null, b int null);

INSERT INTO t1 VALUES (1,1);
GO
Sesi 1 Sesi 2
MULAI TRAN T1
PERBARUI t1
SET b=2
WHERE a=1;
MULAI TRAN T2
PERBARUI t1
SET b=3
WHERE b=2;
TERAPKAN TRAN
TERAPKAN TRAN

Mari kita evaluasi hasil skenario di atas dengan dan tanpa kunci setelah kualifikasi (LAQ), bagian integral dari penguncian yang dioptimalkan.

Tanpa LAQ

Tanpa LAQ, transaksi T2 akan diblokir dan menunggu transaksi T1 selesai.

Setelah kedua transaksi diterapkan, tabel t1 akan berisi baris berikut:

 a | b
 1 | 3

Dengan LAQ

Dengan LAQ, transaksi T2 akan menggunakan versi berkomitmen terbaru dari baris b (b=1 di penyimpanan versi) untuk mengevaluasi predikatnya (b=2). Baris ini tidak memenuhi syarat; oleh karena itu dilewati dan T2 berpindah ke baris berikutnya tanpa diblokir oleh transaksi T1. Dalam contoh ini, LAQ menghapus pemblokiran tetapi mengarah ke hasil yang berbeda.

Setelah kedua transaksi diterapkan, tabel t1 akan berisi baris berikut:

 a | b
 1 | 2

Penting

Bahkan tanpa LAQ, aplikasi tidak boleh berasumsi bahwa SQL Server (di bawah tingkat isolasi penerapan versi) akan menjamin pemesanan yang ketat, tanpa menggunakan petunjuk penguncian. Rekomendasi umum kami untuk pelanggan tentang sistem bersamaan di bawah RCSI dengan beban kerja yang mengandalkan urutan eksekusi transaksi yang ketat (seperti yang ditunjukkan pada latihan sebelumnya), adalah menggunakan tingkat isolasi yang lebih ketat.

Penambahan diagnostik untuk penguncian yang dioptimalkan

Untuk mendukung pemantauan dan pemecahan masalah pemblokiran dan kebuntuan dengan penguncian yang dioptimalkan, cari penambahan berikut:

  • Jenis tunggu untuk penguncian yang dioptimalkan
    • XACT jenis tunggu dan deskripsi sumber daya di sys.dm_os_wait_stats (Transact-SQL):
      • LCK_M_S_XACT_READ - Terjadi ketika tugas sedang menunggu kunci bersama pada jenis XACT wait_resource , dengan niat untuk membaca.
      • LCK_M_S_XACT_MODIFY - Terjadi ketika tugas menunggu kunci bersama pada jenis XACT wait_resource , dengan niat untuk memodifikasi.
      • LCK_M_S_XACT - Terjadi ketika tugas menunggu kunci bersama pada jenis XACT wait_resource , di mana niat tidak dapat disimpulkan. Jarang.
  • Mengunci visibilitas sumber daya
  • Visibilitas sumber daya tunggu
  • Grafik kebuntuan
    • Di bawah setiap sumber daya dalam laporan <resource-list>kebuntuan , setiap <xactlock> elemen melaporkan sumber daya yang mendasar dan informasi spesifik untuk kunci setiap anggota kebuntuan. Untuk informasi selengkapnya dan contohnya, lihat Penguncian dan kebuntuan yang dioptimalkan.

Praktik terbaik dengan penguncian yang dioptimalkan

Aktifkan isolasi rekam jepret yang diterapkan baca (RCSI)

Untuk memaksimalkan manfaat penguncian yang dioptimalkan, disarankan untuk mengaktifkan isolasi rekam jepret yang diterapkan baca (RCSI) pada database dan menggunakan isolasi yang diterapkan baca sebagai tingkat isolasi default. Jika tidak diaktifkan, aktifkan RCSI menggunakan sampel berikut:

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

Di Azure SQL Database, RCSI diaktifkan secara default dan penerapan baca adalah tingkat isolasi default. Dengan RCSI diaktifkan dan saat menggunakan tingkat isolasi yang diterapkan baca, pembaca tidak memblokir penulis dan penulis tidak memblokir pembaca. Pembaca membaca versi baris dari rekam jepret yang diambil di awal kueri. Dengan LAQ, penulis akan memenuhi syarat baris per predikat berdasarkan versi baris terbaru yang diterapkan tanpa memperoleh kunci U. Dengan LAQ, kueri hanya akan menunggu jika baris memenuhi syarat dan ada transaksi tulis aktif pada baris tersebut. Memenuhi syarat berdasarkan versi berkomitmen terbaru dan hanya mengunci baris yang memenuhi syarat yang mengurangi pemblokiran dan meningkatkan konkurensi.

Selain pengurangan pemblokiran, memori kunci yang diperlukan akan berkurang. Ini karena pembaca tidak mengambil kunci apa pun, dan penulis hanya mengambil kunci durasi pendek, alih-alih kunci yang kedaluwarsa di akhir transaksi. Saat menggunakan tingkat isolasi yang lebih ketat seperti bacaan yang dapat diulang atau dapat diserialisasikan, Mesin Database dipaksa untuk menahan kunci baris dan halaman hingga akhir transaksi, untuk pembaca dan penulis, menghasilkan peningkatan pemblokiran dan kunci memori.

Hindari mengunci petunjuk

Meskipun petunjuk tabel dan kueri dihormati, petunjuk tersebut mengurangi manfaat penguncian yang dioptimalkan. Petunjuk kunci seperti UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK, dll., dalam kueri Anda mengurangi manfaat penuh penguncian yang dioptimalkan. Memiliki petunjuk kunci seperti itu dalam kueri memaksa Mesin Database untuk mengambil kunci baris/halaman dan menahannya hingga akhir transaksi, untuk menghormati niat petunjuk kunci. Beberapa aplikasi memiliki logika di mana petunjuk kunci diperlukan, misalnya saat membaca baris dengan pilih dengan UPDLOCK lalu memperbaruinya nanti. Sebaiknya gunakan petunjuk kunci hanya jika diperlukan.

Dengan penguncian yang dioptimalkan, tidak ada batasan pada kueri dan kueri yang ada tidak perlu ditulis ulang. Kueri yang tidak menggunakan petunjuk akan mendapat manfaat paling besar dari penguncian yang dioptimalkan.

Petunjuk tabel pada satu tabel dalam kueri tidak akan menonaktifkan penguncian yang dioptimalkan untuk tabel lain dalam kueri yang sama. Selanjutnya, penguncian yang dioptimalkan hanya memengaruhi perilaku penguncian tabel yang diperbarui oleh pernyataan UPDATE. Contohnya:

CREATE TABLE t3
(a int not null
, b int not null);

CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;

Dalam contoh kueri sebelumnya, hanya tabel t4 yang akan dipengaruhi oleh petunjuk penguncian, sementara t3 masih bisa mendapat manfaat dari penguncian yang dioptimalkan.

UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;

Dalam contoh kueri sebelumnya, hanya tabel t3 yang akan menggunakan tingkat isolasi baca yang dapat diulang, dan akan menahan kunci hingga akhir transaksi. Pembaruan lain untuk t3 masih dapat memperoleh manfaat dari penguncian yang dioptimalkan. Hal yang sama berlaku untuk petunjuk HOLDLOCK.

Pertanyaan yang sering diajukan (FAQ)

Di mana penguncian yang dioptimalkan saat ini tersedia?

Saat ini, penguncian yang dioptimalkan tersedia di Azure SQL Database.

Penguncian yang dioptimalkan tersedia di tingkat layanan berikut:

  • semua tingkat layanan DTU
  • semua tingkat layanan vCore, termasuk yang disediakan dan tanpa server

Penguncian yang dioptimalkan saat ini tidak tersedia di:

  • Instans Terkelola Azure SQL
  • SQL Server 2022 (16.x)

Apakah penguncian dioptimalkan secara default di database baru dan yang sudah ada?

Di Azure SQL Database, ya.

Bagaimana cara mendeteksi apakah penguncian yang dioptimalkan diaktifkan?

Lihat Apakah penguncian yang dioptimalkan diaktifkan?

Apa yang terjadi ketika pemulihan database terakselerasi (ADR) tidak diaktifkan di database saya?

Jika ADR dinonaktifkan, penguncian yang dioptimalkan juga akan dinonaktifkan secara otomatis.

Bagaimana jika saya ingin memaksa kueri untuk memblokir meskipun penguncian dioptimalkan?

Bagi pelanggan yang menggunakan RCSI, untuk memaksa pemblokiran antara dua kueri saat penguncian yang dioptimalkan diaktifkan, gunakan petunjuk kueri READCOMMITTEDLOCK.

Dapatkah saya menonaktifkan penguncian yang dioptimalkan?

Saat ini, pelanggan dapat membuat permintaan dukungan untuk menonaktifkan penguncian yang dioptimalkan.

Gunakan langkah-langkah berikut untuk membuat permintaan dukungan baru dari portal Azure untuk Azure SQL Database.

  1. Pertama, verifikasi bahwa penguncian yang dioptimalkan diaktifkan untuk database Anda.

  2. Pada menu portal Microsoft Azure, pilih Bantuan + dukungan.

    A screenshot of the Azure portal identifying the help and support link.

  3. Di Bantuan + dukungan, pilih Buat permintaan dukungan.

    A screenshot of the Azure portal showing how to create a new support request.

  4. Untuk Jenis masalah, pilih Teknis.

  5. Untuk Langganan, Layanan, dan Sumber Daya, pilih SQL Database yang diinginkan.

  6. Di Ringkasan, ketik "Nonaktifkan penguncian yang dioptimalkan".

  7. Untuk Jenis Masalah, pilih Performa dan Eksekusi Kueri.

  8. Untuk Subjenis Masalah, pilih Pemblokiran dan kebuntuan.

  9. Dalam Detail tambahan, berikan informasi sebanyak mungkin untuk alasan Anda ingin menonaktifkan penguncian yang dioptimalkan. Kami tertarik untuk meninjau alasan dan kasus penggunaan untuk menonaktifkan penguncian yang dioptimalkan dengan Anda.

Konten terkait