Transaction, isolation level, locking, dan deadlock

Lanjutan lesson 0020: pola SET XACT_ABORT ON + TRY/CATCH + THROW di usp_UpdatePrice kini dijelaskan alasannya. Padanan Node: prisma.$transaction(...) atau BEGIN/COMMIT lewat pg. Konsep isolation level dan deadlock sama di semua database relasional; yang khas SQL Server adalah default-nya (READ COMMITTED berbasis lock) dan nama fiturnya.

Catatan versi: dokumentasi Microsoft terbaru banyak menyebut optimized locking. Fitur itu hanya ada di Azure SQL dan SQL Server 2025 ^[11]. Container sql2022 memakai perilaku "without optimized locking", dan itulah yang diajarkan di sini.

1. ACID dan dasar transaction

"A transaction is a sequence of operations performed as a single logical unit of work." Empat properti yang wajib dimilikinya: ^[1]

• Mesinnya: "Locking facilities that preserve transaction isolation" dan "Logging facilities to ensure transaction durability" (transaction log). ^[1]
• Autocommit adalah default: "Every Transact-SQL statement is committed or rolled back when it completes." Satu UPDATE yang mengubah 2.495 baris sudah atomik. Explicit transaction (BEGIN TRANSACTION … COMMIT/ROLLBACK) dibutuhkan saat beberapa statement harus berhasil atau gagal bersama. ^[1]
• Jebakan interview: "If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error." Jadi BEGIN TRAN; UPDATE (ok); UPDATE (gagal); COMMIT; tanpa pengaman menyimpan setengah transaction. Lab Langkah 3a memperlihatkannya. ^[1]

2. Transaction yang benar di T-SQL

SET XACT_ABORT ON;                         -- error run-time apa pun me-rollback SELURUH transaction
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 (masih aktif) atau -1 (uncommittable)
    THROW;                                     -- lempar ulang ke aplikasi (lesson 0020)
END CATCH;

• SET XACT_ABORT ON: "if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back." Default-nya OFF ("OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger"). Compile error (syntax) tidak terpengaruh. ^[3]
• XACT_STATE(): 1 = ada transaction aktif yang bisa di-commit; 0 = tidak ada transaction; -1 = transaction "uncommittable": "it can only request a full rollback of the transaction." Bedanya dengan @@TRANCOUNT: "@@TRANCOUNT can't be used to determine whether that transaction is classified as an uncommittable transaction." ^[4] IF @@TRANCOUNT > 0 ROLLBACK seperti di lesson 0020 juga benar untuk pola ini, karena keduanya hanya me-rollback.
• Nested transaction itu ilusi: "BEGIN TRANSACTION increments @@TRANCOUNT by 1." "A commit of an inner transaction decrements @@TRANCOUNT but has no other effects. A rollback of an inner transaction always rolls back the outer transaction". Yang menentukan hanya COMMIT terluar (lab Langkah 3c). ^[2]

Padanan Node: await prisma.$transaction(async tx => { ... }) otomatis rollback bila callback melempar error. Di T-SQL, perilaku "semua atau tidak sama sekali" itu harus Anda minta sendiri lewat XACT_ABORT dan TRY/CATCH.

3. Transaction di EF Core

• Default sudah aman untuk satu SaveChanges: "all changes in a single call to SaveChanges are applied in a transaction. If any of the changes fail, then the transaction is rolled back and none of the changes are applied to the database." Microsoft menambahkan: "You should only manually control transactions if your application requirements deem it necessary." ^[5]
• Manual, bila beberapa SaveChanges (atau SaveChanges + raw SQL) harus atomik: "You can use the DbContext.Database API to begin, commit, and rollback transactions." ^[5] Isolation level dipilih lewat overload BeginTransaction(IsolationLevel). ^[6]

// Pola dari dokumentasi EF Core. StockMovement hanya contoh tabel riwayat (tidak ada di mini project).
await using var tx = await context.Database.BeginTransactionAsync();   // opsional: (IsolationLevel.Serializable)
try
{
    from.Stock -= qty;
    await context.SaveChangesAsync();

    context.StockMovements.Add(new StockMovement { ProductId = from.Id, Delta = -qty });
    await context.SaveChangesAsync();

    await tx.CommitAsync();       // tanpa Commit, transaction di-rollback saat tx di-dispose
}
catch (Exception)
{
    // log; transaction otomatis di-rollback saat dispose
    throw;
}

• Bila EnableRetryOnFailure aktif, transaction manual tidak bisa langsung dipakai: "Manually controlling transactions in this way is incompatible with implicitly invoked retrying execution strategies." ^[5] Tanpa itu, EF Core melempar InvalidOperationException: "The configured execution strategy 'SqlServerRetryingExecutionStrategy' does not support user-initiated transactions." Solusinya: "manually invoke the execution strategy with a delegate representing everything that needs to be executed. If a transient failure occurs, the execution strategy will invoke the delegate again." Jadi bungkus seluruh unit kerja (begin, SaveChangesAsync, commit) dengan context.Database.CreateExecutionStrategy().ExecuteAsync(...). ^[12]
• Lost update tanpa lock panjang: EF Core mendukung optimistic concurrency: kolom [Timestamp] byte[] Version (rowversion) ikut di WHERE saat UPDATE; kalau baris sudah diubah orang lain, SaveChanges melempar DbUpdateConcurrencyException. Kata dokumentasinya: "optimistic concurrency takes no locks, but arranges for the data modification to fail on save if the data has changed since it was queried." SQL yang dikirim: UPDATE [People] SET [FirstName] = @p0 WHERE [PersonId] = @p1 AND [Version] = @p2;, dan bila ada update bersamaan "the UPDATE fails to find any matching rows and reports that zero were affected", sehingga SaveChanges() "throws a DbUpdateConcurrencyException". [Timestamp] memetakan properti ke kolom rowversion SQL Server yang "automatically changes when the row is updated". ^[13] Microsoft menyebut dua pendekatan: pessimistic ("A system of locks prevents transactions from modifying data in a way that affects other transactions") dan optimistic ("transactions don't lock data when they read it"). ^[1]

4. Isolation level dan anomalinya

Tiga anomali yang menjadi dasar semua pertanyaan isolation level ^[1]:

• Dirty read: "a second transaction reads a row that's being updated by another transaction. The second transaction is reading data that hasn't been committed yet". Kalau penulisnya ROLLBACK, data yang dibaca tidak pernah ada.
• Non-repeatable read: "a second transaction accesses the same row several times and reads different data each time."
• Phantom: "two identical queries are executed and the set of rows returned by the second query is different." Penyebabnya INSERT/DELETE baris yang cocok dengan WHERE.
• Bonus: lost update, dua transaction membaca baris yang sama lalu meng-UPDATE berdasarkan nilai lama: "The last update overwrites updates made by the other transactions". Obatnya UPDATE bersyarat dalam satu statement (seperti usp_AdjustStock di 0020), UPDLOCK, atau optimistic concurrency.

Tabel diringkas dari [1] dan [7]. Dipasang per sesi dengan SET TRANSACTION ISOLATION LEVEL ...; di dalam SP, level kembali ke semula saat SP selesai.

• READ COMMITTED: "statements can't read data that was modified but not committed by other transactions. ... This option is the SQL Server default." ^[7]
• REPEATABLE READ "keeps read and write locks that are acquired on selected data until the end of the transaction. However, because range-locks aren't managed, phantom reads can occur." SERIALIZABLE: "Range-locks are acquired when a SELECT operation uses a range WHERE clause to avoid phantom reads", "the most restrictive of the isolation levels." ^[1]
• Isolation level tidak mengubah lock untuk menulis: "A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level". Isolation level hanya mengatur perilaku membaca. ^[1]
• Trade-off: level rendah = lebih banyak akses bersamaan tapi lebih banyak anomali; level tinggi "requires more system resources and increases the chances that one transaction blocks another." ^[1]

Row versioning: RCSI vs SNAPSHOT (ringkas)

• Dengan row versioning, "read operations don't acquire shared (S) locks on the data being read, and therefore don't block transactions that are modifying data." Biayanya version store (tempdb). ^[1]
• Default berbeda per platform: di SQL Server (termasuk 2022) READ_COMMITTED_SNAPSHOT OFF ("the default setting in SQL Server") dan ALLOW_SNAPSHOT_ISOLATION OFF; di Azure SQL Database RCSI ON. Jadi di sql2022, pembaca menunggu penulis. ^[1]
• Rekomendasi Microsoft: "Microsoft recommends the row versioning-based READ COMMITTED isolation level for all applications, unless an application relies upon the blocking behavior of the lock-based READ COMMITTED isolation level." ^[8]

5. Lock dan blocking

"Locking is a mechanism used by the Database Engine to synchronize access by multiple users to the same piece of data at the same time." "All locks held by a transaction are released when the transaction completes (either commits or rolls back)." ^[1]

• Granularitas: KEY/RID (baris), PAGE, OBJECT (tabel). "Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead". Lock escalation ke tabel terjadi bila "A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index." ^[1]
• Blocking itu normal: "When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released. By default, transactions in the Database Engine don't time out, unless LOCK_TIMEOUT is set." ^[8] SET LOCK_TIMEOUT menentukan "the number of milliseconds a statement waits for a lock to be released"; "A value of -1 (default) indicates no time-out period (that is, wait forever)." ^[14] Jadi SET LOCK_TIMEOUT 3000 membuat statement menyerah setelah 3 detik dengan error 1222 "Lock request time out period exceeded." ^[15] Di aplikasi .NET, yang biasanya terlihat adalah command timeout dari sisi client.
• Melihat blocking: sys.dm_exec_requests kolom blocking_session_id ("If this column is NULL or 0, the request isn't blocked"), wait_type, wait_time; sys.dm_tran_locks menampilkan setiap lock beserta request_mode dan request_status (GRANTED/WAIT). Di SQL Server 2022 keduanya butuh izin VIEW SERVER PERFORMANCE STATE (sa di lab sudah punya). ^{[10] [9]}
• Pedoman transaction: "Don't require input from users during a transaction.", "Keep the transaction as short as possible.", "Access the least amount of data possible while in a transaction.", "Few transactions require the SERIALIZABLE transaction isolation level." ^[1] Di .NET artinya: jangan memanggil HTTP API eksternal atau menunggu user di antara BeginTransaction dan Commit.

6. Deadlock

• Definisi: "A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource that the other tasks are trying to lock." Beda dengan blocking: blocking selesai sendiri saat lock dilepas; deadlock adalah siklus yang tidak akan pernah selesai sendiri. ^[8]
• Deteksi dan victim: deadlock monitor memeriksa secara berkala ("The default interval is 5 seconds"). Engine "rolls back the transaction of the deadlock victim, and returns error 1205 to the application." Victim default adalah transaction "least expensive to roll back"; bisa diatur dengan SET DEADLOCK_PRIORITY. ^[8]
• Pencegahan (judul-judul dari bagian "Minimize deadlocks"): "Access objects in the same order.", "Avoid user interaction in transactions.", "Keep transactions short and in one batch.", "Avoid higher isolation levels such as REPEATABLE READ and SERIALIZABLE when not required.", "Use a row versioning-based isolation level." Tetapi "deadlocks can't be completely avoided", jadi aplikasi tetap harus siap. ^[8]
• Pola baca-lalu-ubah: dua transaction sama-sama memegang S lock lalu sama-sama minta X adalah deadlock klasik. Solusi resmi: "select a row with UPDLOCK hint, then update the row". ^[1] Lebih sederhana lagi: satu UPDATE bersyarat (WHERE Id = @Id AND Stock + @Delta >= 0) seperti usp_AdjustStock.
• Investigasi: "The system_health event session captures xml_deadlock_report events by default", jadi deadlock graph sudah tersedia tanpa setup (lab Langkah 30). ^[8]

-- T-SQL: kunci baris untuk diubah sejak dibaca (U lock ditahan sampai COMMIT)
BEGIN TRANSACTION;
SELECT @stock = Stock FROM dbo.Product WITH (UPDLOCK) WHERE Id = @Id;
IF @stock >= @Qty
    UPDATE dbo.Product SET Stock = Stock - @Qty WHERE Id = @Id;
COMMIT TRANSACTION;

// C#: tangani 1205 dengan mengulang SELURUH unit kerja setelah jeda acak.
for (var attempt = 1; ; attempt++)
{
    try
    {
        await using var tx = await context.Database.BeginTransactionAsync();
        // ... baca dan ubah Product ...
        await context.SaveChangesAsync();
        await tx.CommitAsync();
        break;
    }
    catch (Exception ex) when (ex.GetBaseException() is SqlException { Number: 1205 } && attempt < 3)
    {
        context.ChangeTracker.Clear();                       // buang state yang sudah di-rollback
        await Task.Delay(Random.Shared.Next(50, 250));       // jeda acak
    }
}

Dasar pola retry: "applications should have an error handler that can handle error 1205", dan "The application should pause briefly before resubmitting its query ... Randomizing the duration of the pause minimizes the likelihood of the deadlock reoccurring". ^[8] Error 1205 juga bisa ditangkap di blok CATCH T-SQL.

7. Hands-on (±15 menit): dua jendela, dua sesi

1. Pastikan container jalan (docker start sql2022) dan setup lab sudah dijalankan (6 Category, 100000 Product). Kalau ragu, jalankan ulang dotnet/lab/00-setup-product-catalog-lab.sql.
2. Buka dua jendela query di Azure Data Studio atau VS Code mssql, masing-masing koneksi sendiri ke localhost,1433, user sa. Jendela kiri: dotnet/lab/0021-sesi-a.sql. Jendela kanan: dotnet/lab/0021-sesi-b.sql.
3. Jalankan satu langkah saja setiap kali (pilih blok di antara dua penanda -- Langkah N, lalu Run selection), ikuti urutan di tabel. Setiap langkah diakhiri penanda >>> lanjut yang menyebut jendela berikutnya. Jangan menjalankan seluruh file: langkah yang menunggu akan menggantung.
4. Sebelum menjalankan, tebak dulu: langkah mana yang akan menunggu (ikon query berputar), dan angka berapa yang muncul?

Tanpa GUI: buka dua terminal, masing-masing docker exec -it sql2022 /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'Dev_Passw0rd!' -C, tempel satu langkah, lalu ketik GO. Kalau sebuah langkah tidak menunggu padahal seharusnya, cek Langkah 1: RCSI mungkin ON. Kalau lab berantakan, jalankan IF @@TRANCOUNT > 0 ROLLBACK; di kedua jendela, lalu setup ulang.

Jendela 1: dotnet/lab/0021-sesi-a.sql

-- Lesson 0021 · Transaction, isolation level, locking, deadlock · SESI A
-- Pasangan file ini: dotnet/lab/0021-sesi-b.sql. Buka KEDUA file di dua jendela query (dua koneksi
-- terpisah ke localhost,1433) setelah dotnet/lab/00-setup-product-catalog-lab.sql dijalankan.
--
-- JANGAN jalankan seluruh file sekaligus. Jalankan satu langkah saja (blok selection
-- antara dua penanda "Langkah"), lalu ikuti penanda ">>> lanjut" ke jendela berikutnya.
-- Langkah bernomor urut di kedua file: Langkah 1 (A), Langkah 2 (B), Langkah 3 (A), dst.
-- Langkah yang "menunggu" (ikon query berputar) memang disengaja: itulah blocking.
--
-- Data yang dipakai (dari setup): Product Id 1 = Price 80.19, Stock 31; Product Id 2 = Price 159.38,
-- Stock 62. Semua langkah mengembalikan data ke nilai ini.

-- ============ Bagian 0 · Persiapan ============================================================

-- Langkah 1 (Sesi A) -----------------------------------------------------------------------
USE ProductCatalogLab;
SELECT @@SPID AS SesiA;                        -- catat angkanya: session id jendela ini

-- Default SQL Server 2022: READ COMMITTED berbasis lock (RCSI OFF), SNAPSHOT tidak diizinkan.
-- Harus 0 dan OFF. Kalau is_read_committed_snapshot_on = 1, demo blocking di Bagian 2 tidak terjadi.
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = N'ProductCatalogLab';
-- >>> lanjut: Sesi B, Langkah 2

-- ============ Bagian 1 · Atomicity, XACT_ABORT, TRY/CATCH (Sesi A saja) ======================

-- Langkah 3 (Sesi A) -----------------------------------------------------------------------
-- 3a) JEBAKAN: XACT_ABORT OFF (default) tanpa TRY/CATCH. Error 547 hanya membatalkan
--     STATEMENT yang gagal; batch lanjut, COMMIT tetap jalan, UPDATE pertama tersimpan.
SET XACT_ABORT OFF;
BEGIN TRANSACTION;
    UPDATE dbo.Product SET Stock = Stock + 5 WHERE Id = 1;   -- berhasil
    UPDATE dbo.Product SET Stock = -1 WHERE Id = 2;          -- gagal: CK_Product_Stock (547)
COMMIT TRANSACTION;
SELECT Id, Stock FROM dbo.Product WHERE Id IN (1, 2);        -- Id 1 = 36: setengah transaction tersimpan
UPDATE dbo.Product SET Stock = 31 WHERE Id = 1;              -- kembalikan

-- 3b) POLA BENAR: XACT_ABORT ON + TRY/CATCH. Error apa pun membatalkan SELURUH transaction.
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
        UPDATE dbo.Product SET Stock = Stock + 5 WHERE Id = 1;
        UPDATE dbo.Product SET Stock = -1 WHERE Id = 2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- XACT_STATE: 1 = masih bisa di-commit, -1 = uncommittable (hanya boleh ROLLBACK), 0 = tidak ada.
    SELECT XACT_STATE() AS XactState, ERROR_NUMBER() AS ErrorNumber;   -- -1 dan 547
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    -- Di Stored Procedure, akhiri CATCH dengan THROW; (lesson 0020). Di sini tidak, supaya demo lanjut.
END CATCH;
SELECT Id, Stock FROM dbo.Product WHERE Id IN (1, 2);        -- 31 dan 62: tidak ada yang berubah
SET XACT_ABORT OFF;

-- 3c) Nested transaction bukan transaction sungguhan: COMMIT dalam hanya mengurangi @@TRANCOUNT.
BEGIN TRANSACTION;
    BEGIN TRANSACTION;
        SELECT @@TRANCOUNT AS TranCount;                     -- 2
        UPDATE dbo.Product SET Stock = 999 WHERE Id = 1;
    COMMIT TRANSACTION;                                      -- tidak menyimpan apa pun
    SELECT @@TRANCOUNT AS TranCount;                         -- 1
ROLLBACK TRANSACTION;                                        -- membatalkan SEMUANYA, termasuk yang "di-commit"
SELECT @@TRANCOUNT AS TranCount, Stock FROM dbo.Product WHERE Id = 1;   -- 0 dan 31
-- >>> lanjut: Langkah 4 (masih Sesi A)

-- ============ Bagian 2 · Blocking dan cara melihatnya ========================================

-- Langkah 4 (Sesi A) -----------------------------------------------------------------------
-- Ambil exclusive (X) lock pada baris Id 1 dan JANGAN commit.
BEGIN TRANSACTION;
UPDATE dbo.Product SET Price = 99999.99 WHERE Id = 1;
SELECT @@TRANCOUNT AS TranCount;                             -- 1: transaction masih terbuka
-- >>> lanjut: Sesi B, Langkah 5

-- Langkah 6 (Sesi A) -----------------------------------------------------------------------
-- Sesi B sedang menunggu. Siapa yang mem-block siapa?
SELECT session_id, status, blocking_session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;        -- session Sesi B, diblok oleh Sesi A, wait_type LCK_M_S

-- Lock apa saja yang dipegang dan diminta di database ini?
SELECT request_session_id, resource_type, request_mode, request_status, resource_description
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID(N'ProductCatalogLab')
  AND resource_type <> 'DATABASE'
ORDER BY request_session_id, resource_type;
-- Sesi A: X pada KEY (GRANTED), IX pada PAGE dan OBJECT (intent lock).
-- Sesi B: S pada KEY yang sama (WAIT), IS pada PAGE dan OBJECT.

-- Langkah 7 (Sesi A) -----------------------------------------------------------------------
ROLLBACK TRANSACTION;          -- lock dilepas, SELECT di Sesi B langsung selesai: 80.19
-- >>> lanjut: Langkah 8 (masih Sesi A)

-- ============ Bagian 3 · Dirty read (READ UNCOMMITTED) =======================================

-- Langkah 8 (Sesi A) -----------------------------------------------------------------------
BEGIN TRANSACTION;
UPDATE dbo.Product SET Price = 99999.99 WHERE Id = 1;        -- belum di-commit
-- >>> lanjut: Sesi B, Langkah 9

-- Langkah 10 (Sesi A) ----------------------------------------------------------------------
ROLLBACK TRANSACTION;          -- 99999.99 yang dibaca Sesi B tidak pernah ada secara resmi
-- >>> lanjut: Sesi B, Langkah 11

-- ============ Bagian 4 · Non-repeatable read (READ COMMITTED vs REPEATABLE READ) =============

-- Langkah 13 (Sesi A) ----------------------------------------------------------------------
UPDATE dbo.Product SET Price = 90.00 WHERE Id = 1;           -- autocommit; tidak menunggu
-- >>> lanjut: Sesi B, Langkah 14

-- Langkah 16 (Sesi A) ----------------------------------------------------------------------
-- Sesi B (REPEATABLE READ) memegang S lock sampai transaction-nya selesai: UPDATE ini MENUNGGU.
UPDATE dbo.Product SET Price = 80.19 WHERE Id = 1;           -- selesai setelah Sesi B COMMIT (Langkah 17)
SELECT Id, Price FROM dbo.Product WHERE Id = 1;              -- 80.19
-- >>> lanjut: Sesi B, Langkah 17

-- ============ Bagian 5 · Phantom (REPEATABLE READ vs SERIALIZABLE) ===========================

-- Langkah 19 (Sesi A) ----------------------------------------------------------------------
-- Id baru (IDENTITY) > 100000, jadi masuk ke rentang "Id >= 99995" yang sedang dibaca Sesi B.
INSERT INTO dbo.Product (Name, Price, Stock, CategoryId, CreatedAt)
VALUES (N'Phantom 0021 RR', 10.00, 1, 6, SYSDATETIME());     -- langsung berhasil
-- >>> lanjut: Sesi B, Langkah 20

-- Langkah 22 (Sesi A) ----------------------------------------------------------------------
-- Sesi B (SERIALIZABLE) memegang key-range lock atas rentang itu: INSERT ini MENUNGGU.
INSERT INTO dbo.Product (Name, Price, Stock, CategoryId, CreatedAt)
VALUES (N'Phantom 0021 SER', 10.00, 1, 6, SYSDATETIME());    -- selesai setelah Sesi B COMMIT (Langkah 23)
-- >>> lanjut: Sesi B, Langkah 23

-- Langkah 24 (Sesi A) ----------------------------------------------------------------------
DELETE FROM dbo.Product WHERE Name LIKE N'Phantom 0021%';    -- bersihkan
SELECT COUNT(*) AS ProductCount FROM dbo.Product;            -- 100000
-- >>> lanjut: Langkah 25 (masih Sesi A)

-- ============ Bagian 6 · Deadlock, lalu pencegahannya ========================================

-- Langkah 25 (Sesi A) ----------------------------------------------------------------------
BEGIN TRANSACTION;
UPDATE dbo.Product SET Stock = Stock - 1 WHERE Id = 1;       -- A memegang X lock baris 1
-- >>> lanjut: Sesi B, Langkah 26

-- Langkah 27 (Sesi A) ----------------------------------------------------------------------
UPDATE dbo.Product SET Stock = Stock - 1 WHERE Id = 2;       -- MENUNGGU baris 2 milik Sesi B
-- >>> lanjut: Sesi B, Langkah 28 (jangan tunggu langkah ini selesai)

-- Langkah 29 (Sesi A DAN Sesi B) -----------------------------------------------------------
-- Satu sesi mendapat Msg 1205 (deadlock victim, transaction-nya sudah di-rollback engine);
-- UPDATE di sesi lain selesai. Jalankan blok ini di KEDUA jendela.
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
SELECT @@TRANCOUNT AS TranCount, Id, Stock FROM dbo.Product WHERE Id IN (1, 2);   -- 0; 31 dan 62

-- Langkah 30 (Sesi A, opsional) ------------------------------------------------------------
-- Deadlock graph dicatat otomatis oleh extended event session bawaan system_health.
-- Klik kolom XML untuk melihat proses, resource, dan siapa victim-nya.
SELECT x.ev.value('@timestamp', 'datetime2') AS EventTime, x.ev.query('.') AS DeadlockReport
FROM (
    SELECT CAST(st.target_data AS xml) AS TargetData
    FROM sys.dm_xe_session_targets AS st
    INNER JOIN sys.dm_xe_sessions AS s ON s.address = st.event_session_address
    WHERE s.name = N'system_health' AND st.target_name = N'ring_buffer'
) AS t
CROSS APPLY t.TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(ev)
ORDER BY EventTime DESC;

-- Langkah 31 (Sesi A) ----------------------------------------------------------------------
-- PENCEGAHAN: kedua sesi mengakses baris dalam URUTAN YANG SAMA (Id 1 dulu, lalu Id 2).
BEGIN TRANSACTION;
UPDATE dbo.Product SET Stock = Stock - 1 WHERE Id = 1;
-- >>> lanjut: Sesi B, Langkah 32

-- Langkah 33 (Sesi A) ----------------------------------------------------------------------
UPDATE dbo.Product SET Stock = Stock - 1 WHERE Id = 2;       -- tidak menunggu: baris 2 bebas
ROLLBACK TRANSACTION;          -- lock dilepas; Sesi B (yang hanya blocking, bukan deadlock) lanjut
-- >>> lanjut: Sesi B, Langkah 34

-- ============ Bagian 7 · SNAPSHOT isolation (ringkas, opsional) ==============================

-- Langkah 35 (Sesi A) ----------------------------------------------------------------------
-- Pastikan kedua sesi tidak punya transaction terbuka dulu.
ALTER DATABASE ProductCatalogLab SET ALLOW_SNAPSHOT_ISOLATION ON;
SELECT snapshot_isolation_state_desc FROM sys.databases WHERE name = N'ProductCatalogLab';   -- ON
-- >>> lanjut: Sesi B, Langkah 36

-- Langkah 37 (Sesi A) ----------------------------------------------------------------------
BEGIN TRANSACTION;
UPDATE dbo.Product SET Price = 70.00 WHERE Id = 1;           -- X lock, belum commit
-- >>> lanjut: Sesi B, Langkah 38

-- Langkah 39 (Sesi A) ----------------------------------------------------------------------
COMMIT TRANSACTION;            -- Price kini 70.00
-- >>> lanjut: Sesi B, Langkah 40

-- Langkah 42 (Sesi A) ----------------------------------------------------------------------
-- Kembalikan lab ke kondisi setup.
UPDATE dbo.Product SET Price = 80.19 WHERE Id = 1;
ALTER DATABASE ProductCatalogLab SET ALLOW_SNAPSHOT_ISOLATION OFF;
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = N'ProductCatalogLab';                           -- 0 dan OFF
SELECT Id, Price, Stock FROM dbo.Product WHERE Id IN (1, 2);  -- 80.19/31 dan 159.38/62
-- Selesai.

Jendela 2: dotnet/lab/0021-sesi-b.sql

-- Lesson 0021 · Transaction, isolation level, locking, deadlock · SESI B
-- Pasangan file ini: dotnet/lab/0021-sesi-a.sql. Mulai dari Langkah 1 di Sesi A.
-- JANGAN jalankan seluruh file sekaligus: jalankan satu langkah, lalu ikuti penanda ">>> lanjut".
-- SET TRANSACTION ISOLATION LEVEL berlaku untuk seluruh sesi sampai diganti, jadi setiap
-- bagian menyetel ulang isolation level-nya sendiri.

-- ============ Bagian 0 · Persiapan ============================================================

-- Langkah 2 (Sesi B) -----------------------------------------------------------------------
USE ProductCatalogLab;
SELECT @@SPID AS SesiB;                        -- session id jendela ini, berbeda dari Sesi A
-- >>> lanjut: Sesi A, Langkah 3

-- ============ Bagian 2 · Blocking ============================================================

-- Langkah 5 (Sesi B) -----------------------------------------------------------------------
-- READ COMMITTED berbasis lock butuh shared (S) lock untuk membaca; baris Id 1 dipegang X lock
-- oleh Sesi A, jadi query ini MENUNGGU. Tanpa LOCK_TIMEOUT, ia menunggu selamanya.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT Id, Name, Price FROM dbo.Product WHERE Id = 1;        -- berputar... lalu 80.19 setelah Langkah 7
-- >>> lanjut: Sesi A, Langkah 6 (biarkan query ini tetap menunggu)

-- ============ Bagian 3 · Dirty read ==========================================================

-- Langkah 9 (Sesi B) -----------------------------------------------------------------------
-- READ UNCOMMITTED tidak meminta S lock (sama dengan hint WITH (NOLOCK)): tidak menunggu,
-- tapi membaca data yang belum di-commit.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT Id, Price FROM dbo.Product WHERE Id = 1;              -- 99999.99, langsung
-- >>> lanjut: Sesi A, Langkah 10

-- Langkah 11 (Sesi B) ----------------------------------------------------------------------
SELECT Id, Price FROM dbo.Product WHERE Id = 1;              -- 80.19: yang tadi adalah dirty read
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- >>> lanjut: Langkah 12 (masih Sesi B)

-- ============ Bagian 4 · Non-repeatable read =================================================

-- Langkah 12 (Sesi B) ----------------------------------------------------------------------
-- READ COMMITTED: S lock dilepas begitu baris selesai dibaca, bukan di akhir transaction.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT Id, Price FROM dbo.Product WHERE Id = 1;              -- 80.19
-- >>> lanjut: Sesi A, Langkah 13

-- Langkah 14 (Sesi B) ----------------------------------------------------------------------
SELECT Id, Price FROM dbo.Product WHERE Id = 1;              -- 90.00: transaction yang SAMA, hasil beda
COMMIT TRANSACTION;
-- >>> lanjut: Langkah 15 (masih Sesi B)

-- Langkah 15 (Sesi B) ----------------------------------------------------------------------
-- REPEATABLE READ: S lock ditahan sampai akhir transaction.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT Id, Price FROM dbo.Product WHERE Id = 1;              -- 90.00
-- >>> lanjut: Sesi A, Langkah 16

-- Langkah 17 (Sesi B) ----------------------------------------------------------------------
SELECT Id, Price FROM dbo.Product WHERE Id = 1;              -- tetap 90.00: UPDATE Sesi A masih menunggu
COMMIT TRANSACTION;            -- S lock dilepas; UPDATE di Sesi A langsung selesai (80.19)
-- >>> lanjut: Langkah 18 (masih Sesi B)

-- ============ Bagian 5 · Phantom =============================================================

-- Langkah 18 (Sesi B) ----------------------------------------------------------------------
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT COUNT(*) AS Jumlah FROM dbo.Product WHERE Id >= 99995;   -- 6 (Id 99995..100000)
-- >>> lanjut: Sesi A, Langkah 19

-- Langkah 20 (Sesi B) ----------------------------------------------------------------------
SELECT COUNT(*) AS Jumlah FROM dbo.Product WHERE Id >= 99995;   -- 7: baris baru muncul = phantom
COMMIT TRANSACTION;
-- >>> lanjut: Langkah 21 (masih Sesi B)

-- Langkah 21 (Sesi B) ----------------------------------------------------------------------
-- SERIALIZABLE: range lock atas rentang WHERE, jadi tidak ada baris baru yang bisa masuk.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT COUNT(*) AS Jumlah FROM dbo.Product WHERE Id >= 99995;   -- 7
-- >>> lanjut: Sesi A, Langkah 22

-- Langkah 23 (Sesi B) ----------------------------------------------------------------------
SELECT COUNT(*) AS Jumlah FROM dbo.Product WHERE Id >= 99995;   -- tetap 7: INSERT Sesi A masih menunggu
COMMIT TRANSACTION;            -- range lock dilepas; INSERT di Sesi A langsung selesai
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- >>> lanjut: Sesi A, Langkah 24

-- ============ Bagian 6 · Deadlock ============================================================

-- Langkah 26 (Sesi B) ----------------------------------------------------------------------
BEGIN TRANSACTION;
UPDATE dbo.Product SET Stock = Stock - 1 WHERE Id = 2;       -- B memegang X lock baris 2
-- >>> lanjut: Sesi A, Langkah 27

-- Langkah 28 (Sesi B) ----------------------------------------------------------------------
-- B meminta baris 1 (milik A), A meminta baris 2 (milik B): siklus. Deadlock monitor
-- (default memeriksa tiap 5 detik) memilih satu victim: Msg 1205, transaction-nya di-rollback.
UPDATE dbo.Product SET Stock = Stock - 1 WHERE Id = 1;
-- >>> lanjut: Langkah 29 di KEDUA jendela

-- Langkah 29 (Sesi A DAN Sesi B) -----------------------------------------------------------
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
SELECT @@TRANCOUNT AS TranCount, Id, Stock FROM dbo.Product WHERE Id IN (1, 2);   -- 0; 31 dan 62
-- >>> lanjut: Sesi A, Langkah 30 atau 31

-- Langkah 32 (Sesi B) ----------------------------------------------------------------------
-- Urutan sama dengan Sesi A: Id 1 dulu. Ini hanya BLOCKING (menunggu A selesai), bukan deadlock.
BEGIN TRANSACTION;
UPDATE dbo.Product SET Stock = Stock - 1 WHERE Id = 1;       -- menunggu sampai Sesi A ROLLBACK
-- >>> lanjut: Sesi A, Langkah 33

-- Langkah 34 (Sesi B) ----------------------------------------------------------------------
UPDATE dbo.Product SET Stock = Stock - 1 WHERE Id = 2;
ROLLBACK TRANSACTION;
SELECT Id, Stock FROM dbo.Product WHERE Id IN (1, 2);        -- 31 dan 62
-- >>> lanjut: Sesi A, Langkah 35 (opsional) atau selesai

-- ============ Bagian 7 · SNAPSHOT isolation (ringkas, opsional) ==============================

-- Langkah 36 (Sesi B) ----------------------------------------------------------------------
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT Id, Price FROM dbo.Product WHERE Id = 1;              -- 80.19 (snapshot diambil di sini)
-- >>> lanjut: Sesi A, Langkah 37

-- Langkah 38 (Sesi B) ----------------------------------------------------------------------
-- Baris Id 1 sedang dipegang X lock oleh Sesi A, tapi SNAPSHOT membaca versi lama dari
-- version store (tempdb): tidak menunggu.
SELECT Id, Price FROM dbo.Product WHERE Id = 1;              -- 80.19, langsung
-- >>> lanjut: Sesi A, Langkah 39

-- Langkah 40 (Sesi B) ----------------------------------------------------------------------
SELECT Id, Price FROM dbo.Product WHERE Id = 1;              -- tetap 80.19 walau Sesi A sudah commit 70.00
-- Mengubah baris yang sudah diubah transaction lain setelah snapshot diambil:
-- Msg 3960 (update conflict), transaction ini dibatalkan.
UPDATE dbo.Product SET Price = Price + 1 WHERE Id = 1;
-- >>> lanjut: Langkah 41 (masih Sesi B)

-- Langkah 41 (Sesi B) ----------------------------------------------------------------------
SELECT @@TRANCOUNT AS TranCount;                             -- 0: sudah di-rollback engine
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- >>> lanjut: Sesi A, Langkah 42

8. Latihan tulis query

Tulis sendiri di editor SQL, jalankan di lab, baru buka jawaban. Tidak dinilai otomatis; bandingkan hasil dan bentuk kodenya.

CREATE OR ALTER PROCEDURE dbo.usp_TransferStock
    @FromId int,
    @ToId   int,
    @Qty    int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @FirstId  int = IIF(@FromId < @ToId, @FromId, @ToId),
            @SecondId int = IIF(@FromId < @ToId, @ToId, @FromId),
            @FirstStock int, @SecondStock int;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Kunci kedua baris dalam urutan Id naik, sama untuk SEMUA pemanggil: tidak ada siklus.
        -- UPDLOCK: lock ditahan sampai COMMIT, dan dua pembaca UPDLOCK tidak bisa sama-sama lolos.
        SELECT @FirstStock  = Stock FROM dbo.Product WITH (UPDLOCK) WHERE Id = @FirstId;
        SELECT @SecondStock = Stock FROM dbo.Product WITH (UPDLOCK) WHERE Id = @SecondId;

        IF @FirstStock IS NULL OR @SecondStock IS NULL
            THROW 50001, N'Product tidak ditemukan.', 1;
        IF IIF(@FromId = @FirstId, @FirstStock, @SecondStock) < @Qty
            THROW 50003, N'Stock asal tidak cukup.', 1;

        UPDATE dbo.Product SET Stock = Stock - @Qty WHERE Id = @FromId;
        UPDATE dbo.Product SET Stock = Stock + @Qty WHERE Id = @ToId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;
GO

EXEC dbo.usp_TransferStock @FromId = 1, @ToId = 2, @Qty = 5;
SELECT Id, Stock FROM dbo.Product WHERE Id IN (1, 2);           -- 26 dan 67

BEGIN TRY
    EXEC dbo.usp_TransferStock @FromId = 1, @ToId = 2, @Qty = 100000;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Message;   -- 50003
END CATCH;

EXEC dbo.usp_TransferStock @FromId = 2, @ToId = 1, @Qty = 5;   -- kembali 31 dan 62
SELECT Id, Stock FROM dbo.Product WHERE Id IN (1, 2);
GO

SELECT r.session_id,
       r.blocking_session_id,
       r.wait_type,
       r.wait_time / 1000.0 AS WaitSeconds,
       CASE r.transaction_isolation_level
           WHEN 1 THEN 'READ UNCOMMITTED'
           WHEN 2 THEN 'READ COMMITTED'
           WHEN 3 THEN 'REPEATABLE READ'
           WHEN 4 THEN 'SERIALIZABLE'
           WHEN 5 THEN 'SNAPSHOT'
           ELSE 'UNSPECIFIED'
       END AS IsolationLevel,
       t.text AS WaitingSql
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Ringkasan untuk dicetak: Reference: Transaction, isolation level, locking, deadlock · Istilah: GLOSSARY.md (bagian "Database (SQL Server)")

Ada yang belum jelas? Tanyakan ke saya (agent) di terminal, misalnya: "kenapa UPDATE yang memakai index bisa deadlock dengan SELECT?", "kapan RCSI justru berbahaya untuk logika bisnis?", atau "bagaimana optimistic concurrency EF Core menulis SQL-nya?"

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