[Pgpool-general] Replication Flawed?

[Tatsuo Ishii]

> Is the replication from master to secondary not flawed simply by the fact statements between the two are not synchronised?  You could lock tables as you suggest but if the LOCK TABLE statement is not guaranteed to execute on the secondary server in the same order (of all transactions) as the first, then a different transaction could potentially commit (before LOCK TABLE) on either server which would still result in inconsistent DBs.

LOCK TABLE on the seconrdary will be executed in the same order as
master. 

Suppose there are two transactions A and B. On master A locks a
table. B cannot lock it since A already holds the lock. Then A locks
it on the secondary. B will not lock it on the secondary because B has
to wait for aquiring lock on the master.

> I have run the same tests (and passed) using a JDBC based replication system, Sequoia (sequoia.continuent.org), which ensures replication consistency by ordering statements from all transactions based on ids assigned to them AFAIK.

Once I thought of such that method but I concluded that it will result
in performance loss because assigning "id" to each transactions itself
needs to kill concurrency of transactions.

If Sequoia overcomes the problem I worry about in some way, it will be
interesting. Let me check it.
--
Tatsuo Ishii
SRA OSS, Inc. Japan