SQL Server'da kilit yükseltmenin neden olduğu engelleme sorunlarını çözme

2025-01-10

Özet

Kilit yükseltmesi, birçok ayrıntılı kilidi (satır veya sayfa kilitleri gibi) tablo kilitlerine dönüştürme işlemidir. Microsoft SQL Server, ne zaman kilit yükseltmesi yapileceğini dinamik olarak belirler. Bu kararı verdiği zaman, SQL Server belirli bir taramada tutulan kilit sayısını, işlemin tamamı tarafından tutulan kilit sayısını ve sistemde bir bütün olarak kilitler için kullanılan belleği dikkate alır. Genellikle SQL Server'ın varsayılan davranışı, kilit yükseltme işleminin yalnızca performansı artıracağı veya aşırı sistem kilidi belleğini daha makul bir düzeye düşürmeniz gereken zamanlarda gerçekleşmesine neden olur. Ancak, bazı uygulama veya sorgu tasarımları bu eylemin istenmediği bir zamanda kilit yükseltmeyi tetikleyebilir ve yükseltilen tablo kilidi diğer kullanıcıları engelleyebilir. Bu makalede, kilit yükseltmenin engellemeye neden olup olmadığının nasıl belirleneceği ve istenmeyen kilit yükseltmesiyle nasıl başa çıkılacağı açıklanır.

Özgün ürün sürümü: SQL Server
Özgün KB numarası: 323630

Kilit yükseltmenin engellemeye neden olup olmadığını belirleme

Kilit yükseltmesi çoğu engelleme sorununa neden olmaz. Engelleme sorunlarıyla karşılaştığınız sırada veya yakın zamanda kilit yükseltmesinin gerçekleşip gerçekleşmediğini belirlemek için, olayı içeren lock_escalation bir Genişletilmiş Olaylar oturumu başlatın. Hiçbir lock_escalation olay görmüyorsanız sunucunuzda kilit yükseltmesi gerçekleşmez ve bu makaledeki bilgiler durumunuz için geçerli değildir.

Kilit yükseltmesi oluşuyorsa, yükseltilen tablo kilidinin diğer kullanıcıları engellediğini doğrulayın.

Baş engelleyiciyi ve baş engelleyici tarafından tutulan ve diğer sunucu işlem kimliklerini (SPID) engelleyen kilit kaynağını tanımlama hakkında daha fazla bilgi için bkz . INF: SQL Server engelleme sorunlarını anlama ve çözme.

Diğer kullanıcıları engelleyen kilit, S (paylaşılan) veya X (özel kullanım) kilit moduna sahip bir SEKME (tablo düzeyi) kilidi dışında bir şeyse, sorun kilit yükseltmesi değildir. Özellikle, SEKME kilidi bir amaç kilidiyse (IS, IU veya IX kilit modu gibi), bunun nedeni kilit yükseltmesi değildir. Engelleme sorunlarınız kilit yükseltmeden kaynaklanmadıysa bkz . INF: SQL Server engelleme sorunlarını anlama ve çözme sorun giderme adımları.

Kilit yükseltmesini önleme

Kilit yükseltmesini önlemenin en basit ve en güvenli yöntemi, işlemleri kısa tutmak ve kilit yükseltme eşiklerinin aşılmaması için pahalı sorguların kilit ayak izini azaltmaktır. Bu hedefe ulaşmak için aşağıdaki stratejiler de dahil olmak üzere çeşitli yöntemler vardır:

Büyük toplu işlemleri birkaç küçük işlem olarak ayırın. Örneğin, bir denetim tablosundan 100.000'den fazla eski kaydı kaldırmak için aşağıdaki sorguyu çalıştırırsınız ve ardından sorgunun diğer kullanıcıları engelleyen bir kilit yükseltmesine neden olduğunu belirlersiniz:
```
DELETE FROM LogMessages WHERE LogDate < '20020102';
```
Bu kayıtları bir kerede birkaç yüz kez kaldırarak işlem başına biriken kilit sayısını önemli ölçüde azaltabilirsiniz. Bu, kilit yükseltmesini engeller. Örneğin, aşağıdaki sorguyu çalıştırırsınız:
```
DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;
```
Sorguyu olabildiğince verimli hale getirerek sorgunun kilit ayak izini azaltın. Büyük taramalar veya birçok yer işareti araması kilit yükseltme olasılığını artırabilir. Buna ek olarak, bunlar kilitlenme olasılığını artırır ve eşzamanlılığı ve performansı olumsuz etkiler. Kilit yükseltmesine neden olan sorguyu belirledikten sonra, dizin veya tablo taramalarını kaldırmak ve dizin aramalarının verimliliğini en üst düzeye çıkarmak için yeni dizinler oluşturma veya var olan bir dizine sütun ekleme fırsatlarını arayın. Sorgu performansını geliştirmek için yürütme planını gözden geçirin ve potansiyel olarak yeni kümelenmemiş dizinler oluşturun. Daha fazla bilgi için bkz . SQL Server Dizin Mimarisi ve Tasarım Kılavuzu.

Bu iyileştirmenin bir hedefi, yer işareti aramalarının maliyetini en aza indirmek için dizin aramalarının mümkün olduğunca az satır döndürmesini sağlamaktır (sorgu için dizinin seçiciliğini en üst düzeye çıkarmak). SQL Server, Yer İşareti Arama mantıksal işlecinin birçok satır döndüreceğini tahmin ederse, yer işareti aramasını yapmak için bir PREFETCH yan tümcesi kullanabilir. SQL Server yer işareti araması için kullanıyorsa PREFETCH , sorgunun bir bölümünün işlem yalıtım düzeyini sorgunun bir bölümü için "yinelenebilir okuma" olarak artırması gerekir. Bu, "read-committed" yalıtım düzeyindeki bir SELECT deyim gibi görünebileceklerin binlerce anahtar kilidi (hem kümelenmiş dizinde hem de bir kümelenmemiş dizinde) edinebileceği anlamına gelir. Bu, böyle bir sorgunun kilit yükseltme eşiklerini aşmasına neden olabilir. Bunlar genellikle varsayılan "read-committed" yalıtım düzeyinde görülmese de, yükseltilen kilidin paylaşılan bir tablo kilidi olduğunu fark ederseniz bu özellikle önemlidir. İlerletme işlemine Yer İşareti Araması WITH PREFETCH yan tümcesi neden oluyorsa, Dizin Arama'da görünen kümelenmemiş dizine veya sorgu planındaki Yer İşareti Arama mantıksal işlecinin altındaki Dizin Tarama mantıksal işlecine sütun eklemeyi göz önünde bulundurun. Bir kapsayan dizin (sorguda kullanılan bir tablodaki tüm sütunları içeren bir dizin) veya en azından birleştirme ölçütleri için kullanılan sütunları kapsayan bir dizin veya "select column" listesindeki her şeyi eklemek pratik değilse WHERE yan tümcesi oluşturmak mümkün olabilir.

İç İçe Döngü birleştirmesi de kullanabilir PREFETCHve bu aynı kilitleme davranışına neden olur.
Farklı bir SPID şu anda uyumsuz bir tablo kilidi tutuyorsa kilit yükseltmesi gerçekleşemez. Kilit yükseltmesi her zaman bir tablo kilidine ve hiçbir zaman sayfa kilidine ilerler. Ayrıca, başka bir SPID uyumsuz bir SEKME kilidi tuttuğundan kilit yükseltme girişimi başarısız olursa, yükseltmeyi deneyen sorgu SEKME kilidi beklerken engellenmez. Bunun yerine, kilitleri özgün, daha ayrıntılı düzeyinde (satır, anahtar veya sayfa) almaya devam eder ve düzenli aralıklarla ek yükseltme girişimlerinde bulunur. Bu nedenle, belirli bir tabloda kilit yükseltmesini önlemenin bir yöntemi, yükseltilmiş kilit türüyle uyumlu olmayan farklı bir bağlantı üzerinde kilit almak ve tutmaktır. Tablo düzeyindeki bir IX (özel amaç) kilidi hiçbir satırı veya sayfayı kilitlemez, ancak yine de yükseltilmiş S (paylaşılan) veya X (özel) SEKME kilidiyle uyumlu değildir. Örneğin, mytable tablosundaki birçok satırı değiştiren ve kilit yükseltmesi nedeniyle engellemeye neden olan bir toplu iş çalıştırmanız gerektiğini varsayalım. Bu iş her zaman bir saatten kısa bir sürede biterse, aşağıdaki kodu içeren bir Transact-SQL işi oluşturabilir ve yeni işi toplu işin başlangıç zamanından birkaç dakika önce başlatacak şekilde zamanlayabilirsiniz:
```
BEGIN TRAN;
SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;
```
Bu sorgu bir saat boyunca mytable üzerinde bir IX kilidi alır ve tutar. Bu, bu süre boyunca tabloda kilit yükseltmesini önler. Bu toplu işlem hiçbir veride değişiklik yapmaz veya diğer sorguları engellemez (diğer sorgu TABLOCK ipucunu kullanarak tablo kilidini zorlamadığı veya yönetici ALTER INDEX kullanarak sayfa veya satır kilitlerini devre dışı bırakmadığı sürece).
Sorgunun koşul ve birleştirme sütunları için dizinleri kullanıp kullanamayacağını açıklamak için kullanılan ilişkisel bir veritabanı terimi olan SARGability eksikliğinden kaynaklanan kilit yükseltmesini ortadan kaldırın. SARGability hakkında daha fazla bilgi için bkz . Inside Design Guide Query Considerations. Örneğin, çok fazla satır (veya belki de tek bir satır) istemeyen oldukça basit bir sorgu, tablonun/dizinin tamamını taramaya devam edebilir. WHERE yan tümcesinin sol tarafında bir işlev veya hesaplama varsa bu durum oluşabilir. SARGability özelliği olmayan örnekler arasında örtük veya açık veri türü dönüştürmeleri, ISNULL() sistem işlevi, parametre olarak geçirilen sütuna sahip kullanıcı tanımlı bir işlev veya veya WHERE Column1*Column2 = 5gibi WHERE CONVERT(INT, column1) = @a sütunda hesaplama sayılabilir. Bu gibi durumlarda sorgu, tüm sütun değerlerinin önce alınması ve işleve geçirilmesi gerektiğinden, uygun sütunları içerse bile var olan dizini ARAYAMAZ. Bu, tablonun veya dizinin tamamının taranmasına ve çok sayıda kilidin alınmasına neden olur. Bu gibi durumlarda SQL Server kilit sayısı yükseltme eşiğine ulaşabilir. Çözüm, WHERE yan tümcesindeki sütunlarda işlevleri kullanmaktan kaçınmak ve SARGable koşulları sağlamaktır.

Kilit yükseltmeyi devre dışı bırakma

SQL Server'da kilit yükseltmeyi devre dışı bırakmak mümkün olsa da, bunu önermeyiz. Bunun yerine, Kilit Yükseltmeyi Önleme bölümünde açıklanan önleme stratejilerini kullanın.

Tablo düzeyi: Kilit yükseltmesini tablo düzeyinde devre dışı bırakabilirsiniz. Bkz. ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Hangi tablonun hedefleneceğini belirlemek için T-SQL sorgularını inceleyin. Bu mümkün değilse Genişletilmiş olayları kullanın, lock_escalation olayını etkinleştirin ve object_id sütununu inceleyin. Alternatif olarak Lock:Escalation olayını kullanın ve SQL Profiler kullanarak sütunu inceleyinObjectID2.
Örnek Düzeyi: Örnek için 1211 veya 1224 ya da her iki izleme bayrağını etkinleştirerek kilit yükseltmeyi devre dışı bırakabilirsiniz. Ancak, bu izleme bayrakları SQL Server örneğinde genel olarak tüm kilit yükseltmesini devre dışı bırakır. Kilit yükseltme, sql server'da yararlı bir amaca hizmet eder ve bunun dışında birkaç binlerce kilit alma ve serbest bırakma yüküyle yavaşlayan sorguların verimliliğini en üst düzeye çıkarır. Kilit yükseltmesi, kilitleri izlemek için gerekli belleğin en aza indirilmesine de yardımcı olur. SQL Server'ın kilit yapıları için dinamik olarak ayırabileceği bellek sonludur. Bu nedenle, kilit yükseltmeyi devre dışı bırakırsanız ve kilit belleği yeterince büyürse, herhangi bir sorgu için ek kilit ayırma girişimleri başarısız olabilir ve aşağıdaki hata girdisini oluşturabilir:

Hata: 1204, Önem Derecesi: 19, Durum: 1
SQL Server şu anda bir LOCK kaynağı alamıyor. Daha az etkin kullanıcı olduğunda deyiminizi yeniden çalıştırın veya sistem yöneticisinden SQL Server kilidini ve bellek yapılandırmasını denetlemesini isteyin.

Not

1204 hatası oluştuğunda geçerli deyimin işlenmesini durdurur ve etkin işlemin geri alınmasına neden olur. SQL Server hizmetini yeniden başlatırsanız geri alma işlemi kullanıcıları engelleyebilir veya uzun bir veritabanı kurtarma süresine neden olabilir.

SQL Server Yapılandırma Yöneticisi kullanarak bu izleme bayraklarını (-T1211 veya -T1224) ekleyebilirsiniz. Yeni bir başlangıç parametresinin etkili olması için SQL Server hizmetini yeniden başlatmanız gerekir. veya DBCC TRACEON (1224, -1) sorgusunu DBCC TRACEON (1211, -1) çalıştırırsanız izleme bayrağı hemen etkinleşir.
Ancak, -T1211 veya -T1224'i başlangıç parametresi olarak eklemezseniz, SQL Server hizmeti yeniden başlatıldığında komutun DBCC TRACEON etkisi kaybolur. İzleme bayrağının açılması gelecekteki kilit yükseltmelerini engeller, ancak etkin bir işlemde zaten gerçekleşen kilit yükseltmelerini geri almaz.

ROWLOCK gibi bir kilit ipucu kullanırsanız, bu yalnızca ilk kilit planını değiştirir. Kilit ipuçları kilit yükseltmesini engellemez.

İlerletme eşiklerini kilitleme

Kilit yükseltmesi aşağıdaki koşullardan biri altında gerçekleşebilir:

Bellek eşiğine ulaşıldı - Kilit belleğinin yüzde 40'lık bir bellek eşiğine ulaşılır. Kilit belleği arabellek havuzunun yüzde 24'ünün üzerine çıktığında, kilit yükseltme tetiklenebilir. Kilit belleği görünür arabellek havuzunun yüzde 60'ı ile sınırlıdır. Kilit yükseltme eşiği, kilit belleğinin yüzde 40'ında ayarlanır. Bu, arabellek havuzunun yüzde 60'ının yüzde 40'ı veya yüzde 24'üdür. Kilit belleği yüzde 60 sınırını aşarsa (kilit yükseltme devre dışı bırakılırsa bu çok daha olasıdır), ek kilit ayırma girişimleri başarısız olur ve 1204 hatalar oluşturulur.
Kilit eşiğine ulaşıldı - Bellek eşiği denetlendikten sonra geçerli tablo veya dizinde alınan kilit sayısı değerlendirilir. Sayı 5.000'i aşarsa bir kilit yükseltme tetikler.

Hangi eşiğe ulaşıldığını anlamak için Genişletilmiş olayları kullanın, lock_escalation olayını etkinleştirin ve escalated_lock_count ve escalation_cause sütunlarını inceleyin. Alternatif olarak Lock:Escalation olayını kullanın ve "0 - LOCK_THRESHOLD" deyiminin EventSubClass kilit eşiğini aştığını ve "1 - MEMORY_THRESHOLD" deyiminin bellek eşiğini aştığını gösterdiği değeri inceleyin. Ayrıca ve IntegerData2 sütunlarını IntegerData inceleyin.

Öneriler

Kilit Yükseltmeyi Önleme bölümünde açıklanan yöntemler, tablo veya örnek düzeyinde yükseltmeyi devre dışı bırakmaktan daha iyi seçeneklerdir. Buna ek olarak, önleyici yöntemler genellikle sorgu için kilit yükseltmesini devre dışı bırakmaktan daha iyi performans üretir. Microsoft, bu izleme bayrağını yalnızca kilit yükseltmesi nedeniyle oluşan ciddi engellemeyi azaltmak için etkinleştirmenizi önerir. Bu makalede ele alınanlar gibi diğer seçenekler araştırılır.

Aracılığıyla paylaş