Re: Batch replication ordering (was Re: [GENERAL] 32/64-bit

On Sat, 12 Apr 2003, Steven Singer wrote:

> > Does it matter if transactions that do not affect each other are committed
> > on the slave in a different order than they were on the master? I don't
> > think that's guaranteed by the above (unless the inserts into
> > replication_queue were deferred to transaction end), but I'm also
> > uncertain if it's a constraint you're concerned with.
>
>
> Well it all depends on what you mean by "Does not accect each other". Do
> 2 transactions not affect each other if they touch different rows,tables,
> or deal with data that is unrelated from the point of view of an
> application query the database.

Touches different rows. The database doesn't presumably know anything
about the application beyond what you tell it in function/view/table
definitions.

> Consider the following transactions on the master
> T1: Insert DeptId=1 INTO Department
> T2: Insert EmpId=1,DeptId=1 INTO Employee
>
> Those 2 transactions update different tables but if T2 is sent to slaves
> before T2 then A) You might have foreign key violations and B) Even if you
> don't have foreign keys someone querying the slave will see a database
> that never existed(at any point in time) on the master.

The latter was why I'd asked because it'd definately come up.

At least with deferred FK constraints you're right that it'd definately be
possible to believe it was safe to commit T2 without T1 with max(seqid)
ordering even though it's not unless reading the row to prevent updates
was considered something that needed a sequence id.

I haven't come up with a case that succeeds on the master but orders
incorrectly for immediate constraints if the insert into queue trigger is
after FK, but I haven't tried that hard. Assuming T1 is still open after
T2 attempts the insert, T2 errors (it can't see the matching row). If T1
is committed then T2 will by necessity have a higher max(seq_id). I'd
think for cases where T1 and T2 update the same row in a replicated table,
one waits for the other and if the insert into queue trigger runs after
the wait then the waiting one gets a higher max(seqid) which should
represent a "reasonable" ordering.