SQL Server Eğitimi: Deadlock Graph ile Kilit Zinciri Analizi

SQL Server Eğitimi ile tekrar üretilebilir deadlock senaryosu kurma

Deadlock incelemesine uygulama logundaki 1205 hatasıyla değil, iki oturumda tekrar üretilebilen en küçük işlemle başlayın. Aşağıdaki tabloyu oluşturup ilk batch'i Oturum A'da, ikinci batch'i Oturum B'de aynı anda çalıştırın. UPDLOCK, HOLDLOCK ifadeleri satır kilidini transaction sonuna kadar tuttuğu için iki oturum karşılıklı olarak diğerinin tuttuğu anahtarı bekler. Bu, production'da farklı sırada güncellenen sepet, bakiye veya stok satırlarının küçük bir modelidir.

CREATE TABLE dbo.AccountBalance
(
    AccountId int NOT NULL PRIMARY KEY,
    Balance decimal(19,4) NOT NULL
);
INSERT dbo.AccountBalance(AccountId, Balance) VALUES (1, 100), (2, 100);

-- Oturum A
BEGIN TRAN;
SELECT Balance FROM dbo.AccountBalance WITH (UPDLOCK, HOLDLOCK) WHERE AccountId = 1;
WAITFOR DELAY '00:00:03';
SELECT Balance FROM dbo.AccountBalance WITH (UPDLOCK, HOLDLOCK) WHERE AccountId = 2;
COMMIT;

-- Oturum B: A basladiktan sonra calistirin
BEGIN TRAN;
SELECT Balance FROM dbo.AccountBalance WITH (UPDLOCK, HOLDLOCK) WHERE AccountId = 2;
WAITFOR DELAY '00:00:03';
SELECT Balance FROM dbo.AccountBalance WITH (UPDLOCK, HOLDLOCK) WHERE AccountId = 1;
COMMIT;

Bir veritabanı eğitimi veya sql eğitimi laboratuvarında bu örneği tek sefer çalıştırmak yeterli değildir: plan, veri dağılımı ve eşzamanlılık sabitken önce-sonra kıyaslaması yapın. RML Utilities içindeki ostress ile aynı prosedürü 32 eşzamanlı istemci ve istemci başına 1000 tekrar altında koşturabilirsiniz:

ostress -E -S localhost -d Finance -Q "EXEC dbo.TransferFunds 1, 2, 1.00" -n 32 -r 1000

Testten önce ve sonra Extended Events dosyasındaki xml_deadlock_report sayısını, PerfMon'daki SQLServer:Locks\Number of Deadlocks/sec - _Total sayacını ve uygulama tarafındaki 1205 sayısını aynı zaman penceresinde kaydedin. Hedef, rastgele daha düşük bir sayı değil, aynı istek hacminde sıfır deadlock graph ve kabul edilmiş p95 işlem süresidir.

Veritabanı performans ayarlama için Extended Events ile kanıt toplama

system_health oturumu çoğu kurulumda deadlock graph barındırır, ancak dosya rollover nedeniyle yoğun sistemlerde eski olaylar düşebilir. Incident penceresinde ayrı bir Extended Events oturumu açın; event_file hedefi SQL Server servis hesabının yazabildiği yerel bir dizin olmalıdır. Production'da NO_EVENT_LOSS seçeneği olay kaybını azaltır fakat dispatcher beklemesi yaratabileceğinden, sürekli açık genel amaçlı oturum yerine süreli tanı oturumu olarak kullanın.

CREATE EVENT SESSION DeadlockForensics ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.session_id,
        sqlserver.sql_text,
        sqlserver.username
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'D:\XEvents\deadlock_forensics.xel',
        max_file_size = 100,
        max_rollover_files = 8
)
WITH
(
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = OFF
);
ALTER EVENT SESSION DeadlockForensics ON SERVER STATE = START;

Olayı SSMS arayüzünde yalnızca diyagram olarak okumayın; XEL dosyasını T-SQL ile zaman, istemci ve SQL metniyle birlikte alın. xml_deadlock_report içindeki process-list victim-process, seçilen kurbanı gösterir; bu seçim çoğunlukla DEADLOCK_PRIORITY ve rollback maliyetine dayanır, sorgunun iş açısından daha az önemli olduğunu göstermez. sql_text action'ı uygulama batch'ini bağlamaya yardımcı olur, fakat parametrelenmiş stored procedure içindeki her parametre değerini garanti etmez; bunun için uygulama correlation ID'sini client_app_name veya SESSION_CONTEXT üzerinden ayrıca loglayın.

SELECT
    x.e.value('(@timestamp)[1]', 'datetime2') AS utc_time,
    x.e.value('(action[@name="session_id"]/value)[1]', 'int') AS session_id,
    x.e.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS app_name,
    x.e.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
    x.e.query('(data/value/deadlock)[1]') AS deadlock_graph
FROM sys.fn_xe_file_target_read_file
     (N'D:\XEvents\deadlock_forensics*.xel', NULL, NULL, NULL) AS f
CROSS APPLY (SELECT CONVERT(xml, f.event_data)) AS d(event_xml)
CROSS APPLY (SELECT d.event_xml.nodes('/event') AS n(e)) AS t
CROSS APPLY t.n AS q(x);

Database indexleme: deadlock graph içindeki HOBT kimliğini nesneye çevirme

Graph'taki resource-list altında KEY kaynağında görülen hobtid, doğrudan tablo adı değildir; o B-tree'nin partition kimliğidir. XML'den örneğin 72057594051276800 değerini aldıktan sonra ilgili veritabanı bağlamında aşağıdaki sorguyu çalıştırın. Böylece iki tarafın aynı tabloyu mu, bir nonclustered indeks ile clustered indeksin farklı anahtarlarını mı kilitlediğini ayırabilirsiniz.

DECLARE @hobt_id bigint = 72057594051276800;

SELECT
    OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
    OBJECT_NAME(p.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    p.partition_number
FROM sys.partitions AS p
JOIN sys.indexes AS i
  ON i.object_id = p.object_id
 AND i.index_id = p.index_id
WHERE p.hobt_id = @hobt_id;

Bu adım database indexleme kararını tahmin yerine kanıta dayandırır. Örneğin graph'ta bir süreç IX ile PK_AccountBalance anahtarına, diğeri KEY ile IX_AccountBalance_Tenant anahtarına gidiyorsa, güncelleme planının seek sonrasında key lookup veya geniş tarama yaptığını doğrulayın. Actual execution plan açmak için test oturumunda SET STATISTICS XML ON kullanın ve güncellemenin predicate'ini karşılayan bir indeksin anahtar sırasını kontrol edin. İndeks eklemeden önce sys.dm_db_index_usage_stats değerlerinin sunucu yeniden başlatılınca sıfırlanacağını unutmayın; kalıcı karar için Query Store çalışma zamanı istatistikleri veya kendi zaman serisi metrikleriniz gerekir.

SET STATISTICS XML ON;
UPDATE dbo.AccountBalance
SET Balance = Balance - 1
WHERE AccountId = 1;
SET STATISTICS XML OFF;

SELECT i.name, s.user_seeks, s.user_scans, s.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.database_id = DB_ID()
 AND s.object_id = i.object_id
 AND s.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.AccountBalance');

Veritabanı optimizasyonu: kilit sırası yerine iş anahtarıyla serileştirme

Sadece iki UPDATE ifadesini kodda küçük ID'den büyüğe sıralamak her zaman yeterli değildir. Optimizer, join sırasını ve erişim yolunu değiştirebilir; ayrıca aynı iş varlığına dokunan başka prosedür bu kurala uymayabilir. Para transferi gibi iki hesabın tek mantıksal kaynak olduğu işlemlerde, tenant ve sıralanmış hesap çiftinden üretilen uygulama kilidi daha kesin bir sınır sağlar. sp_getapplock Transaction sahibiyle alındığında COMMIT veya ROLLBACK'ta otomatik bırakılır; @rc negatifse işleme devam etmek yerine hata üretmek zorunludur.

CREATE OR ALTER PROCEDURE dbo.TransferFunds
    @TenantId int,
    @FromAccountId int,
    @ToAccountId int,
    @Amount decimal(19,4)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @rc int;
    DECLARE @resource nvarchar(255) = CONCAT(
        'transfer:', @TenantId, ':',
        IIF(@FromAccountId < @ToAccountId, @FromAccountId, @ToAccountId), ':',
        IIF(@FromAccountId < @ToAccountId, @ToAccountId, @FromAccountId)
    );

    BEGIN TRAN;
    EXEC @rc = sys.sp_getapplock
        @Resource = @resource,
        @LockMode = 'Exclusive',
        @LockOwner = 'Transaction',
        @LockTimeout = 5000;
    IF @rc < 0 THROW 51000, 'Transfer application lock could not be acquired.', 1;

    UPDATE dbo.AccountBalance SET Balance = Balance - @Amount WHERE AccountId = @FromAccountId;
    UPDATE dbo.AccountBalance SET Balance = Balance + @Amount WHERE AccountId = @ToAccountId;
    COMMIT;
END;

Bu değişiklikten önce ve sonra aynı ostress komutunu, aynı sıcak hesap dağılımıyla çalıştırın. Extended Events'te deadlock graph sayısı yanında uygulama kilidi beklemelerini de ölçün: çok geniş bir resource adı tüm transferleri serileştirip throughput'u düşürür, çok dar bir resource adı ise çapraz işlemleri korumaz. sys.dm_tran_locks içinde resource_type = 'APPLICATION' kayıtlarını ve uygulama ölçümündeki p95/p99 süreyi test sırasında izleyin. Kilit zaman aşımı ile deadlock kurbanı farklı hata yollarıdır; ikisini aynı retry sayacında birleştirmek kapasite sorununu gizler.

SELECT request_session_id, request_status, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type = 'APPLICATION';

RCSI, retry ve nosql eğitimi bağlamında yanlış çözümler

READ_COMMITTED_SNAPSHOT, read committed altındaki paylaşımlı okuyucu kilitlerini row version okumalarına dönüştürerek okuyucu-yazıcı deadlock'larını azaltabilir. Buna karşılık iki UPDATE'nin aynı iki anahtarı ters sırada alması writer-writer deadlock olarak kalır. Bu nedenle RCSI'yi ancak staging'de aynı yük testiyle doğrulayın ve version store tüketimini izleyin. ALTER DATABASE komutu mevcut oturumları geri alabilir; bakım penceresi ve bağlantı drenajı olmadan çalıştırmayın.

ALTER DATABASE Finance SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

SELECT
    DB_NAME(database_id) AS database_name,
    reserved_page_count * 8.0 / 1024 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
WHERE database_id = DB_ID(N'Finance');

1205 için retry, düzeltme değil son savunma katmanıdır. Transaction kurban seçildiğinde tüm transaction geri alınır; retry yalnızca işlem idempotent ise ve ödeme sağlayıcısı çağrısı gibi dış yan etkiler transaction dışına güvenli biçimde taşındıysa uygulanmalıdır. Uygulama katmanında 1205'i en fazla 3 kez, rastgele jitter ile yeniden deneyin; unique idempotency key'i veritabanında saklayarak aynı isteğin ikinci kez para hareketi yaratmasını engelleyin. nosql eğitimi sırasında da aynı ilke geçerlidir: belge veritabanına geçmek, aynı belge veya partition key üzerinde eşzamanlı yazma çatışmasını ortadan kaldırmaz.

for (var attempt = 1; attempt <= 3; attempt++)
{
    try { return await ExecuteTransferInNewTransactionAsync(request, cancellationToken); }
    catch (SqlException ex) when (ex.Number == 1205 && attempt < 3)
    {
        var delayMs = Random.Shared.Next(40, 120) * attempt;
        await Task.Delay(delayMs, cancellationToken);
    }
}

Sık Sorulan Sorular

SQL Server eğitimi kapsamında deadlock graph nereden alınır?

Kısa süreli incident toplama için sqlserver.xml_deadlock_report event'iyle bir Extended Events event_file hedefi oluşturun. sys.fn_xe_file_target_read_file ile XEL dosyasını okuyup data/value/deadlock XML düğümünü saklayın; yalnızca 1205 uygulama hatasını saklamak, kilit kaynaklarını göstermez.

Veritabanı performans ayarlama sırasında RCSI tüm deadlock sorunlarını çözer mi?

Hayır. RCSI, read committed okuyucularının S kilidi almasını azaltır; iki transaction'ın aynı satırları ters sırayla UPDATE etmesindeki X veya U kilidi döngüsünü çözmez. Değişiklik öncesi ve sonrası aynı ostress yükünde xml_deadlock_report sayısını, p95 süreyi ve sys.dm_tran_version_store_space_usage içindeki MB değerini karşılaştırın.

Database indexleme deadlock azaltmak için nasıl doğrulanır?

Deadlock XML'indeki hobtid değerini sys.partitions.hobt_id ile tablo ve indeks adına çevirin. Ardından actual plan ile scan, key lookup ve erişim sırasını doğrulayın. Yeni indeksin yararını aynı eşzamanlı yükte deadlock graph sayısı, logical read ve p95 işlem süresiyle; maliyetini ise sys.dm_db_index_usage_stats içindeki user_updates ile ölçün.

SQL eğitimi için 1205 deadlock hatasında retry kaç kez yapılmalı?

Sabit bir sayı evrensel değildir, ancak 2 veya 3 sınırlı deneme ve 40-360 ms arası jitter pratik bir üst sınırdır. Retry işleminden önce transaction'ın tamamen geri alındığını, isteğin idempotency key ile tekilleştirildiğini ve timeout hatalarının 1205 ile aynı retry politikasına yanlışlıkla girmediğini doğrulayın.