Re: Transaction ISOLATION LEVEL - have I missed something?

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.