Optimism at the end, and locking the right size of thing
Validation-based protocols bet that conflicts are rare and only check at the very end, before committing. Multiple granularity lets a transaction lock an entire table or a single row, choosing the right size for the job instead of one-size-fits-all.
After this lesson
You should be able to
- Explain the three phases of a validation-based protocol and when the check actually happens.
- Explain what multiple granularity locking solves that a single fixed lock size cannot.
Validation: work freely, then check your work at the last moment
A validation-based (optimistic) protocol bets that most transactions will not actually conflict, so it avoids the cost of locking or timestamp bookkeeping during normal execution entirely. Each transaction runs in three phases: a read phase, where it reads data and computes all its updates into a private, local workspace without touching the real database yet.
Next comes the validation phase, where the DBMS checks whether this transaction's reads and writes conflict with any other transaction that validated in the meantime — this check is the only moment locking or timestamp systems would have paid a cost continuously for. If validation passes, the write phase applies the transaction's local changes to the real database and commits; if it fails, the transaction is rolled back and restarted, exactly like a failed timestamp check in the previous lesson.
One size does not fit every transaction
Locking one row at a time is efficient for a transaction touching one student's record, but a report that reads every row of a million-row table would need a million separate row locks — enormous overhead for something that logically wants to lock "the whole table, briefly, for reading." Locking the entire table for a single-row update, in the opposite direction, needlessly blocks every other transaction touching any unrelated row in that same table.
Multiple granularity locking arranges data in a hierarchy — database, then table, then page, then row — and lets a transaction choose the level that actually matches its need: a row-level lock for a single update, or a table-level lock for a full scan. Intention locks (marking "a finer-grained lock exists somewhere below this level") let the DBMS quickly detect conflicts across different granularities without checking every single row individually, combining the previous lesson's locking discipline with the right unit size for each job.
Try it yourself
A nightly report scans an entire 500,000-row Student table read-only, while several small transactions update individual student rows throughout the night. Which granularity should the report request, and why?
Need a hint?
The report touches every row, so row-level locking would mean acquiring 500,000 locks for one logical operation — but it is read-only, which matters for what kind of lock it needs at the table level.
Check the worked solution
The report should request a table-level shared lock, since it only reads. A shared lock at the table level still permits other transactions to acquire row-level locks on individual rows they need to update, thanks to the intention-lock mechanism — the report gets the efficiency of one lock instead of 500,000, and the small update transactions are not needlessly blocked from unrelated rows.
Quick check
In a validation-based protocol, why does the DBMS not need to acquire any locks during a transaction's read phase?
Why this lesson exists
Syllabus mapping
Lock Based Protocols, Timestamp Based Protocols, Validation- Based Protocols, Multiple Granularity
Maps to course outcome CO3.