Lesson 01 of 05 · published

The Lock and the Missing Function

~14 min · sqlite, concurrency, single-writer, triggers, fencing

Level 0Loose Note

0 XP0/36 lessons0/12 achievements

0/100 XP to next level100 XP to go0% complete

Enforced by What?

Coop has exactly one writer. Say that to anyone who has run a database and they ask the right question at once: enforced by what? A writer that is the only one because everybody agreed not to write is a convention, and Track 1 already showed you what a multi-writer convention does to a ledger. So the engine enforces it twice, with two mechanisms that guard two different doors.

The first guard keeps out a second engine. When Engine.__init__ runs, it opens a sidecar file named <store>.engine.lock, sets it to mode 0600, and asks the kernel for an exclusive, non-blocking flock. If another process already holds it, the call fails at once instead of waiting, and the constructor raises Conflict('Another Coop writer owns this store'). Two engines on one store is not a race that gets resolved later. It is a start-up that refuses.

That lock has one weakness, and it is the one that matters: flock is advisory. It stops processes that ask for the lock. A plain sqlite3.connect() on the same file never asks. Every old helper, every forgotten script, every session still holding a connection from yesterday walks straight past it. The second guard exists for exactly those.

Authority That Lives in a Function

Every connection the engine opens registers an application-defined SQL function, coop_write_permit(), which returns 1 while the engine is running and 0 once it has closed. Then, for each of the nineteen tracked tables (the seven legacy ones, the reply-review receipts and eleven engine tables) and for each of INSERT, UPDATE and DELETE, the engine installs a BEFORE trigger whose whole body is one line: if coop_write_permit() is not 1, RAISE(ABORT,'Coop writer fenced').

Here is the trick. A trigger lives in the database file, so every connection that writes the table fires it. The function does not live in the file. A function registered with create_function exists only on the connection that registered it. So the trigger is shared and the permission is private: the authority lives in a function only the owner defines.

Watch what that does to an outsider. Some other process opens a raw connection and tries an INSERT. SQLite prepares the statement, pulls in the BEFORE trigger, meets a call to a function this connection has never heard of, and refuses with no such function: coop_write_permit. The write never begins. When the connection was opened does not matter either: SQLite notices the schema changed and re-prepares, so a connection that has been open since before the fences existed fails on its next write. Coop's own test keeps exactly such a connection open, starts the engine, and asserts that the insert raises an OperationalError naming coop_write_permit and that the table is still empty. Reads, meanwhile, go through untouched. The fence is on writing, not on looking.

Where "Coop writer fenced" Actually Fires

Because that message sits right there in the trigger, it is tempting to assume an outsider sees it. It does not. The literal only fires on a connection that has the function and gets 0 back: an engine connection still in use after Engine.close() flipped the closed flag. New connections are refused before that by the engine itself ('Coop writer is stopped'), so the message is the last guard for a writer shutting down mid-request.

Who writes	What happens	Why
A second engine	`Another Coop writer owns this store`	The exclusive `flock` is taken
Any outside connection	`no such function: coop_write_permit`	The trigger calls a function it lacks
The owner's own connection, after close	`Coop writer fenced`	The function exists and returns 0
The owner, running	The write lands	The function exists and returns 1

What the Fence Is Not

The design note says it plainly: this protects ordinary stale clients, not hostile same-UID code that edits schema. A process running as the same user can drop the triggers, or register its own coop_write_permit that returns 1. On the real store that one forgery is not quite enough: the change-tracking triggers (lesson 4) call a second owner-only function, coop_context, so a write with the permit forged and the context missing still fails with no such function: coop_context. Forge both and the write lands. Even a schema change goes through, because DDL fires no row triggers; Coop's own test suite adds a column to todos through a plain connection. So this is not a security boundary. It is a seatbelt for what actually happens in a family of agents: an honest process with an outdated idea of who owns the store.

Put the permission where the stale writer cannot carry it. A rule written into the shared file is read by everyone; a function on the owner's connection is held by one. Make the shared part ask the private part, and every honest outsider fails loudly before it writes a byte.

Code

Coop's engine: the lock, the connection, the fence (trimmed excerpt)·python

# Coop's engine: the lock, the connection, the fence (trimmed excerpt)

class Engine:
    def __init__(self, path: Path, *, fence=True):
        self.path = Path(path).resolve()
        self._closed = False
        self._lock = open(str(self.path) + '.engine.lock', 'a+')
        os.chmod(self._lock.name, 0o600)
        try:
            fcntl.flock(self._lock.fileno(), fcntl.LOCK_EX | fcntl.LOCK_NB)
        except BlockingIOError:
            self._lock.close()
            raise Conflict('Another Coop writer owns this store')
        ...

    @contextmanager
    def connection(self):
        if self._closed:
            raise RuntimeError('Coop writer is stopped')
        conn = sqlite3.connect(self.path, isolation_level=None, timeout=10,
                               detect_types=sqlite3.PARSE_DECLTYPES)
        ...
        conn.create_function('coop_write_permit', 0, lambda: int(not self._closed))
        conn.create_function('coop_context', 1, context.metadata)
        ...

    # In _triggers(), for every tracked table and each of INSERT/UPDATE/DELETE:
        if fence:
            conn.execute(f'''CREATE TRIGGER IF NOT EXISTS coop_fence_{table}_{op.lower()}
            BEFORE {op} ON {table} BEGIN
              SELECT CASE WHEN coop_write_permit() != 1 THEN RAISE(ABORT,'Coop writer fenced') END;
            END''')

A minimal sketch you can run: the whole fence in standard-library Python (macOS or Linux)·python

"""A minimal sketch of Coop's one-writer fence: an exclusive file lock, plus a
SQL function that only the lock owner's connections define."""
import fcntl
import os
import sqlite3
import tempfile
from pathlib import Path

FENCE = """CREATE TRIGGER IF NOT EXISTS coop_fence_todos_{op}
BEFORE {OP} ON todos BEGIN
  SELECT CASE WHEN coop_write_permit() != 1 THEN RAISE(ABORT,'Coop writer fenced') END;
END"""


class Owner:
    def __init__(self, path):
        self.path = path
        self.closed = False
        self.lock = open(f"{path}.engine.lock", "a+")
        os.chmod(self.lock.name, 0o600)
        try:
            fcntl.flock(self.lock.fileno(), fcntl.LOCK_EX | fcntl.LOCK_NB)
        except BlockingIOError:
            self.lock.close()
            raise RuntimeError("Another Coop writer owns this store")
        conn = self.connect()
        conn.execute("CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, title TEXT)")
        for op in ("insert", "update", "delete"):
            conn.execute(FENCE.format(op=op, OP=op.upper()))
        conn.close()

    def connect(self):
        conn = sqlite3.connect(self.path, isolation_level=None)
        # The authority: a function that exists only on the owner's connections.
        conn.create_function("coop_write_permit", 0, lambda: int(not self.closed))
        return conn

    def close(self):
        self.closed = True
        fcntl.flock(self.lock.fileno(), fcntl.LOCK_UN)
        self.lock.close()


def attempt(label, conn):
    try:
        conn.execute("INSERT INTO todos(title) VALUES (?)", (label,))
        print(f"{label:<22} wrote a row")
    except sqlite3.Error as exc:
        print(f"{label:<22} {type(exc).__name__}: {exc}")


db = Path(tempfile.mkdtemp()) / "ledger.db"
owner = Owner(db)
mine = owner.connect()
attempt("owner connection", mine)

outsider = sqlite3.connect(db, isolation_level=None)
attempt("outside connection", outsider)
print("outside read sees", outsider.execute("SELECT COUNT(*) FROM todos").fetchone()[0], "row(s)")

try:
    Owner(db)
except RuntimeError as exc:
    print(f"{'second owner':<22} {exc}")

owner.close()
attempt("owner after close", mine)

# Expected output:
# owner connection       wrote a row
# outside connection     OperationalError: no such function: coop_write_permit
# outside read sees 1 row(s)
# second owner           Another Coop writer owns this store
# owner after close      IntegrityError: Coop writer fenced

External links

Exercise

Run the sketch, then extend it. Open a second outside connection before you construct the Owner, keep it open, and try a write after the fences exist. Then add a fourth case: an outside connection that calls create_function('coop_write_permit', 0, lambda: 1) before writing. Write one sentence for each result saying which guard it met, or which guard it walked around.

Hint

The early connection fails the same way as a late one, because SQLite re-prepares statements after a schema change. The forged function succeeds in the sketch (it has no tracking triggers; on Coop's real store the forger also needs coop_context, and gets no such function: coop_context until it has one), which is the whole point of the warning: the fence stops honest stale clients, and it was never meant to stop code that lies about who it is.

Progress

Progress is local-only — sign in to sync across devices.

← 📒 From Markdown to a LedgerQuiz · 4 questions Next →The Owner File Fails Closed

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.