Transactions & Advanced — Series 3

Preview — 3 of 10 questions

Which isolation level prevents this, and why is the weaker one insufficient?

javascript

Rule: at least one doctor must remain on call.
Two transactions each read "2 doctors on call", each concludes it is safe,
and each takes its own doctor off call.

AREAD COMMITTED, since each transaction sees only committed data

BNone; the rule can only be enforced with a unique constraint

CSERIALIZABLE. Under REPEATABLE READ each transaction has a consistent snapshot and neither modifies what the other read, so no conflict is detected — yet the combined result violates an invariant that neither transaction broke on its own; only serialisable semantics reject one of them

DREPEATABLE READ, which already prevents phantoms and therefore this

Why use an advisory lock for a scheduled job across replicas?

javascript

SELECT pg_try_advisory_lock(hashtext('nightly-report'));

AEvery replica's scheduler fires at the same time, so the job needs mutual exclusion: pg_try_advisory_lock returns immediately with true for exactly one caller and false for the rest, and the lock is released automatically when that session ends — so a crashed instance does not leave the job blocked forever

BAdvisory locks make the job run faster by avoiding row locks

CThey guarantee the job runs exactly once even across database restarts

DThey are required because cron handlers cannot use transactions

How does NOWAIT differ from the default and from SKIP LOCKED?

javascript

SELECT * FROM jobs WHERE id = $1 FOR UPDATE NOWAIT;

ANOWAIT retries internally, while the default blocks

BNOWAIT acquires a weaker lock that other writers can share

CThe three are interchangeable; only the error message differs

DThe default waits until the lock is free; NOWAIT raises an error immediately if it is not; SKIP LOCKED silently omits the locked rows from the result — so the choice expresses whether contention should block, fail loudly, or be worked around

Answer all 10 questions (7 more), see explanations for every answer, and track your score.