The principle and practice of C # database transaction

What is a database transaction

A database transaction is a series of actions performed as a single logical unit of work.

Imagine a transaction of online shopping in which the payment process includes at least the following steps of the database operation:

· Update the inventory information of the goods purchased by the customer

· Save customer payment information-may include interaction with the banking system

· Generate the order and save it to the database

· Update user related information, such as shopping quantity, etc.

In normal circumstances, these operations will proceed smoothly, the final transaction is successful, and all database information related to the transaction is successfully updated. However, if anything goes wrong in any of these processes, such as an exception when updating inventory information on a commodity, a shortfall in the customer's bank account, and so on, it will result in a transaction failure. Once the deal fails, all information in the database must remain unchanged before the transaction, such as failure to update user information at the end of the transaction, so that the failed transaction does not affect the state of the database-the inventory information is not updated, the user is not paid, and the order is not generated. Otherwise, the information in the database will be confusing and unpredictable.

Database transactions are the techniques used to ensure the smoothness and predictability of transactions in this case.

ACID Properties for database transactions

Transaction processing ensures that data-oriented resources are not permanently updated unless all operations within the transactional unit are completed successfully. By combining a set of related actions into a unit that is either fully successful or all failed, you can simplify error recovery and make your application more reliable. To be a transaction, a logical unit of work must satisfy the so-called acid (atomicity, consistency, isolation, and durability) attribute:

· of atomic

The transaction must be an atomic unit of work, or all of its data modifications, or none of them executed. Typically, the operations associated with a transaction have common goals and are interdependent. If the system only performs a subset of these operations, the overall goal of the transaction may be compromised. Atomicity eliminates the possibility that the system handles a subset of operations.

· Consistency

When a transaction completes, you must keep all the data in a consistent state. In the related database, all rules must be applied to the modification of the transaction to preserve the integrity of all data. When a transaction ends, all internal data structures, such as a B-tree index or a two-way list, must be correct. Some of the responsibility for maintaining consistency rests with the application developer, who must ensure that all known integrity constraints are enforced by the application. For example, when developing an application for transfer, avoid moving the decimal point during the transfer process.

· Isolation of

Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transaction. The state that the data is in when the transaction views the data, either when another concurrent transaction modifies its previous state, or when another transaction modifies its state, the transaction does not view the data in the middle state. This is called serializable because it can reload the starting data and replay a series of transactions so that the state of the data at the end is the same as that of the original transaction. The highest isolation level is obtained when the transaction is serializable. At this level, the results obtained from a set of transactions that can be executed in parallel are the same as those obtained by running each transaction continuously. Because height isolation limits the number of transactions that can be executed in parallel, some applications reduce isolation levels in exchange for greater throughput.

· Persistence of

After the transaction is completed, its impact on the system is permanent. This modification is maintained even if a fatal system failure occurs.

The responsibility of the DBMS and our task

Enterprise-Class database management systems (DBMS) are responsible for providing a mechanism to ensure the physical integrity of transactions. As for the commonly used SQL Server2000 system, it has the mechanism of locking device isolation transaction, recording device guarantee transaction persistence and so on. Therefore, we do not have to care about the physical integrity of database transactions, but rather on the circumstances in which database transactions are used, the impact of transactions on performance, how transactions are used, and so on.

This article will cover various aspects of manipulating database transactions within the. NET Framework using the C # language.

Experience the transaction mechanism of SQL language

As a large enterprise-level database, SQL Server2000 provides good support for transactions. We can use SQL statements to define, commit, and roll back a transaction.

The SQL code shown below defines a transaction and is named "Mytransaction" (limited to space, this article does not discuss how to write a SQL language program, please refer to the reader's own books):

DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'MyTransaction'
BEGIN TRANSACTION @TranNameGOUSE pubs
GO
UPDATE roysched
SET royalty = royalty * 1.10
WHERE title_id LIKE 'Pc%'
GO
COMMIT TRANSACTION MyTransaction
GO

This uses the SQL Server2000 sample database pubs, which, when committed, will increase the royalties paid for all best-selling computer books by 10%.

Open SQL Server2000 Query Analyzer, select the Pubs database, and then run this program, the results are obvious.

But how do I run it in a C # program? We remember that in ordinary SQL queries, it is generally necessary to assign the query statement to the Salcommand.commandtext attribute, and here, as with normal SQL query statements, assign these statements to the Sqlcommand.commandtext property. One thing to note is that the "go" statement marks the end of the SQL batch, which is needed to write SQL scripts, but is not necessary here. We can write the following procedure to verify this idea:

Transql.csusing System;
Using System.Data;
Using System.Data.SqlClient;
Namespace ASPCN
{
public class Dbtransql
{
file://transactions into SQL Server execution
public void Dotran ()
{
FILE://establishes the connection and opens the
SqlConnection Myconn=getconn (); MyConn.Open ();
SqlCommand mycomm=new SqlCommand ();
Try
{
Mycomm.connection=myconn;
mycomm.commandtext= "DECLARE @TranName VARCHAR (20)";
mycomm.commandtext+= "Select @TranName = ' Mytransaction '";
mycomm.commandtext+= "BEGIN TRANSACTION @TranName";
mycomm.commandtext+= "use pubs";
mycomm.commandtext+= "UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id like ' pc% '";
mycomm.commandtext+= "COMMIT TRANSACTION mytransaction";
Mycomm.executenonquery ();
}
catch (Exception err)
{
throw new ApplicationException ("Transaction operation error, System Information:" +err. message);
}
Finally
{
Myconn.close ();
}
}
file://Get Data connections
Private SqlConnection Getconn ()
{
String strsql= "Data source=localhost;integrated security=sspi;user id=sa;password=";
SqlConnection myconn=new SqlConnection (strSQL);
return myconn;
}
}
public class Test
{
public static void Main ()
{
Dbtransql trantest=new dbtransql ();
Trantest.dotran ();
Console.WriteLine (transaction processing completed successfully.) ");
Console.ReadLine ();
}
}
}

Notice that the SqlCommand object Mycomm, its CommandText property is just a concatenation of the preceding SQL code string, of course, where the "Go" statement has been completely removed. This statement is like a normal query, where the program submits the SQL text to the DBMS for processing and then receives the returned results (if any results are returned).

Naturally, we finally saw the output "transaction completed successfully", and then using Enterprise Manager to view the roysched table of the pubs database, the value of all title_id fields in the Royalty field of the book beginning with "PC" increased by 0.1 times times.

Here, instead of using the ado.net transaction mechanism, we simply execute the SQL statement that executes the transaction as a normal query, and therefore, in fact, the transaction does not use the related attributes of. NET at all.