---
title: Concurrency
description: How PizzaSQL handles concurrent queries — per-database locking, in-memory caches, and why there is no MVCC.
---

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](/engine/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.