Transaction, isolation level, locking, deadlock

Ringkasan satu halaman. Dari Lesson 0021. Skrip lab: dotnet/lab/0021-sesi-a.sql + dotnet/lab/0021-sesi-b.sql (dua jendela).

ACID ^[1]

• Atomicity: semua perubahan atau tidak sama sekali. Consistency: semua aturan (constraint) tetap terpenuhi. Isolation: transaction bersamaan tidak saling melihat data setengah jadi (lock / row versioning). Durability: yang di-commit bertahan walau crash (transaction log).
• Autocommit = default: tiap statement adalah transaction sendiri. Explicit transaction untuk beberapa statement.
• Default saat error run-time: hanya statement yang gagal yang di-rollback → tanpa XACT_ABORT ON, COMMIT bisa menyimpan setengah transaction.

Pola T-SQL ^{[2] [3] [4]}

SET XACT_ABORT ON;                          -- default OFF (ON di trigger)
BEGIN TRY
    BEGIN TRANSACTION;
        UPDATE dbo.Product SET Stock = Stock - 5 WHERE Id = 1;
        UPDATE dbo.Product SET Stock = Stock + 5 WHERE Id = 2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;  -- 1 aktif, -1 uncommittable (hanya ROLLBACK), 0 tidak ada
    THROW;
END CATCH;

• Nested BEGIN TRAN: COMMIT dalam hanya @@TRANCOUNT - 1; ROLLBACK di level mana pun membatalkan semuanya.

EF Core ^[5]

// Satu SaveChanges = satu transaction (default). Beberapa SaveChanges:
await using var tx = await context.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted);
await context.SaveChangesAsync();
await context.SaveChangesAsync();
await tx.CommitAsync();                     // exception → rollback saat dispose

• EnableRetryOnFailure aktif → bungkus dengan CreateExecutionStrategy().ExecuteAsync(...). ^[10]
• Lost update tanpa lock panjang: optimistic concurrency ([Timestamp] rowversion → DbUpdateConcurrencyException). ^[11]
• Node: prisma.$transaction(async tx => ...).

Isolation level ^{[1] [6]}

• Dirty read: baca data belum di-commit. Non-repeatable read: baris sama dibaca dua kali, nilai beda. Phantom: query sama, himpunan baris beda (INSERT/DELETE).
• UPDATE/DELETE/INSERT selalu memegang X lock sampai transaction selesai, apa pun level-nya.
• RCSI (READ_COMMITTED_SNAPSHOT ON): READ COMMITTED membaca versi per statement, pembaca tidak memblok penulis, transparan untuk aplikasi. SNAPSHOT (ALLOW_SNAPSHOT_ISOLATION ON + SET TRANSACTION ISOLATION LEVEL SNAPSHOT): versi per transaction. Keduanya OFF default di SQL Server 2022; RCSI ON di Azure SQL Database. Microsoft menyarankan RCSI. ^[7]

Lock dan blocking ^[1]

• Intent lock (IS/IX) di PAGE/OBJECT; granularitas KEY/RID → PAGE → OBJECT; escalation ke tabel bila satu statement ≥ 5.000 lock.
• Blocking menunggu tanpa batas kecuali SET LOCK_TIMEOUT (default -1 = tunggu selamanya; error 1222 "Lock request time out period exceeded."). ^{[12] [13]}
• Transaction pendek, tanpa input user / panggilan HTTP di tengahnya, sentuh data sesedikit mungkin.

SELECT session_id, blocking_session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;          -- siapa diblok siapa
SELECT request_session_id, resource_type, request_mode, request_status
FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID();       -- GRANTED / WAIT

Kedua DMV butuh VIEW SERVER PERFORMANCE STATE di SQL Server 2022. ^{[8] [9]}

Deadlock ^[7]

• Siklus: A pegang 1 minta 2, B pegang 2 minta 1. Blocking selesai sendiri; deadlock tidak.
• Deadlock monitor (default tiap 5 detik) memilih victim (paling murah di-rollback, atur dengan DEADLOCK_PRIORITY), me-rollback, error 1205.
• Cegah: urutan akses sama · transaction pendek, satu batch · tanpa input user · hindari REPEATABLE READ/SERIALIZABLE bila tidak perlu · RCSI · UPDLOCK untuk baca-lalu-ubah (atau satu UPDATE bersyarat).
• Tangani: tangkap SqlException.Number == 1205, ulang seluruh unit kerja setelah jeda acak. Deadlock graph: extended event system_health (xml_deadlock_report).

Jawaban 60–90 detik

Isolation level mengatur perilaku membaca: READ UNCOMMITTED (dirty read), READ COMMITTED default (cegah dirty read), REPEATABLE READ (cegah non-repeatable read lewat S lock sampai akhir), SERIALIZABLE (cegah phantom lewat range lock), SNAPSHOT (row versioning, pembaca tidak memblok penulis, bisa konflik 3960). Level lebih tinggi = lebih banyak blocking dan deadlock. Saya pakai READ COMMITTED, idealnya RCSI. Deadlock = siklus saling menunggu; engine memilih victim dan mengirim 1205. Cegah dengan urutan akses sama dan transaction pendek; tangani dengan retry 1205.

Sumber: [1] Transaction locking and row versioning guide · [2] SET XACT_ABORT · [3] XACT_STATE · [4] BEGIN TRANSACTION · [5] EF Core: Transactions · [6] SET TRANSACTION ISOLATION LEVEL · [7] Deadlocks guide · [8] sys.dm_exec_requests · [9] sys.dm_tran_locks · [10] EF Core: Connection resiliency · [11] EF Core: Concurrency conflicts · [12] SET LOCK_TIMEOUT · [13] MSSQLSERVER_1222