WEB / WEB-18.02

Query lifecycle, transactions, and ORM

Separate bound data from statements and trace ownership from query results to commit. Indexes, pools, and ORM organize different parts of that lifecycle.

CORE · RED · BLUE · ARTIFACT · PRACTICE

Read every scenario without motion

Commit note

Choose use case
The application selects a use case to change Tea note to Updated note and add an audit row. Fixed statements and bound data remain separate; no lease or transaction has started.
Application
Tea note → Updated note + audit selected
Connection owner
No lease
SQLite writer state
Tea note; audit=0
Independent reader
Tea note
Connection boundary
The connection owner admits the writer lease and executes BEGIN. SQLite writer and independent reader are separate connections; driver calls are local, with no network packet.
Application
Tea note → Updated note + audit selected
Connection owner
Writer lease; BEGIN
SQLite writer state
Tea note; audit=0
Independent reader
Tea note
Pending write
The title update is visible in the writer transaction; execute success is neither commit nor fresh-reader visibility.
Application
Tea note → Updated note + audit selected
Connection owner
Writer lease; BEGIN
SQLite writer state
Writer: Updated note; uncommitted
Independent reader
Tea note
Separate reader
A separate connection finishes its SELECT on the previous committed title; no long-lived read snapshot is held.
Application
Tea note → Updated note + audit selected
Connection owner
Writer lease; BEGIN
SQLite writer state
Writer: Updated note; uncommitted
Independent reader
Before commit: Tea note
Second statement
The audit statement inserts a valid row. The writer sees Updated note and audit=1 within its open transaction; the reader’s last observation is still Tea note, with no commit yet.
Application
Tea note → Updated note + audit selected
Connection owner
Writer lease; BEGIN
SQLite writer state
Audit valid; pending
Independent reader
Before commit: Tea note
Transaction end
The writer executes COMMIT, accepting Updated note and audit=1 in the same transaction. The independent reader has not issued a fresh SELECT yet, so its prior Tea note observation remains.
Application
Tea note → Updated note + audit selected
Connection owner
COMMIT succeeded
SQLite writer state
Updated note; audit=1 committed
Independent reader
Before commit: Tea note
Fresh read
A fresh SELECT observes the completed transaction outcome; crash durability and replica consistency are not measured here.
Application
Tea note → Updated note + audit selected
Connection owner
COMMIT succeeded
SQLite writer state
Updated note; audit=1 committed
Independent reader
Fresh reader: Updated note
Use-case result
The application uses the observed outcome; local results, ORM objects, and response preparation have different ownership stages.
Application
Commit result: Updated note; audit=1
Connection owner
COMMIT succeeded
SQLite writer state
Updated note; audit=1 committed
Independent reader
Fresh reader: Updated note

Rollback note

Choose use case
The application selects a use case to change Tea note to Updated note and add an audit row. Fixed statements and bound data remain separate; no lease or transaction has started.
Application
Tea note → Updated note + audit selected
Connection owner
No lease
SQLite writer state
Tea note; audit=0
Independent reader
Tea note
Connection boundary
The connection owner admits the writer lease and executes BEGIN. SQLite writer and independent reader are separate connections; driver calls are local, with no network packet.
Application
Tea note → Updated note + audit selected
Connection owner
Writer lease; BEGIN
SQLite writer state
Tea note; audit=0
Independent reader
Tea note
Pending write
The title update is visible in the writer transaction; execute success is neither commit nor fresh-reader visibility.
Application
Tea note → Updated note + audit selected
Connection owner
Writer lease; BEGIN
SQLite writer state
Writer: Updated note; uncommitted
Independent reader
Tea note
Separate reader
A separate connection finishes its SELECT on the previous committed title; no long-lived read snapshot is held.
Application
Tea note → Updated note + audit selected
Connection owner
Writer lease; BEGIN
SQLite writer state
Writer: Updated note; uncommitted
Independent reader
Before commit: Tea note
Second statement
The audit insert raises a NOT NULL IntegrityError. SQLite ABORT reverts only that failed statement; writerAfterError=Updated note and transactionOpenAfterError=true remain, with no added audit row.
Application
Tea note → Updated note + audit selected
Connection owner
Writer lease; BEGIN
SQLite writer state
Audit error; title still pending
Independent reader
Before commit: Tea note
Transaction end
The writer explicitly ROLLBACKs, discarding pending Updated note and closing the transaction. Store state becomes Tea note and audit=0; the independent reader’s new SELECT is the next step.
Application
Tea note → Updated note + audit selected
Connection owner
Explicit ROLLBACK succeeded
SQLite writer state
Tea note; audit=0 restored
Independent reader
Before commit: Tea note
Fresh read
A fresh SELECT observes the completed transaction outcome; crash durability and replica consistency are not measured here.
Application
Tea note → Updated note + audit selected
Connection owner
Explicit ROLLBACK succeeded
SQLite writer state
Tea note; audit=0 restored
Independent reader
Fresh reader: Tea note
Use-case result
The application uses the observed outcome; local results, ORM objects, and response preparation have different ownership stages.
Application
Rollback result: Tea note; audit=0
Connection owner
Explicit ROLLBACK succeeded
SQLite writer state
Tea note; audit=0 restored
Independent reader
Fresh reader: Tea note

Lease busy/reset

Choose use case
Application B requests a lease from the local adapter with capacity=1. Checkout precedes query execution; B has neither a query result nor an empty-row observation at this start.
Application
B lease request; capacity=1 selected
Connection owner
No lease
SQLite writer state
Tea note; audit=0
Independent reader
Tea note
Connection boundary
The only slot is held by A, so B acquires no lease. This local busy decision yields queryExecuted=false for B; it is not a SQLite SELECT returning an empty row set.
Application
B lease request; capacity=1 selected
Connection owner
Held by A; B no lease
SQLite writer state
Tea note; audit=0
Independent reader
Tea note
Busy result
The second borrower executes no query without a lease; this is neither an empty database result nor an SQL error.
Application
B query not executed
Connection owner
Held by A; B no lease
SQLite writer state
Tea note; audit=0
Independent reader
Tea note
Pending lease write
The first borrower begins a transaction and updates a benign pending title within its lease; no commit or next-borrower visibility exists yet.
Application
B query not executed
Connection owner
Held by A; B no lease
SQLite writer state
Pending lease note; uncommitted
Independent reader
Tea note
Reset rollback
The first borrower pending transaction is explicitly rolled back; no claim covers every backend resource.
Application
B query not executed
Connection owner
Held by A; B no lease
SQLite writer state
Pending title discarded
Independent reader
Tea note
Return lease
The connection becomes reusable after transaction cleanup; capacity remains an instructional value.
Application
B query not executed
Connection owner
Returned clean; available
SQLite writer state
Pending title discarded
Independent reader
Tea note
Next read
After clean return, a later borrower acquires the lease and sees Tea note on a new read. The right-hand read card now represents that borrower; it is not a read performed by previously refused B.
Application
B query not executed
Connection owner
Returned clean; available
SQLite writer state
Pending title discarded
Independent reader
Next borrower: Tea note

ACTORS · STATE · DATA FLOW

Statements, bound data, and result shape

Query lifecycle begins when the application selects an operation. The driver supplies a statement and bound values, the engine executes a suitable plan, and a cursor returns rows. Our SELECT id, title FROM notes WHERE title=? is fixed; Tea note is a parameter value. Placeholders bind values, not table/column names or arbitrary SQL fragments. Dynamic identifiers need a separate controlled-selection contract.

Cursor.execute runs a query and fetchone/fetchall consumes results. Selected columns define shape; finding a row does not authorize its use. These are public notes and the title is bound only as data. Separate statement/parameter artifact fields expose that distinction with a normal harmless title. Mapping a result to an application object does not change query-engine ownership.

Transaction decisions are broader than statement success

A transaction defines the boundary for several selected changes. Our writer executes BEGIN, updates a title, and inserts an audit row. After successful COMMIT, a fresh reader sees Updated note and one audit row. Writer-local uncommitted results differ from committed state visible to another connection. This reader finishes each SELECT and holds no long-lived read transaction; other isolation/journal choices have different visibility boundaries.

The rollback branch first changes the title, then a NOT NULL audit condition fails on supplied harmless invalid data. SQLite default ABORT reverts the failing statement, not automatically every earlier change in the transaction. The writer explicitly ROLLBACKs, after which a reader sees Tea note and zero audit rows. An error class or update rowcount alone does not mean the transaction ended or committed.

The native disk fixture reopens a connection after successful commit and reads the same data. That proves orderly-reopen persistence, not power-loss, corruption, replica-failover, or every durability setting. It also excludes prepared HTTP responses/client arrivals. SQLite permits multiple readers but only one simultaneous write transaction; do not equate every database product concurrency semantics with this example.

Different responsibilities of indexes, pools, and ORM

An index offers a lookup option to the planner, not a promise that every query uses it or gets faster. For selected WHERE title=?, SQLite shows a scan before idx_notes_title and an indexed search afterward. Result rows stay the same; no duration/throughput is measured. Indexes cost storage/write maintenance and replace neither integrity/authorization nor commit.

A connection pool reuses physical connections across requests/use cases. Checkout is a resource lease, not HTTP keep-alive or a browser session. Full capacity needs a bounded wait/refuse policy. Returning connections requires uncommitted-state cleanup. SQLAlchemy documents reset-on-return, while rollback alone may not clean every temporary/session resource on every backend. Our one-slot lease adapter explicitly rolls back without executing a SQLAlchemy pool.

An ORM can map relational rows to objects and organize a unit of work. SQLAlchemy Session identity maps keep object identity per primary key; changing an object, flushing SQL, and committing are separate stages. Queries/lazy loads can cause autoflush; successfully flushed SQL is not a commit. Native work uses Python DB-API, not an ORM framework or its default query count.

Read every scenario without motion

ABORT and rollback

The writer-transaction condition was read
The writer changed Tea note to Updated note. A later audit statement fails a NOT NULL constraint; that failure does not automatically erase the earlier successful statement.
Writer/lease conditions
Writer: Updated note
Transaction and cleanup
Audit: IntegrityError
Consumer/borrower boundary
Cleanup pending
ABORT scope was separated
rollback.failure specifies ABORT statement scope. transactionOpenAfterError=true and writerAfterError=Updated note show that the transaction remains open after failure.
Writer/lease conditions
Statement failed
Transaction and cleanup
Transaction still open
Consumer/borrower boundary
Writer still sees update
Error handling was linked with cleanup
Producing an HTTP error is not transaction-cleanup evidence. The artifact declares explicitRollback=true; that separate operation rolls back the pending change.
Writer/lease conditions
Explicit rollback=true
Transaction and cleanup
Pending write discarded
Consumer/borrower boundary
Reader outcome pending
The final reader state was checked
readerAfterRollback=Tea note and auditRows=0. Known rollback does not contradict the writer seeing Updated note at the error boundary; they describe different times.
Writer/lease conditions
Reader: Tea note
Transaction and cleanup
Audit rows=0
Consumer/borrower boundary
No committed update
The connection was considered for its next borrower
The pool retains returnedAfterRollback=true and transactionOpenOnReturn=false. Returning a connection without cleanup can mix the next task’s transaction context.
Writer/lease conditions
Rollback completed
Transaction and cleanup
Transaction open=false on return
Consumer/borrower boundary
Clean lease return
Error semantics were not generalized
This is the selected SQLite ABORT and explicit-rollback flow. It does not assume every error rolls back the same scope or every database exposes the same exception API.
Writer/lease conditions
Selected failure semantics
Transaction and cleanup
Known cleanup result
Consumer/borrower boundary
No universal rollback claim

Lease busy

Pool capacity was selected
The pool example is a local lease adapter with capacity=1. This is not a SQLAlchemy production-pool size; it is selected to illustrate an access boundary.
Writer/lease conditions
Capacity=1
Transaction and cleanup
Lease decision pending
Consumer/borrower boundary
Query not started
The busy-acquisition record was reviewed
busyAcquired=false means no connection lease was acquired. Because the query did not execute, this branch has no database result to read.
Writer/lease conditions
Busy acquired=false
Transaction and cleanup
Query executed=false
Consumer/borrower boundary
No result rows
No-query was not treated as an empty result
busyQueryExecuted=false is not a SELECT that found zero rows. Lease admission and query results are kept separate.
Writer/lease conditions
Lease denied
Transaction and cleanup
Database not queried
Consumer/borrower boundary
Empty database UNKNOWN
The clean-return condition was read
returnedAfterRollback=true and transactionOpenOnReturn=false are declared. Reusability of the slot is assessed together with cleanup of its pending transaction.
Writer/lease conditions
Lease returned
Transaction and cleanup
Transaction closed
Consumer/borrower boundary
Next borrower pending
The next-borrower state was matched
nextBorrowerTitle=Tea note shows state after the selected rollback. The busy branch itself is not considered to have learned this title from a query.
Writer/lease conditions
Busy branch: no query
Transaction and cleanup
Next borrower: Tea note
Consumer/borrower boundary
Observation contexts separate
Pool-abstraction scope was preserved
This lease result proves neither real pool contention, timeout duration nor ORM autoflush. Each adapter’s acquisition and reset behavior needs its own contract.
Writer/lease conditions
Local lease boundary known
Transaction and cleanup
ORM runtime unobserved
Consumer/borrower boundary
No tuning claim

ASSUMPTION · BOUNDARY · CONTROL

Execution success, commit, and access policy

Review benign fixed statements separately from parameters. Searching Tea note compares data rather than granting arbitrary query construction. Binding helps treat selected values as data instead of SQL structure, but it alone determines neither resource selection policy nor object permission. This lesson stays within a public fixture.

A writer seeing an updated title does not imply a separate reader sees it. The writer retaining that title after a failed audit statement demonstrates SQLite ABORT scope; both connections returning to original state after explicit rollback is separate evidence. A possible failure is mapping a transaction error only to an HTTP error body while leaving work pending. Trace outcome handling together with connection lifecycle.

Abstractions do not erase boundaries

An ORM property containing Updated note is not a store-commit proof. Read flush/query/cache/identity-map behavior through framework/configuration scope. Likewise, a changed index plan proves neither result correctness nor production throughput. Limit claims to the exact operation and observed boundary; query counts or class names alone are insufficient.

The no-lease branch executes no query; it is not an empty database result. Cleanup before the next borrower matters when returning a lease. Fixture capacity=1 is an instructional boundary, not production tuning advice. The tiny native adapter demonstrates clean reuse without measuring real pool contention, timeout durations, ORM autoflush, or concurrent scheduling.

Read every scenario without motion

Commit visibility

Two connection observations were linked
writerBefore=Tea note and writerDuring=Updated note. readerBeforeCommit=Tea note is a fresh read on another connection; the writer view is not the reader view.
Connection observation
Writer sees Updated note
Transaction/plan review
Reader sees Tea note
Evidence conclusion
Commit pending
The post-commit read boundary was reviewed
readerAfterCommit=Updated note describes a new read after commit. The reader consumed its earlier SELECT; the same result is not assumed for a long-lived snapshot.
Connection observation
Fresh read after commit
Transaction/plan review
Reader: Updated note
Evidence conclusion
Audit review pending
The audit record was linked with state
auditRowsAfterCommit=1 is linked with the updated title in the accepted transaction result. writerDuring alone cannot replace this committed tuple.
Connection observation
Title committed
Transaction/plan review
Audit rows=1
Evidence conclusion
Commit tuple known
A reopened read was treated as separate evidence
reopenedTitle=Updated note shows the record readable after reopening the connection. With powerLossTested=false, it is not a crash-durability benchmark.
Connection observation
Reopened: Updated note
Transaction/plan review
Power loss not tested
Evidence conclusion
Durability claim bounded
An ORM object change did not become commit
orm.objectChangedIsCommit=false and flushIsCommit=false. Framework object state, flush and database commit are assessed as different boundaries.
Connection observation
Object change ≠ commit
Transaction/plan review
Flush ≠ commit
Evidence conclusion
Framework execution separate
No permission outcome was added
The fixed parameterized query compares Tea note as data. Commit visibility and parameter binding cannot prove the actor’s object-permission check.
Connection observation
Query values separated
Transaction/plan review
Commit visibility known
Evidence conclusion
Permission unestablished

Plan and cleanup

The index-plan comparison was opened
The index record declares scan before and indexed search after. A plan category describes the access path, not response data or elapsed time.
Connection observation
Before: scan
Transaction/plan review
After: indexed search
Evidence conclusion
Result review pending
Row equality was matched separately
resultRowsEqual=true retains the same query result. With durationMeasured=false, no speedup percentage or throughput result is given.
Connection observation
Rows equal=true
Transaction/plan review
Duration measured=false
Evidence conclusion
No speed claim
The lease-cleanup condition was read
The selector counts lease observations with returned=true and transactionOpen=true. An index plan cannot validate this connection-cleanup condition.
Connection observation
Returned + open is condition
Transaction/plan review
Plan ≠ cleanup evidence
Evidence conclusion
Selector review pending
Negative and positive fixtures were separated
The normal reset record has expected=0 and the inconsistent comparison has positiveExpected=1. The comparison is an input testing the condition, not an actual dirty-lease event.
Connection observation
Normal expected=0
Transaction/plan review
Comparison expected=1
Evidence conclusion
Fixture distinction retained
A missing observation was not counted as clean
With a missing lease record or transactionOpen field, zero matches cannot prove a clean connection. Return, transaction state and next-borrower result are needed together.
Connection observation
Missing record possible
Transaction/plan review
Cleanup UNKNOWN
Evidence conclusion
No clean conclusion
Abstraction evidence remained separate
Plan correctness, row equality and connection reset are three observations. A plain sqlite3 result cannot prove SQLAlchemy pool behavior or cleanup of every backend-local resource.
Connection observation
Plan evidence bounded
Transaction/plan review
Lease evidence bounded
Evidence conclusion
Other resources unobserved

IMPLEMENTATION · SECURITY CONTROL · VERIFICATION

Keep query and transaction ledgers separate

The query artifact records statement, parameters=[Tea note], and rows=[[1,Tea note]]. The normal transaction separates writerBefore=Tea note, writerDuring=Updated note, readerBeforeCommit=Tea note, and readerAfterCommit=Updated note. This is bounded fresh-read visibility because the reader consumes and finishes SELECTs, not a generalization to long-lived snapshots.

The rollback ledger records transactionOpen=true and writerAfterError=Updated note before explicitRollback=true restores readerAfterRollback=Tea note. auditRows=0 is checked. These are authored observation fields, not predefined product logs. Associate the DB-API error with selected SQL state without adding client-delivery claims or durability benchmarks.

Cleanup selectors, index plans, and ORM scope

XPath count(/audit/lease[@returned="true" and @transactionOpen="true"]) counts authored leases returned with an open transaction. Normal reset gives 0 and a deliberately inconsistent positive fixture gives 1. queryExecuted=false in the busy branch is benign and expected. With no observation, zero is insufficient evidence rather than a clean-connection proof.

Compare result rows and EXPLAIN QUERY PLAN before and after indexing. Plan text depends on the engine and version; a changed plan does not establish an unmeasured speed result. In an ORM, memory mutation, flush and commit also remain distinct: the SQLAlchemy Session identity map maintains object identity without replacing the transaction outcome. Reset clears the pending transaction in this example, not necessarily every backend-local resource. Driver, transaction, pool and application outcomes therefore remain separate.

The application chooses SQL structure and binds data such as Tea note separately through driver placeholders rather than concatenating it into query text. Placeholders do not grant permission to choose tables or columns; a variable identifier requires a predefined allowed application choice. Define both transaction success and failure paths: commit only after required steps complete, and roll back the transaction left open after this example’s failing statement before returning the connection for new work. Failed reset must not become assumed-clean reuse. Verify commit, explicit rollback and a query never executed because the lease is busy separately; missing query or pool-state evidence cannot support success.

NOW APPLY IT TO THE CASE

Which result remains unproved when the writer sees an update?

The Tea note update is read through commit, rollback and lease-return boundaries. Index plans, ORM object state and reader visibility are not reduced to one success flag.

Compare the cached case representation with its authoritative data owner. Keep stored value, revision, transaction and cache-copy completion separate.

Open the related case file ↗

READ THE EXAMPLE TOGETHER

Query, transaction, and lease-outcome ledger

Select a field and examine what it supports and where the evidence ends.

Read the raw artifact and field explanations

{
  "query": {
    "statement": "SELECT id, title FROM notes WHERE title=?",
    "parameters": [
      "Tea note"
    ],
    "rows": [
      [
        1,
        "Tea note"
      ]
    ],
    "identifiersBoundAsValues": false
  },
  "commit": {
    "writerBefore": "Tea note",
    "writerDuring": "Updated note",
    "readerBeforeCommit": "Tea note",
    "readerAfterCommit": "Updated note",
    "auditRowsAfterCommit": 1,
    "reopenedTitle": "Updated note",
    "powerLossTested": false
  },
  "rollback": {
    "writerAfterError": "Updated note",
    "transactionOpenAfterError": true,
    "explicitRollback": true,
    "readerAfterRollback": "Tea note",
    "auditRows": 0,
    "failure": "NOT NULL expected IntegrityError; ABORT statement scope"
  },
  "index": {
    "name": "idx_notes_title",
    "column": "title",
    "beforeCategory": "scan",
    "afterCategory": "indexed search",
    "resultRowsEqual": true,
    "durationMeasured": false
  },
  "pool": {
    "implementation": "own one-slot local lease adapter; not SQLAlchemy runtime",
    "capacity": 1,
    "busyAcquired": false,
    "busyQueryExecuted": false,
    "returnedAfterRollback": true,
    "transactionOpenOnReturn": false,
    "nextBorrowerTitle": "Tea note"
  },
  "orm": {
    "reference": "SQLAlchemy 2.0.54 documentation only",
    "objectChangedIsCommit": false,
    "flushIsCommit": false,
    "frameworkExecuted": false
  },
  "selector": {
    "language": "XPath",
    "xml": "<audit><lease returned=\"true\" transactionOpen=\"false\"/></audit>",
    "xpath": "count(/audit/lease[@returned=\"true\" and @transactionOpen=\"true\"])",
    "expected": 0,
    "positiveFixtureXml": "<audit><lease returned=\"true\" transactionOpen=\"true\"/></audit>",
    "positiveExpected": 1,
    "scope": "Only authored protocol-decision observations, not product log field names or a live HTTP capture.",
    "missingFieldBehavior": "Missing observation is insufficient evidence; zero matches is not proof of correct runtime behavior."
  }
}

query: Bound data separate from fixed SQL structure.
rollback: Statement ABORT separated from explicit transaction rollback.
pool: Lease/reset at instructional capacity, not a framework pool.

Owned tiny SQLite file and local adapter; orderly reopen only, with no crash, ORM, network/client response, or production-pool measurement.

Two reality checks

Any statement error always rolls back the whole transaction.

SQLite default ABORT reverts the failing statement; earlier work requires selected transaction handling and explicit rollback.

An ORM flush or updated object means commit.

Object change, SQL flush, and commit are separate stages; trace the framework transaction boundary.

YOUR TURN

Work through this story.

Predict the outcome, find the evidence boundary, and explain it in your own words.

After a NOT NULL statement error, transactionOpen is true. Has an earlier successful update automatically rolled back?

No; explicit rollback is required.
Yes; every database error automatically rolls back the whole transaction.

Read the explained answer

No; explicit rollback is required. — WriterAfterError may remain Updated note; after explicitRollback true, the reader sees Tea note and auditRows=0.

The plan changed from scan to indexed search. Was improved speed measured?

Selected lesson artifact fields; teaching example

index: {"name":"idx_notes_title","column":"title","beforeCategory":"scan","afterCategory":"indexed search","resultRowsEqual":true,"durationMeasured":false}

Yes; indexed search guarantees lower latency in every workload.
No; durationMeasured=false and rows match.

Read the explained answer

No; durationMeasured=false and rows match. — The index shows an access-path change; actual latency/workload/hardware comparison needs separate measurement.

What separate completions are ORM object changes/flush, database commit and pooled-connection return?

Read the explained answer

Changing an ORM object or flushing it does not commit the transaction. The ORM behavior is explained from documentation here; the framework was not run (frameworkExecuted=false). The database example shows reader visibility and the title after reopening, but power loss was not tested. Returning the connection must leave no open transaction. The local one-slot adapter is not an executed SQLAlchemy pool.

□ I separated flush from commit.

□ I preserved pool-reset and executed-framework scope.

Open the application console · request / response / browser

Also open request, URL, cookie and DOM examples

Sources and scope

Python 3.14 — sqlite3Tutorial · sqlite3.connect · Cursor.execute · How to use placeholders to bind values in SQL queries · Transaction control via the isolation_level attribute · How to use the connection context manager
SQLite — Transaction2. Transactions · 2.1. Read transactions versus write transactions · 2.2. DEFERRED, IMMEDIATE, and EXCLUSIVE transactions · 2.3. Implicit versus explicit transactions
SQLite — The ON CONFLICT ClauseABORT · ROLLBACK
SQLite — Query Planning1.1. Tables Without Indices · 1.3. Lookup By Index · 1.5. Multiple AND-Connected WHERE-Clause Terms
SQLAlchemy 2.0.54 — Session BasicsWhat does the Session do ? · Flushing · Committing · Rolling Back · Expiring / Refreshing
SQLAlchemy 2.0.54 — Connection PoolingConnection Pooling · Connection Pool Configuration · Reset On Return · Custom Reset-on-Return Schemes

YOUR NOTES / THIS BROWSER

Query lifecycle, transactions, and ORM

1. Core · Mechanism

Commit note

Rollback note

Lease busy/reset

Statements, bound data, and result shape

Transaction decisions are broader than statement success

Different responsibilities of indexes, pools, and ORM

2. Red · Trust & Impact

ABORT and rollback

Lease busy

Execution success, commit, and access policy

Abstractions do not erase boundaries

3. Blue · Secure development & defense

Commit visibility

Plan and cleanup

Keep query and transaction ledgers separate

Cleanup selectors, index plans, and ORM scope

Which result remains unproved when the writer sees an update?

Two reality checks

After a NOT NULL statement error, transactionOpen is true. Has an earlier successful update automatically rolled back?

The plan changed from scan to indexed search. Was improved speed measured?

What separate completions are ORM object changes/flush, database commit and pooled-connection return?

Sources and scope