[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    questions about snapshot building logic
From:       Вадим Самохин <samokhinvadim () gmail ! com>
Date:       2022-08-29 11:29:33
Message-ID: CAGVmuwomSpZcB-=fufk=c-OEUZM5qOURv=VpZ1JYanVz1V8Ruw () mail ! gmail ! com
[Download RAW message or body]

I've read some posts about how snapshots are built (here
<https://www.interdb.jp/pg/pgsql05.html>, here
<https://brandur.org/postgres-atomicity>, and here
<https://postgrespro.com/blog/pgsql/5967899>) and I thought that I
understood what's going on -- until I tried that in practice. My postgres
version is 11.6.

So, I start a first session:
*session 1*:
begin isolation level repeatable read;
update restaurant set address = '1' where id = 1;
select txid_current(); -- it is 1402
select txid_current_snapshot(); -- 1402:1402:

*Question 1*: why is xmax equal to xmin? Isn't xmax the id of a
not-yet-started transaction, that is 1402 + 1 = 1403?

after that, I start a second session:

*session 2*:
begin isolation level repeatable read;
update restaurant set address = '2' where id = 2;
select txid_current(); -- 1403
select txid_current_snapshot(); -- 1402:1402:

*Question 2*: how is it possible that xmax is less than a current
transaction id? One can assume from session 1 that xmax = current
transaction id, so why xmax != 1403? And why is 1402 absent from a xip list?

*session 3:*
begin isolation level repeatable read;
update restaurant set address = '3' where id = 3;
select txid_current(); -- 1404
select txid_current_snapshot(); -- 1402:1402: -- all the same
rollback;

After then I commit the second transaction:

*session 2:*
commit;

... and start the fourth transaction:

*session 4:*
begin isolation level repeatable read;
update restaurant set address = '4' where id = 4;
select txid_current(); -- 1405
select txid_current_snapshot(); -- 1402:1405:1402

*Question 3*: why did a snapshot change so much? xmax is now 1405, and the
first transaction is finally in a xip list!

[Attachment #3 (text/html)]

<div dir="ltr"><div>I&#39;ve read some posts about how snapshots are built (<a \
href="https://www.interdb.jp/pg/pgsql05.html">here</a>, <a \
href="https://brandur.org/postgres-atomicity">here</a>, and <a \
href="https://postgrespro.com/blog/pgsql/5967899">here</a>) and I thought that I \
understood what&#39;s going on -- until I tried that in practice. My postgres version \
is 11.6.<br></div><div><br></div><div>So, I start a first \
session:<b><br></b></div><div><b>session 1</b>:</div><div>begin isolation level \
repeatable read;</div><div>update restaurant set address = &#39;1&#39; where id = \
1;</div><div>select txid_current(); -- it is 1402</div><div>select \
txid_current_snapshot(); -- 1402:1402:</div><div><br></div><div><i>Question 1</i>: \
why is xmax equal to xmin? Isn&#39;t xmax the id of a not-yet-started transaction, \
that is 1402 + 1 = 1403?<br></div><div><br></div><div>after that, I start a second \
session:<br></div><div><br></div><div><div><b>session 2</b>:</div><div><div>begin \
isolation level repeatable read;</div><div>update restaurant set address = \
&#39;2&#39; where id = 2;</div><div>select txid_current(); -- 1403</div><div>select \
txid_current_snapshot(); -- 1402:1402:<br><div><br></div><div><i>Question 2</i>: how \
is it possible that xmax is less than a current transaction id? One can assume from \
session 1 that xmax = current transaction id, so why xmax != 1403? And why is 1402 \
absent from a xip list?<br></div><div><br></div><div><b>session \
3:</b></div><div><div>begin isolation level repeatable read;</div><div>update \
restaurant set address = &#39;3&#39; where id = 3;</div><div>select txid_current(); \
-- 1404</div><div>select txid_current_snapshot(); -- 1402:1402: -- all the \
same</div><div>rollback;</div><div><br></div></div><div></div><div>After then I \
commit the second transaction:</div><div><br></div><div><b>session \
2:</b></div><div>commit;</div><div><br></div><div>... and start the fourth \
transaction:</div><div><br></div><div><b>session 4:</b></div><div><div><div>begin \
isolation level repeatable read;</div><div>update restaurant set address = \
&#39;4&#39; where id = 4;</div><div>select txid_current(); -- 1405</div><div>select \
txid_current_snapshot(); -- 1402:1405:1402</div><div><br></div><div><div><i>Question \
3</i>: why did a snapshot change so much? xmax is now 1405, and the first transaction \
is finally in a xip list!<br></div></div></div></div></div></div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]