In SQLAlchemy, an Engine created before a pre-forking server starts four workers begins returning protocol errors and mixed-up results; why, and how does engine.dispose(close=False) fix it?
answer
- a socket is a file descriptor
- the fork copies the pool
- start the child with an empty pool
- dereference, do not close
basics
~20 sPooled connections are sockets, and fork copies them into every worker, so several processes talk over one connection. Calling engine.dispose(close=False) in each child replaces its pool with an empty one without closing the parent's connections.
solid answer
~40 sIf the parent used the Engine before forking, its `QueuePool` holds open DBAPI connections, and each is a socket file descriptor the fork copies into every worker. Two processes then send and read on the same socket, and messages interleave: protocol errors, wrong rows, dropped connections. `pool_pre_ping` does not catch it, because the shared socket is alive. The documented fix is `engine.dispose(close=False)` in each child at startup, for example in a Gunicorn `post_fork` hook or a `multiprocessing` initializer: it gives the child a new, empty pool with the same settings and event listeners, and with `close=False` it drops the old pool without closing the parent's sockets. Alternatives are `engine.dispose()` in the parent just before forking, `poolclass=NullPool`, or a pid-checking pool event.
code
python · 29 linesimport multiprocessing as mp
import os
from sqlalchemy import create_engine, text
engine = create_engine("sqlite:///orders.db", pool_size=5, max_overflow=10)
with engine.connect() as conn: # parent now holds one pooled connection
conn.execute(text("SELECT 1"))
print("parent", os.getpid(), engine.pool.status())
def init_worker():
# drop the inherited pool without closing the parent's sockets
engine.dispose(close=False)
def work(n):
with engine.connect() as conn:
value = conn.execute(text("SELECT :n"), {"n": n}).scalar_one()
return os.getpid(), value, engine.pool.checkedin()
if __name__ == "__main__":
ctx = mp.get_context("fork")
with ctx.Pool(2, initializer=init_worker) as workers:
for row in workers.map(work, [1, 2]):
print("child", row)
print("parent", os.getpid(), engine.pool.status())go deeper
Recall the rule: a SQLAlchemy pool must not be shared across a fork, and each worker process needs its own connections.
Explain why fork copies socket file descriptors, and what dispose(close=False) does differently from the default dispose().
Diagnose interleaved protocol errors after a preload deploy, put the dispose call in the server's post-fork hook, and explain why pre-ping did not catch it.
Make per-process engine setup part of the service template, so startup checks added later cannot quietly reintroduce shared connections.
## The failure A pre-forking server such as Gunicorn with `preload_app` (`--preload`), a `multiprocessing` pool, or a plain `os.fork()` all start child processes as **copies of the parent**. If the parent created an `Engine` at import time and used it even once, for a startup health check, a cache warm-up or a migration check, its `QueuePool` now holds open DBAPI connections. Each of those is a **socket file descriptor**, and the fork copies it into every child. Now four worker processes and the parent all believe they own the same connection. When two of them check it out, they write requests into the same socket and read each other's replies. SQLAlchemy's pooling documentation describes the range of symptoms, from connections that simply do not work to sockets **used by several processes at once, leading to broken messaging**, which it calls the most common case. In practice that looks like protocol errors, SSL errors, a query receiving another query's rows, or the server closing the connection. `pool_pre_ping` does not help: the shared socket is still open, so the ping can pass. ## The rule The pooling documentation states it plainly: pooled connections **must not be shared with a forked process**. The child must start with a pool that holds none of the parent's connections. ## The four documented fixes 1. **`engine.dispose(close=False)` in the child, at startup.** This is the approach SQLAlchemy recommends. `Engine.dispose()` replaces the engine's pool with a new, empty one built with the same settings, and it **moves the pool's event listeners** to the new pool. With `close=False` the old pool is simply dereferenced: the child never touches the parent's sockets, so the parent keeps working. 2. **`engine.dispose()` in the parent, directly before forking.** The parent closes its idle connections, so the children inherit an empty pool. The parent pays a reconnect afterwards. 3. **`poolclass=NullPool`.** A pool that never keeps a connection has nothing to pass on, at the price of a connect per checkout. 4. **A pool event that checks the process id.** A `connect` listener stores `os.getpid()` in `connection_record.info`; a `checkout` listener compares it and raises `DisconnectionError` when they differ, which makes the pool replace that connection. ## Why close=False matters The default `dispose()` (`close=True`) **closes every checked-in connection** in the old pool. In a child, those are the parent's sockets too, so closing them from the child can disturb connections the parent is still using. `close=False` exists for exactly this case. ## Where to put the call - **Gunicorn**: in a `post_fork(server, worker)` server hook, which runs in each worker right after it is forked. - **`multiprocessing.Pool`**: in the `initializer` function, as below. - **Plain `os.fork()`**: first thing in the child branch. ## What dispose does not cover - A **`Connection` object** that was checked out when the fork happened cannot be fixed by these steps. Keep each `Connection` inside one process and one thread. - A **`Session` with a transaction in progress** must never cross a fork. Create new `Session` objects in the child. - If the parent **never connected**, its pool is empty and nothing is copied. That is a coincidence of today's startup code, not a guarantee; the first health check someone adds breaks it. Make the dispose call unconditional. ## How to confirm the diagnosis 1. Add a `connect` pool listener that logs `os.getpid()` for each new DBAPI connection, and a `checkout` listener that logs the pid doing the checkout. A connection opened under the parent's pid and checked out under a worker's pid is the proof. 2. Check whether the errors began when preloading was switched on, or when someone added a startup query that runs before the fork. 3. Log `engine.pool.status()` at the start of each worker: a fresh pool holds no connections, while an inherited one already shows checked-in connections. ## Checklist - Create the `Engine` once per process, or dispose its pool in each child. - Call `engine.dispose(close=False)` in every child's start-up hook. - Do not pass connections or open sessions across the fork. - Keep `pool_pre_ping` for stale connections, but do not rely on it to detect sharing.
- Why not call plain engine.dispose() in the SQLAlchemy child process?The default `close=True` closes every checked-in connection in the old pool. In a child those connections share sockets with the parent, so the child would be closing connections the parent may still be using. `close=False` dereferences the old pool and leaves those connections alone.
- How can a SQLAlchemy pool event detect a connection copied across a fork?Listen for `connect` and store `os.getpid()` in `connection_record.info`. In a `checkout` listener compare it with the current pid; on a mismatch detach the DBAPI connection and raise `sqlalchemy.exc.DisconnectionError`. The pool treats that as a dead connection and opens a new one for this process.
- Does engine.dispose(close=False) make it safe to hand an open Session to a child process?No. Dispose only replaces the Engine's pool. A `Connection` checked out at the moment of the fork, or a `Session` with a transaction in progress, still refers to the parent's connection. Create new Sessions in the child and keep each Connection inside one process.
- Each forked worker automatically gets its own fresh SQLAlchemy pool
- pool_pre_ping will detect a connection shared across a fork
- Plain engine.dispose() in the child is just as safe as close=False
- Disposing the pool also fixes a Session that crossed the fork
- If the parent never connected, the dispose call can be left out safely