LEARN. PRACTICE. GROW.Locate the boundary before correcting
The script uses three actual connections to a temporary SQLite file with invented data. This workshop has no pool: each connection is already available when a statement is called. A starts BEGIN IMMEDIATE and changes the value from 10 to 20 without committing. A observes 20 while another connection still observes 10. When B attempts to start writing, it receives SQLITE_BUSY, code 5. After A rolls back, B can write and commit 30. Record the sequence, not just the message. Evidence distinguishes competing writers from the lack of free connections studied earlier. A problem record should also retain the affected operation, transaction settings and version so another team can reproduce the condition.
The same read can remain old
In the second sequence, A starts a read transaction and queries 30. B changes the value to 40 and commits. A still queries 30 while the third connection sees 40. When attempting to write, A receives SQLITE_BUSY_SNAPSHOT, code 517. Repeating UPDATE in the same transaction produces the same error. This observation avoids two unsupported corrections: increasing the pool and retrying the statement indefinitely. The script ends A’s transaction, starts another and rereads 40. The fictional intent is to add ten to the current value, so it commits 50. Reusing the earlier calculation of 30 plus ten would produce a different result. A decision approved against specific data may require renewed validation before any replay.
Early reservation also has a cost
The third sequence obtains the writer position before querying. While A retains an active BEGIN IMMEDIATE, B cannot start writing, although a read on another connection works. The strategy can avoid late read upgrade, but moves contention to the start and makes reservation duration relevant. In a fictional closing scenario, retaining that reservation during a slow external call can prevent other jobs from advancing. The technical PM should request the complete unit-of-work boundary, dependencies and observed durations. A larger busy timeout can absorb transient contention through additional waiting; it does not renew an old snapshot. The decision needs a time budget and functional outcome without turning the setting into a universal SLA.
Reproduction, hypothesis and limits
Before running, predict the value visible on each connection and where each write can fail. Compare your prediction with checks and trace in the JSON report. The trace retains statements, parameters, results and codes; the report identifies Python, SQLite and the script hash. Sequences are interleaved on one thread to control ordering, not to measure throughput. The lab explicitly sets LEGACY_TRANSACTION_CONTROL and isolation_level=None and uses explicit SQL transaction statements. That choice must accompany reproduction. Two matching executions support these local mechanisms; they are not a real-incident diagnosis, Oracle/JDBC exercise, network test, performance guarantee or human approval of a RUN procedure.
python3 content/labs/problem-transactions/run.py --output /tmp/dr-problem-transactions.json
# Controlled sequence, separate connections, confirmed WAL mode:
# A: BEGIN; SELECT value... -> 30
# B: BEGIN IMMEDIATE; UPDATE... SET value=40; COMMIT
# A: UPDATE... -> SQLITE_BUSY_SNAPSHOT (517)
# A: ROLLBACK; BEGIN IMMEDIATE; reread and reevaluate; COMMITA reads 30; B commits 40; A still reads 30 and receives 517 when attempting a write. Only a new transaction permits reevaluating the increment and committing 50.
Common pitfalls
Treating every lock as pool exhaustion, retrying over the same old read or reusing a value calculated before the competing change.
Related topics: Resources and cancellation · Atomicity and correction acceptance
Identify where the operation fails and which state it retains; renewing a transaction also requires reevaluating operation assumptions.
Reference: Transaction · Problem management practices 2026-09; ServiceNow Brazil examples with scoped plugins and properties