Trying to work out whether this feature might form part of some new design we're thinking of doing., and I can find very little discussion of this feature so I'm wondering whether anyone much is using it and what experience of it there is.
My main concern is performance. Reading various sources of information (but being a bit confused by page 646 of the book): (1) ON EXTERNAL DATA SOURCE ... WITH COMMON TRANSACTION Scenario: (a) Running a stored procedure in database A (b) EXECUTE STATEMENT ... ON EXTERNAL DATA SOURCE ... WITH COMMON TRANSACTION ... on database B (c) repeat (b) several times (d) Complete the stored procedure which is running in database A and commit the transaction in database A. Suppose the actual statement executed in (b) is updating one field in one record in a properly indexed table, ie is about the simplest UPDATE statement possible. My reading of the material I've found is that (i) The first time (b) is run a new connection will be opened to database B and a new transaction will be started (ii) On subsequent executions of (b) then (provided the connection string etc matches) both the connection and the transaction will be re-used (iii) I can't find any documentation of when the connection on database B get closed, and when and under what circumstances the transaction on database B gets committed and when and under what circumstances it gets rolled back (2) ON EXTERNAL DATA SOURCE ... WITH AUTONOMOUS TRANSACTION Scenario as above, except AUTONOMOUS instead of COMMON at (b). My reading of the material I've found is that (i) Every time (b) is run a new connection will be opened to database B and a new transaction will be started (ii) Each time the statement run in (b) completes the transaction in database B will be committed, unless there was an error in which case it will be rolled back (ii) Each time the statement run in (b) completes the connection to database B will be closed Have I got that right so far? - in which case please (A) In case (1), what's the answer to point (iii)? (B) In case (2), isn't it rather expensive to reopen the connection to database B every time? Or is the cost of reconnection so little compared to the cost of starting a new transaction that it doesn't matter? (! - opening a connection must be several packets across the network and starting a new server process, so it can't be *that* cheap?) -- Tim Ward
