A misconception about distributed transactions: Does using TransactionScope always turn on distributed transactions?

Background:

Transactions are a basic concept of a database management system, and transactions have four basic characteristics, namely, acid: atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability), The consistency and integrity of the database can be ensured through the transaction mechanism.

However, database transactions can only be transacted at the same session level of the DB instance. Distributed transactions can coordinate operations between multiple sessions of a DB instance, even database operations between multiple DB instances, and maintain transactional characteristics. In principle, however, we do not recommend the use of distributed transactions because distributed transactions consume more resources and perform poorly.

However, we have always misunderstood the code usage and effects of distributed transactions: will it be possible to open distributed transactions with TransactionScope?

Verify:

Let's do a simple demo: Two connection strings are exactly the same, ADO. NET will reuse connections in the connection pool, what will the result be?

using(TransactionScope ts =NewTransactionScope ())        {SqlConnection conn; Conn=NewSqlConnection ("server=.; UID=TKK123;PWD=AAAAAA"); Conn.        Open (); SqlCommand cmd=Conn.        CreateCommand (); Cmd.commandtext="Select 1 as TKK"; Cmd.        ExecuteNonQuery (); Conn.        Close (); Conn=NewSqlConnection ("server=.; UID=TKK123;PWD=AAAAAA"); Conn.        Open (); CMD=Conn.        CreateCommand (); Cmd.commandtext="Select 2 as TKK"; Cmd.        ExecuteNonQuery (); Conn.        Close ();    Ts.complete (); } Console.WriteLine ("OK"); Console.readkey ();

Strange thing happened, and did not see what we thought of the Distributed transaction!!!

We changed one of the connection strings so that ADO was a two data source, so that it would really turn on distributed transactions.

using(TransactionScope ts =NewTransactionScope ())        {SqlConnection conn; Conn=NewSqlConnection ("server=.; UID=TKK123;PWD=AAAAAA"); Conn.        Open (); SqlCommand cmd=Conn.        CreateCommand (); Cmd.commandtext="Select 1 as TKK"; Cmd.        ExecuteNonQuery (); Conn.        Close (); Conn=NewSqlConnection ( "server=.; UID=TKK123;PWD=AAAAAA; "); --Adds a semicolon, does not share the connection conn.        Open (); CMD=Conn.        CreateCommand (); Cmd.commandtext="Select 2 as TKK"; Cmd.        ExecuteNonQuery (); Conn.        Close ();    Ts.complete (); } Console.WriteLine ("OK"); Console.readkey ();

Let's look at how distributed transactions coordinate each database connection, the current case we are using the same database, so although two database connections are established, the same transaction ID is still the same at the database level.

If we open two different db instances, what will we see? Try it ...

A misconception about distributed transactions: Does using TransactionScope always turn on distributed transactions?