Transaction Errors when accessing multiple databases

Sorry in advance if this seems slighly vague - we're tracking down a problem and while we're certain what the problem is (a database spanning trancaction on SQL Server requires RPC, which isn't allowed in our DMZ) we're still uncertain as to why its happening.

So, are there any known issues with Lightspeed (Version 3.1.1834.14582) and:

• Multiple Lightspeed models in the same application
• Each (well, two) model using a different database.

Also, does Lightspeed wrap everything up in a transaction by default?

The problem we have is summed up here:

MSDTC will need to be allowed through any firewalls on both machines as will port 134 (the RPC endpoint mapper). Check to make sure these things are setup properly and let me know if it's still failing.

To expand on what is happening here:
As you mentioned, this isn't thrown until after you attempt to open the second connection.  SqlClient uses the Transaction Management Escalation feature of System.Transactions when dealing with Sql 2005 (also known as PromotableSinglePhaseEnlistment).  During the first connection the Commit of the Transaction is delegated to the escalateable enlistment (i.e. to SQL and its internal Transaction), so msdtc is not involved.  When you attempt to open the second connection, SqlClient takes an action that forces us to escalate, or promote the management of the Transaction to msdtc.  In doing so, we ask SQL to promote its internal Transaction to an msdtc Transaction.  Sql does so on the server side and return to us on the client side a marshalled version of the msdtc Transaction.  The last frame you see on the call stack of the exception is System.Transaction attempting to use this to Receive the Transaction from the server side.  This requires us to communicate with the msdtc instance on the server machine.  I beleive the firewall is preventing that and thus the TransactionManagerCommunicationException.

(Taken from this thread: http://social.msdn.microsoft.com/Forums/en-US/windowstransactionsprogramming/thread/71f7a219-c85d-4a04-973b-c73464f59606/)

Cheers,

Matt.

p.s. its entirely possible that we've cocked up some threading code somewhere and things are being shared that should not be shared.  Still investigating.

Hi Matt,

We dont have any known issues with the scenario you have outlined, but LightSpeed does indeed employ transactions to cover some situations. The queries generated by a call to SaveChanges are wrapped in a transaction to ensure they are atomic (see: http://www.mindscapehq.com/documentation/lightspeed/Basic-Operations/Transactions for details).

Additionally the KeyTable identity generator and the Sequence identity generator will create seperate transactions for the queries that they generate to reserve a block of identifiers.

That said each transaction is isolated to its connection (and to the situations mentioned above) so I would not expect there to be any ambient enlistment taking place. Do you have any transaction use in your calling code?

Jeremy

Thanks for that - as far as I'm aware this code doesn't explicitly use transactions.

And the problem appears to have magically disappeared...  Not something I was hoping for, today's going to be fun :-)

Cheers,

Matt.