Wich DBMS supports TransactionScope and distributed transactions?

[BLUE]

Only SQL Server 2005 or also DB2, Oracle and MySQL?
Thanks,
Luigi.

[Nicholas Paldino [.NET/C# MVP]]

Luigi,

I imagine all of them do. While the underlying resource has to have the
ability to perform transactions (or it usually does, to help), it is the
provider for the databases which has to be aware of the current transaction
on the thread and react accordingly. The classes in System.Data.SqlClient
are most definitely aware, as are the OleDb classes I assume.

Your best bet is to check the documentation for the provider, or even
better, just wire up an example and see if it works.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"BLUE" <bluewrote in message news:eI**************@TK2MSFTNGP02.phx.gbl...

Only SQL Server 2005 or also DB2, Oracle and MySQL?
Thanks,
Luigi.

[Marc Gravell]

If you [or somebody] did such an example, it might help to listen to:
System.Transactions.TransactionManager.Distributed TransactionStarted

Note that some providers (certainly SQL2005) use promotable
transactions, so it can use the LTM initially rather than DTC; this
means that the distributed-transaction is not started until the second
connection gets involved. This is easiest to demonstrate with
different connection strings (e.g. different database), which forces
DTC - but note that this area is *further* complicated (as if /that/
were necessary) by the presence of MARS... so the connection you've
snagged (possibly silently using enterprise library) might already be
busy in the same scope, but you get to share.

[note: I know from previous posts that the OP is aware of this; this
is more for the benefit of the archive...]

Marc

[BLUE]

So far googling and googling I've found that except for SQL Server provider,
it is not sure if the others support Transaction scope + DTC:
- MySQL it was a feature they want to develop but now they don't tell
- Oracle changelog states it supports, but users does not agree
- DB2 seems to support

http://www.bytefx.com/blog/CategoryV...ory,MySQL.aspx

http://www.oracle.com/technology/tec...wfeatures.html
http://www.oracle.com/technology/doc...0.2_readme.txt
http://forums.oracle.com/forums/thre...hreadID=379962

http://www.ibm.com/developerworks/fo...m=467#13785120
I think all supports TransactionScope without DTC so the problem is the
following.

I'm doing a select on Table1 and then I have a loop on the datareader: for
each record I do many queryies to preform insert or update on 4 other tables
(some selects are involved of course).

Table1 is used only to insert records (each application insert a new record
so each concurrent access does not affect the others as if there was no
concurrency).

From time to time I need to update the 4 other tables with data from Table1
where Date < X (X = Now) and after insertion I'll delete the inserted
records.

I've thought I can do the select outside the TransactionScope, but:

- if I loop on the datareader inside the transactionscope, a distributed
transaction occurs?

- when I have to delete inserted records I cannot do DELETE FROM Table1
WHERE Date < X since between te select and the entering in the transaction
scope, a concurrent access could have inserted a row that matches the where
clause.
I have to do a "DELETE INSERTED ROW" instead of a global delete right?
Thanks a lot,
Luigi.