The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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".

asked 02 Aug '10, 20:45

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

edited 04 Aug '10, 09:13

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?

(03 Aug '10, 00:04) Mark Culp

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...)

(04 Aug '10, 09:07) 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".

(04 Aug '10, 09:11) Breck Carter

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.

permanent link

answered 09 Aug '10, 18:10

Karim%20Khamis's gravatar image

Karim Khamis
accept rate: 40%

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 :)

(10 Aug '10, 15:00) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 02 Aug '10, 20:45

question was seen: 465 times

last updated: 09 Aug '10, 18:10