Step by Step C # technical talk 7. Transaction Processing)

Overview

Transactions must be atomic, consistent, isolated, and persistent. Although acronyms are easy to remember, the meaning of each word is not obvious. The following is a brief description.
● Atomicity: Atomicity ensures that either all updates are executed or nothing happens. Due to the atomicity guarantee in the transaction, developers do not have to write code to process the update successfully, but the other is not.
● Consistency: Consistency means that the transaction results keep the system consistent. Data remains valid before the transaction starts, just as at the end of the transaction. Consistency also ensures that the transaction must keep the database in a consistent state. If some operations of the transaction fail, the other parts must return to the original state.
● Isolation: multiple users may access the same database at the same time. Isolation ensures that data changes in the transaction cannot be seen outside the transaction before the transaction is completed. And cannot access some intermediate states. if the transaction is terminated, these statuses will not occur.
● Durability: Durability means consistency even if the system crashes. If the database system crashes, durability must ensure that committed transactions are indeed written to the database.

 

Transaction type
Transactions are classified into local transactions and distributed transactions.
● Local transaction: This type of transaction uses known data sources (such as SQL Server), and is also a single-stage transaction. If a single database stores all transaction-related data, you can force ACID rules on itself. This means that in a single database Server (such as SQL Server), local transactions can be used across databases as long as the same connection is used.
● Distributed Transaction: This type of transaction uses multiple known transaction data sources. Distributed behavior may require reading messages from the Message Queue Server, obtaining data from the SQL Server database, and writing messages to other databases.
Some software packages (such as MSDTC) can help implement distributed transactions programmatically. By using some methods (such as two-phase commit and rollback), you can control the commit and rollback actions across all data sources, to ensure integration. MSDTC is only applicable to applications compatible with transaction management interfaces. Currently available applications include MSMQ, SQL Server, Oracle, Sybase, and other currently available applications (called resource manager ).
Two-phase submission
In a distributed transaction environment, different resource managers need to implement reliable Commit Protocols. The most common implementation is two-phase commit. In two-phase submission, the actual submission is divided into two phases:
● The first phase involves preparing some required changes for submission. In this way, RM (Resource Manager) will communicate with the Transaction Coordinator to inform it that the update is ready and the preparation is executed and submitted, but it is not submitted yet.
● Once all the resource managers inform the Transaction Coordinator that the preparation is ready, the Transaction Coordinator will make all the participants understand that the work is ready to continue and then execute the changes.
In two-phase commit, one or more databases can participate in distributed transactions. In fact, any objects registered in MSDTC transactions can participate in distributed transactions managed by MSDTC. For example, MSMQ can participate in transactions that connect two different databases by two SqlConnection objects. It is too simple to briefly describe the two-phase commit, and the two-phase commit is beyond the scope of this book. Since the reader has a preliminary understanding of the transaction, he can understand the support for the transaction process provided by. NET 1.x.

 

Four types of transaction processing

I. SQL transactions

SQL transactions use SQL server transactions: In the stored procedure, Begin Tran, Rollback Tran, and Commit Tran are used to implement transactions:

Advantage: optimal execution efficiency

Restrictions: Transaction context is only called in the database, making it difficult to implement complex business logic.

Example: (The AdventureWorks data provided by SQL Server is used as an example)

Stored Procedures with transactions:

Create procedure dbo. spModifyAddress
(
@ City nvarchar (30 ),
@ AddressID int,
@ PostalCode nvarchar (15 ),
@ Name nvarchar (50 ),
)
AS
Begin Tran
Update Address Set City = @ City, PostalCode = @ PostalCode where AddressID = @ AddressID
Update AddressType set Name = @ Name where AddressTypeID = @ AddressID
Declare @ UpdateError int
Select @ UpdateError = @ error
If (@ UpdateError = 0)
COMMIT Tran
Else
ROLLBACK Tran
GO

 

Call:

Public void SQLTran ()
{
SqlConnection conn = new SqlConnection ("Data Source = 127.0.0.1; Initial Catalog = AdventureWorks; Persist Security Info = True; User ID = sa; Password = 123 ;");
SqlCommand cmd = new SqlCommand ();
Cmd. CommandText = "spModifyAddress ";
Cmd. CommandType = CommandType. StoredProcedure;
Cmd. Connection = conn;
Conn. Open ();
SqlParameter [] paras = new SqlParameter [] {
New SqlParameter ("@ AddressID", SqlDbType. Int, 4 ),
New SqlParameter ("@ City", SqlDbType. NVarChar, 30 ),
New SqlParameter ("@ PostalCode", SqlDbType. NVarChar, 32 ),
New SqlParameter ("@ Name", SqlDbType. NVarChar, 50 )};
Paras [0]. Value = "2 ";
Paras [1]. Value = "zhejang ";
Paras [2]. Value = "315000 ";
Paras [3]. Value = "group ";
Foreach (SqlParameter para in paras)
{
Cmd. Parameters. Add (para );
}
Cmd. ExecuteNonQuery ();
}

 

Ii. ADO.net transactions

Ado.net transactions may be commonly used.

Advantage: it is simple, and the efficiency is similar to that of database transactions.

Disadvantage: transactions cannot span databases and can only be connected to one database. This transaction cannot be used on two databases.

Example:

Public void SQLTran ()
{
SqlConnection conn = new SqlConnection ("Data Source = 127.0.0.1; Initial Catalog = AdventureWorks; Persist Security Info = True; User ID = sa; Password = 123 ;");
SqlTransaction sqlTran = conn. BeginTransaction ();
SqlCommand cmd = new SqlCommand ();
Cmd. CommandText = "spModifyAddress ";
Cmd. CommandType = CommandType. StoredProcedure;
Cmd. Connection = conn;
Conn. Open ();
Cmd. Transaction = sqlTran;
AddParameter (cmd );
Try
& N