SQL Anywhere database replication - Transaction replication problem

Author

Transaction replication problem

Russell J Morgan

2005-03-30, 9:41 am

Dbeng7 -v ASA704 ebf3526 dbremote transaction based replication

Hi All,

Take the following scenario:

Two tables replicating with a one subscriber, the foreign table is joined to
the primary table by a foreign key. Before replication takes place, at the
consolidated database the master record and all its 10 child records are
deleted. At the remote site, one more record is added to the foreign table.
When replication takes place at both ends, the transactions from the
consolidated fail to delete the primary record because of the 11th record
added to foreign table at the subscriber is not referenced in the delete
transaction block and the foreign key rules do not allow orphan records. The
11th foreign record from the subscriber fails to be inserted at the
consolidated because there is no primary record.

Slowly but surely the databases are drifting away from being synchronised
even with minimum latency being in seconds.

The only way I know how to deal with this problem is to add cascading
foreign keys with delete cascade option supplied. Is this the only way to
prevent inconsistency across by replicating environment?

Thanks in advance

Russell J Morgan

Reg Domaratzki $iAnywhere Solutions$

2005-03-31, 8:02 pm

Cascading deletes is the easiest way to handle this. Another more
complicated option is to define "logical deletes", where rows aren't really
deleted, but just marked are deleted with an extra column in the table. As
far as your application is concerned, the row is deleted, since it will
ignore rows that have been "logically" deleted. This is very much a design
decision that is made early on though, and isn't something that's trivial to
implement once deployed.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Russell J Morgan" <russell.morgan@careervision.co.uk> wrote in message
news:424ac8d5$1@foru
ms-1-dub...
> Dbeng7 -v ASA704 ebf3526 dbremote transaction based replication
>
>
>
> Hi All,
>
>
>
> Take the following scenario:
>
>
>
> Two tables replicating with a one subscriber, the foreign table is joined
to
> the primary table by a foreign key. Before replication takes place, at the
> consolidated database the master record and all its 10 child records are
> deleted. At the remote site, one more record is added to the foreign
table.
> When replication takes place at both ends, the transactions from the
> consolidated fail to delete the primary record because of the 11th record
> added to foreign table at the subscriber is not referenced in the delete
> transaction block and the foreign key rules do not allow orphan records.
The
> 11th foreign record from the subscriber fails to be inserted at the
> consolidated because there is no primary record.
>
>
>
> Slowly but surely the databases are drifting away from being synchronised
> even with minimum latency being in seconds.
>
>
>
> The only way I know how to deal with this problem is to add cascading
> foreign keys with delete cascade option supplied. Is this the only way to
> prevent inconsistency across by replicating environment?
>
>
>
> Thanks in advance
>
>
>
> Russell J Morgan
>
>

Russell J Morgan

2005-04-14, 9:23 am

Sure, Thanks Reg

"Reg Domaratzki (iAnywhere Solutions)" < Spam_bad_rdomarat@ia
nywhere.com>
wrote in message news:424c171f$1@foru
ms-2-dub...
> Cascading deletes is the easiest way to handle this. Another more
> complicated option is to define "logical deletes", where rows aren't
really
> deleted, but just marked are deleted with an extra column in the table.
As
> far as your application is concerned, the row is deleted, since it will
> ignore rows that have been "logically" deleted. This is very much a
design
> decision that is made early on though, and isn't something that's trivial
to
> implement once deployed.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> "Russell J Morgan" <russell.morgan@careervision.co.uk> wrote in message
> news:424ac8d5$1@foru
ms-1-dub...
joined[color=darkred
]
> to
the[color=darkred]
> table.
record[color=darkred
]
> The
synchronised[color=d
arkred]
to[color=darkred]
>
>

Sponsored Links