Transactions and concurrency

Transactions are on by default and behave the way they do on a full database server. Work inside one either all lands or none of it does, and a process that dies mid-transaction leaves the database as it was.

using var transaction = connection.BeginTransaction();

try
{
    // ... several statements
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Through EF Core, SaveChanges wraps itself in a transaction, and context.Database.BeginTransaction() gives you a wider one when several calls have to succeed together.

Wrap large writes, even single statements

A statement running outside a transaction puts its writes on the media as it goes. One UPDATE across twenty thousand rows writes thousands of pages before it returns, and a process killed halfway through leaves some of them written.

Inside an explicit transaction nothing reaches the media until the commit, so the statement becomes all or nothing against a process that dies. It is also faster: one commit flushes once instead of once per statement. For a bulk write, both reasons point the same way.

MVCC

Multi-version concurrency control is on by default. Rather than overwriting a row, a write creates a new version of it tagged with the transaction that made it, and readers see the version that was current when their transaction started.

The consequence is that readers and writers stop waiting for each other. A report that scans a large table does not block the writes arriving underneath it, and those writes do not make the report see half of one state and half of another.

Old versions accumulate and are collected once no live transaction can still need them.

MVCC can be turned off with MVCC=false, which gives a single-writer database where a transaction holds a database-wide write lock. That is faster for a workload with exactly one writer and no need for snapshots, and it takes concurrent transactions away entirely: a second session's BEGIN fails while the first is open.

Isolation levels

Five, matching the names you already know.

Set it per transaction:

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

For the connection:

Data Source=app.witdb;Isolation Level=Snapshot

Or in SQL, before the transaction begins:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

Snapshot suits reporting and any long read that has to agree with itself. ReadCommitted suits ordinary work. The two strict levels cost more and are for cases where a wrong answer is expensive.

Every level above ReadCommitted is optimistic here, and that changes what your code has to do. A transaction reads from its snapshot and records what it read; a conflict is found at commit and raised as an exception. Nothing blocks, so a caller expecting to wait gets an error instead, and has to retry the whole transaction. Code written for a database that locks will have no retry in it.

Serializable permits write skew. Two transactions read overlapping rows, write disjoint ones, and both commit, breaking an invariant that held for each of them alone. The classic shape is two on-call doctors each checking that the other is still on duty before signing off, and it was measured: both see two on call, both commit, and afterwards there are none. Nothing detects it, because neither transaction touched what the other wrote.

What Serializable does prevent, also measured: a transaction that reads a range, another that inserts into it and commits, and then a write from the first, which is refused with a serialization failure. And two transactions writing the same row, where the second is refused.

So an invariant that spans rows is not enforced by choosing a higher level. Enforce it by writing a row both transactions touch, so the conflict becomes visible, by taking FOR UPDATE on what you read, or by serialising the operation outside the database.

Conflicts, and what your code does about them

Under MVCC a conflict surfaces at commit rather than at the moment you write. The engine checks whether anything you touched has moved since your snapshot, and if it has, the commit throws.

try
{
    transaction.Commit();
}
catch (WitDbConcurrencyException)
{
    transaction.Rollback();
    // read the current state and decide again
}

This is worth designing for rather than catching once and hoping. A conflict means your transaction made its decision on rows that have since changed, so the honest response is to read them again and work out what to do, rather than to retry the same write in a loop.

At RepeatableRead and Serializable, what you read is checked as well as what you wrote, so a transaction that only read can still fail to commit.

Savepoints

A savepoint marks a place inside a transaction that you can return to without losing the rest.

using var transaction = connection.BeginTransaction();

// ... work that must stand

transaction.Save("before_import");

try
{
    // ... work that might not
}
catch
{
    transaction.Rollback("before_import");
}

transaction.Commit();

In SQL:

SAVEPOINT before_import;
ROLLBACK TO SAVEPOINT before_import;
RELEASE SAVEPOINT before_import;

Statements nest to a depth of 32, which is generous for handwritten SQL and reachable by a generator that composes subqueries without a limit of its own.

Row-level locking

When a decision depends on a row not changing under you, take a lock on it.

SELECT * FROM Accounts WHERE Id = @id FOR UPDATE;

FOR UPDATE takes an exclusive lock, held until the transaction ends. FOR SHARE takes a shared one, which other readers can hold at the same time but no writer can.

Two modifiers decide what happens when the row is already locked:

SELECT * FROM Jobs WHERE Status = 'pending' FOR UPDATE NOWAIT;
SELECT * FROM Jobs WHERE Status = 'pending' FOR UPDATE SKIP LOCKED;

NOWAIT fails immediately rather than waiting. SKIP LOCKED passes over the locked rows and returns the rest, which is how a work queue hands different jobs to different workers without them queueing behind each other.

Deadlocks are detected rather than waited out. When two transactions each hold what the other wants, one is chosen and its work is rolled back with an exception, so a deadlock costs you a retry rather than a hung process.

How many things can write at once

One process owns the database file, and inside that process connections share a single engine. One writer proceeds at a time; readers do not queue behind it, which is what MVCC is for.

Parallel Mode allows several writers where the workload supports it, and Lock Timeout controls how long a writer waits before giving up. Both are in connection strings.

Where to go next

• Storage engines, why MVCC and the LSM store interact badly
• Write-ahead logging, what makes a commit durable
• WitSQL reference, the transaction statements in full
• Limitations, the edges of all of the above