> Tatsuo Ishii wrote: > >>> Can that situation occurs also with PgPool replication like: > >>> PgPool sends update of A in M . > >>> First request on A goes on M. > >>> Second request on A goes to S. > >>> PgPool sends update to S. > >>> > > > > This could happen too, though the window is very narrow comparing with > > Slony-I. As of pgpool-II 2.0, any query is executed as an explicit > > transaction. Let's say you have a master and two slaves and you send an > > update query to pgpool-II. > > > > > Thanks, it was almost a theoretical question - but if it can happen, it > will happen in Production the day you are not expecting it ;) > > Also note that in pgpool, if you send an update then a select to > > retrieve the result in a *same session*, it's guaranteed that the > > select returns the result of update since the update and select will > > be executed on a same PostgreSQL node even if the load balanace option > > is enabled. > > > I had noticed that. > > Does the lock mechanism change anything to my question ? > > In fact I made the following tests with and without the lock: > > * pgpool distributing the load on 3 machines A,B,C defined in that order > in the list. > * I create a table with an id as primary key and a string column. > *Through several psql connections via pgpool I update the DB and add > some rows. > * Then I add manually in B server a line with id let's say 5. When then > I reconnect the psql client and try to add a row with id 5, it fails as > expected and detecting data inconsistency, it removes A. > * When I do not set any lock, I get in fact A with the new row with > id=5, the second one with previous row and C with no row. > * When I use the lock A and C remain consistent with no new row while B > has its previous row. > > My idea is to keep the DB consistency among the three databases (a kind > of two-phase commit). And I just added that row to check the behavior on > the three database when an error might occur on a given DB. > It is like with no-lock I can't guarantee the DB consistency and that > with lock I can. > Am I right ? > And with the lock, can the update be "accessed" on all the tables only > after all the DB have comited their update ? > > Thanks for your answer and also thank you for PgPool which is a very > nice tool.
I think you could avoid the window by using a strong lock. BEGIN; LOCK t1; UPDATE t1; SELECT... COMMIT; With this, any transaction will not see the effect of UPDATE until all servers commit the transaction. Of course this will sacrifice concurrency though. BTW I don't think 2PC helps you in this regard. -- Tatsuo Ishii SRA OSS, Inc. Japan _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
