Ado. NET Learning notes-transaction processing

1. Definition of a transactionA transaction is a work that cannot be divided and must be performed concurrently. If all the work that makes up the transaction is performed, the transaction executes successfully, and if one of the items is not executed, the transaction is rolled back to its initial state and the execution fails. A transaction contains 4 basic properties (ACID):

• Atomicity (automicity), not divided into smaller units
• Consistency (consistency), transaction is a whole
• Isolation (isolation), which is not visible to other transactions within
• Persistence (durability), transactions that are not successfully committed are rolled back to their initial state when a power outage is resumed

2. Concurrency model and database locking
Transactional consistency and isolation properties depend on the database's locking mechanism (LOCKING mechanism), where the data is locked until the transaction is completed or rolled back when a transaction requires data from another in-progress transaction. Transaction access to locked data must wait for the data to be released, which can affect overall performance and scalability during CEO transactions. This concurrent data access model, which exploits data locks, is called a pessimistic (pessimistic) model. In the optimistic (optimistic) model, instead of using a data lock, the data is read directly, and when the operation completes, the data is read, and if the data has changed, an exception is thrown and processed using custom logic.3. Transaction ISOLATION LEVELComplete isolation is logically optimal, but in practice it can affect performance and scalability, such as full isolation when making read-only queries. When you fine-tune the isolation of things, we may do the following:

• Dirty Read (DIRTY Read): You can read a value that has been changed by another transaction, but the problem occurs when the transaction encounters a problem rollback.
• Non-repeatable read (nonrepeatable Read): Data inconsistencies were found when the data values in another transaction were read two times because the transaction was changed between two read data.
• Phantom Read (Phanton Read): One transaction reads the data to be deleted by another transaction or reads a second read to the data just added by another transaction

Level	DIRTY READ	Nonrepeatable READ	PHANTOM READ	CONCURRENCY MODEL
Read UNCOMMITTED	Yes	Yes	Yes	None
Read Committed with Locks	No	Yes	Yes	Pessimistic
Read Committed with Snapshots	No	Yes	Yes	Optimistic
Repeatable Read	No	No	Yes	Pessimistic
Snapshot	No	No	No	Optimistic
Serializable	No	No	No	Pessimistic

In MySQL, only 4 transaction isolation levels are provided, namely:

READ UNCOMMITTED (Read UNCOMMITTED content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).
Read Committed (read submit content)

This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.
Repeatable Read (can be reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism.

Serializable (Serializable)
This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

 4. Single-Transaction and distributed transactionsIn ADO, a transaction generally refers to all the work that can be handled in an open link. If you need a transaction that contains more than one link, you need to use a distributed transaction. In Windows, the management tool that handles distributed transactions is the DTC distributed TRANSACTION coordinator.5. Create a transactionThere are two types of transactions, one is an implicit transaction and one is an explicit transaction. Each SQL statement runs as an implicit transaction, meaning that even if you do not explicitly create a transaction, the transaction exists, and a statement is a transaction that guarantees the full execution of each SQL statement, either successfully or rolled back. shows that creating a transaction requires code to be added to the program.6. Use the T-sqlt-sql statement to display the Create transactionExamples are as follows:

sql:explicit Transaction SET xact_abort onbegin TRY   BEGIN TRANSACTION   --work code here   COMMIT transactionend trybegin CATCH   ROLLBACK TRANSACTION   --cleanup codeend CATCH

Note the principal of a transaction is in a try statement block, and a stored procedure can be used in a try statement block.7. Create a transaction using the Dbtransaction object display in ADOThe sample code is as follows: static void Main () {connectionstringsettings cnsetting = configurationmanager.connectionstrings[            "TestApp.Properties.Settings.toucaiConnectionString"]; using (Mysqlconnection cn = new Mysqlconnection ())) {CN.                ConnectionString = cnsetting.connectionstring; cn.                Open (); using (Mysqltransaction Tran = CN. BeginTransaction (){try {//work code here using (Mysqlcommand cmd = CN. CreateCommand ()) {cmd. Transaction = Tran;Cmd.commandtext = "SELECT count (*) from employee"; var count = (long) cmd.                            ExecuteScalar (); Console.WriteLine (count.                        ToString ()); }//if We made it this far, committran.commit ();} catch (Exception XCP) {Tran. Rollback ();Cleanup Code Console.WriteLine (XCP.                    Message); }}}} in this code, you first create a linked object (CN), and then this linked object uses the BeginTransaction () method to create a transaction with a try statement block that contains the relevant execution code for the transaction and commits the thing Service, if an exception is thrown, the catch statement block rolls back the transaction, and of course can do some other work, such as modifying the values of some variables in the comment code line. Note that the transaction property value of the command object (CMD) must be set to the transaction instance that the link generates. This thing is scoped to a try statement block (which should also include a catch statement block), but because it is created by a link, it cannot be used by other links.8. Set the transaction isolation levelEach link can set its own transaction isolation level, there are three ways to set the transaction isolation level:

• Add related SQL statements to the stored procedure, such as set TRANSACTION isolation level repeatable READ
• Add a query hint statement in the SQL statement, such as

  SELECT * from CUSTOMERS with (NOLOCK)

• In the BeginTransaction () method, add the isolation level setting information, such as CN. BeginTransaction (Isolationlevel.repeatableread)

 

9. System.Transactions namespaces

System.Transactions namespace provides more powerful transactional support, is simpler to use, and naturally supports distributed transactions. 10. Create a transaction using TransactionScope in addition to creating a transaction using the linked Begaintransaction () method, You can also create transactions using the TransactionScope class in the System.Transactions space, which is called a "local lightweight transaction", provides full distributed transaction support, and more crucially, the transaction is an implicit transaction. Transactions created with the previous method need to display the call commit method and the rollback method to commit and rollback, while the scope created by TransactionScope automatically contains a transaction and can be automatically committed and rolled back. The sample code is as follows: using (TransactionScope ts = newTransactionScope())             {                using (mysqlconnecti On cn = New Mysqlconnection ())                 {                    CN. ConnectionString = cnsetting.connectionstring;                    CN. Open ();                    //work code here      &N Bsp             using (mysqlcommand cmd = CN. CreateCommand ())                     {                        try                        {                            CMD. CommandType = commandtype.text;  &NBsp                         CMD. CommandText = "UPDATE employee SET EmpID = ' MY002 ' WHERE empID = ' MY001 '";                            var count = cmd. ExecuteNonQuery ();                            CMD. CommandText = "update EmployeesSET EmpID = ' MY002 ' WHERE empID = ' MY001 '; Count = cmd.                            ExecuteNonQuery (); Console.WriteLine (count. ToString ());Ts.complete ();                       }          &N Bsp             catch (Exception ex)                 &NBSP ;       {                            (Co) Nsole. WriteLine (ex. Message);                       }                                         &NBS P }                                  &N Bsp }           } This code does nothing to the database because the second SQL statement has an error, so although the first statement is correct, the entire transaction is still rolled back and the data is not changed. It is proved that the rollback mechanism is running normally. The code begins by creating a transaction scope in a using statement, in which case a link is automatically added to the link by the automatic transaction scope, and the command does not have to specify its transaction property. If an error is thrown or the compl is not foundETE () statement, the transaction is rolled back. Otherwise, the entire transaction is completed when the complete () statement is seen. Note that the first statement of the transaction is still valid if the statement in the transaction (executable) is executed, whether the code can see the complete () statement, or if we put the Ts.complete () statement outside the Try-catch statement block, that is, the transaction will not be rolled back despite the exception thrown. The complete () statement executes only once, and throws an exception if executed more than once. Because links are created within the scope of a transaction, if you create multiple links within this scope, you can create a distributed transaction.  10. Set Transaction OptionsWhen you create a transaction using a transaction scope, you set the transaction isolation level and transaction timeout length (Transaction ' s Timeout Period) by setting the transaction options (Transaction options) method. There are two setting methods, one setting the Transactionoptions enumeration value and the other using the transaction scopeconstructor Functionsetting, the value of the Transactionoptions enumeration value has the following 3: Required: If your system already has a transaction running, add the commands in the transaction scope, and if not, create one, which is the default setting. RequiresNew: Always create a new transaction, regardless of whether an existing transaction is running. For example, there is already a thing in the banking system to handle the deposit and withdrawal operation, if a reconciliation operation is required at this time, there is no need to exist in a transaction with the deposit and withdrawal operation, that is, the reconciliation operation will run independently regardless of whether the deposit and withdrawal operation is successful or not. Suppress: Ignores any transactions within the scope of a transaction that are used to create a non-transactional environment in which the compete () operation is not valid.11. Using Distributed Transactionswhen using a transactional scope, ADO. NET always generates lightweight transactions first, but in some cases local lightweight is also promoted to distributed transactions:

• When a durable resource manager was used that doesn ' t implement the ipromotablesinglephasenotification interface, such as SQL Server 2000

• When durable resource managers is enlisted in the same transaction

• When the transaction spans multiple application domains

Ado. NET Learning notes-transaction processing