The principle and practice of C # database transaction

Data | database

What is a database transaction

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

The payment process includes at least the following database operations, assuming a single transaction for online shopping:

· 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.

Normally, 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 transaction is the technology 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. 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, it must keep all 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 completion of the transaction, 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

Here we use the SQL Server2000 sample database of 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 to run 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, all the title_id fields with a book beginning with "PC" increased by 0.1 times times the value of the Royalty field.

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.
Understanding transaction mechanisms in. Net

As you know, there are two namespaces (namespace) in the. NET Framework for applications interacting with the database system: System.Data.SqlClient and System.Data.OleDb. The former is designed to connect to a Microsoft company's own SQL Server database, which can be adapted to a variety of different databases. Both namespaces contain classes dedicated to managing database transactions, respectively, the System.Data.SqlClient.SqlTranscation class and the System.Data.OleDb.OleDbTranscation class.

Like their names, most of these two classes are functionally the same, and the main difference is their connection mechanism, which provides a set of objects that call SQL Server directly, and the latter uses native OLE DB to enable data access. In fact, ado.net transactions are handled entirely within the database and are not supported by the Microsoft Distributed Transaction Coordinator (DTC) or any other transactional mechanism. This article will mainly introduce the System.Data.SqlClient.SqlTranscation class, the following paragraph, in addition to the special note, will use the System.Data.SqlClient.SqlTranscation class.
Opening and submitting of transactions

Now that we have a clear idea of the concept and principles of the transaction, and as a C # developer who already has some basics, we are already familiar with some of the key points in writing database interactions, namely, using the open () method of the object of the SqlConnection class to establish a connection to the database server. The connection is then assigned to the Connection property of the SqlCommand object, and the SQL statement to execute is assigned to its CommandText property, so the database can be manipulated through the SqlCommand object. Of course we need to define an object of the SqlTransaction type for the transaction handler we are going to write. And seeing the transcation attribute of the SqlCommand object, it's easy to think that the new SqlTransaction object should be associated with it.

Based on the above understanding, we will begin to write our first transaction handler. We can skillfully write the following procedure:

Dotran.csusing System;
Using System.Data;
Using System.Data.SqlClient;
Namespace ASPCN
{
public class DbTran
{
file://Perform transaction processing
public void Dotran ()
{
FILE://establishes the connection and opens the
SqlConnection Myconn=getconn ();
MyConn.Open ();
SqlCommand mycomm=new SqlCommand ();
SqlTransaction mytran=new sqltransaction ();
Try
{
Mycomm.connection=myconn;
Mycomm.transaction=mytran;

file://Navigate to the pubs database
mycomm.commandtext= "use pubs";
Mycomm.executenonquery ();

file://Update Data
FILE://will all the computer class books
mycomm.commandtext= "UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id like ' pc% '";
Mycomm.executenonquery ()//COMMIT TRANSACTION
Mytran.commit ();
}
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 ()
{
DbTran trantest=new DbTran ();
Trantest.dotran ();
Console.WriteLine (transaction processing completed successfully.) ");
Console.ReadLine ();
}
}
}

Obviously, this program is very simple, we are very confident to compile it, but, the unexpected results of our sense of achievement immediately vanish:

error CS1501: Overload "SqlTransaction" method does not get "0" parameter

What is the reason? Notice the code we initialized:

SqlTransaction mytran=new sqltransaction ();

Obviously, the problem is here, in fact, the SqlTransaction class doesn't have a public constructor, and we can't create a new sqltrancaction type of variable like this. It is necessary to have a variable of type sqltransaction before transaction processing, and it is essential to associate the variable with the Transcation property of the SqlCommand class, but the initialization method is rather 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 type sqltransaction. After the BeginTransaction () method is invoked, all SQL statement execution actions based on the data connection object are considered to be part of the transaction Mytran. You can also specify transaction isolation levels and transaction names in the parameters of the method, such as:

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

The concept of isolation level we'll explore in the following sections, where we just need to remember how a transaction is started and associated with a particular data link.

Don't rush to understand what our affairs have done, see this line:

Mytran.commit ();

Yes, that's how the transaction is submitted. After the statement is executed, all database operations of the transaction take effect and persist for the persistence mechanism of the database transaction-even if the system has a fatal error after that, the transaction's impact on the database does not disappear.

We can get the following code for the above program (to save space, the repetition has been omitted, please refer to the previous text):

DoTran.cs ... ｝

file://Perform transaction processing
public void Dotran ()
{
FILE://establishes the connection and opens the
SqlConnection Myconn=getconn ();
MyConn.Open ();
SqlCommand mycomm=new SqlCommand ();

File://SqlTransaction mytran=new sqltransaction ();
file://Note that the SqlTransaction class has no exposed constructors

SqlTransaction Mytran;

FILE://Create a transaction
Mytran=myconn.begintransaction ();
Try
{
file://Since then, data operations based on the connection are considered part of the transaction
file://the following binding connection and transaction objects
Mycomm.connection=myconn;
Mycomm.transaction=mytran; file://Navigate to the pubs database
mycomm.commandtext= "use pubs";
Mycomm.executenonquery ()//Update data
FILE://will all the computer class books
mycomm.commandtext= "UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id like ' pc% '";
Mycomm.executenonquery ();

file://COMMIT 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 the transaction. The next step we have to consider is what we can do and what not to do in a business.

Another error that goes to extremes

Confident novices may revel in some of the knowledge they have mastered, and the would-be developers who have just contacted database transaction processing are also complacent about applying the transaction mechanism to every single module of his data handler. Indeed, the transaction mechanism seems so tempting--simple, wonderful and practical--and I certainly want to use it to avoid all possible mistakes--and I even want to wrap my data operations from start to finish with my business.

Look, I'm going to start with creating a database:

Using System;
Using System.Data;
Using System.Data.SqlClient;

Namespace ASPCN
{
public class DbTran
{
file://Perform transaction processing
public void Dotran ()
{
FILE://establishes the connection and opens the
SqlConnection Myconn=getconn ();
MyConn.Open ();

SqlCommand mycomm=new SqlCommand ();
SqlTransaction Mytran;

Mytran=myconn.begintransaction ();

file://the following binding connection and transaction objects
Mycomm.connection=myconn;
Mycomm.transaction=mytran;

FILE://attempted to create a database TestDB
mycomm.commandtext= "CREATE database TestDB";
Mycomm.executenonquery ();

file://COMMIT Transaction
Mytran.commit ();
}

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 ()
{
DbTran trantest=new DbTran ();
Trantest.dotran ();
Console.WriteLine (transaction processing completed successfully.) ");
Console.ReadLine ();
}
}
}

//---------------

Unhandled exception: System.Data.SqlClient.SqlException: CREATE DATABASE statement is not allowed within a multiple-statement transaction.

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

Note that SQL statements such as the following are not allowed to appear in a transaction:

ALTER DATABASE modifies databases
Backup LOG back up logs
Create DATABASE creating databases
DISK INIT Create a database or transaction log device
Drop DATABASE Delete Databases
Dump TRANSACTION DUMP TRANSACTION log
Load database mount a backup copy of the databases
Load TRANSACTION mount transaction log backup copy
Reconfigure updates the current configuration of the configuration option that uses the sp_configure system stored procedure (sp_configure config_value column in the result set) value.
Restore DB restores database backups made using the backup command
The restore log restores log backups made using the backup command
Update STATISTICS information that has key value distributions for one or more statistical groups (collections) in the specified table or indexed view

In addition to these statements, you can use any legitimate SQL statement in your database transaction.
Transaction rollback

One of the four characteristics of a transaction is atomicity, meaning that a transaction consisting of a particular sequence of operations is either completed or not done. How do you guarantee the atomicity of a transaction if an unexpected and unexpected error occurs during transaction processing? When a transaction is aborted, a rollback operation must be performed in order to eliminate the impact of the operation already performed on the database.

In general, it is a good idea to use rollback action in exception handling. Before, we have a program to update the database, and verify its correctness, slightly modified, you can get:

RollBack.cs
Using System;
Using System.Data;
Using System.Data.SqlClient;

Namespace ASPCN
{
public class DbTran
{
file://Perform transaction processing
public void Dotran ()
{
FILE://establishes the connection and opens the
SqlConnection Myconn=getconn ();
MyConn.Open ();

SqlCommand mycomm=new SqlCommand ();
SqlTransaction Mytran;

FILE://Create a transaction
Mytran=myconn.begintransaction ();
file://Since then, data operations based on the connection are considered part of the transaction
file://the following binding connection and transaction objects
Mycomm.connection=myconn;
Mycomm.transaction=mytran;

Try
{
file://Navigate to 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://make an error using the statement that creates the database below
mycomm.commandtext= "Create database TestDB";
Mycomm.executenonquery ();

mycomm.commandtext= "UPDATE roysched SET royalty = royalty * 1.20 WHERE title_id like ' ps% '";
Mycomm.executenonquery ();

file://COMMIT Transaction
Mytran.commit ();
}
catch (Exception err)
{
Mytran.rollback ();
Console.Write ("Transaction operation error, rolled back.") System Information: "+err." message);
}
}

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 ()
{
DbTran trantest=new DbTran ();
Trantest.dotran ();
Console.WriteLine (transaction processing completed successfully.) ");
Console.ReadLine ();
}
}
}

First, we artificially made a mistake in the middle--using the CREATE DATABASE statement we talked about earlier. Then, in the catch block for exception handling, you have the following statement:

Mytran.rollback ();

When an exception occurs, the program execution stream jumps to the catch block, which is the first execution of the statement, which rolls back the current transaction. As you can see in this program, there is already an operation to update the database before the create DB--0.1 times times the value of the Royalty field in the book that starts all the title_id fields in the roysched table of the pubs database with "PC". However, a rollback caused by an exception has not occurred for the database. Thus, the Rollback () method maintains the consistency of the database and the atomicity of the transaction.

Using storage points

The business is only a worst-case safeguard, in fact, the operating reliability of the system is very high, errors rarely occur, so it is too expensive to check the validity of each transaction before it is executed--most of the time this time-consuming check is unnecessary. We have to think of another way to improve efficiency.

The transaction storage point provides a mechanism for rolling back part of the transaction. Therefore, instead of checking the validity of the update before updating, we preset a storage point that, after the update, continues if there are no errors, or rolls back to the storage point before the update. This is where the storage point works. It is important to note that updates and rollbacks are costly, and that using a storage point is very effective only if the likelihood of encountering an error is small and the cost of checking the validity of the update is relatively high.

When programming with the. NET Framework, you can easily define transaction storage points and rollback 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 your program, the newly created storage point replaces the existing storage points.

When rolling back a transaction, you simply use an overloaded function of the rollback () method:

Mytran.rollback ("Noupdate");

The following procedure illustrates the method and timing of rolling back to the storage point:
Using System;
Using System.Data;
Using System.Data.SqlClient;

Namespace ASPCN
{
public class DbTran
{
file://Perform transaction processing
public void Dotran ()
{
FILE://establishes the connection and opens the
SqlConnection Myconn=getconn ();
MyConn.Open ();

SqlCommand mycomm=new SqlCommand ();
SqlTransaction Mytran;

FILE://Create a transaction
Mytran=myconn.begintransaction ();
file://Since then, data operations based on the connection are considered part of the transaction
file://the following binding connection and transaction objects
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://COMMIT Transaction
Mytran.commit ();
}
catch (Exception err)
{
file://update error, rollback to specified storage point
Mytran.rollback ("Noupdate");
throw new ApplicationException ("Transaction operation error, System Information:" +err. message);
}
}

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 ()
{
DbTran trantest=new DbTran ();
Trantest.dotran ();
Console.WriteLine (transaction processing completed successfully.) ");
Console.ReadLine ();
}
}
}

Obviously, in this program, the probability of invalid update is very small, and the cost of validating its validity before updating is quite high, so we do not need to validate its validity before updating, but combine the storage point mechanism of transaction to provide the guarantee of data integrality.

Concept of Isolation Level

Enterprise-class databases can cope with thousands of concurrent accesses per second, resulting in concurrent control problems. As the database theory shows, due to concurrent access, there are several predictable problems that can arise at unpredictable times:

Dirty reads: Reads that contain uncommitted data. For example, a transaction 1 changed a row. Transaction 2 reads the changed row before transaction 1 commits the change. If transaction 1 rolls back the change, transaction 2 reads a row that has never been logically present.

Non-repeatable reads: When a transaction reads the same row more than once, and a separate transaction modifies the row between two (or more) reads, because the row is modified between multiple reads within the same transaction, each read generates a different value, which raises an inconsistency.

Illusion: A task that inserts new rows or deletes existing rows in a range of rows previously read by another task that has not yet committed its transaction. A task with an uncommitted transaction cannot repeat its original read because of changes in the number of rows in the range.

As you can imagine, the root cause of these situations is that there is no mechanism to avoid cross access when concurrent access occurs. The isolation level is set up to prevent these situations from happening. The level at which a transaction prepares 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. Lower isolation levels can increase concurrency, but at the cost of reducing data correctness. Conversely, a higher isolation level ensures that data is correct, but can have a negative impact on concurrency.

Depending on the isolation level, the DBMS provides a different mutex guarantee for concurrent access. In a SQL Server database, there are four isolation levels: uncommitted read, commit read, repeatable read, serializable read. These four isolation levels guarantee concurrent data integrity to varying degrees:
Isolation level dirty Read not repeatable read Phantom
Not submitted read Yes Yes Yes
Submit Read No Yes Yes
REPEATABLE Read No No Yes
Serializable Read No no No

As can be seen, "serializable" provides the highest level of isolation, at which point the execution results of concurrent transactions will be exactly the same as serial execution. As mentioned earlier, the highest level of isolation means the lowest level of concurrency, so the efficiency of the database service is actually relatively low at this isolation level. Although serializable is important for transactions to ensure that the data in the database is correct at all times, many transactions do not always require complete 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, for chapters that have already been edited, the author cannot make any changes to this section without the approval of the editor. In this way, although there are new chapters that are not edited, editors can still ensure the correctness of the book item at any time. Editors can view previously edited chapters and recently submitted chapters. In this way, several other isolation levels also have their meaning.

In the. NET Framework, the isolation level of a transaction is defined by the enumeration System.Data.IsolationLevel: £

[Flags]
[Serializable]
public enum IsolationLevel

Its members and the corresponding meanings are as follows:

Member meaning
Chaos cannot overwrite pending changes in a higher isolation level transaction.
ReadCommitted maintains a shared lock while reading data to avoid dirty reads, but can change the data before the transaction ends, resulting in unreadable read or phantom data.
ReadUncommitted can be dirty-read, meaning that no shared locks are published and exclusive locks are not accepted.
RepeatableRead locks are placed on all data used in the query to prevent other users from updating the data. Prevents unreadable reads, but can still have phantom rows.
Serializable places a range lock on the dataset to prevent other users from updating rows or inserting rows into the dataset before the transaction completes.
Unspecified is using a different isolation level than the specified isolation level, but the level cannot be determined.

The four isolation levels of the database are mapped here.

By default, SQL Server uses the readcommitted (commit read) isolation level.

The last thing about the isolation level is that if you change the isolation level during the execution of a transaction, subsequent names are executed at the most recent isolation level--the change in the isolation level takes effect immediately. With this, you can use isolation levels more flexibly in your transactions to achieve higher efficiency and concurrent security.

The last Advice

Undoubtedly, the introduction of transactions is a good way to deal with possible data errors, but you should also see the huge cost of transaction processing-the CPU time and storage space needed for storage points, rollback, and concurrency control.