I feel kind of stupid asking this question, but to clear things out, sometimes stupid questions must be asked :)
So, we can define a write skew as Martin Kleppmann did in his talk:
Write skew pattern:
1. read something
2. make a decision
3. write decision
By the time the write(3) is committed, the premise(1) of the decision(2) is no longer true
There is a pessimistic approach, when we basically say "only one subject can use shared resource in a given moment, others should wait before the subject finishes".
And then there is an optimistic approach, with phases as defined in Wikipedia:
I. Begin: Record a timestamp marking the transaction's beginning.
II. Modify: Read database values, and tentatively write changes.
III. Validate: Check whether other transactions have modified data that this transaction has used (read or written). This includes transactions that completed after this transaction's start time, and optionally, transactions that are still active at validation time.
IV. Commit/Rollback: If there is no conflict, make all changes take effect. If there is a conflict, resolve it, typically by aborting the transaction, although other resolution schemes are possible.
My question is, what guarantees do we have that new "knowledge" is not being written while validation (III) is happening, thus fulfilling the definition of write skew given above?
Basically, that validation module on phase III must keep some inner ledger and process them in serial way, so that checking process from transaction2 doesn't happen before transaction1 write event.
Have we just moved this whole problem of write skew one level down? So we have a serializable pessimistic approach on low level to be able to have an optimistic approach on a higher level? Am I getting something wrong?
I'd appreciate any clarifications.
For optimistic locking to work 'III. Validate' and 'IV. Commit/Rollback' need to be a single atomic operation. So in this sense, yes "we just moved this whole problem of write skew one level down".
However, 'II. Modify' is a user operation outside of the control of the database, which can take a long time to complete and cannot be optimized by the database implementation. 'III. Validate' and 'IV. Commit/Rollback' OTOH are operations implemented by the database, which can be optimized to be fast by the database implementation.