Implement local COMMIT that is not sent to remote servers

[Breck_Carter]

If you establish one or more remote (proxy) database connections from a "master" connection on the local database, and then issue a COMMIT on that master connection, COMMIT operations are sent to ALL the remote database connections.

...still, after all these years using proxy tables, this behavior can reach out and Whack! you 🙂

It would be valuable and useful to be able to decouple remote database transaction design from that of the local database... perhaps as an additional non-default keyword such as COMMIT LOCAL ONLY.

---

Perhaps documenting this COMMIT side-effect would be sufficient, in two places: in the COMMIT syntax Help topic, and in the general discussion of "remote data access".

[MarkCulp]

Can you give us an example when you might want to do this?

Maybe this is an example of when autonomous transactions would be useful (if SA were to supported them). I.e. If SA supported autonomous transactions, would this satisfy your need?

[Breck_Carter]

The example is somewhat artificial: A Foxhound "test driver" script that created remote server connections to V5.5, 6, 7, 8, 9, 10 and 11 servers and then used TRIGGER EVENT to start multiple connections to each with some of the connections blocking each other... the point being to prove that a single Foxhound instance can sort out the resulting Sturm und Drang 🙂 Each script segment contained a COMMIT which meant that only the last segment actually created the blocking situation... all the previous work was "undone" by a COMMIT being sent to all remote servers. (more...)

[Breck_Carter]

It took quite a while to debug because the "global COMMIT to everyone" is a rather surprising side effect. I was able to remove the COMMIT... another alternative would be to wrap everything in events so separate connections are used... so perhaps the suggestion should be "document this side effect in the Help COMMIT topic".

[Former Member]

If the remote servers are used for read access only, then you would be better off issuing create server statements that contained the read only clause. SA will not propagate any commit or rollback to remote servers that are explicitly marked as read only.

[Breck_Carter]

Alas... the problem arose because these remote servers ARE subject to updates. If they were read access only then the problem caused by COMMIT would not have occurred 🙂