Transaction completion timing

[Steve Dodd]

Say we have two transactions run sequentially: T1 writes some data, and T2 reads the written data. There is a non-zero time delay between the apparent T1 commit, and the subsequent T2 query.


Is there any guarantee that the data written in T1 will be visible to the query in T2?


We have a situation in our system where it would appear that the answer is NO.


Based on my limited understanding of PostgreSQL internals, although T1 has apparently been committed (from the client’s perspective), that doesn’t necessarily mean that it has been completed inside the database. It seems that a commit only means that the transaction has been added to the WAL, but it doesn’t mean the transaction results are available to subsequent transactions. At some later date, the database completes T1, and then makes it visible to future transactions. Only after that point would the data be visible to T2. So if T2 runs too soon after T1, T1’s writes won’t be visible to T1.


Can someone clarify this for me?


Thanks so much,
Steve
--
This e-mail message is intended for the named recipient(s) above, and may
contain information that is privileged, confidential and/or exempt from
disclosure under applicable law. If you have received this message in
error, or are not the named recipient(s), please do not read the content.
Instead, immediately notify the sender and delete this e-mail message. Any
unauthorized use, disclosure or distribution is strictly prohibited.
Quantify Labs Inc and the sender assume no responsibility for any errors,
omissions, or readers' misinterpretations and/ or understanding of the
content or transmission of this email.

[Tom Lane]

Post by Steve Dodd
Say we have two transactions run sequentially: T1 writes some data, and T2 reads the written data. There is a non-zero time delay between the apparent T1 commit, and the subsequent T2 query.
Is there any guarantee that the data written in T1 will be visible to the query in T2?
We have a situation in our system where it would appear that the answer is NO.

Extremely hard to believe. There is a very well-defined point where the
transaction's effects become visible to other transactions, and that is
certainly before the commit is reported as complete to the client.
I suspect if you dig into it you'll find that your client-side code
is doing something unexpected.

Also worth checking is that "T2" is actually a transaction, and not
a single statement within a serializable-mode transaction. In the latter
case it would see the database state as of the transaction's snapshot,
which might precede T1's commit.

regards, tom lane

[Steve Dodd]

You could very well be right.




We are using JPA under Hibernate, using container managed transactions. So T1 and T2 above are actually container managed transactions, each running in response to REST API requests. They should be bound 1:1 with underlying PostgreSQL transactions, but perhaps there is some asynchronous going on inside of Hibernate





It’s off topic for this forum, but if anyone happens to know if JPA/Hibernate could be the culprit here, please let me know.

Post by Tom Lane

Post by Steve Dodd
Say we have two transactions run sequentially: T1 writes some data, and T2 reads the written data. There is a non-zero time delay between the apparent T1 commit, and the subsequent T2 query.
Is there any guarantee that the data written in T1 will be visible to the query in T2?
We have a situation in our system where it would appear that the answer is NO.

Extremely hard to believe. There is a very well-defined point where the
transaction's effects become visible to other transactions, and that is
certainly before the commit is reported as complete to the client.
I suspect if you dig into it you'll find that your client-side code
is doing something unexpected.
Also worth checking is that "T2" is actually a transaction, and not
a single statement within a serializable-mode transaction. In the latter
case it would see the database state as of the transaction's snapshot,
which might precede T1's commit.
regards, tom lane

--
This e-mail message is intended for the named recipient(s) above, and may
contain information that is privileged, confidential and/or exempt from
disclosure under applicable law. If you have received this message in
error, or are not the named recipient(s), please do not read the content.
Instead, immediately notify the sender and delete this e-mail message. Any
unauthorized use, disclosure or distribution is strictly prohibited.
Quantify Labs Inc and the sender assume no responsibility for any errors,
omissions, or readers' misinterpretations and/ or understanding of the
content or transmission of this email.