Problem 1 - Olympiad

A university uses a relational schema where STUDENT(StudentID PK, Name, AdvisorID FK) references ADVISOR(AdvisorID PK, Name). A trigger enforces that every student must have an advisor whose ID exists in ADVISOR. The DBA deletes an advisor row without cascading. Which combination of transaction isolation level and lock type on ADVISOR would guarantee the trigger never sees a phantom advisor deletion, and what is the earliest instant the trigger’s check can be safely executed?

A. SERIALIZABLE with range-lock; after the advisor row is X-locked but before DELETE is committedB. REPEATABLE READ with shared row-lock; after the advisor row is S-locked and DELETE commitsC. SERIALIZABLE with range-lock; after the advisor row is X-locked and DELETE commitsD. REPEATABLE READ with intent shared table-lock; before the advisor row is locked

Correct: A

Under SERIALIZABLE the DBMS acquires a range (next-key) lock on the predicate AdvisorID=?, preventing concurrent inserts or deletes that could create phantoms. The trigger’s referential check must run after the advisor row is X-locked (so the decision to delete is irrevocable inside the transaction) but before the DELETE commits (so the trigger can still abort the transaction if a student references the advisor).