Separate valid rows from a cross-row rule
Two doctors A and B are on call, with a rule that at least one must remain. Each transaction checks that another doctor is available and then removes itself. Updating different rows avoids a same-row overwrite conflict but can still leave nobody on call. Follow reads and committed updates rather than relying only on anomaly names.
The disposable experiment opens two PostgreSQL connections and compares READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Level names do not establish identical implementations across every database and version. Relate the recorded18.6 behavior to its documentation. Also separate successful SQL completion from preservation of the business rule. Two individually successful updates can be the evidence of a failed cross-row invariant rather than evidence that the whole workflow was correct.
Align both reads before either decision commits
Begin both transactions and confirm that each reads an on-call count of two. Then A removes itself and commits; afterward B attempts its own removal and commit. Both initial reads must precede A's commit. If B reads later, the experiment is a different history. The driver waits for psql responses and explicit markers rather than using sleeps to guess overlap.
The updates target different rows, so this is not simply two writers waiting for one row lock. Each decision depends on the other's presence and changes another part of the predicate. Successful writing does not retroactively make an earlier read current. Record read results, changed rows and commit outcomes separately. A changed event ordering is a separate experiment, not automatically the same reproduction under another label.
Snapshot stability is weaker than serializability
A stable transaction snapshot does not automatically make the combined outcome equivalent to a sequential execution. Both repeatable-read transactions can observe the same initial state and modify separate rows. Repeating a read and seeing a stable value differs from preserving the cross-transaction dependency rule. Use the MVCC and write-skew foundations to identify which guarantee is needed.
Serializable execution may reject a transaction instead of allowing both incompatible decisions to succeed. Rejection can be the mechanism preserving correctness, not evidence that the database failed it. The application must classify retriable errors and rerun the entire decision transaction from a fresh read. Repeating only the final UPDATE can retain exactly the stale reasoning that made the first attempt invalid.
Execute all levels and inspect final state
The Python driver opens two psql subprocesses and uses explicit completion markers after each SQL stage. It resets a disposable table for each isolation level. In this history, read committed and repeatable read leave zero on call; serializable rejects one attempt and leaves one. Finally closes sessions and rolls back remaining work. The connection is confined to a dedicated local Unix socket, without production credentials.
An arbitrary exception is not accepted as a successful negative test. The driver checks for a serialization-failure message rather than treating syntax or connectivity errors as equivalent. Save engine version, complete output, exit status and exact source hash. Assertions also confirm both intended initial reads. This is a controlled comparison of one write-skew history, not an exhaustive implementation test for every isolation anomaly.
import os,subprocess
socket=os.environ.get('LAVACS_LAB_PGSOCKET','/var/run/postgresql')
if not socket.startswith('/'):raise ValueError('use a disposable local Unix socket')
base=['psql','-X','-q','-At','-v','ON_ERROR_STOP=1','-h',socket,'-d','postgres']
def once(sql):return subprocess.check_output(base+['-c',sql],text=True).strip()
class Session:
def __init__(self):self.p=subprocess.Popen(base,stdin=subprocess.PIPE,stdout=subprocess.PIPE,stderr=subprocess.PIPE,text=True,bufsize=1);self.number=0
def send(self,sql):
self.number+=1;mark='END_'+str(self.number);self.p.stdin.write(sql+'\n\\echo '+mark+'\n');self.p.stdin.flush();lines=[]
while True:
line=self.p.stdout.readline()
if not line:raise RuntimeError(self.p.stderr.read())
if line.strip()==mark:return '\n'.join(lines)
lines.append(line.rstrip())
def close(self):
if self.p.poll() is None:
try:self.p.communicate('ROLLBACK;\n\\q\n',timeout=5)
except subprocess.TimeoutExpired:self.p.kill();self.p.communicate()
for level in ['READ COMMITTED','REPEATABLE READ','SERIALIZABLE']:
once('DROP TABLE IF EXISTS phase4_doctors;CREATE TABLE phase4_doctors(name text PRIMARY KEY,on_call bool);INSERT INTO phase4_doctors VALUES(\'A\',true),(\'B\',true);')
a=Session();b=Session()
try:
for s in [a,b]:s.send('BEGIN ISOLATION LEVEL '+level+';');assert s.send('SELECT count(*) FROM phase4_doctors WHERE on_call;')=='2'
a.send("UPDATE phase4_doctors SET on_call=false WHERE name='A';COMMIT;")
aborted=False
try:b.send("UPDATE phase4_doctors SET on_call=false WHERE name='B';COMMIT;")
except RuntimeError as error:
assert 'could not serialize' in str(error);aborted=True
remaining=int(once('SELECT count(*) FROM phase4_doctors WHERE on_call;'))
assert (remaining,aborted)==((1,True) if level=='SERIALIZABLE' else (0,False))
print(level,'remaining=',remaining,'aborted=',aborted)
finally:a.close();b.close()
once('DROP TABLE phase4_doctors;')
| Isolation | Executed history result | On-duty count |
|---|---|---|
| READ COMMITTED | Both commit | 0 |
| REPEATABLE READ | Both commit | 0 |
| SERIALIZABLE | One serialization failure | 1 |
Reevaluate business decisions on retry
After B is rejected, a fresh transaction sees that A already left and B is now the only remaining doctor. Under the business rule, B should stay. Retrying is therefore not a promise to force the original UPDATE to succeed eventually. It means rereading and reevaluating whether the operation is still valid.
If a transaction emitted a notification or payment before failing, database rollback does not erase that external effect. Operation identity and outbox-style boundaries may be needed to prevent duplicate effects on retry. This experiment changes only synthetic database rows and contacts no real user or external payment service. Isolation and distributed exactly-once claims are separate. Define the whole-request retry boundary rather than only how to catch an error from the final SQL statement.
Compare locks and constraints as alternative designs
A design might serialize decisions through a shared lock or restructure an invariant around one constrained object. It must identify which competing operations the lock covers and in what order resources are acquired. Adding the phrase FOR UPDATE does not automatically protect every arbitrary predicate invariant.
Database uniqueness and referential constraints can enforce some rules more directly than application-only checks, but at-least-one across arbitrary rows is not the same as a one-column CHECK. Compare specifications, rejection categories, contention waits and retry behavior. The product must decide when and how a user receives a failure, beyond merely achieving a valid final state. This lesson supplies evidence from one controlled history rather than claiming to implement every policy of a complete scheduling application.
Name anomalies through the reads they contain
Reading one row twice around another committed update is different from two transactions changing separate rows and breaking a predicate. Changes in the rows matching a query are another distinct history. Associate an anomaly name with the exact reads, commits and violated rule rather than treating the label as a complete scenario.
An isolation-level table does not determine every application outcome. Explicit locks, constraints, SQL structure, engine implementation and version affect what happens. Preserve initial state and event order while changing one condition at a time. Do not transfer success for one history to a different history without another argument or execution. Keep the checked question paired with the observed answer.
Two reads can construct a total that never existed
Consider accounts A and B with one hundred each. A reader obtains A's one hundred. Another transaction then transfers ten from A to B and atomically commits ninety and one hundred ten. The reader subsequently obtains B's one hundred ten and reports two hundred ten. Both committed states total two hundred; the two reads do not describe one shared point in time.
PostgreSQL READ COMMITTED gives successive statements fresh snapshots, making this constructed history possible. It does not mean the transfer committed an intermediate combination of one hundred and one hundred ten. Separate the writer's atomic change from the reader's multi-statement view. Combining the aggregation into one statement or choosing a stable snapshot changes the observation boundary, with its own requirements.
This is a hand-traced history distinct from the executed duty-flag example. That program checks write skew, not this account total. A new execution would need explicit markers for the first read, transfer commit and second read. The final number alone cannot identify the anomaly; retain the statements and event order that produced it.
Exercise: choose the next action after rejection
After both read count two and A commits its departure, a serializable rejection of B calls for a fresh transaction and reread. Seeing B alone should change the business decision: do not permit B to leave. Blindly repeating the last UPDATE misses that distinction. Explain how rejection can preserve correctness rather than merely delay success.
If read committed leaves nobody on call, do not describe it as two writers overwriting the same row. The writes were disjoint; their decisions depended on the other's presence. A syntax-error interruption also cannot be counted as the expected serialization rejection. Verify both error category and final state. Combine history, isolation, retry scope and business rule in the answer rather than choosing a level name without explaining its consequence.