Transactions & Concurrency

WitDatabase provides enterprise-grade transaction support with full ACID compliance, multiple isolation levels, and advanced concurrency control through MVCC and row-level locking. This guide covers everything you need to know about transactions, from basic usage to advanced scenarios.

1. Overview

What is a Transaction?

A transaction is a unit of work that groups multiple database operations into a single, atomic operation. Either all operations in the transaction succeed and are permanently saved (commit), or none of them are (rollback).

Consider transferring money between bank accounts:

[[Svg Src="./witdatabase-transaction-comparison.svg" Alt="witdatabase-transaction-comparison"]]

Transactions solve the fundamental problem of partial failures in multi-step operations.

Why Transactions Matter

Transactions provide four critical guarantees, known as ACID properties:

Transaction Lifecycle

Every transaction follows this lifecycle:

[[Svg Src="./witdatabase-transaction-lifecycle.svg" Alt="witdatabase-transaction-lifecycle"]]

2. ACID Properties

ACID is the foundation of reliable database systems. Understanding these properties helps you design robust applications.

Atomicity

"All or nothing" — Either every operation in a transaction succeeds, or none do. There is no partial completion.

Atomicity is crucial when multiple operations must succeed together:

using var transaction = connection.BeginTransaction();

try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    // Operation 1: Deduct from source account
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @from";
    cmd.Parameters.AddWithValue("@amount", 100.00m);
    cmd.Parameters.AddWithValue("@from", sourceAccountId);
    int rowsAffected = cmd.ExecuteNonQuery();
    
    if (rowsAffected == 0)
        throw new InvalidOperationException("Source account not found");
    
    // Operation 2: Add to destination account
    cmd.Parameters.Clear();
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @to";
    cmd.Parameters.AddWithValue("@amount", 100.00m);
    cmd.Parameters.AddWithValue("@to", destAccountId);
    rowsAffected = cmd.ExecuteNonQuery();
    
    if (rowsAffected == 0)
        throw new InvalidOperationException("Destination account not found");
    
    // Both succeeded — make permanent
    transaction.Commit();
}
catch
{
    // Something failed — undo everything
    transaction.Rollback();
    throw;
}

If the system crashes between Operation 1 and Operation 2, WitDatabase automatically rolls back the incomplete transaction when it restarts. No money is lost.

How WitDatabase implements atomicity:

• All changes are first written to the Write-Ahead Log (WAL)
• Changes are applied to the database only after WAL is safely on disk
• On crash recovery, incomplete transactions are rolled back using the WAL

Consistency

"Rules are always enforced" — The database moves from one valid state to another. Constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) are never violated.

Consistency ensures your data always makes sense:

// This transaction maintains consistency
using var transaction = connection.BeginTransaction();

try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    // Try to insert an order for a non-existent customer
    cmd.CommandText = @"
        INSERT INTO Orders (CustomerId, Total, OrderDate) 
        VALUES (@customerId, @total, @date)";
    cmd.Parameters.AddWithValue("@customerId", 99999); // Doesn't exist!
    cmd.Parameters.AddWithValue("@total", 150.00m);
    cmd.Parameters.AddWithValue("@date", DateTime.Now);
    
    cmd.ExecuteNonQuery(); // Throws: Foreign key constraint violation
    
    transaction.Commit();
}
catch (WitDbConstraintException ex)
{
    transaction.Rollback();
    // Database remains consistent — no orphan orders
    Console.WriteLine(

        
        

            
            Loading...
        
quot;Cannot create order: {ex.Message}");
}

Consistency guarantees in WitDatabase:

• PRIMARY KEY: Unique identifier for each row
• UNIQUE: No duplicate values in a column
• NOT NULL: Required values
• FOREIGN KEY: Referential integrity between tables
• CHECK: Custom validation rules

Isolation

"Transactions don't interfere" — Concurrent transactions execute as if they were running alone. One transaction doesn't see another's uncommitted changes (depending on isolation level).

Consider two users updating the same data simultaneously:

[[Svg Src="./witdatabase-transaction-isolation.svg" Alt="witdatabase-transaction-isolation"]]

WitDatabase provides five isolation levels to control this behavior. Higher isolation means more protection but potentially lower concurrency.

Durability

"Committed = Permanent" — Once a transaction commits successfully, the changes survive any subsequent failures, including power outages, crashes, or hardware failures.

[[Svg Src="./witdatabase-wal-durability-timeline.svg" Alt="witdatabase-wal-durability-timeline"]]

How WitDatabase ensures durability:

• Write-Ahead Logging (WAL): Changes logged before applied
• Synchronous commits: WAL flushed to disk before commit returns
• Crash recovery: Uncommitted changes rolled back, committed changes preserved

You can trade durability for performance in specific scenarios:

// Maximum durability (default)
"Data Source=app.witdb;Journal=wal"

// Faster but less durable (data loss possible on crash)
"Data Source=app.witdb;Sync Writes=false"

ACID in Practice

Here's a complete example demonstrating all four ACID properties:

public async Task<bool> TransferMoneyAsync(int fromAccount, int toAccount, decimal amount)
{
    await using var connection = new WitDbConnection(_connectionString);
    await connection.OpenAsync();
    
    // ATOMICITY: All operations in one transaction
    await using var transaction = await connection.BeginTransactionAsync();
    
    try
    {
        await using var cmd = connection.CreateCommand();
        cmd.Transaction = transaction;
        
        // CONSISTENCY: Check business rules
        cmd.CommandText = "SELECT Balance FROM Accounts WHERE Id = @id";
        cmd.Parameters.AddWithValue("@id", fromAccount);
        var balance = (decimal?)await cmd.ExecuteScalarAsync();
        
        if (balance == null)
            throw new InvalidOperationException("Source account not found");
        
        if (balance < amount)
            throw new InvalidOperationException("Insufficient funds");
        
        // ISOLATION: Other transactions don't see these changes until commit
        cmd.Parameters.Clear();
        cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id";
        cmd.Parameters.AddWithValue("@amount", amount);
        cmd.Parameters.AddWithValue("@id", fromAccount);
        await cmd.ExecuteNonQueryAsync();
        
        cmd.Parameters.Clear();
        cmd.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id";
        cmd.Parameters.AddWithValue("@amount", amount);
        cmd.Parameters.AddWithValue("@id", toAccount);
        await cmd.ExecuteNonQueryAsync();
        
        // DURABILITY: After commit returns, changes survive crashes
        await transaction.CommitAsync();
        return true;
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

3. Transaction Basics

This section covers the fundamental patterns for working with transactions in WitDatabase.

Starting a Transaction

Transactions are created from an open connection:

using var connection = new WitDbConnection("Data Source=app.witdb");
connection.Open();

// Begin transaction with default isolation level (ReadCommitted)
using var transaction = connection.BeginTransaction();

// All commands must be associated with the transaction
using var cmd = connection.CreateCommand();
cmd.Transaction = transaction;  // Required!

cmd.CommandText = "INSERT INTO Logs (Message) VALUES ('Transaction started')";
cmd.ExecuteNonQuery();

transaction.Commit();  // Or transaction.Rollback();

Always associate commands with the transaction via cmd.Transaction = transaction. Commands executed without this association won't be part of the transaction.

The Standard Pattern

Always use try-catch-finally to ensure proper cleanup:

using var connection = new WitDbConnection(connectionString);
connection.Open();

using var transaction = connection.BeginTransaction();

try
{
    // Perform operations
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    cmd.CommandText = "UPDATE Products SET Stock = Stock - 1 WHERE Id = @id";
    cmd.Parameters.AddWithValue("@id", productId);
    cmd.ExecuteNonQuery();
    
    cmd.Parameters.Clear();
    cmd.CommandText = "INSERT INTO OrderItems (OrderId, ProductId) VALUES (@orderId, @productId)";
    cmd.Parameters.AddWithValue("@orderId", orderId);
    cmd.Parameters.AddWithValue("@productId", productId);
    cmd.ExecuteNonQuery();
    
    // Success — commit
    transaction.Commit();
    Console.WriteLine("Order placed successfully");
}
catch (Exception ex)
{
    // Failure — rollback
    transaction.Rollback();
    Console.WriteLine(

        
        

            
            Loading...
        
quot;Order failed: {ex.Message}");
    throw;
}

If you forget to call Commit() or Rollback(), the transaction is automatically rolled back when disposed.

Async Transactions

For async code (ASP.NET Core, etc.), use the async variants:

await using var connection = new WitDbConnection(connectionString);
await connection.OpenAsync();

await using var transaction = await connection.BeginTransactionAsync();

try
{
    await using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    cmd.CommandText = "UPDATE Inventory SET Quantity = Quantity - @qty WHERE ProductId = @id";
    cmd.Parameters.AddWithValue("@qty", orderQuantity);
    cmd.Parameters.AddWithValue("@id", productId);
    await cmd.ExecuteNonQueryAsync();
    
    cmd.Parameters.Clear();
    cmd.CommandText = "INSERT INTO Orders (ProductId, Quantity) VALUES (@id, @qty)";
    cmd.Parameters.AddWithValue("@id", productId);
    cmd.Parameters.AddWithValue("@qty", orderQuantity);
    await cmd.ExecuteNonQueryAsync();
    
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Auto-Commit Mode

When you execute commands without an explicit transaction, each command runs in its own auto-committed transaction:

// Each statement is auto-committed
cmd.CommandText = "INSERT INTO Logs (Message) VALUES ('Log 1')";
cmd.ExecuteNonQuery();  // Committed immediately

cmd.CommandText = "INSERT INTO Logs (Message) VALUES ('Log 2')";
cmd.ExecuteNonQuery();  // Committed immediately

This is convenient for simple operations but doesn't provide atomicity across multiple statements.

SQL Transaction Statements

You can also control transactions via SQL:

// Using SQL statements
engine.Execute("BEGIN TRANSACTION");

engine.Execute("INSERT INTO Orders (CustomerId) VALUES (1)");
engine.Execute("INSERT INTO OrderItems (OrderId, ProductId) VALUES (1, 100)");

engine.Execute("COMMIT");
// Or: engine.Execute("ROLLBACK");

SQL transaction control is useful when:

• Working with the SQL Engine directly
• Running migration scripts
• Executing stored procedures

4. Savepoints

Savepoints create checkpoints within a transaction, allowing partial rollback without aborting the entire transaction.

Why Savepoints?

Sometimes you need to undo part of a transaction while keeping the rest:

[[Svg Src="./witdatabase-savepoints-comparison.svg" Alt="witdatabase-savepoints-comparison"]]

Savepoints are essential for complex business logic where some operations can fail independently.

Creating Savepoints

Use Save() to create a savepoint:

using var transaction = connection.BeginTransaction();
using var cmd = connection.CreateCommand();
cmd.Transaction = transaction;

try
{
    // Create the order (must succeed)
    cmd.CommandText = "INSERT INTO Orders (CustomerId, Total) VALUES (@cid, 0) RETURNING Id";
    cmd.Parameters.AddWithValue("@cid", customerId);
    var orderId = (long)cmd.ExecuteScalar()!;
    
    // Create savepoint before adding items
    transaction.Save("before_items");
    
    decimal total = 0;
    foreach (var item in orderItems)
    {
        try
        {
            cmd.Parameters.Clear();
            cmd.CommandText = @"
                INSERT INTO OrderItems (OrderId, ProductId, Quantity, Price)
                VALUES (@orderId, @productId, @qty, @price)";
            cmd.Parameters.AddWithValue("@orderId", orderId);
            cmd.Parameters.AddWithValue("@productId", item.ProductId);
            cmd.Parameters.AddWithValue("@qty", item.Quantity);
            cmd.Parameters.AddWithValue("@price", item.Price);
            cmd.ExecuteNonQuery();
            
            total += item.Quantity * item.Price;
        }
        catch (WitDbException ex)
        {
            // Individual item failed — log and continue
            Console.WriteLine(

        
        

            
            Loading...
        
quot;Could not add item {item.ProductId}: {ex.Message}");
        }
    }
    
    // Update order total
    cmd.Parameters.Clear();
    cmd.CommandText = "UPDATE Orders SET Total = @total WHERE Id = @id";
    cmd.Parameters.AddWithValue("@total", total);
    cmd.Parameters.AddWithValue("@id", orderId);
    cmd.ExecuteNonQuery();
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Rolling Back to a Savepoint

Use Rollback(savepointName) to undo changes back to a savepoint:

using var transaction = connection.BeginTransaction();
using var cmd = connection.CreateCommand();
cmd.Transaction = transaction;

// Phase 1: Create customer (must succeed)
cmd.CommandText = "INSERT INTO Customers (Name) VALUES (@name) RETURNING Id";
cmd.Parameters.AddWithValue("@name", "New Customer");
var customerId = (long)cmd.ExecuteScalar()!;

// Phase 2: Try to import their data (may fail)
transaction.Save("before_import");

try
{
    ImportCustomerData(cmd, customerId, externalData);
}
catch (Exception ex)
{
    Console.WriteLine(

        
        

            
            Loading...
        
quot;Import failed: {ex.Message}, using defaults");
    
    // Rollback import, keep customer
    transaction.Rollback("before_import");
    
    // Apply default settings instead
    ApplyDefaultSettings(cmd, customerId);
}

transaction.Commit();  // Customer is saved with either imported or default data

Nested Savepoints

You can create multiple savepoints for fine-grained control:

using var transaction = connection.BeginTransaction();

// Level 1
cmd.CommandText = "INSERT INTO A VALUES (1)";
cmd.ExecuteNonQuery();

transaction.Save("sp1");

    // Level 2
    cmd.CommandText = "INSERT INTO B VALUES (2)";
    cmd.ExecuteNonQuery();
    
    transaction.Save("sp2");
    
        // Level 3
        cmd.CommandText = "INSERT INTO C VALUES (3)";
        cmd.ExecuteNonQuery();
        
        // Rollback to sp2 (undoes C, keeps A and B)
        transaction.Rollback("sp2");
    
    // Rollback to sp1 (undoes B, keeps A)
    transaction.Rollback("sp1");

transaction.Commit();  // Only A is committed

Releasing Savepoints

Use Release() to remove a savepoint you no longer need:

transaction.Save("checkpoint");

// ... operations succeeded ...

// Release savepoint (can't rollback to it anymore)
transaction.Release("checkpoint");

// This would throw:
// transaction.Rollback("checkpoint"); // Error: savepoint doesn't exist

Releasing savepoints can free memory in long transactions with many savepoints.

SQL Savepoint Syntax

Via SQL statements:

BEGIN TRANSACTION;

INSERT INTO Orders (CustomerId) VALUES (1);

SAVEPOINT before_items;

INSERT INTO OrderItems (OrderId, ProductId) VALUES (1, 100);
INSERT INTO OrderItems (OrderId, ProductId) VALUES (1, 101);

-- Something went wrong with items
ROLLBACK TO SAVEPOINT before_items;

-- Try different approach
INSERT INTO OrderItems (OrderId, ProductId) VALUES (1, 200);

COMMIT;

Savepoint Best Practices

Do:

• Use meaningful savepoint names (before_items, pre_validation)
• Create savepoints before risky operations
• Handle rollback-to-savepoint as a normal control flow
• Release savepoints you no longer need in long transactions

Don't:

• Create too many savepoints (memory overhead)
• Use savepoints as a substitute for proper error handling
• Forget that rollback-to-savepoint doesn't end the transaction
• Assume savepoints work across connections (they don't)

5. Isolation Levels

Isolation levels control how concurrent transactions interact with each other. WitDatabase supports five isolation levels, each providing different trade-offs between consistency and performance.

Understanding Concurrency Anomalies

Before diving into isolation levels, let's understand the problems they solve:

Dirty Read — Reading uncommitted changes that may be rolled back.

[[Svg Src="./witdatabase-dirty-read.svg" Alt="witdatabase-dirty-read"]]

Non-Repeatable Read — Same query returns different values within one transaction.

[[Svg Src="./witdatabase-non-repeatable-read.svg" Alt="witdatabase-non-repeatable-read"]]

Phantom Read — Same query returns different rows within one transaction.

[[Svg Src="./witdatabase-phantom-read.svg" Alt="witdatabase-phantom-read"]]

Isolation Level Comparison

Setting Isolation Level

// ADO.NET: specify when beginning transaction
using var transaction = connection.BeginTransaction(IsolationLevel.Snapshot);

// SQL: set before beginning transaction
engine.Execute("SET TRANSACTION ISOLATION LEVEL SNAPSHOT");
engine.Execute("BEGIN TRANSACTION");

// Connection string: set default for all transactions
"Data Source=app.witdb;Isolation Level=Snapshot"

// Builder: set default isolation level
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithTransactions()
    .WithDefaultIsolationLevel(WitIsolationLevel.Snapshot)
    .Build();

READ UNCOMMITTED

The lowest isolation level. Transactions can see uncommitted changes from other transactions. This provides maximum concurrency but minimum consistency.

Behavior:

• Can read data that another transaction has modified but not yet committed
• If that transaction rolls back, you've read data that never existed
• No read locks acquired

When to use:

• Approximate counts or statistics where exact accuracy isn't critical
• Monitoring dashboards showing real-time approximations
• Situations where performance matters more than consistency

// Example: Dashboard showing approximate user count
using var tx = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
using var cmd = connection.CreateCommand();
cmd.Transaction = tx;

cmd.CommandText = "SELECT COUNT(*) FROM Users";
var approximateCount = (long)cmd.ExecuteScalar()!;
// Might be slightly off, but very fast

tx.Commit();

Warning: Almost never use this for business logic. It's only appropriate when you explicitly accept that results may be inconsistent.

READ COMMITTED

The default isolation level. Only committed data is visible. This prevents dirty reads but allows non-repeatable reads and phantoms.

Behavior:

• Only sees data that has been committed
• Same query may return different results if another transaction commits between reads
• Read locks released immediately after reading

When to use:

• Default choice for most applications
• Simple CRUD operations
• When you don't need to re-read data within the same transaction

// Default isolation level
using var tx = connection.BeginTransaction(); // ReadCommitted by default
using var cmd = connection.CreateCommand();
cmd.Transaction = tx;

// This read only sees committed data
cmd.CommandText = "SELECT Balance FROM Accounts WHERE Id = 1";
var balance = (decimal)cmd.ExecuteScalar()!;

// If another transaction commits a change here,
// a second read would see the new value

tx.Commit();

REPEATABLE READ

Prevents dirty reads and non-repeatable reads. Once you read a value, it won't change within your transaction (but new rows may appear).

Behavior:

• Read locks held until transaction ends
• Same query returns same value throughout transaction
• New rows inserted by other transactions may still appear (phantoms)

When to use:

• Reports that query the same data multiple times
• Calculations that depend on consistent values
• When you need to read, process, and re-read data

// Example: Calculating average order value
using var tx = connection.BeginTransaction(IsolationLevel.RepeatableRead);
using var cmd = connection.CreateCommand();
cmd.Transaction = tx;

// Read total
cmd.CommandText = "SELECT SUM(Amount) FROM Orders WHERE CustomerId = @id";
cmd.Parameters.AddWithValue("@id", customerId);
var total = (decimal)cmd.ExecuteScalar()!;

// Read count
cmd.Parameters.Clear();
cmd.CommandText = "SELECT COUNT(*) FROM Orders WHERE CustomerId = @id";
cmd.Parameters.AddWithValue("@id", customerId);
var count = (long)cmd.ExecuteScalar()!;

// This is consistent: total and count from same snapshot
var average = count > 0 ? total / count : 0;

tx.Commit();

SERIALIZABLE

The highest isolation level. Provides complete isolation — transactions execute as if they were running one at a time, serially.

Behavior:

• Range locks prevent phantom reads
• Transactions may block or fail due to conflicts
• Maximum consistency, minimum concurrency

When to use:

• Financial transactions where accuracy is critical
• Inventory management (checking and updating stock)
• Any scenario where concurrent modifications could cause data corruption

// Example: Checking and withdrawing from account
using var tx = connection.BeginTransaction(IsolationLevel.Serializable);
using var cmd = connection.CreateCommand();
cmd.Transaction = tx;

// Check balance
cmd.CommandText = "SELECT Balance FROM Accounts WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", accountId);
var balance = (decimal)cmd.ExecuteScalar()!;

if (balance >= amount)
{
    // Withdraw — no other transaction can change balance between SELECT and UPDATE
    cmd.Parameters.Clear();
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id";
    cmd.Parameters.AddWithValue("@amount", amount);
    cmd.Parameters.AddWithValue("@id", accountId);
    cmd.ExecuteNonQuery();
    
    tx.Commit();
    return true;
}
else
{
    tx.Rollback();
    return false; // Insufficient funds
}

SNAPSHOT

Uses Multi-Version Concurrency Control (MVCC) to provide a consistent snapshot of data as of the transaction start. This gives the consistency of Serializable with much better concurrency.

Behavior:

• Each transaction sees database as it was when the transaction started
• Readers never block writers, writers never block readers
• Write conflicts are detected at commit time
• Uses more storage for multiple versions

When to use:

• Read-heavy workloads with occasional writes
• Long-running reports alongside OLTP operations
• When you need consistency without blocking
• Recommended default for most applications using MVCC

// Example: Generating report while other transactions modify data
using var tx = connection.BeginTransaction(IsolationLevel.Snapshot);
using var cmd = connection.CreateCommand();
cmd.Transaction = tx;

// All queries in this transaction see a consistent snapshot
// from the moment the transaction started

cmd.CommandText = "SELECT SUM(Amount) FROM Orders WHERE Date >= @start";
cmd.Parameters.AddWithValue("@start", DateTime.Today.AddMonths(-1));
var monthlyTotal = (decimal?)cmd.ExecuteScalar() ?? 0;

cmd.Parameters.Clear();
cmd.CommandText = "SELECT AVG(Amount) FROM Orders WHERE Date >= @start";
cmd.Parameters.AddWithValue("@start", DateTime.Today.AddMonths(-1));
var monthlyAverage = (decimal?)cmd.ExecuteScalar() ?? 0;

// Even if other transactions are inserting orders right now,
// our totals and averages are consistent with each other

tx.Commit();

Choosing the Right Isolation Level

Isolation Level in SQL

-- Set isolation level before beginning transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

-- Your queries here...

COMMIT;

-- Available levels:
-- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

6. MVCC (Multi-Version Concurrency Control)

MVCC is a concurrency control mechanism that allows multiple transactions to access the same data simultaneously without blocking each other. It's the foundation for Snapshot isolation and high-concurrency workloads.

How MVCC Works

Instead of modifying data in place, MVCC creates a new version of each row when it's updated:

[[Svg Src="./witdatabase-locking-vs-mvcc.svg" Alt="witdatabase-locking-vs-mvcc"]]

Key concepts:

• Each write creates a new version, not an in-place update
• Each version is tagged with the transaction ID that created it
• Readers see the version that was current when their transaction started
• Old versions are cleaned up by garbage collection

Benefits of MVCC

Enabling MVCC

MVCC is enabled by default but can be explicitly configured:

// Via connection string
"Data Source=app.witdb;MVCC=true"

// Via builder
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithTransactions()
    .WithMvcc()  // Enable MVCC
    .WithDefaultIsolationLevel(WitIsolationLevel.Snapshot)
    .Build();

// Disable MVCC for simple single-writer scenarios
"Data Source=app.witdb;MVCC=false"

MVCC in Action

Let's trace through a concurrent scenario:

[[Svg Src="./witdatabase-mvcc-snapshot-timeline.svg" Alt="witdatabase-mvcc-snapshot-timeline"]]

Transaction A sees a consistent view of the database throughout its execution, even though Transaction B modified and committed changes during that time.

Write Conflicts

MVCC detects conflicts when two transactions try to modify the same row:

[[Svg Src="./witdatabase-mvcc-write-conflict.svg" Alt="witdatabase-mvcc-write-conflict"]]

WitDatabase handles this conflict:

using var txA = connection.BeginTransaction(IsolationLevel.Snapshot);

try
{
    // Try to update
    cmd.Transaction = txA;
    cmd.CommandText = "UPDATE Products SET Price = 250 WHERE Id = 1";
    cmd.ExecuteNonQuery();
    
    txA.Commit(); // May throw if conflict detected
}
catch (WitDbConcurrencyException ex)
{
    // Another transaction modified this row
    // Retry with fresh data
    txA.Rollback();
    
    // Retry logic...
}

Garbage Collection

Old versions accumulate as transactions create new ones. MVCC includes automatic garbage collection:

[[Svg Src="./witdatabase-mvcc-gc.svg" Alt="witdatabase-mvcc-gc"]]

Garbage collection runs automatically in the background. You don't need to manage it manually.

MVCC Trade-offs

When to Use MVCC

Use MVCC when:

• Read-heavy workloads (reports, analytics, dashboards)
• Long-running transactions that shouldn't block others
• Web applications with many concurrent users
• You need consistent snapshots for complex queries

Consider disabling MVCC when:

• Single-writer scenarios (batch processing, ETL)
• Write-heavy workloads with few reads
• Memory is constrained (versions use memory)
• You don't need snapshot isolation

MVCC Best Practices

Keep transactions short:

// GOOD: Short transaction
using var tx = connection.BeginTransaction(IsolationLevel.Snapshot);
var data = ReadData(cmd);
tx.Commit();

ProcessData(data); // Processing outside transaction

// BAD: Long transaction holds snapshot, prevents GC
using var tx = connection.BeginTransaction(IsolationLevel.Snapshot);
var data = ReadData(cmd);
ProcessData(data); // Long processing inside transaction
tx.Commit();

Handle write conflicts:

public async Task UpdateWithRetryAsync(int id, decimal newPrice, int maxRetries = 3)
{
    for (int attempt = 1; attempt <= maxRetries; attempt++)
    {
        await using var tx = await connection.BeginTransactionAsync(IsolationLevel.Snapshot);
        
        try
        {
            await using var cmd = connection.CreateCommand();
            cmd.Transaction = tx;
            
            cmd.CommandText = "UPDATE Products SET Price = @price WHERE Id = @id";
            cmd.Parameters.AddWithValue("@price", newPrice);
            cmd.Parameters.AddWithValue("@id", id);
            await cmd.ExecuteNonQueryAsync();
            
            await tx.CommitAsync();
            return; // Success
        }
        catch (WitDbConcurrencyException)
        {
            await tx.RollbackAsync();
            
            if (attempt == maxRetries)
                throw; // Give up after max retries
            
            // Wait before retry (exponential backoff)
            await Task.Delay(TimeSpan.FromMilliseconds(100 * attempt));
        }
    }
}

Use read-only transactions when appropriate:

// For reports that only read data, mark transaction as read-only
// This allows MVCC to optimize and not track write sets
using var tx = connection.BeginTransaction(IsolationLevel.Snapshot);

// Only perform reads
var report = GenerateReport(cmd);

tx.Commit(); // No writes means no conflict possible

7. Row-Level Locking

While MVCC handles most concurrency scenarios elegantly, sometimes you need explicit locks to coordinate access to specific rows. WitDatabase provides SQL-standard row-level locking with FOR UPDATE and FOR SHARE clauses.

Lock Types

WitDatabase supports two types of row locks:

Lock compatibility matrix:

FOR UPDATE (Exclusive Lock)

Use FOR UPDATE when you intend to modify the row. This prevents other transactions from reading (with FOR UPDATE/SHARE) or modifying the row until your transaction completes.

-- Lock the row for update
SELECT * FROM Accounts WHERE Id = 1 FOR UPDATE;

-- Now only this transaction can modify this row
UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;

COMMIT;  -- Lock released

Typical use case: Read-then-modify patterns where you need to ensure no one changes the data between your read and write.

using var tx = connection.BeginTransaction(IsolationLevel.ReadCommitted);
using var cmd = connection.CreateCommand();
cmd.Transaction = tx;

// Lock the row
cmd.CommandText = "SELECT Balance FROM Accounts WHERE Id = @id FOR UPDATE";
cmd.Parameters.AddWithValue("@id", accountId);
var balance = (decimal)cmd.ExecuteScalar()!;

// Check and update (no race condition possible)
if (balance >= amount)
{
    cmd.Parameters.Clear();
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id";
    cmd.Parameters.AddWithValue("@amount", amount);
    cmd.Parameters.AddWithValue("@id", accountId);
    cmd.ExecuteNonQuery();
    
    tx.Commit();
    return true;
}
else
{
    tx.Rollback();
    return false;
}

FOR SHARE (Shared Lock)

Use FOR SHARE when you want to ensure the row isn't modified by others, but you don't plan to modify it yourself. Multiple transactions can hold shared locks on the same row.

-- Lock for reading (prevent modifications)
SELECT * FROM Products WHERE Id = 1 FOR SHARE;

-- Other transactions can also SELECT ... FOR SHARE
-- But UPDATE/DELETE will block until we commit

Typical use case: Ensuring data consistency for calculations or validations without intending to modify.

using var tx = connection.BeginTransaction();
using var cmd = connection.CreateCommand();
cmd.Transaction = tx;

// Lock related rows to ensure consistency
cmd.CommandText = "SELECT * FROM OrderItems WHERE OrderId = @id FOR SHARE";
cmd.Parameters.AddWithValue("@id", orderId);

using var reader = cmd.ExecuteReader();
var items = new List<OrderItem>();
while (reader.Read())
{
    items.Add(MapToOrderItem(reader));
}
reader.Close();

// Calculate total (no one can modify items during calculation)
var total = items.Sum(i => i.Price * i.Quantity);

// Use the consistent total
cmd.Parameters.Clear();
cmd.CommandText = "UPDATE Orders SET Total = @total WHERE Id = @id";
cmd.Parameters.AddWithValue("@total", total);
cmd.Parameters.AddWithValue("@id", orderId);
cmd.ExecuteNonQuery();

tx.Commit();

NOWAIT

By default, if a lock cannot be acquired, the transaction waits until the lock becomes available. Use NOWAIT to fail immediately instead.

-- Try to lock, fail immediately if already locked
SELECT * FROM Accounts WHERE Id = 1 FOR UPDATE NOWAIT;

using var tx = connection.BeginTransaction();
using var cmd = connection.CreateCommand();
cmd.Transaction = tx;

try
{
    cmd.CommandText = "SELECT * FROM Accounts WHERE Id = @id FOR UPDATE NOWAIT";
    cmd.Parameters.AddWithValue("@id", accountId);
    
    using var reader = cmd.ExecuteReader();
    // Process...
}
catch (WitDbException ex) when (ex.Message.Contains("lock"))
{
    // Row is locked by another transaction
    Console.WriteLine("Account is being modified by another user, please try again");
    tx.Rollback();
    return false;
}

When to use NOWAIT:

• User-interactive applications where waiting is unacceptable
• Retry-based architectures where you want to try a different row
• Timeouts are critical

SKIP LOCKED

Use SKIP LOCKED to skip rows that are already locked by other transactions. This is useful for implementing work queues.

-- Get the next available (unlocked) row
SELECT * FROM Tasks 
WHERE Status = 'pending' 
ORDER BY CreatedAt 
LIMIT 1 
FOR UPDATE SKIP LOCKED;

Typical use case: Job queues where multiple workers compete for tasks.

public async Task<Job?> ClaimNextJobAsync()
{
    await using var connection = new WitDbConnection(_connectionString);
    await connection.OpenAsync();
    
    await using var tx = await connection.BeginTransactionAsync();
    await using var cmd = connection.CreateCommand();
    cmd.Transaction = tx;
    
    // Get next unlocked job
    cmd.CommandText = @"
        SELECT Id, Data FROM Jobs 
        WHERE Status = 'pending' 
        ORDER BY Priority DESC, CreatedAt 
        LIMIT 1 
        FOR UPDATE SKIP LOCKED";
    
    await using var reader = await cmd.ExecuteReaderAsync();
    
    if (!await reader.ReadAsync())
    {
        // No jobs available (all pending jobs are locked by other workers)
        await tx.RollbackAsync();
        return null;
    }
    
    var jobId = reader.GetInt64(0);
    var data = reader.GetString(1);
    await reader.CloseAsync();
    
    // Mark as claimed
    cmd.CommandText = "UPDATE Jobs SET Status = 'processing', WorkerId = @worker WHERE Id = @id";
    cmd.Parameters.AddWithValue("@worker", Environment.MachineName);
    cmd.Parameters.AddWithValue("@id", jobId);
    await cmd.ExecuteNonQueryAsync();
    
    await tx.CommitAsync();
    
    return new Job { Id = jobId, Data = data };
}

Lock Timeout

Configure how long to wait for a lock before giving up:

// Via connection string
"Data Source=app.witdb;Lock Timeout=60"  // 60 seconds

// Via builder
var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithLockTimeout(TimeSpan.FromSeconds(60))
    .Build();

When the timeout expires, a TimeoutException is thrown.

Deadlock Detection

When two transactions wait for each other's locks, a deadlock occurs:

[[Svg Src="./witdatabase-deadlock.svg" Alt="witdatabase-deadlock"]]

WitDatabase automatically detects deadlocks and aborts one transaction (the "victim"):

try
{
    cmd.CommandText = "SELECT * FROM Accounts WHERE Id = @id FOR UPDATE";
    cmd.Parameters.AddWithValue("@id", accountId);
    cmd.ExecuteReader();
    
    // ... more operations
    
    tx.Commit();
}
catch (WitDbDeadlockException)
{
    tx.Rollback();
    
    // Retry the entire transaction
    return await RetryTransactionAsync();
}

Deadlock prevention tips:

• Always lock rows in the same order (e.g., by primary key ascending)
• Keep transactions short
• Acquire all needed locks at the start of the transaction
• Use NOWAIT and handle failures instead of waiting

Row Locking Best Practices

Do:

• Use FOR UPDATE when you plan to modify the row
• Use FOR SHARE when you only need to prevent modifications
• Use NOWAIT in interactive applications
• Use SKIP LOCKED for work queues
• Handle deadlocks with retry logic
• Keep lock durations short

Don't:

• Hold locks during user think time
• Lock more rows than necessary
• Mix locking strategies arbitrarily
• Ignore deadlock possibilities
• Use row locks when MVCC is sufficient

8. Best Practices

This section summarizes best practices for working with transactions in WitDatabase.

Keep Transactions Short

Long-running transactions cause problems:

• Hold locks that block other transactions
• Prevent MVCC garbage collection
• Increase conflict probability
• Risk timeout and rollback

// BAD: Long transaction
using var tx = connection.BeginTransaction();
var data = LoadAllData(cmd);           // Fast
ProcessData(data);                      // SLOW - 30 seconds!
SaveResults(cmd, data);                 // Fast
tx.Commit();
// Transaction held for 30+ seconds

// GOOD: Minimal transaction scope
var data = LoadAllData(connection);     // Own transaction (auto-commit)
ProcessData(data);                      // No transaction
using var tx = connection.BeginTransaction();
SaveResults(cmd, data);                 // Short transaction
tx.Commit();
// Transaction held for milliseconds

Don't Hold Transactions During User Interaction

Never wait for user input while a transaction is open:

// BAD: Transaction during user interaction
using var tx = connection.BeginTransaction();
var order = LoadOrder(cmd, orderId);
ShowConfirmationDialog(order);          // User thinks for 5 minutes!
ConfirmOrder(cmd, orderId);
tx.Commit();

// GOOD: Transaction after user confirms
var order = LoadOrder(connection, orderId);  // No transaction
if (ShowConfirmationDialog(order))
{
    using var tx = connection.BeginTransaction();
    ConfirmOrder(cmd, orderId);
    tx.Commit();
}

Handle Failures Gracefully

Always use try-catch and ensure rollback:

using var tx = connection.BeginTransaction();

try
{
    // Operations that may fail
    ExecuteBusinessLogic(cmd, tx);
    tx.Commit();
}
catch (WitDbConcurrencyException)
{
    tx.Rollback();
    // Retry with fresh data or inform user
    throw new ConflictException("Data was modified by another user");
}
catch (WitDbDeadlockException)
{
    tx.Rollback();
    // Automatic retry is usually appropriate
    return await RetryAsync();
}
catch (Exception)
{
    tx.Rollback();
    throw;
}

Choose the Right Isolation Level

Use Savepoints for Complex Operations

Break complex transactions into recoverable sections:

using var tx = connection.BeginTransaction();

try
{
    CreateOrder(cmd, order);
    
    tx.Save("order_created");
    
    try
    {
        ProcessPayment(cmd, payment);
    }
    catch (PaymentException)
    {
        tx.Rollback("order_created");
        // Mark order as payment failed instead of rolling back entirely
        MarkPaymentFailed(cmd, order.Id);
    }
    
    tx.Commit();
}
catch
{
    tx.Rollback();
    throw;
}

Avoid Nested Transaction Attempts

WitDatabase doesn't support true nested transactions. Use savepoints instead:

// BAD: Nested transactions (throws exception)
using var tx1 = connection.BeginTransaction();
using var tx2 = connection.BeginTransaction(); // Error!

// GOOD: Use savepoints
using var tx = connection.BeginTransaction();
tx.Save("inner");
// ... operations ...
tx.Rollback("inner"); // Partial rollback
tx.Commit();

Lock in Consistent Order

To prevent deadlocks, always acquire locks in the same order:

// BAD: Inconsistent order
// Thread 1: Locks A, then B
// Thread 2: Locks B, then A
// → Deadlock possible!

// GOOD: Always lock in ID order
var ids = new[] { accountId1, accountId2 }.OrderBy(id => id).ToArray();

foreach (var id in ids)
{
    cmd.CommandText = "SELECT * FROM Accounts WHERE Id = @id FOR UPDATE";
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("@id", id);
    cmd.ExecuteReader();
}

Use Appropriate Timeout

Configure lock timeout based on your application:

// Interactive applications: short timeout, fail fast
"Data Source=app.witdb;Lock Timeout=5"

// Batch processing: longer timeout
"Data Source=app.witdb;Lock Timeout=300"

9. Quick Reference

Transaction Methods

ADO.NET (WitDbConnection):

SQL Statements:

Isolation Levels

Row Locking

Configuration

Connection String:

Data Source=app.witdb;
Transactions=true;
MVCC=true;
Isolation Level=Snapshot;
Lock Timeout=30;
Journal=wal

Builder Pattern:

var db = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithTransactions()
    .WithMvcc()
    .WithDefaultIsolationLevel(WitIsolationLevel.Snapshot)
    .WithLockTimeout(TimeSpan.FromSeconds(30))
    .WithWalJournal()
    .Build();

Exception Types