Engellemeyi ve kilitlemeyi açıklama

12 dakika

Kilitleme, ACID modelinin bölünmezliğini, tutarlılığını ve yalıtım özelliklerini korumak için gerekli olan ilişkisel veritabanlarının önemli bir özelliğidir. Tüm RDBMS'ler, veritabanı yazma işlemlerinin tutarlılığını ve yalıtımını ihlal edecek eylemleri engeller. SQL programcıları, veri tutarlılığını sağlamak için işlemleri doğru noktalarda başlatmalı ve sonlandırmalıdır. Veritabanı altyapısı, ilişkisel modelin temeli olan etkilenen tabloların mantıksal tutarlılığını korumak için kilitleme mekanizmaları sağlar.

SQL Server'da engelleme, bir işlem belirli bir kaynakta (satır, sayfa, tablo, veritabanı) kilit tuttuğunda ve ikinci bir işlem aynı kaynakta uyumsuz kilit türüne sahip bir kilit almaya çalıştığında gerçekleşir. Genellikle kilitler kısa bir süre tutulur ve kilidi tutan işlem serbest bırakıldıktan sonra engellenen işlem kilidi alabilir ve işlemini tamamlayabilir.

SQL Server, bir işlemi tamamlamak için gereken en küçük miktarda veriyi kilitleyerek eşzamanlılık üst sınırını sağlar. Örneğin, SQL Server tek bir satırı kilitlerse, tablodaki diğer tüm satırlar diğer işlemler için kullanılabilir durumda kalır ve eşzamanlı çalışmayı etkinleştirir. Ancak, her kilit için bellek kaynakları gerekir, bu nedenle tek bir tabloda binlerce ayrı kilidin tutulması tek bir işlem için uygun maliyetli değildir. Eşzamanlılığı maliyetle dengelemek için SQL Server kilit yükseltmesi adlı bir teknik kullanır. Tek bir nesnedeki 5.000'den fazla satırın tek bir deyimde kilitlenmesi gerekiyorsa, SQL Server birden çok satır kilidini tek bir tablo kilidine yükseltir.

Kilitleme normal bir davranıştır ve gün boyunca sık sık gerçekleşir. Yalnızca engellemeye neden olduğunda ve hızlı bir şekilde çözümlenmediğinde sorun olur. Engellemenin neden olduğu iki tür performans sorunu vardır:

İşlem, bir kaynak kümesini serbest bırakmadan önce uzun süre kilitler ve diğer işlemlerin sorgu performansını ve eşzamanlılığını engellemesine ve düşürmesine neden olur.
Bir işlem, bir kaynak kümesine kilitler alır ve bunları hiçbir zaman serbest bırakmaz ve çözmek için yönetici müdahalesi gerektirir.

Kilitlenme, bir işlem bir kaynak üzerinde kilit tuttuğunda ve başka bir işlem farklı bir kaynakta kilit tuttuğunda oluşan başka bir engelleme senaryosudur. Her işlem daha sonra diğer işlem tarafından kilitlenmiş durumdaki kaynak üzerinde bir kilit almaya çalışır ve iki işlem de tamamlanamadıkçe sonsuz beklemeye neden olur. SQL Server altyapısı bu senaryoları algılar ve geri alınması gereken en az miktarda işi hangi işlemin gerçekleştirdiğini temel alarak işlemlerden birini öldürerek kilitlenmeyi çözer. Öldürülen işlem kilitlenme kurbanı olarak bilinir. Kilitlenmeler, varsayılan olarak etkinleştirilmiş olan system_health genişletilmiş olay oturumunda kaydedilir.

İşlem kavramını anlamak önemlidir. Autocommit, SQL Server ve Azure SQL Veritabanı'nın varsayılan modudur. Bu, aşağıdaki deyim tarafından yapılan değişikliklerin otomatik olarak veritabanının işlem günlüğüne kaydedileceği anlamına gelir.

INSERT INTO DemoTable (A) VALUES (1);

Geliştiricilerin uygulama kodları üzerinde daha ayrıntılı denetime sahip olmasını sağlamak için SQL Server, işlemlerinizi açıkça denetlemenize de olanak tanır. Aşağıdaki sorgu, DemoTable tablosundaki bir satıra, işlemi tamamlamaya yönelik sonraki bir komut eklenene kadar serbest bırakılmayan bir kilit uygular.

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

Aşağıdaki sorguyu yazmanın doğru yolu aşağıdaki gibidir:

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

COMMIT TRANSACTION

komutu, COMMIT TRANSACTION değişikliklerin kaydını işlem günlüğüne açıkça işler. Değiştirilen veriler sonunda zaman uyumsuz olarak veri dosyasına girer. Bu işlemler, veritabanı altyapısına yönelik bir çalışma birimini temsil eder. Geliştirici komutu göndermeyi COMMIT TRANSACTION unutursa işlem açık kalır ve kilitler serbest bırakılmaz. Bu, uzun süre çalışan işlemlerin ana nedenlerinden biridir.

Veritabanı altyapısının veritabanının eşzamanlılığını sağlamak için kullandığı diğer mekanizma satır sürümü oluşturmadır. Veritabanına bir satır sürüm oluşturma yalıtım düzeyi etkinleştirildiğinde, altyapı TempDB'de değiştirilen her satırın sürümlerini korur. Bu genellikle okuma sorgularının veritabanına yazılan sorguları engellemesini önlemek için karma kullanım iş yüklerinde kullanılır.

İşleme veya geri alma bekleyen açık işlemleri izlemek için aşağıdaki sorguyu çalıştırın:

SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
        WHEN 0 THEN 'The transaction has not been completely initialized yet.'
        WHEN 1 THEN 'The transaction has been initialized but has not started.'
        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed.'
        WHEN 7 THEN 'The transaction is being rolled back.'
        WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;

Yalıtım düzeyleri

SQL Server, verileriniz için garanti etmeniz gereken tutarlılık ve doğruluk düzeyini tanımlamanıza olanak sağlayan çeşitli yalıtım düzeyleri sunar. Yalıtım düzeyleri eşzamanlılık ve tutarlılık arasında bir denge bulmanıza olanak verir. Yalıtım düzeyi, veri değişikliğini önlemek için alınan kilitleri etkilemez. bir işlem her zaman değiştiren veriler üzerinde özel bir kilit alır. Ancak, yalıtım düzeyiniz kilitlerinizin tutulma süresini etkileyebilir. Düşük yalıtım düzeyleri, birden çok kullanıcı işleminin aynı anda verilere erişme becerisini artırır, ancak oluşabilecek veri tutarlılığı risklerini artırır. SQL Server'daki yalıtım düzeyleri aşağıdaki gibidir:

Okunmamış veriyi oku – En düşük yalıtım düzeyi mevcuttur. Kirli okumalara izin verilir, yani bir işlem henüz işlenmemiş başka bir işlem tarafından yapılan değişiklikleri görebilir.
Kayıtlı okuma – bir işlemin başka bir işlem tarafından değiştirilmeyen, daha önce okunan verileri, ilk işlemin bitmesini beklemeden okumasına izin verir. Bu düzey, seçme işlemi gerçekleştirilir gerçekleşmez okuma kilitlerini de serbest bırakır. Bu varsayılan SQL Server düzeyidir.
Yinelenebilir Okuma – Bu düzey, seçilen veriler üzerinde alınan okuma ve yazma kilitlerini işlemin sonuna kadar tutar.
Serileştirilebilir – Bu, işlemlerin yalıtıldığı en yüksek yalıtım düzeyidir. Okuma ve yazma kilitleri seçilen verilerde alınır ve işlemin sonuna kadar serbest bırakılmaz.

SQL Server ayrıca satır sürümü oluşturma içeren iki yalıtım düzeyi içerir.

Onaylanmış Anlık Görüntü Okuma – Bu düzeyde okuma işlemleri satır veya sayfa kilidi almaz ve veritabanı motoru her okuma işlemini sorgunun başındaki verilerin tutarlı bir anlık görüntüsüyle sunar. Bu düzey genellikle kullanıcılar OLTP veritabanında sık sık raporlama sorguları çalıştırdığında, okuma işlemlerinin yazma işlemlerini engellemesini önlemek için kullanılır.
Snapshot : Bu düzey, satır sürümü oluşturma aracılığıyla işlem düzeyinde okuma tutarlılığı sağlar. Bu düzey güncelleştirme çakışmalarına karşı savunmasızdır. Bu düzey altında çalışan bir işlem başka bir işlem tarafından değiştirilen verileri okursa, anlık görüntü işlemi tarafından yapılan bir güncelleştirme sonlandırılır ve geri alınır. Bu, okundu anlık görüntü yalıtımıyla ilgili bir sorun değildir.

Yalıtım düzeyleri, gösterildiği gibi T-SQL SET komutuyla her oturum için ayarlanır:

SET TRANSACTION ISOLATION LEVEL

 { READ UNCOMMITTED

 | READ COMMITTED

 | REPEATABLE READ

 | SNAPSHOT

 | SERIALIZABLE

 }

Bir veritabanında çalışan tüm sorgular veya belirli bir kullanıcı tarafından çalıştırılan tüm sorgular için genel yalıtım düzeyi ayarlamanın bir yolu yoktur. Bu bir oturum düzeyi ayarıdır.

Engelleme sorunlarını izleme

Engelleyici sorunların tanımlanması, düzensiz doğası nedeniyle zorlayıcı olabilir. ile birleştirildiğinde sys.dm_tran_locksDMVsys.dm_exec_requests, her oturum tarafından tutulan kilitler hakkında bilgi sağlar. Engelleme sorunlarını izlemenin daha etkili bir yolu, Genişletilmiş Olaylar altyapısını sürekli olarak kullanmaktır.

Engelleme sorunları genellikle iki kategoriye ayrılır:

Zayıf işlem tasarımı: Örneğin, olmayan COMMIT TRANSACTION bir işlem hiçbir zaman bitmeyecektir. Tek bir işlemde çok fazla iş yapmaya çalışmak veya bağlı sunucu bağlantısı kullanarak dağıtılmış bir işlem yapmak, öngörülemeyen performansa yol açabilir.
Şema tasarımının neden olduğu uzun süre çalışan işlemler: Bu genellikle eksik dizine veya kötü tasarlanmış bir güncelleştirme sorgusuna sahip bir sütunda güncelleştirme içerir.

Kilitlemeyle ilgili performans sorunlarının izlenmesi, kilitlemeyle ilgili performans düşüşünü hızla belirlemenize olanak tanır.

Engellemeyi izleme hakkında daha fazla bilgi için bkz. SQL Server engelleme sorunlarını anlama ve çözme.

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?