it is done using now()....
But what I don't understand is how the transaction that started first
could 'see' the record that hadn't been changed yet by the initial
update to 'COMPLETE'?
I thought:
"Each transaction sees a snapshot (database version) as of its
starttime, no matter what other transactions are doing while it runs"
How is the "when" column determined? You did not show it in your SQL
commands.
If it's being driven off now() or CURRENT_TIMESTAMP, then the above
isn't all that surprising, because the value is the time of transaction
start not the time at which the update was made.
