Concurrency

6 / 11 · connections and snapshots in the full document · PDF

A load runs on whatever it is given. On a connection or a transaction, every query runs in turn and sees the transaction's own uncommitted writes. On a Pooled pool, the queries of each level run at the same time, each on a pooled connection held for that query only — and on PostgreSQL, all of them can share one snapshot, so the levels of a load see the same database even while others write.

Top: one connection runs the five queries $root, assignee, notes, subtasks and subtasks.assignee one after another. Middle: Pooled::snapshot with three connections, as lanes over time. At the start, connection 1 begins REPEATABLE READ READ ONLY and exports its snapshot; connections 2 and 3 begin and set the transaction snapshot. Level 0 runs $root on connection 1; level 1 runs assignee, notes and subtasks at the same time on connections 1, 2 and 3; level 2 runs subtasks.assignee; at the end every connection rolls back. A note says every connection joins the snapshot before the first query. Bottom: rows compare a connection, Pooled::snapshot and Pooled::read_committed by the connection given, connections, what the load sees, and graphs.

The caller's connection. Wherever a load or write takes a connection, it accepts a connection, a sqlx::Transaction, a pooled connection or a Pooled pool, and every query of a load runs on the connection given, so it sees the uncommitted writes of its transaction MPA-DB-5. That is the default because it is the one that is always correct. Queries are not pipelined on one connection MPA-NOT-8: SQLx 0.9 has no pipelining API.

A pool, level by level. With a Pooled pool the child queries of each level run together, each on a connection held for that query only, so a load uses at most the connections of its Pooled and cannot deadlock on its own pool MPA-LOAD-11. The keys of a level are collected from the rows above before its queries start. Pooled::snapshot begins REPEATABLE READ READ ONLY on one connection, exports its snapshot, and imports it on the others; it exists only for PostgreSQL, so asking for it on MySQL or SQLite does not compile.

Joining up front. Every connection imports the snapshot before the first query runs. If a query failed while others were still joining, its transaction could end under them; joining first removes that race. Pooled::read_committed works on any database and lets each query see what is committed when it runs. Graph loads run their queries one at a time on one connection, because which query reaches an entity first decides where its row comes from, and that should not vary from run to run.