Beginning of database transactions

Long ago, we wanted to implement a transaction, usually a database transaction based on SQL, which is usually implemented by SQL query language. As follows, we updated the price of two books at the same time:

BEGIN TRANSACTION
UPDATE tb_Book SET Price=122 WHERE IDENT_CURRENT=1001
UPDATE tb_Book SET Price=88 WHERE IDENT_CURRENT=1002
IF @@ERROR <> 0
BEGIN

ROLLBACK TRANSACTION  

END
ELSE

COMMIT TRANSACTION

ADO.NET Transaction and Distributed Transaction

With the continuous development of. NET technology, it can be achieved through ADO. NET, so that we can better apply transactions to business logic, rather than database storage, which can better achieve the separation of business and storage, so that transactions are controlled by business logic, and databases focus on data storage to achieve their respective responsibilities. In ADO.NET, transactions are written as follows:

using (DbTransaction transaction = connection.BeginTransaction())
{

command.Transaction = transaction;
try
{
    command.ExecuteNonQuery();
    transaction.Commit();
}
catch (Exception e)
{
    transaction.Rollback();
    throw;
}

}

Distributed transaction
The above code can only be based on the same database connection. Later, in order to realize the transaction of remote multiple databases, a distributed transaction is proposed, and the database server is praised for transaction association.

using (TransactionScope transactionScope = new TransactionScope())
{

//Operating the database in database server 1
using (SqlConnection connection = new SqlConnection(connectionString1))
{
    SqlCommand command = new SqlCommand(commandText1, connection);
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}

//Operating database in database server 2
using (SqlConnection connection = new SqlConnection(connectionString2))
{
    SqlCommand command = new SqlCommand(commandText2, connection);
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}

transactionScope.Complete();

}

File System Based Transactions (TXF)

Sometimes, we need to manage the operation of NTFS file system. For example, first we save pictures to hard disk and then save file paths to database. These two steps satisfy the principle of transaction (ACID). The database should be synchronized with the file system, either deleted, or both exist and die together. This is the case. Unfortunately, Microsoft does not provide such API interface in. NET for developers to use. Zero bloggers struggle with the attitude of technological excellence, and finally find a KTM transaction management solution encapsulated in the operating system kernel (kernel32.dll) in a community abroad. Zero bloggers aim at their own needs. The solution has been improved. The C NET invocation mode of this scheme is as follows:

using (TransactionScope transactionScope = new TransactionScope())
{

string commandText = "UPDATE Book SET Price=88.50 WHERE ID=1001";

var fileStream = TransactedFile.Open(@"log.txt", FileMode.OpenOrCreate, FileAccess.Write, FileShare.Write);
StreamWriter streamWrite = new StreamWriter(fileStream);
streamWrite.WriteLine(string.Concat(DateTime.Now, commandText));
streamWrite.Flush();
streamWrite.Close();

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(commandText, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();

transactionScope.Complete();

}

The above example code first creates a TransactionScope transaction environment, which associates file operations with database operations into the same transaction. TransactedFile is the KTM-based local transaction encapsulation mentioned above. The database transaction itself supports implicit automatic association. The current and the latter are simultaneously associated with TransactionScope transactions. After the context environment, a completed transaction is formed. The above example first writes the current time and the SQL statement to be executed to the log.txt of the disk, then updates the Book price through SQL, and rolls back the log written if the update price fails (automatically deletes the log).

Transaction is a complex system, mainly KTM, DTC and LTM transactions, internal implementation and its complexity. This paper mainly describes the basic usage of transactions.