|
|
Yayınlandı 20.07.2016 12:37:53
|
|
|
Kilitlerin genel görünümü
1. Neden kilitler getiriliyor
Birden fazla kullanıcı veritabanında aynı anda eşzamanlı işlemler yaptığında, aşağıdaki veri tutarsızlıkları ortaya çıkar:
Eksik güncellemeler
İki kullanıcı, A ve B, aynı veriyi okur ve değiştirir; bir kullanıcının değişikliğinin sonucu diğer değişikliğin sonucunu, örneğin bilet rezervasyon sistemini yok eder
Kirli okuma
Kullanıcı A veriyi değiştirir, ardından kullanıcı B veriyi okur, ancak kullanıcı A bir nedenle verinin değiştirilmesini iptal eder ve veri orijinal değerine geri döner.
Tekrar tekrar okuma
Kullanıcı A veriyi okur, ardından kullanıcı B veriyi okuyor ve değiştirir
Ortak kontrolün ana yöntemi, kullanıcıların belirli işlemleri yapmasını engelleyerek veri tutarsızlıklarını önlemek için belirli işlemleri engellemektir
2. Kilitlerin sınıflandırılması
Kilit iki kategoriye ayrılır:
1 . Veritabanı sistemi açısından bakıldığında: münhasır kilitler (yani münhasır kilitler), paylaşılan kilitler ve güncelleme kilitleri olarak ikiye ayrılmıştır
MS - SQL Server aşağıdaki kaynak kilitleme kalıplarını kullanır.
Kilit Modu Açıklaması
Paylaş(lar), veriyi değiştirmeyen veya güncellemeyen işlemler (yalnızca okunan işlemler) için kullanılır; örneğin SELECT ifadeleri.
Güncelleme (U) güncellenebilir kaynaklarda kullanılır. Birden fazla oturum okunduğunda, kilitlendiğinde ve muhtemelen kaynak güncellemesi gerçekleştiğinde yaygın çıkmaz türlerini önler.
Özel (X), INSERT, UPDATE veya DELETE gibi veri değiştirme işlemleri için kullanılır. Aynı kaynakta aynı anda birden fazla güncelleme yapılmadığından emin olun.
Niyet Kilitleri, kilit hiyerarşisi oluşturmak için kullanılır. Niyet kilitleri türleri şunlardır: Niyetli Paylaşılan (IS), Niyetli Özel (IX) ve Niyetli Özel (SIX).
Şema kilitleri, tablo şemasına bağlı işlemler yapılırken kullanılır. Şema kilitlerinin türleri şunlardır: şema modifikasyonu (Sch -M) ve şema kararlılığı (Sch -S).
Toplu güncellemeler (BU'lar), büyük hacimli verilerin bir tabloya kopyalandığı ve TABLOCK ipucu belirtildiğinde kullanılır.
Paylaşılan kilitler
Paylaşılan bir kilit, eşzamanlı işlemlerin bir kaynağı okumasına (SEÇMEYE) olanak tanır. Bir kaynakta paylaşılan (S) kilit varsa, başka hiçbir işlem veriyi değiştiremez. Veri okunur okunmaz paylaşılan (S) kilidi kaynak üzerinden, işlem izolasyon seviyesi tekrarlanabilir veya daha yüksek olarak ayarlanmadıkça veya paylaşılan (S) kilidi işlem ömrü boyunca kilit ipucu ile tutulmadıkça serbest bırakın.
Kilidi güncelleme
Güncelleme (U) kilitleri, olağan hallerinde çıkmazları önler. Tipik bir güncelleme modeli, bir kaydı okuyup bir kaynak (sayfa veya satır) için paylaşılan (S) kilit elde eden ve ardından bir satırı değiştirerek kilidin özel (X) bir kilide dönüştürülmesini gerektiren bir işlemden oluşur. İki işlem bir kaynakta paylaşılan mod kilidi edinip aynı anda veriyi güncellemeye çalışırsa, bir işlem kilidi münhasır (X) kilide dönüştürmeye çalışır. Paylaşılan moddan münhasır kilide geçiş bir süre beklemelidir çünkü bir işlemin münhasır kilidi, başka bir işlemin paylaşılan mod kilidi ile uyumlu değildir; Bir kilit beklemesi yaşanır. İkinci işlem, güncelleme için özel (X) kilit elde etmeye çalışır. Çıkmaz, her iki işlemin de münhasır (X) kilite dönüştürülmesi ve her işlemin diğer işlemin paylaşılan mod kilidini serbest bırakmasını beklemesi nedeniyle meydana gelir.
Bu olası çıkmaz sorununu önlemek için güncellenmiş (U) kilit kullanın. Aynı anda yalnızca bir işlem bir kaynak için güncellenmiş (U) kilidi alabilir. İşlem kaynağı değiştirirse, güncelleme (U) kilidi münhasır (X) kilide dönüştürülür. Aksi takdirde, kilit ortak kilide dönüştürülür.
Özel kilitler
Özel (X) kilitler, eşzamanlı işlemlerin kaynaklara erişmesini engeller. Diğer işlemler, münhasır (X) kilit tarafından kilitlenen verileri okuyamaz veya değiştiremez.
Niyet kilidi
Niyet kilidi, SQL Server'ın hiyerarşideki bazı temel kaynaklarda paylaşılan (S) kilit veya özel (X) kilit edinmesi gerektiğini gösterir. Örneğin, tablo düzeyine yerleştirilen bir share-intent kilidi, işlemin tablodaki bir sayfa veya satıra bir paylaşım(lar) kilidi koymayı amaçladığını gösterir. Tablo seviyesinde bir niyet kilidi belirlemek, başka bir işlemin o sayfayı içeren tabloda özel (X) bir kilit elde etmesini engeller. Niyet kilitleri, SQL Server yalnızca niyet kilidini tablo seviyesinde kontrol ederek bir işlemin o masada güvenli bir kilit edinip edinemeyeceğini belirlediği için performansı artırabilir. Bir işlemin tüm tabloyu kilitleyip kilitleyemeyeceğini belirlemek için her satır veya sayfadaki kilitleri kontrol etmek yerine.
Niyet kilitleri arasında Niyet Paylaşımı (IS), Niyet Özel (IX) ve Niyetle Özel Paylaşım (SIX) bulunur.
Kilit Modu Açıklaması
Niyet Paylaşımı (IS), işlemin amacının okuma hiyerarşisindeki temel kaynakların bazıları, tamamı değil, olduğunu her kaynağa S-kilitleri yerleştirerek gösterir.
Intent Exclusive (IX), işlemin amacının hiyerarşideki temel kaynakların bazılarını, ancak hepsini değil, her kaynağa X-kilidi koyarak değiştirmek olduğunu gösterir. IX, IS'nin bir üst kümesidir.
Niyetle münhasır paylaşım (SIX), işlemin amacının hiyerarşideki tüm temel kaynakları okumak ve her kaynağa IX kilitleri yerleştirerek bazı ama tamamını değil, alt kaynakları değiştirmek olduğunu gösterir. Üst düzey kaynaklarda eşzamanlı IS kilitlerine izin verin. Örneğin, bir tablonun SIX kilidi, bir SIX kilidini tabloya yerleştirir (eşzamanlı IS kilitlerine izin verir) ve IX kilidini mevcut değiştirilmiş sayfaya (değiştirilmiş satırda bir X kilidi) yerleştirir. Her kaynak, diğer işlemlerin kaynağı güncellemesini önlemek için belirli bir süre boyunca yalnızca bir SIX kilidine sahip olabilirken, diğer işlemler tablo düzeyinde IS kilitleri edinerek hiyerarşideki temel kaynakları okuyabilir.
Münhasır kilit: Sadece kilitleme işlemini gerçekleştiren program bunu kullanabilir ve üzerinde yapılan diğer işlemler kabul edilmez. Bir veri güncelleme komutu yürüttüğünde, SQL Server otomatik olarak özel bir kilit kullanır. Bir nesnede başka kilitler varsa, ona özel bir kilit ekleyemezsiniz.
Paylaşılan kilit: Paylaşılan kilit tarafından kilitlenen kaynak diğer kullanıcılar tarafından okunabilir, ancak diğer kullanıcılar bunu değiştiremez.
Güncelleme kilidi: SQL Server veriyi güncellemeye hazır olduğunda, önce veri nesnesini kilitler, böylece veri değiştirilemez ama okunabilir. SQL Server veri güncellemek istediğinde, güncelleme kilidini otomatik olarak özel bir kilit ile değiştirir ve nesnede başka kilitler bulunduğunda ona güncelleme kilidi ekleyemez (güncelleme kilidi) ekleyebilir.
2 . Programcının bakış açısından: iyimser kilit ve karamsar kilitle ayrılır.
Optimism Lock: Kilidin işini tamamen veritabanına bağlayır.
Kötümser kilitler: Programcılar, veri veya nesneler üzerindeki kilit yönetimini kendileri yönetir.
MS - SQLSERVER, veritabanında aynı anda modifikasyonlar yapan birden fazla kullanıcı arasında karamsar eşzamanlılık kontrolü uygulamak için kilitler kullanır
3. Kilidin parçacık boyutu
Kilit granülerliği bloklanan hedefin büyüklüğüdür, küçük bloklama granülerliği yüksek eşzamanlılıktır, ancak genel gider büyüktür ve büyük bloklama granülerliği düşük eşzamanlılıkta ancak yük küçüktür
SQL Server, satırlar, sayfalar, anahtarlar, anahtar aralıkları, indeksler, tablolar veya veritabanları için kilit granülerliğini destekler
Kaynak Tanımı
RID satır tanımlayıcısı. Bir masadaki sırayı tek tek kilitlemek için kullanılır.
Anahtar sırası kilidi, indeks. Serileştirilebilir işlemlerdeki anahtar aralığını korumak için kullanılır.
8 kilobayt (KB) veri sayfası veya indeks sayfası.
Genişletilmiş Disk Sekiz bitişik veri sayfası veya indeks sayfasından oluşan bir küme.
Tablo Tüm veriler ve indeksler dahil olan tüm tablo.
Veritabanı veritabanı.
4. Kilitlenme süresinin uzunluğu
Kilidin tutulma süresi, kaynağı istenen seviyede korumak için gereken süredir.
Okuma işlemlerini korumak için kullanılan paylaşılan kilidin tutma süresi, işlem izolasyon seviyesine bağlıdır. Varsayılan işlem izolasyon seviyesi READ COMMITTED olduğundan, paylaşılan kilit yalnızca okuma sayfasının süresi boyunca kontrol edilir. Taramada, kilit bir sonraki sayfada alınana kadar açılmaz. Bir HOLDLOCK isteği belirtirseniz veya işlem izolasyon seviyesini TEKRARLANABILIR OKUMA veya SERIALİZASYON olarak ayarlarsanız, kilit işlem bitene kadar serbest bırakılmaz.
İmleç için ayarlanmış eşzamanlılık seçeneğine bağlı olarak, imleç ortak modda kaydırılma kilidi elde ederek çıkarımı koruyabilir. Scroll kilidi gerektiğinde, kaydırma kilidi ancak imleç bir sonraki kez çıkarıldığında veya kapandığında, hangisi önce olursa serbest bırakılır. Ancak, bir HOLDLOCK belirtirseniz, kaydırma kilidi işlem sonuna kadar açılmaz.
Güncellemeyi korumak için kullanılan özel kilit, işlemin sonuna kadar serbest bırakılmayacak.
Bir bağlantı, başka bir bağlantı tarafından kontrol edilen bir kilitle çelişen bir kilit edinmeye çalışırsa, kilidi ele geçirmeye çalışan bağlantı şu sürece engellenecektir:
Çakışan kilit serbest bırakılır ve bağlantı istenen kilidi alır.
Bağlantı süresi doldu. Varsayılan olarak zaman aşımı aralığı yoktur, ancak bazı uygulamalar süresiz beklemeyi önlemek için zaman aşımına ulaşma aralıkları ayarlar
SQL Server'da kilitlerin beş özelleştirmesi
1 Çıkmaz kilitleri yönetin ve çıkmaz önceliklerini belirleyin
Çıkmaz, birden fazla kullanıcının farklı bloklar için başvurması nedeniyle sonsuz beklemedir; çünkü başvuru sahibinin engelleme hakkının bir kısmı var ve diğer kullanıcıların sahip olduğu kısmi engelleme için beklerken
SET DEADLOCK_PRIORITY'yi kullanarak oturumun çıkmaz durumunda nasıl tepki vereceğini kontrol edebilirsiniz. Her iki süreç de veriyi kilitlerse ve her süreç kendi kilidini serbest bırakana kadar kendi kilidini serbest bırakamazsa, çıkmaz bir durum oluşur.
2 Zaman aşımlarını yönetin ve kilit zaman aşımına kadar süreler belirleyin.
@@LOCK_TIMEOUT Mevcut oturum için mevcut kilit zaman aşımına ayarını milisaniyeler içinde döndürür
SET LOCK_TIMEOUT ayarı, uygulamanın ifadenin kaynağı engellemek için beklediği maksimum süreyi ayarlamasına olanak tanır. Bildirimin bekleme süresi LOCK_TIMEOUT ayarından fazla olduğunda, sistem otomatik olarak engelleme ifadesini iptal eder ve uygulamaya kilit talebi zaman aşıma süresinin aşıldığına dair 1222 hata mesajı döndürür
örnek
Aşağıdaki örnekte, kilit zaman aşımı süresi 1.800 milisaniye olarak ayarlanmıştır.
SET LOCK_TIMEOUT1800
3) İşlem izolasyon seviyesini belirleyin.
4) SELECT, INSERT, UPDATE ve DELETE ibeleri için tablo düzeyinde kilit ipuçları kullanın.
5) İndeksin kilitlenme granülerliğini yapılandırın
İndeksleme için kilit granülerliğini ayarlamak için sistemde sp_indexoption saklanan prosedürleri kullanabilir
6. Kilidin bilgilerini görüntüleyin
1 EXEC gerçekleştirin SP_LOCK kilit hakkında bilgi bildirin
2 Sorgu analizöründe Ctrl + 2 tuşlarına basarak kilidin bilgilerini görmek için
7. Kullanım için önlemler
Çıkmazdan nasıl kaçınılır
1. İşlemler kullanılırken, işlemlerin mantıksal işleme sürecini kısaltmaya çalışın ve işlemleri erken gönderin veya geri alın.
2 Deadlock zaman aşımına göre parametreyi makul bir aralığa ayarlayın, örneğin: 3 dakika - 10 dakika; Zamanın sonrasında, işlemin takılması önlemek için işlem otomatik olarak durdurulur;
3. Programı optimize edin, çıkmaz olgusunu kontrol edin ve kaçının;
4. Tüm script ve SP'leri tam versiyondan önce dikkatlice test edin.
5 Tüm SP'lerin hata işlemesi (@error aracılığıyla) olmalıdır
6 SQL SERVER işlemlerinin varsayılan seviyesini değiştirmeyin. Zorla kilitleme önerilmez
Sorunu çözmek Bir sıra tablosu veritabanını nasıl kilitlemeli?
8. Kilitlerle ilgili birkaç soru
1 Bir masanın sırasını nasıl kilitlemelir
IŞLEM IZOLASYONU SEVIYESINI READUNCOMMITTED AYARLA.
*FROM tablodan ROWLOCK seçin where id = 1
2 Veritabanında bir tabloyu kilitle
*MASADAN SEÇ (TUTMA)
Kilit açıklaması:
sybase:
Tablo setini güncelle col1 = col1 burada1= 0 ;
MSSQL:
Tablodan col1 seçin (TABLOCKX) burada 1=0 ;
oracle:
TABLE KILITLEME MASASI ÖZEL MODDA;
Kilit kilitlendikten sonra, kilitli kullanıcı kilidini açana kadar kimse onu kullanamaz ve kilit commit veya rollback ile açılır
Birkaç örnek izleniminizi derinleştirmenize yardımcı olur
Set1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
1) Özel kilit
İki yeni bağlantı oluşturun
İlk bağlantıda aşağıdaki ifadeyi çalıştırın
Tran başlat
Güncelleme tablo1
küme A= ' aa '
burada B= ' b2 '
gecikme bekle' 00:00:30' --30 saniye bekle
commit tran
İkinci bağlantıda aşağıdaki ifadeyi çalıştırın
Tran başlat
*Tablo1'den seç
burada B= ' b2 '
commit tran
Yukarıdaki iki ifade aynı anda çalıştırılırsa, select sorgusu güncellemenin yürütülmesini beklemelidir, yani 30 saniye beklemelidir
2) Paylaşılan kilit
İlk bağlantıda aşağıdaki ifadeyi çalıştırın
Tran başlat
*from table1 tutma kilidi seçin - Tutma kilidi kilide yapay olarak eklenir
burada B= ' b2 '
gecikme bekle' 00:00:30' --30 saniye bekle
commit tran
İkinci bağlantıda aşağıdaki ifadeyi çalıştırın
Tran başlat
tablo 1'den A,C seç
burada B= ' b2 '
Güncelleme tablo1
küme A= ' aa '
burada B= ' b2 '
commit tran
Yukarıdaki iki ifade aynı anda çalıştırılırsa, ikinci bağlantıdaki select sorgusu çalıştırılabilir
Güncelleme, paylaşılan kilidi ilk işlem açana kadar beklemeli ve onu özel bir kilide dönüştürmek için yürütülmeden önce, yani 30 saniye beklemek zorunda kalır
3) Çıkmaz
Eklenen tablo2(D,E)
D E
d1 e1
d2 e2
İlk bağlantıda aşağıdaki ifadeyi çalıştırın
Tran başlat
Güncelleme tablo1
küme A= ' aa '
burada B= ' b2 '
gecikmeyi bekle' 00:00:30'
Güncelleme Tablo2
küme D= ' d5 '
burada E= ' e1 '
commit tran
İkinci bağlantıda aşağıdaki ifadeyi çalıştırın
Tran başlat
Güncelleme Tablo2
küme D= ' d5 '
burada E= ' e1 '
gecikme bekle' 00:00:10'
Güncelleme tablo1
küme A= ' aa '
burada B= ' b2 '
commit tran
Aynı zamanda, sistem çıkmazı algılar ve süreci iptal eder
Eklemek için:
SQL Server 2000 tarafından desteklenen tablo düzeyinde kilitleme ipuçları
HOLDLOCK, paylaşılan kilidi tüm işlem tamamlanana kadar tutar ve kilitli nesne gerekmediğinde serbest bırakılmalıdır; bu da SERIALIZEDİLİR işlem izolasyon seviyesine eşit
NOLOCK ifadesi, paylaşılan kilit verilmeden yürütülür ve kirli okumalara izin verilir; bu da READ UNCOMMITTED işlem izolasyon seviyesine eşittir
PAGLOCK, bir tablo kilidi kullanılan birden fazla sayfa kilidi kullanır
READPAST, sql sunucusunun kilitli satırları atlamasına ve işlemleri yürütmesine izin verir ve READ UNCOMMITTED işlem izolasyon seviyeleri için sadece RID kilitlerini atlar, sayfa, bölge ve tablo kilitlerini değil
ROWLOCK, sıralı kilitlerin kullanımını zorunlu kılar
TABLOCKX, işlem sırasında başka herhangi bir işlemin tabloyu kullanmasını engelleyen özel bir tablo düzeyinde kilit kullanımını zorunlu kılar
UPLOCK, paylaşılan kilitsiz bir tablo okunurken güncellemeler kullanımını zorunlu kılıyor
Uygulama Kilidi:
Uygulama kilidi, istemci kodu tarafından oluşturulan bir kilittir, SQL Server tarafından oluşturulan bir kilit değildir
Uygulama kilitlerinin işlenmesi için iki süreç
sp_getapplock Uygulama kaynaklarını kilitle
sp_releaseapplock Uygulama kaynaklarını aç
Not: Bir veritabanında bir tabloyu kilitlemek arasındaki fark
*FROM tablosundan WITH( HOLDLOCK ) Diğer işlemler tabloyu okuyabilir ancak güncellenip silemez
*FROM TABLE WITH(TABLOCKX) SEÇ Diğer işlemler tabloyu okuyamaz, güncellelemez ve silmez
|
Önceki:O http://localhost:111/xxx.svc noktada bir son nokta dinleme yoktu...Önümüzdeki:SQL kilitleri NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
