Bagikan melalui

Penguncian yang dioptimalkan

  • Artikel

Berlaku untuk: Azure SQL Database

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

Apa itu penguncian yang dioptimalkan?

Penguncian yang dioptimalkan membantu mengurangi memori kunci karena sangat sedikit kunci yang ditahan bahkan 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, lihat Penguncian ID Transaksi (TID).
  • Kunci setelah kualifikasi (LAQ) adalah pengoptimalan yang mengevaluasi predikat kueri menggunakan versi baris terbaru yang diterapkan tanpa memperoleh kunci, sehingga meningkatkan konkurensi. Untuk informasi selengkapnya, lihat Kunci setelah kualifikasi (LAQ).

Contohnya:

  • Tanpa penguncian yang dioptimalkan, memperbarui seribu baris dalam tabel mungkin memerlukan seribu kunci baris eksklusif yangX disimpan hingga akhir transaksi.
  • Dengan penguncian yang dioptimalkan, memperbarui seribu baris dalam tabel mungkin memerlukan seribu X kunci baris tetapi setiap kunci dilepaskan segera setelah setiap baris diperbarui, dan hanya satu kunci TID yang ditahan hingga akhir transaksi. Karena kunci dilepaskan dengan cepat, penggunaan memori kunci berkurang dan eskalasi kunci jauh lebih kecil kemungkinannya terjadi, meningkatkan konkurensi beban kerja.

Catatan

Mengaktifkan penguncian yang dioptimalkan mengurangi atau menghilangkan kunci baris dan halaman yang diperoleh oleh pernyataan Bahasa Modifikasi Data (DML) seperti INSERT, , UPDATEDELETE, MERGE. Ini tidak berpengaruh pada jenis database dan kunci objek lainnya, seperti kunci skema.

Ketersediaan

Penguncian yang dioptimalkan hanya tersedia di Azure SQL Database, di semua tingkat layanan dan ukuran komputasi.

Penguncian yang dioptimalkan saat ini tidak tersedia di Azure SQL Managed Instance atau di SQL Server.

Apakah penguncian yang dioptimalkan diaktifkan?

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

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');
Hasil Deskripsi
0 Penguncian yang dioptimalkan dinonaktifkan.
1 Penguncian yang dioptimalkan diaktifkan.
NULL Penguncian yang dioptimalkan tidak tersedia.

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, sambungkan ke database dan jalankan 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 menjamin properti ACID transaksi.

Saat transaksi perlu memodifikasi data, transaksi 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 mencoba 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 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)

Saat tingkat isolasi berbasis penerapan versi baris sedang digunakan atau saat ADR diaktifkan, setiap baris dalam database secara internal berisi ID transaksi (TID). TID ini bertahan pada disk. Setiap transaksi memodifikasi stempel baris yang baris tersebut dengan TID-nya.

Dengan penguncian TID, alih-alih mengambil kunci pada kunci baris, kunci diambil pada TID baris. Transaksi yang memodifikasi memegang X kunci pada TID-nya. Transaksi lain memperoleh S kunci pada TID untuk menunggu hingga transaksi pertama selesai. Dengan penguncian TID, kunci halaman dan baris terus diambil untuk modifikasi, tetapi setiap kunci halaman dan baris dirilis segera setelah setiap baris dimodifikasi. Satu-satunya kunci yang ditahan hingga akhir transaksi adalah kunci tunggal X pada sumber daya TID, menggantikan beberapa kunci halaman dan baris (kunci).

Pertimbangkan contoh berikut yang menunjukkan kunci untuk sesi saat ini saat transaksi tulis aktif:

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

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

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

BEGIN TRANSACTION;

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 TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

Jika penguncian yang dioptimalkan diaktifkan, permintaan hanya menyimpan satu X kunci pada XACT sumber daya (transaksi).

Cuplikan layar kumpulan hasil kueri pada sys.dm_tran_locks untuk satu sesi hanya memperlihatkan satu kunci saat penguncian yang dioptimalkan diaktifkan.

Jika penguncian yang dioptimalkan tidak diaktifkan, permintaan yang sama menyimpan empat kunci - tiga X kunci kunci pada setiap baris dan satu IX kunci (niat eksklusif) pada halaman yang berisi baris:

Cuplikan layar kumpulan hasil kueri pada sys.dm_tran_locks untuk satu sesi memperlihatkan tiga kunci saat penguncian yang dioptimalkan tidak diaktifkan.

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

Penguncian dan penguncian yang dioptimalkan setelah kualifikasi (LAQ)

Membangun infrastruktur TID, penguncian yang dioptimalkan mengubah bagaimana pernyataan DML seperti INSERT, , UPDATEDELETE, dan MERGE memperoleh kunci.

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

Dengan penguncian yang dioptimalkan, dan ketika READ COMMITTED tingkat isolasi rekam jepret (RCSI) diaktifkan, predikat diperiksa pada versi baris terbaru yang diterapkan tanpa mengambil kunci apa pun. Jika predikat tidak terpenuhi, kueri akan berpindah ke baris berikutnya dalam pemindaian. Jika predikat terpenuhi, X kunci baris diambil untuk memperbarui baris. Kunci X baris 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 memblokir satu sama lain.

Contohnya:

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 TRANSACTION;
UPDATE t1
SET b = b + 10
WHERE a = 1;
BEGIN TRANSACTION;
UPDATE t1
SET b = b + 10
WHERE a = 2;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

Tanpa penguncian yang dioptimalkan, sesi 2 diblokir karena sesi 1 memegang U kunci pada sesi baris 2 perlu diperbarui. Namun, dengan penguncian yang dioptimalkan, sesi 2 tidak diblokir karena U kunci tidak diambil, dan karena dalam versi terbaru yang diterapkan dari baris 1, kolom a sama dengan 1, yang tidak memenuhi predikat sesi 2.

Karena dengan kunci LAQ U tidak diambil, transaksi bersamaan mungkin mengubah baris setelah predikat dievaluasi. Jika predikat terpenuhi dan tidak ada transaksi aktif lainnya pada baris (tidak ada X kunci TID), baris dimodifikasi. Jika ada transaksi aktif, Mesin Database menunggunya selesai, dan mengevaluasi kembali predikat lagi pada saat modifikasi karena transaksi lain mungkin telah memodifikasi baris. Jika predikat masih terpenuhi, baris dimodifikasi.

Pertimbangkan contoh berikut di mana evaluasi predikat dicoba kembali secara otomatis karena transaksi lain telah mengubah baris:

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

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

Perubahan perilaku kueri dengan penguncian yang dioptimalkan dan RCSI

Beban kerja bersamaan di bawah isolasi rekam jepret yang diterapkan baca (RCSI) yang mengandalkan urutan eksekusi transaksi yang ketat mungkin mengalami perbedaan perilaku kueri saat penguncian yang dioptimalkan diaktifkan.

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

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO
Sesi 1 Sesi 2
BEGIN TRANSACTION T1;
UPDATE t4
SET b = 2
WHERE a = 1;
BEGIN TRANSACTION T2;
UPDATE t4
SET b = 3
WHERE b = 2;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

Mari kita evaluasi hasil skenario di atas dengan dan tanpa kunci setelah kualifikasi (LAQ).

Tanpa LAQ

Tanpa LAQ, UPDATE pernyataan dalam transaksi T2 diblokir, menunggu transaksi T1 selesai. Setelah T1 selesai, T2 memperbarui kolom b pengaturan baris karena 3 predikatnya terpenuhi.

Setelah kedua transaksi diterapkan, tabel t4 berisi baris berikut:

 a | b
 1 | 3

Dengan LAQ

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

Setelah kedua transaksi diterapkan, tabel t4 berisi baris berikut:

 a | b
 1 | 2

Penting

Bahkan tanpa LAQ, aplikasi tidak boleh berasumsi bahwa Mesin Database menjamin pengurutan yang ketat tanpa menggunakan petunjuk penguncian saat tingkat isolasi berbasis penerapan versi baris digunakan. Rekomendasi umum kami untuk pelanggan yang menjalankan beban kerja bersamaan di bawah RCSI yang mengandalkan urutan eksekusi transaksi yang ketat (seperti yang ditunjukkan dalam contoh sebelumnya) adalah menggunakan tingkat isolasi yang lebih ketat seperti REPEATABLE READ dan SERIALIZABLE.

Penambahan diagnostik untuk penguncian yang dioptimalkan

Peningkatan berikut membantu Anda memantau dan memecahkan masalah pemblokiran dan kebuntuan saat penguncian yang dioptimalkan diaktifkan:

  • Jenis tunggu untuk penguncian yang dioptimalkan
    • XACT jenis tunggu untuk S kunci pada TID, 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 XACT wait_resource jenis, dengan niat untuk membaca.
      • LCK_M_S_XACT_MODIFY - Terjadi ketika tugas sedang menunggu kunci bersama pada XACT wait_resource jenis, dengan niat untuk memodifikasi.
      • LCK_M_S_XACT - Terjadi ketika tugas sedang menunggu kunci bersama pada XACT wait_resource jenis, di mana niat tidak dapat disimpulkan. Ini tidak umum.
  • 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 READ COMMITTED isolasi sebagai tingkat isolasi default. Jika belum diaktifkan, aktifkan RCSI dengan menyambungkan ke master database dan jalankan pernyataan berikut:

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

Di Azure SQL Database, RCSI diaktifkan secara default dan READ COMMITTED merupakan tingkat isolasi default. Dengan RCSI diaktifkan dan saat menggunakan READ COMMITTED tingkat isolasi, pembaca membaca versi baris dari rekam jepret yang diambil pada awal pernyataan. Dengan LAQ, penulis memenuhi syarat baris per predikat berdasarkan versi baris terbaru yang diterapkan dan tanpa memperoleh U kunci. Dengan LAQ, kueri hanya 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 berkurang. Ini karena pembaca tidak mengambil kunci apa pun, dan penulis hanya mengambil kunci durasi pendek, alih-alih kunci yang ditahan hingga akhir transaksi. Saat menggunakan tingkat isolasi yang lebih ketat seperti REPEATABLE READ atau SERIALIZABLE, Mesin Database menahan baris dan kunci halaman hingga akhir transaksi bahkan dengan penguncian yang dioptimalkan diaktifkan, untuk pembaca dan penulis, menghasilkan peningkatan pemblokiran dan mengunci penggunaan memori.

Hindari mengunci petunjuk

Meskipun petunjuk tabel dan kueri seperti UPDLOCK, , READCOMMITTEDLOCKXLOCK, HOLDLOCK, dll. dihormati ketika penguncian yang dioptimalkan diaktifkan, mereka mengurangi manfaat penguncian yang dioptimalkan. Petunjuk kunci memaksa Mesin Database untuk mengambil kunci baris atau 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 UPDLOCK petunjuk 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 manfaat petunjuk dari penguncian yang dioptimalkan sebagian besar.

Petunjuk tabel pada satu tabel dalam kueri tidak menonaktifkan penguncian yang dioptimalkan untuk tabel lain dalam kueri yang sama. Selanjutnya, penguncian yang dioptimalkan hanya memengaruhi perilaku penguncian tabel yang diperbarui oleh pernyataan DML seperti INSERT, , UPDATEDELETE, atau MERGE. Contohnya:

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

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

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

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

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

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

Pertanyaan Umum (FAQ)

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 READCOMMITTEDLOCK petunjuk kueri.

Apakah penguncian yang dioptimalkan digunakan pada replika sekunder baca-saja?

Tidak, karena pernyataan DML tidak dapat berjalan pada replika baca-saja, dan baris dan kunci halaman yang sesuai tidak diambil.

Apakah penguncian yang dioptimalkan digunakan saat memodifikasi data dalam tempdb dan dalam tabel sementara?

Tidak untuk saat ini.

Konten terkait