A misunderstanding about distributed transactions: Will distributed transactions be started when TransactionScope is used ?,
Background:
Transactions are a basic concept of a database management system. transactions have four basic features: ACID: Atomicity, Consistency, Isolation, and Durability ), the transaction mechanism can ensure Database Consistency and integrity.
However, database transactions can only be controlled at the same session level of the database instance. Distributed transactions can coordinate the operations between multiple sessions of a database instance, or even database operations between multiple database instances, and maintain the transaction characteristics. In principle, distributed transactions are not recommended because distributed transactions consume a large amount of resources and execution efficiency is poor.
However, we have always misunderstood the code usage and effects of distributed transactions: Will distributed transactions be started when TransactionScope is used?
Verification:
Let's make a simple Demo: The two connection strings are identical, and ADO. NET will reuse the connections in the connection pool. What will happen?
using (TransactionScope ts = new TransactionScope()) { SqlConnection conn; conn = new SqlConnection("server=.;uid=tkk123;pwd=aaaaaa"); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "select 1 as tkk"; cmd.ExecuteNonQuery(); conn.Close(); conn = new SqlConnection("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();
Something strange happened, and we didn't see what we thought was distributed transactions !!!
Using (TransactionScope ts = new TransactionScope () {SqlConnection conn; conn = new SqlConnection ("server = .; uid = tkk123; pwd = aaaaaa "); conn. open (); SqlCommand cmd = conn. createCommand (); cmd. commandText = "select 1 as tkk"; cmd. executeNonQuery (); conn. close (); conn = new SqlConnection ("Server =.; uid = tkk123; pwd = aaaaaa ;"); -- Added a semicolon to avoid sharing 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 take a look at how distributed transactions coordinate the connection of each database. In this case, we use the same database. Although we have established two database connections, however, it is still the same transaction ID at the database level.
If we open two different database instances, what results will we see? Try it...