PostgreSQL Kilitlerini Okumak: Blocking, Deadlock ve Uzun Transaction'lar

İstemci uygulamalarında anlık gecikme (latency) artışları, bağlantı havuzu tükenmesi ve basamaklı HTTP 504 Gateway Timeout hataları yaşandığında, ekiplerin ilk refleksi genellikle donanım yetersizliğine—yüksek CPU, bellek darboğazı veya disk I/O doyumuna—odaklanmaktır. Oysa Çok Sürümlü Eşzamanlılık Denetimi (MVCC) mimarisiyle çalışan PostgreSQL sistemlerinde, en ağır verim düşüşleri çoğu zaman CPU kullanımı %15'in altındayken ve disk I/O tamamen rölantideyken gerçekleşir.

Üretim sistemlerinde aceleci ve yıkıcı komutlar çalıştırmadan önce üç temel arıza profilini birbirinden ayırmak şarttır:

1. 1CPU / Disk Doyumu: Tablo taramaları (Seq Scan), kartezyen döngüler veya diske dökülen sıralama işlemleri (temporary files) nedeniyle tüm çekirdekler hesaplama yapmaktadır.
2. 2Kilitsiz Bağlantı Doyumu: İstemciler veritabanının yeni backend süreci açma veya havuzlayıcının bağlantı atama hızından daha hızlı istek açmaktadır; sorgular hızlı biter ancak kapıda kuyruk oluşur.
3. 3Kilit Bekleme Darboğazı: Backend süreçleri atanmıştır fakat işlem durumu çalışır halden wait_event_type = 'Lock' durumuna geçmiştir. Oturumlar, başka bir oturum tarafından tutulan satır, tablo veya transaction kilidini bekleyerek uyumaktadır.

PostgreSQL'in MVCC mimarisinin temel sloganı genellikle *"Okuyucular yazarları engellemez, yazarlar da okuyucuları engellemez"* şeklinde özetlenir. Bu kural, standart SELECT okumaları ile eşzamanlı UPDATE, DELETE veya INSERT işlemleri arasında geçerli olsa da, standart izolasyon seviyelerinde (READ COMMITTED) kilitlenme yaşanmayacağı gibi yanıltıcı bir güven yaratır.

MVCC şunları önlemez:

• •Eşzamanlı Yazma-Yazma Çakışmaları: İki veya daha fazla eşzamanlı işlem aynı satırı UPDATE veya DELETE ile değiştirmeye çalıştığında ya da SELECT ... FOR UPDATE ile açıkça satır kilidi istediğinde, kilit yalnızca ilk işleme verilir. İkinci işlem, ilk işlem COMMIT veya ROLLBACK yapana kadar onun transaction ID'si üzerinde kilit kuyruğunda bekletilir.
• •DDL ve DML Çakışmaları: ALTER TABLE, CONCURRENTLY kullanılmadan açılan CREATE INDEX, DROP TABLE veya VACUUM FULL işlemleri AccessExclusiveLock talep eder. Bu kilit, masum bir SELECT sorgusunun tuttuğu AccessShareLock dahil istisnasız tüm kilit modlarıyla çakışır. Arka planda unutulmuş tek bir uzun SELECT, bekleyen bir DDL'i bloke eder; o DDL de arkasından gelen binlerce yeni SELECT sorgusunu sıraya dizerek tüm veritabanını felç eder.
• •Transaction Ömrü Kuralı: Bir transaction içinde alınan satır kilitleri, SQL ifadesi biter bitmez bırakılmaz. İlgili UPDATE ifadesi 1 milisaniyede bitmiş olsa bile, kilit o oturum COMMIT veya ROLLBACK gönderene kadar satır üzerinde asılı kalır.

Kilit beklemesi başladığında doğrudan pg_locks tablosunu birden fazla self-join ile birleştirmeye çalışmak sıklıkla yanıltıcı sonuçlar üretir; çünkü pg_locks PostgreSQL'in kilit kuyruk önceliğini ve uyumluluk matrisini tek başına göstermez.

Modern PostgreSQL sürümlerinde (v14–18), motorun çekirdek bekleme kuyruğunu doğrudan sorgulayan yerleşik pg_blocking_pids(pid) fonksiyonu standart tanı aracıdır. Bu fonksiyon, hedef oturumu doğrudan veya dolaylı olarak engelleyen tüm süreçlerin PID dizisini döndürür.

Kriz anında yapılan en yaygın hata, pg_stat_activity tablosunu salt query_start veya xact_start süresine göre sıralayıp en üstteki oturumu suçlu ilan etmektir.

• •xact_start: Transaction bloğunun başladığı an (BEGIN).
• •query_start: Mevcut SQL cümlesinin sunucuya iletildiği an.
• •state_change: Oturumun durum değiştirdiği an (örneğin kilit beklemesine girdiği veya idle in transaction durumuna geçtiği an).

30 dakikadır açık olan bir analitik sorgu yalnızca paylaşımlı okuma kilidi tutuyor olabilir ve kimseyi engellemez. Buna karşılık, 10 saniye önce bir satırı güncelleyip ardından uygulama tarafında hata aldığı için idle in transaction durumunda kalan bir mikroservis, arkasından gelen yüzlerce işlemi kilitleyebilir. Teşhiste her zaman bekleyen oturumlardan (pg_blocking_pids) geriye doğru kök bloklayıcıya yürünmelidir.

Bu iki olay arasındaki operasyonel fark kesin çizgilerle ayrılmalıdır:

1. 1Doğrusal Bloklama (Linear Blocking): Oturum A Kilit 1'i tutar, Oturum B Kilit 1'i bekler. Oturum A sonlanmadığı sürece Oturum B sonsuza kadar bekler (bir lock_timeout tanımlanmamışsa). Veritabanı motoru bu duruma kendiliğinden müdahale etmez.
2. 2Döngüsel Deadlock: Oturum A Satır 1'i kilitleyip Satır 2'yi ister; aynı anda Oturum B Satır 2'yi kilitleyip Satır 1'i ister. İki taraf da diğerinin kilidi bırakmasını beklediğinden sistem kilitlenir.

• •PostgreSQL, arka planda bir deadlock denetleyicisi çalıştırır. Bekleme süresi deadlock_timeout (varsayılan: 1000ms) değerini aştığında motor kilit grafını tarar.
• •Döngü tespit edildiğinde PostgreSQL oturumlardan birini kurban (victim) seçer, işlemini SQLSTATE 40P01 koduyla anında iptal eder ve transaction'ını geri alır.
• •Önemli Gerçek: Kurban abort edildiği anda döngü çözülür ve canlı kilit grafı normale döner. Bu nedenle gerçekleşmiş bir deadlock'un canlı pg_stat_activity üzerinden adli analizi yapılamaz; tek kanıt PostgreSQL sunucu loglarıdır.

Bu rehberdeki tüm iddialar, sentetik ve izole Docker laboratuvarında gerçek PostgreSQL motoru üzerinde yürütülerek kanıtlanmıştır.

Test Ortamı:

• •Motor Sürümü: PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2), 64-bit
• •İmaj Özeti (Digest): sha256:67f41722b7a8cbdb868a44a4995c846eddfdc2973bccb291ce937dce88ad5675
• •Parametreler: log_lock_waits = on, deadlock_timeout = 1000ms, log_line_prefix = '%m [%p] %q%u@%d app=%a '
• •Fixtür: İki hesap satırı: id=1 ($1000.00), id=2 ($500.00).

• •Senaryo: Oturum A (app_checkout_a, PID 113) id=1 satırını güncelledi. Oturum B (app_batch_b, PID 120) aynı satırı güncellemek istedi.
• •Örneklenen Telemetri:

• •Sonuç: Oturum A ROLLBACK gönderdiğinde Oturum B 0.04 saniyede kilidi aldı ve COMMIT etti. Bakiye başarıyla $1050.00 olarak doğrulandı.

• •Senaryo: Oturum A (PID 127) 1'i kilitleyip 2'yi istedi; Oturum B (PID 134) 2'yi kilitleyip 1'i istedi.
• •Motor Tepkisi: Tam 1000ms sonra PostgreSQL döngüyü saptadı:

• •Bulgu: Oturum A kurban seçilip abort edildi, Oturum B hayatta kalarak işlemini tamamladı. Kurban önceden bilinemez; uygulamanın her iki tarafta da 40P01 yakalaması gerekir.

İki durum da aynı hata sınıfını (55P03, lock_not_available) üretir ancak mekanizmaları farklıdır:

• •NOWAIT (3a): SELECT ... FOR UPDATE NOWAIT anında (0.0507 saniye) şu hatayı döndü:

ERROR: could not obtain lock on row in relation "accounts".

• •lock_timeout (3b): SET lock_timeout = '400ms' tam belirlenen süreyi bekleyerek (0.4048 saniye) şu hatayla durdu:

ERROR: canceling statement due to lock timeout.

• •Çıkarım: NOWAIT sıfır bekleme garantisi verir; lock_timeout ise kuyrukta birikmeyi üst sınıra bağlar.

57014 (query_canceled) kodu iki farklı senaryoda oluşur:

• •Statement Timeout (4a): statement_timeout = '500ms' ile çalışan sorgu 0.5215 saniyede otomatik düştü:

ERROR: canceling statement due to statement timeout.

• •Kullanıcı İptali (4b): pg_cancel_backend çalıştırıldığında oturum şu mesajla durdu:

ERROR: canceling statement due to user request.

• •Çıkarım: İzleme sistemleri yalnızca hata koduna bakarak operatör müdahalesi ile timeout aşımını ayıramaz; log metninin ayrıştırılması şarttır.

Bu deney, bağlantı sızıntılarının operasyonel tehlikesini ispatladı:

• •Oturum A (PID 162) satırı güncelleyip idle in transaction durumunda bekletildi.
• •İptal Denemesi: Gözlemci SELECT pg_cancel_backend(162); çalıştırdı.
• •Dönen Değer: cancel_returned_bool = true (başarılı döndü).
• •Sistem Gerçeği: PID 162 hala idle in transaction durumundaydı ve tablodaki RowExclusiveLock kilidini bırakmadı. pg_cancel_backend yalnızca aktif koşan sorguyu iptal eder; boşta duran transaction'ı kapatamaz!
• •Hedefli Sonlandırma: Gözlemci SELECT pg_terminate_backend(162); çalıştırdı.
• •Oturum sunucu seviyesinde tamamen kapatıldı.
• •PostgreSQL transaction'ı güvenle geri aldı ve kilitler anında serbest kalarak kuyruktaki işlemler çözüldü.

Kilitlenme olaylarında şu adımlar disiplinle izlenmelidir:

1. 1Kök Bloklayıcıyı Belirle: pg_blocking_pids sorgusunu çalıştırın. Asla rastgele bekleyen oturumları sonlandırmayın.
2. 2Bloklayıcının Durumunu Değerlendir:

• •Eğer state = 'active' ise: Bloklayıcı ağır bir hesaplama yapmaktadır. Önce güvenli iptal deneyin:

• •Eğer state = 'idle in transaction' ise: pg_cancel_backend kilitleri bırakmaz. İlgili servis sahibiyle görüşüp oturumu sonlandırın:

1. 1Kilitlerin Çözüldüğünü Doğrulayın: Bekleyen işlemlerin state = 'active' durumuna geçip başarıyla tamamlandığını izleyin.

Operasyonel ayrıntılar için doğrulanmış runbook prosedürlerimizi kullanın:

• •Kilit Zinciri ve Kök Bloklayıcıyı Çözme
• •Uzun ve Boşta Kalan Transaction'ları Yönetme
• •Olay Sonrası Deadlock İnceleme Prosedürü
• •Canlı Teşhis Scripti: Kim Kimi Blokluyor?

Kilit darboğazlarını kökten çözmek veritabanı ve uygulama katmanlarının ortak disiplinini gerektirir:

1. 1Deterministik Erişim Sıralaması: Eğer Servis A Satır 1 ve ardından Satır 2'yi güncelliyorsa, Servis B ise Satır 2 ve ardından Satır 1'i güncelliyorsa deadlock matematiksel bir kaçınılmazlıktır. Uygulama kodunda satır erişimlerini mutlaka deterministik bir sıraya dizin (SELECT ... FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE).
2. 2Transaction Sınırlarını Daraltmak: Veritabanı transaction bloğu içinde harici REST API çağrıları, e-posta gönderimi veya dosya yazımı yapmayın. Harici servis 5 saniye geciktiğinde veritabanınız 5 saniye boyunca kilit tutar.
3. 3Kademeli Timeout Hiyerarşisi:

• •idle_in_transaction_session_timeout = '15s': Unutulmuş bağlantı sızıntılarının tablo kilitlerini sonsuza kadar tutmasını engeller.
• •lock_timeout = '2s': Kilit kuyruğunda birikmeyi engelleyerek bağlantı havuzunun çökmesini önler.
• •statement_timeout = '15s': Kontrolden çıkmış sorguları sınırlar.

1. 1Jitter Destekli Yeniden Deneme (Exponential Backoff): Kilit çakışması (SQLSTATE 40P01 veya 55P03) yaşandığında istemcilerin anında yeniden deneme yapmasını engelleyin. Onlarca istemcinin aynı anda yeniden denemesi bir *retry fırtınası* (retry storm) yaratarak kilitlenmeyi kalıcı hale getirir. Rastgele gecikmeli (jittered) geri çekilme kullanın.

Bu konunun diğer kritik boyutları için kardeş platformlarımızdaki analizleri inceleyin:

• •Araç Seçimi (dataxtools): PostgreSQL Kilit Beklemelerinde Araç Seçimi: Canlı Oturumdan Geçmiş Olay Kanıtına
• •Olay Yönetimi & SRE (dataopsphere): Kilit Beklemesi Olayını Yönetmek: Sahiplik, Müdahale Kararı ve Toparlanma Kanıtı
• •Çekirdek Mimari: PostgreSQL MVCC & Concurrency Derin Analizi