Concurrency

PizzaSQL is thread-safe, but its concurrency model is coarse. There is no MVCC and no fine-grained row locking. Concurrency is governed by a small number of locks and caches at the per-database-instance level.

Locking model

Each database namespace has a SchemaManager that owns two locks:

Lock	Purpose	Scope
mu	protects the in-memory schema/index caches and version counter	per database
txMu	coordinates transactions with ordinary statements	per database

The transaction lock works as follows:

• BEGIN takes the write lock (txMu.Lock()) and holds it until COMMIT or ROLLBACK.
• Every other statement (a SELECT, INSERT, etc. issued outside a transaction) takes the read lock (txMu.RLock()) for the duration of that single statement.

Because a transaction holds the write lock exclusively, an open transaction blocks all other statements on that database, including reads. Multiple non-transactional statements may run concurrently (read locks are shared), but each statement is additionally serialized against schema mutations via mu.

The net effect:

• Cross-connection, non-transactional reads/writes can interleave statement-by-statement; each statement is atomic with respect to the row cache.
• One open transaction pauses everything else on the database until it finishes.
• Different databases have independent locks — traffic on database A never blocks database B.

Connection and executor topology

The locking happens on the shared SchemaManager/TableManager for a database, not per connection:

• PostgreSQL wire server: every accepted connection gets its own Executor, but they share the same SchemaManager and TableManager for the target database (via the DatabaseManager). The locks above are therefore shared across all connections to a database.
• HTTP server: one Executor (and thus one SchemaManager/TableManager) is cached per database name and reused across HTTP requests, so concurrent HTTP requests to the same database share the same locks.

The shared state is what makes the database-wide transaction lock effective across clients.

In-memory caches and their invalidation

Several caches live in memory per TableManager/SchemaManager and affect observable behaviour:

• Row cache (rowCache / rowIDMap): a table’s rows, loaded lazily on first SELECT. A SELECT reads from storage once, then serves from cache until a write invalidates the table.
• Index entry cache (indexCache): value→rowids mappings, built lazily (see Indexes).
• Schema cache: table/index definitions, invalidated by DDL.

Writes (INSERT/UPDATE/DELETE) invalidate the affected table’s row cache and update any warm in-memory index entries. A table-level or index-level write is a full invalidation, not a per-row patch.

Implications:

• The first query against a table in a fresh connection (or after a restart) pays a full read of that table from storage.
• Because caches are per TableManager, a schema or data change made through one executor is eventually seen by others — the executor re-syncs its analyzer catalog when the schema version changes, and row data is read from the shared storage-backed cache.

No MVCC — what that means

• There is no snapshot isolation: a long SELECT does not run against a stable point-in-time snapshot while other statements proceed. Instead, statements serialize against the transaction lock.
• There are no row-level locks and no SELECT ... FOR UPDATE, NOWAIT, or deadlock detection.
• Write conflicts are resolved at the primary-key level (duplicate-PK detection on insert), not by version checking.

Thread safety and the KV layer

Below the SQL layer, a connection pool to the PizzaKV key-value store fans out individual key operations. Pool connections are checked out per operation and returned afterward; a timeout or short read on a pooled connection closes it and replaces it rather than reusing a possibly-corrupt connection. This is internal detail, but it means key-value I/O is safe to issue from many goroutines (e.g. INSERT ... SELECT writes rows concurrently).

Practical guidance

• Avoid holding long transactions on a busy database — they block all other traffic.
• For high-concurrency workloads, prefer many short statements over one big transaction.
• Assume the first access after a cold start or a fresh connection is slower (cold caches); warm caches are fast.
• Do not rely on read-your-writes visibility across databases — each database is a separate lock and cache domain.