PostgreSQL'de Transaction, Isolation ve Locking: Serializable SSI, SELECT FOR UPDATE SKIP LOCKED ve Advisory Lock

Sürüm notu: Bu yazıdaki örnekler PostgreSQL 18.7 (resmî postgres:18 Docker image'ı), .NET 10, Npgsql 10.0.3 ve Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 ile, 02'de kurduğumuz Bookstore lab'ında, şema yeniden yüklendikten hemen sonra çalıştırılmıştır.

Önce Tahmin Et

Sitedeki serilerin ortak örnek uygulaması Bookstore (bir online kitapçı) için iki depo görevlisi aynı anda aynı kitabın stoğunu düzeltiyor. Uygulama stoğu okuyor, yeni değeri C# tarafında hesaplıyor ve geri yazıyor. Kayıp güncellemeyi önlemek için ekipten biri isolation level'ı REPEATABLE READ yapmış. İki oturum (A ve B) aynı anda şunu çalıştırıyor; A, B'den önce commit ediyor:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT quantity_on_hand FROM stock_items WHERE book_id = 1;     /* ikisi de 36 görür */
UPDATE stock_items SET quantity_on_hand = 35 WHERE book_id = 1;
COMMIT;

SQL Server'da REPEATABLE READ, okunan satırda shared lock'u transaction sonuna kadar tutar; iki oturum da update için diğerinin lock'unu bekler ve biri deadlock kurbanı olur. PostgreSQL'de B'nin UPDATE'i ne olur? Aynı denemeyi varsayılan READ COMMITTED ile yapsak?

/* B, REPEATABLE READ */
UPDATE stock_items SET quantity_on_hand = 35 WHERE book_id = 1;
ERROR:  could not serialize access due to concurrent update

/* B, READ COMMITTED */
UPDATE stock_items SET quantity_on_hand = 35 WHERE book_id = 1;
UPDATE 1
COMMIT

Kimse beklemedi, deadlock da olmadı. REPEATABLE READ'de B, kendi snapshot'ından sonra başka bir transaction'ın değiştirdiği satırı güncellemeye çalıştığı için hata aldı (SQLSTATE 40001). READ COMMITTED'de ise iki UPDATE da başarılı oldu, stok 34 olması gerekirken 35'te kaldı: klasik lost update. Bu yazıda PostgreSQL'in isolation level'larını SQL Server karşılıklarıyla, row lock modlarını, SKIP LOCKED ile veritabanında job queue'yu, advisory lock'u, deadlock ve timeout'ları ve .NET tarafında 40001 hatasında retry'ı görüyoruz.

Isolation Level'lar: SQL Server ile Karşılaştırma

08'de gördüğümüz MVCC (Multi-Version Concurrency Control; satırın birden çok sürümünü tutan eşzamanlılık yöntemi) yüzünden PostgreSQL'de okuyan, yazanı hiçbir isolation level'da beklemez. Fark, snapshot'ın ne zaman alındığında ve çakışmanın nasıl ele alındığında. Tablodaki RCSI (Read Committed Snapshot Isolation; SQL Server'da okuyanın son commit edilmiş sürümü okuduğu mod) ile karşılaştırarak:

SQL Server Derinlemesine serisinin isolation level yazısıyla kıyaslarsak: PostgreSQL'in READ COMMITTED'i RCSI'ya, REPEATABLE READ'i SQL Server'ın SNAPSHOT'ına, SERIALIZABLE'ı ise lock yerine çakışma tespitiyle çalışan bir SNAPSHOT'a karşılık geliyor. Bu yüzden PostgreSQL'de REPEATABLE READ ve SERIALIZABLE kullanan kod, 40001 hatasını yakalayıp transaction'ı baştan tekrar etmek zorunda.

Lost update'i READ COMMITTED'de önlemenin en basit yolu, hesabı veritabanına bırakmak: SET quantity_on_hand = quantity_on_hand - 1. PostgreSQL ikinci UPDATE'te satırın commit edilmiş son hâlini yeniden okur ve 34 yazar. Hesap mutlaka uygulamada yapılacaksa ya satırı SELECT ... FOR UPDATE ile kilitlersin ya da 12. yazıda göreceğimiz xmin concurrency token'ı ile optimistic kontrol yaparsın.

SERIALIZABLE ve SSI: Write Skew ve 40001

REPEATABLE READ'in yakalamadığı bir anomali var: iki transaction aynı veriyi okuyup farklı satırlar yazdığında oluşan write skew. Bookstore'da kural şu: bir müşterinin aynı anda en fazla bir Pending siparişi olabilir. Kod önce kontrol ediyor, sonra ekliyor. İki istek aynı anda geliyor; snapshot'taki TxDemo.cs, iki isteğin de kontrolü bitirmesini bekleyip sonra eklemeye geçiyor:

await using NpgsqlTransaction transaction = await connection.BeginTransactionAsync(isolation);

await using NpgsqlCommand check = new(
    "SELECT count(*) FROM orders WHERE customer_id = @customerId AND status = 'Pending'", connection, transaction);
check.Parameters.AddWithValue("customerId", CustomerId);
long pending = (long)(await check.ExecuteScalarAsync())!;

if (attempt == 1)
{
    bothChecked.SignalAndWait(); // iki istek de kontrolü yaptı, şimdi ekliyorlar
}
if (pending > 0)
{
    await transaction.CommitAsync();
    return $"{name}: saw {pending} pending order, nothing inserted";
}
/* INSERT INTO orders ... 'Pending' ... ; COMMIT */

$env:BOOKSTORE_PG = "Host=localhost;Port=5432;Database=bookstore;Username=bookstore_app;Password=<app-password>"
docker exec -it bookstore-pg psql -U bookstore_owner -d bookstore -f /lab/09-transactions.sql
dotnet run --project src/Bookstore.PgLab -- write-skew

-- READ COMMITTED
A: saw 0 pending orders, inserted BK-TA1
B: saw 0 pending orders, inserted BK-TB1
pending orders for customer 1234: 2
-- SERIALIZABLE
A: saw 0 pending orders, inserted BK-TA1
B: 40001 could not serialize access due to read/write dependencies among transactions
pending orders for customer 1234: 1
-- SERIALIZABLE with retry
A: attempt 1 failed with 40001, retrying
A: saw 1 pending order, nothing inserted
B: saw 0 pending orders, inserted BK-TB1
pending orders for customer 1234: 1

READ COMMITTED'de kural delindi: iki bekleyen sipariş. REPEATABLE READ da aynı sonucu verirdi, çünkü iki transaction farklı satırlar ekledi; güncellenen ortak bir satır yok. SERIALIZABLE'da PostgreSQL, iki transaction'ın birbirinin okuduğu veriyi değiştirdiğini fark etti ve birini 40001 ile durdurdu. SSI (Serializable Snapshot Isolation; transaction'ları kilitlemeden, okuma ve yazma bağımlılıklarını izleyerek seri çalışmış gibi sonuç veren yöntem) bunu predicate lock'larla yapar; bunlar kimseyi bekletmez, yalnızca çakışmayı tespit eder. Son blokta retry ile tekrar denenen istek bekleyen siparişi gördü ve eklemedi. Doğru sonuç ancak retry ile birlikte gelir. customer_id üzerindeki index de önemli: index yoksa predicate lock tablo seviyesine çıkar ve ilgisiz müşterilerin istekleri de birbirini 40001 ile düşürmeye başlar.

Row Lock Modları: FOR UPDATE, FOR NO KEY UPDATE ve Foreign Key

PostgreSQL'de dört row lock modu var; güçlüden zayıfa FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE ve FOR KEY SHARE. Bir UPDATE, key kolonlarını değiştirmiyorsa FOR NO KEY UPDATE alır; foreign key kontrolü ise parent satırda FOR KEY SHARE alır. Bu ikisi birbiriyle çakışmaz. Uygulamada sık yazılan SELECT ... FOR UPDATE ise çakışır. A oturumu kitabın fiyatını güncellemek için satırı kilitliyor, B oturumu o kitap için sipariş kalemi ekliyor:

/* A */
BEGIN;
SELECT book_id, price FROM books WHERE book_id = 1 FOR UPDATE;

/* B */
SET lock_timeout = '1s';
BEGIN;
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES (1, 1, 1, 10.00);

ERROR:  canceling statement due to lock timeout
CONTEXT:  while locking tuple (0,1) in relation "books"
SQL statement "SELECT 1 FROM ONLY "public"."books" x WHERE "book_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

CONTEXT satırı foreign key kontrolünün ne yaptığını açıkça gösteriyor. A aynı satırı FOR NO KEY UPDATE ile kilitlediğinde B'nin INSERT'ü beklemeden geçti. Kural: satırı güncellemek için kilitliyorsan ve primary key'i değiştirmeyeceksen FOR NO KEY UPDATE yaz. Yoğun bir kataloğun fiyat güncellemesi, aksi hâlde aynı kitaba gelen bütün siparişleri bekletir.

SKIP LOCKED ile Veritabanında Job Queue

Message Queue'larla Çalışmak serisinin outbox yazısında mesajları önce veritabanındaki bir tabloya yazıp sonra bir relay ile broker'a gönderiyorduk. Relay'i birden fazla instance ile çalıştırınca soru şu: aynı mesajı iki worker almasın, ama birbirlerini de beklemesinler. 09-transactions.sql 1000 mesajlık bir outbox_messages tablosu oluşturuyor; her worker şu sorguyla 10'luk bir paket alıyor, 20 ms'lik "gönderme" işini yapıyor ve paketi işlenmiş olarak işaretleyip commit ediyor:

SELECT outbox_message_id FROM outbox_messages
WHERE processed_at IS NULL
ORDER BY outbox_message_id
LIMIT 10
FOR UPDATE SKIP LOCKED

-- FOR UPDATE
worker-1: 200 messages
worker-2: 0 messages
worker-3: 330 messages
worker-4: 470 messages
total: 1000, duplicates: 0, elapsed: 2.4 s
-- FOR UPDATE SKIP LOCKED
worker-1: 250 messages
worker-2: 250 messages
worker-3: 250 messages
worker-4: 250 messages
total: 1000, duplicates: 0, elapsed: 0.6 s

SKIP LOCKED olmadan dört worker da aynı ilk 10 satırı istedi ve sıraya girdi. Bekleyen worker, kilit açılınca satırların artık koşulu sağlamadığını gördü (READ COMMITTED satırı yeniden kontrol eder) ve boş sonuçla döndü. Kod bunu "kuyruk boş" diye yorumladığı için worker-2 hiç mesaj işlemeden çıktı; iş fiilen sıralı yürüdü. SKIP LOCKED ile her worker kilitli satırları atlayıp sıradaki boş paketi aldı: eşit dağılım, dörtte bir süre, tekrar yok. Partial index (WHERE processed_at IS NULL) taramayı yalnızca bekleyen mesajlarla sınırlıyor. İşlenen mesajları düzenli silmeyi ya da arşivlemeyi unutma; yoğun güncellenen bu tablo 08'deki autovacuum ayarlarının tam hedefi.

Advisory Lock: Tek Instance'ta Çalışacak İş

Gece çalışan stok senkronizasyonu üç API instance'ında da zamanlanmış, ama aynı anda yalnız biri çalışmalı. Advisory lock, anlamını uygulamanın verdiği bir sayıya alınan kilit; hiçbir tabloyla ilgisi yok. Background Job'lar ve Scheduled Job'lar serisinin çok instance'lı ortam yazısında distributed lock'u job scheduler açısından ele almıştık:

SELECT pg_try_advisory_xact_lock(hashtext('nightly-stock-sync'))

instance-1: another instance is running, skipped
instance-2: ran the sync
instance-3: another instance is running, skipped

_xact_ sürümü kilidi transaction bitince kendiliğinden bırakır; bağlantı koparsa da kilit gider, "sahipsiz kilit" kalmaz. Session seviyesindeki pg_advisory_lock ise elle bırakılana ya da bağlantı kapanana kadar kalır; connection pool ile birlikte kullanırken dikkat ister. try sürümü beklemez, kilidi alamazsa false döner; job'ı atlamak için doğru seçim bu.

Deadlock, Timeout'lar ve DDL Kuyruğu

İki transaction stok satırlarını ters sırada güncellediğinde:

ERROR:  deadlock detected
DETAIL:  Process 909 waits for ShareLock on transaction 1404; blocked by process 917.
Process 917 waits for ShareLock on transaction 1403; blocked by process 909.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "stock_items"
COMMIT;
ROLLBACK

PostgreSQL bir lock'u deadlock_timeout (varsayılan 1 saniye) kadar bekledikten sonra deadlock kontrolü yapar ve döngüyü kuran transaction'lardan birini iptal eder; iki sorgunun metni server log'unda. Son iki satıra dikkat: hata alan transaction'da COMMIT, ROLLBACK olarak sonuçlanır. Önlemi SQL Server'dakiyle aynı: satırları her yerde aynı sırada (ör. book_id sırasıyla) kilitle; SQL Server Derinlemesine serisinin deadlock yazısındaki pattern'ler burada da geçerli.

Bekleme sürelerini sınırlayan dört ayar var: lock_timeout (bir lock için bekleme), statement_timeout (tek statement), idle_in_transaction_session_timeout (transaction içinde boşta bekleme; 08'de VACUUM'u durduran oturumun çözümü) ve 17 ile gelen transaction_timeout (transaction'ın toplam süresi). Hepsi varsayılan olarak kapalı; role ya da database seviyesinde ALTER ROLE bookstore_app SET statement_timeout = '30s' ile vermek iyi bir başlangıç.

PostgreSQL'de DDL (Data Definition Language; CREATE, ALTER gibi şema komutları) de transactional: migration'daki ALTER TABLE hata verirse bütün değişiklik geri alınır. Ama çoğu ALTER TABLE ACCESS EXCLUSIVE lock ister ve bunun bir yan etkisi var. A uzun bir rapor çalıştırırken B migration'da kolon ekliyor, C de sıradan bir okuma yapıyor:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type, left(query, 45) AS query
FROM pg_stat_activity
WHERE datname = 'bookstore' AND pid <> pg_backend_pid() AND backend_type = 'client backend'
ORDER BY backend_start;

 pid | blocked_by | state  | wait_event_type |                   query
-----+------------+--------+-----------------+--------------------------------------------
 852 | {}         | active | Timeout         | SELECT pg_sleep(4);
 867 | {852}      | active | Lock            | ALTER TABLE books ADD COLUMN subtitle text
 881 | {867}      | active | Lock            | SELECT title FROM books WHERE book_id = 2

C'nin okuması A ile çakışmıyor, ama kuyrukta kendisinden önce bekleyen ALTER TABLE'ın arkasına girdi. Uzun bir rapor sürdükçe migration bekler, migration bekledikçe tabloya gelen bütün sorgular bekler; production'da kısa bir kesinti böyle başlar. Çözüm, migration oturumunda SET lock_timeout = '3s' verip lock alınamazsa migration'ı kısa bir aradan sonra yeniden denemek.

.NET Tarafı: SqlState ile Retry ve EF Core Execution Strategy

Npgsql, sunucu hatalarını PostgresException olarak fırlatır; hangi hata olduğunu SqlState söyler. Retry edilecek kodlar: 40001 (PostgresErrorCodes.SerializationFailure) ve 40P01 (DeadlockDetected). Yukarıdaki write skew örneğindeki retry tam olarak bu:

catch (PostgresException ex) when (retry && ex.SqlState == PostgresErrorCodes.SerializationFailure && attempt < 5)
{
    Console.WriteLine($"{name}: attempt {attempt} failed with {ex.SqlState}, retrying");
    await Task.Delay(Random.Shared.Next(10, 50) * attempt);
}

Retry edilen şey tek bir komut değil, bütün transaction: okuma da tekrarlanmalı ki ikinci denemede güncel veriyle karar verilsin. Bekleme süresine rastgelelik katmak, aynı anda düşen isteklerin yine aynı anda çakışmasını önler.

Entity Framework Core'da (EF Core; tabloları C# nesnelerine eşleyen Microsoft kütüphanesi) EnableRetryOnFailure bu işi bir execution strategy ile yapar. Npgsql provider'ı, PostgresException.IsTransient doğru olan hataları geçici sayar; 40001 ve 40P01 bu listede. Ama strategy yalnızca kendi çalıştırdığı işi tekrar edebilir:

DbContextOptions<ReportingContext> options = new DbContextOptionsBuilder<ReportingContext>()
    .UseNpgsql(connectionString, npgsql => npgsql.EnableRetryOnFailure(maxRetryCount: 3))
    .Options;

// bütün iş strategy'nin içinde: retry transaction'ı baştan başlatır
IExecutionStrategy strategy = db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
    attempts++;
    await using IDbContextTransaction transaction = await db.Database.BeginTransactionAsync(IsolationLevel.Serializable);
    await db.Database.ExecuteSqlAsync(
        $"UPDATE stock_items SET quantity_reserved = quantity_reserved + 1 WHERE book_id = {1}");
    /* ilk denemede 40001 simüle edilir */
    await transaction.CommitAsync();
});

ExecuteSql inside a user transaction: ok
The configured execution strategy 'NpgsqlRetryingExecutionStrategy' does not support user-initiated transactions. Use the execution strategy returned by 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as a retriable unit.
committed after 2 attempt(s)

İlk iki satır, strategy'nin dışında BeginTransactionAsync açıp iş yaptığımız denemeden: ExecuteSqlAsync sessizce geçti, ama ilk EF Core sorgusu (db.Books.CountAsync()) exception verdi. Yani kontrol her komutta yapılmıyor; hatayı ancak transaction içinde bir sorgu çalışınca görürsün. Doğru kullanımda ilk deneme 40001 aldı (RAISE EXCEPTION ... USING ERRCODE = 'serialization_failure' ile simüle ettik), strategy transaction'ı baştan çalıştırdı ve stok bir kez arttı. SaveChanges'ı kendi başına çağırdığında strategy bunu zaten kendisi sarar; ayrı bir transaction açıyorsan bu yapı şart.

Üretimde Dikkat

Lock beklemelerini görmek için log_lock_waits = on aç: deadlock_timeout'tan uzun bekleyen her lock log'a yazılır. Anlık durum için pg_stat_activity'deki wait_event_type = 'Lock' satırları ve pg_blocking_pids() yeterli.

SERIALIZABLE'ı seçeceksen bütün yazan transaction'larda kullan; aynı veriye READ COMMITTED ile yazan bir transaction SSI'ın korumasının dışında kalır. 40001 oranını metrik olarak izle; yükseliyorsa transaction'lar çok uzun ya da gerekli index'ler eksik.

Uygulama rolüne statement_timeout ve idle_in_transaction_session_timeout, migration oturumuna lock_timeout ver. Uzun raporları ayrı bir rolle ve gerekiyorsa read replica'da (Veritabanı Performansı ve Scaling serisi) çalıştır.

Sık Düşülen Tuzaklar

Transaction içinde dış servis çağırmak: ödeme servisi 30 saniye cevap vermezse satır lock'ları 30 saniye tutulur ve 08'de gördüğümüz gibi VACUUM durur. Dış çağrıyı transaction'ın dışına al.

40001'i "beklenmeyen hata" diye loglayıp kullanıcıya 500 dönmek: REPEATABLE READ ve SERIALIZABLE'da bu hata işin normal bir parçası; retry'sız kullanılamazlar.

NOWAIT ile SKIP LOCKED'ı karıştırmak: NOWAIT kilitli satır görünce hata (55P03) fırlatır, SKIP LOCKED satırı atlar. Kuyruk için ikincisi, "bu kayıt şu an başkası tarafından düzenleniyor" uyarısı için birincisi.

Migration'ı lock_timeout'suz çalıştırmak: yukarıdaki kuyruk etkisiyle tek bir ALTER TABLE, tablodaki bütün trafiği durdurabilir.

Küçük Bir Deneme

★☆☆ Önce Tahmin Et'teki denemeyi SET quantity_on_hand = quantity_on_hand - 1 ile READ COMMITTED'de tekrarla ve stoğun 34 olduğunu doğrula. B'nin UPDATE'i A commit edene kadar bekledi mi?

★★☆ queue demosunda worker sayısını 8'e çıkar ve paket boyutunu 1 yap. SKIP LOCKED'lı sürümün süresi nasıl değişti, neden?

★★★ Write skew demosunda orders tablosundaki ix_orders_customer_id index'ini sil ve iki farklı müşteri için aynı anda sipariş açan iki SERIALIZABLE transaction çalıştır. Birbirleriyle ilgisiz olmalarına rağmen 40001 alıyorlar mı? pg_locks'ta mode = 'SIReadLock' satırlarına bak.

Bir sonraki yazıda Bookstore'un kitap aramasını PostgreSQL'in full-text search'üyle kuruyoruz: tsvector, tsquery, Türkçe configuration, unaccent ve typo toleransı için pg_trgm.