Use of transactions in c#

1. Overview

1. Transaction ACID Features

Transactions treat a series of work as a unit of work, which has ACID characteristics:

A: Atomicity Indivisibility
In other words, there are multiple tasks in a transaction. If one job fails, the entire transaction will be considered as a failure.
C: Consistency
When a transaction is completed, all data must maintain a consistent state. For relational databases, simply put it, it does not destroy data integrity.
I: Isolation
Transactions are isolated from other transactions, that is, when a transaction's work modifies data, if the entire transaction has not ended, other transactions cannot know that the data has been modified.
D: Durability persistence
After the transaction is completed, its function will always exist in the system.

2. 5 transaction mechanisms used by .NET developers:

SQL and stored procedure level transactions. (Database transaction)
Level of transactions.
Page-level transactions.
Enterprise-level service COM+ transactions.
Transaction processing.

These five transaction mechanisms have their own advantages and disadvantages, which are reflected in performance, code quantity and deployment settings. Developers can choose corresponding transaction mechanisms based on the actual situation of the project.

2. Database transactions

1. Transaction rules for different databases

Database transactions are the basis of other transaction models. When a transaction is created, different database systems have their own rules.

SQL Server works in automatic submission mode by default, and each statement will be submitted immediately after execution;
Oracle requires you to include a commit statement.
When a statement is executed through OLE DB, a commit action will be attached after it is executed.

For example: SQL Server database T-SQL statement displays the specified transaction

declare @TranName varchar(20);
select @TranName = 'MyTransaction';

begin transaction @TranName;
go

use AdventureWorks;
go

delete from  where JobCandidateID = 13;
go

commit transaction MyTransaction;
go

Or use in stored procedures

create procedure Tran1
as
    begin tran;
    set xact_abort on; --set xact_abort onIt means that an error is encountered will be rolled back immediately。
    insert into P_Category ( CategoryId, Name ) values ( '1', 'test1' );
    insert into P_Category ( CategoryId, Name ) values ( '2', 'test2' );
    commit tran;
go

2. Advantages and limitations of database transactions

(1) Advantages:

All transaction logic is contained in a separate call.
Have the best performance to run a transaction.
Independent of the application.

(2) Limitations:

The transaction context only exists in the database call.
The database code is related to the database system.

III. Affairs

Transactions are various derived classes of classes. A transaction is not a distributed transaction and does not support crossing multiple connections. It is always associated with a local transaction on one connection.

Explicit transactions occupy less resources and are fast, but have simple functions and can only manage transactions between a single object and a single persistent resource.

using (SqlConnection conn = new SqlConnection(["MySqlServer"].ConnectionString))
{
    ();
    using (SqlTransaction tran = ())
    {
        using (SqlCommand cmd = new SqlCommand())
        {
             = conn;

             = tran;
             = ;
            try
            {
                 = "insert into TranTable(Priority) values(1)";
                ();
                 = "insert into TranTable(Priority) values(256)";
                ();
                ();
                ("Ok");
            }
            catch (SqlException ex)
            {
                ();
                ("Error:" + );
            }
        }
    }
    ();
}

4. Automatic transaction processing

There is no need to display for transaction processing, the runtime library automatically creates transactions. Multiple objects can easily run in the same transaction. But it requires a COM+host model.

Classes using this technique must be derived from the ServicedComponen class.

[Transaction()]
public class OrderContrl : ServicedComponent
{
    [AutoComplete]
    public void NewOrder(Order order)
    {
        using (OrderData data = new OrderData())
        {
            (order);
        }
    }
}

V. Affairs

It is the base class for all transaction classes.

The infrastructure makes transaction programming across the platform simple and efficient by supporting transactions initiated in SQL Server, MSMQ and Microsoft Distributed Transaction Coordinator (MSDTC).

It provides both an explicit programming model based on the Transaction class and an implicit programming model using the TransactionScope class, where the transactions inside it are automatically managed by the infrastructure. It is highly recommended to use a simpler implicit development model TransactionScope.

1. Explicit Transaction

The method in which both commit and rollback transactions are determined by programmers programmatically is called "Explicit Transaction". Transaction class and its derived classes are explicit transactions.

2. Derived classes of Transaction

CommittableTransaction: commitable transactions
DependentTransaction: Dependent transactions
SubordinateTransaction: Subordinate transactions that can be delegated

3. Transaction class members

Current: Get or set up environment transactions.
IsolationLevel: Gets the isolation level of the transaction.
TransactionInformation: Retrieve additional information about a transaction.
DependentClone(DependentCloneOption): Creates a dependency replica of the transaction.
Rollback(): Rollback (absolute) the transaction.
Dispose(): Release the resource occupied by this object.
TransactionCompleted: Indicates that the transaction has been completed.

6. Commitable transactions: CommitableTransaction (explicit transaction)

The only transaction class that supports committing is CommitableTransaction, which is directly inherited from Transaction.

CommittableTransaction provides a "Commit" synchronization method and a "BeginCommit" and "EndCommit" asynchronous methods to combine the commit to transactions.

Creating a CommittableTransaction transaction does not automatically set up the environment transaction.

CommittableTransaction transactions cannot be reused. You can register a database connection to a transaction.

Note: Only one DbConnection is a local transaction; MSDTC will be started when there are multiple DbConnections (MSDTC is not stable enough, try to avoid introducing distributed services)

using (SqlConnection conn = new SqlConnection(["MySqlServer"].ConnectionString))
{
    using (CommittableTransaction ct = new CommittableTransaction ())
    {
        ();
        (ct);//Register database connection to transaction        using (SqlCommand cmd = new SqlCommand())
        {
             = conn;
             = ;
            try
            {
                 = "insert into TranTable(Priority) values(1)";
                ();
                 = "insert into TranTable(Priority) values(256)";
                ();
                (); //Submit transaction                ("Ok");
            }
            catch (SqlException ex)
            {
               ();//Rolleate the transaction               ("Error:" + );
            }
        }
        ();
    }
}

7. Environmental transactions: TrasactionScope (implicit transactions, recommended)

TransactionScope is an implicit transaction. It creates an execution scope for a set of transactional operations, which starts when TransactionScope is created and ends when TransactionScope is recycled (called the Dispose method).

TransactionScope implements the IDisposable interface, except for the Dispose method, it only has one unique method: Complete().

Currently TransactionScope can only handle database transactions, and for other transactions, such as I/O, the current .NET version cannot handle them.

using (SqlConnection conn = new SqlConnection("Data Source=.; Initial Catalog=TestDb; Integrated Security=SSPI;"))
{
    using (TransactionScope ts = new TransactionScope())
    {
        ();
        try
        {
            SqlCommand cmd = new SqlCommand(conn);
             = "INSERT INTO [Test]([Name],[Value]) VALUES ('Test 1','1')";
            ();
             = "INSERT INTO [Test]([Name],[Value]) VALUES ('Test 2','2')";
            ();

            ();
        }
        catch (SqlException)
        {
        }
        ();
    }
}

TransactionScope has a rich set of constructors. Let's first look at how the corresponding parameters of these constructors affect the behavior of TransactionScope's transaction control.

void Main()
{
    TransactionOptions transactionOptions = new TransactionOptions()
    {
        IsolationLevel = ,
        Timeout = new TimeSpan(0, 2, 0)//Two minutes later    };

    using (TransactionScope scope = new TransactionScope(, transactionOptions))
    {
         += (sender, args) =&gt;
        {
            ();
        }

        AddStudent(new Student { });
        ();//Submit transaction    }
}

public void AddStudent(Student s)
{
    SqlConnection conn = new SqlConnection();//The newly created connections in the environment transaction are automatically attached to the transaction    //If the connection already exists before TransactionScope is established, you need to manually register the transaction with ()    ();
    try
    {
        SqlCommand command = new SqlCommand();
    }
    catch (Exception ex)
    {
        throw;
    }
}

1. IsolationLevel

Among the 7 isolation levels, Serializable has the highest isolation level, representing a completely serialization (synchronization)-based data access method. According to the isolation level to the highest to the lowest, the 7 different isolation levels represent the meanings as follows:

Serializable: Serializable. (Default, highest level) Variable data can be read during a transaction, but it cannot be modified or any new data can be added;
RepeatableRead: Repeatable. Variable data can be read during a transaction, but cannot be modified. New data can be added during a transaction;
ReadCommitted: Reads submitted data. Variable data cannot be read during a transaction, but it can be modified;
ReadUncommitted: Read uncommitted data. Variable data can be read and modified during a transaction;
Snapshot: Snapshot. Variable data can be read. Before the transaction modifies the data, it verifies whether another transaction has changed the data after it initially reads the data. If the data has been updated, an error will be raised. This allows the transaction to obtain previously committed data values;
Chaos: Chaos. Unable to override pending changes in transactions with higher isolation levels;
Unspecified: Not specified. An isolation level is being used that is different from the specified isolation level, but it cannot be determined. If this value is set, an exception is thrown.

2. Nested environment transactions

using (TransactionScope outerScope = new TransactionScope())
{
    using (TransactionScope innerScope = new TransactionScope(, transactionOptions))
    {
        //Transaction-based operation        ();
    }
    //Transaction-based operation    ();
}

3、TransactionScopeOption

Use TransactionScopeOptions to change the default transaction type of TransactionScope.

Required: (default) If a transaction already exists, the transaction scope will join the existing transaction. Otherwise, it will create its own transaction.
RequiresNew: This transaction scope will create its own transaction.
Suppress: Suppress. Setting an environment transaction within the transaction scope to empty means that operations within the transaction scope are not controlled by the transaction. This option can be used when some of the code needs to remain outside the transaction.

4. MSDTC component settings:

Generally speaking, as long as you use "TransactionScope", you must configure MSDTC, install a firewall, and open port 139. This port cannot be changed.

If the WEB server and the database are on the same server, TransactionScope uses local transactions, and there is no need to configure MSDTC.
If the WEB server and the database are not on the same server, TransactionScope will automatically increase the transaction level to distributed transactions, and then you need to configure MSDTC.

Setting up the MSDTC component: Control Panel --->Administrative Tools --->Services, enable the Distributed Transaction Coordinator service.

Control Panel -> Administrative Tools -> Component Services -> Computer -> My Computer -> Right-click -> Properties, select the MSDTC page, and confirm "Use local coordinator".
Click the "Safety Configuration" button below
Check: "Allow network DTC access", "Allow remote clients", "Allow inbound", "Allow outbound", "Do not require authentication".
For the database server side, you can choose "Require verification for caller"
Check: "Enable Transaction Internet Protocol (TIP) Transactions".
Add exceptions to both firewalls
Available command line: netsh firewall set allowedprogram %windir%/system32/ MSDTC enable

8. Depend on transaction DependentTransaction to call transactions across multiple threads (explicit transactions)

An environment transaction is bound to a thread. If a new thread is created, it will not have environmental transactions in the first thread. The transactions in the two threads are completely independent.

If multiple threads use the same environment transaction, a dependency transaction needs to be passed to the new thread, and the DependentClone method of Transaction is called to create a dependency transaction.

Dependent transactions are represented by the DependentTransaction type. Like CommittableTransaction, DependentTransaction is also a subclass of Transaction.

DependentTransaction exists on an existing Transaction object, which is equivalent to a child transaction of a dependent transaction, with a unique method member:Complete. Calling this method means sending a notification to the dependent transaction, indicating that all operations related to the dependent transaction have been completed.

The DependentClone method has a parameter of the DependentCloneOption enumeration type:

BlockCommitUntilComplete: indicates that the dependent transaction will wait for notification of the dependent transaction (sub-transaction) before committing (calling the Complete or Rollback method) or exceeding the timeout limit set by the transaction;
RollbackIfNotComplete: If the dependent transaction is completed before the dependent transaction (subtransaction), the dependent transaction is rolled back and a TransactionAbortedException exception is thrown.

The following code example demonstrates how to create a dependent transaction to manage two concurrent tasks, specifically cloning a dependent transaction and passing it to a helper thread.

void Main()
{
    using (TransactionScope scope = new TransactionScope())
    {
        DependentTransaction dTx = ();
        Thread thread = new Thread(ThreadMethod);
        (dTx);
        /* Do some transactional work here, then: */
        ();
    }
}

public void ThreadMethod(object transaction)
{
    DependentTransaction dTx = transaction as DependentTransaction;
    try
    {
        //Presize the DependentTransaction object as a parameter of TransactionScope to initialize the environment transaction        using (TransactionScope ts = new TransactionScope(dTx))
        {
            /* Perform transactional work here */
            ();//Complete environmental affairs        }
    }
    finally
    {
        ();//Complete dependency transaction        ();
    }
}

This is all about this article about C# transactions. I hope it will be helpful to everyone's learning and I hope everyone will support me more.