Re: Batch replication ordering (was Re: [GENERAL] 32/64-bit transaction IDs?)

On Friday April 11 2003 4:44, elein wrote:
> If the items going to the slave are ordered and complete transactions,
> does it matter whether or not they are applied as complete transactions
> on the slave in groups or one at a time?

I don't think it really matters as long as the order is correct and the
commits occur on the transactional boundaries.

> What I'm not sure about is the overlapping transactions where the
> second completes before the first.
>
> begin (xact 1)
> select ... for update ...
> begin (xact 2)
> update ...
> end (xact 2)
>
> end (xact 1)
>
> In this case, on the slave, xact2 will be commited before xact 1 is
> begun. This seems OK to me, but I'd like some confirmation to understand
> this. Or is this the wrong question?

IMO, this is exactly what makes the analysis a little tricky for people like
me who don't regularly live in the world of transaction minutiae.

If xact2 tries to update a row already updated by xact1, xact2 will block
until xact1 commits or rolls back, and cannot end before xact1 due to MVCC.
If xact1 tries to update a row already updated by xact2, xact1 will block
until xact2 is resolved.

My question is, given all the ways in which these orderings can occur, which
I think simplifies to about 3 in the case of 2 transactions updating the
same row, is there a fool-proof ordering for replaying in batches?

Ed