From: | Pól Ua Laoínecháin <linehanp(at)tcd(dot)ie> |
---|---|
To: | |
Cc: | "pgsql-novice(at)lists(dot)postgresql(dot)org" <pgsql-novice(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Transaction ISOLATION LEVEL - have I missed something? |
Date: | 2021-05-13 20:31:02 |
Message-ID: | CAF4RT5RTZT1CX818hFdVd=jKpSorz1rMp83ObUbVGZn2wqoqqg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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 read in 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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-05-13 21:02:31 | Re: Transaction ISOLATION LEVEL - have I missed something? |
Previous Message | David G. Johnston | 2021-05-13 18:16:39 | Re: Transaction ISOLATION LEVEL - have I missed something? |