C # principles and practices of database transactions (1)

DatabaseTransactions
A data warehouse transaction is 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 is successful, and all database information related to the transaction is also 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 user information is updated in the last step, 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 information in the database 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
Event 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 application Program More reliable. To become a transaction, a logical unit of work must meet the so-called acid (atomicity, consistency, isolation, and durability) attributes:
Original child
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 depend on each other. 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 a task is completed, all data must be in the same state. 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 account transfer, you should avoid moving any decimal point during the transfer process.
Isolation
Modifications made by a concurrent firm must be separated 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
Enterprise-level database Management All systems (DBMS) have the responsibility to provide a mechanism to ensure the physical integrity of transactions. Common SQL For the Server2000 system, it has mechanisms such as locking device isolation transactions and recording devices to ensure transaction persistence. Therefore, we do not need to care about the physical integrity of database transactions, but what conditions should we focus on 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.

Verify the transaction mechanism of the 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.
SQLCodeA transaction is defined and named "mytransaction ":

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 does one 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: // place the transaction to SQL Server Executed 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 mainly introduces the system. Data. sqlclient. sqltranscation class. In the following section, except for the special note, 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. We can also see the transcation attribute of the sqlcommand object. It is easy to think that the newly created sqltransaction object should be associated with it.