[Pgpool-general] Replication Flawed?

[Jim C. Nasby]

On Thu, May 18, 2006 at 12:59:06AM +0900, Tatsuo Ishii wrote:
> > 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.

Sequoia has the notion of statement queues; read-only statements can run
in any queue, and be serviced by any database, but DML must run in a
single queue and be serviced in order.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby at pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461