SQL Server'da Kilit Yükseltme — Gün Sonu İşi Neden Müşteriyi Bekletti?

Kütüphaneci binlerce kitabı yeniden diziyor. Önce her rafa “meşgul” yazısı asıyor. Bir noktada bu kadar yazıyla uğraşmaktansa bütün salonun kapısına tek bir “kapalı” levhası asıyor.

Satır kilidi, tablo kilidi

SQL Server bir satırı güncellerken o satırı kilitler; başka biri aynı satırı aynı anda değiştiremez. Diğer satırlar serbesttir.

Ama her kilit bellekte yer tutar. Tek bir ifade bir tabloda çok fazla kilit aldığında, SQL Server bunları tek bir tablo kilidine çevirir. Buna kilit yükseltme?Terimin tanımını açlock escalationSQL Server'ın, tek bir işlemin tuttuğu çok sayıda satır ya da sayfa kilidini daha az sayıda tablo kilidine çevirmesi. Bellek kazandırır, eşzamanlılığı azaltır.Sözlükte gör → denir.

Dokunmadığın satırlar da kilitli

Çözüm, büyük güncellemeyi eşiğin altında kalan parçalara bölmektir. Her parça kendi satırlarını kilitler, işini yapar ve onaylanır. Tablo hiç kilitlenmez; müşterinin satırı serbest kalır.

NOLOCK bir çözüm değil

Bekleyen sorgular için akla ilk gelen WITH (NOLOCK) olur. Okuyucu artık beklemez, ama kilitsiz okuduğu için yarım kalmış değişiklikleri görebilir. Sayfalar okunurken yer değiştirirse aynı satırı iki kez sayabilir ya da hiç göremez.

Okuyucuların yazanları beklememesi için daha güvenli yol, veritabanında satır sürümlemeyi?Terimin tanımını açsatır sürümlemeSQL Server'da READ_COMMITTED_SNAPSHOT: okuyucu, yazanı beklemek yerine satırın onaylanmış son sürümünü okur. Yarım veriyi görmez.Sözlükte gör → açmaktır. Okuyucu, onaylanmış son sürümü görür; yazanı beklemez, yarım veriyi de görmez.

Kendin gör

1. Varsayılanla oynat. Tek UPDATE tablo kilidine yükseldi; müşteri, işi bitene kadar iki tur bekledi.
2. “4.000 satırlık üç parça”yı seç. Her parça eşiğin altında kaldı; müşteri geldiği anda güncelledi.

Tuzaklar

Yarım kalan iş. Parçalara bölünen iş ortada kesilirse, bazı satırlar güncellenmiş, bazıları güncellenmemiş olur. İşi kaldığı yerden devam edebilecek şekilde yaz: hangi satırların bittiği belli olsun.

Yükseltmeyi kapatmak. Bir tablo için yükseltmeyi kapatmak mümkündür. Ama o zaman sunucu milyonlarca satır kilidi tutmak zorunda kalabilir ve belleği zorlanır; bunu ölçmeden yapma.

İndekssiz WHERE. Güncellemenin WHERE koşulu bir indeksle desteklenmiyorsa, SQL Server satırları bulmak için çok daha fazlasını tarar ve kilitler. Parçalama, iyi bir indeksle birlikte işe yarar.

Aşağıda gün sonu işinin parçalara bölünmüş hâli var: her döngüde en fazla 4.000 satır güncellenir ve onaylanır.

1
-- One batch: at most 4,000 rows, below the escalation threshold.
2
UPDATE TOP (4000) p
3
SET    p.market_value = p.quantity * c.close_price,
4
       p.revalued_on  = ?
5
FROM   position AS p
6
JOIN   closing_price AS c ON c.isin = p.isin AND c.price_date = ?
7
WHERE  p.revalued_on < ?;   -- rows already done are skipped, so a restart resumes

1
package com.bank.eod;
2

3
import java.time.LocalDate;
4
import org.springframework.jdbc.core.JdbcTemplate;
5
import org.springframework.transaction.support.TransactionTemplate;
6

7
public final class EndOfDayJob {
8

9
    private final JdbcTemplate jdbc;
10
    private final TransactionTemplate tx;
11
    private final String batchSql;
12

13
    public EndOfDayJob(JdbcTemplate jdbc, TransactionTemplate tx, String batchSql) {
14
        this.jdbc = jdbc;
15
        this.tx = tx;
16
        this.batchSql = batchSql;
17
    }
18

19
    public void revalue(LocalDate businessDate) {
20
        int updated;
21
        do {
22
            // Each batch commits on its own: its row locks are released before the next one.
23
            updated = tx.execute(status -> jdbc.update(batchSql, businessDate, businessDate, businessDate));
24
        } while (updated > 0);
25
    }
26
}

1
-- Readers see the last committed version instead of waiting for writers.
2
ALTER DATABASE Custody SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Kendini sına