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

Şunlar için geçerlidir: SQL Server

Özgün KB numarası: 323630

Özet

Kilit yükseltme, birçok ayrıntılı kilidi (satır veya sayfa kilitleri gibi) tek bir tablo kilidine dönüştürür. SQL Server, kilit sayısına veya kilit belleğine göre dinamik olarak yükseltmeyi tetikler ve varsayılan davranışı çoğu iş yükünde aktarım hızını artırır. Ancak bazı sorgu veya işlem tasarımları, sonuçta elde edilen tablo kilidi diğer kullanıcıları engellediğinde yükseltmeye neden olur. Bu makalede, kilit yükseltmenin SQL Server engellemeye neden olup olmadığının nasıl belirleneceği, nasıl engelleneceğinin ve nasıl (ve ne zaman) devre dışı bırakılmayacağı açıklanır.

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ı belirleme hakkında daha fazla bilgi için bkz. 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ın nedeni kilit yükseltmesi değilse, sorun giderme adımlarını SQL Server engelleme sorunlarını anlama ve çözme bölümüne bakın.

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. Aşağıdaki stratejilerden birini veya daha fazlasını kullanın.

Büyük işlemleri daha küçük işlemlere bölme

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 yaklaşım 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;

Kilitleri azaltmak için sorguları ve dizinleri iyileştirme

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. Ayrıca kilitlenme olasılığını artırır ve eşzamanlılık ile 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.

Uyumlu bir tablo düzeyinde kilidi ayrı bir bağlantıda tutma

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.

Not

Bu teknik genel bir öneri değil geçici bir çözümdür. Yükseltmeyi engellemek istediğiniz süre boyunca açık bir işlem ve IX kilidi tutmanız gerekir. Daha önce açıklanan sorgu ve işlem iyileştirmelerini tercih edin.

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 (amaç özel kullanım) 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 kilit, 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 bir tablo kilidini zorlamadığı veya yönetici ALTER INDEX kullanarak sayfa veya satır kilitlerini devre dışı bırakmadığı sürece).

Önkoşulların SARGable olduğundan emin olun

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 yine de tablonun veya dizinin tamamını tarayabilir. 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, sistem işlevi, ISNULL() 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 bir hesaplama yer alır. 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 inceleyin ObjectID2 .
  • Ö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. Geri alma işlemi, SQL Server hizmetini yeniden başlatırsanız 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 (1211, -1) sorgusunu DBCC TRACEON (1224, -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 IntegerData sütunlarını IntegerData2 inceleyin.

Öneriler

Yükseltmeyi Engelleme bölümünde açıklanan yöntemler, yükseltmeyi tablo veya örnek düzeyinde 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 bu makalede ele alınanlar gibi diğer seçenekler araştırılırken kilit yükseltme nedenlerini kilitleyen ciddi engellemeyi azaltmak için etkinleştirmenizi önerir.

İlgili içerik

  • Last updated on