Thread: When is newly inserted data visible to another connection?
Hi all, If there is nothing else wrong in our test case we noticed the following: We have done a test with two connections to the database on different computers. After the first client (writer) had inserted new data into a quite simple table, it told another client (by TCP communication) to be ready, however, this second client (reader) did not see the data then immediately in the database. So we delayed the reading client from 2 to 5s to have it see all data in the table. So, firstly: Is that a possible scenario from postgre's view (and do we have to address it here) ? Secondly: If yes, is there a way to determine when newly inserted data is visible to other clients? Remark: In case transacted write access keeps the inserted lines together and therefore make it either visible in whole or nothing of it -- this would not be a solution for the question *when* it appears visible to other clients. Thank You Felix
fkater@googlemail.com wrote: > Hi all, > > If there is nothing else wrong in our test case we noticed > the following: > > We have done a test with two connections to the database on > different computers. After the first client (writer) had > inserted new data into a quite simple table, it told another > client (by TCP communication) to be ready, however, this > second client (reader) did not see the data then immediately > in the database. So we delayed the reading client from 2 to > 5s to have it see all data in the table. > Essential information is missing. Did the first client COMMIT before toggling client 2? Also you might find the information from http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html interesting, since that specifies how to control behaviour of concurrent transactions looking at each others data. > Secondly: If yes, is there a way to determine when newly > inserted data is visible to other clients? > Not before it is committed. To which clients the just committed data is visible depends on the transaction isolation level (see link above). regards, Yeb Havinga
Yeb Havinga: > fkater@googlemail.com wrote: [...] > > We have done a test with two connections to the database > > on different computers. After the first client (writer) > > had inserted new data into a quite simple table, it told > > another client (by TCP communication) to be ready, > > however, this second client (reader) did not see the > > data then immediately in the database. So we delayed the > > reading client from 2 to 5s to have it see all data in > > the table. > Essential information is missing. Did the first client > COMMIT before toggling client 2? Yes, of course, the commit was done before toggling client 2. I would like to mention that the table itself is simple however contains a bytea column and some of the inserted rows contain some MBs of binary data which usually take a while. But, yes, we trigger client 2 only *after* the commit was done and returned successfully (using v8.2.4 on win32 via libpq). > Also you might find the information from > http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html > interesting, since that specifies how to control behaviour > of concurrent transactions looking at each others data. Thank you for the interesting link. I think, though, that this does not address the question why there is a delay between the point in time A that client 1 has successfully commited and the point in time B when client 2 can see all new rows! Even in pure serialization it should be possible that client 2 can immediately start reading *after* client 1 has completely commited, shouldn't it? FYI: We are using the default setup for transaction isolation. > > Secondly: If yes, is there a way to determine when newly > > inserted data is visible to other clients? > > > Not before it is committed. To which clients the just > committed data is visible depends on the transaction > isolation level (see link above). Hm, I do not get it -- AFAIK the article you mentioned deals with the question what a concurrent transaction can see from another one which is pending/not committed. But this is not the case here. The first transaction is commited before. To sum up our question: If client 1 has commited some rows, when is the moment that client 2 can see/read all that data? Do we have to consider a gap and if yes how to determine it? Thank You! Felix
On Aug 18, 2010, at 6:57 AM, fkater@googlemail.com wrote: > Even in pure serialization it should be possible > that client 2 can immediately start reading *after* client 1 > has completely commited, shouldn't it? Unless client 2 had previously started a transaction and is reading from that. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
"fkater@googlemail.com" <fkater@googlemail.com> writes:
> Thank you for the interesting link. I think, though, that
> this does not address the question why there is a delay
> between the point in time A that client 1 has successfully
> commited and the point in time B when client 2 can see all
> new rows!
There is no such delay. Either you forgot to commit in client 1,
or client 2 had a query snapshot that was taken before client 1
committed.
regards, tom lane