Message-ID: <7901c7e4-69c6-4bb2-8e9d-ad556d493717@iseki.space>
Date: Wed, 9 Oct 2024 18:07:09 +0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
To: pgsql-general@lists.postgresql.org
From: iseki zero <admin@iseki.space>
Subject: Questions about document "Concurrenry control" section
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/7901c7e4-69c6-4bb2-8e9d-ad556d493717%40iseki.space>
Precedence: bulk

Hello,

I found it difficult to understanding some paragraph in the document 
"Concurrency control" section.

 > The Repeatable Read mode provides a rigorous guarantee that each 
transaction sees a completely stable view of the database. However, this 
view will not necessarily always be consistent with some serial (one at 
a time) execution of concurrent transactions of the same level. For 
example, even a read-only transaction at this level may see a control 
record updated to show that a batch has been completed but/not/see one 
of the detail records which is logically part of the batch because it 
read an earlier revision of the control record. Attempts to enforce 
business rules by transactions running at this isolation level are not 
likely to work correctly without careful use of explicit locks to block 
conflicting transactions.

At: 
https://www.postgresql.org/docs/17/transaction-iso.html#XACT-REPEATABLE-READ:~:text=The%20Repeatable%20Read%20mode,to%20block%20conflicting%20transactions.

Specifically, I can't understand the example. Why in an earlier 
revision, the control record show that the batch has been completed? In 
my recognization, the control record state transation should be 
"running" -> "completed". And only after all batch operation completed 
then the control record will be changed to "completed".

The another big problem is, I interpret the whole batch operation is in 
one transaction. So, we can read updates from another uncommited yet 
transaction?? I read the front paragraph(Read Committed), the locking 
behaviour will effect the read view, and I understand the behaviour 
might still be exists in Repeatable Read. But the example said, 
read-only transaction.

Thank you

iseki zero