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
27.0k444614889
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
5.6k53870
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×107

question asked: 02 Aug '10, 20:45

question was seen: 478 times

last updated: 09 Aug '10, 18:10