Principles and Practices of database transactions (zz)

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 set of related operations into a unit that either succeeds or fails, you can simplify error recovery and make the application more 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.

The following SQL code defines a transaction and is named "MyTransaction" (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.

　Another extreme Error

New users with confidence may be excited by some of their knowledge, as do prospective developers who are new to database transaction processing, he is eager to apply the transaction mechanism to every statement in every module of his data processing program. Indeed, the transaction mechanism looks so attractive-concise, wonderful, and practical, of course I want to use it to avoid all possible errors-I even want to use transactions to wrap up my data operations from start to end.

Let's take a look. Next I will start from creating a database:

Using 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; MyTran = myConn. BeginTransaction (); File: // bind the following connection and transaction object MyComm. Connection = myConn; MyComm. Transaction = myTran; File: // try to create a database TestDB MyComm. CommandText = "CREATE database TestDB "; MyComm. ExecuteNonQuery (); File: // submit the transaction MyTran. Commit (); } 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 (); } } } //---------------

Unprocessed exception: system. Data. sqlclient. sqlexception: the create database statement is not allowed in Multi-statement transactions.

At System. Data. SqlClient. SqlCommand. ExecuteNonQuery () At Aspcn. DbTran. DoTran () At Aspcn. Test. Main ()

Note: The following SQL statements cannot appear in transactions:

ALTER DATABASE	Modify Database
BACKUP LOG	Backup log
CREATE DATABASE	Create a database
DISK INIT	Create a database or Transaction Log Device
DROP DATABASE	Delete Database
DUMP TRANSACTION	Dumping transaction logs
LOAD DATABASE	Load database backup copies
LOAD TRANSACTION	Load transaction log backup copies
RECONFIGURE	Update the current configuration (config_value column in The sp_configure result set) value of the configuration option changed using the sp_configure system stored procedure.
RESTORE DATABASE	Restore the Database BACKUP Using the BACKUP command
RESTORE LOG	Restore the log BACKUP using the BACKUP command
UPDATE STATISTICS	Updates the key value distribution information of one or more statistical groups (sets) in the specified table or index view.

In addition to these statements, you can use any valid SQL statements in your database transactions.

 

　　Transaction rollback

One of the four features of a transaction is atomicity. It means that a transaction composed of a specific operation sequence is either completely completed or not done. If an unknown Unexpected error occurs during transaction processing, how can we ensure the atomicity of the transaction? When a transaction is aborted, you must perform a rollback operation to eliminate the impact of the executed operation on the database.

In general, it is better to use rollback for exception handling. Previously, we have obtained a database update program and verified its correctness. Modify it a little bit and we can get:

 

// RollBack. cs Using 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; File: // create a transaction MyTran = myConn. BeginTransaction (); 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; Try { File: // locate the pubs Database Mycomm. commandtext = "use pubs "; Mycomm. executenonquery (); 　　　 Mycomm. commandtext = "Update roysched set royalty = royalty * 1.10 where title_id like 'pc % '"; Mycomm. executenonquery (); File: // The following statements used to create a database are used to create an error. Mycomm. commandtext = "create database testdb "; Mycomm. executenonquery (); Mycomm. commandtext = "Update maid set maid = maid * 1.20 where title_id like 'ps % '"; Mycomm. executenonquery (); File: // submit the transaction Mytran. Commit (); } Catch (exception ERR) { Mytran. rollback (); Console. Write ("transaction operation error, rolled back. System Information: "+ err. Message ); } } 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 (); } } }

First, we create an error in the middle-use the create database statement described earlier. Then, the Catch Block for exception handling contains the following statement:

Mytran. rollback ();

When an exception occurs, the program executes the stream to jump to the catch block. The first statement is this statement, which rolls back the current transaction. In this section, we can see that before the create database, there is already an operation to update the database -- the value of the royalty field of all books whose title_id field starts with "PC" in the royal sched table of the pubs database is increased by 0.1 times. However, the rollback caused by exceptions does not happen to the database. Therefore, the rollback () method maintains Database Consistency and atomicity of transactions.

 

　　Use storage points

Transactions are only the worst-case safeguard measures. In fact, at ordinary times, the operating reliability of the system is quite high, and errors rarely occur. Therefore, it is too costly to check the validity of each transaction before it is executed-this time-consuming check is unnecessary in most cases. We have to find another way to improve efficiency.

Transaction storage points provide a mechanism for rolling back some transactions. Therefore, we do not have to check the validity of the update before the update. Instead, we can preset a storage point. After the update, if there is no error, continue to execute, otherwise, roll back to the storage point before the update. This is the role of a storage point. It should be noted that the update and rollback costs are very high, only when the error is very unlikely and the cost of checking the update validity in advance is relatively high, the storage point is very effective.

When programming using the. net Framework, you can easily define transaction storage points and roll back to specific storage points. The following statement defines a storage point "NoUpdate ":

MyTran. Save ("NoUpdate ");

When you create a storage point with the same name in the program, the newly created storage point replaces the original storage point.

When you roll back a transaction, you only need to use an overload function of the Rollback () method:

MyTran. Rollback ("NoUpdate ");

The following section describes how and when to roll back to a storage point:

 

Using 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; File: // create a transaction MyTran = myConn. BeginTransaction (); 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; Try { MyComm. CommandText = "use pubs "; MyComm. ExecuteNonQuery (); MyTran. Save ("NoUpdate "); 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) { File: // update error. rollback to the specified storage point MyTran. Rollback ("NoUpdate "); Throw new ApplicationException ("transaction operation error, system information:" + err. Message ); } } 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, in this program, the chance of an update being invalid is very small, and the cost of verifying its validity before the update is quite high, so we do not need to verify its validity before the update, it combines the transaction storage point mechanism to ensure data integrity.

 

　　Isolation level concept

Enterprise-level databases can cope with thousands of concurrent accesses every second, resulting in concurrency control problems. According to the database theory, due to concurrent access, the following unexpected problems may occur at unpredictable times:

　　Dirty read: Read contains uncommitted data. For example, transaction 1 changes a row. Transaction 2 reads changed rows before transaction 1 commits changes. If transaction 1 rolls back the change, transaction 2 reads the rows that have never existed logically.

　　Cannot be read repeatedly: When a transaction reads the same row more than once, and a separate transaction modifies the row between two (or multiple) reads, because the row is modified between multiple reads in the same transaction, different values are generated for each read, causing inconsistency.

　　Phantom: Insert a new row or delete an existing row in the range of rows read by another task that has not committed its transaction. Tasks with uncommitted transactions cannot repeat their original reads due to changes to the number of rows in the range.

As you think, the root cause of these situations is that there is no mechanism to avoid cross-access during concurrent access. Isolation-level settings are designed to avoid these situations. The level at which the transaction is prepared to accept inconsistent data is called the isolation level. The isolation level is the degree to which a transaction must be isolated from other transactions. Low isolation levels can increase concurrency, but the cost is to reduce data correctness. On the contrary, high isolation levels can ensure data correctness, but may have a negative impact on concurrency.

Depending on the isolation level, DBMS provides different mutex guarantees for parallel access. The SQL Server database provides four isolation levels: uncommitted read, committed read, Repeatable read, and serializable read. These four isolation levels can ensure the concurrency data integrity to varying degrees:

 

Isolation level	Dirty read	Cannot be read repeatedly	Phantom
Uncommitted read	Yes	Yes	Yes
Submit read	No	Yes	Yes
Repeatable read	No	No	Yes
Serializable read	No	No	No

It can be seen that "serializable read" provides the highest level of isolation, and the execution result of concurrent transactions will be exactly the same as that of serial execution. As mentioned above, the highest level of isolation means the lowest level of concurrency. Therefore, at this isolation level, the database service efficiency is actually relatively low. Although serializability is important for transactions to ensure the correctness of data in the database during all time periods, many transactions do not always require full isolation. For example, multiple authors work in different chapters of the same book. New chapters can be submitted to the project at any time. However, the author cannot make any changes to an edited chapter without the approval of the editor. In this way, despite the existence of unedited new chapters, the editors can still ensure the correctness of the book project at any time. Editors can view previously edited chapters and recently submitted chapters. In this way, several other isolation levels also have their meanings.

In the. NET Framework, the transaction isolation level is defined by the enumeration system. Data. isolationlevel:

[Flags] [Serializable] Public enum IsolationLevel

Its members and meanings are as follows:

Members	Meaning
Chaos	You cannot rewrite the pending changes in a transaction with a higher isolation level.
ReadCommitted	Keep the shared lock when reading data to avoid dirty reading, but you can change the data before the transaction ends, resulting in non-repeated reading or phantom data.
ReadUncommitted	Dirty reads are allowed, meaning that shared locks are not published or exclusive locks are not accepted.
RepeatableRead	Lock all data used in the query to prevent other users from updating the data. Prevents repeated reads, but still supports phantom rows.
Serializable	Place a range lock on the dataset to prevent other users from updating rows or inserting rows into the dataset before the transaction is completed.
Unspecified	The specified isolation level is being used but cannot be determined.

Obviously, the four isolation levels of the database are mapped here.

By default, SQL server uses readcommitted (committed read) isolation level.

The last point about the isolation level is that if you change the isolation level during transaction execution, the subsequent names are all executed at the latest isolation level-the change at the isolation level takes effect immediately. With this, you can use the isolation level more flexibly in your transactions to achieve higher efficiency and concurrency security.

　　Advice

Undoubtedly, introducing transaction processing is a good way to deal with possible data errors, however, we should also see the huge cost of transaction processing-the CPU time and storage space required for storage points, rollback, and concurrency control.