TransactionScope distributed transactions and non-distributed transactions

Distributed transactions do not sound good. It only minimizes the possibility of data inconsistency and cannot be completely avoided. In my application, the total number of operations is about million, and there are dozens of errors. Of course, this error rate is tolerable. What we cannot bear is that when your DB is in a cluster, msdtc will also be used as a resource, and some cluster problems will lead to strange msdtc unavailability, troubleshooting is depressing. As we all know, as a large system, cluster is unlikely to be used, so the problem of msdtc will be very prominent, and I feel really fragile ...... Transactions are used to ensure the consistency of very critical data. whether to use transactions depends on your business needs. however. NET 2.0 is not tough enough to support distributed transactions. In some cases, you have to sacrifice something, such as some parts that can be executed in parallel, but cannot be executed in parallel. System. transactions does not even find Transactioin objects that support parallel transaction execution. Therefore, if you want to make the code more "Dummies", you have to perform all the operation steps in sequence, of course, you cannot quickly respond to parallel steps in parallel execution. Of course, the throughput of the system may be increased by a little bit because of the serial increase, depending on your needs. If you have to use distributed transactions, you have to consider: 1. Must this operation be performed in transactions? If this step is not completed or fails, is it worth rolling back the entire transaction? Is there no elegant compensation or fault tolerance measures? 2. Are there so many points necessary for distributed transactions? Must I perform real-time operations? Some points cannot be reduced through notification operations? 3. Have you performed transaction-independent operations after initiating a distributed transaction, even though these operations are irrelevant to the transaction? (For example, reading data, computing, returning messages to users, and calling and returning from other modules) the transaction should be completed as soon as possible. 4. Have you considered some read operations in the transaction? This is an easy mistake. You Enlist a select operation in the transaction. 5. Your operations and certain steps can be executed after all the operations are completed. Such operations have obvious notification features. Notification operations mean that I will give you a notification and I promise to notify you. You must take this notification and ensure that the process is successful, but you do not have to notify you to handle it. This operation can obviously be performed using another task.

TransactionScope declares in the document that it only increases the transaction level when necessary (distributed transactions are used only when multiple databases are used. If it is the same database, it is best to use SqlTransaction), But not in fact. In TransactionScope, as long as you operate the database more than once with different SqlConnection objects (whether your target is the same instance or database), the transaction level will be upgraded to distributed transactions. It's very tricky, right? Of course, we can understand this from the current implementation of SqlClient: we know that the connection held by the transaction is not released before the transaction is committed or rolled back, whether you call Close or Dispose in the code or not (the Dispose mode should be mentioned here. There is no essential difference in calling these two methods ). Therefore, even if two sqlconnections are generated using the same connection string, the internal connection in the internal referenced connection pool is not one. That is to say, from the DB perspective, it is two different sessions. different sessions cannot share a local transaction because the connection is not the sameAnd can only be managed through distributed transactions. But cannot Microsoft optimize it? Yes. The connection string can be parsed into a bunch of fields, just like SqlConnectionStringBuilder. Then, compare the information in the previous internal connection to determine whether to reuse the previous connection or get a new one from the connection pool. Of course there is another hurdle, Different connection strings generate different connection pools., Even if there is only one more space, the connection pool may have to be transformed. However, if Microsoft does not perform such a transformation, we have to do it ourselves -- all the operations on the same database of the same instance are executed on a SqlConnection. It is worth mentioning that the DbConnection object has an EnlistTransaction method, which gives us the opportunity to manually distribute transactions. Now we design things. In most cases, we use the top-down design, and finally we will care about the persistent method. In this case, DbConnection. EnlistTransaction is particularly useful. In contrast, TransactionScope is too rigid. Moreover, manual Enlist control allows us to execute a bunch of operations in parallel, and finally commit or roll back together (as long as your needs can endure a slightly higher error rate. In theory, the two-phase commit method of distributed transactions is destined that the so-called "slightly higher" is not much higher than the direct use of distributed transactions .) Finally, we will mention the configuration of MSDTC and the DTCPing tool. MSDTC configuration is mainly "Security Configuration" and must be configured on the Application Server (the application server is also a node of distributed transactions) and all related DB servers. How to configure google. After the configuration is complete, copy the DTCPing to all these servers and perform two-way connectivity confirmation. A-> B, and B->. Pay attention to the usage of DTCPing, which is clearly written above. If a machine cannot parse the name of another machine, modify the hosts file. 2) SqlTransaction objectUse the BeginTransaction method of the Connection object to generate the Commit () method and Rollback method of the SqlTransaction object specified by the SqlCommand object. SqlTransactionAlways use Try/Catch for exception handling. If the connection is terminated or the transaction has been rolled back on the server, both Commit and Rollback generate InvalidOperationException.

The member function Save (string rollbackstring) creates a Save point in the transaction (which can be used to roll back a part of the transaction) and specifies the name of the Save point. For exampleSqlTransaction. Save ("NoUpdate") // create a rollback point.SqlTransaction. Rollback ("NoUpdate") // roll back to the Rollback point

Public void RunSqlTransaction (string myConnString)
{
SqlConnection myConnection = new SqlConnection (myConnString );
MyConnection. Open ();

SqlCommand myCommand = new SqlCommand ();
SqlTransactionMyTrans;

// Start a local transaction
MyTrans = myConnection.BeginTransaction(IsolationLevel. ReadCommitted, "SampleTransaction ");
// Must assign both transaction object and connection
// To Command object for a pending local transaction
MyCommand. Connection = myConnection;
MyCommand. Transaction = myTrans;

Try
{
MyCommand. CommandText = "Insert into Region (RegionID, RegionDescription)
VALUES (100, 'description ')";
MyCommand. ExecuteNonQuery ();
MyCommand. CommandText = "Insert into Region (RegionID, RegionDescription)
VALUES (101, 'description ')";
MyCommand. ExecuteNonQuery ();
MyTrans. Commit ();
Console. WriteLine ("Both records are written to database .");
}
Catch (Exception e)
{
MyTrans. Rollback ("SampleTransaction ");
Console. WriteLine (e. ToString ());
Console. WriteLine ("Neither record was written to database .");
}
Finally
{
MyConnection. Close ();
}
} 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. The highest value is obtained when the transaction is serializable.Isolation level. At this level, the results obtained from a group of parallel transactions are the same as those obtained by running each firm consecutively. High Isolation limits the number of transactions that can be executed in parallel, so some applications decreaseIsolation levelIn exchange for a larger 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.

  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. WhileIsolation levelTo avoid these situations. The level at which the transaction is prepared to accept inconsistent data is calledIsolation level.Isolation levelIs the degree to which a transaction must be isolated from other transactions. Relatively lowIsolation levelConcurrency can be increased, but the cost is to reduce the correctness of the data. On the contrary, the higherIsolation levelData correctness can be ensured, but it may have a negative impact on concurrency.

AccordingIsolation levelDBMS provides different mutex guarantees for parallel access. In the SQL Server database, four typesIsolation level: Uncommitted read, committed read, Repeatable read, and serializable read. These four typesIsolation levelThe concurrency data integrity can be ensured 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. ThereforeIsolation levelIn fact, the database service efficiency is 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 wayIsolation levelIt also has its own meaning.

In the. net FrameworkIsolation levelIs defined by enumeration System. Data. IsolationLevel:
　

 

[Flags] [Serializable] Public enum IsolationLevel

 

Its members and meanings are as follows:

 

Members	Meaning
Chaos	Cannot be rewrittenIsolation levelPending changes in a higher transaction.
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	Using and specifyingIsolation levelDifferentIsolation levelBut cannot determine this level.

 

Explicit comments: four databasesIsolation levelIng exists here.

By default, SQL Server uses ReadCommitted to submit read requests)Isolation level.

AboutIsolation levelThe last point is that if you change the transaction execution processIsolation level, The subsequent names are all in the latestIsolation levelRun --Isolation levelChanges take effect immediately. With this, you can use it more flexibly in your transactions.Isolation levelTo achieve higher efficiency and concurrency security.