Understanding BEGIN CONCURRENT: Turso's MVCC Mechanism for Non-Locking Writes

BEGIN CONCURRENT is a Turso-specific SQL extension that enables optimistic write transactions through snapshot isolation, allowing multiple connections to write simultaneously without acquiring exclusive locks until commit-time conflict detection.

Turso extends SQLite with multi-version concurrency control (MVCC) to eliminate the traditional single-writer bottleneck. The BEGIN CONCURRENT statement activates this mode by creating a database snapshot at transaction start, letting many connections proceed independently while detecting conflicts only when COMMIT is executed.

How BEGIN CONCURRENT Differs from Standard SQLite Transactions

Standard SQLite transactions use BEGIN IMMEDIATE or BEGIN EXCLUSIVE to acquire a single write lock that blocks all other writers until completion. In contrast, Turso's BEGIN CONCURRENT creates a snapshot of the database state at the moment the statement is issued.

This provides snapshot isolation: each transaction sees a consistent view of data as of its start time, while write operations proceed optimistically without blocking. Success depends on whether overlapping modifications occur before commit.

The Three-Stage Implementation Pipeline

The implementation spreads across the SQL parser, bytecode emitter, and virtual machine executor in the Turso source tree.

Parsing the CONCURRENT Token

In sqlite/parser/src/parser.rs (around line 5308), the lexer recognizes BEGIN CONCURRENT TRANSACTION and maps it to a specific AST variant:

TransactionType::Concurrent

This distinguishes the concurrent transaction type from standard Deferred, Immediate, or Exclusive modes at the parsing stage.

Emitting the Transaction Bytecode

The emitter in core/translate/transaction.rs handles the translation via translate_tx_begin (line 61). It generates a Transaction bytecode instruction carrying the concurrent mode flag:

program.emit_insn(Insn::Transaction {
    db: crate::MAIN_DB_ID,
    tx_mode: TransactionMode::Concurrent,
    schema_cookie: schema.schema_version,
});

The TransactionMode::Concurrent variant is defined in core/translate/emitter/mod.rs, signaling to the virtual machine that this transaction requires MVCC snapshot semantics rather than traditional file locking.

Executing the MVCC Transaction

When the virtual machine reaches the Transaction opcode, core/vdbe/execute.rs invokes op_transaction (line 67) and its helper begin_mvcc_tx (line 88). For concurrent transactions, the execution path opens a read-only snapshot on the MVStore:

let write = matches!(tx_mode, TransactionMode::Write | TransactionMode::Concurrent);
…
if let Some(mv_store) = mv_store.as_ref() {
    match begin_mvcc_tx(mv_store, &pager, &tx_mode, None, &conn) {
        Ok(tx_id) => conn.set_mv_tx_for_db(*db, Some((tx_id, tx_mode))),
        Err(err) => return Err(err),
    }
}

The begin_mvcc_tx function delegates to mv_store.begin_tx(pager.clone()) for Concurrent mode, registering the transaction ID while capturing the current database state. The connection stores this snapshot ID to ensure all subsequent reads within the transaction use the same consistent view.

Commit-Time Conflict Detection

Since BEGIN CONCURRENT transactions acquire no exclusive locks during execution, conflict detection occurs entirely at commit time. When the VM executes COMMIT, it calls MvStore::commit_tx, which compares the transaction's write-set against the current MVCC versions of all touched pages.

If another concurrent transaction has modified any of the same rows, the store returns:

LimboError::TxError("write-write conflict")

The VM propagates this error to the client, triggering a rollback. The application layer must then catch the error and retry the transaction sequence.

Enabling MVCC Mode

BEGIN CONCURRENT requires the database to operate in MVCC journal mode. Attempting to execute the statement on a standard WAL or DELETE mode database yields a runtime error. Enable the prerequisite mode with:

PRAGMA journal_mode = mvcc;

This requirement is documented in docs/sql-reference/statements/transactions.mdx.

Practical Usage Examples

The following examples demonstrate the optimistic concurrency pattern across different Turso clients.

Python Client

import turso

db = turso.Database("file:example.db")
db.execute("PRAGMA journal_mode = mvcc")

def writer(id_, delta):
    conn = turso.Database("file:example.db")
    conn.execute("BEGIN CONCURRENT")
    conn.execute(f"UPDATE accounts SET balance = balance + {delta} WHERE id = {id_}")
    try:
        conn.execute("COMMIT")
        print(f"Writer {id_} committed")
    except turso.DatabaseError as e:
        print(f"Writer {id_} conflict: {e}")

writer(1, 100)

JavaScript Client

import { Database } from "turso";

const db = new Database("file:example.db");
await db.exec("PRAGMA journal_mode = mvcc");

await db.exec("BEGIN CONCURRENT");
await db.exec("UPDATE inventory SET qty = qty - 1 WHERE sku = 42");
try {
  await db.exec("COMMIT");
  console.log("Commit succeeded");
} catch (e) {
  console.log("Write-write conflict – retry required");
}

SQL Transaction Flow

-- Connection A
PRAGMA journal_mode = mvcc;
BEGIN CONCURRENT;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- succeeds

-- Connection B (started before A committed)
PRAGMA journal_mode = mvcc;
BEGIN CONCURRENT;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;  -- fails with write-write conflict
ROLLBACK;
-- Retry after A's commit
BEGIN CONCURRENT;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;  -- succeeds

Summary

• Optimistic Concurrency: BEGIN CONCURRENT uses snapshot isolation instead of exclusive locks, allowing multiple writers to proceed simultaneously.
• Three-Phase Implementation: The feature is implemented in sqlite/parser/src/parser.rs (parsing), core/translate/transaction.rs (bytecode emission), and core/vdbe/execute.rs (execution).
• Deferred Conflict Detection: Write-write conflicts are detected only at COMMIT via the MVStore's version comparison, returning LimboError::TxError on overlap.
• MVCC Requirement: The database must use PRAGMA journal_mode = mvcc before executing concurrent transactions.

Frequently Asked Questions

What is the difference between BEGIN CONCURRENT and BEGIN IMMEDIATE?

BEGIN IMMEDIATE acquires a reserved write lock immediately, blocking other writers until the transaction completes, while BEGIN CONCURRENT creates a snapshot and proceeds without locking, detecting conflicts only at commit time through MVCC version checking.

What happens when two concurrent transactions modify the same row?

When the second transaction attempts to commit, the MvStore::commit_tx function detects the overlapping write-set and returns a write-write conflict error (LimboError::TxError), forcing the transaction to roll back. The application must retry the transaction after the first writer commits.

Is BEGIN CONCURRENT compatible with standard SQLite databases?

No. BEGIN CONCURRENT is a Turso-specific extension that requires the MVCC storage engine. Standard SQLite does not recognize the CONCURRENT transaction type and lacks the multi-version storage layer necessary for snapshot isolation.

How do I handle write-write conflicts in my application?

Wrap the commit operation in a retry loop: catch the conflict error, roll back the transaction, acquire a fresh snapshot with a new BEGIN CONCURRENT, re-apply the changes, and attempt commit again. Since conflicts are relatively rare under optimistic concurrency, this pattern typically succeeds within one or two attempts.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}