Both Doctors Checked That Someone Else Was on Call
Last timeDoing It With Snapshots
Two transactions, two different rows, both reading a rule that spans several rows and each one breaking it on the strength of the other holding it up.
The previous lesson ended with one surviving gap, and it is specific enough to
construct on purpose. It has a name, write skew, and once you have seen it once
you will recognise the shape in your own code.
The example to carry
A hospital rota. The rule is that at least one doctor must be on call at all
times. Two doctors are on call and both feel unwell at the same moment.
| step | step | doctor Ayers transaction | doctor Bell transaction | doctors on call | what happened |
|---|---|---|---|---|---|
| 1 | 1 | takes a snapshot | takes a snapshot | 2 | Both transactions begin within the same second. Each records the state of the database as it is now, which is two doctors on call. |
| 2 | 2 | counts doctors on call: 2 | counts doctors on call: 2 | 2 | Both counts are correct. Both read the same committed state because both snapshots were taken before either wrote anything. |
| 3 | 3 | 2 is more than 1, so proceeding | 2 is more than 1, so proceeding | 2 | Both checks pass. The logic is right: it is safe to go off call as long as somebody else is on call, and somebody else is. |
| 4 | 4 | writes its own row to off call | waiting its turn | 2 | A write to the Ayers row. No other transaction has written that row, so there is no version conflict and nothing to report. |
| 5 | 5 | committed | writes its own row to off call | 1 | A write to the Bell row. A different row. Still no version conflict, because the detection compares versions of one row and these are two rows. |
| 6 | 6 | done | committed | 0 | Both committed, both individually correct, and nobody is on call. No error was raised anywhere and nothing in any log says what went wrong. |
Three ingredients are needed and all three are necessary. Both transactions
read an overlapping set of rows. Both check a rule over that set. And each
writes a different row.
The lesson stops here
5 more paragraphs to go
You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.
The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.
See the planThe contentsThis is the reading half
Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.
The contents