C # principles and practices of database transactions (I)

What is a database transaction?

Database transactions are a series of operations performed as a single logical unit of work.

Imagine a transaction for online shopping. the payment process includes at least the following database operations:

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

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

· Generate an order and save it to the database

· Update user information, such as the number of shopping items

Under normal circumstances, these operations will go smoothly, the transaction will be successful, and all database information related to the transaction will be updated. However, if an error occurs in any part of the process, for example, an exception occurs when updating the inventory information, or the customer's bank account has insufficient deposits, the transaction will fail. Once a transaction fails, all the information in the database must remain unchanged before the transaction. For example, if the last step fails to update the user information, the transaction fails, make sure that the failed transaction does not affect the database status-the inventory information is not updated, the user has not paid, and the order has not been generated. Otherwise, the database information will be messy and unpredictable.

Database transactions are a technology used to ensure the stability and predictability of transactions in such circumstances.

ACID properties of database transactions

Transaction Processing ensures that data-oriented resources are not updated permanently unless all operations in the transaction unit are successfully completed. By combining a group of related operations into a unit that either succeeds or fails, you can simplify error recovery and make the applicationProgramMore reliable. To become a transaction, a logical unit of work must meet the so-called acid (atomicity, consistency, isolation, and durability) attributes:

·Atomicity

A transaction must be an atomic unit of work. modifications to its data must either be performed in all or not. Generally, operations associated with a transaction share a common goal and are mutually dependent. If the system executes only one subset of these operations, the overall goal of the transaction may be broken. Atomicity eliminates the possibility of a subset of system processing operations.

·Consistency

When the transaction is completed, all data must be consistent. In related databases, all rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures (such as B-tree indexes or two-way linked lists) must be correct. Some maintenance consistency responsibilities are borne by application developers who must ensure that the application has enforced all known integrity constraints. For example, when developing an application for transfer, do not move any decimal point during transfer.

·Isolation

Modifications made by a concurrent firm must be isolated from those made by any other concurrent firm. The status of the data when the transaction is viewing the data is either the status before the transaction is modified or the status after the transaction is modified. The transaction does not view the data in the intermediate status. This is called serializability because it can reload the starting data and replays a series of transactions so that the State at the end of the data is the same as that of the original transaction execution. When a transaction is serializable, the highest isolation level is obtained. At this level, the results obtained from a group of parallel transactions are the same as those obtained by running each firm consecutively. Since high isolation limits the number of transactions that can be executed in parallel, some applications reduce the isolation level in exchange for greater throughput.

·Durability

After the transaction is completed, its impact on the system is permanent. This modification will remain even if a fatal system failure occurs.
DBMS responsibilities and our tasks

All enterprise-level database management systems (DBMS) have the responsibility to provide a mechanism to ensure the physical integrity of transactions. For the commonly used SQL Server2000 system, it has mechanisms such as locking device isolation transactions and recording devices to ensure transaction persistence. Therefore, we do not have to worry about the physical integrity of database transactions, but should focus on the use of database transactions, the impact of transactions on performance, and how to use transactions.

This document describes how to use C # To manipulate database transactions in the. NET Framework.
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.

SQLCodeDefine a transaction and name it "mytransaction" (for space limitations, this article does not discuss how to compile an SQL language program. Please refer to the relevant books ):

Declare @ tranname varchar (20) Select @ tranname = 'mytransaction' Begin transaction @ trannamegouse pubs Go Update roysched Set maid = maid * 1.10 Where title_id like 'pc %' Go Commit transaction mytransaction Go

The example database pubs that comes with SQL Server2000 is used here. After the transaction is committed, the royalty paid for all the best-selling computer books will be increased by 10%.

Open the SQL Server Query analyzer, select the pubs database, and then run this program. The result is obvious.

But how to run it in the C # program? We remember that in normal SQL queries, we usually need to assign the query statement to salcommand. commandtext attribute. These statements are assigned to sqlcommand just like common SQL query statements. commandtext. Note that the "go" statement indicates the end of SQL batch processing. It is required to write SQL scripts, but it is not necessary here. We can write the following program to verify this idea:

// Transql. csusing system; Using system. Data; Using system. Data. sqlclient; Namespace aspcn { Public class dbtransql { File: // put the transaction into SQL Server for execution Public void dotran () { File: // create a connection and open it 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 connection 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 has been completed successfully. "); Console. Readline (); } } }

Note that the sqlcommand object mycomm only connects the preceding SQL code strings. Of course, all the "go" statements have been removed. This statement is like a normal query. The program actually submits the SQL text to the DBMS for processing, and then receives the returned results (if any ).

Naturally, we finally see the output "transaction processing has been completed successfully", and then use the Enterprise Manager to view the pubs database's royal sched table, the value of the royalty field of all books whose title_id field starts with "PC" is increased by 0.1 times.

Here, we do not use the transaction processing mechanism of ado.net, but simply execute the SQL statement that executes the transaction as a normal query. Therefore, in fact, this transaction is completely useless.. net.
Understand the transaction mechanism in. net

As you know, in the. NET Framework, there are mainly two namespaces used for application interaction with the database system: system. Data. sqlclient and system. Data. oledb. The former is used to connect Microsoft's own SQL Server database, while the latter can adapt to a variety of different databases. Both namespaces contain classes specifically used to manage database transactions, namely system. Data. sqlclient. sqltranscation and system. Data. oledb. oledbtranscation.

Like their names, most of the functions of these two classes are the same. The main difference between the two is their connection mechanism. The former provides a set of objects that directly call SQL Server, the latter uses the local ole db to enable data access. In fact, ado.net transactions are processed completely within the database and are not supported by Microsoft Distributed Transaction Processing Coordinator (DTC) or any other transactional mechanism. This article will mainly introduce the system. Data. sqlclient. sqltranscation class. In the following sections, except for special instructions, the system. Data. sqlclient. sqltranscation class will be used.
Start and submit a transaction

Now we are very familiar with the concept and principle of transactions. As a C # developer who already has some basic knowledge, we are familiar with the key points of writing database interaction programs, that is, use the open () method of the sqlconnection object to establish a connection with the database server, and then assign the connection to the connection attribute of the sqlcommand object, grant the commandtext attribute of the SQL statement to be executed, and then perform database operations through the sqlcommand object. For the transaction processing program we will write, of course, a sqltransaction type object needs to be defined. When we see the transcation attribute of the sqlcommand object, it is easy to think that the newly created sqltransaction object should be associated with it.

Based on the above understanding, we will start to write our first transaction processing program. We can write the following program skillfully:

// Dotran. csusing system; Using system. Data; Using system. Data. sqlclient; Namespace aspcn { Public class dbtran { File: // execute Transaction Processing Public void dotran () { File: // create a connection and open it Sqlconnection myconn = getconn (); Myconn. open (); Sqlcommand mycomm = new sqlcommand (); Sqltransaction mytran = new sqltransaction (); Try { Mycomm. Connection = myconn; Mycomm. Transaction = mytran; File: // locate the pubs Database Mycomm. commandtext = "use pubs "; Mycomm. executenonquery (); File: // update data File: // convert all computer books Mycomm. commandtext = "Update roysched set royalty = royalty * 1.10 where title_id like 'pc % '"; Mycomm. executenonquery (); // submit the transaction Mytran. Commit (); } Catch (exception ERR) { Throw new applicationexception ("transaction operation error, system information:" + err. Message ); } Finally { Myconn. Close (); } } File: // get data connection 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 () { Dbtran trantest = new dbtran (); Trantest. dotran (); Console. writeline ("transaction processing has been completed successfully. "); Console. Readline (); } } }

Obviously, this program is very simple and we are very confident in compiling it. However, unexpected results make our sense of accomplishment disappear:

Error cs1501: the "0" parameter is not obtained when the "sqltransaction" method is overloaded.

Why? We noticed the initialization code:

Sqltransaction mytran = new sqltransaction ();

Obviously, the problem lies here. In fact, the sqltransaction class does not have a public constructor, so we cannot create a variable of the sqltrancaction type. Before the transaction is processed, a variable of the sqltransaction type is required. It is also necessary to associate the variable with the transcation attribute of the sqlcommand class, but the initialization method is more special. When initializing the sqltransaction class, you need to use the begintranscation () method of the sqlconnection class:

Sqltransaction mytran; mytran = myconn. begintransaction ();

This method returns a variable of the sqltransaction type. After the begintransaction () method is called, all SQL statement execution actions based on the data connection object will be considered as part of the transaction mytran. You can also specify the transaction isolation level and transaction name in the parameters of this method, for example:

Sqltransaction mytran; Mytran = myconn. begintransaction (isolationlevel. readcommitted, "sampletransaction ");

We will discuss the concept of isolation level in subsequent content. Here we only need to remember how a transaction is started and associated with a specific data link.

Do not rush to understand what we have done in our transactions, and see this line:

Mytran. Commit ();

Yes, this is the transaction commit method. After the statement is executed, all database operations of the transaction will take effect and are maintained by the persistence mechanism of the Database Transaction-even if a fatal error occurs after this, the impact of the transaction on the database will not disappear.

After modifying the above program, we can get the following code (to save space, the repetition has been omitted, please refer to the previous article ):

// Dotran. CS ......} File: // execute Transaction Processing Public void dotran () { File: // create a connection and open it Sqlconnection myconn = getconn (); Myconn. open (); Sqlcommand mycomm = new sqlcommand (); File: // sqltransaction mytran = new sqltransaction (); File: // note that the sqltransaction class has no public Constructor Sqltransaction mytran; File: // create a transaction Mytran = myconn. begintransaction (); Try { File: // from then on, data operations based on the connection are considered as part of the transaction. File: // bind the following connection and transaction object Mycomm. Connection = myconn; Mycomm. Transaction = mytran; file: // locate the pubs Database Mycomm. commandtext = "use pubs "; Mycomm. executenonquery (); // update data File: // convert all computer books Mycomm. commandtext = "Update roysched set royalty = royalty * 1.10 where title_id like 'pc % '"; Mycomm. executenonquery (); File: // submit the transaction Mytran. Commit (); } Catch (exception ERR) { Throw new applicationexception ("transaction operation error, system information:" + err. Message ); } Finally { Myconn. Close (); } } ......

So far, we have only mastered how to start and commit transactions. Next, we must consider what can be done in a transaction or what cannot be done.

ArticleReference from: http://www.ddvip.net/program/c-/index7/9.htm