Hi again,
>> All statements in a transaction which is READ COMMITTED sees the data
>> as of CLOCK_TIMESTAMP() and all statements in a transaction that is
>> SERIALIZABLE see the data as of TRANSACTION_TIMESTAMP().
> No. The isolation level is immaterial to which times are returned. I welcome being proven wrong but nothing I’ve
readin that link suggests otherwise so I’m disinclined to prove it correct.
(maybe I expressed myself poorly previously)
I didn't say that the times returned would vary depending on the
isolation level - I don't see why they would. Or, at least, that's not
what I meant to say!
Take this scenario.
START TRANSACTION ; -- either TA (READ COMMITTED - RC) or TB (SERIALIZABLE - S)
SELECT TRANSACTION_TIMESTAMP AS tt_1, CLOCK_TIMESTAMP AS ct_1; --
tt_1 and ct_1 are (almost) identical, give or take a few microseconds
- i.e. the transaction start time
SELECT * FROM x; -- s1 @t1
-- TA and TB perform some long-running DW query.. the actual length of
time isn't important - what's important is what happens in the
meantime - could be < 0.1s...
--
-- In the meantime, x gets modified by a totally different transaction
by a totally different user - user_x...
--
SELECT TRANSACTION_TIMESTAMP AS tt_2, CLOCK_TIMESTAMP AS ct_2; --
now, there is a big difference between tt_2 and ct_2 for both TA and
TB. However, tt_1 and tt_2 are identical, again for both transactions
- fixed at the start of either TA or TB.
SELECT * FROM x; -- s2 @t2
COMMIT;
For TA (RC), the result of s1 will be the state of x at time = t1 (~ =
tt_1 and ~ = ct_1) the result of s2 will be the state of x at time =
ct_2 and there will be a difference due to the user_x's modfications.
For TB (S), the result of s1 @t1 will be the same as the result of s2
@t2 because of SERIALIZE-ability.
I hope that clears up what I meant - and also that what I have written
above demonstrates a reasonable understanding of the difference
between the potential outcomes of an RC transaction and an S one?
Thanks again for your input - I think that putting all of that down on
paper (well, screen...) has helped crystallise the concepts for me
(any corrections, addenda... appreciated - any references to useful
URLs in this respect likewise).
Pól...
> David J.