SQL Server 2025 Optimized Locking: TID Locking, Lock After Qualification ve Davranış Değişiklikleri

Sürüm notu: Bu yazıdaki SQL (Structured Query Language) örnekleri SQL Server 2025 (17.0.5035.5, Cumulative Update 10) üzerinde, compatibility level 170 ile çalıştırılmıştır. Optimized locking SQL Server 2025'te veritabanı bazında açılır ve varsayılan olarak kapalıdır.

Önce Tahmin Et: Stoğu Tükenen Kitap Yeniden Stoklanır mı?

Bookstore'da iki iş aynı anda çalışıyor. Birinci session, 1 numaralı kitabın son kopyasını satıyor ve stoğu sıfıra çekiyor; transaction'ı birkaç saniye açık. İkinci session, deponun "stoğu bitmiş her kitaba 10 adet ekle" işi:

/* session 1: the last copy of book 1 is sold */
BEGIN TRANSACTION;
UPDATE dbo.StockItems SET QuantityOnHand = 0 WHERE BookId = 1;
WAITFOR DELAY '00:00:05';
COMMIT;

/* session 2: restock every sold-out book */
UPDATE dbo.StockItems SET QuantityOnHand += 10 WHERE QuantityOnHand = 0;
SELECT @@ROWCOUNT AS restocked;

Başlangıçta 207 kitabın stoğu sıfır; 1 numaralı kitabın stoğu 36. İkinci session, birincisi commit etmeden başlıyor. İki transaction da bittiğinde 1 numaralı kitabın stoğu kaç olur, 0 mı 10 mu? Veritabanında RCSI (Read Committed Snapshot Isolation) açık; aynı deneyi aynı veriyle iki kez yaptık, tek fark bir veritabanı ayarı:

setting                          restocked  BookId 1 QuantityOnHand  session 2 waited
OPTIMIZED_LOCKING = OFF          208        10                       LCK_M_U
OPTIMIZED_LOCKING = ON           207        0                        no

Klasik locking'de ikinci session bekledi, birincinin commit'inden sonra 1 numaralı kitabın stoğunu sıfır gördü ve onu da stokladı. Optimized locking'de hiç beklemedi, kitabı atladı ve kitap stoksuz kaldı. İkisi de doğru çalışıyor; ama sonuç farklı. Bu yazı SQL Server 2025'in optimized locking özelliğini anlatıyor: lock'ları nasıl azalttığını, blocking'i nasıl kaldırdığını ve bu tablodaki gibi davranışın nerede değiştiğini.

Optimized Locking Nedir, Nasıl Açılır?

Optimized locking iki parçadan oluşur. TID (Transaction ID) locking, değiştirilen her satır için ayrı lock tutmak yerine transaction'ın kendi kimliğine tek bir lock koyar. Lock after qualification (LAQ) ise UPDATE ve DELETE'in hangi satırları değiştireceğine lock almadan, satırın son commit edilmiş versiyonuna bakarak karar vermesidir. İkinci parça yalnız RCSI açıkken çalışır.

SQL Server 2025'te özellik veritabanı başına açılır ve ön koşulu ADR'dir (Accelerated Database Recovery): her satıra yazılan transaction kimliği ve version store bu altyapıdan gelir. Azure SQL Database'de ve SQL database in Microsoft Fabric'te ise her zaman açıktır ve kapatılamaz.

ALTER DATABASE sqls_13 SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE sqls_13 SET OPTIMIZED_LOCKING = ON;
ALTER DATABASE sqls_13 SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

SELECT DATABASEPROPERTYEX('sqls_13', 'IsOptimizedLockingOn') AS is_optimized_locking_enabled;
SELECT is_accelerated_database_recovery_on, is_read_committed_snapshot_on, is_optimized_locking_on
FROM sys.databases WHERE name = 'sqls_13';

is_optimized_locking_enabled
----------------------------
1

is_accelerated_database_recovery_on|is_read_committed_snapshot_on|is_optimized_locking_on
-----------------------------------|-----------------------------|-----------------------
1|1|1

DATABASEPROPERTYEX 0 dönerse özellik kapalı, NULL dönerse o sürümde yok demektir. ADR'yi kapatmak istersen önce optimized locking'i kapatman gerekir. READ_COMMITTED_SNAPSHOT değişikliği veritabanında başka aktif connection olmamasını ister; WITH ROLLBACK IMMEDIATE açık transaction'ları geri alır, bu yüzden production'da bakım penceresinde yapılır.

TID Locking: 1.000 Lock Yerine Bir

Serinin 11. yazısındaki (Lock'lar ve Blocking) ölçümü tekrarlıyoruz. 1.000 siparişi güncelleyen transaction açıkken kendi lock'larımızı sayıyoruz; sonra aynısını 20.000 sipariş için yapıyoruz:

BEGIN TRANSACTION;
UPDATE dbo.Orders SET Status = 'Cancelled' WHERE OrderId BETWEEN 990001 AND 991000;
SELECT resource_type, request_mode, COUNT(*) AS locks
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID()
GROUP BY resource_type, request_mode
ORDER BY resource_type;
ROLLBACK;

/* OPTIMIZED_LOCKING = OFF, 1.000 satır */
DATABASE|S|1
KEY|X|1000
OBJECT|IX|1
PAGE|IX|17

/* OPTIMIZED_LOCKING = OFF, 20.000 satır */
DATABASE|S|1
OBJECT|X|1

/* OPTIMIZED_LOCKING = ON, 1.000 satır */
DATABASE|S|1
OBJECT|IX|1
XACT|X|1

/* OPTIMIZED_LOCKING = ON, 20.000 satır */
DATABASE|S|1
OBJECT|IX|1
XACT|X|1

Klasik locking'de 1.000 key lock ve 17 page lock transaction sonuna kadar duruyor; 20.000 satırda escalation devreye giriyor ve tablonun tamamına X konuyor. Optimized locking'de ise satır sayısı ne olursa olsun tek bir XACT lock var. Her satıra, onu son değiştiren transaction'ın kimliği (TID) yazılıyor. Satır değiştirilirken yine kısa süreli bir X key lock alınıyor ama satır değişir değişmez bırakılıyor. Transaction boyunca korunan tek şey, kendi TID'sinin üzerindeki X. Lock sayısı satır sayısıyla büyümediği için escalation eşiğine de ulaşılmıyor; 11. yazıda 6.249 lock'ta tablonun tamamını kilitleyen işlem burada tabloyu açık bırakıyor.

Peki başka bir session aynı satırı değiştirmek isterse ne olur? Satırda yazan TID'ye bakar, o transaction hâlâ açıksa TID'ye S lock ister ve bekler:

/* session 2, session 1 açıkken */
UPDATE dbo.StockItems SET QuantityOnHand += 10 WHERE BookId = 1;

session_id|blocking_session_id|wait_type|wait_resource
----------|-------------------|---------|-------------
54|0|WAITFOR|
60|54|LCK_M_S_XACT_MODIFY|XACT: 56:12750:0 KEY: 56:72057594047561728 (8194443284a0)

Blocking hâlâ var; aynı satırı değiştirmek isteyen iki transaction'dan biri her zaman bekler. Değişen, beklemenin neyin üzerinde olduğu. Wait type artık LCK_M_U ya da LCK_M_X değil, LCK_M_S_XACT_MODIFY: değiştirmek niyetiyle XACT (transaction) kaynağını bekliyor. Okuma niyetiyle beklerse LCK_M_S_XACT_READ görürsün. wait_resource hem transaction'ı (XACT: 56:12750:0) hem de altındaki satırı gösteriyor. Monitoring script'lerin LCK_M_% ile filtreliyorsa bu yeni wait'leri zaten yakalar; ama wait type'a göre ayrı ayrı alarm kurduysan listeyi güncellemelisin.

Lock After Qualification: Blocking'in Kaybolması

Açılıştaki deneye dönelim ve bu sefer ikinci session'ın neyi beklediğine bakalım. Klasik locking'de (RCSI açık olsa bile):

session_id|blocking_session_id|wait_type|wait_resource
----------|-------------------|---------|-------------
54|0|WAITFOR|
62|54|LCK_M_U|KEY: 56:72057594047561728 (8194443284a0)

RCSI okuyucuların lock almasını kaldırır ama bir UPDATE'in hangi satırları değiştireceğini bulmak için yaptığı tarama okuma sayılmaz. Klasik modelde UPDATE taradığı her satıra önce U lock alır, predicate'i kontrol eder, uymuyorsa lock'u bırakıp devam eder. 1 numaralı kitabın satırında birinci session'ın X'i var; U verilemez, tarama orada durur. Predicate'in o satıra uyup uymadığı bile henüz bilinmiyor.

LAQ bu sırayı tersine çevirir. Predicate, satırın son commit edilmiş versiyonu üzerinde ve lock almadan değerlendirilir. Uymuyorsa satır atlanır; uyuyorsa o zaman X lock alınır ve satır değiştirilir. 1 numaralı kitabın son commit edilmiş stoğu 36; QuantityOnHand = 0 şartına uymuyor, atlanıyor. Sonuç: ikinci session'ın listesinde bekleyen bir satır yok, 207 kitabı stokluyor ve bitiyor.

Bu yüzden LAQ'ın en büyük kazancı, farklı satırlara dokunan ama aynı satırları tarayan işler arasındaki blocking'in kalkması. Klasik modelde index'i olmayan bir predicate ile çalışan UPDATE, taradığı her kilitli satırda beklerdi. LAQ ile yalnız gerçekten değiştireceği bir satır kilitliyse bekler.

LAQ iyimser çalışır. Predicate'i kontrol ettikten sonra satır başka bir transaction tarafından değiştirilmişse, SQL Server o transaction'ın bitmesini bekler ve predicate'i yeniden değerlendirir (requalification). Bazı plan operator'leri bunu desteklemez; o durumda statement içeride iptal edilip LAQ olmadan baştan çalıştırılır ve lock_after_qual_stmt_abort event'i üretilir. Bu yeniden işleme sık olursa, logical read'lerle ölçülen bir geri besleme mekanizması LAQ'ı plan ya da veritabanı bazında geçici olarak kapatır.

Davranış Değişikliği: Sonucun Farklılaştığı Yer

Açılıştaki tablonun ikinci satırı LAQ'ın bedeli. İkinci session'ın predicate'i QuantityOnHand = 0. LAQ, 1 numaralı kitabı son commit edilmiş değere, yani 36'ya bakarak eledi. Birinci session'ın sıfırlaması o anda commit edilmemişti. Klasik modelde ise ikinci session bekledi, birinci commit edince satırın güncel değerini (0) okudu ve kitabı stokladı. Yani klasik modelde sonuç "işlerin sırası"na göre, LAQ'da ise "statement başladığında commit edilmiş olan"a göre belirleniyor.

Microsoft'un dokümanı burada net: RCSI altında, locking hint'i olmadan, veritabanı motorunun transaction'ları katı bir sıraya koymasını varsaymamalısın; LAQ olmadan da bu bir garanti değildir. Katı sıraya dayanan iş akışları için önerilen yol daha sıkı isolation level'lar: REPEATABLE READ ya da SERIALIZABLE. Bookstore örneğinde gerçek çözüm başka: stok yenileme işi, yarım kalmış satışları gözden kaçırıyorsa bir sonraki çalışmasında onları yakalar. Bu, LAQ'ı açmadan önce hangi işlerin sıraya güvendiğini bulman gerektiği anlamına geliyor.

Böyle bir iş bulduysan, ve izole etmek istiyorsan, hint ile LAQ'ı o statement için kapatabilirsin:

/* session 2: restock every sold-out book */
UPDATE dbo.StockItems WITH (UPDLOCK) SET QuantityOnHand += 10 WHERE QuantityOnHand = 0;
SELECT @@ROWCOUNT AS restocked;

session_id|blocking_session_id|wait_type|wait_resource
----------|-------------------|---------|-------------
54|0|WAITFOR|
60|54|LCK_M_S_XACT_MODIFY|XACT: 56:12754:0 KEY: 56:72057594047561728 (8194443284a0)

restocked
---------
208

Session yine bekliyor ve sonuç klasik modeldeki gibi 208 kitap; 1 numaralı kitap da stoklanıyor. Bekleme hâlâ XACT üzerinden, çünkü TID locking açık; kapanan yalnız LAQ. Dokümana göre LAQ şu durumlarda kullanılmaz:

• UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK gibi çakışan locking hint'leri varsa.
• Isolation level READ COMMITTED değilse ya da READ_COMMITTED_SNAPSHOT kapalıysa.
• Tabloda columnstore index varsa, statement'ta değişken ataması varsa ya da OUTPUT bir table variable'a yazıyor veya result set döndürüyorsa.
• Değiştirilecek satırları okumak için birden fazla seek/scan operator'ü kullanılıyorsa ve tüm MERGE statement'larında.
• LAQ heuristic'leri onu o plan ya da veritabanı için kapattıysa.

Hint'lerin bir yan etkisi daha var: bir tablodaki hint aynı sorgudaki başka tabloyu etkilemez. UPDATE ... FROM t5 JOIN t6 WITH (UPDLOCK) yazarsan, yalnız t6 lock alır; t5 optimized locking'den yararlanmaya devam eder.

Deadlock Graph'ta xactlock

Bir önceki yazıdaki ters erişim sırası deadlock'unu optimized locking açık veritabanında tekrar çalıştırdık. Deadlock yine oluşuyor; TID locking aynı satırı değiştirmek isteyen iki transaction'ın birbirini beklemesini engellemez. Ama graph farklı görünüyor (okunabilirlik için id attribute'larını çıkardık):

<resource-list>
  <xactlock xdesIdLow="12759" xdesIdHigh="0" dbid="56" mode="X">
    <UnderlyingResource>
      <keylock hobtid="72057594047430656" dbid="56" objectname="sqls_13.dbo.Orders" indexname="PK_Orders"/>
    </UnderlyingResource>
    <owner-list><owner id="processd9c930c58" mode="X"/></owner-list>
    <waiter-list><waiter id="processdb5a9f048" mode="S" requestType="wait"/></waiter-list>
  </xactlock>
  <xactlock xdesIdLow="12758" xdesIdHigh="0" dbid="56" mode="X">
    <UnderlyingResource>
      <keylock hobtid="72057594047561728" dbid="56" objectname="sqls_13.dbo.StockItems" indexname="PK_StockItems"/>
    </UnderlyingResource>
    <owner-list><owner id="processdb5a9f048" mode="X"/></owner-list>
    <waiter-list><waiter id="processd9c930c58" mode="S" requestType="wait"/></waiter-list>
  </xactlock>
</resource-list>

Kaynaklar artık keylock değil xactlock: her biri bir transaction kimliği, sahibi X, bekleyen S modunda. Hangi satır için beklendiğini UnderlyingResource altındaki keylock söylüyor; okuma yöntemi aynı, yalnız bir seviye daha derine bakıyorsun. Deadlock'ları index adlarına göre gruplayan bir script yazdıysan, UnderlyingResource'u da okuyacak şekilde güncellemen gerekir. Kurban seçimi değişmedi: logused değeri düşük olan checkout yine kurban oldu.

SQL Server 2019/2022'de Farkı

Optimized locking SQL Server 2022 ve öncesinde yok; OPTIMIZED_LOCKING seçeneği, XACT kaynakları, LCK_M_S_XACT_* wait'leri ve xactlock elementi yalnız 2025'te var. 2019 ve 2022'de ADR ve RCSI'ı açabilirsin ama UPDATE'ler klasik modelle U lock alarak tarar ve key lock'ları transaction sonuna kadar tutar; açılıştaki deneyin sonucu her zaman ilk satırdaki gibidir. Bunun pratik bir sonucu var: Azure SQL Database'de geliştirip on-premises SQL Server 2022'ye deploy edilen bir uygulama, iki ortamda farklı blocking ve farklı sonuç görebilir. Azure SQL Database'de RCSI ve optimized locking varsayılan olarak açık, 2022'de ikisi de yok.

Üretimde Dikkat

Açmadan önce sıraya güvenen işleri bul. LAQ'ın değiştirdiği şey, "beklersem güncel değeri görürüm" varsayımı. Durum alanına göre çalışan toplu UPDATE'ler (stok yenileme, süresi dolan rezervasyonları iptal etme, "Pending" siparişleri işleme) ilk adaylar. Bunları ya idempotent ve tekrar çalıştırılabilir yap ya da daha sıkı bir isolation level ile çalıştır.

Ölçerek aç. locking_stats Extended Events event'i her veritabanı için birkaç dakikada bir escalation sayısını, TID locking ve LAQ'ın açık olup olmadığını ve LAQ'ın kaç sorguda hangi nedenle kullanılmadığını raporlar; özellik kapalıyken de çalışır. Açmadan önce bir hafta toplamak, sonra karşılaştırmak iyi bir başlangıç. lock_after_qual_stmt_abort event'i ve sys.query_store_plan_feedback'te LAQ Feedback satırları, yeniden işlenen statement'ları gösterir.

Lock hint'lerini gözden geçir. Optimized locking, hint'siz sorgulara en çok fayda sağlar. Eski kodda "deadlock oluyordu, UPDLOCK ekledik" diye kalmış hint'ler, LAQ'ı o statement için kapatır. Gerçekten gerekli olanları tut, kalanları ölçerek kaldır.

Kapsamı bil. Optimized locking yalnız DML (Data Manipulation Language) statement'larının satır ve page lock'larını etkiler; şema lock'ları aynı kalır. 11. yazıdaki Sch-M zinciri optimized locking açıkken de yaşanır. tempdb ve temp table'larda da şu an kullanılmaz.

Sık Düşülen Tuzaklar

Optimized locking'i RCSI olmadan açıp LAQ beklemek. TID locking çalışır, lock sayısı düşer, escalation kaybolur; ama UPDATE'ler hâlâ U lock ile tarar ve açılıştaki blocking aynen sürer. Faydanın büyük kısmı ikisinin birlikte açılmasından gelir.

"Blocking bitti" sanmak. Aynı satırı değiştirmek isteyen iki transaction hâlâ birbirini bekler; deadlock'lar da devam eder. Optimized locking blocking'in nedenini değiştirir, varlığını değil. Bir önceki yazıdaki retry ve idempotency kuralları geçerli.

Eski wait type'larına göre kurulmuş alarmlar. LCK_M_U ve LCK_M_X'e bakan bir dashboard, optimized locking'den sonra blocking'in "kaybolduğunu" gösterebilir; aslında bekleme LCK_M_S_XACT_MODIFY'a taşınmıştır.

Azure SQL ile on-premises arasındaki farkı test etmemek. Aynı entegrasyon testi iki ortamda farklı sonuç verebilir. Testleri production'daki ayarlarla aynı ayarlarda çalıştır; Docker'daki SQL Server 2025 image'ında optimized locking ve RCSI varsayılan olarak kapalıdır.

Küçük Bir Deneme

★☆☆ Optimized locking açıkken RCSI'ı kapat (READ_COMMITTED_SNAPSHOT OFF) ve açılıştaki deneyi tekrarla. İkinci session bekliyor mu, hangi wait type ile? Sonuç kaç?

★★☆ Stok yenileme UPDATE'ini OUTPUT inserted.BookId ile yaz ve result set döndür. LAQ kullanılıyor mu? Dokümandaki istisnalar listesiyle açıkla.

★★★ locking_stats event'ini toplayan, MAX_DURATION'lı bir Extended Events session'ı kur. 11. yazıdaki escalation deneyini optimized locking kapalı ve açıkken çalıştır ve event'teki escalation sayılarını karşılaştır.

Concurrency bloğunu burada kapatıyoruz. Lock'lar, deadlock'lar ve optimized locking, veritabanının aynı anda çalışan işleri nasıl koruduğunu anlatıyordu. Bir sonraki yazıda veritabanında çalışan koda geçiyoruz: stored procedure'ler, parameter'lar, hata yönetimi, ownership chaining ile yetki ve güvenli dynamic SQL.