İyileştirilmiş kilitleme

Şunlar için geçerlidir: Microsoft Fabric'da Azure SQL VeritabanıSQL veritabanı

Bu makalede, kilit bellek tüketimini azaltan ve eşzamanlı işlemler için engellemeyi azaltan gelişmiş bir işlem kilitleme mekanizması sunan yeni bir veritabanı altyapısı özelliği olan iyileştirilmiş kilitleme özelliği tanıtılır.

İyileştirilmiş kilitleme nedir?

Optimizasyonlu kilitleme, büyük işlemler için bile çok az sayıda kilit tutularak kilit belleğinin azaltılmasına yardımcı olur. İyileştirilmiş kilitleme, ayrıca kilit yükseltmelerini de önler. Bu, tabloya daha fazla eşzamanlı erişim sağlar.

Optimize edilmiş kilitleme, iki birincil bileşenden oluşur: işlem tanımlayıcısı (TID) kilitleme ve nitelemeden sonra kilitleme (LAQ).

• İşlem kimliği (TID), bir işlemin benzersiz tanımlayıcısıdır. Her satır, üzerinde değişiklik yapan son TID ile işaretlenmiştir. Olası olarak çok sayıda anahtar veya satır tanımlayıcısı kilidi yerine TID üzerindeki tek bir kilit kullanılır. Daha fazla bilgi için bkz. İşlem Kimliği (TID) kilitleme.
• Nitelendirmeden Sonra Kilitleme (LAQ), bir kilit edinmeden satırın en son onaylanmış sürümünü kullanarak sorgu koşullarını değerlendiren ve böylece eşzamanlılığı artıran bir iyileştirmedir. Daha fazla bilgi için bkz. Vasıflandırmadan sonra kilitleme (LAQ).

Mesela:

• İyileştirilmiş kilitleme olmadan, bir tablodaki 1.000 satırı güncelleştirmek, işlemin sonuna kadar tutulan 1.000 özel (X) satır kilidi gerektirebilir.
• İyileştirilmiş kilitleme ile, bir tablodaki 1.000 satırı güncelleştirmek için 1.000 X satır kilidi gerekebilir, ancak her satır güncelleştirildiğinde her kilit serbest bırakılır ve işlemin sonuna kadar yalnızca bir TID kilidi tutulur. Kilitler hızlı bir şekilde yayınlandığından, kilit bellek kullanımı azalır ve kilit yükseltme oluşma olasılığı çok daha düşüktür, bu da iş yükü eşzamanlılığını iyileştirir.

Kullanılabilirlik

İyileştirilmiş kilitleme, Azure SQL Veritabanı'nda ve yalnızca Microsoft Fabric SQL veritabanında, tüm hizmet katmanlarında ve işlem boyutlarında kullanılabilir.

İyileştirilmiş kilitleme şu anda Azure SQL Yönetilen Örneği'nde veya SQL Server'da kullanılamamaktadır.

İyileştirilmiş kilitleme etkinleştirildi mi?

İyileştirilmiş kilitleme, kullanıcı veritabanı başına etkinleştirilir. Veritabanınıza bağlanın ve ardından en iyi duruma getirilmiş kilitlemenin etkinleştirilip etkinleştirilmediğini denetlemek için aşağıdaki sorguyu kullanın:

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

Optimize edilmiş kilitleme, diğer veritabanı özelliklerine dayanır.

• İyileştirilmiş kilitleme, hızlandırılmış veritabanı kurtarma (ADR) veritabanında etkinleştirilmesini gerektirir.
• İyileştirilmiş kilitlemeden en iyi şekilde yararlanmak için okuma anlık görüntü yalıtımı (RCSI), veritabanında etkinleştirilmelidir. İyileştirilmiş kilitlemenin LAQ bileşeni yalnızca RCSI etkinleştirildiğinde etkindir.

Azure SQL Veritabanı'nda hem ADR hem de RCSI varsayılan olarak etkindir. Geçerli veritabanınızda bu seçeneklerin etkinleştirildiğini doğrulamak için veritabanına bağlanın ve aşağıdaki T-SQL sorgusunu çalıştırın:

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

Kilitlemeye genel bakış

Bu, iyileştirilmiş kilitleme etkinleştirilmediğinde oluşan davranışın kısa bir özetidir. Daha fazla bilgi için İşlem kilitleme ve satır sürümleme kılavuzunugözden geçirin.

Veritabanı altyapısında kilitleme, işlemlerin ACID özelliklerini garanti etmek için birden çok işlemin aynı verileri aynı anda güncelleştirmesini engelleyen bir mekanizmadır.

Bir işlemin verileri değiştirmesi gerektiğinde, veriler üzerinde bir kilit talep eder. Veriler üzerinde başka çakışan kilitler tutulmazsa kilit verilir ve işlem değişiklikle devam edebilir. Veriler üzerinde çakışan başka bir kilit tutulursa, işlemin devam edebilmesi için kilidin serbest bırakılmasını beklemesi gerekir.

Birden çok işlem aynı verilere eşzamanlı olarak erişmeye çalıştığında, veritabanı altyapısının eşzamanlı okuma ve yazma işlemleriyle olası karmaşık çakışmaları çözmesi gerekir. Kilitleme, motorun ANSI SQL işlem yalıtım seviyelerininsemantiğini sağlayabildiği mekanizmalardan biridir. Veritabanlarında kilitleme çok önemli olsa da, düşük eşzamanlılık, kilitlenmeler, karmaşıklık ve kilit yükü performansı ve ölçeklenebilirliği etkileyebilir.

İyileştirilmiş kilitleme ve işlem kimliği (TID) kilitleme

satır sürüm oluşturma tabanlı yalıtım düzeyleri kullanımda olduğunda veya ADR etkinleştirildiğinde, veritabanındaki her satır dahili olarak bir işlem kimliği (TID) içerir. Bu TID diskte kalıcı hale getirilir. Bir satırı değiştiren her işlem, bu satırı TID değeriyle damgalar.

TID kilitlemesi ile, satırın anahtarını kilitlemek yerine, satırın TID'sine kilit uygulanır. Değiştiren işlem, TID'si üzerinde bir X kilidi tutar. Diğer işlemler, ilk işlem tamamlanana kadar beklemek için TID üzerinde bir S kilidi edinir. TID kilitleme ile, değişiklikler için sayfa ve satır kilitleri alınmaya devam eder, ancak her satır değiştirildiğinde her sayfa ve satır kilidi serbest bırakılır. İşlem sonuna kadar tutulan tek kilit, TID kaynağındaki tek X kilididir ve birden çok sayfa ve satır (anahtar) kilidini değiştirir.

Yazma işlemi etkinken geçerli oturum için kilitleri gösteren aşağıdaki örneği göz önünde bulundurun:

/* 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;

İyileştirilmiş kilitleme etkinleştirilirse, istek XACT (işlem) kaynağında yalnızca tek bir X kilidi tutar.

İyileştirilmiş kilitleme etkinleştirilmediyse, aynı istek dört kilit tutar: Her satır için üç X anahtar kilidi ve satırları içeren sayfadaki bir IX (niyetle özel) kilidi.

sys.dm_tran_locks dinamik yönetim görünümü (DMV), en iyi duruma getirilmiş kilitlemeyi uygulamada gözlemleme gibi kilitleme sorunlarını incelemede veya gidermede yararlıdır.

Kalifikasyondan sonra iyileştirilmiş kilitleme ve kilitleme (LAQ)

TID altyapısını kullanarak, iyileştirilmiş kilitleme, INSERT, UPDATE, DELETEve MERGE gibi DML deyimlerinin kilit alma şeklini değiştirir.

İyileştirilmiş kilitleme olmadan, sorgu önkoşulları önce güncelleştirme (U) satır kilidi alınarak bir taramada satır satır denetlenir. Koşul karşılanırsa, satırı güncelleştirmeden önce özel (X) satır kilidi alınır ve işlem sonuna kadar tutulur.

İyileştirilmiş kilitleme ile ve READ COMMITTED anlık görüntü yalıtım düzeyi (RCSI) etkinleştirildiğinde, önkoşullar hiçbir kilit alınmadan satırın en son işlenen sürümünde denetlenir. Koşul karşılamıyorsa, sorgu taramada bir sonraki satıra geçer. Koşul karşılanırsa, satırı güncelleştirmek için bir X satır kilidi alınır. X satır kilidi, satır güncelleştirmesi tamamlanır tamamlanmaz, işlem bitmeden önce serbest bırakılır.

Koşul değerlendirmesi hiçbir kilit alınmadan gerçekleştirildiğinden, farklı satırları değiştiren eşzamanlı sorgular birbirini engellemez.

Mesela:

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

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

İyileştirilmiş kilitleme olmadan oturum 2 engellenir çünkü oturum 1, 2. oturumun güncelleştirilmesi gereken satırda U bir kilit tutar. Ancak optimize edilmiş kilitleme ile, U kilitler alınmadığı ve 1. satırın en son işlenmiş sürümünde sütun a 1'e eşit olduğu için oturum 2'nin koşulunu karşılamadığından, oturum 2 engellenmez.

LAQ U kilitleri alınmadığından, koşul değerlendirildikten sonra eşzamanlı bir işlem satırı değiştirebilir. Koşul karşılanırsa ve satırda başka etkin işlem yoksa (X TID kilidi yoksa), satır değiştirilir. Etkin bir işlem varsa, veritabanı altyapısı işlemin tamamlanmasını bekler ve değişiklik sırasında koşulu yeniden değerlendirir çünkü diğer işlem satırı değiştirmiş olabilir. Koşul yine de karşılanırsa, satır değiştirilir.

Başka bir işlem satırı değiştirdiğinden koşul değerlendirmesinin otomatik olarak yeniden denendiği aşağıdaki örneği göz önünde bulundurun:

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

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

İyileştirilmiş kilitleme ve RCSI ile sorgu davranışı değişiklikleri

Okunabilir onaylı anlık görüntü yalıtımı (RCSI) altındaki katı işlem yürütme sırasına dayanan eşzamanlı iş yükleri, optimize edilmiş kilitleme etkinleştirildiğinde sorgu davranışında farklılıklar yaşayabilir.

T2 işleminin T1 işlemi sırasında güncelleştirilen sütun b temelinde tablo t4 güncelleştirdiği aşağıdaki örneği göz önünde bulundurun.

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

INSERT INTO t4
VALUES (1,1);
GO

Önceki senaryonun sonucunu nitelendirme sonrası kilit (LAQ) ile ve kilit olmadan değerlendirelim.

LAQ olmadan

LAQ olmadan, T2 işlemindeki UPDATE deyimi engellenir ve T1 işleminin tamamlanmasını bekler. T1 tamamlandıktan sonra T2, koşulu karşılandığından b satır ayarı sütununu 3 olarak güncelleştirir.

her iki işlem de işlendikten sonra tablo t4 aşağıdaki satırları içerir:

 a | b
 1 | 3

LAQ ile

LAQ ile T2 işlemi, sütun b'ın 1'e eşit olduğu satırın en son işlenmiş (commit edilmiş) sürümünü kullanarak koşulunu (b = 2) değerlendirir. Satır uygun değil; bu nedenle atlanır ve ifade T1 işlemi tarafından engellenmeden tamamlanır. Bu örnekte LAQ engellemeyi kaldırır ancak farklı sonuçlara yol açar.

her iki işlem de işlendikten sonra tablo t4 aşağıdaki satırları içerir:

 a | b
 1 | 2

İyileştirilmiş kilitleme için teşhis eklentileri

Aşağıdaki iyileştirmeler, iyileştirilmiş kilitleme etkinleştirildiğinde engelleme ve kilitlenme sorunlarını izlemenize ve gidermenize yardımcı olur:

• İyileştirilmiş kilitleme için bekleme türleri
  • XACT, S kilidi üzerindeki bekleme türleri ve sys.dm_os_wait_stats (Transact-SQL)içindeki kaynak açıklamaları:
    • LCK_M_S_XACT_READ - Görev, okuma amacıyla XACTwait_resource türünde paylaşılan kilit beklediğinde gerçekleşir.
    • LCK_M_S_XACT_MODIFY - Bir görev, XACTwait_resource türünde değişiklik yapmak amacıyla bir paylaşımlı kilit beklediğinde gerçekleşir.
    • LCK_M_S_XACT - Görev, amacın çıkarılamadığı XACTwait_resource türünde paylaşılan kilit beklerken gerçekleşir. Bu senaryo yaygın değildir.
• Kaynakların görünürlüğünü kilitleme
  • XACT kaynakları kilitliyor. Daha fazla bilgi için bkz. resource_descriptionsys.dm_tran_locks (Transact-SQL).
• Kaynak görünürlüğünü bekleme
  • XACT bekleme kaynakları. Daha fazla bilgi için bkz. wait_resourcesys.dm_exec_requests (Transact-SQL).
• Kilitlenme grafı
  • <resource-list>kilitlenme raporundaki her kaynağın altında, her <xactlock> öğesi, bir kilitlenmenin her üyesinin kilitleri için temel kaynakları ve belirli bilgileri raporlar. Daha fazla bilgi ve örnek için bkz. Optimizasyonlu kilitleme ve kilitlenmeler.

İyileştirilmiş kilitleme ile en iyi yöntemler

Okundu anlık görüntü yalıtımını (RCSI) etkinleştirme

İyileştirilmiş kilitlemenin avantajlarını en üst düzeye çıkarmak için, veritabanında okuma kaydedilmiş anlık görüntü yalıtımı (RCSI) etkinleştirmeniz ve varsayılan yalıtım düzeyi olarak READ COMMITTED yalıtımı kullanmanız önerilir. Henüz etkinleştirilmemişse, master veritabanına bağlanıp aşağıdaki deyimi yürüterek RCSI'yi etkinleştirin:

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

Azure SQL Veritabanı'nda RCSI varsayılan olarak etkinleştirilir ve READ COMMITTED varsayılan yalıtım düzeyidir. RCSI etkinleştirildiğinde ve READ COMMITTED yalıtım düzeyini kullanırken, okuyucular ifadeye başlandığında alınan anlık görüntüden satırın bir sürümünü okur. LAQ ile yazılımcılar, satırların en son işlenmiş sürümüne göre ve U kilitleri almadan koşula göre satırları nitelendirir. LAQ ile sorgu, yalnızca satır uygun olduğunda ve o satırda etkin bir yazma işlemi mevcutsa bekler. En son taahhüt edilen sürüme göre nitelendirme ve yalnızca nitelikli satırları kilitleme, engellemeyi azaltır ve eşzamanlılığı artırır.

Azaltılmış engellemeye ek olarak, gerekli kilit belleği azalır. Bunun nedeni, okuyucuların hiçbir kilit almamalarından ve yazarların işlemin sonuna kadar tutulan kilitler yerine yalnızca kısa süreli kilitler almasıdır. REPEATABLE READ veya SERIALIZABLEgibi daha katı yalıtım düzeyleri kullandığınızda, veritabanı altyapısı hem okuyucular hem de yazarlar için iyileştirilmiş kilitleme etkin olsa bile işlemin sonuna kadar satır ve sayfa kilitlerini tutar ve böylece bellek kullanımını engeller ve kilitler.

İpuçlarını kilitlememeye çalışın

İyileştirilmiş kilitleme etkinleştirildiğinde, UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCKgibi tablo ve sorgu ipuçları dikkate alınır, ancak bunlar iyileştirilmiş kilitlemenin avantajını azaltır. Kilit ipuçları, veritabanı motorunu satır veya sayfa kilitleri almaya ve bu kilitleri kilit ipuçlarının amacı doğrultusunda işlem sonuna kadar tutmaya zorlar. Bazı uygulamalar, örneğin UPDLOCK ipucuyla bir satırı okurken ve daha sonra güncelleştirirken kilit ipuçlarının gerekli olduğu bir mantığa sahiptir. Kilit ipuçlarını yalnızca gerektiğinde kullanmanızı öneririz.

İyileştirilmiş kilitleme sayesinde, mevcut sorgularda kısıtlama yoktur ve sorguların yeniden yazılması gerekmez. İpuçları kullanmayan sorgular, en iyi şekilde iyileştirilmiş kilitlemeden yararlanır.

Sorgudaki bir tablodaki tablo ipucu, aynı sorgudaki diğer tablolar için iyileştirilmiş kilitlemeyi devre dışı bırakmaz. Ayrıca, iyileştirilmiş kilitleme yalnızca INSERT, UPDATE, DELETEveya MERGEgibi bir DML deyimi tarafından güncelleştirilen tabloların kilitleme davranışını etkiler. Örneğin:

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;

Önceki sorgu örneğinde, yalnızca tablo t6 kilitleme ipucundan etkilenirken, t5 hala iyileştirilmiş kilitlemeden yararlanabilir.

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

Önceki sorgu örneğinde, yalnızca tablo t5REPEATABLE READ yalıtım düzeyini kullanır ve işlemin sonuna kadar kilitleri tutar. t5 için yapılan diğer güncelleştirmeler, iyileştirilmiş kilitlemeden yararlanmaya devam edebilir. Aynı durum HOLDLOCK ipucu için de geçerlidir.

Sık sorulan sorular (SSS)

İyileştirilmiş kilitleme hem yeni hem de mevcut veritabanlarında varsayılan olarak açık mı?

Azure SQL Veritabanı'nda evet.

İyileştirilmiş kilitlemenin etkinleştirilip etkinleştirilmediğini nasıl algılayabilirim?

Bkz. İyileştirilmiş kilitleme etkinleştirildi mi?.

Hızlandırılmış veritabanı kurtarma (ADR) veritabanımda etkinleştirilmediğinde ne olur?

ADR devre dışı bırakılırsa, iyileştirilmiş kilitleme de otomatik olarak devre dışı bırakılır.

Optimize edilmiş kilitlemeye rağmen sorguları engellemeye zorlamak istersem ne yapabilirim?

RCSI kullanan müşteriler için, iyileştirilmiş kilitleme etkinleştirildiğinde iki sorgu arasında engellemeyi zorlamak için READCOMMITTEDLOCK sorgu ipucunu kullanın.

İyileştirilmiş kilitleme salt okunur ikincil çoğaltmalarda kullanılıyor mu?

Hayır, çünkü DML ifadeleri salt okunur çoğaltmalarda çalıştırılamaz ve bu nedenle karşılık gelen satır ve sayfa kilitleri alınmaz.

Tempdb'deki ve geçici tablolardaki veriler değiştirilirken iyileştirilmiş kilitleme kullanılıyor mu?

Şu anda yok.

İlgili içerik

• İşlem kilitleme ve satır sürüm oluşturma kılavuzu
• Okuma kaydedilmiş anlık görüntü yalıtımı (RCSI)
• sys.dm_tran_locks (Transact-SQL)
• hızlandırılmış veritabanı kurtarma