Part 3 of 5 · 1 min read
Detecting the pattern without plain locking
Tracking which rows each transaction read and wrote lets the database spot a dangerous structure without blocking reads or writes upfront.
Think of a referee's assistant simply keeping a running list of which plays each referee reviewed and ruled on. The assistant checks that list for a suspicious pattern afterward, rather than stopping every single review in progress to compare notes in real time.
In plain words: rather than acquiring locks upfront the way two-phase locking does, serializable snapshot isolation lets transactions read and write freely, exactly as plain snapshot isolation does, while separately tracking which rows each transaction has read and which rows it has written. This tracking lets the database recognize the specific read-write and write-read relationships between concurrent transactions that form a dangerous structure, without ever making a transaction wait for a lock during its normal execution. The check happens as a side effect of this bookkeeping, not by intercepting every single read and write with a lock request.
A worked example. As transaction A reads the doctors row later written by transaction B, and transaction B reads the row later written by transaction A, the database's tracking records both relationships, recognizing the dangerous structure once both have occurred.
The key lines. No new SQL; this tracking is internal bookkeeping the storage engine performs alongside ordinary MVCC reads and writes.
Read-write tracking
Read-write tracking records which rows each transaction has read and written, letting the database recognize a dangerous structure forming between concurrent transactions without acquiring locks upfront.
Example: a referee's assistant keeping a running list of reviews, checked afterward for a suspicious pattern, mirrors read-write tracking's after-the-fact detection.