SQL Anywhere database replication - Prevent changes to a record except from replication

Author

Prevent changes to a record except from replication

Edgard L. Riba

2005-07-08, 9:23 am

Hi,

Yesterday I got a "concerned" call from my users because two transactions
that had been posted became misteriously "unposted" and changed :-(.

Although this scenario has been uncommon, it has happened a few times in the
past, and every time it happens is a pain...

I was thinking that a solution to this would be to have a trigger that
prevents all changes to the posted transactions (there are actually two
tables, transactionHeader and transactionRows).

This seems like a reasonable solution, except that since I'm using
replication, I wonder whether I need to allow the trigger to permit changes
coming from replication (inserts/updates/deletes), even if the transaction
is posted.

For example, if there is a transaction on location #1 that needs to
replicate to location #2 and I do the following changes at location #1:
UPDATE transactionHeader SET Total=300.00 WHERE sysID=100;
COMMIT
UPDATE transactionHeader SET statusFlag='POSTED' WHERE sysID=100;
COMMIT

Is it possible that the change in "statusFlag" replicates to location#2
before the change in "Total"?

Or if I do:
UPDATE transactionHeader SET Total=300.00,
statusFlag='POSTED' WHERE sysID=100;

Again, is it possible that the change in "statusFlag" replicates to
location#2 before the change in "Total"? Or in this case replication will
do the whole transaction or nothing?

Thanks,
Edgard

Rob Waywell

2005-07-08, 1:24 pm

Since SQL Remote replicates completed transactions in the order they are
committed in the transaction log, both the transaction orders and the commit
boundaries* are preserved. (* exception to the commit boundaries being
preserved is if grouping is turned on, which it is by default, then multiple
transactions (default of 20) will be grouped and applied as a single group.
See the -g switch on dbremote)

Complications come when you have to use UPDATE .... PUBLICATION statements
to move related records since the UPDATE ... PUBLICATION may cause child
records to move prior to parent records. However neither of your examples
showed that scenario.

In your first example you have two transactions, each with a single
operation. These transactions will be replicated in order and assuming
grouping is turned on, will be applied as a single transaction at the
receiving side. Since the log records the exact updates executed this may be
multiple operations. eg: If the update affects 10 records, the log records
the 10 updated records by primary key.

In your second example you have a single operation, each record updated will
be logged and once the operation is committed those updates will be
available to be replicated.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"Edgard L. Riba" <elriba at rimith dot com> wrote in message
news:42ce8fdf@forums
-2-dub...
> Hi,
>
> Yesterday I got a "concerned" call from my users because two transactions
> that had been posted became misteriously "unposted" and changed :-(.
>
> Although this scenario has been uncommon, it has happened a few times in
> the past, and every time it happens is a pain...
>
> I was thinking that a solution to this would be to have a trigger that
> prevents all changes to the posted transactions (there are actually two
> tables, transactionHeader and transactionRows).
>
> This seems like a reasonable solution, except that since I'm using
> replication, I wonder whether I need to allow the trigger to permit
> changes coming from replication (inserts/updates/deletes), even if the
> transaction is posted.
>
> For example, if there is a transaction on location #1 that needs to
> replicate to location #2 and I do the following changes at location #1:
> UPDATE transactionHeader SET Total=300.00 WHERE sysID=100;
> COMMIT
> UPDATE transactionHeader SET statusFlag='POSTED' WHERE sysID=100;
> COMMIT
>
> Is it possible that the change in "statusFlag" replicates to location#2
> before the change in "Total"?
>
> Or if I do:
> UPDATE transactionHeader SET Total=300.00,
> statusFlag='POSTED' WHERE sysID=100;
>
> Again, is it possible that the change in "statusFlag" replicates to
> location#2 before the change in "Total"? Or in this case replication
> will do the whole transaction or nothing?
>
> Thanks,
> Edgard
>

Edgard L. Riba

2005-07-09, 3:23 am

Hi Rob,

Thanks for clearing that up.

I have one last question regarding this.

Let's say the following transactions occur on the remote server:

UPDATE transactionHeader SET Total=300.00 WHERE sysID=100;
COMMIT
UPDATE transactionHeader SET statusFlag='POSTED' WHERE sysID=100;
COMMIT
..... then later on during the day:
UPDATE dailyControl SET dayIsClosed='TRUE' WHERE Date='2005-07-07';

In other words, after the transactions get properly updated on the remote
server, another "daily-control" log table gets updated so that the day is
"closed" an no further transactions can occur on that date.

If I follow you correctly, the updates on "transactionHeader" will replicate
in the same order they occured. But, the update on "dailyControl"? Will
it replicate AFTER the transaction updates?

Best regards,
Edgard

"Rob Waywell" <rwaywell@no_spam.ianywhere.com> escribió en el mensaje
news:42ceb833$1@foru
ms-1-dub...
> Since SQL Remote replicates completed transactions in the order they are
> committed in the transaction log, both the transaction orders and the
> commit boundaries* are preserved. (* exception to the commit boundaries
> being preserved is if grouping is turned on, which it is by default, then
> multiple transactions (default of 20) will be grouped and applied as a
> single group. See the -g switch on dbremote)
>
> Complications come when you have to use UPDATE .... PUBLICATION statements
> to move related records since the UPDATE ... PUBLICATION may cause child
> records to move prior to parent records. However neither of your examples
> showed that scenario.
>
> In your first example you have two transactions, each with a single
> operation. These transactions will be replicated in order and assuming
> grouping is turned on, will be applied as a single transaction at the
> receiving side. Since the log records the exact updates executed this may
> be multiple operations. eg: If the update affects 10 records, the log
> records the 10 updated records by primary key.
>
> In your second example you have a single operation, each record updated
> will be logged and once the operation is committed those updates will be
> available to be replicated.
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available through the
> iAnywhere
> Developer Community at www.ianywhere.com/developer
> "Edgard L. Riba" <elriba at rimith dot com> wrote in message
> news:42ce8fdf@forums
-2-dub...
>
>

Rob Waywell

2005-07-11, 9:23 am

The replication order depends on the order of the COMMIT operations in the
transaction log. Assuming that the transactions in your example are all from
a single connection, then the order of the COMMIT operations will be as
shown in your example.

Keep in mind however, that it is possible for Connection A to start a long
running transaction before Connection B starts a short running transaction
and for Connection B to COMMIT its transaction before Connection A commits
its transaction. So you could have a scenario such as:

Connection A
> UPDATE transactionHeader SET statusFlag='POSTED' ...

Connection B
> UPDATE dailyControl SET dayIsClosed='TRUE' WHERE Date='2005-07-07';
> COMMIT

Connection A
>COMMIT

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"Edgard L. Riba" <elriba at rimith dot com> wrote in message
news:42ced924$1@foru
ms-1-dub...
> Hi Rob,
>
> Thanks for clearing that up.
>
> I have one last question regarding this.
>
> Let's say the following transactions occur on the remote server:
>
> UPDATE transactionHeader SET Total=300.00 WHERE sysID=100;
> COMMIT
> UPDATE transactionHeader SET statusFlag='POSTED' WHERE sysID=100;
> COMMIT
> .... then later on during the day:
> UPDATE dailyControl SET dayIsClosed='TRUE' WHERE Date='2005-07-07';
>
> In other words, after the transactions get properly updated on the remote
> server, another "daily-control" log table gets updated so that the day is
> "closed" an no further transactions can occur on that date.
>
> If I follow you correctly, the updates on "transactionHeader" will
> replicate in the same order they occured. But, the update on
> "dailyControl"? Will it replicate AFTER the transaction updates?
>
> Best regards,
> Edgard
>
>
>
> "Rob Waywell" <rwaywell@no_spam.ianywhere.com> escribió en el mensaje
> news:42ceb833$1@foru
ms-1-dub...
>
>

Sponsored Links