Engelleme, deadlock, izolasyon: iki oturumu kafanda çalıştırmak

Tarayıcıda tek bağlantı var; kilit gösterilemez. Bu yüzden zaman çizelgesi, anlık görüntü sorgusu ve deadlock kaydı okuyoruz.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Neden burada denenemiyor?

Motor sınırıBu motor tek bağlantıdır. SET TRANSACTION ISOLATION LEVEL, WITH (NOLOCK), LOCK_TIMEOUT hatasız kabul edilir ama hiçbir davranış üretmez; sonuç ekranında bunu söyleyen uyarı görürsün. Aşağıdaki çizelgeler gerçek sunucuda olacakları anlatır, burada çalıştırılmadı.

Engelleme: normal bir bekleme

Zaman	Oturum 61	Oturum 62
09:06	`BEGIN TRAN; UPDATE bank_accounts ... WHERE account_id = 100002;`
09:11	(COMMIT unutuldu, boşta)	`UPDATE bank_accounts ... WHERE account_id = 100002;` bekliyor
09:40	hâlâ boşta, kilit duruyor	29 dakikadır bekliyor

Tutan bitince bekleyen devam eder. Varsayılan bekleme süresi sınırsızdır, LOCK_TIMEOUT verilmedikçe zaman aşımı olmaz. Teşhis: blocking_session_id zincirini tepeye kadar izle.

Sorgular kurgu anlık görüntü üzerindeBu tarayıcı motoru gerçek sistem görünümü çalıştırmaz. T-SQL modunda sys.dm_*, sys.query_store_*, msdb.dbo.* yazımı, hayali bir portal için elle kurulmuş tablolara yönlenir. Yazım ve sütun adları gerçekle aynıdır, ama yalnız seçili sütunlar var ve sayılar ölçüm değildir. Parametre alan görünümlerde (sys.dm_db_index_physical_stats) parametreler yok sayılır, bütün satırlar döner; süzmek için WHERE yaz. Gerçek sunucuda aynı sorgu için yetki gerekir: SQL Server 2022 ve sonrasında VIEW SERVER PERFORMANCE STATE, öncesinde VIEW SERVER STATE.

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time / 1000 AS bekleme_sn, r.wait_resource, st.text AS sorgu
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

sys.dm_exec_requests yalnız çalışan istekleri gösterir. Tepedeki suçlu sıklıkla boşta ve açık işlemi olan oturumdur; onu sys.dm_exec_sessions gösterir. blocking_session_id 0 engel yok, -2 ile -5 özel durumlardır: bu yüzden > 0 süzülür.

Belirti	Durum	Hareket
Çalışıyor, okuma artıyor	Uzun sorgu	Bekle ya da sorguyu iyileştir
`sleeping`, açık işlem > 0	Kapatılmamış işlem	Sahibine sor; gerekirse `KILL`. Uygulamada hata yolunda geri al
`KILLED/ROLLBACK`	Geri alınıyor	Bekle. Yeniden başlatma daha uzun sürer

Deadlock: döngüsel bekleme

Zaman	Oturum 62	Oturum 70
1	`UPDATE ... account_id = 100001` kilit alır
2		`UPDATE ... account_id = 100002` kilit alır
3	`UPDATE ... 100002` 70'i bekler
4		`UPDATE ... 100001` 62'yi bekler: döngü

Motor döngüyü bulur, kurbanı seçer: önce düşük DEADLOCK_PRIORITY, eşitse geri alması ucuz olan. Kurban 1205 alır, işlemi geri alınır. Uygulama kısa ve rastgele bekleyip yeniden dener. Kalıcı çözüm: nesnelere aynı sırayla erişmek, işlemi kısa tutmak, işlem içinde kullanıcı beklememek, satır sürümlemeyi düşünmek.

Deadlock kaydını okumak

Üç bölüm: victim-list, process-list, resource-list. system_health oturumu bu kaydı varsayılan olarak tutar. Elle kurulmuş örnek:

<deadlock>
  <victim-list><victimProcess id="process7"/></victim-list>
  <process-list>
    <process id="process7" spid="62" transactionname="transfer" lockMode="X" isolationlevel="read committed (2)">
      <inputbuf>UPDATE bank_accounts SET balance = balance - 100 WHERE account_id = 100002</inputbuf>
    </process>
    <process id="process9" spid="70" transactionname="mutabakat" lockMode="X" isolationlevel="read committed (2)">
      <inputbuf>UPDATE bank_accounts SET balance = balance + 100 WHERE account_id = 100001</inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock objectname="bank_accounts" indexname="PK_bank_accounts" mode="X">
      <owner-list><owner id="process9" mode="X"/></owner-list>
      <waiter-list><waiter id="process7" mode="X" requestType="wait"/></waiter-list>
    </keylock>
    <keylock objectname="bank_accounts" indexname="PK_bank_accounts" mode="X">
      <owner-list><owner id="process7" mode="X"/></owner-list>
      <waiter-list><waiter id="process9" mode="X" requestType="wait"/></waiter-list>
    </keylock>
  </resource-list>
</deadlock>

İzolasyon düzeyleri: hangi anomali kalır

Düzey	Kirli okuma	Tekrarlanamayan	Hayalet	Nasıl
READ UNCOMMITTED, `NOLOCK`	Var	Var	Var	Paylaşımlı kilit almaz
READ COMMITTED (varsayılan)	Yok	Var	Var	Kilit ya da RCSI ile sürüm
REPEATABLE READ	Yok	Yok	Var	Okunan satırda kilit tutar
SNAPSHOT	Yok	Yok	Yok	İşlem başındaki sürümü okur; yazma çakışması hata verir
SERIALIZABLE	Yok	Yok	Yok	Aralık kilidi

Niyet kilidi ve kilit yükseltme

Satırı kilitlemeden önce sayfa ve tabloya niyet kilidi (IS, IX) konur: başkası tablo kilidi isterse her satırı taramadan çakışmayı görür. Bir ifade çok fazla satır kilidi alırsa motor bellek için bunları tablo kilidine yükseltebilir; bu yüzden büyük toplu güncelleme bütün tabloyu kilitleyebilir, parçalara bölmek bu riski de azaltır.

-- Çalışmaz, başvuru
SET DEADLOCK_PRIORITY LOW;
SET LOCK_TIMEOUT 5000;
KILL 61;
DBCC OPENTRAN;