SQL Server Transactions — Ensuring Data Consistency

A transaction guarantees ACID properties:

Atomicity:    All operations in the transaction succeed, or ALL are rolled back.
              "Approve prescription AND write audit log" — both or neither.

Consistency:  The database moves from one valid state to another.
              A prescription can't be in both 'Draft' and 'Approved' status.

Isolation:    Concurrent transactions don't see each other's partial changes.
              Two pharmacists approving the same prescription simultaneously
              don't both see it as 'Draft' and approve it twice.

Durability:   Once committed, changes survive crashes.
              An approved prescription stays approved even if the server reboots.

Without transactions in a clinical system:
  Pharmacist A reads prescription (Draft)
  Pharmacist B reads prescription (Draft)
  Pharmacist A approves → writes 'Approved'
  Pharmacist B approves → also writes 'Approved' (sees no conflict — both succeed)
  Result: prescription approved twice, two audit entries, double dispensing risk

With a transaction + optimistic concurrency:
  Pharmacist B's approve fails — row version changed since they read it
  Pharmacist B sees: "This prescription was recently updated — please refresh"

// EF Core SaveChanges() is always in an implicit transaction
// For multi-table operations, use an explicit transaction

public sealed class PrescriptionApprovalService
{
    private readonly ClinicalDbContext _context;

    // Single SaveChanges — implicit transaction (all changes committed together):
    public async Task ApproveAsync(
        PrescriptionId prescriptionId,
        ApproverId     approverId,
        DateTime       utcNow,
        CancellationToken ct)
    {
        var prescription = await _context.Prescriptions
            .FindAsync([prescriptionId.Value], ct)
            ?? throw new NotFoundException("Prescription not found.");

        prescription.Approve(approverId, utcNow);   // mutates entity

        var auditEntry = new PrescriptionAuditEntry(
            prescriptionId, "Approved", approverId, utcNow);

        _context.PrescriptionAuditEntries.Add(auditEntry);

        // Single SaveChanges — EF Core wraps both in one transaction:
        await _context.SaveChangesAsync(ct);
    }

    // Explicit transaction — for multi-step operations with non-EF operations:
    public async Task ApproveWithNotificationAsync(
        PrescriptionId prescriptionId,
        ApproverId     approverId,
        DateTime       utcNow,
        CancellationToken ct)
    {
        await using var transaction = await _context.Database.BeginTransactionAsync(ct);

        try
        {
            var prescription = await _context.Prescriptions
                .FindAsync([prescriptionId.Value], ct)
                ?? throw new NotFoundException("Prescription not found.");

            prescription.Approve(approverId, utcNow);
            await _context.SaveChangesAsync(ct);

            // Second operation in the same transaction:
            await _context.Database.ExecuteSqlRawAsync(
                "INSERT INTO PharmacyWorkQueue (PrescriptionId, QueuedAt, Priority) VALUES ({0}, {1}, {2})",
                prescriptionId.Value, utcNow, "Normal");

            await transaction.CommitAsync(ct);
        }
        catch
        {
            await transaction.RollbackAsync(ct);
            throw;
        }
    }
}

// SQL Server isolation levels — choose based on concurrency vs consistency trade-off

// READ COMMITTED (default):
//   Reads don't see uncommitted data from other transactions
//   Two reads in the same transaction can see different values (non-repeatable read)
//   Good for most OLTP workloads

// READ COMMITTED SNAPSHOT ISOLATION (RCSI — recommended for most clinical systems):
//   Uses row versions — readers don't block writers, writers don't block readers
//   No dirty reads, no blocking on reads
//   Enable at database level: ALTER DATABASE ClinicalDb SET READ_COMMITTED_SNAPSHOT ON;

// REPEATABLE READ:
//   Same read twice in one transaction sees the same data
//   Prevents non-repeatable reads — holds shared locks until transaction ends
//   Higher blocking risk

// SERIALIZABLE (highest isolation):
//   Prevents phantom reads — range locks prevent new rows appearing mid-transaction
//   Maximum locking — significant throughput impact
//   Use for: "create prescription only if patient has no active duplicates" logic

// Setting isolation level in EF Core:
await using var transaction = await _context.Database.BeginTransactionAsync(
    IsolationLevel.Snapshot,  // or ReadCommitted, RepeatableRead, Serializable
    ct);

// SNAPSHOT isolation — point-in-time read consistency, no blocking:
// ALTER DATABASE ClinicalDb SET ALLOW_SNAPSHOT_ISOLATION ON;
// Then use IsolationLevel.Snapshot for long-running reports

// Optimistic concurrency: detect conflicts at save time rather than locking at read time
// Uses a rowversion column (SQL Server timestamp)

// Schema:
// ALTER TABLE Prescriptions ADD RowVersion ROWVERSION NOT NULL;

// EF Core entity:
public sealed class Prescription
{
    public Guid Id { get; private set; }
    // ... other properties

    // EF Core detects this as a concurrency token:
    [Timestamp]
    public byte[] RowVersion { get; private set; } = default!;
}

// EF Core configuration:
builder.Property(p => p.RowVersion)
       .IsRowVersion()
       .IsConcurrencyToken();

// When two pharmacists try to approve the same prescription:
// Pharmacist A reads: RowVersion = 0x0000001
// Pharmacist B reads: RowVersion = 0x0000001
// Pharmacist A saves: UPDATE WHERE RowVersion = 0x0000001 → 1 row affected → commit → RowVersion = 0x0000002
// Pharmacist B saves: UPDATE WHERE RowVersion = 0x0000001 → 0 rows affected → DbUpdateConcurrencyException

// Handle the concurrency conflict:
public async Task<ApprovalResult> ApproveAsync(
    PrescriptionId prescriptionId, ApproverId approverId, CancellationToken ct)
{
    try
    {
        var prescription = await _context.Prescriptions.FindAsync(
            [prescriptionId.Value], ct)
            ?? return ApprovalResult.NotFound();

        prescription.Approve(approverId, DateTime.UtcNow);
        await _context.SaveChangesAsync(ct);

        return ApprovalResult.Success();
    }
    catch (DbUpdateConcurrencyException)
    {
        // Another user modified this prescription — tell the caller to retry with fresh data
        return ApprovalResult.Conflict(
            "This prescription was updated by another user. Please reload and try again.");
    }
}

-- Deadlocks occur when two transactions each hold a lock the other needs
-- Clinical example:
--   Transaction A: locks Prescriptions, then tries to lock Patients
--   Transaction B: locks Patients, then tries to lock Prescriptions
--   Each waits for the other → deadlock — SQL Server kills one

-- Prevention strategy 1: consistent lock ordering
-- Always acquire locks in the same order across all queries:
-- ALWAYS lock Patients before Prescriptions — never the reverse

-- Prevention strategy 2: UPDLOCK hint — get an update lock on read
-- Prevents a second transaction from getting a shared lock while you plan to write

BEGIN TRANSACTION;

SELECT @Status = Status
FROM   dbo.Prescriptions WITH (UPDLOCK, ROWLOCK)  -- take update lock now, not at UPDATE time
WHERE  Id = @PrescriptionId;

IF @Status = 'Draft'
    UPDATE dbo.Prescriptions SET Status = 'Approved' WHERE Id = @PrescriptionId;

COMMIT TRANSACTION;

-- Prevention strategy 3: shorter transactions
-- Don't hold locks across user-facing delays (e.g., waiting for confirmation)
-- Read the data → close transaction → show user → open new transaction → write

-- Detecting deadlocks in production:
SELECT  xdr.value('@timestamp[1]', 'datetime2')    AS DeadlockTime,
        xdr.query('.')                              AS DeadlockGraph
FROM (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets   t
    JOIN sys.dm_xe_sessions          s ON s.address = t.event_session_address
    WHERE s.name = 'system_health'
      AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS xdr (xdr)
ORDER BY DeadlockTime DESC;

// Dapper: pass the transaction to each command to include it in the transaction

public async Task ApproveWithDapperAsync(
    Guid prescriptionId, Guid approverId, decimal inrValue, CancellationToken ct)
{
    await using var conn = new SqlConnection(_connectionString);
    await conn.OpenAsync(ct);

    await using var tx = await conn.BeginTransactionAsync(ct);

    try
    {
        // Both commands in the same transaction:
        await conn.ExecuteAsync(
            """
            UPDATE Prescriptions
            SET    Status      = 'Approved',
                   ApprovedBy  = @approverId,
                   ApprovedAt  = SYSUTCDATETIME(),
                   InrValue    = @inrValue
            WHERE  Id = @prescriptionId AND Status = 'Draft'
            """,
            new { prescriptionId, approverId, inrValue },
            transaction: tx);   // pass transaction

        await conn.ExecuteAsync(
            """
            INSERT INTO PrescriptionAuditLog (PrescriptionId, Action, PerformedBy, PerformedAt)
            VALUES (@prescriptionId, 'Approved', @approverId, SYSUTCDATETIME())
            """,
            new { prescriptionId, approverId },
            transaction: tx);   // same transaction

        await tx.CommitAsync(ct);
    }
    catch
    {
        await tx.RollbackAsync(ct);
        throw;
    }
}