C # transactions

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

Ii. Use transactions in the Stored Procedure

Use DBName

Go

Create Procedure ProcedureName

(@ Parameter1 Type,

@ Parameter2 Type,

........................................)

As

Set NoCount On

Declare @ currentError int

Begin Transaction:

UPDATE maid SET maid = maid * 1.10 WHERE title_id LIKE 'pc %'

Set @ currentError = @ RowCount

If @ currentError = 0

Begion

GoTo ErrorHandle:

End

Commit TranSaction

Set Nocount Off

Return @ currentError

ErrorHandle:

RoolBack Transaction

Set Nocount off

Return @ currentError

Go

Iii. Use stored procedures in Batch Processing

// 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 ();
}
}
}

Use transactions in 4.ado.net

In the. net Framework, there are 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

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. The SqlTransaction class does not have a public constructor. before the transaction is processed, A SqlTransaction type variable is required. It is also necessary to associate the variable with the Transcation attribute of the SqlCommand class, however, 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 ();

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 ");

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.

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

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.