1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
//! The transactions the lanes bracket their statements with — one pair per direction.
//!
//! [`ReadSnapshot`] and [`WriteTransaction`] are the DIALECT's half: what a transaction of that kind
//! looks like here, one statement each. [`BeginRead`] and [`BeginWrite`] are the SURFACE's: whether
//! the caller has already opened one. Four facts, one home each, and the four functions below spend
//! them in pairs.
//!
//! The two directions are separate all the way down rather than one question with a flag, and the
//! reason is at the bottom of the table: Postgres's read statement is `READ ONLY`, which refuses an
//! `INSERT` at the server. So a surface can be inside a read and not inside a write, and a dialect's
//! two `BEGIN`s are two statements rather than one with an adjective.
use SqlDialect;
/// How ONE dialect opens a read-only transaction whose statements see one state of the database.
///
/// # Why a `const` statement rather than sqlx's `begin`
///
/// [`Acquire::begin`](sqlx::Acquire::begin) issues each driver's plain `BEGIN`, and a plain `BEGIN`
/// is not a snapshot on every engine here: Postgres defaults to `READ COMMITTED`, where each
/// statement sees its own snapshot, so a call whose second statement gathers what its first one
/// paged would tear inside its own transaction and report nothing. Naming the statement is what
/// makes the promise the same sentence on all three, and it keeps the per-dialect part where a
/// reader can see it — the same split [`QuoteIdent`](crate::QuoteIdent) and
/// [`BindCeiling`](crate::BindCeiling) make, where the dialect states one value and the work is
/// written once.
///
/// # It is the SUGAR, and the seam underneath it stays open
///
/// The framework never begins a transaction on its own. A caller who wants one on other terms opens
/// it and passes it: the engine is generic over [`Acquire`](sqlx::Acquire), which a
/// `&mut Transaction` satisfies exactly as a `&Pool` does. This trait is what
/// `find_<db>_snapshot` spends, and nothing else reaches it.
///
/// A MongoDB face has no member of this trait to name, and that is the honesty it buys: a
/// multi-statement snapshot there needs a session and a replica set, which a standalone `mongod` —
/// the common local deployment — has not got. So the method does not exist there rather than
/// promising something a deployment may not be able to keep.
///
/// # In this era one page is one statement, and the promise is still worth stating
///
/// A record whose columns are all on the row pages in a single `SELECT`, which is atomic without
/// help. What needs the transaction is the materialization that gathers an edge-stored collection
/// after the page — one statement more, on the same connection, which is exactly the pair a
/// snapshot keeps consistent.
///
/// # What each statement was measured against
///
/// | dialect | engine | the statement |
/// |---|---|---|
/// | SQLite | 3.51.0 | `BEGIN DEFERRED` |
/// | Postgres | 17.10 | `BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY` |
/// | MySQL | 8.4.11 | `START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY` |
///
/// The first two ran the query layer's whole acceptance inside one — the ordered scan through
/// `find_<db>_snapshot`, answering the same rows the un-transacted call did. MySQL's was put to the
/// server directly, which is what the statement needs: it is accepted, and `@@transaction_isolation`
/// reports `REPEATABLE-READ`, so what `WITH CONSISTENT SNAPSHOT` adds there is WHEN the snapshot is
/// taken and not which one.
/// SQLite's transactions are DEFERRED by default, which is the read case exactly: the shared lock
/// is taken at the first read rather than at `BEGIN`, and in WAL mode the reader then sees one
/// state of the database for the transaction's life while writers carry on.
///
/// Spelled out rather than left to the default, because the default is the one thing a reader
/// cannot see: `BEGIN IMMEDIATE` is the same keyword with a write lock behind it.
/// Postgres needs the isolation level NAMED, and this is the dialect the whole trait exists for.
/// Its default is `READ COMMITTED`, where every statement takes a fresh snapshot — so a plain
/// `BEGIN` would give a transaction whose two statements disagree, which is precisely the tear a
/// snapshot is asked for to prevent.
///
/// `READ ONLY` on top costs nothing and says what the transaction is for: it refuses a write rather
/// than letting a read path acquire one by accident.
/// InnoDB's default is already `REPEATABLE READ`, so what `WITH CONSISTENT SNAPSHOT` adds is WHEN
/// the snapshot is taken: at the statement rather than at the first read. That is the difference
/// between a transaction that is consistent from the moment it opened and one that is consistent
/// from the moment it first looked.
/// How ONE dialect opens a transaction that WRITES.
///
/// # It is a second trait rather than [`ReadSnapshot`] with an adjective
///
/// Two of the three dialects spell a write's `BEGIN` differently from a read's, and on one of them
/// the read's is a statement a write cannot run in at all: Postgres's is `READ ONLY`, which refuses
/// an `INSERT` at the server. So there is no one statement with a flag over it, and a trait holding
/// both would be one name answering two questions.
///
/// It is deliberately NOT a member of [`SqlDialect`](super::SqlDialect)'s bundle, where every other
/// per-dialect capability sits, and the reason is a language rule rather than a taste: both traits
/// name `BEGIN`, `COMMIT` and `ROLLBACK`, so a dialect carrying the two of them cannot answer
/// `Db::BEGIN` at all. Measured — adding it to the bundle is **`E0034`, multiple applicable items in
/// scope, at five sites**, and closing them means spelling `<Db as ReadSnapshot>::BEGIN` at every
/// existing read to say what it already said. So it travels with the signatures that spend it, which
/// is the arrangement that trait's own note already records for the three predicates it leaves out.
///
/// # What each statement is, and why SQLite's is not the default one
///
/// | dialect | the statement |
/// |---|---|
/// | SQLite | `BEGIN IMMEDIATE` |
/// | Postgres | `BEGIN` |
/// | MySQL | `START TRANSACTION` |
///
/// SQLite's default is DEFERRED, which takes a shared lock at the first read and tries to UPGRADE it
/// at the first write. An upgrade another connection has already taken is `SQLITE_BUSY` — reported
/// part-way through a transaction that has already written, and not waited for, because the waiting
/// deadlock is exactly what SQLite refuses instead. `IMMEDIATE` takes the write lock at `BEGIN`, so
/// the contention is answered before any statement of the write has run.
///
/// The other two need no adjective. Postgres's default transaction is read-write, and InnoDB's is
/// the `REPEATABLE READ` a write wants; what each of them is NOT is the read statement beside it,
/// which is the whole of why this trait exists.
/// `BEGIN IMMEDIATE` rather than the DEFERRED default — see the trait's own note for the upgrade
/// that is refused rather than waited for.
/// Postgres's plain `BEGIN`, which is READ WRITE and `READ COMMITTED`. Both are what a write wants:
/// a multi-statement write reads nothing it did not itself write, so the isolation the read side
/// names is buying a guarantee this lane has no use for and paying a serialization failure for it.
/// MySQL's own keyword for the same thing, without the `WITH CONSISTENT SNAPSHOT` the read side
/// names: that clause fixes WHEN a snapshot is taken, which is a promise about reads.
/// Whether the surface a call was handed is ALREADY inside a transaction.
///
/// # The DIALECT states the statement and the SURFACE states whether to run it
///
/// [`ReadSnapshot`] answers *what does a read transaction look like here*, which is a fact about the
/// engine. This answers *is there one already*, which is a fact about what the caller passed — a
/// `&Pool` hands out a connection with no transaction on it, and a `&mut Transaction` hands out the
/// connection its own is open on. Two questions, one home each, and the seam that spends both is
/// `begin_read` beside the lanes.
///
/// # PASSTHROUGH, never a nested transaction and never a savepoint
///
/// A call handed a surface that reports `true` runs its statements and opens nothing: the caller's
/// transaction is the read the call was asked for, and a savepoint inside it would be a second scope
/// with the same visibility and a rollback the caller did not ask for. So the framework's own
/// bracket is the OUTERMOST one or it is absent, which is what lets a caller put two calls — a page
/// and the hydration that follows it — inside one read:
///
/// ```text
/// let mut read = pool.begin().await?; // the caller's own, on the caller's terms
/// let page = Media::find_sqlite(&mut read) … .fetch().await?; // passes through
/// let hydrated = Media::hydrate_sqlite(kind, &ids, &mut read).await?; // the same read
/// ```
///
/// Handed the pool instead, each of those opens and closes its own — which is the torn read a
/// coherence refusal reports rather than hides.
///
/// # What each surface answers, and why the list is closed without being closed
///
/// | surface | answers | because |
/// |---|---|---|
/// | `&Pool<Db>` | `false` | a pooled connection is checked out with nothing open on it |
/// | `&mut PoolConnection<Db>` | `false` | the same connection, held by the caller rather than the pool |
/// | `&mut Transaction<'_, Db>` | `true` | the caller's transaction IS the read |
/// | `&mut <Db as Database>::Connection` | `false` | a bare connection, per dialect and per gate |
///
/// Those are the surfaces sqlx implements [`Acquire`](sqlx::Acquire) for, so the list is complete
/// today; the trait is public and unsealed, so a surface this crate has never heard of answers for
/// itself rather than being assumed.
/// The one surface that already holds the read, and therefore the one the framework opens nothing
/// on.
/// A bare connection is stated per DIALECT rather than once over `Db::Connection`, and that is the
/// coherence rule rather than a taste: `impl<C: Connection> BeginRead for &mut C` would overlap the
/// transaction's impl above, because nothing lets rustc rule out a future
/// `Transaction: Connection`. So each shipped driver's own connection type states it, behind that
/// driver's feature.
/// See the SQLite impl beside this one for why the connection types are named one at a time.
/// See the SQLite impl beside this one for why the connection types are named one at a time.
/// Whether the surface a call was handed is already inside a transaction a WRITE can run in.
///
/// # It is [`BeginRead`]'s question and never its answer
///
/// The two look like one fact and are not, and the counterexample is shipped: a `&mut Transaction`
/// a caller opened with [`ReadSnapshot::BEGIN`] is inside a Postgres transaction that is `READ
/// ONLY`, so it answers `true` to *am I inside a read* and its honest answer to *am I inside a
/// write* is that a write there is refused at the server. This trait is public and unsealed for
/// exactly that: a surface this crate has never heard of answers the two questions for itself
/// rather than having one inferred from the other.
///
/// What the shipped surfaces answer is the same list [`BeginRead`]'s note prints, because sqlx's own
/// `Transaction` carries no read-only marker in its type — a caller that opened one on read-only
/// terms and then writes through it gets the server's refusal, which is the message that says what
/// happened.
///
/// # PASSTHROUGH, never a nested transaction and never a savepoint
///
/// A surface that reports `true` runs the write's statements and opens nothing, for [`BeginRead`]'s
/// reason one direction over: the caller's transaction IS the atomicity the write was asked for, and
/// a savepoint inside it would be a second scope with a rollback the caller did not ask for. It is
/// also what lets a caller put SEVERAL records' writes inside one:
///
/// ```text
/// let mut write = pool.begin().await?; // the caller's own, on the caller's terms
/// Media::insert_sqlite(&mut write, media).await?; // passes through
/// Scene::insert_sqlite(&mut write, scene).await?; // the same transaction
/// write.commit().await?;
/// ```
///
/// Handed the pool instead, each of those opens and closes its own, and a failure between them
/// leaves the first one stored.
/// The one surface that already holds the transaction, and therefore the one the framework opens
/// nothing on.
/// A bare connection is stated per DIALECT for [`BeginRead`]'s coherence reason — a blanket over
/// `&mut C: Connection` would overlap the transaction's impl above.
/// See the SQLite impl beside this one for why the connection types are named one at a time.
/// See the SQLite impl beside this one for why the connection types are named one at a time.
/// Open the read transaction, unless the surface the call was handed is already inside one.
///
/// The `bool` is *this call opened it*, which is what [`close_read`] needs: a passthrough closes
/// nothing, because the transaction is the CALLER's and committing it here would end a read they are
/// still in the middle of.
pub async
/// Close what [`open_read`] opened, whichever way the call went.
///
/// The error path's failed close is dropped and the success path's is reported, for
/// `query::sql::find`'s own reason: a call that already has something to say gains nothing from a
/// rollback that did not run, and a commit that did not run means the read was not the promised one.
pub async
/// Open the write transaction where the write is several statements and the surface is not already
/// inside one.
///
/// # A SINGLE-statement write opens nothing, and `multi` is what says which it is
///
/// One statement is atomic on its own in every dialect here, so a bracket around it would be two
/// round trips buying a guarantee the statement already keeps. The caller decides from the row's
/// SHAPE rather than from its data — see [`write`](super::write), where the rule is stated — so the
/// bracket is a fact about the record's declaration and not about what one instance happened to
/// hold.
///
/// The `bool` is *this call opened it*, which is what [`close_write`] needs: a passthrough closes
/// nothing, because the transaction is the CALLER's and committing it here would end a write they
/// are still in the middle of.
///
/// The statement is spelled `<Db as WriteTransaction>::BEGIN` rather than `Db::BEGIN`, and it has to
/// be: a caller's `Db` carries [`ReadSnapshot`] through [`SqlDialect`](super::SqlDialect) and this
/// trait beside it, so the bare projection is ambiguous. That is the cost this module's own note
/// weighs against folding the write half into the bundle.
pub async
/// Close what [`open_write`] opened, whichever way the write went.
///
/// # The failed ROLLBACK is dropped and the failed COMMIT is not, and here that is the whole promise
///
/// It is [`close_read`]'s split with more riding on it. A rollback that did not run leaves a
/// connection the pool resets, and the call already has something to say; a COMMIT that did not run
/// means the write a caller was told landed did not, so it is reported and the caller sees a failure
/// rather than a success over an abandoned transaction.
pub async